Count occurrences in any order

user_6224926 0
Viewed 62

Imagine I have a data frame that looks like this:

df <- data.frame(
  name1 = c("John", "Jake", "Ann", "Mary", "Harry"),
  name2 = c("Jake", "John", "Mary", "Ann", "Mary"),
  value = c(2, 4, 6, 8, 10)
)

I want to create a new data frame that sums value over any rows where name1 and name2 and the same regardless of their order. So my result would be:

result <- data.frame(
  name1 = c("John", "Ann", "Harry"),
  name2 = c("Jake", "Mary", "Mary"),
  value = c(6, 14, 10) 
)

Any clever ideas, preferably using tidyverse? Thanks so much!

dplyr dataframe r
3 Answers

You may use pmin and pmax which sorts the data alphabetically and take sum of the value.

library(dplyr)

df %>%
  group_by(nam1 = pmin(name1, name2), nam2 = pmax(name1, name2)) %>%
  summarise(value = sum(value), .groups = 'drop')

#  nam1  nam2  value
#  <chr> <chr> <dbl>
#1 Ann   Mary     14
#2 Harry Mary     10
#3 Jake  John      6
user_39629140

An option with igraph package

library(igraph)

get.data.frame(
  simplify(
    graph_from_data_frame(df, directed = FALSE),
    edge.attr.comb = sum
  )
)

gives

  from    to value
1 John  Jake     6
2  Ann  Mary    14
3 Mary Harry    10
user_121587570

We can use

library(data.table)

setDT(df)[,.(value = sum(value)), by = .(nam1 = pmin(name1, name2), nam2 = pmax(name1, name2))]
user_37322710
Related