Calculate a series of weighted means in R for groups with different weightings

2.3k views Asked by At

I have the following dataset (simple version of my actual data), 'data', and would like to calculate weighted means for variables x1 and x2, using weightings w1 and w2 respectively, split up into two groups (groups determined by the variable n).

data <- data.frame(n = c(1,1,1,2,2,2), x1 = c(4,5,4,7,5,5), x2 = c(7,10,9,NaN,11,12), w1 = c(0,1,1,1,1,1), w2 = c(1,1,1,0,0,1))

I'm trying to do it using with() but get an error when I run this:

with(data, aggregate(x = list(x1=x1, x2=x2), by = list(n = n), FUN = weighted.mean, w = list(w1 = w1,w2 = w2)))

On the otherhand, if weights aren't specified it works, but in this case default level weights are used (i.e. same as using FUN=mean).

with(data, aggregate(x = list(x1=x1, x2=x2), by = list(n = n), FUN = weighted.mean))

This question is similar to weighted means by group and column, except that my question includes different weightings for different columns. I tried using a data table but it runs into the same weighting errors as with(). Thanks in advance for any help.

2

There are 2 answers

3
akrun On

Try

library(data.table)
setDT(data)[, .(x1=weighted.mean(x1, w1), x2=weighted.mean(x2, w2)) , by = n]

Or as @thelatemail commented, we can use Map to loop over "x's", corresponding "w's" columns and call with a single weighted.mean

setDT(data)[, Map(weighted.mean, list(x1,x2), list(w1,w2)), by = n]

If there are many "x" and "w" columns, we can use grep to get the column names, mget to return the values inside the Map

setDT(data)[,  Map(weighted.mean, mget(grep('x', names(data), 
    value=TRUE)), mget(grep('w', names(data), value=TRUE))), by = n]
0
Steven Beaupré On

Try:

library(dplyr)
data %>% 
  group_by(n) %>% 
  summarise(x1 = weighted.mean(x1, w1), x2 = weighted.mean(x2, w2))