使用Pandas实现字段名转值及逐行反聚合的复杂转换

需要将数据集中的特定字段名转换为值，同时将聚合的数值拆分为独立行，完成宽表到长表的透视转换。

示例输入数据 #

import pandas as pd

data = {
    "Start": ['8/1/2013', '8/1/2013'],
    "Date": ['9/1/2013', '9/1/2013'],
    "End": ['10/1/2013', '10/1/2013'],
    "Area": ['NY', 'CA'],
    "Final": ['3/1/2023', '3/1/2023'],
    "Type": ['CC', 'AA'],
    "Middle Stat": [226, 130],
    "Low Stat": [20, 50],
    "High Stat": [10, 0],
    "Middle Stat1": [0, 0],
    "Low Stat1": [0, 0],
    "High Stat1": [0, 0]
}
df = pd.DataFrame(data)

表格形式展示：

期望输出结果 #

Start       Date        End         Area    Final       Type    Stat    Range   Stat1
8/1/2013    9/1/2013    10/1/2013   NY      3/1/2023    CC      20      Low     0
8/1/2013    9/1/2013    10/1/2013   CA      3/1/2023    AA      50      Low     0
8/1/2013    9/1/2013    10/1/2013   NY      3/1/2023    CC      226     Middle  0
8/1/2013    9/1/2013    10/1/2013   CA      3/1/2023    AA      130     Middle  0
8/1/2013    9/1/2013    10/1/2013   NY      3/1/2023    CC      10      High    0
8/1/2013    9/1/2013    10/1/2013   CA      3/1/2023    AA      0       High    0

当前尝试代码 #

pd.wide_to_long(df, 
                stubnames=['Low','Middle','High'],
                i=['Start','Date','End','Area','Final'],
                j='',
                sep=' ',
                suffix='(stat)'
).unstack(level=-1, fill_value=0).stack(level=0).reset_index()

原始完整数据集（含空值） #

import pandas as pd

data = {'Start': ['9/1/2013', '10/1/2013', '11/1/2013', '12/1/2013'],
        'Date': ['10/1/2016', '11/1/2016', '12/1/2016', '1/1/2017'],
        'End': ['11/1/2016', '12/1/2016', '1/1/2017', '2/1/2017'],
        'Area': ['NY', 'NY', 'NY', 'NY'],
        'Final': ['3/1/2023', '3/1/2023', '3/1/2023', '3/1/2023'],
        'Type': ['CC', 'CC', 'CC', 'CC'],
        'Low Stat': ['', '', '', ''],
        'Low Stat1': ['', '', '', ''],
        'Middle Stat': ['0', '0', '0', '0'],
        'Middle Stat1': ['0', '0', '0', '0'],
        'Re': ['','','',''],
        'Set': ['0', '0', '0', '0'],
        'Set2': ['0', '0', '0', '0'],
        'Set3': ['0', '0', '0', '0'],
        'High Stat': ['', '', '', ''],
        'High Stat1': ['', '', '', '']}

df = pd.DataFrame(data)

解决方案 #

方法一：pd.melt+列名拆分（推荐）

通过拆分列名提取分组信息，再透视得到目标格式：

import pandas as pd

# 加载示例数据
df = pd.DataFrame(data)

# 第一步：将宽表转为长表，保留标识列
melted = df.melt(
    id_vars=['Start', 'Date', 'End', 'Area', 'Final', 'Type'],
    var_name='Col',
    value_name='Value'
)

# 第二步：拆分列名，提取Range（Low/Middle/High）和Stat类型（Stat/Stat1）
melted[['Range', 'StatType']] = melted['Col'].str.split(' ', expand=True)

# 第三步：透视得到Stat和Stat1列
result = melted.pivot_table(
    index=['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Range'],
    columns='StatType',
    values='Value',
    fill_value=0
).reset_index()

# 重命名列，匹配期望格式
result.columns = ['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Range', 'Stat', 'Stat1']

# 按Low/Middle/High排序
order_map = {'Low': 0, 'Middle': 1, 'High': 2}
result['sort_key'] = result['Range'].map(order_map)
result = result.sort_values('sort_key').drop('sort_key', axis=1).reset_index(drop=True)

print(result)

方法二：修正pd.wide_to_long参数

调整参数匹配列名规则，再整理格式：

# 重命名列，将空格替换为下划线，方便wide_to_long识别
df_renamed = df.rename(columns=lambda x: x.replace(' ', '_'))

# 使用wide_to_long转换
temp = pd.wide_to_long(
    df_renamed,
    stubnames=['Low', 'Middle', 'High'],
    i=['Start', 'Date', 'End', 'Area', 'Final', 'Type'],
    j='StatType',
    sep='_',
    suffix='Stat\\d*'
).reset_index()

# 再次melt整理出Range列，合并Stat1数据
result = temp.melt(
    id_vars=['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'StatType'],
    var_name='Range',
    value_name='Stat'
).dropna(subset=['Stat'])

# 匹配对应Range的Stat1值
result['Stat1'] = result.apply(
    lambda row: df_renamed.loc[df_renamed.index == row.name, f"{row['Range']}_Stat1"].values[0],
    axis=1
)

# 调整列顺序并排序
result = result[['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Stat', 'Range', 'Stat1']]
order_map = {'Low': 0, 'Middle': 1, 'High': 2}
result['sort_key'] = result['Range'].map(order_map)
result = result.sort_values('sort_key').drop('sort_key', axis=1).reset_index(drop=True)

print(result)

处理含空值的原始数据集

先统一替换空值为0，再执行上述转换逻辑：

# 加载原始数据
df_raw = pd.DataFrame(data_raw)

# 替换空值为0，转换数值类型
stat_cols = [col for col in df_raw.columns if 'Stat' in col]
df_raw[stat_cols] = df_raw[stat_cols].replace('', '0').astype(int)

# 执行方法一的转换流程
melted_raw = df_raw.melt(
    id_vars=['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Re', 'Set', 'Set2', 'Set3'],
    var_name='Col',
    value_name='Value'
)
melted_raw[['Range', 'StatType']] = melted_raw['Col'].str.split(' ', expand=True)

result_raw = melted_raw.pivot_table(
    index=['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Re', 'Set', 'Set2', 'Set3', 'Range'],
    columns='StatType',
    values='Value',
    fill_value=0
).reset_index()
result_raw.columns = ['Start', 'Date', 'End', 'Area', 'Final', 'Type', 'Re', 'Set', 'Set2', 'Set3', 'Range', 'Stat', 'Stat1']

# 排序
order_map = {'Low': 0, 'Middle': 1, 'High': 2}
result_raw['sort_key'] = result_raw['Range'].map(order_map)
result_raw = result_raw.sort_values('sort_key').drop('sort_key', axis=1).reset_index(drop=True)

print(result_raw)

内容的提问来源于Stack Exchange，提问作者Lynn