R version 4.5.2 (2025-10-31 ucrt) -- "[Not] Part in a Rumble" Copyright (C) 2025 The R Foundation for Statistical Computing Platform: x86_64-w64-mingw32/x64 R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details. Natural language support but running in an English locale R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications. Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R. > # 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) > # 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')) > # Most NDACAN data files are too large for spreadsheet viewing to be helpful. > dim(n23) [1] 146294 12 > # Subsetting tells R to print only the cells corresponding to certain rows, columns. > n23[1:5,1] SubYr 1: 2023 2: 2023 3: 2023 4: 2023 5: 2023 > n23[1:5,c(1:2,6)] SubYr StaTerr RptDisp 1: 2023 MA 1 2: 2023 MA 5 3: 2023 MA 1 4: 2023 MA 5 5: 2023 MA 5 > # head() returns the first five rows of all columns. > head(n23) SubYr StaTerr RptID ChID RptDt RptDisp RpDispDt ChPrior 1: 2023 MA 1000061B2B5C 1000061B8C18 2023-05-23 1 2023-07-07 2 2: 2023 MA 1000061B2B5F 100005D4F9F1 2023-08-08 5 2023-09-21 2 3: 2023 MA 1000061B2B60 1000061B3B5D 2023-04-23 1 2023-06-08 2 4: 2023 MA 1000061B2B61 1000061B623E 2023-01-23 5 2023-04-12 2 5: 2023 MA 1000061B2B62 1000061B4A6B 2022-10-08 5 2022-10-18 2 6: 2023 MA 1000061B2B64 100000A42A27 2022-10-23 5 2023-03-26 1 FcMoney Per1Rel AFCARSID StFCID 1: 9 8 101110059623 MA101110059623 2: 9 NA 901109501999 MA901109501999 3: 9 1 101110021883 MA101110021883 4: 9 NA 601109954834 MA601109954834 5: 9 NA 1109874768 MA001109874768 6: 9 NA 701107214629 MA701107214629 > # 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) SubYr StaTerr RptDisp 1: 2023 MA 1 2: 2023 MA 5 3: 2023 MA 1 4: 2023 MA 5 5: 2023 MA 5 6: 2023 MA 5 > # So it's equivalent to typing: > select(head(n23), SubYr, StaTerr, RptDisp) SubYr StaTerr RptDisp 1: 2023 MA 1 2: 2023 MA 5 3: 2023 MA 1 4: 2023 MA 5 5: 2023 MA 5 6: 2023 MA 5 > # 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) SubYr StaTerr RptDisp 1: 2023 CT 5 2: 2023 CT 88 3: 2023 ME 7 4: 2023 CT 1 5: 2023 CT 1 6: 2023 NH 5 7: 2023 ME 5 8: 2023 CT 1 9: 2023 CT 5 10: 2023 CT 5 11: 2023 CT 5 12: 2023 CT 5 13: 2023 CT 1 14: 2023 CT 1 > # 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) FcMoney n 1: 1 732 2: 2 5646 3: 9 20491 4: NA 119425 > # 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()) # A tibble: 4 × 2 FcMoney n 1 1 732 2 2 5646 3 9 20491 4 NA 119425 > # 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() > # Let's now recode variables how we want them > str(n23c) Classes ‘data.table’ and 'data.frame': 146294 obs. of 11 variables: $ SubYr : int 2023 2023 2023 2023 2023 2023 2023 2023 2023 2023 ... $ StaTerr : chr "MA" "MA" "MA" "MA" ... $ RptID : chr "1000061B2B5C" "1000061B2B5F" "1000061B2B60" "1000061B2B61" ... $ ChID : chr "1000061B8C18" "100005D4F9F1" "1000061B3B5D" "1000061B623E" ... $ RptDt : IDate, format: "2023-05-23" "2023-08-08" ... $ RptDisp : int 1 5 1 5 5 5 1 1 1 5 ... $ RpDispDt: IDate, format: "2023-07-07" "2023-09-21" ... $ ChPrior : int 2 2 2 2 2 1 2 2 2 2 ... $ FcMoney : int 9 9 9 9 9 9 9 9 9 9 ... $ Per1Rel : int 8 NA 1 NA NA NA 1 1 1 NA ... $ StFCID : chr "MA101110059623" "MA901109501999" "MA101110021883" "MA601109954834" ... - attr(*, ".internal.selfref")= > 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) SubYr StaTerr RptID ChID RptDt RptDisp RpDispDt 1: 2023 MA 1000061B2B5C 1000061B8C18 2023-05-23 Substantiated 2023-07-07 2: 2023 MA 1000061B2B5F 100005D4F9F1 2023-08-08 Unsubstantiated 2023-09-21 3: 2023 MA 1000061B2B60 1000061B3B5D 2023-04-23 Substantiated 2023-06-08 4: 2023 MA 1000061B2B61 1000061B623E 2023-01-23 Unsubstantiated 2023-04-12 5: 2023 MA 1000061B2B62 1000061B4A6B 2022-10-08 Unsubstantiated 2022-10-18 6: 2023 MA 1000061B2B64 100000A42A27 2022-10-23 Unsubstantiated 2023-03-26 ChPrior FcMoney Per1Rel StFCID Per1Rel_new 1: No 8 MA101110059623 Legal guardian 2: No NA MA901109501999 3: No 1 MA101110021883 Parent 4: No NA MA601109954834 5: No NA MA001109874768 6: Yes NA MA701107214629 > # 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) ChPrior FcMoney n 1: 1 1 319 2: 1 2 1590 3: 1 9 6275 4: 1 NA 44092 5: 2 1 413 6: 2 2 4056 7: 2 9 14216 8: 2 NA 75333 > n23c_test2 |> + count(ChPrior, FcMoney) ChPrior FcMoney n 1: Yes Yes 319 2: Yes No 1590 3: Yes 50367 4: No Yes 413 5: No No 4056 6: No 89549 > # 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) [1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE > names(n22) == names(n23) [1] FALSE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE > names(n22) [1] "subyr" "StaTerr" "RptID" "ChID" "RptDt" "RptDisp" "RpDispDt" [8] "chprior" "fcmoney" "per1rel" "AFCARSID" "StFCID" > names(n23) [1] "SubYr" "StaTerr" "RptID" "ChID" "RptDt" "RptDisp" "RpDispDt" [8] "ChPrior" "FcMoney" "Per1Rel" "AFCARSID" "StFCID" > # 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) subyr staterr stfcid afcarsid rptid chid rptdt 1: 2023 CT CT000498444007 498444007 100005F4BC0A 100005B41803 2023-03-08 2: 2023 CT CT000498808273 498808273 100005F40626 100005F40A82 2023-03-08 3: 2023 MA MA401109971692 401109971692 1000061B37E2 1000061B7C6E 2023-02-08 4: 2023 NH NH000008637889 8637889 100005F7E731 100005C41DC3 2022-11-08 5: 2023 CT CT000498996404 498996404 100005F3AED4 1000046DAAF5 2022-11-08 6: 2021 MA MA001100488366 1100488366 100005892E6D 1000009F4ED9 2020-12-23 7: 2020 CT CT000492921697 492921697 1000053A7E99 100001904829 2019-11-23 rptdisp rpdispdt chprior fcmoney per1rel per1rel_new 1: Substantiated 2023-03-21 No 1 Parent 2: Unsubstantiated 2023-05-02 No NA 3: Substantiated 2023-03-27 No 88 Other 4: Unsubstantiated 2022-12-12 No NA 5: Unsubstantiated 2022-11-30 Yes NA 6: Unsubstantiated 2021-02-04 Yes No NA 7: Unsubstantiated 2019-12-19 No NA > # 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'))