R: Tidying and summarising a paired comparison dataset in the tidyverse style

user_14385985 0
Viewed 61

I have a dataset with features {a,b,c...} belonging to a pair of players taken form the set {a, b, c}. Each row represents the outcome of a matchup, columns name_1, name_2 represent player names, and all other columns a1, a2, b1, b2, c1, c2, etc.. represent numeric features corresponding to the player in the matchup.

Below is the example of a dataset:

set.seed(17)
df <- tibble(
  name_1 = sample(letters[1:3], length(letters), replace = TRUE),
  name_2 = sample(letters[1:3], length(letters), replace = TRUE),
  a1 = rnorm(length(letters)),
  a2 = rnorm(length(letters)),
  b1 = rnorm(length(letters)),
  b2 = rnorm(length(letters)),
  c1 = rnorm(length(letters)),
  c2 = rnorm(length(letters))) %>% 
  filter(!(name_1 == name_2))

What I need is to find a summary statistic for each feature grouped by player. The trouble is that the same player, for example, a, can be located sometimes under name_1, sometimes under name_2, hence his features can be located at feature1 or feature2.

Here is my feeble attempt to do this for one player (namely, a) and one feature (namely, a):

df %>% 
  mutate(feature_a_joined = case_when(df$name_1 == "a" ~ a1,
                                      df$name_2 == "a" ~ a2)) %>% 
  summarise(mean = mean(feature_a_joined, na.rm = TRUE))

I am fairly new to R, but the examples that I`ve seen in multiple vignettes refer to more standard datasets. Is there an efficient way to make a summary for each player and each variable?

Update My expected result would be something like this:

# A tibble: 3 x 4
  player feature_a_mean feature_b_mean feature_c_mean
  <chr>           <dbl>          <dbl>          <dbl>
1 a              -0.330          2.38           0.960
2 b              -0.482          1.30           0.207
3 c              -0.482         -0.477         -1.71
purrr tidyr tidyverse r
1 Answers

We can use map. Get the unique column names ('un1') from the data. Loop over those (map), apply the OP's code with case_when and get the mean

library(dplyr)
library(purrr)
library(stringr)
un1 <- unique(str_remove(names(df)[-(1:2)], "\\d+"))
map_dfc(un1, ~
       df %>%
         summarise(!! str_c('mean_', .x) :=
         mean(case_when(name_1 == .x ~ !! rlang::sym(str_c(.x, '1')), 
                        name_2 == .x ~ !! rlang::sym(str_c(.x, '2'))), 
          na.rm = TRUE)))

-output

# A tibble: 1 x 3
#    mean_a mean_b  mean_c
#     <dbl>  <dbl>   <dbl>
#1 -0.00673  0.186 -0.0632

Update

Based on the OP's expected output (assuming the output values are placeholders), we reshape the multiple blocks of columns to 'long' format with pivot_longer, do a group by to get the summarise across columns 'a' to 'c'

library(tidyr)
df %>% 
   pivot_longer(everything(), names_to = c('.value', 'grp'), 
          names_sep= '(?<=[a-z])_?(?=[0-9])')  %>% 
   group_by(player = name) %>% 
   summarise(across(a:c, mean, na.rm = TRUE), .groups = 'drop')

-output

# A tibble: 3 x 4
#  player        a      b       c
#  <chr>     <dbl>  <dbl>   <dbl>
#1 a      -0.00673  0.197  0.126 
#2 b      -0.0455   0.186 -0.138 
#3 c      -0.118   -0.468 -0.0632
user_37322710
Related