Pandas 月度转季度年度汇总技巧

在数据分析中，我们经常会遇到需要将细粒度的时间序列数据（如月度数据）聚合为粗粒度数据（如季度或年度数据）的场景。当原始数据以宽格式存储，即每个月份作为独立列时，手动指定每个季度或年份的列进行求和会非常繁琐且难以适应动态数据范围。本文将介绍一种灵活且高效的方法，利用 Pandas 的数据重塑和聚合功能来解决这一问题。

1. 理解挑战与数据结构

假设我们有一个 Pandas DataFrame，其中包含多个实体（例如产品、地区）的数值数据，并且每个月份的数据都存储在一个以 YYYYMM 格式命名的列中。

示例原始 DataFrame：

import pandas as pd

data = {
    '201003': [10, 14],
    '201004': [11, 19],
    '201005': [14, 20],
    '201006': [22, 22],
    '201007': [10, 26],
    '201008': [19, 11]
}
df_original = pd.DataFrame(data, index=['A', 'B'])

print("原始 DataFrame:")
print(df_original)

输出：

原始 DataFrame:
   201003  201004  201005  201006  201007  201008
A      10      11      14      22      10      19
B      14      19      20      22      26      11

我们的目标是将这些 YYYYMM 列的数据聚合为季度和年度的总和，并生成新的数据结构。

2. 重塑数据结构：从宽到长

要灵活地处理时间信息并进行聚合，首先需要将 DataFrame 从宽格式（月份作为列）转换为长格式（月份作为行）。pandas.melt() 函数是实现这一目标的关键工具。它会将指定的列“融化”成行，创建一个新的 variable 列来存储原始列名，以及一个 value 列来存储对应的值。

# 将原始 DataFrame 重置索引，以便 'A', 'B' 成为常规列，或在 melt 中指定 id_vars
# 这里我们假设索引是实体标识，希望保留，所以使用 reset_index
df_melted = df_original.reset_index().melt(id_vars='index', var_name='YYYYMM', value_name='Value')

print("\n重塑后的 DataFrame (df_melted):")
print(df_melted)

输出：

重塑后的 DataFrame (df_melted):
  index  YYYYMM  Value
0     A  201003     10
1     B  201003     14
2     A  201004     11
3     B  201004     19
4     A  201005     14
5     B  201005     20
6     A  201006     22
7     B  201006     22
8     A  201007     10
9     B  201007     26
10    A  201008     19
11    B  201008     11

现在，每个 YYYYMM 列名及其对应的值都转换为了一行，方便后续处理。id_vars='index' 将原始的行索引（'A', 'B'）保留为一个名为 'index' 的新列。

3. 提取时间信息：年份和月份

在长格式数据中，YYYYMM 列包含了我们所需的年份和月份信息。我们可以使用字符串切片操作轻松地从 YYYYMM 字符串中提取这些信息。

df_melted['Year'] = df_melted['YYYYMM'].str[:4]  # 提取前4位作为年份
df_melted['Month'] = df_melted['YYYYMM'].str[4:] # 提取后2位作为月份

print("\n提取年份和月份后的 DataFrame:")
print(df_melted)

输出：

提取年份和月份后的 DataFrame:
   index  YYYYMM  Value  Year Month
0      A  201003     10  2010    03
1      B  201003     14  2010    03
2      A  201004     11  2010    04
3      B  201004     19  2010    04
4      A  201005     14  2010    05
5      B  201005     20  2010    05
6      A  201006     22  2010    06
7      B  201006     22  2010    06
8      A  201007     10  2010    07
9      B  201007     26  2010    07
10     A  201008     19  2010    08
11     B  201008     11  2010    08

4. 映射季度信息

为了进行季度汇总，我们需要将每个月份映射到对应的季度。这可以通过创建一个月份到季度的字典，然后使用 map() 函数应用到 Month 列上来实现。

# 定义月份到季度的映射
month_quarter_map = {
    '01': 1, '02': 1, '03': 1,  # 第一季度
    '04': 2, '05': 2, '06': 2,  # 第二季度
    '07': 3, '08': 3, '09': 3,  # 第三季度
    '10': 4, '11': 4, '12': 4   # 第四季度
}

df_melted['Quarter'] = df_melted['Month'].map(month_quarter_map)

print("\n添加季度信息后的 DataFrame:")
print(df_melted)

