python - Pandas 加入复制不正确

Question

我有三个数据框:

maindf  = pd.DataFrame({'Risk':['AB','AC','AD'],'amnt':[100,200,300]})

maindf
Out[4]: 
  Risk  amnt
0   AB   100
1   AC   200
2   AD   300

disc = pd.DataFrame({'Risk':['AB','AB','AB','AC','AC','AD'], 'discPerc':[0.4,0.5,0.1,0.5,0.5,1]})

disc
Out[7]: 
  Risk  discPerc
0   AB       0.4
1   AB       0.5
2   AB       0.1
3   AC       0.5
4   AC       0.5
5   AD       1.0

ops = pd.DataFrame({'Risk':['AB','AB','AC','AC','AD','AD'], 'opsPerc':[0.5,0.5,0.4,0.6,0.2,0.8]})

ops
Out[9]: 
  Risk  opsPerc
0   AB      0.5
1   AB      0.5
2   AC      0.4
3   AC      0.6
4   AD      0.2
5   AD      0.8

我想将数据帧连接到 maindf，这样如果我需要按“风险”列分组，我将使 discPerc 和 opsPerc 总和为 1(因为它们在 disc/ops 数据帧中)

一个简单的双左连接结果:

merged = pd.merge(maindf,disc,on='Risk',how='left')

merged = pd.merge(merged,ops, on = 'Risk', how = 'left')

merged
Out[19]: 
   Risk  amnt  discPerc  opsPerc
0    AB   100       0.4      0.5
1    AB   100       0.4      0.5
2    AB   100       0.5      0.5
3    AB   100       0.5      0.5
4    AB   100       0.1      0.5
5    AB   100       0.1      0.5
6    AC   200       0.5      0.4
7    AC   200       0.5      0.6
8    AC   200       0.5      0.4
9    AC   200       0.5      0.6
10   AD   300       1.0      0.2
11   AD   300       1.0      0.8

对此进行分组得到:

merged.groupby('Risk').sum()
Out[20]: 
      amnt  discPerc  opsPerc
Risk                         
AB     600       2.0      3.0
AC     800       2.0      2.0
AD     600       2.0      1.0

相反，我希望合并后的数据框看起来像:

   Risk  amnt  discPerc  opsPerc
0    AB   100       0.4      nan
1    AB   100       0.5      nan
2    AB   100       0.1      nan
3    AB   100       nan      0.5
4    AB   100       nan      0.5
6    AC   200       0.5      nan
7    AC   200       0.5      nan
8    AC   200       nan      0.4
9    AC   200       nan      0.6
10   AD   300       1.0      nan
11   AD   300       nan      0.2
12   AD   300       nan      0.8

这样我可以求和得到百分比为 1。

Answer 1

您可以连接 disc 和 ops，然后与原始 DataFrame 合并:

pd.concat((disc, ops)).merge(maindf)
Out: 
   Risk  discPerc  opsPerc  amnt
0    AB       0.4      NaN   100
1    AB       0.5      NaN   100
2    AB       0.1      NaN   100
3    AB       NaN      0.5   100
4    AB       NaN      0.5   100
5    AC       0.5      NaN   200
6    AC       0.5      NaN   200
7    AC       NaN      0.4   200
8    AC       NaN      0.6   200
9    AD       1.0      NaN   300
10   AD       NaN      0.2   300
11   AD       NaN      0.8   300