dashboard_02_merge.Rmd

---
title: "Dashboard 02 - Merge"
---

### Import packages 

```{r importcode01, message=FALSE}
library(dplyr)
library(purrr)
library(stringr)
library(readr)
library(lubridate)
library(hms)
library(testthat)
```

# Current data tables

* Consent 
* Sociodemographic information
* Gift card
* APS investigations 
* Clutter scale
* General health
* Observational measures
* Self report 

# Initial data wrangling

## Convert all variable names to snake case

```{r walkcode}
walk(
  # Grab the names of all data frames in the global envrironment
  .x = ls()[map_lgl(ls(), ~ is.data.frame(get(.)))],
  .f = function(x) {
    # Grab individual df from environment
    df <- get(x)
    # Grab the variables names
    var_names <- names(df)
    # Convert variable names to snake case
    var_names <- str_replace_all(var_names, '(\\B)([A-Z])', '_\\2')
    # Convert variable names to lower case
    var_names <- str_to_lower(var_names)
    # Fix medstar_id
    var_names[var_names == "medstar_i_d"] <- "medstar_id"
    # assign back to the dataframe
    names(df) <- var_names
    # Replace df with new names in global environment
    assign(x, df, envir = .GlobalEnv)
  }
)
```


## Keep only the columns needed from each data frame

```{r consent_sub}
consent_sub <- consent %>%
  select(
    medstar_id, consent_created_by = x_created_by,
    consent_timestamp = x_created_timestamp, consent_status = x_record_status,
    consent_record_month = x_record_month, consent_record_year = x_record_year,
    consent_interview_month  = xc_interview_month,
    consent_interview_year = xc_interview_year
    )
```


```{r aps_investigations_sub}
aps_investigations_sub <- aps_investigations %>%
  select(aps_investigation_created_by = x_created_by,
         aps_investigation_created_timestamp = x_created_timestamp,
         aps_investigation_status = x_record_status,
         medstar_id, name_full, aps_report, aps_times, aps_first, aps_recent,
         x_record_month, x_record_year, xc_lead_panel_summary_count)
```

```{r clutter}
# Clutter scale
clutter_scale_sub <- clutter_scale %>%
  select(clutter_scale_created_by = x_created_by,
         clutter_scale_timestamp = x_created_timestamp, medstar_id,
         clutter_living, look_kitchen, clutter_kitchen, look_bedroom,
         clutter_bedroom, look_living, clutter_living_photo,
         clutter_kitchen_photo, clutter_bedroom_photo, name_full,
         clutter_scale_status = x_record_status, name_last, x_record_month,
         x_record_year)
```


```{r generalhealth}
# General Health
general_health_sub <- general_health %>%
  select(general_health_created_by = x_created_by,
         general_health_timestamp = x_created_timestamp, medstar_id,
         name_full, health_bp_systolic, health_heart, health_oxygen, health_temp,
         health_pain, outcomes_health_general, outcomes_health_vigorous,
         outcomes_health_moderate, outcomes_health_uphill, outcomes_health_bend,
         outcomes_health_block, outcomes_health_bathe, outcomes4_weeks_pain,
         outcomes_health_keep, outcomes_month_social, outcomes_month_nervous,
         outcomes_month_calm, outcomes_month_blue, outcomes_month_happy,
         outcomes_month_dumps, outcomes_tf_ill, outcomes_tf_anybody,
         outcomes_tf_bad, outcomes_tf_health, depres_satisfied,
         depres_hopeless, depres_better, depres_activities, depres_empty,
         depres_bored, depres_spirits, depres_afraid, depres_happy,
         depres_helpless, depres_home, depres_memory, depres_alive,
         depres_worthless, depres_energy, x_record_status, auditc_drink, auditc_day,
         auditc_five, health_bp_diastolic, outcomes_health_unable, nesarc_drink,
         nesarc_months, x_record_month, x_record_year, peg_pain,
         peg_enjoyment, peg_activity)
```

