Basic Childcare Expenditure Analysis

In this notebook, I broke down some 2023 Consumer Expenditure Survey data, to analyze child care expenditure share.

Below, I downloaded the necessary packages for data cleaning and uploaded the data set.

library(dplyr)
library(patchwork)
data_dir <- "/Users/rileystern/Downloads/intrvw23 3/"
list.files(data_dir)

 [1] "expn23"       "fmli232.csv"  "fmli233.csv"  "fmli234.csv"  "fmli241.csv"  "itbi232.csv" 
 [7] "itbi233.csv"  "itbi234.csv"  "itbi241.csv"  "itii232.csv"  "itii233.csv"  "itii234.csv" 
[13] "itii241.csv"  "memi232.csv"  "memi233.csv"  "memi234.csv"  "memi241.csv"  "mtbi232.csv" 
[19] "mtbi233.csv"  "mtbi234.csv"  "mtbi241.csv"  "ntaxi232.csv" "ntaxi233.csv" "ntaxi234.csv"
[25] "ntaxi241.csv" "para23"      

I downloaded the 2023 CES data and used the MTBI (monthly expenditures) and FMLI (family-level information) files. Then, I merged them by user ID. The monthly expenditures give a summary of where expenditures lie for respondants and the family-level information provides information about families needing childcare.

fmli_files <- list.files(data_dir, pattern = "^fmli.*\\.csv$", full.names = TRUE)
mtbi_files <- list.files(data_dir, pattern = "^mtbi.*\\.csv$", full.names = TRUE)




read_and_combine <- function(file_list) {
  combined <- lapply(file_list, function(f) read.csv(f, stringsAsFactors = FALSE)) %>%
    bind_rows()
  return(combined)
}

fmli_all <- read_and_combine(fmli_files)
mtbi_all <- read_and_combine(mtbi_files)

merged_data <- inner_join(mtbi_all, fmli_all, by = "NEWID")

Next, I downloaded the necessary graph package.

library(ggplot2)

From the FMLI and MTBI merged dataframe I created, I selected specific variables that would be relevant to childcare expenditure analysis. These include the user ID (NEWID), amount spent on childcare last quarter (BBYDAYPQ), total expenditure for the quarter (TOTEXPPQ), and age of children (CHILDAGE). I initally also included the variable for income class (INCLASS2), but later found this wasn’t necessary.

childcare_data <- merged_data %>%
  select(NEWID, TOTEXPPQ, BBYDAYPQ, INCLASS2, CHILDAGE)

To clean the data, I filtered out the empty or NA responses, and also required CHILDAGE to be greater than 0, so that families without children would not be counted. In future, perhaps there should be an upper bound for this as well. I also created a new column in this dataframe called expend_quintile, which takes the TOTEXPPQ column and divides it into quintiles, and then assigns each user a quintile based on how much they expend.

childcare_users <- childcare_data %>%
  filter(!is.na(TOTEXPPQ), !is.na(BBYDAYPQ), !is.na(INCLASS2), CHILDAGE > 0) %>%
  mutate(
    childcare_share = BBYDAYPQ / TOTEXPPQ,
    expend_quintile = ntile(TOTEXPPQ, 5)
  )

Then, I created a another dataframe based off the childcare_users, that isolated only those families that pay for childcare- i.e. not those who get it free.

childcare_payers <- childcare_users %>%
  filter(BBYDAYPQ > 0)

Next, I found averages for both dataframes, for these expenditures to be able to graph.

summary_users <- childcare_users %>%
  group_by(expend_quintile) %>%
  summarise(avg_share = mean(childcare_share, na.rm = TRUE))

summary_payers <- childcare_payers %>%
  group_by(expend_quintile) %>%
  summarise(avg_share = mean(childcare_share, na.rm = TRUE))

Here, I created a consistent axis for the graphs.

max_share <- max(c(summary_users$avg_share, summary_payers$avg_share), na.rm = TRUE)

The first plot I created features all childcare users, regardless of pay or not.

p1 <- ggplot(summary_users, aes(x = factor(expend_quintile), y = avg_share)) +
  geom_col(fill = "pink") +
  labs(
    title = "Average Childcare Share (All Users, Including Free)",
    x = "Expenditure Quintile",
    y = "Average Share of Expenditures"
  ) +
  theme_minimal() +
  scale_y_continuous(limits = c(0, max_share))

The second plot has just the payers.

p2 <- ggplot(summary_payers, aes(x = factor(expend_quintile), y = avg_share)) +
  geom_col(fill = "lavender") +
  labs(
    title = "Average Childcare Share (Spending Households Only)",
    x = "Expenditure Quintile",
    y = "Average Share of Expenditures"
  ) +
  theme_minimal() +
  scale_y_continuous(limits = c(0, max_share))

install.packages("clipr")

trying URL 'https://cran.rstudio.com/bin/macosx/big-sur-arm64/contrib/4.4/clipr_0.8.0.tgz'
Content type 'application/x-gzip' length 51681 bytes (50 KB)
==================================================
downloaded 50 KB

The downloaded binary packages are in
    /var/folders/rx/1zr50b_d1s78nny7x6_83j5m0000gn/T//RtmprM34l1/downloaded_packages

library(clipr)
library(knitr)

knitr::include_graphics("~/Desktop/Childcare-Affordability.png")

NA