R - Collapsing observations and creating new columns based on multiple columns

Question

In my dataframe there are multiple rows for a single observation (each referenced by ref). I would like to collapse the rows and create new columns for the keyword and the reg_birth columns.

df1 <- structure(list(rif = c("text10", "text10", "text10", "text11", "text11"), 
                      date = c("20180329", "20180329", "20180329", "20180329", "20180329"), 
                      keyword = c("Lucca", "Piacenza", "Milano", "Cascina", "Padova"), 
                      reg_birth = c("Tuscany", "Emilia", "Lombardy", "Veneto", "Veneto")), 
                 row.names = c(NA, 5L), class = "data.frame")

If I just want to create the columns for the 'keyword' column I used this code, as shown in this answer.

df1 %>% group_by(rif,date) %>% 
mutate(n = row_number()) %>% 
pivot_wider(id_cols = c(rif,date), values_from = keyword, names_from = n, names_prefix = 'keyword')

However, I don't know how to do the same for an additional column (reg_birth in this case).

This is my expected output

     rif keyword reg.birth keyword2 reg_birth2 keyword3 reg_birth3
1 text10   Lucca   Tuscany Piacenza     Emilia   Milano   Lombardy
2 text11 Cascina    Veneto   Padova     Veneto     <NA>       <NA>

Thank you.

I just included it in the question – Jasper Oct 08 '21 at 10:28 — Jasper, Oct 08 '21 at 10:28

score 1 · Accepted Answer · answered Oct 08 '21 at 10:36

You may try with pivot_wider from tidyr.

library(dplyr)
library(tidyr)

df1 %>%
  mutate(id = data.table::rowid(rif, date)) %>%
  pivot_wider(names_from = id, values_from = c(keyword, reg_birth))

#  rif    date     keyword_1 keyword_2 keyword_3 reg_birth_1 reg_birth_2 reg_birth_3
#  <chr>  <chr>    <chr>     <chr>     <chr>     <chr>       <chr>       <chr>      
#1 text10 20180329 Lucca     Piacenza  Milano    Tuscany     Emilia      Lombardy   
#2 text11 20180329 Cascina   Padova    NA        Veneto      Veneto      NA

R - Collapsing observations and creating new columns based on multiple columns

1 Answers1