How to cross two columns from groupby result

Question

I generated an grouped dataframe using below code

df %>% group_by(stage, delay) 
   %>% summarise(n = n(),
                 mean = mean(eval))

Result:

 stage  delay   mean
     2      3   0.261 
     2      1   0.325 
     2      2  -0.783 
     1      3  0.0226
     1      1  -0.435 
     1      2   0.491

What I m looking for:

                  1       2       3   
       1     -0.435   0.491  0.0226
       2      0.325  -0.783   0.261

akrun · Accepted Answer · 2020-03-08T21:08:04.773

We can add pivot_wider at the end

library(dplyr)
library(tidyr)
df %>%
    group_by(stage, delay) %>%
    summarise(n = n(), mean = mean(eval)) %>%
    pivot_wider(names_from = delay, values_from = mean)

Or with spread

    ... %>%
    spread(delay, mean)

In base R, we can also use xtabs

xtabs(mean ~ stage + delay, out)
# delay
#stage       1       2       3
#    1 -0.4350  0.4910  0.0226
#    2  0.3250 -0.7830  0.2610

data

out <- structure(list(stage = c(2L, 2L, 2L, 1L, 1L, 1L), delay = c(3L, 
1L, 2L, 3L, 1L, 2L), mean = c(0.261, 0.325, -0.783, 0.0226, -0.435, 
0.491)), class = "data.frame", row.names = c(NA, -6L))

neat base R solution, liked it! – ThomasIsCoding Mar 08 '20 at 21:27 — ThomasIsCoding, Mar 08 '20 at 21:27

score 1 · Answer 2 · answered Mar 08 '20 at 21:27

Another base R option is using reshape, i.e.,

dfout <- reshape(df[order(df$stage,df$delay),],
                 direction = "wide",
                 idvar = "stage",
                 timevar = "delay")

such that

> dfout
  stage mean.1 mean.2 mean.3
5     1 -0.435  0.491 0.0226
2     2  0.325 -0.783 0.2610

DATA

df <- structure(list(stage = c(2L, 2L, 2L, 1L, 1L, 1L), delay = c(3L, 
1L, 2L, 3L, 1L, 2L), mean = c(0.261, 0.325, -0.783, 0.0226, -0.435, 
0.491)), class = "data.frame", row.names = c(NA, -6L))

How to cross two columns from groupby result

2 Answers2

data