Canovice Canovice - 3 months ago 13
R Question

R dataframe - using common value in one column to find values in another column

Sorry I couldn't come up with a better name for the title, I am struggling very much with explaining the problem at hand with words so let me show an example with a dataframe instead.

dput(DF)

structure(list(game_id = c(162044, 162044, 162044, 162044, 162044,
162044, 162044, 162044, 162044, 162044, 162044, 162044, 162044,
162044, 162044, 166807, 166807, 166807, 166807, 166807, 166807,
166807, 166807, 166807, 166807, 166807, 166807, 166807, 166807,
166807, 166807, 166807, 166807, 166807, 167948, 167948, 167948,
167948, 167948, 167948, 167948, 167948, 167948, 167948, 167948,
167948, 167948, 167948, 167948, 167948), team_id = c(108738,
108738, 108738, 108738, 108738, 108738, 108738, 126351, 126351,
126351, 126351, 126351, 126351, 126351, 126351, 54161, 54161,
54161, 54161, 54161, 54161, 54161, 54161, 133418, 133418, 133418,
133418, 133418, 133418, 133418, 133418, 133418, 54161, 54161,
54161, 54161, 54161, 54161, 54161, 54161, 54161, 135796, 135796,
135796, 135796, 135796, 135796, 135796, 135796, 135796)), .Names = c("game_id",
"team_id"), row.names = c(NA, 50L), class = "data.frame")


head(DF)

game_id team_id
1 162044 108738
2 162044 108738
3 162044 108738
4 162044 108738
5 162044 108738
6 162044 108738
7 162044 108738
8 162044 126351
9 162044 126351
10 162044 126351
11 162044 126351
12 162044 126351
13 162044 126351
14 162044 126351
15 162044 126351
16 166807 54161
17 166807 54161
18 166807 54161
19 166807 54161
20 166807 54161
21 166807 54161
22 166807 54161
23 166807 54161
24 166807 133418
25 166807 133418
26 166807 133418
27 166807 133418
28 166807 133418
29 166807 133418
30 166807 133418
31 166807 133418
32 166807 133418
33 166807 54161
34 166807 54161
35 167948 54161
36 167948 54161
37 167948 54161
38 167948 54161
39 167948 54161
40 167948 54161
41 167948 54161
42 167948 135796
43 167948 135796
44 167948 135796
45 167948 135796
46 167948 135796
47 167948 135796
48 167948 135796
49 167948 135796
50 167948 135796


This is the dataframe I am working with. Of importance is the fact that, for each game_id, there are two team_ids. If it helps with the problem, the game_ids are all sorted, and the team_ids could be grouped so that for each game_id, all instances of the first team_id appear, then all instances of the second team_id appear (this is currently not the case, as you can see in rows 33 and 34). I need to create a third column, called team2_id, where in this column are the other team_ids corresponding to the game_id. Example of desired output is below:

head(DF)

game_id team_id team2_id
1 162044 108738 126351
2 162044 108738 126351
3 162044 108738 126351
4 162044 108738 126351
5 162044 108738 126351
6 162044 108738 126351
7 162044 108738 126351
8 162044 126351 108738
9 162044 126351 108738
10 162044 126351 108738
11 162044 126351 108738
12 162044 126351 108738
13 162044 126351 108738
14 162044 126351 108738
15 162044 126351 108738
16 166807 54161 133418
17 166807 54161 133418
18 166807 54161 133418
19 166807 54161 133418
20 166807 54161 133418
21 166807 54161 133418
22 166807 54161 133418
23 166807 54161 133418
24 166807 133418 54161
25 166807 133418 54161
26 166807 133418 54161
27 166807 133418 54161
28 166807 133418 54161
29 166807 133418 54161
30 166807 133418 54161
31 166807 133418 54161
32 166807 133418 54161
33 166807 54161 133418
34 166807 54161 133418
35 167948 54161 135796
36 167948 54161 135796
37 167948 54161 135796
38 167948 54161 135796
39 167948 54161 135796
40 167948 54161 135796
41 167948 54161 135796
42 167948 135796 54161
43 167948 135796 54161
44 167948 135796 54161
45 167948 135796 54161
46 167948 135796 54161
47 167948 135796 54161
48 167948 135796 54161
49 167948 135796 54161
50 167948 135796 54161


At first glance, I didn't think I would struggle with this type of data manipulation, however I am unfortunately finding this to be fairly difficult. Any help would be appreciated!

Thanks,

Answer

Here is a solution using tidyr and dplyr.

library(dplyr)
library(tidyr)
teams <- DF %>%
  group_by(game_id, team_id) %>% # For every game_id and team_id
  summarise() %>% # Create one row
  # Grouping is now for every game_id
  mutate(team_number = paste0("team", row_number(), "_id")) %>% # generate the to-be column name of this team_id
  spread(team_number, team_id) # Spread the column names to create the new columns
# Now join the team data to the original DF:
res <- DF %>%
  left_join(teams, by = "game_id")

res will now have the same columns as DF plus two more columns, team1_id and team2_id containing the team_id of the first resp. second team in the game as it appears in the data. It will also have the column team_id wich is equal to either team1_id or team2_id depending on which team the actual player belonged to.

Fun fact: If a game_id has more than two team_ids the code will gracefully create new columns team3_id, team4_id, etc. as needed.