Pandas：一对多加入+剩余价值余额新行：带发票的拆分合同

我有两张桌子

数据框合同

    CtrNo  Quantity product
0  S001      -400       A
1  S002      -200       B
2  S003      -150       C

数据框发票

   InvNo CtrNo  Quantity ShipmentNo
0  IN001  S001        55      SH001
1  IN002  S001        45      SH002
2  IN003  S001        15           
3  IN004  S003        10      SH003
4  IN005  S002        50      SH004
5  IN006  S001        25           
6  IN007  S002        15           
7  IN008  S003        45      SH005
8  IN009  S001         5           
9  IN010  S002        10      SH006

第二张表详细列出了已开具发票的内容，“装运”描述了已开具发票的产品。

我需要向他们开具发票数量的合同，然后添加剩余余额的新行。我无法使用pd.merge做同样的事情。

输出表应如下所示

    InvNo CtrNo Product  Quantity ShipmentNo        Status
0   IN001  S001       A        85      SH001           NaN
1   IN002  S001       A        45      SH002           NaN
2   IN003  S001       A        15        NaN           NaN
3   IN006  S001       A        25        NaN           NaN
4   IN009  S001       A         5        NaN           NaN
5     NaN  S001       A       225        NaN  not invoiced
6   IN005  S002       B        50      SH004           NaN
7   IN007  S002       B        15        NaN           NaN
8   IN010  S002       B        10      SH006           NaN
9     NaN  S002       B       125        NaN  not invoiced
10  IN004  S003       C        10      SH003           NaN
11  IN008  S003       C        45      SH005           NaN
12    NaN  S003       C        95        NaN  not invoiced

下面是创建输入数据框的代码

d = {'CtrNo': ['S001', 'S002','S003'], 
     'Quantity': [-400, -200 ,-150] ,
     'product': ['A' , 'B'  ,'C']}

contracts = pd.DataFrame(d)


i = {'InvNo' :["IN001","IN002","IN003",
                       "IN004","IN005","IN006","IN007","IN008","IN009","IN010"],
             'CtrNo' :["S001","S001","S001","S003",
                       "S002","S001","S002","S003","S001","S002"],
               'Quantity' :[55, 45, 15, 10, 50, 25, 15, 45, 5, 10],
        'ShipmentNo' : ["SH001","SH002","","SH003",
                       "SH004","","","SH005","","SH006"] }

invoices = pd.DataFrame(i)

问题来源：stackoverflow