数据字典的逐行映射键到新的数据字典

Question

我有两本关于数据帧LP3和ExeedenceDict的字典。ExeedenceDict是一个包含4个包含键'two','ten','twentyfive','onehundred'的数据的字典。LP3字典有'LP_DevilMalad', 'LP_Bloomington', 'LP_DevilEvans', 'LP_Deep', 'LP_Maple', 'LP_CubMaple', 'LP_Cottonwood', 'LP_Mill', 'LP_CubNrPreston'键

编辑:我不确定标题这个问题的最简洁的方式，但我认为标题符合我的要求。

在ExeedenceDict中的每个数据文件中都有一个列，其行值等于LP3字典中的键。

下面是我创建的two中ExeedenceDict的“空白”数据。使用代码：

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]}

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 

下面是LP_DevilMalad字典中键LP3的数据格式。本词典是通过读取10个excel电子表格中的数据而建立的。使用代码：

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)}

'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的键到Location列的ExeedenceDict数据格式中，我遇到了匹配每个数据文件的列值的问题。我们的目标是想出一个脚本，通过某种字典理解来迭代地完成所有这些工作。

请注意，two数据仅是LP3数据中的6个索引值，ten是第9个索引值，‘25’是第10个索引值，onehundred是第12个索引值。

基于上述两个数据框架的ExeedenceDict中的键ExeedenceDict的goale数据框架如下所示：

注意到其余的dataframe将被来自第6个索引的值填充，这些值来自LP3字典中其余的dataframe值。

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

Answer 1

如果没有一个可重复的示例，就无法测试它，但是我会按照下面的思路做一些事情：

index_map = {
    "two": 6,
    "ten": 9,
    "twentyfive": 10,
    "onehundred": 12
}

col_of_interest = ["Annual Exceedence", "With Reg Skew", "Without Reg Skew", "5% Lower", "95% Upper"]

for index_key, df in ExeedenceDict.items():
    lp_index = index_map[index_key]
    for lp_val in df['Location'].values:
        df.loc[df['Location'] == lp_val, col_of_interest] = LP3[lp_val].loc[lp_index, col_of_interest].values