R dplyr pivot (rankings table) – new column with unique row values from multiple columns, then assign row values from another column

Let’s say we have the following

tibble (
      rank = c(1, 2, 3), 
      race_A = c("John", "Nick", "George"),
      race_B = c("Jay", "Nick", "John"),
      race_C = c("Jay", "Jack", "Robbie")
    )

which generates this tibble

# A tibble: 3 × 4
   rank race_A race_B race_C
  <dbl> <chr>  <chr>  <chr> 
1     1 John   Jay    Jay   
2     2 Nick   Nick   Jack  
3     3 George John   Robbie

How do i transform it to the following

# A tibble: 3 × 4
   racer race_A race_B race_C
  <dbl> <chr>  <chr>  <chr> 
1 John   1       3      NA
2 Nick   2       2      NA
3 George 3       NA     NA
4 Jay    NA      1      1
5 John   NA      3      NA
6 Jack   NA      NA     2
7 Robbie NA      NA     3   

>Solution :

df %>%  
  pivot_longer(-rank) %>% 
  pivot_wider(names_from = name, 
              values_from = rank) %>% 
  arrange(race_A)

# A tibble: 6 × 4
  value  race_A race_B race_C
  <chr>   <dbl>  <dbl>  <dbl>
1 John        1      3     NA
2 Nick        2      2     NA
3 George      3     NA     NA
4 Jay        NA      1      1
5 Jack       NA     NA      2
6 Robbie     NA     NA      3