######### # NOTES # ######### # This program file demonstrates strategies discussed in # session 2 of the 2026 NDACAN Summer Training Series # "Data Cleaning and Management." # For questions, contact the presenter # Alex Roehrkasse (aroehrkasse@butler.edu). # Note that because of the process used to anonymize data, # all unique observations include partially fabricated data # that prevent the identification of respondents. # As a result, all descriptive and model-based results are fabricated. # Results from this and all NDACAN presentations are for training purposes only # and should never be understood or cited as analysis of NDACAN data. ##################### # TABLE OF CONTENTS # ##################### # 0. SETUP # 1. EXAMINING YOUR DATA # 2. CLEANING YOUR DATA # 3. CODING PROGRAMMATICALLY # 4. SAVING YOUR DATA ############ # 0. SETUP # ############ ## SETTING UP THE ENVIRONMENT ## # Let's clear the environment rm(list=ls()) # Pacman installs packages if necessary, otherwise loading them. if (!requireNamespace("pacman", quietly = TRUE)){ install.packages("pacman") } pacman::p_load(data.table, tidyverse) # Let's define some filepaths (note the organization of project and data folders) project <- 'C:/Users/aroehrkasse/Box/Presentations/-NDACAN/2026_summer_series/' data <- 'C:/Users/aroehrkasse/Box/NDACAN/2026_summer_series/' # And set one as the working directory. setwd(project) # Always set a seed to allow for reproduction of random processes. set.seed(1013) ## READING DATA ## # Let's read anonymized versions of the # NCANDS Child File for 2020-2024 n20 <- fread(paste0(data,'CF2020v4_ANON.tab')) n21 <- fread(paste0(data,'CF2021v3_ANON.tab')) n22 <- fread(paste0(data,'CF2022v2_ANON.tab')) n23 <- fread(paste0(data,'CF2023v2_ANON.tab')) n24 <- fread(paste0(data,'CF2024v2_ANON.tab')) # For the purposes of the presentation, # note that I have sampled New England # and selected a small number of key variables. # We'll focus on NCANDS 2023 for now. ########################## # 1. EXAMINING YOUR DATA # ########################## # Most NDACAN data files are too large for spreadsheet viewing to be helpful. dim(n23) # There are several helpful ways to view snippets of the data. # Subsetting tells R to print only the cells corresponding to certain rows, columns. n23[1:5,1] n23[1:5,c(1:2,6)] # head() returns the first five rows of all columns. head(n23) # head() can nicely be combined with select(). # Note that here we introduce the pipe operator '|>' (FKA '%>%'). # The pipe takes the preceding element # as the first input of the following function. # It's like saying, "and to that, now do this." n23 |> head() |> select(SubYr, StaTerr, RptDisp) # So it's equivalent to typing: select(head(n23), SubYr, StaTerr, RptDisp) # To get an overview, it can sometimes be helpful to view a random sample # of the data rather than a block of data. n23 |> slice_sample(prop = .0001) |> select(SubYr, StaTerr, RptDisp) ######################### # 2. CLEANING YOUR DATA # ######################### ## PREPARING ## # Most NDACAN datasets are large. Before cleaning them, it can be helpful # to choose only those variables of interest. While I've already # done this, let's do it some more: n23c <- n23 |> select(SubYr, StaTerr, RptID, ChID, RptDt, RptDisp, RpDispDt, ChPrior, FcMoney, Per1Rel, StFCID) # It's important to understand that data will not always be coded # exactly in the manner they're described in the Code Book. n23c |> count(FcMoney) # Note that count() is just a special case of summarize() # in which you group our data # according to the values of a variable, and then # count the number of rows in each group, # i.e. having each value. n23c |> group_by(FcMoney) |> summarize(n = n()) # For this reason, it's VERY important to inspect the values of # EVERY variable you're interested in working with. n23c %>% select(SubYr, RptDisp, ChPrior, FcMoney, Per1Rel) %>% pivot_longer(cols = everything(), names_to = "column_name", values_to = "value") %>% count(column_name, value) |> view() ## RECODING ## # Let's now recode variables how we want them str(n23c) n23c_test1 <- n23c |> mutate(RptDisp = if_else(RptDisp == 99, NA_integer_, RptDisp), RptDisp = factor(RptDisp, levels = c(1:7,88), labels = c('Substantiated', 'Indicated/reason to suspect', 'Alt. response, victim', 'Alt. response, nonvictim', 'Unsubstantiated', 'Unsubstantiated, false report', 'Closed, no finding', 'Other')), ChPrior = factor(ChPrior, levels = c(1,2), labels = c('Yes', 'No')), FcMoney = if_else(FcMoney == 9, NA_integer_, FcMoney), FcMoney = factor(FcMoney, levels = c(1,2), labels = c('Yes', 'No')), Per1Rel_new = case_when(Per1Rel == 1 ~ 'Parent', Per1Rel == 2 ~ 'Other relative, non-foster', Per1Rel == 7 ~ 'Unmarried partner of parent', Per1Rel %in% c(3,4,33) ~ 'Foster parent', Per1Rel %in% c(5,6,9) ~ 'Professional', Per1Rel == 8 ~ 'Legal guardian', Per1Rel == 10 ~ 'Friend/neighbor', Per1Rel == 88 ~ 'Other', Per1Rel == 99 ~ NA_character_)) head(n23c_test1) ############################## # 3. CODING PROGRAMMATICALLY # ############################## # Given the complexity here, we can easily make mistakes. # Let's try to recode a variables a little more "programmatically." # First, let's recode together multiple *variables* # that have similar encodings. n23c_test2 <- n23c |> mutate(across(c(ChPrior, FcMoney), ~ if_else(.x == 9, NA_integer_, .x)), across(c(ChPrior, FcMoney), ~ factor(.x, levels = c(1,2), labels = c('Yes', 'No')))) n23c |> count(ChPrior, FcMoney) n23c_test2 |> count(ChPrior, FcMoney) # Now, let's recode multiple *years* of NCANDS data at the same time. # First, make a list of NCANDS Child File dataframes. nlist <- list(n20 = n20, n21 = n21, n22 = n22, n23 = n23, n24 = n24) # Note that the variable names in each year don't match. names(n21) == names(n22) names(n22) == names(n23) names(n22) names(n23) # Now let's write a program, or function, # that we can apply to this list. nclean <- function(df) { df |> rename_with(tolower) |> # rename all columns as all lowercase select(subyr, staterr, stfcid, afcarsid, rptid, chid, rptdt, rptdisp, rpdispdt, chprior, fcmoney, per1rel) |> mutate(rptdisp = if_else(rptdisp == 99, NA_integer_, rptdisp), rptdisp = factor(rptdisp, levels = c(1:7,88), labels = c('Substantiated', 'Indicated/reason to suspect', 'Alt. response, victim', 'Alt. response, nonvictim', 'Unsubstantiated', 'Unsubstantiated, false report', 'Closed, no finding', 'Other')), across(c(chprior, fcmoney), ~ if_else(.x == 9, NA_integer_,.x)), across(c(chprior, fcmoney), ~ factor(.x, levels = c(1,2), labels = c('Yes', 'No'))), per1rel_new = case_when(per1rel == 1 ~ 'Parent', per1rel == 2 ~ 'Other relative, non-foster', per1rel == 7 ~ 'Unmarried partner of parent', per1rel %in% c(3,4,33) ~ 'Foster parent', per1rel %in% c(5,6,9) ~ 'Professional', per1rel == 8 ~ 'Legal guardian', per1rel == 10 ~ 'Friend/neighbor', per1rel == 88 ~ 'Other', per1rel == 99 ~ NA_character_)) } # And now list-apply the function to our list of data frames. nlistc <- lapply(nlist, nclean) # And now we can even stack the cleaned data frames, # converting a list into a single data frame. nc <- list_rbind(nlistc) slice_sample(nc, prop = .00001) ####################### # 4. SAVING YOUR DATA # ####################### # Lastly, we should save our cleaned data. # Saving as a CSV is common, but erases much of the encoding. fwrite(nc, paste0(data,'ncands_clean.csv')) # R's native data format will preserve everything. write_rds(nc, paste0(data,'ncands_clean.rds'))