如何在R/Python/SQL中重排球队对阵列，按首列排序唯一组合

需求说明 #

按TeamA → TeamB → TeamC → TeamD的优先级，将目标球队统一移至Team1列：

• 所有涉及高优先级球队的对阵，将高优先级球队固定在Team1
• 同步调整Team1_Win字段：若原记录中目标球队在Team2列，则胜负状态取反（1 - 原Team1_Win）
• 低优先级球队仅处理其与更低优先级球队的对阵（已和高优先级球队处理过的对阵不再重复调整）

R 实现（使用dplyr） #

library(dplyr)

# 原始数据
data <- data.frame(GameNum = c(1,2,3,4,5,6,7,8),
                   Team1 = c("TeamA", "TeamA", "TeamA", "TeamA", 
                             "TeamB", "TeamB", "TeamB", "TeamC"),
                   Team2 = c("TeamB", "TeamC", "TeamD", "TeamD", 
                             "TeamA", "TeamA", "TeamC", "TeamA"),
                   Team1_Win = c(1, 0, 1, 1, 1, 0, 1, 0))

# 定义球队优先级顺序
team_order <- c("TeamA", "TeamB", "TeamC", "TeamD")

# 处理逻辑
processed_data <- data %>%
  rowwise() %>%
  mutate(
    # 判断当前对阵中优先级更高的球队
    target_team = case_when(
      Team1 %in% team_order & Team2 %in% team_order ~ 
        team_order[min(match(Team1, team_order), match(Team2, team_order))],
      TRUE ~ Team1 # 若只有一方在优先级列表，保留原Team1
    ),
    # 调整Team1、Team2和胜负标记
    Team1 = target_team,
    Team2 = ifelse(target_team == Team1, Team2, Team1),
    Team1_Win = ifelse(target_team == Team1, Team1_Win, 1 - Team1_Win)
  ) %>%
  select(-target_team) %>% # 移除临时字段
  ungroup()

print(processed_data)

Python 实现（使用pandas） #

import pandas as pd

# 原始数据
data = pd.DataFrame({
    "GameNum": [1,2,3,4,5,6,7,8],
    "Team1": ["TeamA", "TeamA", "TeamA", "TeamA", "TeamB", "TeamB", "TeamB", "TeamC"],
    "Team2": ["TeamB", "TeamC", "TeamD", "TeamD", "TeamA", "TeamA", "TeamC", "TeamA"],
    "Team1_Win": [1, 0, 1, 1, 1, 0, 1, 0]
})

# 定义球队优先级顺序，用字典映射优先级数值（数值越小优先级越高）
team_priority = {"TeamA": 1, "TeamB": 2, "TeamC": 3, "TeamD": 4}

def process_row(row):
    t1_prio = team_priority.get(row["Team1"], 99)
    t2_prio = team_priority.get(row["Team2"], 99)
    
    if t1_prio <= t2_prio:
        return row["Team1"], row["Team2"], row["Team1_Win"]
    else:
        # 交换球队，胜负取反
        return row["Team2"], row["Team1"], 1 - row["Team1_Win"]

# 应用处理函数
data[["Team1", "Team2", "Team1_Win"]] = data.apply(
    lambda x: pd.Series(process_row(x)), axis=1
)

print(data)

SQL 实现 #

假设数据存储在名为game_results的表中，使用CASE WHEN逻辑处理：

SELECT
    GameNum,
    -- 确定优先级更高的球队作为新的Team1
    CASE
        WHEN (
            (Team1 = 'TeamA') OR 
            (Team1 = 'TeamB' AND Team2 != 'TeamA') OR 
            (Team1 = 'TeamC' AND Team2 = 'TeamD')
        ) THEN Team1
        ELSE Team2
    END AS Team1,
    -- 确定对应的Team2
    CASE
        WHEN (
            (Team1 = 'TeamA') OR 
            (Team1 = 'TeamB' AND Team2 != 'TeamA') OR 
            (Team1 = 'TeamC' AND Team2 = 'TeamD')
        ) THEN Team2
        ELSE Team1
    END AS Team2,
    -- 调整胜负标记
    CASE
        WHEN (
            (Team1 = 'TeamA') OR 
            (Team1 = 'TeamB' AND Team2 != 'TeamA') OR 
            (Team1 = 'TeamC' AND Team2 = 'TeamD')
        ) THEN Team1_Win
        ELSE 1 - Team1_Win
    END AS Team1_Win
FROM game_results;

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