Creating multiple new columns in a dataframe in an iterative way

user_13726530 0
Viewed 177

-- Small edit made to test data. Columns are no longer grouped by round, but instead grouped by Team as is the case in the real dataset.

I have tried a variety of methods for creating multiple new columns, while minimising the repetition in the code. My initial and successful method requires excessive copy and pasting, but I would like to minimise this as much as possible. Below is example data for the problem: 

df <- tribble(~R1TeamX, ~R2TeamX,~R3TeamX, ~R1TeamY,~R2TeamY, ~R3TeamY, 
              10, 11, 12, 15, 19, 20, 
              11, 13, 14, 25, 18, 15)

This example data is for three rounds with the scores for both team X and team Y. I am looking to create additional columns, finding the difference between the scores of Team X and Y. The real dataset has upwards of 30 rounds.

My initial solution used mutate and works as follows: 

df <- df %>%
mutate(R1Diff = R1TeamX - R1TeamY, 
       R2Diff = R2TeamX - R2TeamY, 
       R3Diff = R3TeamX - R3TeamY)

While this does the job, it is not scaleable. I have attempted to reduce this down to less code using str_c & mutate, but cannot identify the correct looping method to make this work for several lines of code. Below is my attempt at standardising the code so far: 

teamx <- str_c("R", 1:3, "TeamX")
teamy <- str_c("R", 1:3, "TeamY")
round_diff <- str_c("R", 1:3, "Diff")

df <- df %>%
  mutate(!!round_diff[1] := UQ(parse_quo(teamx[1], global_env())) - UQ(parse_quo(teamy[1], global_env())),
         !!round_diff[2] := UQ(parse_quo(teamx[2], global_env())) - UQ(parse_quo(teamy[2], global_env())),
         !!round_diff[3] := UQ(parse_quo(teamx[3], global_env())) - UQ(parse_quo(teamy[3], global_env())))

While additional code is required, this standardises my input to some degree reducing some of the leg work, but I know there must be some way to reduce this into a single line. I have explored mutate_at and for loops to no avail. I suspect this problem could also be tackled with purrr::map, but I do have enough ability in this area to identify the correct approach.

Any help would be greatly appreciated.

dplyr tidyverse r
3 Answers

Whilst it's possible to do this in dplyr and tidyr functions, remember you still have some useful base R options open to you. This method uses lapply and makes the assumption that your columns are alternating between team X and team Y

seq(length(df)/2) %>%
  lapply(function(x) df[[x]] - df[[x + 1]]) %>%
  as.data.frame() %>%
  setNames(paste0("R", seq(length(df)/2), "Diff")) %>%
  cbind(df,.)

#>   R1TeamX R1TeamY R2TeamX R2TeamY R3TeamX R3TeamY R1Diff R2Diff R3Diff
#> 1      10      11      12      15      19      20     -1     -1     -3
#> 2      11      13      14      25      18      15     -2     -1    -11
user_125003150

try to do it this way

library(tidyverse)
df %>% 
  mutate(id = row_number()) %>% 
  pivot_longer(
    -id,
    names_to = c("set", ".value"), 
    names_pattern = "(R\\d+Team)(X|Y)"
               ) %>% 
  mutate(Diff = X - Y) %>% 
  pivot_longer(-c(id, set)) %>% 
  pivot_wider(id, names_from = c(set, name), values_from = value, names_sep = "")
user_120254830

Here's a solution that I believe is robust with respect to the number of rounds, the number of opponents of Team X and the order in which the results are stored.

First, make the data tidy: remove information about Teams and Rounds from column names.

newDF <- df %>% 
            mutate(id = row_number()) %>% 
            pivot_longer(
              -id,
              names_to = c("Round", "Team"), 
              names_pattern = "R(\\d+)Team(X|Y)",
              values_to="Score"
           )

Now calculate the differences in scores

newDF %>% 
  # Calculate difference in scores
  mutate(Team=ifelse(Team == "X", Team, "Opponent")) %>% 
  pivot_wider(values_from=Score, names_from=Team) %>% 
  mutate(Diff=X - Opponent) %>% 
  select(-Opponent) %>% 
  # Bring in identity of oponent
  left_join(
    newDF %>% 
      filter(Team != "X") %>% 
      select(-Score) %>% 
      rename(Opposition=Team),
      by=c("id", "Round")
  )

Giving

# A tibble: 6 x 5
     id Round     X  Diff Opposition
  <int> <chr> <dbl> <dbl> <chr>     
1     1 1        10    -5 Y         
2     1 2        11    -8 Y         
3     1 3        12    -8 Y         
4     2 1        11   -14 Y         
5     2 2        13    -5 Y         
6     2 3        14    -1 Y

based on OP's revised input data.

user_134348710
Related