Better output with dplyr -- breaking functions and results

Question

This is a long-lasting question, but now I really to solve this puzzle. I'm using dplyr all the time and I think it is great to summarise variables. However, I'm trying to display a pivot table with partial success only. Dplyr always reports one single row with all results, what's annoying. I have to copy-paste the results to excel to organize everything...

I got the code here and it almost working.

This result

Should be like the following one:

Because I always report my results using this style

Use this code to get the same results:

library(tidyverse) 
set.seed(123)
ds <- data.frame(group=c("american", "canadian"), 
                 iq=rnorm(n=50,mean=100,sd=15),
                 income=rnorm(n=50, mean=1500, sd=300),
                 math=rnorm(n=50, mean=5, sd=2))
ds %>% 
  group_by(group) %>% 
   summarise_at(vars(iq, income, math),funs(mean, sd)) %>%
  t %>% 
  as.data.frame %>% 
  rownames_to_column %>%
  separate(rowname, into = c("feature", "fun"), sep = "_")

To clarify, I've tried this code, but spread works with only one summary (mean or sd, etc). Some people use gather(), but it's complicated to work with group_by and gather().

Thanks for any help.

Your expected output doesn't look fine to me. The first row is character class — akrun
Try ds %>% group_by(group) %>% summarise_at(vars(iq, income, math),funs(mean, sd)) %>% gather(key, val, iq_mean:math_sd) %>% separate(key, into = c('key1', 'key2')) %>% unite(group, group, key2) %>% spread(group, val) — akrun
Thanks, you are always helping me. I added a new image to clarify my question. — Luis
Thanks, I checked it. In excel you can can have multiple column headers, but, in R, it is better to have a single column header to not having issues with the type of the column — akrun
Hey, your code worked !! There are some way to adapt your code to my old syntax ? I change the variables to test via function: var_to_test <- c("iq", "income", "math") ds %>% group_by(group) %>% summarise_at(vars(var_to_test),funs(mean, sd)) %>% gather(key, val, var_to_test) %>% separate(key, into = c('key1', 'key2')) %>% unite(group, group, key2) %>% spread(group, val) — Luis

akrun akrun · Accepted Answer · 2018-07-17T03:45:43

Instead of transposing (t) and changing the class types, after the summarise step, do a gather to change it to 'long' format and then spread it back after doing some modifications with separate and unite

library(tidyverse)
ds %>% 
  group_by(group) %>% 
  summarise_at(vars(iq, income, math),funs(mean, sd)) %>% 
  gather(key, val, iq_mean:math_sd) %>% 
  separate(key, into = c('key1', 'key2')) %>% 
  unite(group, group, key2) %>%
  spread(group, val)

Better output with dplyr -- breaking functions and results

1 Answers