Diane - AEPS & ASQ-4 DATA PROCESSING

Last update: 21 March, 2021

Please notice that this report refers to birth to three only

1 load package

pacman::p_load(tidyverse, janitor)

2 Data handling

3 get all ASQ4 data

read_excel_allsheets <- function(filename) {
    sheets <- readxl::excel_sheets(filename) #get all sheet names
    x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
    names(x) <- sheets #get names only
    x #return
}
#get the excel file
excel_list <- read_excel_allsheets("C:/Users/luisf/Dropbox/ASQ4_AEPS Data for Luis 2.2021/Luis Feb 2021/Final ALL ASQ4 for AEPS 2019_2020.xlsx")

#transform into vectors
list2env(setNames(excel_list, #list
                  paste0("ds_",janitor::make_clean_names(names(excel_list)))),  #fixing different names and other patterns
         envir=.GlobalEnv) #where?

4 Create a backup

backup_asq4_demo <- ds_asq4_demo 
backup_asq4_items <- ds_asq4_items

#remove all attributes

#ds_asq4_demo[] <- lapply(ds_asq4_demo, function(x) { attributes(x) <- NULL; x })
#backup_asq4_items[] <- lapply(backup_asq4_items, function(x) { attributes(x) <- NULL; x })

5 clean names

ds_asq4_demo <- clean_names(ds_asq4_demo)
ds_asq4_items <- clean_names(ds_asq4_items)

6 merge datasets

ds_asq <- left_join(ds_asq4_demo, ds_asq4_items, by = c("asq4_id"))

7 Compute totals

7.1 Change variable computational level

ds_asq <- ds_asq %>% 
  mutate_at(vars(c1:c6, gm1:gm6, fm1:fm6, ps1:ps6, p1:p6), ~as.numeric(.))

7.2 Compute totals for each ASQ-4 domain

ds_asq<-ds_asq %>% 
  mutate(com_sum = rowSums(select(.,c1:c6))) %>% 
  mutate(gm_sum = rowSums(select(.,gm1:gm6))) %>% 
  mutate(fm_sum = rowSums(select(.,fm1:fm6))) %>% 
  mutate(ps_sum = rowSums(select(.,ps1:ps6))) %>% 
  mutate(per_sum = rowSums(select(.,p1:p6)))

8 AEPS Demographics

aeps_1_demo <- readxl::read_excel("C:/Users/luisf/Dropbox/ASQ4_AEPS Data for Luis 2.2021/Luis Feb 2021/copy KM Cleaned AEPS 3.5.21/1.Cleaned AEPSBirthToThreeFormNoN 2 Grace 9.13.2019.xlsx", sheet = 2)

#fix names
aeps_1_demo <- clean_names(aeps_1_demo)
#remove empty columns and rows
aeps_1_demo <- remove_empty(aeps_1_demo, which = c("rows", "cols"), quiet = TRUE)
#remove useless rows
aeps_1_demo <- aeps_1_demo %>% filter(!is.na(childs_id))

#add new features to merge
aeps_1_demo <- aeps_1_demo %>% 
  mutate(aeps_file_number = 1) %>% #same as ds_asq
  mutate(spread_sheet = spread_sheet_id_1) %>% #same as full dataset!
  mutate(aeps_sprdsheet_id_number = spread_sheet_id_1) #same as ds_asq

9 1.AEPSBirthToThreeFormNoN 2 Grace 9.13.2019

10 First file 1 - Fine motor (1)

10.1 get data

aeps_1_fine <- readxl::read_excel("C:/Users/luisf/Dropbox/ASQ4_AEPS Data for Luis 2.2021/Luis Feb 2021/copy KM Cleaned AEPS 3.5.21/1.Cleaned AEPSBirthToThreeFormNoN 2 Grace 9.13.2019.xlsx", sheet = 3)

#clear excel attributes
aeps_1_fine[] <- lapply(aeps_1_fine, function(x) { attributes(x) <- NULL; x })
#fix names
aeps_1_fine <- clean_names(aeps_1_fine)
#remove empty columns and rows
aeps_1_fine <- remove_empty(aeps_1_fine, which = c("rows", "cols"), quiet = TRUE)
#create a skill
aeps_1_fine <- aeps_1_fine %>% 
  mutate(skill = .[[1]]) %>% #this is the first column )in this case -- fine motor (sub)domain
  select(1,skill, everything())
