如何使用pd.wide_to_long转换宽表为长表并保留Trip Date索引？

问题背景 #

现有如下宽格式DataFrame：

import pandas as pd

citydata = pd.DataFrame(columns=['Trip Date','City 1 Name','City 2 Name','City 3 Name','City 1 Cost','City 2 Cost','City 3 Cost','City 1 Hotel','City 2 Hotel','City 3 Hotel'])
citydata.loc[0] = ['2023-01-15','London','Paris','Barcelona',5500,4375,2530,"Marriott","Hilton","W"]
citydata.loc[1] = ['2023-08-15','Sydney','Auckland','Beijing',9500,2300,11000,"Peninsula","Meridien","AirBnb"]

对应表格：

需要将其转换为长格式，最终表格包含Trip Date、City Name、City Cost、City Hotel四列，目标格式如下：

方法一：使用pd.wide_to_long #

pd.wide_to_long专为带重复前缀/后缀的宽格式数据设计，步骤如下：

1. 添加临时唯一标识列，确保每行数据可被唯一识别
2. 调用pd.wide_to_long，指定标识列、属性前缀、城市编号占位符
3. 清理结果，移除临时列并重置索引

代码实现：

# 添加临时行标识列
citydata['row_id'] = range(len(citydata))

# 执行宽转长
long_df = pd.wide_to_long(
    citydata,
    stubnames=['City Name', 'City Cost', 'City Hotel'],  # 列名中的属性部分
    i=['row_id', 'Trip Date'],  # 标识每行的列
    j='city_num',  # 城市编号的占位符
    sep=' ',  # 列名中的分隔符为空格
    suffix=r'\d+'  # 匹配列名中的数字编号
).reset_index()

# 移除不需要的临时列
long_df = long_df.drop(['row_id', 'city_num'], axis=1)

# 查看结果
print(long_df)

方法二：使用pd.melt结合透视 #

若对pd.wide_to_long参数不熟悉，可先用melt将数据转为极长格式，再拆分列名并透视：

代码实现：

# 先将非Trip Date列转为键值对
melted = citydata.melt(id_vars='Trip Date', var_name='city_attr', value_name='value')

# 拆分city_attr列，提取城市编号和属性
melted[['city_num', 'attr']] = melted['city_attr'].str.split(' ', n=2, expand=True)[[1, 2]]

# 透视将属性转为列
long_df = melted.pivot_table(
    index=['Trip Date', 'city_num'],
    columns='attr',
    values='value',
    aggfunc='first'
).reset_index()

# 重命名列并移除临时列
long_df = long_df.rename(columns={
    'Name': 'City Name',
    'Cost': 'City Cost',
    'Hotel': 'City Hotel'
}).drop('city_num', axis=1)

# 查看结果
print(long_df)

两种方法均可得到目标格式的长DataFrame，且保留了Trip Date作为每行的标识。

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