Grouping based on two variables, including their individual combinations (e.g. A - B is same than B - A)

Question

I got stuck during an coding problem at work. I have a data frame with three variables var1 and var2 and length. The latter is the mutual length between var1 and var2, e.g. a boundary.

Ultimately I want to calculate the percentage of each combination of var1 - var2 (var2 - var1 is regarded as identical) on the total length of each unique element in either var1 and var2. Because this sounds too complicated I have made some examples to show where I am stuck.

library(tidyverse)

df <- tibble( 
  var1 = c("A","B","A","D","A"),
  var2 = c("B","A","D","A","B"),
  Length = c(10,12,5,20,34))



#First I wanted the total length of each variable, irrespective of it occurring in var1 or var2  
# I think that I figured this out. Let me know it its a bit convoluted

  var_unique <- unique(c(unique(df$var1),unique(df$var2)))
  names(var_unique) <- var_unique

 total_length <- map_df(var_unique, function(x){

    df %>%
      filter( var1 == x | var2 == x )%>%
     summarise(var_total_length = sum(Length))

  },.id = "var" )

 total_length
#> # A tibble: 3 x 2
#>   var   var_total_length
#>   <chr>            <dbl>
#> 1 A                   81
#> 2 B                   56
#> 3 D                   25

 # Second I need the length of each combination of var1 and var2.
 #I would like the "A" - "B" should be the same than "B" - "A"
 # Grouping does not work in this case. This is where I am stuck

 #Neiter this

 df %>% group_by(var1,var2) %>%
   mutate(combination_length = sum(Length))
#> # A tibble: 5 x 4
#> # Groups:   var1, var2 [4]
#>   var1  var2  Length combination_length
#>   <chr> <chr>  <dbl>              <dbl>
#> 1 A     B         10                 44
#> 2 B     A         12                 12
#> 3 A     D          5                  5
#> 4 D     A         20                 20
#> 5 A     B         34                 44

 # nor that one does the job, because it looks at individual combination of var1 and var2.

 df %>% group_by(var1,var2) %>%
   summarise(combination_length = sum(Length))
#> # A tibble: 4 x 3
#> # Groups:   var1 [3]
#>   var1  var2  combination_length
#>   <chr> <chr>              <dbl>
#> 1 A     B                     44
#> 2 A     D                      5
#> 3 B     A                     12
#> 4 D     A                     20



 # this is the dataframe that I would like. Rows 1,2 and 5 of df should be considered the 
 # same group

tibble( 
   var1 = c("A","B","A","D","A"),
   var2 = c("B","A","D","A","B"),
   Length = c(10,12,5,20,34),
   combination_length = c(56,56,25,25,56))
#> # A tibble: 5 x 4
#>   var1  var2  Length combination_length
#>   <chr> <chr>  <dbl>              <dbl>
#> 1 A     B         10                 56
#> 2 B     A         12                 56
#> 3 A     D          5                 25
#> 4 D     A         20                 25
#> 5 A     B         34                 56



 # Ultimately i want to divide each combination by the total length of the variable 
 # occurring in the combination to obtain the percentage of each boundary for each unique variable

^{Created on 2019-11-27 by the reprex package (v0.3.0)}

I assume there are ways to make it less complex than I try to do it.

Ronak Shah Ronak Shah · Accepted Answer · 2019-11-27T11:28:54

We can use sorted var1, var2 in group_by which can be done using pmax and pmin

library(dplyr)

df %>%
  group_by(group1 =  pmin(var1, var2), group2 = pmax(var1, var2)) %>%
  mutate(combination_length = sum(Length)) %>%
  ungroup() %>%
  select(-group1, -group2)

# var1  var2  Length combination_length
#  <chr> <chr>  <dbl>              <dbl>
#1 A     B         10                 56
#2 B     A         12                 56
#3 A     D          5                 25
#4 D     A         20                 25
#5 A     B         34                 56

Grouping based on two variables, including their individual combinations (e.g. A - B is same than B - A)

2 Answers