Using pivot wider to add columns with unique values by group

December 17, 2023

This should be a simple task. The aim is to take each Team value in my df and create two new cols based on the id column.

My data:

id <- c("1", "1", "1", "1", "2", "2", "2", "2")
Team <- c("Team_A", "Team_A", "Team_B", "Team_B", "Team_C", "Team_C", "Team_D", "Team_D")

df <- data.frame(id, Team)

Output:

  id   Team
1  1 Team_A
2  1 Team_A
3  1 Team_B
4  1 Team_B
5  2 Team_C
6  2 Team_C
7  2 Team_D
8  2 Team_D

What is should look like:

Team_1 <- c("Team_A", "Team_A", "Team_A", "Team_A", "Team_C", "Team_C", "Team_C", "Team_C")
Team_2 <- c("Team_B", "Team_B", "Team_B", "Team_B", "Team_D", "Team_D", "Team_D", "Team_D")

df <- data.frame(id, Team, Team_1, Team_2)

Output:

  id   Team Team_1 Team_2
1  1 Team_A Team_A Team_B
2  1 Team_A Team_A Team_B
3  1 Team_B Team_A Team_B
4  1 Team_B Team_A Team_B
5  2 Team_C Team_C Team_D
6  2 Team_C Team_C Team_D
7  2 Team_D Team_C Team_D
8  2 Team_D Team_C Team_D

So, e.g. for id 1, there were only two teams Team_A and Team_B and so on.

I tried pivot_wider and spread but I still could not manage this. I often got the error Values from Team are not uniquely identified; output will contain list-cols.

>Solution :

 df %>%
    mutate(Team_ = list(unique(Team)), .by=id) %>%
    unnest_wider(Team_, names_sep ='')

# A tibble: 8 × 4
  id    Team   Team_1 Team_2
  <chr> <chr>  <chr>  <chr> 
1 1     Team_A Team_A Team_B
2 1     Team_A Team_A Team_B
3 1     Team_B Team_A Team_B
4 1     Team_B Team_A Team_B
5 2     Team_C Team_C Team_D
6 2     Team_C Team_C Team_D
7 2     Team_D Team_C Team_D
8 2     Team_D Team_C Team_D