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.
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