如何将含连续编号列的DataFrame纵向合并为长格式数据

问题背景 #

现有包含传感器数据及来源标识的DataFrame df1，结构如下：

需要将其转换为长格式DataFrame df_all，保留Product、Date等标识，最终结构如下：

原始实现方式需要反复重命名列，且无法适配列名存在差异的场景（例如Sensor 1 data可能变为Sensor value 1）：

df1.rename(columns={"Sensor 1 data": "Sensor data", "Sensor 1 cal.": "Sensor cal."})
df_all = pd.concat([df_all, df1['Product','Date','Sensor data', 'Sensor cal.']])

df1.rename(columns={"Sensor 2": "Sensor data", "Sensor 2 cal.": "Sensor cal."})
df_all = pd.concat([df_all, df1['Product','Date','Sensor data', 'Sensor cal.']])

解决方案 #

方法1：使用pd.melt（固定列名场景） #

针对当前列名规则，可通过melt方法一次性完成转换，无需多次重命名和拼接：

import pandas as pd

# 定义需要保留的标识列
id_vars = ['Product', 'Date']

# 映射数据列与校准列分组
value_groups = {
    'Sensor data': ['Sensor 1 data', 'Sensor 2 data'],
    'Sensors cal.': ['Sensor 1 cal.', 'Sensor 2 cal.']
}

# 分别对数据列、校准列执行melt，再合并结果
df_data = pd.melt(df1, id_vars=id_vars, value_vars=value_groups['Sensor data'], value_name='Sensor data').drop('variable', axis=1)
df_cal = pd.melt(df1, id_vars=id_vars, value_vars=value_groups['Sensors cal.'], value_name='Sensors cal.').drop('variable', axis=1)

df_all = pd.concat([df_data, df_cal['Sensors cal.']], axis=1)

方法2：正则匹配列名（适配列名差异场景） #

如果不同文件的列名存在变化，可通过正则表达式自动识别传感器数据列和校准列，实现通用转换：

import pandas as pd
import re

# 提取固定标识列
id_vars = ['Product', 'Date']

# 用正则匹配传感器数据列（支持"Sensor 1 data"、"Sensor value 1"等格式）
data_cols = [col for col in df1.columns if re.search(r'Sensor.*\d.*(data|value)', col)]
# 匹配校准列
cal_cols = [col for col in df1.columns if re.search(r'Sensor.*\d.*cal', col)]

# 按传感器编号排序，确保数据列与校准列一一对应
data_cols.sort(key=lambda x: int(re.search(r'\d+', x).group()))
cal_cols.sort(key=lambda x: int(re.search(r'\d+', x).group()))

# 执行转换并合并
df_data = pd.melt(df1, id_vars=id_vars, value_vars=data_cols, value_name='Sensor data').drop('variable', axis=1)
df_cal = pd.melt(df1, id_vars=id_vars, value_vars=cal_cols, value_name='Sensors cal.').drop('variable', axis=1)

df_all = pd.concat([df_data, df_cal['Sensors cal.']], axis=1)

方法3：wide_to_long简洁实现（列名有规律场景） #

利用pandas.wide_to_long方法，通过统一列名后缀实现转换，适合列名规则明确的场景：

import pandas as pd

# 调整列名格式为"前缀_编号"，适配wide_to_long要求
df1.columns = ['Product', 'Date', 'Sensor_data_1', 'Sensor_data_2', 'Sensor_cal_1', 'Sensor_cal_2']

# 执行宽表转长表
df_all = pd.wide_to_long(df1, stubnames=['Sensor_data', 'Sensor_cal'], i=['Product', 'Date'], j='Sensor').reset_index(drop=True)

# 重命名为目标列名
df_all = df_all.rename(columns={'Sensor_data': 'Sensor data', 'Sensor_cal': 'Sensors cal.'})

以上方法均无需反复重命名和拼接，其中方法2可灵活适配不同列名规则，适合批量处理多个文件的场景。

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