Pandas重塑宽表：从结构化列名提取多值实现宽表转长表

问题说明 #

• 待处理宽表固定保留列为id、classroom、city
• 其余共600个指标列命名规则统一：alumn_x_subject_y_mark、alumn_x_subject_y_name、alumn_x_subject_y_teacher，其中x取值范围为range(20)、y取值范围为range(10)，二者笛卡尔积生成200组维度组合，对应mark/name/teacher三类指标
• 目标输出长表需包含列：id、classroom、city、alumn、subject、mark、name、teacher，所有原指标列转换为对应行数据
• 宽表从外部文件读取，列结构固定不可提前修改，此前尝试melt、wide_to_long方法未确认适配性

测试样例数据生成代码 #

可运行以下代码生成同结构空DataFrame做功能验证：

import pandas as pd
import itertools
vals = list(itertools.product(*[range(20), range(10)]))
test_df = pd.DataFrame(columns=['id', 'classroom', 'city']+ \
             ['alumn_{0}_subject_{1}_mark'.format(x, y) for x, y in vals] + \
             ['alumn_{0}_subject_{1}_name'.format(x, y) for x, y in vals] + \
             ['alumn_{0}_subject_{1}_teacher'.format(x, y) for x, y in vals]
             , dtype=object)

适配实现代码 #

该方案不依赖列顺序，只要列名符合命名规则即可正确转换，完全适配外部读取数据的场景：

import pandas as pd

# df为外部读取的原始宽表
# 1. 固定列保留，所有指标列转为行
long_tmp = df.melt(
    id_vars=['id', 'classroom', 'city'],
    var_name='col_raw',
    value_name='value'
)

# 2. 从原列名中提取alumn编号、subject编号、指标类型
pattern = r'alumn_(\d+)_subject_(\d+)_(mark|name|teacher)'
long_tmp[['alumn', 'subject', 'metric']] = long_tmp['col_raw'].str.extract(pattern)
# 数值类型转换，匹配原编号的整数格式
long_tmp['alumn'] = long_tmp['alumn'].astype(int)
long_tmp['subject'] = long_tmp['subject'].astype(int)

# 3. 指标类型转为列，输出最终结构
final_df = long_tmp.pivot(
    index=['id', 'classroom', 'city', 'alumn', 'subject'],
    columns='metric',
    values='value'
).reset_index().rename_axis(columns=None)

方案说明 #

• 基于正则提取列名信息，不受列排列顺序影响，适配外部文件读入时列顺序不可控的场景
• 无需提前枚举x、y的所有取值组合，自动适配现有列结构
• 逻辑分层清晰，出现数据异常时可逐环节排查，维护成本低

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