library(readxl)
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(tidyr)
library(writexl)
library(lubridate)
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
library(lme4)
## Loading required package: Matrix
##
## Attaching package: 'Matrix'
## The following objects are masked from 'package:tidyr':
##
## expand, pack, unpack
library(openxlsx)
library(Matrix)
cps <- read_excel("C:/Users/peima/OneDrive/Desktop/cps.xlsx")
# create 3 sub-datasets and extract 3 dates baseline/Mo8/Mo16
dataset1 <- cps %>%
select(record_id, baseline_cmplt_date) %>%
filter(!is.na(baseline_cmplt_date)) %>%
distinct(record_id, .keep_all = TRUE)
print(dataset1)
## # A tibble: 158 × 2
## record_id baseline_cmplt_date
## <dbl> <dttm>
## 1 100 2020-03-05 00:00:00
## 2 101 2020-04-01 00:00:00
## 3 102 2020-06-05 00:00:00
## 4 103 2020-05-26 00:00:00
## 5 104 2020-05-19 00:00:00
## 6 106 2020-06-07 00:00:00
## 7 107 2020-05-14 00:00:00
## 8 108 2020-06-28 00:00:00
## 9 109 2020-08-02 00:00:00
## 10 110 2020-05-14 00:00:00
## # ℹ 148 more rows
dataset2 <- cps %>%
select(record_id, mo8_cmplt_date) %>%
filter(!is.na(mo8_cmplt_date)) %>%
distinct(record_id, .keep_all = TRUE)
print(dataset2)
## # A tibble: 123 × 2
## record_id mo8_cmplt_date
## <dbl> <dttm>
## 1 101 2020-12-02 00:00:00
## 2 102 2021-02-05 00:00:00
## 3 103 2021-02-16 00:00:00
## 4 104 2021-01-11 00:00:00
## 5 106 2021-02-08 00:00:00
## 6 107 2020-12-21 00:00:00
## 7 109 2021-05-03 00:00:00
## 8 110 2020-12-28 00:00:00
## 9 111 2020-12-24 00:00:00
## 10 114 2021-01-16 00:00:00
## # ℹ 113 more rows
dataset3 <- cps %>%
select(record_id, mo16_cmplt_date) %>%
filter(!is.na(mo16_cmplt_date)) %>%
distinct(record_id, .keep_all = TRUE)
print(dataset3)
## # A tibble: 97 × 2
## record_id mo16_cmplt_date
## <dbl> <dttm>
## 1 102 2021-11-19 00:00:00
## 2 103 2021-11-19 00:00:00
## 3 104 2021-09-03 00:00:00
## 4 106 2021-10-19 00:00:00
## 5 109 2022-01-26 00:00:00
## 6 110 2021-08-15 00:00:00
## 7 111 2021-08-10 00:00:00
## 8 114 2021-09-10 00:00:00
## 9 115 2021-08-26 00:00:00
## 10 119 2021-12-14 00:00:00
## # ℹ 87 more rows
for(i in 1:nrow(cps)) {
id <- cps$record_id[i]
time_period <- cps$redcap_event_name[i]
if(time_period == "baseline_arm_1") {
cps$date[i] <- dataset1 %>% filter(record_id == id) %>% pull(baseline_cmplt_date) %>% first()
} else if(time_period == "8_months_arm_1") {
cps$date[i] <- dataset2 %>% filter(record_id == id) %>% pull(mo8_cmplt_date) %>% first()
} else if(time_period == "16_months_arm_1") {
cps$date[i] <- dataset3 %>% filter(record_id == id) %>% pull(mo16_cmplt_date) %>% first()
}
}
print(cps)
## # A tibble: 378 × 203
## record_id redcap_event_name cps_crid adc_crid type_caregiver adc_site
## <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 100 baseline_arm_1 200 2593 1 1
## 2 101 baseline_arm_1 201 3947 1 1
## 3 101 8_months_arm_1 NA NA NA NA
## 4 102 baseline_arm_1 202 2509 1 1
## 5 102 8_months_arm_1 NA NA NA NA
## 6 102 16_months_arm_1 NA NA NA NA
## 7 103 baseline_arm_1 203 57051 1 2
## 8 103 8_months_arm_1 NA NA NA NA
## 9 103 16_months_arm_1 NA NA NA NA
## 10 104 baseline_arm_1 204 56034 1 2
## # ℹ 368 more rows
## # ℹ 197 more variables: diagnosis <dbl>, enrollment_status <dbl>,
## # ensta_oth <lgl>, baseline_cmplt_date <dttm>, mo8_cmplt_date <dttm>,
## # diffmo_base_mo8 <dbl>, mo16_cmplt_date <dttm>, diffmo_mo8_mo16 <dbl>,
## # cmplt_assmnt_notes <lgl>, cg_cr_notes <chr>, date <dttm>,
## # caregiver_age <dbl>, recipient_age <dbl>, caregiver_gender <dbl>,
## # caregiver_gender_other <lgl>, recipient_gender <dbl>, …
# fill in the empty cells of the variables collectec at baseline for Month 8 and 16
cps_clean <- cps %>%
group_by(record_id) %>%
fill(type_caregiver, adc_site,enrollment_status,caregiver_age, diagnosis,
recipient_age,caregiver_gender, recipient_gender, ethnicity,
marital_status,highest_education, education, language,
birth_place_us, employment_situation, household_income,
people_in_household, safe_neighborhood,hl_1, hl_2,neighborhood_1,
neighborhood_2,
.direction = "down")
cps_clean$date <- as.Date(cps_clean$date)
cps_clean <- cps_clean %>%
group_by(record_id) %>%
mutate(baseline_date = ifelse(redcap_event_name == "baseline_arm_1", date, NA)) %>%
fill(baseline_date, .direction = "downup") %>%
ungroup()
cps_clean <- cps_clean %>%
mutate(
days_from_baseline = case_when(
redcap_event_name != "baseline_arm_1" ~ as.integer(date - baseline_date),
TRUE ~ 0
)
)
cps_clean$years_from_baseline <- cps_clean$days_from_baseline / 365.25
# recoding the scores for pss variables
cps_clean <- cps_clean %>%
mutate(
pss_4r = 4 - pss_4,
pss_5r = 4 - pss_5,
pss_7r = 4 - pss_7,
pss_8r = 4 - pss_8
)
# calculating the scores
cps_clean <- cps_clean %>%
mutate(
zbi_total = rowSums(cbind(zbi_1, zbi_2, zbi_3, zbi_4, zbi_5, zbi_6, zbi_7, zbi_8, zbi_9, zbi_10,
zbi_11, zbi_12, zbi_13, zbi_14, zbi_15, zbi_16, zbi_17, zbi_18, zbi_19,
zbi_20, zbi_21, zbi_22), na.rm = TRUE),
pss_total = rowSums(cbind(pss_1,pss_2,pss_3,pss_4r,pss_5r,pss_6,pss_7r,pss_8r,pss_9,pss_10), na.rm = TRUE),
fam_familialobligations = rowSums(cbind(fam_1, fam_2, fam_3, fam_4, fam_5, fam_6), na.rm = TRUE),
fam_supportfromfamily = rowSums(cbind(fam_7, fam_8, fam_9), na.rm = TRUE),
fam_familyasreferents = rowSums(cbind(fam_11, fam_12, fam_13, fam_14, fam_15), na.rm = TRUE),
ss_total = rowSums(cbind(ss_1,ss_2,ss_3,ss_4,ss_5,ss_6), na.rm = TRUE),
averageecog_mem = rowMeans(cbind(ecog_mem_1, ecog_mem_2, ecog_mem_3, ecog_mem_4,
ecog_mem_5, ecog_mem_6, ecog_mem_7, ecog_mem_8), na.rm = TRUE),
averageecog_lang = rowMeans(cbind(ecog_lang_1,ecog_lang_2,ecog_lang_3,
ecog_lang_4,ecog_lang_5,ecog_lang_6,
ecog_lang_7,ecog_lang_8,ecog_lang_9), na.rm = TRUE),
averageecog_vis = rowMeans(cbind(ecog_vis_1,ecog_vis_2,ecog_vis_3,
ecog_vis_4,ecog_vis_5,ecog_vis_6,ecog_vis_7), na.rm = TRUE),
averageecog_plan = rowMeans(cbind(ecog_plan_1,ecog_plan_2,ecog_plan_3,
ecog_plan_4,ecog_plan_5), na.rm = TRUE),
averageecog_organization = rowMeans(cbind(ecog_organization_1,ecog_organization_2,
ecog_organization_3,ecog_organization_4,
ecog_organization_5,ecog_organization_6), na.rm = TRUE),
averageecog_attention = rowMeans(cbind(ecog_attention_1,ecog_attention_2,
ecog_attention_3,ecog_attention_4), na.rm = TRUE),
avgcog_total = rowMeans(cbind(ecog_mem_1,ecog_mem_2,ecog_mem_3,ecog_mem_4,
ecog_mem_5,ecog_mem_6,ecog_mem_7,ecog_mem_8,ecog_lang_1,ecog_lang_2,ecog_lang_3,
ecog_lang_4,ecog_lang_5,ecog_lang_6,ecog_lang_7,ecog_lang_8,ecog_lang_9,
ecog_vis_1,ecog_vis_2,ecog_vis_3,ecog_vis_4,ecog_vis_5,ecog_vis_6,ecog_vis_7,
ecog_plan_1,ecog_plan_2,ecog_plan_3,ecog_plan_4,ecog_plan_5,ecog_organization_1,
ecog_organization_2,ecog_organization_3,ecog_organization_4,ecog_organization_5,
ecog_organization_6,ecog_attention_1,ecog_attention_2,ecog_attention_3,
ecog_attention_4), na.rm = TRUE),
fam_familialobligations = rowSums(cbind(fam_1, fam_2, fam_3, fam_4, fam_5, fam_6), na.rm = TRUE),
fam_supportfromfamily = rowSums(cbind(fam_7,fam_8,fam_9), na.rm = TRUE),
fam_familyasreferents = rowSums(cbind(fam_11,fam_12,fam_13,fam_14,fam_15), na.rm = TRUE),
pac_total = rowSums(cbind(pac_1,pac_2,pac_3,pac_4,pac_5,pac_6,pac_7,pac_8,pac_9), na.rm = TRUE),
pac_selfaffirmation = rowSums(cbind(pac_1,pac_2,pac_3,pac_4,pac_5,pac_6), na.rm = TRUE),
pac_outlookonlife = rowSums(cbind(pac_7,pac_8,pac_9), na.rm = TRUE),
ss_total = rowSums(cbind(ss_1,ss_2,ss_3,ss_4,ss_5,ss_6), na.rm = TRUE),
)
# removing unwanted columns
cps_clean <- cps_clean %>%
select(
-cps_crid, -adc_crid, -ensta_oth, -baseline_cmplt_date, -mo8_cmplt_date,
-diffmo_base_mo8, -mo16_cmplt_date, -diffmo_mo8_mo16, -cmplt_assmnt_notes,
-cg_cr_notes, -caregiver_gender_other, -recipient_gender, -recipient_gender_other,
-ethnicity_other, -marital_status, -marital_status_other, -highest_education,
-education, -language, -language_other, -birth_place_us, -birth_place_outside_us,
-birth_place_out_us_other, -duration_living_in_us, -employment_situation,
-employment_situation_other, -carecon_otherre, -uc_davis_care_partner_study_complete,
-ensta_oth, -duration_living_resi, -move , -newaddress, -f_safe_neighborhood,
-f_neighborhood_1, -f_neighborhood_2, -carecon_ylive,
)
# save the cleaned up data on desktop
output_file_path <- "C:/Users/peima/OneDrive/Desktop/cps_clean.xlsx"
write.xlsx(cps_clean, file = output_file_path)