```{r giftcard}
# Gift card
gift_card_sub <- gift_card %>%
  select(gift_card_created_by = x_created_by,
         gift_card_timestamp = x_created_timestamp,
         gift_card_status = x_record_status, medstar_id, name_full,
         x_interviewer_name, gift_card_i_d, x_qualtrics, x_record_month,
         x_record_year, x_record_comment, x_randomizer, xc_u_r_l, xc_peg,
         xc_mo_health, xc_mo_feelings, xc_mo_outcomes_tf, xc_depres_scale,
         xc_auditc, xc_neglect_person, xc_finance_person,
         xc_emotional_incident_person, xc_physical_person, xc_sexual_person,
         xc_aps_report, xc_detect, xc_eai_general_assessment,
         xc_eai_abuse_indicators, xc_eai_neglect_indicators,
         xc_eai_exploitation_indicators, xc_eai_abandonment_indicators,
         xc_eai_environmental, xc_summary, xc_aps_reported, xc_case_i_d,
         xc_interview_month, xc_interview_year, xc_interview_month_year_abbr)
```


```{r observational}
# Observational measures
observational_measures_sub <- observational_measures %>%
  select(observational_measures_created_by = x_created_by,
         observational_measures_timestamp = x_created_timestamp, medstar_id,
         clothes, hygiene, poor_hygiene, nutrition, malnutrition, skin_integrity,
         hair, nails, bruising, fractures, stages_healing, abuse_statement,
         contracture, decubitus, dehydration, diarrhea, depression,
         impaction, urine_burn, obvious_disease, inappropriate_medication,
         repetitive_hosp_admission, neglect_statement, money_misuse,
         evidence, goods_for_services, inability_account_money,
         exploitation_statement, caretaker_withdrawn_care, left_alone_unsafe,
         abandonment_statement, yard_sidewalk, roof, windows,
         walls, odor, kitchen_cleanliness, bathroom_cleanliness,
         bedroom_cleanliness, living_room_cleanliness, structural_problems,
         pets, utilities, unusual_odor, disarray_hoarding, health_safety_concern,
         caregiver_lack_knowledge, caregiver_unengaged, caregiver_frustrated,
         caregiver_anxious, isolated_home, depressed, poor_personal_hygiene,
         inadequately_clothed, difficulty_medications, unmet_needs,
         report_elder_abuse, hoarding_medications, name_full,
         observational_measures_status = x_record_status,
         interior_condition, clutter, neglect_comments, exploitation_comments,
         abandonment_comments, exterior_condition, summary_evidence_abuse,
         summary_evidence_neglect, summary_evidence_exploitation,
         summary_evidence_abandonment, summary_comments,
         general_assessment_comments, lacerations, sexual_abuse,
         abuse_indicators_comments, x_record_month, x_record_year, aps_reported,
         aps_reported_confirmation_number, aps_reported_method, xc_case_i_d,
         at_physical, at_phys_why, at_sexual, at_sex_why, at_emotional,
         at_emot_why, at_neglect, at_neg_why, at_abandon, at_aban_why,
         at_financial, at_fina_why, at_self, at_self_why,
         xc_lead_panel_summary_count, xc_lead_panel_summary_comment_count,
         xc_lead_panel_summary, c_detect_status)
```

