在R中基于球队过往比赛更新赛前得分：多列查找最新值

初始数据框 #

我们有如下结构的R语言数据框：

home_team <- c('Team A', 'Team B', 'Team C', 'Team D', 'Team B', 'Team F')
away_team <- c('Team B', 'Team C', 'Team D', 'Team A', 'Team F', 'Team A')
home_team_score_pre <- c(300, 150, 600, 800, 50, 450)
away_team_score_pre <- c(550, 340, 100, 208, 412, 18)
winning_team <- c('Team A', 'Team C', 'Team C', 'Team D', 'Team F', 'Team F')
res <- c(16, 25, 11, 4, 22, 9) 
home_team_score_change <- c(16, -25, 11, 4, -22, 9) 
away_team_score_change <- c(-16, 25, -11, -4, 22, -9)
home_team_score_post <- c(316, 125, 611, 804, 28, 459)
away_team_score_post <- c(534, 365, 89, 204, 434, 9)

对应的表格输出为：

home_team away_team home_team_score_pre away_team_score_pre winning_team res home_team_score_change away_team_score_change
1    Team A    Team B                 300                 550       Team A  16                     16                    -16
2    Team B    Team C                 150                 340       Team C  25                    -25                     25
3    Team C    Team D                 600                 100       Team C  11                     11                    -11
4    Team D    Team A                 800                 208       Team D   4                      4                     -4
5    Team B    Team F                  50                 412       Team F  22                    -22                     22
6    Team F    Team A                 450                  18       Team F   9                      9                     -9
  home_team_score_post away_team_score_post
1                  316                  534
2                  125                  365
3                  611                   89
4                  804                  204
5                   28                  434
6                  459                    9

得分规则 #

每支球队赛前有初始得分（对应home_team_score_pre和away_team_score_pre字段）。比赛结束后，根据胜负结果（res字段）调整得分：

• 获胜球队加上res值
• 失利球队减去res值
  最终得到赛后得分（home_team_score_post和away_team_score_post字段）。

更新需求 #

需要更新home_team_score_pre和away_team_score_pre字段，规则如下：

• 查找每支球队上一场比赛的赛后得分（无论该队当时是主场还是客场），作为当前比赛的赛前得分
• 若球队是首次参赛，则填入默认值100

示例说明 #

新增两行比赛数据：

home_team away_team home_team_score_pre away_team_score_pre
1    Team C    Team B  
2    Team A    Team F  

处理逻辑：

• Team C的上一场比赛是第3行，取其home_team_score_post值611填入对应home_team_score_pre
• Team B的上一场比赛是第5行，取其home_team_score_post值28填入对应位置

最终完整数据框 #

处理后包含新增比赛的完整数据框如下：

home_team away_team home_team_score_pre away_team_score_pre winning_team res home_team_score_change away_team_score_change
1    Team A    Team B                 100                100       Team A  16                     16                    -16
2    Team B    Team C                 84                 100       Team C  25                    -25                     25
3    Team C    Team D                 125                100       Team C  11                     11                    -11
4    Team D    Team A                 89                 116       Team D   4                      4                     -4
5    Team B    Team F                 59                 100       Team F  22                    -22                     22
6    Team F    Team A                 122                112       Team F   9                      9                     -9
7    Team C    Team B                 136                37        Team B  12                      49                      131
8    Team B    Team F                 49                 131       Team F  10                      0                      0
  home_team_score_post away_team_score_post
1                  116                  84
2                  59                   125
3                  136                  89
4                  93                   112
5                  37                   122
6                  131                  103
7                  124                  49
8                  39                   141

内容的提问来源于stack exchange，提问作者Michael