6

I have data where I want to get a bunch of summary statistics for multiple columns with the tidyverse approach. However, utilizing tidyverse's summarize function, it will create each column statistic as a new column, whereas I would prefer to see the column names as rows and each statistic as a new column. So my question is:

Is there a more elegant (and I know "elegant" is a vague term) way to achieve this than by accompanying the summarize function with a pivot_longer and pivot_wider?

I'm using the latest dev versions of the tidyverse package, i.e. dplyr 0.8.99.9003 and tidyr 1.1.0. So it's fine if any solution requires new functions from these packages that are not yet on CRAN.

library(tidyverse)

dat <- as.data.frame(matrix(1:100, ncol = 5))

dat %>%
  summarize(across(everything(), list(mean = mean,
                                      sum  = sum))) %>%
  pivot_longer(cols      = everything(),
               names_sep = "_",
               names_to  = c("variable", "statistic")) %>%
  pivot_wider(names_from = "statistic")

Expected outcome:

# A tibble: 5 x 3
  variable  mean   sum
  <chr>    <dbl> <dbl>
1 V1        10.5   210
2 V2        30.5   610
3 V3        50.5  1010
4 V4        70.5  1410
5 V5        90.5  1810

Note: I'm not set on the name of any of the columns, so if there's a nice way to get the structure of the table with different/generic names, that'd also be fine.

deschen
  • 10,012
  • 3
  • 27
  • 50

3 Answers3

5

You can skip the pivot_wider step by using ".value" in names_to. 

library(dplyr)

dat %>%
  summarise_all(list(mean = mean,sum  = sum)) %>%
  tidyr::pivot_longer(cols = everything(),
               names_sep = "_",
               names_to  = c("variable", ".value"))


# A tibble: 5 x 3
#  variable  mean   sum
#  <chr>    <dbl> <int>
#1 V1        10.5   210
#2 V2        30.5   610
#3 V3        50.5  1010
#4 V4        70.5  1410
#5 V5        90.5  1810
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
4

not a tidyverse solution, but a data.table one instead.. also, not sure if it is more 'elegant' ;-)

but here you go...

library( data.table )
#make 'dat' a data.table
setDT(dat)
#transpose, keeping column names
dat <- transpose(dat, keep.names = "var_name" )
#melt to long and summarise
melt(dat, id.vars = "var_name")[, .(mean = mean(value), sum = sum(value) ), by = var_name]


#    var_name mean  sum
# 1:       V1 10.5  210
# 2:       V2 30.5  610
# 3:       V3 50.5 1010
# 4:       V4 70.5 1410
# 5:       V5 90.5 1810
Wimpel
  • 26,031
  • 1
  • 20
  • 37
2

You can first stack all columns together and summarise by group.

dat %>%
  pivot_longer(everything()) %>%
  group_by(name) %>% 
  summarise_at("value", list(~mean(.), ~sum(.)))

# # A tibble: 5 x 3
#   name   mean   sum
#   <chr> <dbl> <int>
# 1 V1     10.5   210
# 2 V2     30.5   610
# 3 V3     50.5  1010
# 4 V4     70.5  1410
# 5 V5     90.5  1810
Darren Tsai
  • 32,117
  • 5
  • 21
  • 51
  • It was close, but I voted for Ronak Shah's answer as the accepted one because summarize_at gets deprecated and when using the summarize(across(c("value"),...) replacement I'm struggling with getting the right column names ("mean", "sum"), although I like the neatness of your approach. – deschen May 27 '20 at 12:53