```{r selfreport}
# Self report
self_report_sub <- self_report %>%
  select(self_report_created_by = x_created_by,
         self_report_timestamp = x_created_timestamp,
         self_report_status = x_record_status, medstar_id, name_full,
         neglect_go, neglect_go_help, neglect_go_reliable, neglect_food,
         neglect_food_help, neglect_food_reliable, neglect_house,
         neglect_house_help, neglect_house_reliable, neglect_clean,
         neglect_clean_help, neglect_clean_reliable, neglect_ready,
         neglect_ready_help, neglect_ready_reliable, neglect_bills,
         neglect_bills_help, neglect_bills_reliable, neglect_person,
         finance_help, finance_person, finance_person_permission,
         finance_person_decisions, finance_copies, finance_person_forged,
         finance_person_tricked, finance_person_stolen,
         finance_stranger_permission, finance_stranger_forged,
         finance_stranger_tricked, emotional_yelled,
         emotional_yelled_times, emotional_yelled_age, emotional_embarrassed,
         emotional_embarrassed_times, emotional_embarrassed_age,
         emotional_harassed, emotional_harassed_times, emotional_harassed_age,
         emotional_refused, emotional_refused_times, emotional_refused_age,
         emotional_incident_reported, emotional_incident_person,
         emotional_person_live, emotional_person_alcohol,
         emotional_person_counseling, emotional_person_police,
         emotional_person_job, emotional_person_friends, emotional_person_help,
         emotional_person_no_live, physical_hit, physical_hit_times,
         physical_hit_age, physical_restrain, physical_restrain_times,
         physical_restrain_age, physical_hurt, physical_hurt_times,
         physical_hurt_age, physical_incident60, physical_incident_reported,
         physical_incident_person, physical_person, physical_person_live,
         physical_person_alcohol, physical_person_counseling,
         physical_person_police, physical_person_job, physical_person_friends,
         physical_person_help, physical_person_no_live, sexual_harm,
         sexual_harm_times, sexual_harm_age, sexual_touched, sexual_touched_times,
         sexual_touched_age, sexual_forced, sexual_forced_times,
         sexual_forced_age, sexual_picture, sexual_picture_times,
         sexual_picture_age, sexual_incident60, sexual_incident_reported,
         sexual_incident_person, sexual_person, sexual_person_live,
         sexual_person_alcohol, sexual_person_counseling, sexual_person_police,
         sexual_person_job, sexual_person_friends, sexual_person_help,
         sexual_person_no_live, emotional_yelled_times_times,
         emotional_yelled_age_years, emotional_embarrassed_times_times,
         emotional_embarrassed_age_years, emotional_harassed_times_times,
         emotional_harassed_age_years, emotional_refused_times_times,
         emotional_refused_age_years, emotional_incident60,
         physical_hit_times_times, physical_hit_age_years,
         physical_restrain_times_times, physical_restrain_age_years,
         physical_hurt_times_times, physical_hurt_age_years,
         sexual_harm_times_times, sexual_harm_age_years,
         sexual_touched_times_times, sexual_touched_age_years,
         sexual_forced_times_times, sexual_forced_age_years,
         sexual_picture_times_times, sexual_picture_age_years, x_record_month,
         x_record_year, finance_stranger_permission_times,
         finance_stranger_permission_times_times,
         finance_stranger_permission_age, finance_stranger_permission_age_years,
         finance_stranger_forged_times, finance_stranger_forged_times_times,
         finance_stranger_forged_age, finance_stranger_forged_age_years,
         finance_stranger_tricked_times, finance_stranger_tricked_times_times,
         finance_stranger_tricked_age, finance_stranger_tricked_age_years,
         xc_case_i_d, fear_afraid, fear_afraid_response, fear_future,
         fear_future_response, fear_grateful, fear_grateful_response,
         fear_forward, fear_forward_response, fear_important,
         fear_important_response, xc_emotional_list, xc_physical_list,
         xc_sexual_list, finance_person_permission65,
         finance_person_permission_last_time, finance_person_forged65,
         finance_person_forged_last_time, finance_person_tricked65,
         finance_person_tricked_last_time, finance_person_stolen65,
         finance_person_stolen_last_time, finance_stranger_permission65,
         finance_stranger_permission_last_time, finance_stranger_forged65,
         finance_stranger_forged_last_time, finance_stranger_tricked65,
         finance_stranger_tricked_last_time, emotional_yelled65,
         emotional_yelled_last_time, emotional_embarrassed65,
         emotional_embarrassed_last_time, emotional_harassed65,
         emotional_harassed_last_time, emotional_refused65,
         emotional_refused_last_time, emotional_incident_reported_v2,
         emotional_incident_person_v2, emotional_person_live_then,
         emotional_person_live_now, physical_hit65, physical_hit_last_time,
         physical_restrain65, physical_restrain_last_time, physical_hurt65,
         physical_hurt_last_time, physical_incident_reported_v2,
         physical_incident_person_v2, physical_person_live_then,
         physical_person_live_now, sexual_harm65, sexual_harm_last_time,
         sexual_touched65, sexual_touched_last_time, sexual_forced65,
         sexual_forced_last_time, sexual_picture65, sexual_picture_last_time,
         sexual_incident_reported_v2, sexual_incident_person_v2,
         sexual_person_live_then, sexual_person_live_now, report_private,
         report_influence, xc_lead_panel_summary_count)
```

