there are 3 dataframes ( Inventory, supply and shipment)
df1: Inventory snapshot as of Apr 30th
| index | Item code | Inv | Date |
|---|---|---|---|
| 0 | 56013 | 2 | 2024-04-30 00:00:00 |
| 1 | 56014B | 2 | 2024-04-30 00:00:00 |
| 2 | 56012 | 2 | 2024-04-30 00:00:00 |
df2: Supply plan (from supplier) which plans to receive
| index | Item code | P | Date |
|---|---|---|---|
| 0 | 56013 | 10 | 2024-05-01 00:00:00 |
| 1 | 56014B | 10 | 2024-05-10 00:00:00 |
| 2 | 56012 | 10 | 2024-05-03 00:00:00 |
df3: shipment ( to customer) plan
| index | Item code | Ship_Qty | Date |
|---|---|---|---|
| 0 | 56013 | 1 | 2024-05-01 00:00:00 |
| 1 | 56014B | 2 | 2024-05-02 00:00:00 |
| 2 | 56012 | 1 | 2024-05-03 00:00:00 |
| 3 | 56013 | 2 | 2024-05-04 00:00:00 |
| 4 | 56014B | 3 | 2024-05-05 00:00:00 |
| 5 | 56012 | 1 | 2024-05-06 00:00:00 |
| 6 | 56013 | 4 | 2024-05-07 00:00:00 |
| 7 | 56014B | 1 | 2024-05-08 00:00:00 |
| 8 | 56012 | 1 | 2024-05-09 00:00:00 |
| 9 | 56013 | 2 | 2024-05-10 00:00:00 |
| 10 | 56014B | 2 | 2024-05-11 00:00:00 |
| 11 | 56012 | 1 | 2024-05-12 00:00:00 |
I want to develop inventory projection new dataframe by items, so I coded to merge all 3 dataframes to combine based on ‘Date’ columen as below
df4 = df1.merge(df2, on=’Date’, how=’left’).merge(df3, on=’Date’, how=’left’)
Then I wanted to show specific items only on dataframe
for example
Item code : 56013
I tried code anticipating to show ‘56013’only
filtered_df = df4[df4[‘Item code’] ==56013]
but did not come out
Expeted logic is:
Beginning of Inventory Apr 30th (2)+ Supply plan May 1st (10)- Shipment May1st (1) = Ending Inventory May 1st (11)
Expected Out is below
| Date | P | Ship_Qty | Inv |
|---|---|---|---|
| 2024-4-30 | 0 | 0 | 2 |
| 2024-5-1 | 10 | 1 | 11 |
| 2024-5-2 | 0 | 0 | 11 |
| 2024-5-3 | 0 | 0 | 11 |
| 2024-5-4 | 0 | 2 | 10 |
I tried merge 3 dataframes on “Date” and cumsum function to output ending inventory, but not really work.
Please someone help for coding for expecting outcome.
3