为Pandas DataFrame多条件生成Result列遇问题求助

问题描述 #

现有记录球队比赛结果的DataFrame：

Team Home/Away  Home_Score   Away_Score
0   ABC   Home          2            3
1   ABC   Home          1            2
2   ABC   Away          1            3
3   ABC   Away          0            1

需要新增Result列，根据球队主客场身份和比分返回Win、Loss或Draw，期望输出为Loss, Loss, Win, Win。但使用自定义函数结合apply()调用后，输出全为Win，逻辑未正确执行。

用户的错误代码：

import numpy as np
import pandas as pd

# 假设df已定义

def result(x):    
    for score in df['Home/Away']:
        
        #Home Wins
        if x == 'Home' and np.where(df['Home_Score'] > df['Away_Score']):
            return 'Win'
        
        #Home Losses
        elif x == 'Home' and np.where(df['Home_Score'] < df['Away_Score']):
            return 'Loss'

        #Away Wins
        elif x == 'Away' and np.where(df['Home_Score'] < df['Away_Score']):
            return 'Win'
        
        #Away Losses
        elif x == 'Away' and np.where(df['Home_Score'] > df['Away_Score']):
            return 'Loss'
        
        #Draws
        elif np.where(df['Home_Score'] == df['Away_Score']):
            return 'Draw'
        
df['Result'] = df.apply(lambda x: result(x['Home/Away']), axis=1)

错误原因分析 #

1. 参数传递错误：函数仅接收了Home/Away的值，没有获取当前行的比分数据，无法针对单行进行判断。
2. 冗余循环干扰：函数内遍历整个df['Home/Away']列，导致第一次循环就直接返回结果，逻辑完全偏离单行判断。
3. np.where用法错误：np.where()返回的是满足条件的索引数组（以元组形式），而非布尔值。即使当前行不满足条件，只要返回的元组非空，就会被视为True，导致第一个条件触发后直接返回Win。

正确解决方案 #

方案一：使用apply处理单行数据 #

定义接收整行数据的函数，针对每行的主客场和比分进行判断：

import pandas as pd
import numpy as np

# 构造原始DataFrame
df = pd.DataFrame({
    'Team': ['ABC', 'ABC', 'ABC', 'ABC'],
    'Home/Away': ['Home', 'Home', 'Away', 'Away'],
    'Home_Score': [2, 1, 1, 0],
    'Away_Score': [3, 2, 3, 1]
})

def get_result(row):
    home_score = row['Home_Score']
    away_score = row['Away_Score']
    if row['Home/Away'] == 'Home':
        if home_score > away_score:
            return 'Win'
        elif home_score < away_score:
            return 'Loss'
        else:
            return 'Draw'
    else:  # 客场情况
        if away_score > home_score:
            return 'Win'
        elif away_score < home_score:
            return 'Loss'
        else:
            return 'Draw'

df['Result'] = df.apply(get_result, axis=1)

执行后df['Result']的结果为：['Loss', 'Loss', 'Win', 'Win']，符合预期。

方案二：矢量化操作（更高效） #

使用np.select()进行矢量化判断，避免循环，适合大数据量场景：

import pandas as pd
import numpy as np

# 构造原始DataFrame
df = pd.DataFrame({
    'Team': ['ABC', 'ABC', 'ABC', 'ABC'],
    'Home/Away': ['Home', 'Home', 'Away', 'Away'],
    'Home_Score': [2, 1, 1, 0],
    'Away_Score': [3, 2, 3, 1]
})

# 定义条件列表
conditions = [
    (df['Home/Away'] == 'Home') & (df['Home_Score'] > df['Away_Score']),
    (df['Home/Away'] == 'Home') & (df['Home_Score'] < df['Away_Score']),
    (df['Home/Away'] == 'Away') & (df['Away_Score'] > df['Home_Score']),
    (df['Home/Away'] == 'Away') & (df['Away_Score'] < df['Home_Score']),
    (df['Home_Score'] == df['Away_Score'])
]

# 对应条件的结果
choices = ['Win', 'Loss', 'Win', 'Loss', 'Draw']

df['Result'] = np.select(conditions, choices)

该方法直接对整个DataFrame进行批量判断，执行效率远高于apply()。

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