dplyr group_by over elements of two columns

  • A+
Category:Languages

A simplified version of my dataset can be reproduced by the following:

df <- data.frame(buyer = c("A","C","B"),                  seller = c("B","D","E"),                  amount = c(1,2,3)) 

I am looking for a preferably dplyr solution to achieve the following.

buyer          seller       amount   A              B           1   C              D           2   B              E           3 

should result in a grouped summary for each agent (A,B,C,D,E)

output agent     total_amount   A        1   B        4 #(=1+3)   C        2   D        2 

I could group_by for buyer and seller and then add the results but this is not elegant and somewhat cumbersome.

library(dplyr) res_b <- df %>%       group_by(buyer) %>%       summarise(total_amount=sum(amount)) res_s <- df %>%       group_by(seller) %>%       summarise(total_amount=sum(amount)) 

Any help is appreciated. Other solutions (not in tidyverse) are obviously welcome too.

Edit: should have said that my original dataset as around 60 million observations.

 


We can convert to long format first and do a simple aggregation, i.e.

library(tidyverse)  df %>%   gather(var, agent, -amount) %>%   group_by(agent) %>%   summarise(total_amount = sum(amount)) 

which gives,

# A tibble: 5 x 2   agent   total_amount   <chr>          <dbl> 1 A                1 2 B                4 3 C                2 4 D                2 5 E                3 

You can try data.table for more efficiency. Here is a direct translation of the tidyverse code above,

library(data.table)  dt1 <- setDT(df) melt(dt1, measure.vars = c('buyer', 'seller'), id.vars = 'amount', value.name = "agent"      )[, .(total_amount = sum(amount)), by = agent][] #   agent total_amount #1:     A            1 #2:     C            2 #3:     B            4 #4:     D            2 #5:     E            3 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: