如何将Pandas DataFrame按Distance维度扁平化多层索引？

初始数据 #

import pandas as pd

data = {
    'Date': ['2021-06-15T00:10:00', '2021-06-15T00:10:00', '2021-06-15T00:10:00', '2021-06-15T00:20:00', '2021-06-15T00:20:00', '2021-06-15T00:20:00'],
    'Distance': ['50', '100', '150', '50', '100', '150'],
    'WS': [10, 20, 30, 40, 50, 60],
    'DIR': [11, 21, 31, 41, 51, 61]
}

df = pd.DataFrame(data)

输出：

Date Distance  WS  DIR
0  2021-06-15T00:10:00       50  10   11
1  2021-06-15T00:10:00      100  20   21
2  2021-06-15T00:10:00      150  30   31
3  2021-06-15T00:20:00       50  40   41
4  2021-06-15T00:20:00      100  50   51
5  2021-06-15T00:20:00      150  60   61

期望输出 #

WS_50  DIR_50  WS_100  DIR_100  WS_150  DIR_150
Date                                                                
2021-06-15 00:10:00     10      11      20       21      30       31
2021-06-15 00:20:00     40      41      50       51      60       61

当前问题 #

使用pivot后得到按WS/DIR分组的多层列索引，不符合需求：

df['Date'] = pd.to_datetime(df['Date'])
pivot_df = df.pivot(index='Date', columns='Distance', values=['WS', 'DIR'])

输出：

WS         DIR
Distance            100 150  50 100 150  50
Date
2021-06-15 00:10:00  20  30  10  21  31  11
2021-06-15 00:20:00  50  60  40  51  61  41

解决方法 #

方法1：调整多层列索引并合并列名

通过交换列索引层级、排序、合并列名实现目标格式：

# 交换列索引层级，将Distance移到外层
pivot_df = pivot_df.swaplevel(axis=1)

# 将Distance转为整数并按数值排序（确保50、100、150的顺序）
pivot_df.columns = pivot_df.columns.set_levels(pivot_df.columns.levels[0].astype(int), level=0)
pivot_df = pivot_df.sort_index(axis=1)

# 合并两层列索引为"指标_距离"的格式
pivot_df.columns = [f'{col[1]}_{col[0]}' for col in pivot_df.columns]

方法2：使用melt + pivot组合

先将宽表转为长表，再重新pivot：

# 转为长格式
melted_df = df.melt(id_vars=['Date', 'Distance'], var_name='Metric', value_name='Value')

# 重新pivot
result_df = melted_df.pivot(index='Date', columns=['Distance', 'Metric'], values='Value')

# 处理列名并排序
result_df.columns = [f'{col[1]}_{col[0]}' for col in result_df.columns]
result_df = result_df.sort_index(axis=1)

完整代码（方法1）

import pandas as pd

data = {
    'Date': ['2021-06-15T00:10:00', '2021-06-15T00:10:00', '2021-06-15T00:10:00', '2021-06-15T00:20:00', '2021-06-15T00:20:00', '2021-06-15T00:20:00'],
    'Distance': ['50', '100', '150', '50', '100', '150'],
    'WS': [10, 20, 30, 40, 50, 60],
    'DIR': [11, 21, 31, 41, 51, 61]
}

df = pd.DataFrame(data)
df['Date'] = pd.to_datetime(df['Date'])

# 生成pivot表
pivot_df = df.pivot(index='Date', columns='Distance', values=['WS', 'DIR'])

# 调整列层级并合并列名
pivot_df = pivot_df.swaplevel(axis=1)
pivot_df.columns = pivot_df.columns.set_levels(pivot_df.columns.levels[0].astype(int), level=0)
pivot_df = pivot_df.sort_index(axis=1)
pivot_df.columns = [f'{col[1]}_{col[0]}' for col in pivot_df.columns]

print(pivot_df)

输出结果与期望一致：

WS_50  DIR_50  WS_100  DIR_100  WS_150  DIR_150
Date                                                                
2021-06-15 00:10:00     10      11      20       21      30       31
2021-06-15 00:20:00     40      41      50       51      60       61

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