Python Pandas中转换DataFrame格式：宽表转长表实现方法

给定的宽格式数据集包含用户ID、分组标识，以及work/play/talk三类指标在T1/T2两个时间点的数值，需要转换为每个唯一ID对应两行（T1、T2时间点）的长格式，以下是两种高效实现方式：

方法一：使用pd.wide_to_long（推荐） #

该函数专门针对"前缀.后缀"格式的宽表设计，适配性更强：

import pandas as pd

# 原数据集定义
dt = {'id': {0: 'x1', 1: 'x2', 2: 'x3', 3: 'x4', 4: 'x5', 5: 'x6', 6: 'x7', 7: 'x8', 8: 'x9', 9: 'x10'},
'trt': {0: 'cnt', 1: 'cnt', 2: 'tr', 3: 'tr', 4: 'tr', 5: 'cnt', 6: 'tr', 7: 'tr', 8: 'cnt', 9: 'cnt'},
'work.T1': {0: 0.6516556669957936, 1: 0.567737752571702, 2: 0.1135089821182191, 3: 0.5959253052715212, 4: 0.3580499750096351, 5: 0.4288094183430075, 6: 0.0519033221062272, 7: 0.2641776674427092, 8: 0.3987907308619469, 9: 0.8361341434065253},
'play.T1': {0: 0.8647212258074433, 1: 0.6153524168767035, 2: 0.7751098964363337, 3: 0.3555686913896352, 4: 0.4058499720413238, 5: 0.7066469138953835, 6: 0.8382876652758569, 7: 0.2395891312044114, 8: 0.7707715332508087, 9: 0.3558977444190532},
'talk.T1': {0: 0.5355970377568156, 1: 0.0930881295353174, 2: 0.169803041499108, 3: 0.8998324507847428, 4: 0.4226376069709658, 5: 0.7477464678231627, 6: 0.8226525799836963, 7: 0.9546536463312804, 8: 0.6854445093777031, 9: 0.5005032296758145},
'work.T2': {0: 0.2754838624969125, 1: 0.2289039448369294, 2: 0.0144339059479534, 3: 0.7289645625278354, 4: 0.2498804717324674, 5: 0.1611832766793668, 6: 0.0170426501426845, 7: 0.4861003451514989, 8: 0.1029001718852669, 9: 0.8015470046084374},
'play.T2': {0: 0.3543280649464577, 1: 0.9364325392525644, 2: 0.2458663922734558, 3: 0.4731414613779634, 4: 0.191560871200636, 5: 0.5832219698932022, 6: 0.4594731898978352, 7: 0.467434047954157, 8: 0.3998325555585325, 9: 0.5052855962421745},
'talk.T2': {0: 0.0318881559651345, 1: 0.1144675880204886, 2: 0.468935475917533, 3: 0.3969867376144975, 4: 0.8336191941052675, 5: 0.7611217433586717, 6: 0.5733564489055425, 7: 0.447508045937866, 8: 0.0838020080700516, 9: 0.2191385473124683}}
mydt = pd.DataFrame(dt, columns=['id', 'trt', 'work.T1', 'play.T1', 'talk.T1', 'work.T2', 'play.T2', 'talk.T2'])

# 宽转长操作
long_dt = pd.wide_to_long(
    mydt,
    stubnames=['work', 'play', 'talk'],  # 指定指标名称前缀
    i=['id', 'trt'],  # 保留的标识符列
    j='time',  # 生成的时间列名称
    sep='.'  # 列名中前缀与后缀的分隔符
).reset_index()

转换后的数据结构：

• 每行对应一个ID在某个时间点的观测
• 新增time列存储T1/T2
• work/play/talk列为对应时间点的数值

方法二：使用pd.melt #

如果需要更灵活的列拆分逻辑，可先融合宽表再拆分复合列名：

# 第一步：融合所有数值列
melted = pd.melt(
    mydt,
    id_vars=['id', 'trt'],  # 保留的标识符列
    var_name='metric_time',  # 融合后的复合列名
    value_name='value'  # 数值列名称
)

# 第二步：拆分复合列为指标和时间维度
melted[['metric', 'time']] = melted['metric_time'].str.split('.', expand=True)

# 第三步：整理列顺序并删除临时列
long_dt = melted.drop('metric_time', axis=1)[['id', 'trt', 'time', 'metric', 'value']]

这种方法适合列名格式不固定的场景，最终生成的长表包含metric列（区分work/play/talk）和time列（T1/T2）。

转换后示例数据片段 #

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