如何将两类按地点月份统计的DataFrame合并为多层列索引结构？

我有两个DataFrame：

• 第一个是按**地点（location）和月份（report_month）**统计的货运单（Consignment Note，简称CN）总成本（命名为Total CN）
• 第二个是按地点和月份统计的车辆维护+燃油总成本（命名为Total Cost）

生成这两个DataFrame的代码如下：

def test():
    start_date = '2022-01-01'
    end_date = '2022-05-31'

    ###### First dataframe
    # Get monthly delivery log cost
    total_delivery_log_cost = monthly_delivery_log_cost_by_branch(start_date, end_date)

    # Get monthly pickup cost
    total_pickup_log_cost = monthly_pickup_log_cost_by_branch(start_date, end_date)

    # Union total monthly cost (CN)
    df = pd.concat([total_delivery_log_cost, total_pickup_log_cost])

    # Pivot
    df = df.pivot_table(index=['location'],columns =['report_month'], aggfunc = np.sum, fill_value=0)
    df.columns = df.columns.to_flat_index().to_series().apply(lambda x: x[1])
    df = df.reset_index()
    
    df.to_csv('total_CN.csv')

    ###### Second dataframe
    # Get monthly vehicle cost:
    total_maintainence_cost = monthly_maintainence_cost_by_branch(start_date, end_date)
    print(total_maintainence_cost)

    # Get monthly refuel cost:
    total_refuel_cost = monthly_refuel_cost_by_branch(start_date, end_date)
    print(total_refuel_cost)

    # Union total monthly maintainence cost
    df = pd.concat([total_maintainence_cost, total_refuel_cost])

    # Pivot
    df = df.pivot_table(index=['location'],columns =['report_month'], aggfunc = np.sum, fill_value=0)
    df.columns = df.columns.to_flat_index().to_series().apply(lambda x: x[1])
    df = df.reset_index()
    df.to_csv('total_cost.csv')
    print(df)
    
    print("Success")

样例数据 #

Total CN 样例 #

location  2022-01       2022-02   2022-03      2022-04    2022-05
   ABC        22.00       24.00      60.20        55.30     66.43
   XYZ        50.00       40.33      14.50        50.60     90.40
   XXX        10.00       21.20      22.40        23.40     22.11 
   ...         ...        ...        ....          ....       .....

Total Cost 样例 #

location    2022-01       2022-02   2022-03      2022-04    2022-05
       ABC        30.00       33.00      5.20         65.30      12.43
       XYZ        67.00       21.33      5.50         21.60      42.40
       QWE        10.00       34.20      53.40        34.40      22.11 
   ...         ...        ...        ....          ....       .....

需求 #

将这两个DataFrame合并为多层列索引结构，缺失值填充为0，最终效果如下：

2022-01                  2022-02              .......
location          Total CN  Total Cost     Total CN    Total Cost      .......
    ABC           22.00       30.00         24.00          33.00      
    XYZ           50.00       67.00         40.33          21.33
    XXX           10.00       0.00          21.20          0.00   
    QWE           0.00        10.00          0.00          34.20               
    ....          ....           ....          ....           .....

可以通过以下步骤实现：

1. 读取/获取两个DataFrame
   如果是从CSV读取：

   import pandas as pd
   
   # 读取生成的两个CSV文件
   df_cn = pd.read_csv('total_CN.csv', index_col=False)
   df_cost = pd.read_csv('total_cost.csv', index_col=False)
   

   若直接使用原函数生成的DataFrame，可修改原函数返回这两个变量，直接调用即可。

2. 为每个DataFrame添加多层列标识
   给Total CN的月份列加上'Total CN'层级，Total Cost的月份列加上'Total Cost'层级：

   # 处理Total CN：为月份列构造双层索引
   cn_cols = [('Total CN', col) for col in df_cn.columns if col != 'location']
   df_cn.columns = ['location'] + cn_cols
   
   # 处理Total Cost：同理构造双层索引
   cost_cols = [('Total Cost', col) for col in df_cost.columns if col != 'location']
   df_cost.columns = ['location'] + cost_cols
   

3. 合并DataFrame并填充缺失值
   按location列做全量合并，缺失值用0填充：

   merged_df = pd.merge(df_cn, df_cost, on='location', how='outer').fillna(0)
   

4. 调整多层列的顺序
   让同一月份下的Total CN和Total Cost相邻排列，最终将月份设为列的第一层：

   # 获取所有唯一月份并排序
   months = sorted(set(col[1] for col in merged_df.columns if col != 'location'))
   # 构造目标列顺序：每个月份下先Total CN，再Total Cost
   new_col_order = [('location', '')]
   for month in months:
       new_col_order.append(('Total CN', month))
       new_col_order.append(('Total Cost', month))
   # 重新排列列
   merged_df = merged_df.reindex(columns=new_col_order)
   # 转换为标准多层索引
   merged_df.columns = pd.MultiIndex.from_tuples(merged_df.columns)
   # 将location设为索引，并交换列层级（月份在上，成本类型在下）
   merged_df = merged_df.set_index('location').swaplevel(axis=1).sort_index(axis=1)
   

5. 输出结果
   可打印查看或保存为CSV：

   print(merged_df)
   merged_df.to_csv('merged_total.csv')
   

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