Rolling subtraction over rows, multiple keys

Question

I have two dataframes that look like this:

df1:

| item| order | qty_ordered |
|-----|-------|-------------|
| 1   |   a1  |  5          |
| 1   |   a2  |  7          |
| 2   |   a3  |  10         |
| 2   |   a4  |  8          |

df2:

| item| on_hand|
|-----|--------|
| 1   |   26   |
| 2   |   12   |

I need to take the qty_ordered for each item and subtract sequentially them from the on_hand value.

So desired output would be:

| item| order | qty_ordered | net |
|-----|-------|-------------|-----|
| 1   |   a1  |  5          |  21 |
| 1   |   a2  |  7          |  14 |
| 2   |   a3  |  10         |  2  |
| 2   |   a4  |  8          | -6  |

I have tried a few different options with .rolling() and .groupby(item) .diff() but no luck.

Any help would be greatly appreciated!

Answer 1

You can try merge then groupby on item and cumulative sum on qty_ordered then subtract from on_hand:

out = df1.merge(df2,on='item') #how='left'
out['net'] = out['on_hand'].sub(out.groupby("item")['qty_ordered'].cumsum())

Or with map , same logic:

df1['net'] = (df1['item'].map(df2.set_index("item")['on_hand'])
               .sub(df1.groupby("item")['qty_ordered'].cumsum()))

---

print(out)

   item order  qty_ordered  on_hand  net
0     1    a1            5       26   21
1     1    a2            7       26   14
2     2    a3           10       12    2
3     2    a4            8       12   -6