calculating the proportion of count variable per group in data.table in R

user_6119375 0
Viewed 724

I have the following data:

dput(mydata)
structure(list(groupSize = structure(c(2L, 1L, 2L, 1L, 4L, 4L, 
3L, 3L, 2L, 2L, 1L, 1L, 3L, 3L, 4L, 4L, 2L, 2L, 1L, 1L, 3L, 3L, 
4L, 4L, 3L, 3L, 2L, 2L, 1L, 1L, 4L, 4L, 2L, 2L, 4L, 4L, 3L, 3L, 
1L, 1L), .Label = c("small", "intermediate", "large", "huge"), class = "factor"), 
    gender = structure(c(1L, 1L, 2L, 2L, 1L, 2L, 1L, 2L, 1L, 
    2L, 1L, 2L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 2L, 1L, 2L, 2L, 1L, 
    1L, 2L, 2L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 2L, 1L, 1L, 2L, 2L, 
    1L), .Label = c("F", "M", "U"), class = "factor"), startYear = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 
    4L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L), .Label = c("2014", "2015", 
    "2016", "2017", "2018"), class = "factor"), count = c(7546, 
    3500, 5930, 7668, 18114, 13826, 11943, 10083, 147, 2791, 
    17158, 19389, 2063, 17901, 11007, 1660, 6660, 15198, 496, 
    18716, 17385, 12726, 11409, 4711, 16140, 244, 15251, 6485, 
    5014, 1104, 438, 10930, 15582, 15626, 2121, 6339, 135, 15432, 
    12263, 10607)), row.names = c(NA, -40L), class = c("data.table", 
"data.frame"))

I would like to calculate the proportion of males and females for every groupSize in year. So i would get for example that in year 2014 for the groupsize "small" there were 45% females and 55% males. How can this be done in data.table in R ?

data.table r
4 Answers

If you are looking for the ratio, you can do : 

library(data.table)
mydata[, prop := count/sum(count) * 100, by = .(startYear, groupSize)]


#       groupSize gender startYear count       prop
# 1: intermediate      F      2014  7546 55.9958445
# 2:        small      F      2014  3500 31.3395415
# 3: intermediate      M      2014  5930 44.0041555
# 4:        small      M      2014  7668 68.6604585
# 5:         huge      F      2014 18114 56.7125861
# 6:         huge      M      2014 13826 43.2874139
# 7:        large      F      2014 11943 54.2222828
# 8:        large      M      2014 10083 45.7777172
#....
user_39629140

You can get the proportion M as follows:

mydata[ , by = .(groupSize, startYear),
       .(pct_M = weighted.mean(gender == 'M', w = count))]
#        groupSize startYear      pct_M
#  1: intermediate      2014 0.44004156
#  2:        small      2014 0.68660458
#  3:         huge      2014 0.43287414
#  4:        large      2014 0.45777717
#  5: intermediate      2015 0.94996596
#  6:        small      2015 0.53052234
#  7:        large      2015 0.89666400
#  8:         huge      2015 0.86895082
#  9: intermediate      2016 0.69530607
# 10:        small      2016 0.97418280
# 11:        large      2016 0.42263625
# 12:         huge      2016 0.70775434
# 13:        large      2017 0.01489258
# 14: intermediate      2017 0.70164704
# 15:        small      2017 0.81954887
# 16:         huge      2017 0.96147080
# 17: intermediate      2018 0.49929505
# 18:         huge      2018 0.25070922
# 19:        large      2018 0.99132781
# 20:        small      2018 0.53620463

This is equivalent to the slightly wordier:

mydata[ , by = .(groupSize, startYear),
       .(pct_M = sum(count[gender == 'M'])/sum(count))]
user_35769840

Calculating and getting into a human-friendly format with dcast():

library(data.table)
mydata[, .(gender, prop = count / sum(count)), by = .(startYear, groupSize)
       ][, dcast(.SD, startYear + groupSize ~ gender)]

#     startYear    groupSize           F          M
#  1:      2014        small 0.313395415 0.68660458
#  2:      2014 intermediate 0.559958445 0.44004156
#  3:      2014        large 0.542222828 0.45777717
#  4:      2014         huge 0.567125861 0.43287414
#  5:      2015        small 0.469477659 0.53052234
#  6:      2015 intermediate 0.050034037 0.94996596
#  7:      2015        large 0.103336005 0.89666400
#  8:      2015         huge 0.131049183 0.86895082
#  9:      2016        small 0.025817198 0.97418280
# 10:      2016 intermediate 0.304693934 0.69530607
# 11:      2016        large 0.577363754 0.42263625
# 12:      2016         huge 0.292245658 0.70775434
# 13:      2017        small 0.180451128 0.81954887
# 14:      2017 intermediate 0.298352963 0.70164704
# 15:      2017        large 0.985107422 0.01489258
# 16:      2017         huge 0.038529205 0.96147080
# 17:      2018        small 0.463795365 0.53620463
# 18:      2018 intermediate 0.500704947 0.49929505
# 19:      2018        large 0.008672191 0.99132781
# 20:      2018         huge 0.749290780 0.25070922
user_45522950

Base R Solution: 

with(df, count/ave(count, groupSize, startYear, FUN = sum))
user_92031580
Related