如何按键映射将LP3的DataFrame特定行填充到ExeedenceDict？

我有两个Pandas DataFrame字典LP3和ExeedenceDict：

1. ExeedenceDict：包含4个DataFrame，键为'two'、'ten'、'twentyfive'、'onehundred'。每个DataFrame的Location列值与LP3的键完全一致，仅Location和Size列有数据，其余列初始为NaN。创建代码如下：

ExeedenceDF = []
cols = ['Location','Size','Annual Exceedence', 'With Reg Skew','Without Reg Skew','5% Lower','95% Upper']
for i in range(5):
  i = pd.DataFrame(columns=cols)
  i['Location'] = LP_names
  i['Size'] = [39.8,24,34,29.7,21.2,53.7,61.7,27.6,31.6]
  ExeedenceDF.append(i)
ExeedenceDict = {'two':ExeedenceDF[0], 'ten':ExeedenceDF[1], 'twentyfive':ExeedenceDF[2], 'onehundred':ExeedenceDF[3]}

空白DataFrame示例（以two为例）：

Location    Size    Annual Exceedence   With Reg Skew   Without Reg Skew    5% Lower    95% Upper
0   LP_DevilMalad   39.8    NaN     NaN     NaN     NaN     NaN
1   LP_Bloomington  24.0    NaN     NaN     NaN     NaN     NaN
2   LP_DevilEvans   34.0    NaN     NaN     NaN     NaN     NaN
3   LP_Deep         29.7    NaN     NaN     NaN     NaN     NaN
4   LP_Maple        21.2    NaN     NaN     NaN     NaN     NaN
5   LP_CubMaple     53.7    NaN     NaN     NaN     NaN     NaN
6   LP_Cottonwood   61.7    NaN     NaN     NaN     NaN     NaN
7   LP_Mill         27.6    NaN     NaN     NaN     NaN     NaN
8   LP_CubNrPreston 31.6    NaN     NaN     NaN     NaN     NaN 

2. LP3：键为9个位置标识（如LP_DevilMalad），每个对应一个处理后的Excel数据DataFrame，处理代码如下：

LP_names = ['LP_DevilMalad', 'LP_Bloomington', 'LP_DevilEvans', 'LP_Deep', 'LP_Maple', 'LP_CubMaple', 'LP_Cottonwood', 'LP_Mill', 'LP_CubNrPreston']
for i, df in enumerate(LP_Data):
  LP_Data[i] = LP_Data[i].dropna()
  LP_Data[i]['Annual Exceedence'] = 1 / LP_Data[i]['Annual Exceedence']
  LP_Data[i] = LP_Data[i].loc[LP_Data[i]['Annual Exceedence'].isin([2, 10, 25, 100])]
LP3 = {k:v for (k,v) in zip(LP_names, LP_Data)}

DataFrame示例（以LP_DevilMalad为例）：

'LP_DevilMalad':     Annual Exceedence  With Reg Skew  Without Reg Skew  Log Variance of Est  \
 6                 2.0           21.4              22.4               0.0091   
 9                10.0           46.5              44.7               0.0119   
 10               25.0           60.2              54.6               0.0166   
 12              100.0           81.4              67.4               0.0270   
 
     5% Lower  95% Upper  
 6       14.1       31.2  
 9       32.1       85.7  
 10      40.6      136.2  
 12      51.3      250.6 

需求 #

需要将LP3中每个位置对应的特定行数据填充到ExeedenceDict对应键的DataFrame中：

• ExeedenceDict['two']对应LP3各DataFrame的索引6行
• ten对应索引9行
• twentyfive对应索引10行
• onehundred对应索引12行
  要求用字典推导式完成批量填充，最终效果示例（以two为例）：

Location    Size    Annual Exceedence   With Reg Skew   Without Reg Skew    5% Lower    95% Upper
0   LP_DevilMalad   39.8    2   21.4    22.4    14.1    31.2
1   LP_Bloomington  24.0    NaN     NaN     NaN     NaN     NaN
2   LP_DevilEvans   34.0    NaN     NaN     NaN     NaN     NaN
3   LP_Deep         29.7    NaN     NaN     NaN     NaN     NaN
4   LP_Maple        21.2    NaN     NaN     NaN     NaN     NaN
5   LP_CubMaple     53.7    NaN     NaN     NaN     NaN     NaN
6   LP_Cottonwood   61.7    NaN     NaN     NaN     NaN     NaN
7   LP_Mill         27.6    NaN     NaN     NaN     NaN     NaN
8   LP_CubNrPreston 31.6    NaN     NaN     NaN     NaN     NaN

先建立ExeedenceDict键与LP3中目标索引的映射关系，再通过字典推导式批量处理每个DataFrame：

# 定义键到目标索引的映射
key_index_map = {
    'two': 6,
    'ten': 9,
    'twentyfive': 10,
    'onehundred': 12
}

# 用字典推导式批量填充ExeedenceDict
ExeedenceDict = {
    key: (
        # 复制原DataFrame避免修改原始数据
        df.copy()
        # 遍历需要填充的列，通过Location匹配LP3中的对应数据
        .assign(**{
            col: lambda x: x['Location'].map(
                lambda loc: LP3[loc].at[target_idx, col] if target_idx in LP3[loc].index else pd.NA
            )
            for col in ['Annual Exceedence', 'With Reg Skew', 'Without Reg Skew', '5% Lower', '95% Upper']
        })
    )
    for key, df in ExeedenceDict.items()
    for target_idx in [key_index_map[key]]
}

代码说明 #

1. 映射关系：key_index_map明确了两个字典间的索引对应规则，逻辑清晰且便于后续修改维护。
2. 批量填充：通过字典推导式遍历ExeedenceDict的所有键和DataFrame，对每个需要填充的列，利用Location列的映射关系，从LP3中提取对应索引的行数据。
3. 异常处理：加入if target_idx in LP3[loc].index的判断，避免因索引不存在导致的报错；使用copy()确保原始空白数据不被修改。

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