如何在MultiIndex大DataFrame匹配shortCode行前插入小DataFrame对应行

问题说明 #

现有两个DataFrame：

小DataFrame（Spot数据） #

WS      period shortCode identifier
6        197.78  2023-03-10   TC2-FFA       spot
7        196.79  2023-03-10   TC5-FFA       spot
8        253.13  2023-03-10   TC6-FFA       spot
9        198.13  2023-03-13  TC12-FFA       spot
10       166.67  2023-03-10  TC14-FFA       spot
11       217.86  2023-03-10  TC17-FFA       spot
18        97.00  2023-03-10   TD3-FFA       spot
19       172.19  2023-03-10   TD7-FFA       spot
20       205.71  2023-03-13   TD8-FFA       spot
21       175.63  2023-03-10  TD19-FFA       spot
22       115.45  2023-03-10  TD20-FFA       spot
23  11350000.00  2023-03-10  TD22-FFA       spot
24       232.14  2023-03-10  TD25-FFA       spot

带MultiIndex的大DataFrame #

datumUnit                      $/mt       WS
identifier period shortCode                 
TC2BALMO   Mar 23 TC2-FFA    39.376  228.930
TC2CURMON  Mar 23 TC2-FFA    35.946  208.988
TC2+1_M    Apr 23 TC2-FFA    38.444  223.512
TC2+2_M    May 23 TC2-FFA    37.786  219.686
TC2+3_M    Jun 23 TC2-FFA    36.613  212.866
                            ...      ...
TD25+3Q    Q4 23  TD25-FFA   42.909  185.432
TD25+4Q    Q1 24  TD25-FFA   39.000      NaN
TD25+5Q    Q2 24  TD25-FFA   32.421      NaN
TD25+1CAL  Cal 24 TD25-FFA   34.250      NaN
TD25+2CAL  Cal 25 TD25-FFA   33.955      NaN

其MultiIndex结构：

MultiIndex([( 'TC2BALMO', 'Mar 23',  'TC2-FFA'),
            ('TC2CURMON', 'Mar 23',  'TC2-FFA'),
            (  'TC2+1_M', 'Apr 23',  'TC2-FFA'),
                  ...
            (  'TD25+4Q',  'Q1 24', 'TD25-FFA'),
            (  'TD25+5Q',  'Q2 24', 'TD25-FFA'),
            ('TD25+1CAL', 'Cal 24', 'TD25-FFA'),
            ('TD25+2CAL', 'Cal 25', 'TD25-FFA')],
           names=['identifier', 'period', 'shortCode'], length=198)

需求：将小DataFrame中的Spot行按shortCode匹配，插入到大DataFrame对应shortCode分组的顶部，且不改变大DataFrame原有行顺序。

实现方案 #

1. 预处理Spot数据 #

先调整小DataFrame的结构，使其与大DataFrame的索引、列对齐：

import pandas as pd

# 保留需要的列并调整顺序
spot_df = spot_df[["identifier", "period", "shortCode", "WS"]].copy()
# 转换日期格式为"23-03-10"样式
spot_df["period"] = spot_df["period"].str.replace("20", "").str.replace("-", "")
# 添加大DataFrame中存在的$/mt列，值设为缺失
spot_df["$/mt"] = pd.NA
# 设置与大DataFrame一致的MultiIndex
spot_df = spot_df.set_index(["identifier", "period", "shortCode"])

2. 分组插入Spot数据 #

按shortCode拆分大DataFrame，对每个分组插入对应的Spot行（如果存在），再合并所有分组：

# 存储处理后的分组
processed_groups = []

# 按shortCode遍历大DataFrame的分组
for short_code, group in big_df.groupby(level="shortCode"):
    # 获取当前shortCode对应的Spot行
    matching_spot = spot_df.xs(short_code, level="shortCode", drop_level=False) if short_code in spot_df.index.get_level_values("shortCode") else pd.DataFrame()
    # 将Spot行放在分组顶部，合并后加入列表
    processed_groups.append(pd.concat([matching_spot, group]))

# 合并所有分组得到最终结果
final_df = pd.concat(processed_groups)

结果说明 #

最终的final_df会在每个shortCode分组的最上方插入对应的Spot数据，同时完全保留大DataFrame原有行的顺序，符合预期效果。

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