Group by two columns and make third one wide of r data frame

February 28, 2023

I have a data frame that has the following structure:

df <- data.frame("first" = c("A","A","B","B","B"),
           "second" = c("C","C","D","D","Z"),
           "third" = c("E","F","G","H","I"))

  first second third
1     A      C     E
2     A      C     F
3     B      D     G
4     B      D     H
5     B      Z     I

I’m trying to group this by the first two columns and make the third one wide for all the values. So like this:

  first second third fourth
1     A      C     E      F
2     B      D     G      H
3     B      Z     I   <NA>

The new colnames don’t matter though. Just one row for every unique ‘first’ and ‘second’ column with as many new columns as needed. I tried nesting and unnesting wide, but that doesn’t work for me.

>Solution :

One option would be to first add a row identifier per group which could then be used a the names_from argument using tidyr::pivot_wider:

library(tidyr)
library(dplyr)

df |>
  group_by(first, second) |>
  mutate(row = seq(n())) |>
  ungroup() |>
  pivot_wider(names_from = "row", values_from = "third") |>
  rename(third = 3, fourth = 4)
#> # A tibble: 3 × 4
#>   first second third fourth
#>   <chr> <chr>  <chr> <chr> 
#> 1 A     C      E     F     
#> 2 B     D      G     H     
#> 3 B     Z      I     <NA>