I’m attempting to match job lines to purchase orders on items within a date range while tracking the available quantity of the items.
If I have three dataframes:
joblines = pd.DataFrame({
'order': ['1-1', '1-1', '2-1', '3-1'],
'item': ['A1','A2','A1', 'A1'],
'startdate':[pd.Timestamp('2024-7-25'), pd.Timestamp('2024-7-25'), pd.Timestamp('2024-8-05'), pd.Timestamp('2024-9-02')],
'qty': [1, 2, 3, 3]
})
items = pd.DataFrame({
'item': ['A1', 'A2'],
'onhand':[2, 2]
})
polines = pd.DataFrame({
'po':['1','2','3'],
'item':['A1', 'A2', 'A1'],
'qty': [1, 1, 5],
'reqdate': [pd.Timestamp('2024-7-23'), pd.Timestamp('2024-7-26'), pd.Timestamp('2024-9-01')]
})
I’m attempting to get here:
| Order | Item | Start | Qty | PO | Req | Notes |
|---|---|---|---|---|---|---|
| 1-1 | A1 | 2024-07-25 | 1 | 1 | 2024-07-23 | OnHand |
| 1-1 | A2 | 2024-07-25 | 2 | 2 | 2024-07-26 | OnHand |
| 2-1 | A1 | 2024-08-05 | 3 | No Available PO | ||
| 3-1 | A2 | 2024-09-02 | 3 | 3 | 2024-09-01 |
So the first two lines are covered by existing quantities, the third line has no associated purchase order (+- 5 days between start and req), and the last line has a purchase order coming in to cover its quantity.
Is this possible using pandas?