How to automatically populate the values of a column into a new data frame?

Question

This is a kind of problem of problem that is generally solved using Microsoft Excel. Since I am not familiarized with VBA and Macros I need to take the help of R.

The data frame looks like this.

df <- data.frame(player_no = c(1,2,3,4,5,6), player = c("Ram", "Shyam", "Jordan", "Chris", "Salim", "Salman"), sold_to = c("Team_1", "Team_2", "Team_3", "Team_2", "Team_3", "Team_1"))

Now I wish to create a new data frame wherein the values of the player_no and the player gets automatically populated into the respective teams.

The pictorial representation of what is expected is shown below:-

The expected output is as follows

#          Team 1                    Team 2              Team 3
# Sno player_no  player  Sno  player_no  player  Sno player_no player
# 1    1          Ram     1     2         Shyam   1   3         Jordan
# 2    6          Salman  2     4         Chris   2   5         Salim

Once the expected output is achieved, is it possible to transfer back the output into an excel file (using writexl package) that mimics the pictorial representation that is mentioned in the question ?

Answer 1

You could use dplyr and tidyr:

df %>%
  pivot_wider(names_from="sold_to", 
              names_glue="{sold_to}_{.value}", 
              values_from=c("player_no", "player"), 
              values_fn=list
              ) %>%
  unnest(everything()) %>%
  select(Team_1_player_no, Team_1_player, 
         Team_2_player_no, Team_2_player, 
         Team_3_player_no, Team_3_player)

which returns not exactly your desired output, but it's quite similar:

# A tibble: 2 x 6
  Team_1_player_no Team_1_player Team_2_player_no Team_2_player Team_3_player_no Team_3_player
             <dbl> <fct>                    <dbl> <fct>                    <dbl> <fct>        
1                1 Ram                          2 Shyam                        3 Jordan       
2                6 Salman                       4 Chris                        5 Salim