Home > database >  Alternate input from 2 columns into one column in R
Alternate input from 2 columns into one column in R

Time:08-04

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
  • Related