如何从DataFrame列名提取城市名并合并同类型指标数据列？

问题描述 #

现有一个包含多城市气象指标及负载缺口数据的DataFrame，结构如下：

#   Column                Non-Null Count  Dtype         
---  ------                --------------  -----         
 0   time                  8763 non-null   datetime64[ns]
 1   Madrid_wind_speed     8763 non-null   float64       
 2   Valencia_wind_deg     8763 non-null   object        
 3   Bilbao_rain_1h        8763 non-null   float64       
 4   Valencia_wind_speed   8763 non-null   float64       
 5   Seville_humidity      8763 non-null   float64       
 6   Madrid_humidity       8763 non-null   float64       
 7   Bilbao_clouds_all     8763 non-null   float64       
 8   Bilbao_wind_speed     8763 non-null   float64       
 9   Seville_clouds_all    8763 non-null   float64       
 10  Bilbao_wind_deg       8763 non-null   float64       
 11  Barcelona_wind_speed  8763 non-null   float64       
 12  Barcelona_wind_deg    8763 non-null   float64       
 13  Madrid_clouds_all     8763 non-null   float64       
 14  Seville_wind_speed    8763 non-null   float64       
 15  Barcelona_rain_1h     8763 non-null   float64       
 16  Seville_pressure      8763 non-null   object        
 17  Seville_rain_1h       8763 non-null   float64       
 18  Bilbao_snow_3h        8763 non-null   float64       
 19  Barcelona_pressure    8763 non-null   float64       
 20  Seville_rain_3h       8763 non-null   float64       
 21  Madrid_rain_1h        8763 non-null   float64       
 22  Barcelona_rain_3h     8763 non-null   float64       
 23  Valencia_snow_3h      8763 non-null   float64       
 24  Madrid_weather_id     8763 non-null   float64       
 25  Barcelona_weather_id  8763 non-null   float64       
 26  Bilbao_pressure       8763 non-null   float64       
 27  Seville_weather_id    8763 non-null   float64       
 28  Valencia_pressure     6695 non-null   float64       
 29  Seville_temp_max      8763 non-null   float64       
 30  Madrid_pressure       8763 non-null   float64       
 31  Valencia_temp_max     8763 non-null   float64       
 32  Valencia_temp         8763 non-null   float64       
 33  Bilbao_weather_id     8763 non-null   float64       
 34  Seville_temp          8763 non-null   float64       
 35  Valencia_humidity     8763 non-null   float64       
 36  Valencia_temp_min     8763 non-null   float64       
 37  Barcelona_temp_max    8763 non-null   float64       
 38  Madrid_temp_max       8763 non-null   float64       
 39  Barcelona_temp        8763 non-null   float64       
 40  Bilbao_temp_min       8763 non-null   float64       
 41  Bilbao_temp           8763 non-null   float64       
 42  Barcelona_temp_min    8763 non-null   float64       
 43  Bilbao_temp_max       8763 non-null   float64       
 44  Seville_temp_min      8763 non-null   float64       
 45  Madrid_temp           8763 non-null   float64       
 46  Madrid_temp_min       8763 non-null   float64       
 47  load_shortfall_3h     8763 non-null   float64       
dtypes: datetime64[ns](1), float64(45), object(2)

需要实现两个目标：

1. 从列名中提取城市名，作为单独的新列
2. 将同类指标（如wind_speed、rain_1h）的数据合并到对应列中

解决方案 #

通过列名拆分和数据重塑两步完成，具体代码实现如下：

1. 导入依赖并准备数据 #

import pandas as pd
# 假设原DataFrame名为df，这里省略数据加载步骤

2. 拆分列名并转换为长表格式 #

# 排除不需要转换的全局列：time和load_shortfall_3h
target_cols = df.columns.drop(['time', 'load_shortfall_3h'])

# 拆分列名为城市名和指标名（仅拆分第一个下划线，避免破坏带下划线的指标名）
col_info = {col: col.split('_', maxsplit=1) for col in target_cols}
# 将拆分结果转为DataFrame，方便后续合并
col_df = pd.DataFrame.from_dict(col_info, orient='index', columns=['city', 'metric'])

# 将宽表转为长表，保留全局列作为标识
melted_data = df.melt(
    id_vars=['time', 'load_shortfall_3h'],
    value_vars=target_cols,
    var_name='original_column',
    value_name='metric_value'
)

# 合并城市和指标信息，清理冗余列
melted_data = melted_data.merge(col_df, left_on='original_column', right_index=True)
melted_data = melted_data.drop('original_column', axis=1)

执行后得到长表格式结果，包含time、load_shortfall_3h、city、metric、metric_value五列，每个城市的每个指标对应一行数据。

3. 可选：转换为宽表格式 #

如果需要将同类指标作为单独列（每个城市在每个时间点对应一行），可使用pivot_table进一步转换：

wide_format_data = melted_data.pivot_table(
    index=['time', 'city', 'load_shortfall_3h'],
    columns='metric',
    values='metric_value',
    aggfunc='first'  # 确保每个指标只保留一个值
).reset_index()

转换后，wind_speed、rain_1h等指标会成为单独的列，每个城市的所有指标在同一行展示。

注意事项 #

• 拆分列名时使用maxsplit=1，确保wind_deg、rain_1h这类带下划线的指标名不会被错误拆分
• 原数据中的object类型列（如Valencia_wind_deg）会保留原有类型，无需额外处理
• 若存在缺失值，pivot_table会自动填充NaN，可根据需求用fillna处理

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