[Python] Cumulative Sum by Week in dataframe with additions and subtractions based on...

I'm trying to get a baseline supply dataframe showing me what is available by item and org for each week, starting with the current week. I have one dataframe for on-hand quantities that represents what's available this week, and another dataframe with work orders that includes the org, item, completion week, and quantity.

My goal is to project from the current week up to 52 weeks in the future. I want to start with the current on-hand values, and for each org and item combination, I’ll add the work order quantities to the on-hand inventory in the week they are completed. Additionally, I need to subtract/remove the on-hand quantity starting the week after its expiration (e.g., if the expiration week is 46, the quantity is removed in week 47).

Below is what I have been working with, along with some sample data, but I'm pretty sure I'm doing it completely wrong (dataframe rookie). Any help would be much appreciated.

Onhand Inventory (as of today)

org_id,item_id,expiration_week,qty
300000158934064,100000550480114,51,100
300000158934088,100000550480114,44,200
300000158934088,100000550476976,41,300
300000158934088,100000550476784,47,400
300000158934076,100000550476784,49,500
300000158934076,100000550476703,48,600

Planned Workorders

org_id,item_id,completion_week,qty
300000158934064,100000550480114,45,50
300000158934088,100000550480114,49,100
300000158934088,100000550476784,43,150
300000158934076,100000550476784,49,200
300000158934076,100000550476703,50,250


# Merge the dataframes on org_id and inventory_item_id
df = pd.merge(onhand, work_orders, on=['org_id', 'inventory_item_id'], how='outer')

# Fill NaN values with 0 (if any)
df = df.fillna(0)

# Calculate the cumulative sum of quantity in df2
df['cumulative_qty'] = df.groupby(['org_id', 'inventory_item_id'])['qty_y'].cumsum()

# Update the qty in df2 using the cumulative summary
df['updated_qty'] = df['qty_x'] + df['cumulative_qty']

# Sort values by weeknumber
df = df.sort_values(['org_id', 'inventory_item_id', 'weeknumber'])

# Reset index
df = df.reset_index(drop=True)

# Pivot the dataframe
pivot_df = df.pivot_table(index=['org_id', 'inventory_item_id'],
columns='weeknumber',
values='updated_qty',
fill_value=0)

# Calculate the cumulative sum across the weeks
pivot_df = pivot_df.cumsum(axis=1)

# Reset index
pivot_df = pivot_df.reset_index()




Continue reading...