How to combine a sales forecast, planned deliveries, and current inventory to project future inventory using python pandas

user_7560115 0
Viewed 106

I have three data sources:

Sales Forecast - This is future forecasted sales for products:

Product Forecast_Quantity Forecast_Month
Product A 5 2021-02-28
Product B 6 2021-02-28
Product C 2 2021-02-28
Product A 5 2021-03-31
Product B 6 2021-03-31
Product C 2 2021-03-31
Product A 5 2021-04-30
Product B 6 2021-04-30
Product C 2 2021-04-30

Planned Deliveries (Purchase Orders) - what is planned to be delivered in:

Product Delivery_Quantity Delivery_Month
Product A 2 2021-02-28
Product B 4 2021-02-28
Product C 5 2021-02-28
Product A 8 2021-03-31
Product B 2 2021-03-31
Product C 4 2021-03-31
Product A 2 2021-04-30
Product B 6 2021-04-30
Product C 3 2021-04-30

Current inventory - what is currently in stock:

Product Inventory_Quantity Inventory_Month
Product A 20 2021-01-31
Product B 16 2021-01-31
Product C 21 2021-01-31

I wish to create a small program that returns a dataframe projecting my future inventory for each product. It should take last months closing inventory add on the delivery quantity and take off the forecasted sales. Expected output:

Product Inventory_Quantity Inventory_Month
Product A 20 2021-01-31
Product B 16 2021-01-31
Product C 21 2021-01-31
Product A 17 2021-02-28
Product B 14 2021-02-28
Product C 24 2021-02-28

It would be for 18 months of data (but I've just used a 2 months of output for the above table).

I've have tried a few different approaches, such as trying cusum() or using a for loop but I don't my logic has been right.

This is the code I have so far that combines the forecast and deliveries into a 'net' figure:

import pandas as pd
import numpy as np

# Import CSV files as dataframe
fcs = pd.read_csv(r'forecast.csv')
inv = pd.read_csv(r'inventory.csv')
pos = pd.read_csv(r'purchase_orders.csv')

# Inner join to get a net position for each month (PO Qty - Fcs Qty)
net = pd.merge(pos, fcs, left_on=['SKU', 'Delivery_Date'], right_on=['SKU', 'Forecast_Date'])

# Create net
net['net'] = (net['PO_Quantity'] - net['Forecast_Quantity'])

I'm unsure on the best approach to now generate the new table that combines with the inventory?

forecasting pandas dataframe python-3.x python
1 Answers

Use concat to vertically combine the three dataframes. Then, fillna the inventory columns with the others and melt the dataframe. From there, you can take the cumsum:

df = (pd.concat([df1.assign(Forecast_Quantity=df1['Forecast_Quantity'] * -1)
                    .rename({'Forecast_Month' : 'Inventory_Month', 
                            'Forecast_Quantity' : 'Inventory_Quantity'}, axis=1),
                 df2.rename({'Delivery_Month' : 'Inventory_Month', 
                            'Delivery_Quantity' : 'Inventory_Quantity'}, axis=1),
                 df3]).sort_values(['Product', 'Inventory_Month']))
df['Inventory_Quantity'] = df.groupby('Product')['Inventory_Quantity'].cumsum()
df = (df.groupby(['Product', 'Inventory_Month'], as_index=False).last()
        .sort_values('Inventory_Month'))
df

Out[1]: 
      Product Inventory_Month  Inventory_Quantity
0   Product A      2021-01-31                20.0
4   Product B      2021-01-31                16.0
8   Product C      2021-01-31                21.0
1   Product A      2021-02-28                17.0
5   Product B      2021-02-28                14.0
9   Product C      2021-02-28                24.0
2   Product A      2021-03-31                20.0
6   Product B      2021-03-31                10.0
10  Product C      2021-03-31                26.0
3   Product A      2021-04-30                17.0
7   Product B      2021-04-30                10.0
11  Product C      2021-04-30                27.0
user_63667700
Related