基于字典和当前Groupby之间的差异创建行

Question

    Ode  Proceeds  Pos        Amount  Positions  Target  Weighting  Additions
0   676     30160  FPE   51741.25000          5    0.10   0.187636        NaN
1   676     30160  HFA   57299.63616          5    0.20   0.207794        NaN
2   676     30160  PFL   60437.40563          5    0.20   0.219173        NaN
3   676     30160  PSO   53053.57410          5    0.15   0.192396        NaN
4   676     30160  RNS   53220.36636          5    0.20   0.193001        NaN
5   953     34960  PFL    8506.19390          1    0.20   1.000000        NaN
6   637     14750  PFL    8341.21701          3    0.20   0.302517        NaN
7   637     14750  PSO   12669.65078          3    0.15   0.459499        NaN
8   637     14750  RNS    6561.85824          3    0.20   0.237984        NaN
9   673     12610  FPE   31220.47500          5    0.10   0.175041        NaN
10  673     12610  HFA   34020.29280          5    0.20   0.190738        NaN
11  673     12610  PFL   37754.00236          5    0.20   0.211672        NaN
12  673     12610  PSO   31492.56779          5    0.15   0.176566        NaN
13  673     12610  RNS   43873.58472          5    0.20   0.245982        NaN
14  318     93790  PFL   59859.39180          2    0.20   0.285266        NaN
15  318     93790  PSO  149977.71090          2    0.15   0.714734        NaN
16  222     75250  FPE   21000.00000          6    0.10   0.100000     7525.0
17  222     75250  HFA   42000.00000          6    0.20   0.200000    15050.0
18  222     75250  PFL   42000.00000          6    0.20   0.200000    15050.0
19  222     75250  PSO   31500.00000          6    0.15   0.150000    11287.5
20  222     75250  RNS   42000.00000          6    0.20   0.200000    15050.0
21  222     75250  CRD   31500.00000          6    0.15   0.150000    11287.5

下面的信息是所需的输出-简单地从上面的信息的前5行中剪切出来，显示新的列['Target Amount']以及最后一行的创建-当您比较Ode 676时，它在下面字典中的6个Pos中有5个。因为ODE676缺少CRD，所以我需要一种方法来创建一行并填写信息

target_dict = {"PFL":.20,"RNS":.20,"HFA":.20,"PSO":.15,"CRD":.15,"FPE":.10}

        Ode  Proceeds  Pos        Amount  Positions  Target  Weighting   Target Amt     Additions
    0   676     30160  FPE   51741.25000          5    0.10   0.187636   30591.22       -21150.03
    1   676     30160  HFA   57299.63616          5    0.20   0.207794   61182.45       3882.81
    2   676     30160  PFL   60437.40563          5    0.20   0.219173   61182.45       745.04
    3   676     30160  PSO   53053.57410          5    0.15   0.192396   45886.83       -7166.74
    4   676     30160  RNS   53220.36636          5    0.20   0.193001   61182.45       7962.08
    5   676     30160  CRD   0                         0.15   0          45886.83       45886.83

CRD将被添加到6个位置，然后['Target Amt']将基于所有['Amount']加上['Proceeds']的总和来计算ODE676的总数。我可以计算出计算结果，但是我无法根据ode676的'target_dict'和当前['Pos']之间的差异为Ode添加行，其中['Positions'] < 6。你知道吗

Answer 1

您可以将^{}与^{}一起使用，这将创建“Ode”的唯一值与target_dict的每个键之间的所有组合，例如：

df_all = (df.set_index(['Ode', 'Pos']) #first set index for reindex them after
            .reindex(pd.MultiIndex.from_product([df.Ode.unique(), target_dict.keys()],
                                                 names = ['Ode','Pos']))
            .reset_index()) # index back as columns
print (df_all) #note I took rows for Ode = 676 and 953 only
    Ode  Pos  Proceeds       Amount  Positions  Target  Weighting
0   676  PFL   30160.0  60437.40563        5.0    0.20   0.219173
1   676  RNS   30160.0  53220.36636        5.0    0.20   0.193001
2   676  HFA   30160.0  57299.63616        5.0    0.20   0.207794
3   676  PSO   30160.0  53053.57410        5.0    0.15   0.192396
4   676  CRD       NaN          NaN        NaN     NaN        NaN
5   676  FPE   30160.0  51741.25000        5.0    0.10   0.187636
6   953  PFL   34960.0   8506.19390        1.0    0.20   1.000000
7   953  RNS       NaN          NaN        NaN     NaN        NaN
8   953  HFA       NaN          NaN        NaN     NaN        NaN
9   953  PSO       NaN          NaN        NaN     NaN        NaN
10  953  CRD       NaN          NaN        NaN     NaN        NaN
11  953  FPE       NaN          NaN        NaN     NaN        NaN

现在要在查找时完成数据，可以尝试^{}、^{}和groupby.transform：

# fillna some columns with 0
df_all.Amount = df_all.Amount.fillna(0)
df_all.Weighting = df_all.Weighting.fillna(0)
# map the dictionary to get the values in target column
df_all.Target = df_all.Pos.map(target_dict)
# create the groupby Ode
gr = df_all.groupby('Ode')
# fill Proceeds and Positions with the first not nan value in the group
df_all.Proceeds = gr.Proceeds.transform('first')
df_all.Positions = gr.Positions.transform('first')
# create the columns Target_amt and Additions according to your equation
df_all['Target_Amt'] = (gr.Amount.transform(sum) + df_all.Proceeds)*df_all.Target
df_all['Additions'] =  df_all.Amount - df_all.Target_Amt

你会得到：

print (df_all)
    Ode  Pos  Proceeds       Amount  Positions  Target  Weighting  \
0   676  PFL   30160.0  60437.40563        5.0    0.20   0.219173   
1   676  RNS   30160.0  53220.36636        5.0    0.20   0.193001   
2   676  HFA   30160.0  57299.63616        5.0    0.20   0.207794   
3   676  PSO   30160.0  53053.57410        5.0    0.15   0.192396   
4   676  CRD   30160.0      0.00000        5.0    0.15   0.000000   
5   676  FPE   30160.0  51741.25000        5.0    0.10   0.187636   
6   953  PFL   34960.0   8506.19390        1.0    0.20   1.000000   
7   953  RNS   34960.0      0.00000        1.0    0.20   0.000000   
8   953  HFA   34960.0      0.00000        1.0    0.20   0.000000   
9   953  PSO   34960.0      0.00000        1.0    0.15   0.000000   
10  953  CRD   34960.0      0.00000        1.0    0.15   0.000000   
11  953  FPE   34960.0      0.00000        1.0    0.10   0.000000   

       Additions    Target_Amt  
0    -745.040820  61182.446450  
1   -7962.080090  61182.446450  
2   -3882.810290  61182.446450  
3    7166.739262  45886.834837  
4  -45886.834837  45886.834837  
5   21150.026775  30591.223225  
6    -187.044880   8693.238780  
7   -8693.238780   8693.238780  
8   -8693.238780   8693.238780  
9   -6519.929085   6519.929085  
10  -6519.929085   6519.929085  
11  -4346.619390   4346.619390