Pivoting a dataframe by grouping column values as individual columns, using dplyr

Question

First time poster - pls be nice.

I have a df with 2 columns, which I would like to pivot such that each unique value in one of the columns become the column names of the new df with its associated values in the cells of that column.

Consider starwars as an example...

starwars %>% 
select(name, homeworld)

# A tibble: 87 x 2
   name               homeworld
   <chr>              <chr>    
 1 Luke Skywalker     Tatooine 
 2 C-3PO              Tatooine 
 3 R2-D2              Naboo    
 4 Darth Vader        Tatooine 
 5 Leia Organa        Alderaan 
 6 Owen Lars          Tatooine 
 7 Beru Whitesun lars Tatooine 
 8 R5-D4              Tatooine 
 9 Biggs Darklighter  Tatooine 
10 Obi-Wan Kenobi     Stewjon  
# … with 77 more rows

... I would like to pivot the df such that each homeworld is a column, and the names of characters from that homeworld below. Of course, there will be some columns with more observations than others - I am ok for these to be NAs to fill the df.

Playing with pivot_wider, and using an answer to a similar question, I have managed to get homeworlds as rows, not columns...

starwars %>%
select(name, homeworld) %>%
group_by(homeworld) %>%
mutate(rn = row_number()) %>%
ungroup() %>%
pivot_wider(names_from = rn, values_from = name)

# A tibble: 49 x 12
   homeworld  `1`                 `2`               `3`            `4`         `5`               `6`      `7`             `8`            `9`          `10`       `11`       
   <chr>      <chr>               <chr>             <chr>          <chr>       <chr>             <chr>    <chr>           <chr>          <chr>        <chr>      <chr>      
 1 Tatooine   Luke Skywalker      C-3PO             Darth Vader    Owen Lars   Beru Whitesun la… R5-D4    Biggs Darkligh… Anakin Skywal… Shmi Skywal… Cliegg La… NA         
 2 Naboo      R2-D2               Palpatine         Jar Jar Binks  Roos Tarpa… Rugor Nass        Ric Olié Quarsh Panaka   Gregar Typho   Cordé        Dormé      Padmé Amid…
 3 Alderaan   Leia Organa         Bail Prestor Org… Raymus Antill… NA          NA                NA       NA              NA             NA           NA         NA         
 4 Stewjon    Obi-Wan Kenobi      NA                NA             NA          NA                NA       NA              NA             NA           NA         NA         
 5 Eriadu     Wilhuff Tarkin      NA                NA             NA          NA                NA       NA              NA             NA           NA         NA         
 6 Kashyyyk   Chewbacca           Tarfful           NA             NA          NA                NA       NA              NA             NA           NA         NA         
 7 Corellia   Han Solo            Wedge Antilles    NA             NA          NA                NA       NA              NA             NA           NA         NA         
 8 Rodia      Greedo              NA                NA             NA          NA                NA       NA              NA             NA           NA         NA         
 9 Nal Hutta  Jabba Desilijic Ti… NA                NA             NA          NA                NA       NA              NA             NA           NA         NA         
10 Bestine IV Jek Tono Porkins    NA                NA             NA          NA                NA       NA              NA             NA           NA         NA         
# … with 39 more rows

... what I would like is this but rotated 90°.

I'd really appreciate any help, ideally sticking with dplyr / tidyverse language.

Answer 1

names_from takes the value of the column which we want as column names in wide format and values_to takes the values of those columns. Here we can use names_from as homeworld as we want those values as column name.

library(dplyr)
library(tidyr)

starwars %>%
  select(name, homeworld) %>%
  group_by(homeworld) %>%
  mutate(rn = row_number()) %>%
  ungroup() %>%
  pivot_wider(names_from = homeworld, values_from = name) %>%
  select(-rn)