Pivoting a table without key-value pairs

Question

I have a dataframe in form of

table_name <- c("city", "city", "city", "car", "car", "country", "country", "country", "country", "country")
column <- c("Vienna", "Paris", "London", "Honda", "Nissan", "Germany", "Spain", "Italy", "Portugal", "France")

df <- data.frame(table_name, column)

   table_name   column
1        city   Vienna
2        city    Paris
3        city   London
4         car    Honda
5         car   Nissan
6     country  Germany
7     country    Spain
8     country    Italy
9     country Portugal
10    country   France

and I would like to widen this table and get it in this format:

  table_name  column1   column2  column3  column4    column5                                 
1 "city"      "Vienna"  "Paris"  "London" ""         ""      
2 "car"       "Honda"   "Nissan" ""       ""         ""      
3 "country"   "Germany" "Spain"  "Italy"  "Portugal" "France"

Is there an elegant way to do this with reshape2 or tidyr? I dont mind if there are empty string or NULL values. The naming of the new column should be simply adding the next integer, or something.

It's not in the form of key-value pairs, so I am struggling a little bit.

Answer 1

We can create a sequence column and use pivot_wider

library(dplyr)
library(tidyr)
library(stringr)
library(data.table)
df %>%
   mutate(col1 = str_c('column', rowid(table_name))) %>% 
   pivot_wider(names_from = col1, values_from = column)
# A tibble: 3 x 6
#  table_name column1 column2 column3 column4  column5
#  <fct>      <fct>   <fct>   <fct>   <fct>    <fct>  
#1 city       Vienna  Paris   London  <NA>     <NA>   
#2 car        Honda   Nissan  <NA>    <NA>     <NA>   
#3 country    Germany Spain   Italy   Portugal France 

---

Or using dcast from data.table

library(data.table)
dcast(setDT(df), table_name ~ paste0('column',
       rowid(table_name)), value.var = 'column')
#  table_name column1 column2 column3  column4 column5
#1:        car   Honda  Nissan    <NA>     <NA>    <NA>
#2:       city  Vienna   Paris  London     <NA>    <NA>
#3:    country Germany   Spain   Italy Portugal  France