Weighted mean of duplicated columns

Question

I have df:

x <- c(1,1,2,2,3,3,4,5)
y <- c(1,1,2,3,3,3,4,4)
freq <- c(4,6,7,2,2,6,5,1)
distmean <-c(2,4,5,7,3,5,2,7)
df <- data.frame(x,y,freq,distmean)


x      y   freq   distmean
1      1      4          2
1      1      6          4 
2      2      7          5
2      3      2          7
3      3      2          3
3      3      6          5 
4      4      5          2 
5      4      1          7

I want to aggregate the rows based on x and y, with the sum of freq and a mean of distmean weighted by freq.

So in the end I want:

x      y   freq    distmean
1      1     10         3.2
2      2      7         5.0
2      3      2         7.0
3      3      8         4.5
4      4      5         2.0 
5      4      1         7.0

I tried using aggregate() which I can combine the duplicated rows with, but can't figure out a weighted mean.

Possible duplicate of [Using aggregate to apply several functions on several variables in one call](https://stackoverflow.com/questions/12064202/using-aggregate-to-apply-several-functions-on-several-variables-in-one-call) — Maurits Evers, Nov 14 '17 at 03:41
I thought too. Ok, so `df %>% group_by(x, y) %>% summarise(freq_1 = sum(freq), dist_mean = weighted.mean(distmean, freq)) ` should give it. — Ronak Shah, Nov 14 '17 at 03:48
Yep, and data.table is nearly identical `df[ , .(freq=sum(freq), distmean=weighted.mean(distmean,freq)), by=.(x,y)]` — thelatemail, Nov 14 '17 at 03:49

score 0 · Answer 1 · answered Nov 14 '17 at 04:19

This may not be a short method to do this. But can be done this way if you want to avoid complex functions.

df2=a=aggregate(freq ~ x+y, data=df, sum, na.rm=TRUE) df$dist=df$distmean*df$freq b=aggregate(dist ~ x+y, data=df, sum, na.rm=TRUE) df2$distmean=(b/a)[3]

Weighted mean of duplicated columns

1 Answers1