I have a data frame that is formatted like so:
GameId | colour | whiteElo | blackElo |
---|---|---|---|
1 | white | 2281 | 2293 |
1 | black | 2281 | 2293 |
4 | white | 2670 | 2595 |
4 | black | 2670 | 2595 |
6 | white | 2525 | 2470 |
6 | black | 2525 | 2470 |
and so on...
It's dput() output for the first 10 rows is as follows:
structure(list(GameId = c(1L, 1L, 4L, 4L, 6L, 6L, 10L, 10L, 13L,
13L), colour = c("white", "black", "white", "black", "white",
"black", "white", "black", "white", "black"), whiteElo = c(2281,
2281, 2670, 2670, 2525, 2525, 2315, 2315, 2170, 2170), blackElo = c(2293,
2293, 2595, 2595, 2470, 2470, 2340, 2340, 2155, 2155)), class = c("grouped_df",
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -10L), groups = structure(list(
GameId = c(1L, 4L, 6L, 10L, 13L), .rows = structure(list(
1:2, 3:4, 5:6, 7:8, 9:10), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -5L), .drop = TRUE))
I want to create a column which inserts the corresponding colour's Elo into that row to create a format like so:
GameId | colour | whiteElo | blackElo | EloRating |
---|---|---|---|---|
1 | white | 2281 | 2293 | 2281 |
1 | black | 2281 | 2293 | 2293 |
4 | white | 2670 | 2595 | 2670 |
4 | black | 2670 | 2595 | 2595 |
6 | white | 2525 | 2470 | 2525 |
6 | black | 2525 | 2470 | 2470 |
The data is structured as white black white black ect.
Thanks in advance for the help!
CodePudding user response:
df %>%
mutate(EloRating = ifelse(colour == "white", whiteElo, blackElo))
# # A tibble: 10 × 5
# # Groups: GameId [5]
# GameId colour whiteElo blackElo EloRating
# <int> <chr> <dbl> <dbl> <dbl>
# 1 1 white 2281 2293 2281
# 2 1 black 2281 2293 2293
# 3 4 white 2670 2595 2670
# 4 4 black 2670 2595 2595
# 5 6 white 2525 2470 2525
# 6 6 black 2525 2470 2470
# 7 10 white 2315 2340 2315
# 8 10 black 2315 2340 2340
# 9 13 white 2170 2155 2170
# 10 13 black 2170 2155 2155
CodePudding user response:
The data seems to be grouped. It may be better to ungroup
first for efficiency. An option is also with case_when
library(dplyr)
df1 %>%
ungroup %>%
mutate(EloRating = case_when(colour == "white" ~ whiteElo, TRUE ~ blackElo))
-output
# A tibble: 10 × 5
GameId colour whiteElo blackElo EloRating
<int> <chr> <dbl> <dbl> <dbl>
1 1 white 2281 2293 2281
2 1 black 2281 2293 2293
3 4 white 2670 2595 2670
4 4 black 2670 2595 2595
5 6 white 2525 2470 2525
6 6 black 2525 2470 2470
7 10 white 2315 2340 2315
8 10 black 2315 2340 2340
9 13 white 2170 2155 2170
10 13 black 2170 2155 2155