R: calculate groupwise means with overlapping groups

user_7707288 0
Viewed 75

I have the following table with sample data:

set.seed(10)
dat <- data.frame(grp1 = sample(c(0, 1), size = 5, replace = TRUE),
                   grp2 = sample(c(0, 1), size = 5, replace = TRUE),
                   grp3 = sample(c(0, 1), size = 5, replace = TRUE),
                   value = round(runif(5, min = 0, max = 10), 0) )
dat
  grp1 grp2 grp3 value
1    0    0    0     4
2    0    1    1     1
3    1    1    0     3
4    1    0    0     4
5    1    0    1     8

But in the end, I need a tibble like this:

group              mean
-----------------------
grp1                5.0    
grp2                2.0
grp3                4.5

Usually, I would do a pivot_longer(cols = starts_with("grp")), so I could easily group_by(name) and calculate the groupwise means using summarise(mean = mean(value, na.rm=TRUE)). But here my problem is, that each of the 5 elements can belong to multiple groups (grp1, grp2, grp3), so there's some overlapping between the groups. My naive attempt would be to calculate the means for each group separately and then rbind() them. But as the number of groups increases this gets annoying, so I am looking for a way to automate this.

dplyr tidyverse r
5 Answers

An idea can be,

library(dplyr)
library(tidyr)

dat %>% 
 pivot_longer(grp1:grp3, names_repair = 'unique') %>% 
 filter(value...3 == 1) %>% 
 group_by(name) %>% 
 summarise(means = mean(value...1))

New names:
* value -> value...1
* value -> value...3
# A tibble: 3 x 2
  name             means
  <chr>             <dbl>
1 grp1                5  
2 grp2                2  
3 grp3                4.5
user_56355800

Here is one option. The solution is mainly based on base R. I only created a tibble using dplyr in the end.

library(dplyr)

dat2 <- dat[, -ncol(dat)] * dat[, ncol(dat)]

dat3 <- sapply(dat2, function(x) mean(x[x != 0]))

dat4 <- tibble(
  group = names(dat3),
  mean = dat3
)

dat4
# # A tibble: 3 x 2
#   group  mean
#   <chr> <dbl>
# 1 grp1    5  
# 2 grp2    2  
# 3 grp3    4.5
user_76698090 
colSums(dat$value * dat[-ncol(dat)]) / colSums(dat[-ncol(dat)])
data.frame(grp = names(d), mean = unname(d))

#   grp mean
#1 grp1  5.0
#2 grp2  2.0
#3 grp3  4.5
user_134606020

Using base R

stack(colMeans(replace(dat[-4], dat[-4] == 0, NA) * dat$value, na.rm = TRUE))[2:1]
   ind values
1 grp1    5.0
2 grp2    2.0
3 grp3    4.5
user_37322710

Another base R option (similar to the answer by @akrun)

> rev(stack(colMeans((NA^(1 - dat[startsWith(names(dat), "grp")])) * dat$value, na.rm = TRUE)))
   ind values
1 grp1    5.0
2 grp2    2.0
3 grp3    4.
user_121587570
Related