survey_munge

### 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
...