高级Pandas数据预处理：多列组Melt与列合并

问题背景 #

需要将包含多组运营商（Operator）及对应座位车型价格的宽格式数据集转换为长格式，用于构建以Operator和Seats为特征的价格预测模型。原数据集存在空值（部分车型报价缺失或运营商数量较少）。

原数据集 #

目标格式 #

尝试的代码（存在匹配错误） #

import pandas as pd

# 创建原始DataFrame
df = pd.DataFrame({
    'Mileage': [80],
    'Operator 1': ['BusRus'],
    '16-Seater': ['£199'],
    '29-Seater': ['£299'],
    'Operator 2': ['Wheels'],
    '16-Seater.1': ['£190'],
    '29-Seater.1': ['£290']
})

# 宽表转长表
df_melt = pd.melt(df, id_vars=['Mileage', 'Operator 1', 'Operator 2'],
                  var_name='Seats', value_name='Price')

# 拆分Seats列
df_melt[['NumSeats', 'VehicleType']] = df_melt['Seats'].str.split('-', expand=True)

# 匹配运营商（逻辑错误）
df_melt['Operator'] = df_melt.apply(lambda x: x['Operator 1'] if '1' in x['Seats'] else x['Operator 2'], axis=1)

# 清理列
df_melt = df_melt.drop('Seats', axis=1).rename(columns={'Operator 1': 'Dummy1', 'Operator 2': 'Dummy2'})
df_melt = df_melt.drop(['Dummy1', 'Dummy2'], axis=1)[['Mileage', 'Operator', 'NumSeats', 'Price']]

print(df_melt)

错误结果 #

问题原因 #

原代码判断运营商的逻辑错误：用'1' in x['Seats']匹配时，29-Seater中包含字符'1'，会被错误分配给Operator 2；而16-Seater.1中的'1'是列后缀，应该属于Operator 2，却被误判为Operator 1。

修正方案 #

正确的逻辑是根据列名的后缀区分归属：无.1后缀的价格列属于Operator 1，带.1后缀的属于Operator 2。同时统一Seats列的格式。

修正后的代码 #

import pandas as pd

# 创建原始DataFrame
df = pd.DataFrame({
    'Mileage': [80],
    'Operator 1': ['BusRus'],
    '16-Seater': ['£199'],
    '29-Seater': ['£299'],
    'Operator 2': ['Wheels'],
    '16-Seater.1': ['£190'],
    '29-Seater.1': ['£290']
})

# 处理Operator 1的数据
df_op1 = df[['Mileage', 'Operator 1', '16-Seater', '29-Seater']].copy()
df_op1 = df_op1.melt(id_vars=['Mileage', 'Operator 1'], var_name='Seats', value_name='Price')
df_op1['Operator'] = df_op1['Operator 1']
# 统一Seats格式为"X Seater"
df_op1['Seats'] = df_op1['Seats'].str.replace('-', ' ')

# 处理Operator 2的数据
df_op2 = df[['Mileage', 'Operator 2', '16-Seater.1', '29-Seater.1']].copy()
df_op2 = df_op2.melt(id_vars=['Mileage', 'Operator 2'], var_name='Seats', value_name='Price')
df_op2['Operator'] = df_op2['Operator 2']
# 去掉后缀并统一格式
df_op2['Seats'] = df_op2['Seats'].str.replace('.1', '').str.replace('-', ' ')

# 合并两个子表，整理列顺序
final_df = pd.concat([df_op1, df_op2], ignore_index=True)
final_df = final_df[['Mileage', 'Operator', 'Seats', 'Price']]

print(final_df)

执行结果 #

更通用的扩展方案（支持更多运营商） #

如果数据集有更多运营商（比如Operator 3、Operator 4），可以用循环批量处理：

import pandas as pd

# 示例：假设有3个运营商的扩展数据
df = pd.DataFrame({
    'Mileage': [80],
    'Operator 1': ['BusRus'],
    '16-Seater': ['£199'],
    '29-Seater': ['£299'],
    'Operator 2': ['Wheels'],
    '16-Seater.1': ['£190'],
    '29-Seater.1': ['£290'],
    'Operator 3': ['CoachCorp'],
    '16-Seater.2': ['£205'],
    '29-Seater.2': ['£305']
})

final_dfs = []
# 遍历运营商数量（这里假设最多3个，可根据实际调整）
for i in range(1, 4):
    op_col = f'Operator {i}'
    # 匹配对应运营商的价格列：无后缀（i=1）或带.{i-1}后缀
    if i == 1:
        price_cols = [col for col in df.columns if 'Seater' in col and '.' not in col]
    else:
        suffix = f'.{i-1}'
        price_cols = [col for col in df.columns if col.endswith(suffix)]
    # 提取子表并处理
    sub_df = df[['Mileage', op_col] + price_cols].copy()
    sub_df = sub_df.melt(id_vars=['Mileage', op_col], var_name='Seats', value_name='Price')
    sub_df['Operator'] = sub_df[op_col]
    # 清理Seats列格式
    sub_df['Seats'] = sub_df['Seats'].str.replace(r'\.\d+$', '', regex=True).str.replace('-', ' ')
    final_dfs.append(sub_df[['Mileage', 'Operator', 'Seats', 'Price']])

# 合并所有结果
final_df = pd.concat(final_dfs, ignore_index=True)
print(final_df)

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