How to compute on columns pairwise, from two groups with dplyr

Question

I have a dataset of this shape.

group   a1   a2   ...   a9   b1   b2 ... b7
1       1    0    ...   1    0    1  ... 1
1       1    1    ...   1    0    0  ... 1
1       0    0    ...   0    1    0  ... 1
1       1    1    ...   0    1    1  ... 0
2       1    0    ...   1    0    1  ... 1
2       1    1    ...   1    0    0  ... 1
2       0    0    ...   0    1    0  ... 1
2       1    1    ...   0    1    1  ... 0
...

and what I'd like to do is apply a two-argument summary function to all pairs of columns, maintaining the grouped nature of the data.

So, for example

f = function(a, b) { mean(a) + mean(b) + mean(a & b) }

would return something like (I'm not actually going to compute the value of the function, I'll just put "x" to indicate where the stat would go, but of course it would be different for each group-a-b combination).

group a_col  b_col  stat
1     a1     b1     x
1     a1     b2     x
1     a1     b3     x
...
1     a9     b7     x
2     a1     b1     x
...

A commenter asked for some sample data. Here it is:

structure(list(group = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 
3L, 3L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 
7L, 7L, 7L, 7L, 7L, 7L, 8L, 8L, 8L, 9L, 10L, 10L), a1 = c(0L, 
1L, 1L, 1L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 
1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 
1L, 0L, 0L, 0L), a2 = c(0L, 0L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 
0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 
0L, 0L, 1L, 1L, 1L, 0L, 0L, 1L, 0L, 0L, 1L), a3 = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 
1L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 
0L, 0L), a4 = c(0L, 0L, 1L, 0L, 0L, 1L, 1L, 0L, 1L, 1L, 0L, 1L, 
1L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 
0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L), a5 = c(1L, 0L, 0L, 0L, 0L, 
0L, 1L, 1L, 1L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 
0L, 1L, 0L, 0L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 0L, 0L
), b1 = c(1L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 
0L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 0L, 0L, 
0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L), b2 = c(0L, 0L, 1L, 0L, 0L, 0L, 
1L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 
1L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 1L, 1L, 1L, 0L, 0L), 
    b3 = c(0L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 
    1L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 
    1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-37L))

can you add reproducible data? Its no fun to create some for you. — Roman, Jul 24 '18 at 12:27

score 3 · Accepted Answer · answered Jul 31 '18 at 08:20

A solution using tidyverse. We can gather the columns based on the starting letters twice and then conduct the operation. Assuming your data is called dat, dat2 is the final output.

library(tidyverse)

dat2 <- dat %>%
  gather(column_a, value_a, starts_with("a")) %>%
  gather(column_b, value_b, starts_with("b")) %>%
  group_by(group, column_a, column_b) %>%
  summarise(stat = mean(value_a) + mean(value_b) + mean(value_a + value_b)) %>%
  ungroup()
dat2
# # A tibble: 150 x 4
#    group column_a column_b  stat
#    <int> <chr>    <chr>    <dbl>
#  1     1 a1       b1         3  
#  2     1 a1       b2         2  
#  3     1 a1       b3         2  
#  4     1 a2       b1         2  
#  5     1 a2       b2         1  
#  6     1 a2       b3         1  
#  7     1 a3       b1         3.5
#  8     1 a3       b2         2.5
#  9     1 a3       b3         2.5
# 10     1 a4       b1         2  
# # ... with 140 more rows

I have never used gather twice in a row like that before -- very nice -- thank you! — rcorty, Jul 31 '18 at 16:52

How to compute on columns pairwise, from two groups with dplyr

1 Answers1