5

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.

Community
  • 1
  • 1
Tina218
  • 51
  • 5

2 Answers2

9

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]
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    `data[, Map(weighted.mean, list(x1,x2), list(w1,w2)), by=n]` to save having to type the function multiple times. – thelatemail Jun 11 '15 at 05:43
  • @thelatemail That is very nice option. I was also thinking `Map` in a base R context but didn't get it right. Wouldn't this go as a separate post. – akrun Jun 11 '15 at 06:16
  • 2
    it's essentially the same as your code, just feel free to edit it in if you like. I don't need the imaginary internet points :-) – thelatemail Jun 11 '15 at 06:22
4

Try:

library(dplyr)
data %>% 
  group_by(n) %>% 
  summarise(x1 = weighted.mean(x1, w1), x2 = weighted.mean(x2, w2))
Steven Beaupré
  • 21,343
  • 7
  • 57
  • 77