Pandas pivot还是pivot_table？如何转换指定格式的DataFrame

问题背景 #

原始导入的Excel DataFrame格式如下：

DOW                 Location    7/30/2022   8/6/2022    8/13/2022   8/20/2022
    Volumes Saturday    North       33          32          29          24
    Volumes Saturday    South       34          17          30          28
    Volumes Sunday      North       40          57          25          28
    Volumes Sunday      South       47          38          32          45
    ACT Saturday        North       750         1060        1066        1082
    ACT Saturday        South       545         509         1306        1121
    ACT Sunday          North       801         860         572         795
    ACT Sunday          South       622         526         711         491

需要转换为如下目标格式：

DOW_Location            Location    Date        Volumes  ACT
Volumes Saturday North  North       7/30/2022   33       750
Volumes Sunday North    North       7/31/2022   40       801                
Volumes Saturday South  South       7/30/2022   34       545
Volumes Sunday South    South       7/31/2022   47       622

用户尝试通过df['DOW'] + df['Location']创建唯一索引，从DOW列提取工作日信息生成Category列后使用pivot()，但不清楚多列值场景下values参数的填写方式：

df = df.pivot(index='DOW_Location', columns='Category', values=?)

解决方案 #

方法1：拆分数据集后合并（直观易理解） #

先将Volumes和ACT两类数据拆分处理，再按匹配维度合并：

import pandas as pd

# 处理Volumes数据
volumes_df = df[df['DOW'].str.startswith('Volumes')].copy()
volumes_df['DOW_Clean'] = volumes_df['DOW'].str.replace('Volumes ', '')
# 宽表转长表，将日期列转为行
volumes_df = volumes_df.melt(id_vars=['DOW', 'Location', 'DOW_Clean'], 
                             var_name='Date', value_name='Volumes')
# 生成DOW_Location列
volumes_df['DOW_Location'] = volumes_df['DOW'] + ' ' + volumes_df['Location']

# 处理ACT数据
act_df = df[df['DOW'].str.startswith('ACT')].copy()
act_df['DOW_Clean'] = act_df['DOW'].str.replace('ACT ', '')
act_df = act_df.melt(id_vars=['DOW', 'Location', 'DOW_Clean'], 
                     var_name='Date', value_name='ACT')

# 修正Sunday日期：在对应Saturday日期基础上加1天
act_df['Date'] = pd.to_datetime(act_df['Date'])
volumes_df['Date'] = pd.to_datetime(volumes_df['Date'])
act_df.loc[act_df['DOW_Clean'] == 'Sunday', 'Date'] += pd.Timedelta(days=1)

# 合并两个数据集
result = pd.merge(volumes_df, act_df, on=['Location', 'DOW_Clean', 'Date'], how='left')
# 整理目标列并格式化日期
result = result[['DOW_Location', 'Location', 'Date', 'Volumes', 'ACT']]
result['Date'] = result['Date'].dt.strftime('%m/%d/%Y')
result = result.reset_index(drop=True)

方法2：使用stack+unstack批量重塑 #

先拆分DOW列提取指标类型和工作日，再通过堆叠/拆堆完成格式转换：

import pandas as pd

# 拆分DOW列为指标类型(Metric)和工作日(DOW_Clean)
df[['Metric', 'DOW_Clean']] = df['DOW'].str.split(' ', n=1, expand=True)
# 生成DOW_Location列
df['DOW_Location'] = df['DOW'] + ' ' + df['Location']

# 将日期列转为行（宽表转长表）
stacked = df.set_index(['DOW_Location', 'Location', 'Metric', 'DOW_Clean'])\
            .stack().reset_index(name='Value')
stacked.rename(columns={'level_4': 'Date'}, inplace=True)

# 将Metric转为列（长表转宽表）
pivoted = stacked.pivot(index=['DOW_Location', 'Location', 'DOW_Clean', 'Date'], 
                        columns='Metric', values='Value').reset_index()

# 修正Sunday日期并格式化
pivoted['Date'] = pd.to_datetime(pivoted['Date'])
pivoted.loc[pivoted['DOW_Clean'] == 'Sunday', 'Date'] += pd.Timedelta(days=1)
pivoted['Date'] = pivoted['Date'].dt.strftime('%m/%d/%Y')

# 整理目标列顺序
result = pivoted[['DOW_Location', 'Location', 'Date', 'Volumes', 'ACT']]

关于pivot的说明 #

直接使用pivot()无法处理原始数据中多日期列的场景，因为pivot()的values参数仅能指定单一列或固定列集合。需要先通过melt()或stack()将多日期列转为行数据，再进行pivot()操作，这也是方法2的核心思路。

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