weighted.mean, summarise() and across()

user_14066630 0
Viewed 561

I would like to aggregate the following dataframe (variables y and z) by number and weight it by "weight". This works as follows:

df = data.frame(number=c("a","a","a","b","c","c"), y=c(1,2,3,4,1,7),
                z=c(2,2,6,8,9,1), weight =c(1,1,3,1,2,1))


aggregate = df %>%
  group_by(number) %>%
  summarise_at(vars(y,z), funs(weighted.mean(. , w=weight)))

Since summarise_at should not longer be used, I tried it with across. But I wasn't successful:

aggregate = df %>%
  group_by(number) %>%
  summarise(across(everything(), list( mean = mean, sd = sd)))

# this works for mean but I can't just change it with "weighted.mean" etc.
r
1 Answers

We can pass the anonymous function with ~. By checking the summarise_at, the OP wants to only return the summarisation of columns 'y', 'z', i.e. using everything() would also return the mean, sd and weighted.mean of 'weight' column as well which doesn't make much sense

library(dplyr)
df %>%
  group_by(number) %>%
   summarise(across(c(y, z), 
   list( mean = mean, sd = sd,
            weighted = ~weighted.mean(., w = weight))), .groups = 'drop')
# A tibble: 3 x 7
#  number y_mean  y_sd y_weighted z_mean  z_sd z_weighted
#  <chr>   <dbl> <dbl>      <dbl>  <dbl> <dbl>      <dbl>
#1 a           2  1           2.4   3.33  2.31       4.4 
#2 b           4 NA           4     8    NA          8   
#3 c           4  4.24        3     5     5.66       6.33

Often, the mean and sd works well when there are no NA elements. But if there are NA values, we may need to use na.rm = TRUE (by default it is FALSE. In that case, the lambda call would be useful to pass additional parameters

df %>%
  group_by(number) %>%
   summarise(across(c(y, z), 
   list( mean = ~mean(., na.rm = TRUE), sd = ~sd(., na.rm = TRUE),
            weighted = ~weighted.mean(., w = weight))), .groups = 'drop')
user_37322710
Related