Sum by aggregating complex paired names in R

user_3902509 0
Viewed 44

In R, I'm trying to aggregate a dataframe based on unique IDs, BUT I need to use some kind of wild card value for the IDs. Meaning I have paired names like this:

lion_tiger
elephant_lion
tiger_lion

And I need the lion_tiger and tiger_lion IDs to be summed together, because the order in the pair does not matter.

Using this dataframe as an example:

df <- data.frame(pair = c("1_3","2_4","2_2","1_2","2_1","4_2","3_1","4_3","3_2"), 
             value = c("12","10","19","2","34","29","13","3","14"))

So the values for pair IDs, "1_2" and "2_1" need to be summed in a new table. That new row would then read:

1_2 36

Any suggestions? While my example has numbers as the pair IDs, in reality I would need this to read in text (like the lion_tiger" example above).

dplyr plyr r
2 Answers

We can split the 'pair' column by _, then sort and paste it back, use it in a group by function to get the sum

tapply(as.numeric(as.character(df$value)), 
    sapply(strsplit(as.character(df$pair), '_'), function(x) 
     paste(sort(as.numeric(x)), collapse="_")), FUN = sum)

Or another option is gsubfn

library(gsubfn)
df$pair <- gsubfn('([0-9]+)_([0-9]+)', ~paste(sort(as.numeric(c(x, y))), collapse='_'),  
      as.character(df$pair))
df$value <- as.numeric(as.character(df$value))
aggregate(value~pair, df, sum)
user_37322710

Using tidyverse and purrrlyr

df <- data.frame(name=c("lion_tiger","elephant_lion",
            "tiger_lion"),value=c(1,2,3),stringsAsFactors=FALSE) 

require(tidyverse)
require(purrrlyr)
df %>% separate(col = name, sep = "_", c("A", "B")) %>% 
        by_row(.collate = "rows", 
            ..f = function(this_row) {
                  paste0(sort(c(this_row$A, this_row$B)), collapse = "_")
            }) %>% 
        rename(sorted = ".out") %>%
        group_by(sorted) %>%
        summarize(sum(value))%>%show
## A tibble: 2 x 2
#  sorted `sum(value)`
#  <chr>        <dbl>
#1 elephant_lion   2
#2 lion_tiger      4
user_44948620
Related