如何加速DataFrame循环处理？宽表转换优化方案

问题描述 #

我需要遍历DataFrame中的每个纬度和经度，将数据转换为指定的宽表格式，但当前嵌套for循环的处理速度极慢。请问能否通过多线程、多进程等方式优化该脚本？请给出具体实现方法。

原始代码 #

p=0
for i in tqdm(df_wind_monthly["lat"]):
    for j in df_wind_monthly["lon"]:
        print("lat: " + str(i) + " lon: " + str(j))
        for k in range(1948,2017): 
            rslt_df_wind = df_wind_monthly.loc[(df_wind_monthly['lat'] == i) \
                                          & (df_wind_monthly['lon'] == j) \
                                              & (df_wind_monthly['year'] == k)]; rslt_df_wind = rslt_df_wind.reset_index()
            month_columns.loc[p,"lat"]=rslt_df_wind.loc[0,"lat"]
            month_columns.loc[p,"lon"]=rslt_df_wind.loc[0,"lon"]
            month_columns.loc[p,"years"]=rslt_df_wind.loc[0,"year"]
            month_columns.loc[p,"wind_January"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="January"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_February"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="February"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_March"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="March"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_April"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="April"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_May"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="May"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_June"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="June"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_July"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="July"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_August"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="August"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_September"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="September"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_October"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="October"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_November"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="November"].index.tolist()[0],"wind"]
            month_columns.loc[p,"wind_December"]=rslt_df_wind.loc[rslt_df_wind['month'].loc[lambda x: x=="December"].index.tolist()[0],"wind"]
            p+=1

输入输出示例 #

输入DataFrame（df_wind_monthly）：

time     lat     lon      wind
0       1948-01-16  15.125  15.125  6.509021
1       1948-01-16  15.125  15.375  6.485108
2       1948-01-16  15.125  15.625  6.472615
3       1948-01-16  15.125  15.875  6.472596
4       1948-01-16  15.125  16.125  6.486597

目标输出DataFrame结构：

month_columns=pd.DataFrame(columns=['lat',"lon","years","wind_January","wind_February","wind_March","wind_April","wind_May","wind_June","wind_July","wind_August","wind_September","wind_October","wind_November","wind_December"])

优化方案 #

方案1：优先使用Pandas原生透视表（最快最简洁） #

你的核心需求是将长表转换为宽表，这完全可以通过Pandas的pivot_table实现，无需嵌套循环。这种矢量化操作比循环快几个数量级，是最优解。

import pandas as pd

# 1. 从time列提取年份和月份名称
df_wind_monthly['year'] = pd.to_datetime(df_wind_monthly['time']).dt.year
df_wind_monthly['month'] = pd.to_datetime(df_wind_monthly['time']).dt.month_name()

# 2. 定义月份列名映射
month_mapping = {
    'January': 'wind_January',
    'February': 'wind_February',
    'March': 'wind_March',
    'April': 'wind_April',
    'May': 'wind_May',
    'June': 'wind_June',
    'July': 'wind_July',
    'August': 'wind_August',
    'September': 'wind_September',
    'October': 'wind_October',
    'November': 'wind_November',
    'December': 'wind_December'
}

# 3. 生成透视表
result = df_wind_monthly.pivot_table(
    index=['lat', 'lon', 'year'],
    columns='month',
    values='wind',
    aggfunc='first'  # 假设每个(lat, lon, year, month)组合唯一
).reset_index()

# 4. 调整列名和顺序
result = result.rename(columns=month_mapping)
result = result.rename(columns={'year': 'years'})

# 匹配目标列顺序
target_columns = ['lat', 'lon', 'years', 'wind_January', 'wind_February', 'wind_March', 
                  'wind_April', 'wind_May', 'wind_June', 'wind_July', 'wind_August', 
                  'wind_September', 'wind_October', 'wind_November', 'wind_December']
result = result[target_columns]

方案2：多进程优化（仅适用于超大规模数据） #

如果数据量极大，透视表仍有性能瓶颈，可以用多进程并行处理分组数据。由于Python的GIL限制，多线程对计算密集型任务提升有限，优先用多进程。

import pandas as pd
from multiprocessing import Pool, cpu_count

# 预处理：提取年份和月份
df_wind_monthly['year'] = pd.to_datetime(df_wind_monthly['time']).dt.year
df_wind_monthly['month'] = pd.to_datetime(df_wind_monthly['time']).dt.month_name()

# 定义月份映射和目标列（同方案1）
month_mapping = {
    'January': 'wind_January',
    'February': 'wind_February',
    'March': 'wind_March',
    'April': 'wind_April',
    'May': 'wind_May',
    'June': 'wind_June',
    'July': 'wind_July',
    'August': 'wind_August',
    'September': 'wind_September',
    'October': 'wind_October',
    'November': 'wind_November',
    'December': 'wind_December'
}
target_columns = ['lat', 'lon', 'years', 'wind_January', 'wind_February', 'wind_March', 
                  'wind_April', 'wind_May', 'wind_June', 'wind_July', 'wind_August', 
                  'wind_September', 'wind_October', 'wind_November', 'wind_December']

# 定义单分组处理函数
def process_group(group):
    lat, lon = group.name
    # 分组内生成透视表
    group_pivot = group.pivot_table(
        index='year',
        columns='month',
        values='wind',
        aggfunc='first'
    ).reset_index()
    # 添加经纬度列
    group_pivot['lat'] = lat
    group_pivot['lon'] = lon
    # 调整列名和顺序
    group_pivot = group_pivot.rename(columns=month_mapping)
    group_pivot = group_pivot.rename(columns={'year': 'years'})
    return group_pivot[target_columns]

# 多进程执行
if __name__ == '__main__':
    # 按经纬度分组
    groups = df_wind_monthly.groupby(['lat', 'lon'])
    # 使用CPU核心数-1避免资源耗尽
    with Pool(cpu_count() - 1) as pool:
        processed_results = pool.map(process_group, groups)
    # 合并所有结果
    final_result = pd.concat(processed_results, ignore_index=True)

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