如何对Pandas DataFrame指定列进行透视重组？

问题描述 #

现有Pandas DataFrame，部分列带有_1、_2等下划线后缀（代表不同年份），需要将表格重组，让同后缀的列对应匹配，生成按年份展开的长表。

示例数据（初始版） #

import pandas as pd
df_sample = pd.DataFrame({
    'A': ['BP','Virgin'],
    'B(LY)': ['A','C'],
    'B(LY_1)': ['B', 'D'],
    'C': [1, 3],
    'C_1': [2,4],
    'D': ['W','Y'],
    'D_1': ['X','Z']
})

期望输出（初始版） #

pd.DataFrame({
    'A': ['BP','BP', 'Virgin', 'Virgin'],
    'Year': ['A','B','C','D'],
    'C': [1,2,3,4],
    'D': ['W','X','Y','Z']
})

真实场景数据（修正重复列名后） #

df = pd.DataFrame({
    'Company': ['BP','Virgin'],
    'Account_date(LY)': ['May','Apr'],
    'Account_date(LY_1)': ['Apr', 'Mar'],
    'Account_date(LY_2)': ['Mar', 'Feb'],
    'Account_date(LY_3)': ['Feb', 'Jan'],
    'Acc_day': [1, 5],
    'Acc_day_1': [2,6],
    'Acc_day_2': [3,7],
    'Acc_day_3': [4,8],
    'D': ['W','A'],
    'D_1': ['X','B'],
    'D_2': ['Y','C'],
    'D_3': ['Z','D']
})

期望输出（真实场景） #

pd.DataFrame({
    'Company': ['BP','BP','BP','BP', 'Virgin', 'Virgin','Virgin', 'Virgin'],
    'Year': ['May','Apr','Mar','Feb','Apr','Mar','Feb','Jan'],
    'Acc_day': [1,2,3,4,5,6,7,8],
    'D': ['W','X','Y','Z','A','B','C','D']
})

解决方案 #

核心思路是用pd.wide_to_long函数实现宽表转长表，先统一列名的后缀格式，再批量匹配重组。

步骤1：统一列名后缀 #

给无后缀的目标列添加_0后缀，让所有待重组列的后缀格式统一（_0、_1、_2...），方便函数识别：

# 处理真实场景数据的列名
renamed_cols = {}
for col in df.columns:
    # 跳过唯一标识符列（如Company）
    if col != 'Company' and not any(col.endswith(f'_{i}') for i in ['1','2','3']):
        renamed_cols[col] = f"{col}_0"
df = df.rename(columns=renamed_cols)

步骤2：宽表转长表 #

使用wide_to_long批量重组列，指定标识符列、待重组列前缀、后缀规则：

df_long = pd.wide_to_long(
    df,
    # 待重组列的前缀（所有需要按年份展开的列）
    stubnames=['Account_date(LY)', 'Acc_day', 'D'],
    # 唯一标识符列（不参与重组）
    i='Company',
    # 后缀对应的变量名（可自定义）
    j='year_suffix',
    # 后缀与前缀的分隔符
    sep='_',
    # 后缀的匹配规则（匹配数字）
    suffix=r'\d+'
).reset_index()

步骤3：整理结果 #

重命名列并调整顺序，得到最终格式：

df_result = df_long.rename(columns={'Account_date(LY)': 'Year'})[['Company', 'Year', 'Acc_day', 'D']]
print(df_result)

针对初始示例的适配代码 #

如果处理初始示例数据，只需调整标识符列和待重组前缀：

# 重命名示例数据的列
renamed_sample = {}
for col in df_sample.columns:
    if col != 'A' and not col.endswith('_1'):
        renamed_sample[col] = f"{col}_0"
df_sample = df_sample.rename(columns=renamed_sample)

# 宽转长
df_sample_long = pd.wide_to_long(
    df_sample,
    stubnames=['B(LY)', 'C', 'D'],
    i='A',
    j='year_suffix',
    sep='_',
    suffix=r'\d+'
).reset_index()

# 整理结果
df_sample_result = df_sample_long.rename(columns={'B(LY)': 'Year'})[['A', 'Year', 'C', 'D']]
print(df_sample_result)

注意事项 #

• 确保DataFrame无重复列名，否则需先修正（如真实场景中原数据的重复列Acc_day_2、D_1）。
• stubnames需包含所有需要按年份展开的列的完整前缀，比如Account_date(LY)不能只写Account_date。
• suffix参数支持正则表达式，若后缀包含非数字字符可调整规则。

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