How to group_by in R dplyr specific way?

user_1766682 0
Viewed 51

I have a data.frame with three columns FROM, TO (direction from, direction to) and COUNT:

FROM, TO, COUNT
A, B, 3
B, A, 4
C, D, 1
X, Y, 6
D, C, 9

I need to group by rows and sum the COUNT, if I have a opposite direction present in the data.frame.

Expected result:

FROM, TO, COUNT
A, B, 7  <------- (A, B) 3 + 4 (B,A)
C, D, 10 <- (C, D) 1 + 9 (D,C)
X, Y, 6  <- stays as there is no opposite Y to X
dplyr r
2 Answers

We can use pmin and pmax to consider the groups in different order as the same and then sum each group.

In dplyr you can do this as -

library(dplyr)

df %>%
  group_by(col1 = pmin(FROM, TO), col2 = pmax(FROM, TO)) %>%
  summarise(COUNT = sum(COUNT)) %>%
  ungroup

Or in base R -

aggregate(COUNT~FROM + TO, 
           transform(df, FROM = pmin(FROM, TO), TO = pmax(FROM, TO)), sum)

#  FROM TO COUNT
#1    A  B     7
#2    C  D    10
#3    X  Y     6
user_39629140

We can use data.table

library(data.table)
setDT(df)[, .(COUNT = sum(COUNT)), .(col1 = pmin(FROM, TO), col2 = pmax(FROM, to))]
user_37322710
Related