```{r sociodemographic}
# Sociodemographic information
sociodemographic_information_sub <- sociodemographic_information %>%
  select(sociodemographic_created_by = x_created_by,
         sociodemographic_timestamp = x_created_timestamp,
         sociodemographic_status = x_record_status, medstar_id, name_full,
         sode_people, sode_marital, sode_age, sode_hispanic, sode_race,
         sode_school, sode_employed, sode_income, sode_military, sode_unwanted,
         sode_sexual, hsi_contact, sode_age_years, x_record_month,
         x_record_year, sogi_identity, sogi_orientation)
```


# Merge the data frames together

```{r merged}
merged <- consent_sub %>%
    left_join(self_report_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(aps_investigations_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(clutter_scale_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(general_health_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(gift_card_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(observational_measures_sub, by = c("medstar_id" = "medstar_id")) %>%
    left_join(sociodemographic_information_sub, by = c("medstar_id" = "medstar_id"))
```

### Filter for records who have consented to participate

```{r consented}
merged_consented <- merged %>%
  filter(
    consent_status == "Consent received/Participating"
    )
```

# Issue with transforming `Don������t know` to `Don't know`

```{r}
merged_consented <- merged_consented %>% 
  mutate(
    across(
      .cols = c(sode_employed, sode_marital, sode_race, 
                sode_income, sogi_orientation, aps_first, outcomes_health_keep,
                outcomes_health_unable, auditc_drink, auditc_day, auditc_five, 
                xc_mo_health, xc_auditc, xc_neglect_person, xc_aps_report,
                neglect_go_help, neglect_clean_help, neglect_clean_reliable,
                neglect_go_help, neglect_clean_help, neglect_clean_reliable,
                neglect_ready, neglect_ready_help, neglect_ready_reliable, 
                neglect_bills, neglect_bills_help, neglect_bills_reliable, 
                neglect_person, neglect_statement, neglect_comments, 
                finance_help, finance_person, emotional_yelled_times,
                emotional_yelled_age, emotional_embarrassed_age, 
                emotional_embarrassed_times, emotional_harassed_times, 
                emotional_refused_times, emotional_refused_age, 
                emotional_incident_person, emotional_person_friends, 
                physical_hit_times, physical_restrain_times, 
                physical_restrain_age, physical_hurt_times, 
                physical_hurt_age, physical_person_friends, 
                sexual_harm_times, sexual_harm_age, sexual_touched_times, 
                sexual_forced_times, sexual_person_counseling,
                sexual_person_friends, finance_stranger_permission_times,
                fear_future, fear_afraid, fear_grateful, fear_forward,
                fear_important, emotional_incident_person_v2,
                report_private, xc_neglect_person, outcomes_health_general),
      .fns  = ~ if_else(stringr::str_detect(.x, "know"), "Don't know", .x)
    )
  )
```

```{r}
merged_consented$sode_school <- 
  str_replace(merged_consented$sode_school, c("\xd5"), "'")

merged_consented$fear_afraid_response <- 
  str_replace(merged_consented$fear_afraid_response, c("\r"), "")

merged_consented$fear_future_response <- 
  str_replace(merged_consented$fear_future_response, c("\r"), "")

merged_consented$fear_grateful_response <- 
  str_replace(merged_consented$fear_grateful_response, c("\r"), "")

merged_consented$fear_forward_response <- 
  str_replace(merged_consented$fear_forward_response, c("\r"), "")

merged_consented$fear_important_response <- 
  str_replace(merged_consented$fear_important_response, c("\r"), "")
```