#With this new variable (skill), just keep if is a letter
aeps_1_fine <- aeps_1_fine %>% 
  mutate(skill = if_else(str_detect(skill, "[a-z]"), .[[1]],  NA_character_)) %>% #if skill is a letter, otherwise missing
  fill(skill)
#remove first line (it's almost all na)
aeps_1_fine <- aeps_1_fine %>% 
  filter(!str_detect(.[[1]], "[a-z]"))
#transform to numeric
aeps_1_fine <- aeps_1_fine %>% mutate(!! names(.)[1] := as.numeric(!! rlang::sym(names(.)[1]))) 
#aeps_1_fine[[1]] <- as.numeric(aeps_1_fine[[1]])

10.2 Tranform it to long format

spec <- tibble(`.name` = names(aeps_1_fine)) %>%
  slice(-c(1:2)) %>%
  mutate(`.value` = case_when(
           `.name` %>% str_detect("spread") ~ "spread_sheet",
           `.name` %>% str_detect("score")  ~ "score",
           `.name` %>% str_detect("confirm") ~ "confirm")
  )
# apply the spec
aeps_1_fine_long <- aeps_1_fine %>%
  pivot_longer_spec(spec)

10.3 Add domain

This chunk will get all domains and number skills. It will be useful to merge all ds in the future.

aeps_1_fine_long <- aeps_1_fine_long %>% 
  mutate(domain = names(.)[[1]]) %>% 
  rename(number_skill = names(.)[[1]]) %>% 
  select(domain, number_skill, everything())

11 First file 1 - Gross motor (2)

11.1 get data

aeps_1_gross <- readxl::read_excel("C:/Users/luisf/Dropbox/ASQ4_AEPS Data for Luis 2.2021/Luis Feb 2021/copy KM Cleaned AEPS 3.5.21/1.Cleaned AEPSBirthToThreeFormNoN 2 Grace 9.13.2019.xlsx", sheet = 4)

#clear excel attributes
aeps_1_gross[] <- lapply(aeps_1_gross, function(x) { attributes(x) <- NULL; x })
#fix names
aeps_1_gross <- clean_names(aeps_1_gross)
#remove empty columns and rows
aeps_1_gross <- remove_empty(aeps_1_gross, which = c("rows", "cols"), quiet = TRUE)
#create a skill
aeps_1_gross <- aeps_1_gross %>% 
  mutate(skill = .[[1]]) %>% #this is the first column )in this case -- fine motor (sub)domain
  select(1,skill, everything())
#With this new variable (skill), just keep if is a letter
aeps_1_gross <- aeps_1_gross %>% 
  mutate(skill = if_else(str_detect(skill, "[a-z]"), .[[1]],  NA_character_)) %>% #if skill is a letter, otherwise missing
  fill(skill)
#remove first line (it's almost all na)
aeps_1_gross <- aeps_1_gross %>% 
  filter(!str_detect(.[[1]], "[a-z]"))
#transform to numeric
aeps_1_gross <- aeps_1_gross %>% mutate(!! names(.)[1] := as.numeric(!! rlang::sym(names(.)[1]))) 
#aeps_1_gross[[1]] <- as.numeric(aeps_1_gross[[1]])

11.2 Tranform it to long format

spec <- tibble(`.name` = names(aeps_1_gross)) %>%
  slice(-c(1:2)) %>%
  mutate(`.value` = case_when(
    `.name` %>% str_detect("spread") ~ "spread_sheet",
    `.name` %>% str_detect("score")  ~ "score",
    `.name` %>% str_detect("confirm") ~ "confirm")
  )
# apply the spec
aeps_1_gross_long <- aeps_1_gross %>%
  pivot_longer_spec(spec)

11.3 Add domain

This chunk will get all domains and number skills. It will be useful to merge all ds in the future.

aeps_1_gross_long <- aeps_1_gross_long %>% 
  mutate(domain = names(.)[[1]]) %>% 
  rename(number_skill = names(.)[[1]]) %>% 
  select(domain, number_skill, everything())

12 First file 1 - Adaptive (3)

12.1 get data

