Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
### Data load
library(tidyverse)
library(RMySQL)
library(readxl)
main <- read_excel("JISC_05-02-20.xlsx", skip = 1) %>%
rename(TeamC = `Team Number`, Date = CompletionDate) %>%
select(-starts_with("1."), -starts_with("2."), -starts_with("3."), -starts_with("4."))
main <- main %>%
mutate(Date = as.Date(Date)) %>%
rename(SU = `5. I wish to answer this questionairre.... (Please select one option)`,
form_age = `6. Please tell us how old you are. (Please select one option)`) %>%
mutate(SU = case_when(
SU == "As a Carer of a Service User/Patient, who wishes to respond about the experiences of a Service User/Patient for whom you care." ~ 1,
SU == "On behalf of myself as a Service User/Patient." ~ 0,
SU == "As a Carer who wishes to respond about your own experiences of the trust's services." ~ 9)) %>%
mutate(formtype = case_when(
SU == 9 ~ "carer",
form_age == "12 to 18 years old." ~ "YP",
form_age == "Over 18." ~ "adult"
)) %>%
rename(Promoter2 = `7. How likely are you to recommend this service to friends and family if they needed similar care or treatment? (Please select only one answer.)`,
Improve = `8. What could we do better?`,
Best = `9. What did we do well?`,
Optout = `10. Please tick this box if you DO NOT wish your comments to be made public.`,
Service = `11. How good was the service you received?`,
Listening = `12. How good were our services at listening to you?`,
Communication = `13. How good were our services at communicating with you?`,
Respect = `14. How good were our services at showing you respect?`,
InvCare = `15. How good were our services at involving you in decisions about your care or treatment?`,
Positive = `16. How good were our services at making a positive difference to your health and wellbeing?`,
Promoter_C = `17. Would you say this is a good service for your friends and family to be looked after by if they needed similar care or treatment to you?`,
ServiceC = `18. Did we do a good job?`,
ListeningC = `19. Did we listen to you?`,
RespectC = `20. Did we show you respect?`,
CommunicationC = `21. Did we speak to you in a nice way?`,
InvCareC = `22. Did you feel we asked you what you wanted?`,
InfoServiceC = `23. Did you feel we were good at talking to you?`,
OutOfHoursC = `24. Did you know who to speak to if you were scared or worried?`,
PrivacyC = `25. Did you feel you could talk to us on your own if you wanted to?`,
InvMed = `26. If you are taking any medicine, were we good at telling you about what it could do to you? (If you are not takng any medicine please leave blank.)`,
ConfidentialityC = `27. Was confidentiality explained to you?`,
ImproveC = `28. What could we do better?`,
BestC = `29. What did we do well?`,
OptoutC = `30. Please tick this box if you DO NOT wish your comments to be made public.`,
PromoterYP = `31. I would say this is a good service for my friends and family to be looked after by if they needed a similar care or treatment to me.`,
ServiceYP = `32. Overall I had a good experience.`,
ListeningYP = `33. I feel as though they listened to me.`,
CommunicationYP = `34. I feel they communicated well with me.`,
RespectYP = `35. I was treated with respect.`,
InvCareYP = `36. I feel they asked me what I wanted.`,
PositiveYP = `37. I feel the service helped me.`,
OutOfHoursYP = `38. I knew who to contact if I was worried.`,
PrivacyYP = `39. I was given the chance to speak to staff in private if I wanted to.`,
InvMedYP = `40. The staff explained about the treatment/medication I had.`,
Confidentiality = `41. Confidentiality was explained to me.`,
ImproveYP = `42. What could we do better?`,
BestYP = `43. What did we do well?`,
OptoutYP = `44. Please tick this box if you DO NOT wish your comments to be made public.`,
Gender = `45. Gender:`,
Ethnic = `46. Ethnic Group:`,
Disability = `47. Disability: Do you have a disability or long term health condition which affects your day to day activities?`,
Religion = `48. Religion/Belief:`,
Age = `49. How old are you?`,
carertype = `50. I am a carer, family member, friend, parent, or guardian of someone: (Select all that apply.)`,
carertype2 = `51. I am a young carer: (Please select from the drop down menu.)`,
PromoterCa = `52. How likely is it that you would tell friends and family that this service was helpful to carers?`,
ImproveCa = `53. What could we do better?`,
BestCa = `54. What did we do well?`,
OptOutCa = `55. Please tick this box if you DO NOT wish your comments to be made public.`,
ListeningCa = `56. How good were our services at listening to you?`,
CommunicationCa = `57. How good were our services at communicating with you?`,
RespectCa = `58. How good were our services at showing you respect?`,
CarersAssess = `59. How good were our services at providing you with information about carers' assessment?`,
SupportServices = `60. How good were our services at providing you with information about support services for carers?`,
InformContact = `61. How good were our services at informing you who to contact if you're worried about the person you care for?`,
PrivacyCa = `62. How good were our services at providing you with privacy when discussing the care or treatment of the person you care for?`,
GenderCa = `63. Gender:`,
EthnicCa = `64. Ethnic Group:`,
DisabilityCa = `65. Disability: Do you have a disability or long term health condition which affects your day to day activities?`,
ReligionCa = `66. Religion/Belief:`,
Sexuality = `67. Sexual Orientation:`,
AgeCa = `68. Age (ranges):`,
Relationship = `69. What is your relationship status?`,
Pregnant = `70. Are you pregnant at this time?`,
Baby = `71. Have you had a baby in the last 26 weeks?`)
# coalesce all the data
main <- bind_cols(
map(c("Date", "TeamC", "formtype", "SU", "Promoter", "Improve", "Best", "Optout",
"Service", "Listening", "Communication", "Respect", "InvCare",
"Positive", "Privacy", "InvMed", "Confidentiality",
"CarersAssess", "SupportServices", "InformContact",
"Gender", "Ethnic", "Disability", "Religion", "Age", "carertype",
"Relationship", "Pregnant", "Baby"), function(x) {
df <- main %>%
select(starts_with(x, ignore.case = FALSE)) %>%
select_if(negate(is.logical))
if(ncol(df) > 1){
df <- coalesce(
!!!(df)
) %>%
as_tibble()
}
names(df) <- x
return(df)
})
)
# check unique values
# main %>%
# select(Service : InformContact) %>%
# unlist() %>%
# unique()
# recode
main <- main %>%
mutate(Promoter = case_when(
Promoter == "Extremely likely." ~ 5,
Promoter == "Extremely Unlikely" ~ 1,
Promoter == "Neither likely or unlikely." ~ 3,
Promoter == "Unlikely" ~ 2,
Promoter == "Unlikely." ~ 2,
Promoter == "Likely." ~ 4,
Promoter == "I agree a lot." ~ 5,
Promoter == "Don't Know." ~ 0,
Promoter == "I disagree a lot." ~ 1,
Promoter == "Extremely unlikely." ~ 1,
TRUE ~ NA_real_
))
main <- main %>%
mutate_at(vars(Service : InformContact), function(x) {
case_when(
x == "Excellent." ~ 5,
x == "Fair." ~ 3,
x == "Poor." ~ 2,
x == "Very Poor." ~ 1,
x == "I agree a lot." ~ 5,
x == "Good." ~ 4,
x == "I disagree a lot." ~ 1,
x == "I am undecided." ~ 3,
x == "I agree a bit." ~ 4,
x == "I disagree a bit." ~ 2
)
})
main <- main %>%
mutate(Optout = replace_na(Optout, "N")) %>%
mutate(Optout = case_when(
Optout == "I DO NOT wish my answers to be made public." ~ "Y"
)) %>%
mutate(Gender = case_when(
Gender == "Female." ~ "F",
Gender == "Male." ~ "M",
Gender == "Other." ~ "O"
)) %>%
mutate(Ethnic = case_when(
Ethnic == "White British." ~ "WB",
Ethnic == "Black Caribbean." ~ "BC",
Ethnic == "Do not wish to say." ~ "8",
Ethnic == "Other White." ~ "WO",
Ethnic == "Indian" ~ "AI",
Ethnic == "Mixed, White and Black African." ~ "MB",
Ethnic == "White Irish." ~ "WI"
)) %>%
mutate(Disability = case_when(
Disability == "No." ~ "N",
Disability == "Yes." ~ "Y",
Disability == "Do not wish to say." ~ "8"
)) %>%
mutate(Religion = case_when(
Religion == "No Religion." ~ "N",
Religion == "Other." ~ "O",
Religion == "Christian." ~ "C",
Religion == "Do not wish to say." ~ "8",
Religion == "Muslim." ~ "M"
)) %>%
mutate(Age = case_when(
Age == "26 to 39." ~ 4,
Age == "40 to 64." ~ 5,
Age == "18 to 25." ~ 3,
Age == "Do not wish to say." ~ 8,
Age == "65 to 79." ~ 6,
Age == "12 to 17." ~ 2,
Age == "Over 80." ~ 7
)) %>%
mutate(carertype = case_when(
carertype == "With a learning disability." ~ 2,
carertype == "With a physical health condition." ~ 3,
carertype == "With Mental Health issues." ~ 1,
carertype == "With Dementia." ~ 4,
carertype == "At the end of life." ~ 6
)) %>%
mutate(Relationship = case_when(
Relationship == "Co-habiting." ~ "Co",
Relationship == "Married." ~ "Ma",
Relationship == "Other." ~ "O",
Relationship == "Single." ~ "Si",
Relationship == "Do not wish to say." ~ "8",
Relationship == "Widowed." ~ "Wi",
Relationship == "Divorced." ~ "Di",
Relationship == "Civil Partnership." ~ "Ci"
)) %>%
mutate(Pregnant = case_when(
Pregnant == "No." ~ "N",
Pregnant == "Yes." ~ "Y",
Pregnant == "Do not wish to say." ~ "8"
)) %>%
mutate(Baby = case_when(
Baby == "No." ~ "N",
Baby == "Yes." ~ "Y",
Baby == "Do not wish to say." ~ "8"
))
main$addedby = "survey.monkey"
# add time code
main$Time = as.numeric(substr(quarters(main$Date), 2, 2)) +
(as.numeric(substr(main$Date, 1, 4)) - 2009) * 4 - 1
# all teams must have a team code
main <- main %>%
filter(!is.na(TeamC))
# final test
# this bit is base R, I still love this function ;-)
lapply(main[, c(which(!names(main) %in% c("Improve", "Best", "TeamC", "Date")))], table)
### connect to database
...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.