CODEWITHSUNDEEP
rmergedplyr
user3022875
user3022875

Reputation: 9018

Can dplyr avoid using multiple merges?

I have the data frame "d" below that has 2 columns PCT1 and PCT2. I would like to plot the weighted PCT1 and PCT2 for each group. This requies:

(1) calculating the weighted pct1 and weighted pct2 for each group. Currently I am doing this in TWO calls using dplyr (2) Then I am merging the 2 results with rbind()

Is there a way to avoid calling dplyr twice and still produce the "result" data frame? In reality I have 10 columns not 2 and I would have to call dplyr 10 times and do something like:

 rbind(PCT1,PCT2,PCT3,PCT4, PCT5, ....,PCT10)

Thank you.

   d= data.frame (group =c("A","A","B","B"),
            PCT1 = c(100,50,100,50),
            PCT2 = c(50,1,10,5),
            weight = c(99,1, 100,100))
d

  group PCT1 PCT2 weight
1     A  100   50     99
2     A   50    1      1
3     B  100   10    100
4     B   50    5    100

PCT1  = d %>% group_by(group)  %>%  summarise(vmean =  weighted.mean(PCT1, weight))
PCT1$PCT =1
PCT2  = d %>% group_by(group)  %>%  summarise(vmean =  weighted.mean(PCT2, weight))
PCT2$PCT =2
result = rbind(PCT1, PCT2)

result

 group vmean PCT
1     A 99.50   1
2     B 75.00   1
3     A 49.51   2
4     B  7.50   2

Upvotes: 0

Views: 34

Answers (2)

akrun
akrun

Reputation: 887148

Another option is data.table

library(data.table)
melt(setDT(d), measure = c("PCT1", "PCT2"), variable.name = "PCT_GRP")[,
        .(vmean = weighted.mean(value, weight)) , .(group, PCT_GRP)]
#   group PCT_GRP vmean
#1:     A    PCT1 99.50
#2:     B    PCT1 75.00
#3:     A    PCT2 49.51
#4:     B    PCT2  7.50

Upvotes: 1

joran
joran

Reputation: 173577

You just need to melt your data frame further:

library(dplyr)
library(tidyr)

d <- data.frame (group =c("A","A","B","B"),
                             PCT1 = c(100,50,100,50),
                             PCT2 = c(50,1,10,5),
                             weight = c(99,1, 100,100))

d %>%
    gather(key = PCT_GRP,value = PCT,PCT1:PCT2) %>%
    group_by(group,PCT_GRP) %>%
    summarise(vmean = weighted.mean(PCT,weight))

Upvotes: 3

Related Questions