How do you calculate median, by group, based on aggregate data?

Question

I have the following data set:

 State County   Age   Population
  AL   Alachua  0-5      1043
  AL   Alachua  5-10     1543
  AL   Alachua  10-15    758
  AL   Alachua  15-20    1243
  AK   Baker    0-5      543
  AK   Baker    5-10     788
  AK   Baker    10-15    1200

My age groups actually go on to 85+ but for ease I've only included and example.

How do I calculate the median age for each group of county and state from their populations for all states in my sample?

To be clear for each group of state and county I want to calculate the median age using the population figures for each.

score 0 · Answer 1 · answered Feb 01 '18 at 15:28

Calling your data dd. I use data.table for the grouping. We first make sure that Age is a factor with the correct order for the levels (expand the age_order for the full data). Then we calculate the median age group using matrixStats::weightedMedian. (I just searched Stack Overflow for "weighted median [r]" and got this lovely question). Then we convert the median back to the age group label. I left it in your long format rather than pulling out a summary data frame.

library(data.table)
setDT(dd)
age_order = c("0-5", "5-10", "10-15", "15-20")
dd[, Age := factor(Age, levels = age_order)]
dd[, age_group := as.integer(Age)]
setkey(dd, State, County, Age)
library("matrixStats")
dd[, median_group := weightedMedian(x = age_group, w = Population, ties = "min"), by = c("State", "County")]
dd[, median_age := levels(Age)[median_group]]
dd
#    State  County   Age Population age_group median_group median_age
# 1:    AK   Baker   0-5        543         1            2       5-10
# 2:    AK   Baker  5-10        788         2            2       5-10
# 3:    AK   Baker 10-15       1200         3            2       5-10
# 4:    AL Alachua   0-5       1043         1            2       5-10
# 5:    AL Alachua  5-10       1543         2            2       5-10
# 6:    AL Alachua 10-15        758         3            2       5-10
# 7:    AL Alachua 15-20       1243         4            2       5-10

Using this sample data:

dd = fread(" State County   Age   Population
  AL   Alachua  0-5      1043
  AL   Alachua  5-10     1543
  AL   Alachua  10-15    758
  AL   Alachua  15-20    1243
  AK   Baker    0-5      543
  AK   Baker    5-10     788
  AK   Baker    10-15    1200")

How do you calculate median, by group, based on aggregate data?

1 Answers1