Create new columns from existing column values in R (Using other column as key)

Question

I'm trying to reshape a dataframe, creating new columns from a 2 column dataframe, but i'm kind of lost here.

The original dataframe looks like this:

Name         | Roles
--------------------------------
John         | Account Creator
John         | Security Manager
John         | E-mail Admin
Paul         | Account Creator
Mary         | Security Manager
Mary         | E-mail Admin
Erick        | Ticket Manager
Erick        | E-mail Admin
--------------------------------

I'm trying to turn it into this:

Name         | Role_01          | Role_02          | Role_03
-------------------------------------------------------------------
John         | Account Creator  | Security Manager | E-mail Admin 
Paul         | Account Creator  |                  |
Mary         | Security Manager | E-mail Admin     |
Erick        | Ticket Manager   | E-mail Admin     |  
-------------------------------------------------------------------

The problem is that the number of Roles is variable. In my example the maximum is 3, but the real dataframe sometime has 19 different roles (maybe more in the future).

I had tried to use spread (and pivot_wider), but i have discovered that it doesn't work in this case. =(

Is there any way to make it work? I guess my brain stopped working.

Regards,

Answer 1

First, add a counter for each Name. Then, you can use pivot_wider:

library(tidyverse)

df %>%
  group_by(Name) %>%
  mutate(Count = 1:n()) %>%
  pivot_wider(id_cols = Name, 
              names_from = Count, 
              values_from = Roles, 
              names_prefix = "Role_")

Output

  Name  Role_1           Role_2           Role_3      
  <chr> <chr>            <chr>            <chr>       
1 John  Account Creator  Security Manager E-mail Admin
2 Paul  Account Creator  NA               NA          
3 Mary  Security Manager E-mail Admin     NA          
4 Erick Ticket Manager   E-mail Admin     NA