Transcript for 2026 Summer Training Series Session 2: Data Cleaning and Management Presenter: Alexander F. Roehrkasse, Ph.D., Butler University National Data Archive on Child Abuse and Neglect (NDACAN) [MUSIC] [VOICEOVER] National Data Archive on Child Abuse and Neglect. [ONSCREEN CONTENT SLIDE 1] Welcome to the 2026 NDACAN Summer training series! The session will begin at 12pm EST. This session is being recorded. Please submit questions to the Q&A box! [Alyssa Lindsey] All right. Hello, everyone. Welcome to our 2026 NDACAN Summer Training Series. My name is Alyssa Lindsay. I'm the Graduate Research Associate here at NDACAN. Before I kick off our time together, I just want to give a couple of housekeeping items. We're recording this session so that we can post the recording, the transcripts, the slides, and any related materials on our website several weeks after each presentation. And I'll announce their availability on the Child Maltreatment Research L, or CMRL, listserv, and I'll put the information on how to join that listserv in the chat. Throughout the session, we have, a Q&A box at the bottom right of the screen, so as we're going through, feel free to leave your questions in that Q&A box, and at the end of the presentation, we'll go through each question as they come in. Next slide, please. [ONSCREEN CONTENT SLIDE 2] NDACAN Summer Training series. National Data Archive on Child Abuse and Neglect Duke University, Cornell University, UC San Francisco, & Mathematica [Alyssa Lindsey] Great, so welcome again to the NDACAN Summer Training Series. NDACAN stands for the National Data Archive on Child Abuse and Neglect, and it's housed at Duke University, Cornell University, UC San Francisco, and Mathematica. Next slide. [ONSCREEN CONTENT SLIDE 3] Laying the groundwork foundational skills for using administrative data in child welfare research. Logo for the Children's Bureau features an image of overlapping blue and white silhouettes of children next to red and white stripes. To the right is the text "Children's Bureau: An Office of the Administration for Children & Families." Logo image which features a semicircle of icons representing people holding hands positioned on top of the acronym NDACAN, and next to the text National Data Archive Child Abuse and Neglect. [Alyssa Lindsey] NDACAN does two learning offerings every year. We have our monthly office hour series during the academic year. And in the summertime, we have a Summer Training Series. The theme of this year, 2026 Summer Training Series, is laying the groundwork foundational skills for using administrative data in child welfare research. This series is designed to strengthen the foundational skills necessary to conduct high-quality, impactful research with administrative data. And we're intending this to be useful both for folks who are new to this area and are seeking to strengthen existing skill sets. So throughout these summer presentations, we're aiming for each session to be a useful and thorough starting point for conducting research on child welfare, using administrative data. Next slide. [ONSCREEN CONTENT SLIDE 4] NDACAN Summer Training series schedule. July 1st: Overview of NDACAN administrative datasets July 8th: Data cleaning and management July 15th: Linking NCANDS and AFCARS July 22nd: Handling missing data July 29th: Data presentation and visualization [Alyssa Lindsey] This is just a preview of our schedule for the 2026 Summer Training Series. We'll be meeting every Wednesday at 12 Eastern for an hour. And these are the topics we'll cover. Last week, we kicked things off with an overview of NDACAN administrative data sets. The materials from that session will be available soon, and we'll announce that on the listserv. Today's session will cover data cleaning and management, and the following sessions will be over linking NCANDS and AFCARS, handling missing data, and will end with a presentation on data presentation and visualization. Next slide. [ONSCREEN CONTENT SLIDE 5] Session Agenda. Data cleaning and management Demonstration in R Q & A [Alyssa Lindsey] So again, today's session is going to start with some general advice and overview on data cleaning and management, and then a brief demonstration in R, followed by a question and answer session. Next slide. [ONSCREEN CONTENT SLIDE 6] DATA CLEANING AND MANAGEMENT. [Alyssa Lindsey] So now I'll pass it over to Alex to lead the rest of today's discussion. Thanks. [Alex Roehrkasse] Thanks, Lisa. My name is Alex Roehrkasse. I'm a research associate at the archive and an Assistant Professor Of Sociology And Criminology at Butler University. I used to be a postdoctoral associate at the archive, so I've been doing work with the archive for six or seven years now, and I really enjoy this part of my work. Giving these presentations is also one of my favorite things to do. So today, we're talking about data cleaning and management. This is a broad topic and involves a lot of core skills, foundational skills that we're going to be trying to build this summer. So I want to acknowledge upfront that we're not going to be able to cover everything. The presentation today is going to be a little more focused on some key skills and key strategies that I have found useful both as a data user and a data consultant. in working with archive data, particularly our administrative data sets. Some of the materials and topics that I cover in my slide deck today, we will do demonstrations of next week in the session on linking data, where those skills are especially relevant to data linking. Also, I want to note that some of the material today may feel a little bit slow to some of you, some of it may feel quite fast to others. That's partly because we're trying to pitch this presentation to an audience with multiple and diverse skill sets. So I just want to remind you that both the slides and the code for today's presentation will be available on our website. So if you feel yourself falling behind or getting a little bit lost, rest assured you can circle back to the materials that support today's presentation and study them a little more carefully in a few weeks. As always, we're available to help, so don't hesitate if you have questions today, please, as Alyssa said, drop them in the chat. If questions strike you 2 hours, 2 days, or 2 years from now, don't hesitate to shoot us an email. We're always happy to talk. Okay, with that, let's get started. [ONSCREEN CONTENT SLIDE 7] General advice. Use what’s available to you. NDACAN’s site is rich with resources for support. Don’t hesitate to ask for help. NDACAN staff are here to support all aspects of NDACAN-related projects. Verify and master all AI-supported work. Non-human support can be generative, but is unreliable. [Alex Roehrkasse] I want to start today's presentation with some general advice. First, Use what's available to you. You may have lots of support at your institution or your workplace, you may have very little. Whatever resources you have outside of the archive, I want you to know that the archive is really a rich resource for you, particularly our website has a number of different helpful resources, including past versions of this very training, where you can go to access key information that can support your research using archive data. So if you haven't explored our website, I strongly encourage you to explore it. Second, as I've kind of already said, please don't hesitate to ask for help. NDACAN staff and research associates like myself are really here to support all aspects of research using data that the archive distributes. There's real value in struggling yourself with critical research questions and challenges. But if you find yourself beating your head against the wall for a week or two weeks, please shoot us an email. We're always happy to talk about challenges and strategies for solving them. Lastly, at risk of stating the obvious, artificial intelligence is becoming an increasingly powerful tool that has potential to support research involving archived data. I myself am exploring ways that AI can support my own research. But I want to be really clear. It's very important that you develop the skills, the experience, and the discernment, so that AI can be a complement to your research rather than substitute critical functions in the research process. [ONSCREEN CONTENT SLIDE 8] Data management as crisis management. Diagram illustrating common pitfalls of data management on the left-hand side ("you'll make mistakes," "you won't remember," and "they can't read your mind") and solutions to these pitfalls on the right-hand side ("save everything, and often," "never work from the console," "annotate code liberally," and "keep a research journal"). Arrows link pitfalls to respective solutions. [Alex Roehrkasse] I like to think of data management as crisis management. We're trying to optimize a lot of things, but we're also trying to avoid disasters. What are the kinds of disasters that can arise, and what are the kinds of things we can do to manage that risk? One thing is that you're going to make mistakes. Everyone makes mistakes, no matter how skillful or experienced you are. Some of those mistakes will be small, some of them will be big. One of the best things you can do to protect against mistakes is to save and back up your work often. For example, when I'm coding, I save a new program file every single day that I'm doing meaningful work on that code. What that means is that if I realize a day later or two days later or sometimes even a year later that I made a mistake, I made a bad decision. I can always go back and find my code before I made that bad decision. I can't tell you how often it's been helpful to have archived code that's clearly dated and organized in a clear way. Also, you won't remember. You think you will, but you won't, whether it's minutes later or hours later, or years later. Some research projects take a very long time, even though it feels like when you're making a critical decision you're very sure you'll remember making that decision, I promise you you won't remember all of those decisions. This is another reason why saving and backing things up is important. As I'll explain when we get into our demonstration in R, it's also a good reason never to code from the console. As you'll see, we can tell statistical programs like R or Stata to do things ad hoc. We can type in commands and have them execute them on the fly. I strongly recommend against doing this. Ever. You'll always instead want to write a program file, which is a file you save on your computer, and only ever to execute functions or ask a statistical program to execute a function that you've written down. This way you'll always know what you told your computer to do. You'll also see in the demonstration that I like to annotate my code liberally. So when I'm coding, I'm also writing notes to myself in that code that explains what I'm doing and why I'm doing it. Lastly, I like to keep a research journal for every research project that I'm working on. I make an entry into it at least once a day, and it's a place where I write down decisions I've made, questions I have, challenges that arise, to-do lists. Sometimes these research journals have gotten 40, 50, 80, 100 pages long. And again, I can't tell you how often it's been helpful to go back to those research journals to understand critical decisions I've made. Lastly, insofar as we're participating in a scientific community, a cumulative progress, a cumulative process of discovery, it's important that other people can understand what we've done and why we've done it. Other people can't read your mind, now or in the future. This is another good reason to annotate code liberally, so that when you publish a replication package, people can't just run it, they can understand your code. Keeping a research journal is also important here. I get a number of questions from other scholars about my research, and I frequently consult my research journals so that I can have dialogue with other researchers about my research. [ONSCREEN CONTENT SLIDE 9] File organization and workflow. Project (local machine with backup to cloud server) Programs (R scripts) Drafts Figures Tables (deidentified data) Data (FedRAMP-authorized cloud server, encrypted external drive) Raw Derived (micro-data) [Alex Roehrkasse] Lastly, I want to suggest that file organization and workflow is a pretty critical part of doing effective research, using large administrative data sets, particularly data sets that include sensitive information with restrictions. I like to have two basic folders that I use whenever I'm doing a research project involving restricted administrative data. I have a project folder that's usually on my local machine, but that project folder is backed up regularly to a cloud server. In that project folder, I have a programs folder where I'm saving each of those daily program files. And then I have a separate subfolder that includes all of my output, figures I make, or tables I make. as long as those tables only include de-identified data. That is to say, summary statistics, any information that's not going to identify any of the respondents in my data. I then have a separate data folder. This is where I keep my raw data, the files that are distributed directly by the archive, and also any derived data, any data that I've manipulated, but which still includes micro data or data that could be identified. Now, it's very important that this folder, wherever you place it follows the stipulations of NDACAN data use agreements. At present, most data use agreements allow either for a FedRAMP-authorized cloud server or an encrypted external drive. Whichever works best for you is totally fine, but it's essential that you make sure that any identifiable data resides in one of those secure places. You'll see this workflow, this file organization demonstrated in the second half of the presentation today. [ONSCREEN CONTENT SLIDE 10] EXAMINING YOUR DATA. What is the structure of your data? What are the columns? What are the rows? What viewpoint on your data do you need to understand it? E.g. selecting columns, filtering rows, or summarizing values [Alex Roehrkasse] Okay, so this brings us to the actual data cleaning and management. The very first step in this process is trying to understand your data, trying to get a glimpse of it, trying to examine it. This involves asking questions like, what's the basic structure of your data? What are the columns in your data set? What are the rows? Most NDACAN administrative datasets are really, really large. They're much too big to just open in Excel. And so we often need to think creatively about what viewpoint onto our data do we need in order to best understand it. How can we pull out certain columns, or filter out certain rows, or summarize certain values in order to understand the structure of our data, the contents of our data set? [ONSCREEN CONTENT SLIDE 11] Cleaning your data. How are variables formatted? String, numeric, factor, etc. How are values coded? Codes will not always correspond to the code book: always verify. What formatting/encoding captures the relevant information most effectively? This depends not only on the data, but also on your analytic goals. [Alex Roehrkasse] Once we've examined our data and have some sense of what it actually looks like, what it actually contains, then we usually set about trying to clean it up. We do our best to make sure that the data we distribute are pretty clean, but there's always some issues. And besides, whatever clean data means to you depends a little bit on what your research goals are. Cleaning your data requires you to ask questions like, how are variables formatTed? There's different kinds of variables, depending on the kinds of values that they contain. String or character variables, for example, usually contain alphanumeric text. Numeric variables or integer variables include only numbers. Factor variables are categorical variables that have some ordering structure to them. There's many other kinds of variables or formats of variable, and it's important to understand how your variables are formatted. Second, how are values coded? This is what we most often use a codebook for. We refer to the codebook to tell us how any given variable is encoded. But it's important, and as I'll demonstrate later, the codes in the actual data sets that we distribute don't always correspond to the way they're described in the codebook. For this reason it's essential that you always verify the encoding of every variable that you're interested in using in your analysis. Lastly, and arguably most importantly, what formatting or encoding do you want? What formatting or encoding would best capture the relevant information that you need to do your analysis? Obviously, this is limited by the data itself, but it also depends on your analytic goals. [ONSCREEN CONTENT SLIDE 12] Defining your unit of analysis. Different NDACAN administrative datasets have different default units of analysis NCANDS: child-report AFCARS: child-year NYTD: child-wave Your research question may require you to tidy your data to capture your unit of analysis “How many children experienced a maltreatment investigation in each state annually?” In NCANDS, drop records from subsequent reports experienced by children within the same year, then count records by state and year. “How many maltreatment investigations occurred each half-month?” In NCANDS, drop records from additional children covered in each investigation, then count records by half-month. [Alex Roehrkasse] Another very basic thing that some users overlook, but which is really pretty critical to the early stages of your research process, is to think carefully about your unit of analysis. Your unit of analysis will depend on your research question, but even different research questions can admit different units of analysis. What's important to understand is that sort of by default, all of the administrative data sets that we distribute have more or less a default unit of analysis. That corresponds to whatever a row represents in that data set. So, for example, if you were to download the NCANDS child file. Each row in those files would correspond to a child-report. NCANDS tracks reports of Investigated child maltreatment, and so each row corresponds to a distinct child who experienced a distinct report. What this means is that any given child can have multiple child reports if they received multiple reports. And each report can have multiple child reports insofar as multiple children were involved in a single report. For AFCARS, the layout is somewhat different. The unit of analysis is the child-year. So in any AFCARS foster care file, if a child passes through the foster care system in that year, they'll have exactly one row. So the unit of analysis in AFCARS is the child-year. In NYTD, which is a repeated observation study, the unit of analysis is the child-wave. Your research question may correspond to these unit of analyses but it very likely doesn't. And so in this case, you'll need to reshape your data, tidy your data, somewhere or another modify your data to capture your unit of analysis. Briefly, by way of example, 2 research questions. First, how many children experienced a maltreatment investigation in each U.S. state annually? In order to answer this question, we would have to take NCANDS data, but then drop records from those files from subsequent reports experienced by children within the same year. And then count up all the remaining records by each state and year. A second research question, how many maltreatment investigations occurred in each half month? Here in NCANDS, we would drop records from additional children covered in each investigation, and then count up the records of each distinct investigation grouped by each half month. As you can see, the research question dictates the unit of analysis which in turn dictates how we'll reshape, how we'll tidy our data. [ONSCREEN CONTENT SLIDE 13] Clarifying your sampling frame. Different research questions recommend different temporal sampling frames. E.g. fiscal year 2018, calendar year 2010, or 2012 birth cohort through age 6 It is easy to confuse the timing of event occurrence and event reporting. E.g. a maltreatment investigation included in the 2019 Child File may have occurred in the 2018 or 2020 fiscal years. Careful combination and tidying of multi-year data is usually needed to sample validly. [Alex Roehrkasse] A last very, very basic but very essential question is to clarify your sampling frame. Different research questions recommend different temporal sampling frames. For example, maybe I want to study all children in fiscal year 2018, or in calendar year 2010, or maybe I want to follow the 2012 birth cohort through age 6. Using administrative data, it's very easy to confuse the timing of an event's occurrence with the timing of an event's reporting. Let me give an example. A maltreatment investigation included in the 2019 NCANDS Child File may actually have occurred in the 2018 or 2020 fiscal years. Conversely, a child maltreatment investigation that actually occurred in fiscal year 2019 might show up in the 2020 NCANDS child file. For this reason, careful combination and tidying of multi-year data is often needed in order to sample validly. [ONSCREEN CONTENT SLIDE 14] Data year vs. submission year. A bar chart in showing the number of records observed in a sample of NCANDS data every half-month in fiscal year 2019. In most half-months, most records come from the 2019 NCANDS Child File, but toward the end of the year, a meaningful proportion come from the 2020 NCANDS Child File. Note: Results based on 1% random samples of the 2019 and 2020 NCANDS Child Files. [Alex Roehrkasse] Let me give a brief illustration of this. Here you see just a 1% sample of the 2019 and 2020 NCANDS child files. This bar graph shows the number of records on the vertical axis. And each half month is plotted on the horizontal axis. Data from the 2019 Child File is in blue, and data from the 2020 Child File is in red. Let's say we wanted to know the number of maltreatment investigations that occurred in the fiscal year 2019. If we only used data from the 2019 Child File, it would seem that records started to taper off toward the end of the federal fiscal year. But this would be an artifact of the timing of reporting in the NCANDS system, because, as you see from the red bars. A certain number of reports that actually occurred in the 2019 federal fiscal year only made it into the 2020 child file. And those delayed reports were concentrated at the end of the 2019 federal fiscal year. [ONSCREEN CONTENT SLIDE 15] Tidying your data. Each observation should have its own row. This should correspond to your unit of analysis. Each variable should have its own column. Each value should have its own cell. When they don’t, summarizing and pivoting are the most common methods of tidying. [Alex Roehrkasse] Once we've cleaned our data, Once we've thought carefully about unit of analysis and sample, ideally, we'll arrive at What we like to call tidy data. What is tidy data? A tidy data set is one in which each observation should have its own row. Basically, whatever your unit of analysis is, each distinct observation of that unit should have its own row. Each variable should have its own column. And each value of a variable should have its own cell. Whenever one of these things isn't the case, it's okay, don't freak out. But it usually does mean that we need to summarize or pivot, or do some other manipulation to our data to arrive at tidy data. So very briefly, I'm going to illustrate both summarizing and pivoting data, which are 2 of the most common strategies we use in order to get tidy data. [ONSCREEN CONTENT SLIDE 16] Summarizing as tidying (NCANDS). A "Raw Data Data" table with sample data in three columns: StFCID, FY, StFCID. A one-way arrow points from that table to a "Summarized Data Table" with sample data in three columns: FY, StFCID, NSub. [Alex Roehrkasse] First, summarizing. What is summarizing? Summarizing is what, in other languages, we call collapsing, but in R, summarizing means essentially taking multiple rows of data. Reducing them to a smaller number of rows. And in doing so, creating some new variable that summarizes information in the original raw data. Let me walk through an example briefly. Here is a sort of toy example of some NCANDS data. In the first column is the fiscal year. We have data from 2021 and 2022. In the second column is a unique child identifier. So each child gets a unique and consistent value of this variable. Here we have two children, one from Alabama, who shows up twice here, and one from Kentucky. In the third column, we have an actual variable from NCANDS, report disposition. This is the disposition that the investigation gets after being investigated. One for this variable means that the report was substantiated. Five means that the investigation did not substantiate the allegation of abuse or neglect. In this raw data, the unit of analysis, as it is in the distributed NCANDS child files, is the child-report. So each row corresponds to a unique combination of the child identifier and the report. Which means that children can have multiple child reports in a given year. Let's say, though, we want our unit of analysis to be the child year rather than the child report. In this case, what we need to do is collapse, or summarize, multiple observations of a distinct child In a single year. So we tell R, let's summarize our data, collapsing it, essentially, by year and child ID, and in doing so, generate a new variable that counts the number of substantiated investigations that the child experienced in that year. For the Alabama child in 2021, this is 2, because they experienced two substantiated investigations. For the Kentucky child in the same year, it's 1, because they only experienced 1 report. Notice that for the, sorry, for the Alabama child in 2022, same thing, but for the Kentucky child in 2022, note that even though they had two reports, only one of them was substantiated. And so this is the value of our variable for that child in that year. This is an example of using summarizing as tidying, where if we need our data to correspond to a new or different unit of analysis such as the child year, we can summarize or collapse our data to create a new unit of analysis. Corresponding to each row. [ONSCREEN CONTENT SLIDE 17] PIVOTING as tidying (NCANDS). A "Long Data" (Sample) table with sample data in three columns: FY, StFCID, NSub. A two-way arrow points between that table and a table called "Wide Data" which has sample data in three columns: StFCID, NSub_2021, NSub_2022. [Alex Roehrkasse] What about pivoting? Pivoting is another way of sometimes reshaping our data to arrive at tidy data. Here on the left, you'll see labeled long data. This is the exact same data set we just created by summarizing our data. This data is long in so far as for each child, we have multiple observations for each year. We say it's long by child and year. What if we want to ask a research question that requires us to have simply children as the unit of analysis? Not the child year, but the child. Now we need each child occupying only one row in our dataset. We can pivot our data, and I like to imagine kind of rotating the data on a diagonal, such that instead of multiple rows for each year, we now have one row corresponding to each unique child. And then, instead of one column measuring our count of substantiated investigations, we have multiple columns measuring that same value in multiple years. This is a way of pivoting our data wide. And of course, as indicated by this arrow going in both directions, we can pivot our data wide, or we can take wide data and pivot it long. Pivoting is a key strategy in data manipulation, data management, so that we can make sure that our data are tidy. [ONSCREEN CONTENT SLIDE 18] Programmatic coding. Excessive copying/pasting of code can decrease interpretability, increase risk of error. Programmatic coding makes repetitive processes more transparent, malleable. Common examples: Within the Tidyverse, functions in the purrr package User-written functions Loops [Alex Roehrkasse] Lastly, and we'll talk a little bit about this at the end of our demonstration today. Data cleaning can be pretty intensive. I've written scripts that clean data that can be multiple thousands of lines long. Often, we'll find ourselves iterating different processes over and over again, and we may be tempted to copy and paste code. Copying and pasting code, though, can be dangerous, for at least two reasons. One, it makes your code less interpretable. Second, it increases the risk of making unknown errors. Say I do something 6 times, and so I copy and paste it 6 times with slight alterations each time. Let's say I need to tweak something about that process, and I think I tweak it 6 times, but I only tweak it 5 times, or I forget that I need to tweak it multiple times at all. Copying and pasting increases the chances that I forget to make all of the adjustments I need. Programmatic coding makes repetitive processes more transparent and more malleable. We tell R explicitly to do something multiple times to apply some function or some process to multiple objects. The Tidyverse is a very large package of functions that can help with programmatic coding. Base R includes other commands and functions that are helpful for programmatic coding. We can write our own functions as we'll do a little bit later in the demonstration. And so this is a slightly more advanced topic, but I think a very important one, particularly if you're working with multiple years of data or a large number of variables that have similar properties. [ONSCREEN CONTENT SLIDE 19] Demonstration in R [Alex Roehrkasse] So now we'll start to pivot to our demonstration in R, and as we do, I just want to, highlight a couple things about R. [ONSCREEN CONTENT SLIDE 20] Getting started with R and Rstudio. A screenshot of the web site at which R and RStudio can be downloaded and installed. The screenshot includes click buttons for downloading and descriptions of R and RStudio. [Alex Roehrkasse] We're doing more demonstrations in R with the archive these days because R is an increasingly popular language in which to do analyses of archive data. It has the distinct merit of being open source, which means that it is free to you. If you want to work with R, I recommend that you use what's called a integrated development environment. In particular, I recommend Rstudio and I'll be doing our demonstration today in Rstudio. This image here illustrates how you can download both R and RStudio and get up and running with R yourself. [ONSCREEN CONTENT SLIDE 21] Additional resources. Hands-On Programming with R, Garrett Grolemund R Cookbook (2e), J.D. Long and Paul Teeter R for Data Science (2e), Hadley Wickham, Mine Çetinkaya-Rundel, and Garrett Grolemund [Alex Roehrkasse] If you want to build on today's learning, here's a few additional resources that I very highly recommend. All of these resources, like R, are open source, and so if you search for any of these titles, you'll be able to access them completely free online. Okay, with that, I'll start transitioning to RStudio. And if you'll bear with me for a moment as I do that, I thank you. [ONSCREEN CONTENT] ######### # 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. [Alex Roehrkasse] Okay. This is, more or less, what RStudio looks like. I'm going to give you a brief crash course in what you're looking at. In the upper left-hand quadrant is where you'll see your program file. Today we'll be working with a script which has the suffix dot R. If you like to work in markdown, that would also appear here as well. In the upper right-hand quadrant, you'll see the environment. R is essentially an object-based language. What we do in R is create objects and manipulate those objects. Any objects that R understands to exist will appear in your global environment. In the bottom left-hand quadrant, you'll see the console. Whenever R does things, it will tell you about what it's done in the console. The console is also a place where you can tell R to do things. I could type in 2 plus 2 here, hit enter, and R would do that math for me. But as I strongly recommended in my slide deck, I advise you never to code from the console. Instead, what we'll be doing is using our program file, highlighting different things we want R to do, and then hitting the run button in order to execute that function. Green text is anything that R understands to be a note, or a comment, or an annotation, so it's not something it will run. And I like to leave notes for myself at the top of any program file that I write. For example, these notes just say what this program file is, what it's for. Anyone who has any questions about this program file can contact me here. And then, very importantly, I want to note to everyone in attendance here today that while the data we'll be using look very real, they are not. I've taken measures to anonymize the data we're working with today. By partially fabricating them to prevent identification of the people that the data describes. So these are not real data, they're fabricated data. They'll have many of the same properties that real data have so this should be a realistic presentation, but please understand this is not a presentation or analysis of NDACAN data. [ONSCREEN CONTENT] ##################### # TABLE OF CONTENTS # ##################### # 0. SETUP # 1. EXAMINING YOUR DATA # 2. CLEANING YOUR DATA # 3. CODING PROGRAMMATICALLY # 4. SAVING YOUR DATA [Alex Roehrkasse] I also like to have a table of contents at the top of my program files. So I'll be walking you through some setup, a demonstration of some strategies for examining your data, for cleaning your data, and for coding programmatically. And then, lastly, how to save your data in a way that will preserve the important work you've done. A brief reminder that if you feel we're moving pretty fast, Don't worry. The code will be available to you on our website, so you can follow through at your own pace on a later date. [ONSCREEN CONTENT] ############ # 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) [Alex Roehrkasse] Okay, first thing to do is to set up our environment. As you'll see, over here, R tells us the environment is empty, but if you've already been doing some work, it may not be. And so, the very first thing I like to do in any program file is to clear the environment. Next, we'll load some packages. R has its own base language, a native language that it understands. But a lot of what contemporary researchers do using R, involves user-written packages. You need to tell R to read in these packages. We're going to be using two packages today, data.table and the tidyverse. Data.table is very helpful for reading large files, and the tidyverse is a very, very large package with sort of an omnibus set of functions for data analysis. I like to use another package, pacman, To load other packages. The reason is that pacman knows if you've already installed packages or not. If you haven't installed them, it will go ahead and do it for you. If you've already installed them, it will go ahead and load it. The next thing we'll do is define some file paths. Basically, just create some objects over here, which are just textual descriptions of places on my computer, locations on my computer. One of these locations is my project folder. The other is my data folder. You'll notice that the data folder is on a Fedramp-authorized cloud server. Lastly, we'll tell R that the project folder is our working directory. We'll set a seed, which is not relevant for today's demonstration, but will be in a couple weeks when we talk about missing data. [ONSCREEN CONTENT] ## 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. [Alex Roehrkasse] And now we'll go ahead and read in some data. I'm going to read in 4 files. Four years worth of NCANDS Child File data. Notice that these data are anonymized, they're not real data. Note also that I was able to do that pretty quickly, and you'll notice that each object, each sort of submission year has about 150,000 observations. That's because I've taken a very small sample of the whole NCANDS child file. We'll only be talking about data from New England today, which is about, roughly, a very put it put it this way, a very small sample of the full data set. And furthermore, we'll be talking only about a small number of key variables. So I've already gone ahead and taken a sample of the actual administrative data just to make things simple today and also to make them computationally speedy. Distributed data files are very large, depending on whether you're working on a laptop or a desktop or on a server, many of the operations we're doing today can take minutes or hours, or even days when done on full data sets. So please be aware of this and plan accordingly. I often recommend that as you're troubleshooting and developing code to clean and manage data that you work with a very small sample of the data. And then only once you've finalized your code, run it on the full archive data sets. We'll focus today on data from 2023, but as you'll see, when we start to code programmatically, we'll incorporate multiple years of data. [ONSCREEN CONTENT] ########################## # 1. EXAMINING YOUR DATA # ########################## > # 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 [Alex Roehrkasse] Okay, let's first examine our data. As I said, most NDACAN data files are much too large to just open in Excel and kind of view by brute force in some sort of spreadsheet software. Even looking at a very small sample of the full NCANDS data, we see that there's 150,000 rows in this object. Our Excel very well may crash if you tried to open a file of this size. There are several helpful ways, though, to view snippets of the data in R. One way to do it is to tell R, okay, treat this object as a matrix, and then show me the first 5 rows and the first column of that matrix. What it shows me is the submission year variable and the first 5 values of that variable. We can get a little more fancy and say, okay, again, the first 5 rows, but now show me columns 1, 2, and 6. [ONSCREEN CONTENT] > 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 [Alex Roehrkasse] Now it shows us again the first 5 rows, but columns 1, 5, and 1, 2, and 6, which correspond to distinct variables. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] Very useful is the head function, which returns the first 5 rows of all of the columns in your data set. So we see here the first 5 rows for each variable in our dataset. Sometimes, though, you may have more than 100 variables, and so HEAD can be nicely combined with the SELECT function. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] The select function pulls out columns by name that you want to see or work with. Now we're going to introduce something called the pipe operator. There's a sort of new pipe operator out there these days. It used to sort of look something like this. The pipe operator essentially takes the preceding element as the first input of the following function. It's kind of like saying, okay, and to whatever you've got, now do this thing. So take our NCANDS 2023 object. Apply the head function to it. And then, apply the select function to it, where we pull out these three variables. If we run this chunk of code. We get again first 5 rows and these distinct variables. Note that piping it in this way is equivalent to taking the data object, inserting it into the head function, and then inserting that into the select function. Identical output when we run it this way. In each of these examples we're taking just the first few rows of the data. But sometimes it's helpful to get more of a sample of the data, even a random sample of the data. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] The slice sample function can be helpful here where we take a point .01% sample of the data set and now instead of only getting records from Massachusetts, we get records from Connecticut, Maine, New Hampshire, and we get a greater variety of values for this report disposition variable. So slicing and sampling in this way can be a helpful way of examining your data as well. [ONSCREEN CONTENT] ######################### # 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) [Alex Roehrkasse] Okay, once we have started to get a sense of the structure of our data, we want to start cleaning it. Most datasets, as I've said, are quite large, and so before you invest a lot of time in cleaning them, it can be helpful to choose only those variables that you really plan on working with. You don't need to clean all of the variables in the dataset, only those that you really want to work with. I've already done this, of course, but just to make things explicit. Let's create a new data set that includes only these variables. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] It's very important to understand that the data in the data sets that we distribute will not correspond exactly. The encodings in these data will not correspond to how they're described in the codebook. Overwhelmingly, they do. Universally, no. Let me give you a brief example. The count function counts up the number of observations with distinct values of a certain variable. The FCmoney variable is a variable that captures whether or not a child experiencing a maltreatment report was identified as having financial distress. What do we see? Well, 732 children in our sample had a value of 1, which means yes. 5,646 Had a value of 2, which means no. And then an even larger number had a value of 9, which is what the codebook says is missing. But then, as you'll see, the overwhelming majority of values have a value of NA. Nowhere in the NCANDS codebook will it say that any values of this variable will have the value NA. NA is R's native way of encoding missing data. The codebook tells you all missing data will be encoded as a 9, but that's clearly not the case. So it's very important to verify how each variable is encoded. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] Note that this count function is actually just a special case of the summarize function in which we group our data according to the values of a variable, and then count the number of rows in each group. In other words, the number of observations having each value. We could replicate this code by first grouping our data. And then summarizing it to simply count the number of observations. As you can see, exact same table. [ONSCREEN CONTENT] > # 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() column_name value n ----------- ----- ------ ChPrior 1 52276 ChPrior 2 94018 FcMoney 1 732 FcMoney 2 5646 FcMoney 9 20491 FcMoney NA 119425 Per1Rel 1 30365 Per1Rel 2 1082 Per1Rel 3 57 Per1Rel 4 74 Per1Rel 5 201 Per1Rel 6 186 Per1Rel 7 2067 Per1Rel 8 328 Per1Rel 9 201 Per1Rel 10 110 Per1Rel 88 1323 Per1Rel 99 523 Per1Rel NA 109773 RptDisp 1 45114 RptDisp 2 2332 RptDisp 4 1595 [Alex Roehrkasse] For the reason I just demonstrated with the financial distress variable, it's very important to inspect the encoding, or the values, the full distribution of values, for every variable that you're interested in working with. Here's a chunk of code that I won't work through in detail. I'll just note that we use pivoting and counting in order to generate a table. That shows us the number of observations having distinct values of each. Variable. Here again, we see the FCMoney variable has both values of 9 and NA. We have another variable in our data set, the relationship of the perpetrator to the child. We see that it's encoded in all of the ways the codebook suggests it should be, but, a very large number of observations are also encoded as NA, which the codebook would not indicate. [ONSCREEN CONTENT] ## 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_)) [Alex Roehrkasse] So for this reason, we need to recode our variables how we want them. As we recode our variables, it's pretty helpful to understand What kind of variables we're dealing with. What the format of each variable is. We can do this using the STR function, which tells us, for example, that our submission year variable is an integer variable. Our two-letter state identifier is a character variable. Notably, R intuitively understands that our report date variable is, indeed, a date variable having a certain format. Now, we can set about recoding the variables of interest. Recoding usually involves the mutate function. We use the mutate function to create new variables or to update variables according to the properties of existing variables. So, for example, let's recode our report disposition variable where if that variable equals 99, we will recode it as NA. We will tell R that it is a missing value in the way R needs to understand it. Otherwise, or else, we'll leave it alone. Once we've done this, we'll further re-encode it, telling R that this is a factor variable. That it has certain levels. And that each of those levels actually has a substantive label, which we'll assign to each of those levels. We'll do the same for a variable describing whether a child has a prior investigation. Ditto financial distress. As you can see, we're adding information, adding detail to what R understands about each of these variables. And we're cleaning up or re-encoding missing data. Sometimes, though, we want to re-encode a variable in a completely different way. For example, the perpetrator relationship variable has a very large number of codes, and perhaps we're not interested in quite so much detail. Perhaps we don't care whether a foster parent is a relative or not. Perhaps we don't care whether a professional is a daycare provider or a group home worker. We can re-encode these multiple categories into a single category, professional or foster parent. Let's go ahead and run this large chunk of code. And then examine the object that it creates. [ONSCREEN CONTENT] > 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 [Alex Roehrkasse] Again, just the first five rows of this new object. And you can see that our old perpetrator relationship variable has these numeric codes. Which now, in our new perpetrator variable, have meaningful labels. Okay, so this is kind of encoding or recoding or cleaning by brute force. But as you saw, that chunk of code was pretty long and complex. Imagine that we were doing that over and over and over again over multiple years of NCANDS data. Or imagine that we're recoding scores of variables for any given year. There's a lot of opportunity to make mistakes. So this is an opportunity for us to code programmatically in a way that will make things both more transparent and more reliable. [ONSCREEN CONTENT] > # 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 [Alex Roehrkasse] I'm going to demonstrate two ways to code programmatically. The first is to recode multiple variables at the same time which have similar encodings. For example, the variable CHPRIOR and FCMONEY should both have an encoding where 1 is equal to yes. 2 is equal to No. And any other variable, or any other value, should be missing. And so what we tell R is to mutate across these 2 variables. Where, for any variable in this list, If it's equal to 9, We assign it NA. And then subsequently recode that variable or factorize it. Where the 2 levels are recoded as yes and no. When we do this and compare it to the previous data set, we see that previously the FCMoney variable had both an encoding as 9 and NA. But after this recoding, we get yes, no, and N/A only. [ONSCREEN CONTENT] > # 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) [Alex Roehrkasse] Now let's try a second form of programmatic coding. Where we will recode multiple years of NCANDS data at the same time. We've only been working with 2023 data. What if we want to clean 5 years of NCANDS data at the same time? First, we'll make a list. A list is a kind of object that R understands, and this list will just be a list of the 5 different years of data. So we'll first create this list, and we'll see the list up here, here in our environment. It's described as a large list, having five elements. Each of those elements is just one of these data frames. [ONSCREEN CONTENT] # Note that the variable names in each year don't match. names(n21) == names(n22) names(n22) == names(n23) names(n22) names(n23) > 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 [Alex Roehrkasse] An important thing to note, Ideally, the names of variables would not change across multiple years of data. If we compare the names of variables in the 2021 and 2022 data, that appears to be the case. Same, same, same, same, same. However, if we compare the names of the data in the names of the variables in 2022 and 2023, Not so. [ONSCREEN CONTENT] > 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" [Alex Roehrkasse] If we examine why, it seems to be the case that, in different years, some variables are capitalized in different ways. So we'll need to deal with that whenever we're coding programmatically and manipulating or cleaning multiple years of data at the same time. [ONSCREEN CONTENT] # 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_)) } [Alex Roehrkasse] Now that we've created a list of multiple years of data, we'll write our own function. We'll do this by creating a new object, which will tell R is a function. That function will have one input, which we'll call DF for data frame. The function is then defined by everything that falls between these brackets. What's in the function? What is the function? Well, it looks a lot like that previous chunk of code, where we'll take an object, whatever the input to that function is. And we'll do a bunch of data cleaning. Notably first, though, we will rename any of the variables in any of those objects to all lower cases. This allows our variables to be the same across multiple years of data in which, in the distributed or raw files, they don't actually correspond. Then we'll recode all of the variables as we did before, because, very happily, all those variables are consistent across multiple years. When we tell R to execute the code defining the function, all it really does is create a new object here, namely a function in the environment. [ONSCREEN CONTENT] # And now list-apply the function to our list of data frames. nlistc <- lapply(nlist, nclean) [Alex Roehrkasse] The last thing to do is to apply, or list apply, the function to the list. We use that, or we do that using the L apply function. This creates a new list, over here. Where the list includes not the raw data objects, but the new cleaned data objects. [ONSCREEN CONTENT] # And now we can even stack the cleaned data frames, # converting a list into a single data frame. nc <- list_rbind(nlistc) [Alex Roehrkasse] Of course, lists are a little bit counterintuitive and not the easiest way to do data analysis, so the last thing we can do is unlist the data, or essentially stack all of the elements in the list on top of one another to create a single data frame. We do this using the list rbind command. Creating a new dataset, NC corresponding to just the cleaned NCANDS data for 2020 all the way through 2024. [ONSCREEN CONTENT] > 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 [Alex Roehrkasse] And then if we take a small sample of that data. We see that it includes our new cleaned variables. But also data, not only from multiple states, but also multiple years, 2023, 2021, 2020. This is a admittedly simple, but I think powerful example of how you can clean many years of administrative data reliably, transparently, efficiently. The last thing to do is to save our data. [ONSCREEN CONTENT] # 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')) [Alex Roehrkasse] It's very common to work with CSV files. I do it very often. It's important to understand, though, that much of the encoding work we've done in R will be lost if we save it in a separated file. And so I often encourage people, as you're working with intermediate data files, to save using R's native data format, RDS, which will preserve the encoding that you've done. [ONSCREEN CONTENT SLIDE 22] Questions? User support: NDACANsupport@cornell.edu Garrett baker: garrett.baker@duke.edu Alyssa Lindsey: Alyssa.lindsey@ucsf.edu [Alex Roehrkasse] That's the end of my demonstration. Thanks for bearing with me. I'm going to pull up our slides once more. And reiterate that we're here to field any questions you have about archive involved projects. The user support line is the general support line, and folks who respond there will help triage and direct those inquiries to the appropriate staff. You'll see my contact and Alyssa's contact information there as well. At this point, I'll stop talking and let Alyssa manage the chat to support a Q&A for the remainder of the hour. Thanks, everyone, for your attention. [Alyssa Lindsey] Yeah, thank you, Alex, so much. This is a great presentation. We have time for a few questions. The first question that came in, can you explain further how a report that occurred in 2019 could end up in the 2018 fiscal year report? [Alex Roehrkasse] Thanks. Great question. Yeah, it's a little more intuitive why a report occurring in, say, 2019 would end up in the 2020 submission year. Less intuitive why it would end up in the 2018 submission year. I don't have a good substantive explanation. It's a much less common scenario. I'm usually much more concerned about delays in reporting. It would show up in an earlier submission year, most likely if it was kind of right at the edge of the federal fiscal year. I think my top line advice here, though, is to be careful to think about submission year and data year as distinct frames. And so whatever your data year is. You need to think carefully, but also empirically. About what submission years you need to adequately cover those data years. I suggested that as a rule of thumb, you should have an additional subsequent submission year. We're talking here about NCANDS, really. An additional subsequent data submission year in order to cover each data year that you're interested in. But this can be verified empirically by downloading the data and looking at the timing of reports in each submission year. So, the importance of this, the degree to which it will contribute to bias in your analysis, depends on the nature of your research question, the temporal sampling frame. I mean mostly to highlight this as a potential concern and to encourage you to investigate it yourself using data empirically. [Alyssa Lindsey] Great. Thanks, Alex. The second question says, for those of us who don't use R, do you also provide these kinds of examples in other statistical programs, for example, SPSS? Just to answer that quickly, and Alex, feel free to add, we don't have videos which describe these data procedures in SPSS specifically, but if you send a message to the support email, which I put in the chat and I can put in the chat again. A data analyst who's familiar with SPSS may be able to assist you with any specific questions. And I don't know, Alex, if you have any… Anything else to add. [Alex Roehrkasse] Just a little. Yeah, it's tricky to serve a research community that uses a wide variety of statistical softwares. And we are candidly transitioning to providing a greater proportion of our trainings in R and Rstudio. That said, when we distribute data sets, we often distribute code to help read in our data using the native data formats for different statistical softwares. So if you're an SPSS user, not for all of our data sets, but for many of our data sets, you can read in a native SPSS format of our of our data. Some of our archived, presentations have been done in other statistical software, mostly STATA. Some of those are not no longer available on our website. But if you are, as Alyssa said, looking for support with a specific software, someone on our team will be able to help you with that, and we may have resources that we can share on a case-by-case basis. [Alyssa Lindsey] Great. Thanks, Alex. And then just to address the two questions around if we're going to share the R demonstration codes, yes, I believe that is our plan. And Alex, if you want to share anything. [Alex Roehrkasse] Yeah, please, please, please use it and adapt it. I really only learned to code by being given other people's code and adapting it to my purposes, studying it carefully to understand why they're doing what they're doing, how their code works. And so please don't hesitate to download the code, adapt it to your purposes, reach out with questions about it. That's entirely in the spirit of this presentation and the research support we do. [Alyssa Lindsey] Yes, and then I saw that a third question came in about sharing the slides. Yes, we will be sharing the slides with the recording as well as the R code, and that will all be shared on the website, and an announcement of that will be sent on the listserv. Which you can subscribe to. I put that link in the top of the chat. Thanks, everyone. I have a question. Do you use Tableau software for data analysis and trainings? [Alex Roehrkasse] Short answer, no. But again, if you were curious about support in Tableau, I would email that main user support line, and we'll do our best to find someone who can support Tableau users. [Alyssa Lindsey] And I saw that a participant raised their hand. If you could use the Q&A function to put any questions you have, that would be wonderful. Thank you. And if you need help finding that Q&A box, it's in the lower right of your Zoom screen. It has a question mark in a box. Thanks. We have time for one more question. And a reminder to if any other more specific questions come up, or questions come up after the call, like Alex said, feel free to reach out to our support email, or to the emails that we put in the chat, and we're happy to assist with anything. That comes up regarding this. [ONSCREEN CONTENT SLIDE 23] Next week… Date: July 15th Topic: Linking NCANDS and AFCARS Instructor: Alex Roehrkasse [Alex Roehrkasse] Yeah, and a brief plug for next week's presentation. We'll be talking about linking NCANDS and AFCARS that will build directly on much of what we talked about today. Some of the unit of analysis questions, summarizing and pivoting questions are especially useful when it comes to formatting administrative data sets for linkage. [Alyssa Lindsey] All right. We're at the top of the hour. So just to respect everyone's time, we're going to go ahead and wrap it up for today. See everyone next week, same time. Thank you so much, and thank you, Alex, for the presentation. [Alex Roehrkasse] Thanks, everyone. Take care. [VOICEOVER] The National Data Archive on Child Abuse and Neglect is a joint project of Duke University, Cornell University, University of California, San Francisco, and Mathematica. Funding for NDACAN is provided by the Children's Bureau, an office of the Administration for Children and Families. [Music]