在数据分析场景中，我们经常会遇到包含多列时间序列数据（如按月份或日期排列的数值）的宽格式dataframe。同时，dataframe的每一行可能还包含一个特定的“基准日期”，我们需要根据这个基准日期对该行内的所有时间序列值进行聚合，分别计算出基准日期之前和之后的数据总和。这种需求在财务分析、销售趋势分析等领域尤为常见。
核心思路
解决此类问题的关键在于将宽格式的数据转换为长格式，以便于按日期进行比较和分组聚合。具体步骤如下：
- 数据重塑 (Melt)：将所有日期列转换为两列：一列表示日期名称（变量），另一列表示对应的值。
- 数据类型转换 (Type Conversion)：确保日期列和变量列的数据类型一致，以便进行正确的日期比较。
- 条件分类 (Conditional Assignment)：根据每行的“基准日期”与“变量日期”的比较结果，为每个值打上“Before”（之前）或“After”（之后）的标签。
- 分组聚合 (Groupby and Sum)：按原始的唯一标识符（如Code）和新创建的“Before/After”标签进行分组，计算值的总和。
- 数据透视 (Unstack)：将“Before/After”标签从行索引转换为列，形成独立的“Before”和“After”总和列。
- 数据合并 (Merge)：将计算出的总和列合并回原始DataFrame。
实现步骤与示例代码
假设我们有一个DataFrame df，其结构如下：
- Code：唯一标识符。
- 202001 到 202012：表示不同月份的数值列。
- Date：每行对应的基准日期，格式为字符串（例如'202004'）。
import pandas as pd
import numpy as np
# 模拟数据
data = {
'Code': ['12345', '12346', '12347'],
'202001': [1000, 999, 1983],
'202002': [1001, 1000, 1984],
'202003': [1002, 1001, 1985],
'202004': [1003, 1002, 1986],
'202005': [1004, 1003, 1987],
'202006': [1005, 1003, 1988],
'202007': [3006, 1005, 1989],
'202008': [1007, 1006, 1990],
'202009': [1008, 1007, 1991],
'202010': [1009, 1008, 1992],
'202011': [1010, 1009, 1993],
'202012': [1011, 1010, 1994],
'Date': ['202004', '202006', '202010']
}
df = pd.DataFrame(data)
print("原始DataFrame:")
print(df)
# 1. 数据重塑 (Melt)
# 将除 'Code' 和 'Date' 之外的所有列进行melt操作
# 'variable' 列将包含原列名 (如 '202001')
# 'value' 列将包含对应的值
tmp = df.melt(['Code', 'Date'])
# 2. 数据类型转换 (Type Conversion)
# 确保 'Date' 列和新生成的 'variable' 列（代表日期）都是字符串类型，以便进行字符串比较
tmp = tmp.astype({'Date': str, 'variable': str})
# 3. 条件分类 (Conditional Assignment)
# 使用 np.where 根据 'Date' 列和 'variable' 列的比较结果，创建 'col' 列
# 如果 'Date' (基准日期) 大于 'variable' (当前日期)，则标记为 'Before'
# 否则标记为 'After'
tmp = tmp.assign(col=lambda d: np.where(d['Date'].gt(d['variable']), 'Before', 'After'))
# 4. 分组聚合 (Groupby and Sum)
# 按 'Code' 和 'col' (Before/After) 分组，对 'value' 求和
tmp = tmp.groupby(['Code', 'col'])['value'].sum()
# 5. 数据透视 (Unstack)
# 将 'col' 列从索引转换为列，形成 'Before' 和 'After' 两列
# 并指定列的顺序为 'Before', 'After'
tmp = tmp.unstack('col')[['Before', 'After']]
# 6. 数据合并 (Merge)
# 将计算出的 'Before' 和 'After' 总和合并回原始 DataFrame
# 使用 'Code' 列作为左侧连接键，tmp 的索引作为右侧连接键
out = df.merge(tmp, left_on='Code', right_index=True, how='left')
print("\n结果DataFrame:")
print(out)输出结果：
原始DataFrame:
Code 202001 202002 202003 202004 202005 202006 202007 202008 202009 202010 202011 202012 Date
0 12345 1000 1001 1002 1003 1004 1005 3006 1007 1008 1009 1010 1011 202004
1 12346 999 1000 1001 1002 1003 1003 1005 1006 1007 1008 1009 1010 202006
2 12347 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 202010
结果DataFrame:
Code 202001 202002 202003 202004 202005 202006 202007 202008 202009 202010 202011 202012 Date Before After
0 12345 1000 1001 1002 1003 1004 1005 3006 1007 1008 1009 1010 1011 202004 3003 11063
1 12346 999 1000 1001 1002 1003 1003 1005 1006 1007 1008 1009 1010 202006 5005 7048
2 12347 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 202010 17883 5979注意事项
- 数据类型一致性：在进行日期比较时，务必确保所有参与比较的日期字段（包括原始Date列和melt操作后生成的variable列）具有相同且可比较的数据类型。通常，转换为字符串或Pandas的datetime类型是稳健的做法。本例中，由于日期格式为YYYYMM，直接字符串比较是有效的。如果日期格式复杂或需要考虑时间戳，则建议转换为pd.to_datetime。
- 列名匹配：df.melt()的id_vars参数用于指定保持不变的标识符列。所有未在id_vars中指定的列都将被“融化”。
- 边界条件：本方案中，如果“变量日期”与“基准日期”相等，则该值被计入“After”组。如果业务逻辑要求相等日期计入“Before”组，或者需要单独处理，则需要调整np.where的条件。例如，d['Date'].ge(d['variable'])会将等于的日期也算作“After”组。
- 性能考量：对于非常大的DataFrame，melt操作会显著增加行数，这可能会对内存和计算性能产生影响。但在大多数常规分析场景下，这种方法是高效且易于理解的。
- 现有列处理：如果原始DataFrame中已经存在名为Before或After的列，并且这些列不是你想要的结果，你可能需要在执行操作前使用df.drop(columns=['Before', 'After'])将其删除，以避免列名冲突或混淆。
总结
通过上述Pandas操作组合，我们能够优雅地解决在宽格式DataFrame中根据动态基准日期进行行级聚合的问题。这种方法不仅功能强大，而且代码结构清晰，易于维护和扩展。掌握melt、groupby和merge等Pandas核心功能对于高效处理复杂数据转换任务至关重要。
