如何在dataframe中按给定的百分比将一个金额分成5个产品

Question

我有2个数据帧，如下DF1和DF2

DF1：我正在从Excel文件导入此文件

 DF1 
  Product             % to be applied in Exp
0   00028                  0.1080
1   00031                  0.2067
2   00045                  0.3469
3   00089                  0.1877
4   00099                  0.1507
5   Total                  1.0000

DF2：我是从一个Excel文件中导入这个文件的，该文件包含数千个扩展细节。然而，我只计算了3行

 DF2
   Account    CC Account Dis  Exp Amount
0   523761  US12   Admin Exp    50000.12
1   578212  9999  Travle Exp    89673.89
2   578511  0234       Rent     50202.99

我想把它放在下面。DF2的每一行Exp金额必须由每个产品从DF1中按其百分比分割。有没有办法解决这个问题

输出

>>> Output
    Account    CC Account Dis  Product    Exp Amount
0    523761  US12   Admin Exp       28   5400.012960
1    523761  US12   Admin Exp       31  10335.024804
2    523761  US12   Admin Exp       45  17345.041628
3    523761  US12   Admin Exp       89   9385.022524
4    523761  US12   Admin Exp       99   7535.018084
5    578212  9999  Travle Exp       28   9684.780120
6    578212  9999  Travle Exp       31  18535.593063
7    578212  9999  Travle Exp       45  31107.872441
8    578212  9999  Travle Exp       89  16831.789153
9    578212  9999  Travle Exp       99  13513.855223
10   578511  0234       Rent        28   5421.922920
11   578511  0234       Rent        31  10376.958033
12   578511  0234       Rent        45  17415.417231
13   578511  0234       Rent        89   9423.101223
14   578511  0234       Rent        99   7565.590593

Answer 1

您可以交叉连接这两个表，并将Exp Amount与% to be applied in Exp相乘。然后删除不相关的列

df1.drop(df1.index[df1['Product'] == 'Total'], inplace = True)
df1['Product'] = pd.to_numeric(df1["Product"])
df1['key'], df2['key'] = 1,1
df3 = df2.merge(df1, on = 'key').drop('key', 1)
df3['Exp Amount'] = df3['Exp Amount'].multiply(df3['% to be applied in Exp'])
df3.drop('% to be applied in Exp', 1, inplace = True)
df3 = df3.reindex(columns = ['Account', 'CC', 'Account Dis', 'Product', 'Exp Amount'])
df3
>>>
    Account    CC Account Dis  Product    Exp Amount
0    523761  US12   Admin Exp       28   5400.012960
1    523761  US12   Admin Exp       31  10335.024804
2    523761  US12   Admin Exp       45  17345.041628
3    523761  US12   Admin Exp       89   9385.022524
4    523761  US12   Admin Exp       99   7535.018084
5    578212  9999  Travle Exp       28   9684.780120
6    578212  9999  Travle Exp       31  18535.593063
7    578212  9999  Travle Exp       45  31107.872441
8    578212  9999  Travle Exp       89  16831.789153
9    578212  9999  Travle Exp       99  13513.855223
10   578511  0234        Rent       28   5421.922920
11   578511  0234        Rent       31  10376.958033
12   578511  0234        Rent       45  17415.417231
13   578511  0234        Rent       89   9423.101223
14   578511  0234        Rent       99   7565.590593