输出：

添加季度信息后的 DataFrame:
   index  YYYYMM  Value  Year Month  Quarter
0      A  201003     10  2010    03        1
1      B  201003     14  2010    03        1
2      A  201004     11  2010    04        2
3      B  201004     19  2010    04        2
4      A  201005     14  2010    05        2
5      B  201005     20  2010    05        2
6      A  201006     22  2010    06        2
7      B  201006     22  2010    06        2
8      A  201007     10  2010    07        3
9      B  201007     26  2010    07        3
10     A  201008     19  2010    08        3
11     B  201008     11  2010    08        3

5. 执行季度和年度汇总

现在，我们有了 index（原始实体）、Year 和 Quarter 列，可以轻松地使用 groupby() 函数进行聚合。

5.1 季度汇总

我们可以按 index、Year 和 Quarter 进行分组，然后对 Value 列求和。

df_quarterly_sum = df_melted.groupby(['index', 'Year', 'Quarter'])['Value'].sum().reset_index()

print("\n季度汇总结果:")
print(df_quarterly_sum)

输出：

季度汇总结果:
  index  Year  Quarter  Value
0     A  2010        1     10
1     A  2010        2     47
2     A  2010        3     29
3     B  2010        1     14
4     B  2010        2     61
5     B  2010        3     37

如果希望季度作为列，可以使用 pivot_table 或 unstack()：

df_quarterly_wide = df_quarterly_sum.pivot_table(index=['index', 'Year'], columns='Quarter', values='Value').reset_index()
df_quarterly_wide.columns.name = None # 移除 columns name
print("\n季度汇总结果 (宽格式):")
print(df_quarterly_wide)

输出：

季度汇总结果 (宽格式):
  index  Year   1   2   3
0     A  2010  10  47  29
1     B  2010  14  61  37

5.2 年度汇总

年度汇总类似，只需按 index 和 Year 进行分组求和。

df_annual_sum = df_melted.groupby(['index', 'Year'])['Value'].sum().reset_index()

print("\n年度汇总结果:")
print(df_annual_sum)

输出：

年度汇总结果:
  index  Year  Value
0     A  2010     86
1     B  2010    112

6. 注意事项与最佳实践

• 数据类型一致性: 确保 Value 列的数据类型是数值型（int 或 float），否则求和操作会出错。如果不是，可以使用 pd.to_numeric(df_melted['Value'], errors='coerce') 进行转换。
• 处理缺失值: 在 melt 操作之前或之后，考虑如何处理原始数据中的缺失值。sum() 函数默认会跳过 NaN 值。
• 动态时间范围: 这种方法对于动态的时间范围（例如用户选择不同的起始和结束年份/月份）非常有效，因为我们不再需要硬编码列名。
• 性能优化: 对于非常大的数据集，melt 和 groupby 操作可能会消耗较多内存和时间。确保您的系统有足够的资源。
• 更高级的时间序列处理: 对于更复杂的时间序列操作，例如日期偏移、频率转换等，Pandas 的 DatetimeIndex 或 PeriodIndex 提供了更强大的功能。您可以考虑将 YYYYMM 转换为 Period 对象或 datetime 对象，但对于本教程中简单的季度/年度汇总需求，字符串操作和映射已足够高效。
  • 示例：使用 PeriodIndex (可选)

    # 将 YYYYMM 转换为 PeriodIndex
    df_melted['Period'] = pd.to_datetime(df_melted['YYYYMM'], format='%Y%m').dt.to_period('M')
    # 提取年份和季度
    df_melted['Year_P'] = df_melted['Period'].dt.year
    df_melted['Quarter_P'] = df_melted['Period'].dt.quarter
    # 然后按 'index', 'Year_P', 'Quarter_P' 分组求和
    df_quarterly_sum_period = df_melted.groupby(['index', 'Year_P', 'Quarter_P'])['Value'].sum().reset_index()
    # 这种方法更健壮，尤其是在处理更复杂的日期逻辑时

7. 总结

通过 df.melt() 将宽格式的月度数据转换为长格式，结合字符串操作提取年份和月份，并创建月份到季度的映射，我们能够以一种灵活且可扩展的方式实现季度和年度数据的聚合。这种方法避免了手动指定大量列名的繁琐工作，使得数据处理流程更加自动化和高效，尤其适用于处理具有动态时间范围的数据集。