如何匹配值对，然后减去列的值？

Question

我目前正在使用一个数据集，其中包含2010年至2019年之间所有大满贯网球比赛。数据框架包含每场比赛两行，一行包含有关一个玩家（获胜者）的信息，另一个包含有关另一个播放器的信息（失败者（The Loser） ）。这些对之间的共同点是match_id变量。

我想创建一个名为“等级差”的新变量。这个想法是让每一行赢家和失败者之间的ATP等级差异。

这是我与之合作的数据框架的一个子集：

# A tibble: 9,290 x 5
# Groups:   player_id [444]
   match_id    player_id   rank winner full_name           
   <chr>       <chr>      <dbl> <fct>  <chr>               
 1 m_2019_A_0  atp_104731     6 True   Kevin Anderson      
 2 m_2019_A_1  atp_105932    20 True   Nikoloz Basilashvili
 3 m_2019_A_2  atp_105430    98 True   Radu Albot          
 4 m_2019_A_3  atp_105882   137 True   Stefano Travaglia   
 5 m_2019_A_4  atp_104269    28 True   Fernando Verdasco   
 6 m_2019_A_5  atp_104655    94 True   Pablo Cuevas        
 7 m_2019_A_7  atp_126774    15 True   Stefanos Tsitsipas  
 8 m_2019_A_8  atp_105777    21 True   Grigor Dimitrov     
 9 m_2019_A_9  atp_126207    39 True   Frances Tiafoe      
10 m_2019_A_10 atp_104745     2 True   Rafael Nadal        
# ... with 9,280 more rows

这是我尝试过但没有起作用的方法：

final_match_with_player %>%
group_by(match_id) %>%
mutate(diff_rank = rank[winner == 'True'] - rank[winner == 'False'])

您对如何做到这一点有任何了解吗？

非常感谢您！

Answer 1

这会得到您想要的吗？

# example data
final_match_with_player <- tibble::tribble(
                                ~match_id,   ~player_id, ~rank, ~winner,             ~full_name,
                             "m_2019_A_0", "atp_104731",    6L,    TRUE,       "Kevin Anderson",
                             "m_2019_A_1", "atp_105932",   20L,    TRUE, "Nikoloz Basilashvili",
                             "m_2019_A_2", "atp_105430",   98L,    TRUE,           "Radu Albot",
                             "m_2019_A_3", "atp_105882",  137L,    TRUE,    "Stefano Travaglia",
                             "m_2019_A_0", "atp_106666",   30L,   FALSE,           "Joe Bloggs",
                             "m_2019_A_1", "atp_106667",   40L,   FALSE,             "John Doe",
                             "m_2019_A_2", "atp_106668",   50L,   FALSE,            "Some Body",
                             "m_2019_A_3", "atp_106669",   60L,   FALSE,           "Tennis Pro"
                             )


# create rank_diff
final_match_with_player %>% 
  group_by(match_id) %>% 
  mutate(rank_diff = ifelse(winner, -rank, rank), 
        rank_diff = sum(rank_diff)) %>% 
  ungroup()

导致：

A tibble: 8 × 6

  match_id   player_id   rank winner full_name            rank_diff
  <chr>      <chr>      <int> <lgl>  <chr>                    <int>
1 m_2019_A_0 atp_104731     6 TRUE   Kevin Anderson              24
2 m_2019_A_1 atp_105932    20 TRUE   Nikoloz Basilashvili        20
3 m_2019_A_2 atp_105430    98 TRUE   Radu Albot                 -48
4 m_2019_A_3 atp_105882   137 TRUE   Stefano Travaglia          -77
5 m_2019_A_0 atp_106666    30 FALSE  Joe Bloggs                  24
6 m_2019_A_1 atp_106667    40 FALSE  John Doe                    20
7 m_2019_A_2 atp_106668    50 FALSE  Some Body                  -48
8 m_2019_A_3 atp_106669    60 FALSE  Tennis Pro                 -77

在进一步的信息上编辑，

然后使用case_时的铅和滞后函数进行突变可能会更容易

# create rank_diff
final_match_with_player %>% 
arrange(match_id,rank) %>% 
  mutate(rank_diff = case_when(lead(match_id) == match_id ~ lead(rank) - rank, 
                            TRUE ~ lag(rank) - (rank)))

仅通过match_id和等级排列，

# A tibble: 8 × 6
  match_id   player_id   rank winner full_name            rank_diff
  <chr>      <chr>      <int> <lgl>  <chr>                    <int>
1 m_2019_A_0 atp_104731     6 TRUE   Kevin Anderson              24
2 m_2019_A_0 atp_106666    30 FALSE  Joe Bloggs                 -24
3 m_2019_A_1 atp_105932    20 TRUE   Nikoloz Basilashvili        20
4 m_2019_A_1 atp_106667    40 FALSE  John Doe                   -20
5 m_2019_A_2 atp_106668    50 FALSE  Some Body                   48
6 m_2019_A_2 atp_105430    98 TRUE   Radu Albot                 -48
7 m_2019_A_3 atp_106669    60 FALSE  Tennis Pro                  77
8 m_2019_A_3 atp_105882   137 TRUE   Stefano Travaglia          -77