Household Income - Children and Elderly on Medicaid

library(ipumsr)
library(readr)
library(survey)
library(dplyr)
library(matrixStats)
library(writexl)

ddi <- read_ipums_ddi("usa_00051.xml")
acs_data <- read_ipums_micro(ddi, data_file = "usa_00051.dat", verbose = FALSE)

#create person-level flags
acs_data <- acs_data %>%
  mutate(
    is_head = RELATE == 1,
    is_spouse = RELATE == 2,
    is_elderly = AGE >= 65,
    is_child = AGE < 18,
    is_female_head = is_head & SEX == 2,
    has_medicaid_person = HINSCAID == 2,
    has_foodstamps_person = FOODSTMP == 2,
    child_on_medicaid = is_child & has_medicaid_person,
    elderly_on_medicaid = is_elderly & has_medicaid_person,
  )

#collapse to household-level
hh_summary <- acs_data %>%
  group_by(SERIAL) %>%
  summarise(
    fam_sz = first(FAMSIZE),
    hh_income = first(HHINCOME),
    hh_weight = first(HHWT),
    num_parents = sum(is_head | is_spouse),
    num_children = sum(is_child),
    num_elderly = sum(is_elderly),
    female_head = any(is_female_head),
    all_foodstamps = all(has_foodstamps_person),
    all_children_medicaid = all(child_on_medicaid[is_child]),
    all_elderly_medicaid = all(elderly_on_medicaid[is_elderly]),
    pov_pct = first(POVERTY),   #grab poverty percentage
    pov200 = pov_pct >= 1 & pov_pct < 200      #flag for under 200% poverty (not inlcuding 0=NA)
  )

#filter for households below 200% poverty threshold with children and elderly on Medicaid and all hh members on Food Stamps
hh_summary <- hh_summary %>%
  filter(all_foodstamps & all_children_medicaid & all_elderly_medicaid & pov200,
         !is.na(hh_income) & hh_income >= 0 & hh_income != 9999999,
         )

#classify household types
hh_summary <- hh_summary %>%
mutate(
  household_type = case_when(
    num_parents == 1 & num_children == 1 & num_elderly == 0 & fam_sz == 2 ~ "Single Parent + Child",
    num_parents == 2 & num_children == 1 & num_elderly == 1 & fam_sz == 4 ~ "Two Parents + Child + Elderly",
    num_parents == 1 & num_children == 1 & num_elderly == 1 & fam_sz == 3 ~ "Single Parent + Child + Elderly",
    TRUE ~ "Other"
  )
)

# Calculate weighted median income and household counts by household type
weighted_median_income <- hh_summary %>%
  group_by(household_type) %>%
  summarise(
    weighted_median_income = weightedMedian(hh_income, w = hh_weight, na.rm = TRUE),
    unweighted_count = n(),
    weighted_count = sum(hh_weight, na.rm = TRUE),
    .groups = "drop"
  )

# Print the final table
print(weighted_median_income)

# A tibble: 4 × 4
  household_type          weighted_median_income unweighted_count weighted_count
  <chr>                                    <dbl>            <int>          <dbl>
1 Other                                   20000              5539         630913
2 Single Parent + Child                   18000               326          44092
3 Single Parent + Child …                 26350.               13           1201
4 Two Parents + Child + …                 52707.                2            125

#save to Excel
write_xlsx(weighted_median_income, "hh_inc_medicaid_child_elder_pov200.xlsx")