如何基于月份从Pandas DataFrame多列计算QTD值？

错误原因解释 #

你遇到的ValueError是因为df['month'].isin(['April', 'May', 'June'])返回的是一个布尔Series（每个元素对应行是否满足条件），而非单个布尔值。Pandas无法直接判断整个Series的"真假"——不知道你是要求所有行都满足，还是至少一行满足，因此抛出该错误。不能用全局if语句来处理逐行的条件判断，得用向量化方法。

解决方案步骤 #

1. 预处理：计算所需的组合列 #

先把规则中提到的re3+9、re6+6、re9+3这些组合列提前计算好，方便后续调用：

import pandas as pd
import numpy as np

# 假设你的DataFrame是df，先计算组合列
df['re3_plus_9'] = df['re3'] + 9
df['re6_plus_6'] = df['re6'] + 6
df['re9_plus_3'] = df['re9'] + 3

2. 用向量化条件选择每行的目标值 #

用np.select根据月份所属季度，为每行匹配对应的取值列：

# 定义条件列表和对应的取值列表
conditions = [
    df['month'].isin(['April', 'May', 'June']),
    df['month'].isin(['July', 'August', 'September']),
    df['month'].isin(['October', 'November', 'December']),
    df['month'].isin(['January', 'February', 'March'])
]

choices = [
    df['re'],
    df['re3_plus_9'],
    df['re6_plus_6'],
    df['re9_plus_3']
]

# 生成每行对应的当前值
df['current_value'] = np.select(conditions, choices, default=np.nan)

3. 映射季度并计算QTD（季度累计） #

要计算季度累计，需要先把月份映射到对应季度，再按「年份+季度」分组做累计求和：

# 月份转季度的映射字典
month_to_quarter = {
    'January': 'Q1', 'February': 'Q1', 'March': 'Q1',
    'April': 'Q2', 'May': 'Q2', 'June': 'Q2',
    'July': 'Q3', 'August': 'Q3', 'September': 'Q3',
    'October': 'Q4', 'November': 'Q4', 'December': 'Q4'
}

df['quarter'] = df['month'].map(month_to_quarter)

# 按年份和季度分组，计算累计求和得到QTD
df['QTD'] = df.groupby(['year', 'quarter'])['current_value'].cumsum()

完整示例代码 #

如果需要验证，可以用以下示例数据测试：

# 构造示例DataFrame
data = {
    'year': [2023]*12,
    'month': ['January', 'February', 'March', 'April', 'May', 'June', 
              'July', 'August', 'September', 'October', 'November', 'December'],
    're': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120],
    're3': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
    're6': [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
    're9': [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]
}
df = pd.DataFrame(data)

# 执行上述步骤
df['re3_plus_9'] = df['re3'] + 9
df['re6_plus_6'] = df['re6'] + 6
df['re9_plus_3'] = df['re9'] + 3

conditions = [
    df['month'].isin(['April', 'May', 'June']),
    df['month'].isin(['July', 'August', 'September']),
    df['month'].isin(['October', 'November', 'December']),
    df['month'].isin(['January', 'February', 'March'])
]

choices = [
    df['re'],
    df['re3_plus_9'],
    df['re6_plus_6'],
    df['re9_plus_3']
]

df['current_value'] = np.select(conditions, choices, default=np.nan)

month_to_quarter = {
    'January': 'Q1', 'February': 'Q1', 'March': 'Q1',
    'April': 'Q2', 'May': 'Q2', 'June': 'Q2',
    'July': 'Q3', 'August': 'Q3', 'September': 'Q3',
    'October': 'Q4', 'November': 'Q4', 'December': 'Q4'
}

df['quarter'] = df['month'].map(month_to_quarter)
df['QTD'] = df.groupby(['year', 'quarter'])['current_value'].cumsum()

# 查看关键结果列
print(df[['year', 'month', 'current_value', 'QTD']])

运行后会输出每行的当前值和对应的季度累计QTD，完全符合你的规则要求。

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