Consolidate data to one row per group

Question

I am trying to get my data frame to be more condense. I have two columns, school and color and would like to instead have all the colors for that school in separate columns.

sample data is as follows:

School    Color  
HS  orange  
HS black  
MS black  
MS purple  
MS white  
ES1 black  
ES2 green  
ES2 orange

I would instead like the data to look like:

School Color1 Color2 Color3  
HS orange black NA  
MS black purple white  
ES1 black NA NA  
ES2 green orange NA

Answer 1

dplyr

library(dplyr)
library(tidyr)

dat %>%
  group_by(School) %>%
  mutate(lbl = paste0("Color", row_number())) %>%
  ungroup() %>%
  pivot_wider(names_from = lbl, values_from = Color)
# # A tibble: 4 x 4
#   School Color1 Color2 Color3
#   <chr>  <chr>  <chr>  <chr> 
# 1 HS     orange black  <NA>  
# 2 MS     black  purple white 
# 3 ES1    black  <NA>   <NA>  
# 4 ES2    green  orange <NA>  

data.table

(Note: I'm using magrittr solely to visually break out each step via the %>% operator. This is easily done without that just by data.table-chaining.)

library(magrittr) # just for demonstration, can be done without
library(data.table)

as.data.table(dat) %>%
  .[, lbl := paste0("Color", seq_len(.N)), by = "School"] %>%
  dcast(., School ~ lbl, value.var = "Color")
#    School Color1 Color2 Color3
# 1:    ES1  black   <NA>   <NA>
# 2:    ES2  green orange   <NA>
# 3:     HS orange  black   <NA>
# 4:     MS  black purple  white