R apply a sum on dataset values according to all possible combination of character column-CodePudding

I have a dataset that looks like this

data.frame(A = c("a","b","c","d"),B= c(1,2,3,4))
OUTPUT
A  B
a  1
b  2
c  3
d  4

I would like to get a new dataframe with the sum of the element in column B according to the possible combinations of 2 elements in column A, for example

comb_A sum_B
a b    3
b c    5
c d    7 
a d    5
excetera...

I'm new to r, is there any way to do this? Thank you in advance

CodePudding user response：

You may try in base R

df1 <- as.data.frame(t(combn(df$A, 2)))

data.frame(comb_A = paste(df1$V1, df1$V2), comb_B = df$B[match(df1$V1, df$A)]   df$B[match(df1$V2, df$A)])

  comb_A comb_B
1    a b      3
2    a c      4
3    a d      5
4    b c      5
5    b d      6
6    c d      7

CodePudding user response：

A possible solution in base R:

result <- data.frame(
  expand.grid(comb_B = df$A, comb_A = df$A)[2:1], 
  sum = c(outer(df$B, df$B, \(x,y) x y))
)

result <- result[result$comb_A != result$comb_B,]

result

#>    comb_A comb_B sum
#> 2       a      b   3
#> 3       a      c   4
#> 4       a      d   5
#> 5       b      a   3
#> 7       b      c   5
#> 8       b      d   6
#> 9       c      a   4
#> 10      c      b   5
#> 12      c      d   7
#> 13      d      a   5
#> 14      d      b   6
#> 15      d      c   7

CodePudding user response：

Here's one (albeit messy) way to do it.

library(tidyverse)

df <- data.frame(A = c("a","b","c","d"),B= c(1,2,3,4))
df %>% 
  expand(A, A) %>% 
  unite("comb_A", starts_with("A"), sep = " ") %>% 
  mutate(sum_B = map_dbl(
    str_split(comb_A, " "),
    ~sum(df$B[match(.x, df$A)])
  ))
#> # A tibble: 16 × 2
#>    comb_A sum_B
#>    <chr>  <dbl>
#>  1 a a        2
#>  2 a b        3
#>  3 a c        4
#>  4 a d        5
#>  5 b a        3
#>  6 b b        4
#>  7 b c        5
#>  8 b d        6
#>  9 c a        4
#> 10 c b        5
#> 11 c c        6
#> 12 c d        7
#> 13 d a        5
#> 14 d b        6
#> 15 d c        7
#> 16 d d        8