aeps_1_adaptive <- readxl::read_excel("C:/Users/luisf/Dropbox/ASQ4_AEPS Data for Luis 2.2021/Luis Feb 2021/copy KM Cleaned AEPS 3.5.21/1.Cleaned AEPSBirthToThreeFormNoN 2 Grace 9.13.2019.xlsx", sheet = 5)

#clear excel attributes
aeps_1_adaptive[] <- lapply(aeps_1_adaptive, function(x) { attributes(x) <- NULL; x })
#fix names
aeps_1_adaptive <- clean_names(aeps_1_adaptive)
#remove empty columns and rows
aeps_1_adaptive <- remove_empty(aeps_1_adaptive, which = c("rows", "cols"), quiet = TRUE)
#create a skill
aeps_1_adaptive <- aeps_1_adaptive %>% 
  mutate(skill = .[[1]]) %>% #this is the first column )in this case -- fine motor (sub)domain
  select(1,skill, everything())
#With this new variable (skill), just keep if is a letter
aeps_1_adaptive <- aeps_1_adaptive %>% 
  mutate(skill = if_else(str_detect(skill, "[a-z]"), .[[1]],  NA_character_)) %>% #if skill is a letter, otherwise missing
  fill(skill)
#remove first line (it's almost all na)
aeps_1_adaptive <- aeps_1_adaptive %>% 
  filter(!str_detect(.[[1]], "[a-z]"))
#transform to numeric
aeps_1_adaptive <- aeps_1_adaptive %>% mutate(!! names(.)[1] := as.numeric(!! rlang::sym(names(.)[1]))) 
#aeps_1_adaptive[[1]] <- as.numeric(aeps_1_adaptive[[1]])

12.2 Tranform it to long format

spec <- tibble(`.name` = names(aeps_1_adaptive)) %>%
  slice(-c(1:2)) %>%
  mutate(`.value` = case_when(
    `.name` %>% str_detect("spread") ~ "spread_sheet",
    `.name` %>% str_detect("score")  ~ "score",
    `.name` %>% str_detect("confirm") ~ "confirm")
  )
# apply the spec
aeps_1_adaptive_long <- aeps_1_adaptive %>%
  pivot_longer_spec(spec)

12.3 Add domain

This chunk will get all domains and number skills. It will be useful to merge all ds in the future.

aeps_1_adaptive_long <- aeps_1_adaptive_long %>% 
  mutate(domain = names(.)[[1]]) %>% 
  rename(number_skill = names(.)[[1]]) %>% 
  select(domain, number_skill, everything())

13 Merge AEPS spreadsheets datasets

Just checking if we have 22 children in each dataset

aeps_1_fine_long %>% count(spread_sheet)

aeps_1_gross_long %>% count(spread_sheet)

aeps_1_adaptive_long %>% count(spread_sheet)

I’ll use bind_rows to put each dataset on top of the another. First, fine long with gross long

ds_aeps_birth_three <- bind_rows(
  aeps_1_fine_long,
  aeps_1_gross_long)

Now, this resultant ds with adaptive

ds_aeps_birth_three <- bind_rows(
  ds_aeps_birth_three,
  aeps_1_adaptive_long)

14 Merge AEPS with AEPS demographics

I’ll get aeps_1_demo to add to this partially full dataset the child’ ID

ds_aeps_birth_three <- left_join(ds_aeps_birth_three, aeps_1_demo)

First, i’ll add two key variables present in the ASQ-4 dataset to guarantee the merging will be correct

ds_aeps_birth_three <- ds_aeps_birth_three %>% 
  mutate(aeps_file_number = 1) %>% 
  mutate(aeps_sprdsheet_id_number = spread_sheet) %>% 
  rename(asq4_id = childs_id) %>% 
  mutate(asq4_id = as.numeric(asq4_id)) #in ASQ4 dataset, this variable is numeric

Create a full dataset with ASQ-4 and AEPS

ds_birth_three_aeps_asq <- left_join(
  ds_aeps_birth_three,
  ds_asq,
  by = "asq4_id"
)

14.1 Manual check

ds_birth_three_aeps_asq %>% 
  filter(spread_sheet == "15") %>% View()

14.2 Save as excel

write.csv(ds_birth_three_aeps_asq, file = "ds_birth_three_aeps_asq.csv", row.names = F)

It seems everything worked! (Saturday, 13 March, 2021)
Ask Kimberly