基于球队配对对齐表格行：Python/谷歌表格解决方案求助

我有四列分为两组配对：

• 第一组：Home Team、Away Team
• 第二组：HomeTeam、AwayTeam

要求将两组配对中球队完全匹配的行对齐到同一行，移动第二组配对列数据时，其关联列（HTHG、First Half Score、HTAG、Home Shot）需同步移动。例如第4行第一组是(Tottenham, Southampton)，第二组该配对在第7行，需将第7行的第二组及关联列数据移至第4行。

现寻求Python（Jupyter Notebook）或谷歌表格的实现方案，以下是我尝试过的代码：

尝试代码1 #

pair_mapping = {(row['HomeTeam'], row['AwayTeam']): idx for idx, row in season_2023_2024.iterrows()}

for idx, row in season_2023_2024.iterrows():
    home_team = row['Home Team']
    away_team = row['Away Team']
    matching_idx = pair_mapping.get((home_team, away_team), None)
    
    # 找到匹配行且非当前行时，交换第二组相关列数据
    if matching_idx is not None and matching_idx != idx:
        season_2023_2024.loc[matching_idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']], \
        season_2023_2024.loc[idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']] = \
        season_2023_2024.loc[idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']], \
        season_2023_2024.loc[matching_idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']]

display(HTML(season_2023_2024.to_html(index=False)))

尝试代码2 #

lookup_table = {}
for i, row in season_2023_2024.iterrows():
    lookup_table[(row['Home_Team'], row['Away_Team'])] = row[['HomeTeam', 'AwayTeam', 'HTHG', 'First_Half_Score', 'HTAG', 'Home_Shot']]

for i, row in season_2023_2024.iterrows():
    key = (row['Home_Team'], row['Away_Team'])
    if key in lookup_table:
        for col in ['HomeTeam', 'AwayTeam', 'HTHG', 'First_Half_Score', 'HTAG', 'Home_Shot']:
            season_2023_2024.at[i, col] = lookup_table[key][col]

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