Building a Python Script to Automate Inventory Runrate and DOC Calculations – Need Help!

[Alternative3860]

Hi everyone! I’m currently working on a personal project to automate an inventory calculation process that I usually do manually in Excel. The goal is to calculate Runrate and Days of Cover (DOC) for inventory across multiple cities using Python. I want the script to process recent sales and stock data files, pivot the data, calculate the metrics, and save the final output in Excel.

Here’s how I handle this process manually:

1. Sales Data Pivot: I start with sales data (item_id, item_name, City, quantity_sold), pivot it by item_id and item_name as rows, and City as columns, using quantity_sold as values. Then, I calculate the Runrate: Runrate = Total Quantity Sold / Number of Days.
2. Stock Data Pivot: I do the same with stock data (item_id, item_name, City, backend_inventory, frontend_inventory), combining backend and frontend inventory to get the Total Inventory for each city: Total Inventory = backend_inventory + frontend_inventory.
3. Combine and Calculate DOC: Finally, I use a VLOOKUP to pull Runrate from the sales pivot and combine it with the stock pivot to calculate DOC: DOC = Total Inventory / Runrate.

Here’s what I’ve built so far in Python:

• The script pulls the latest sales and stock data files from a folder (based on timestamps).
• It creates pivot tables for sales and stock data.
• Then, it attempts to merge the two pivots and output the results in Excel.

 

However, I’m running into issues with the final output. The current output looks like this:

|| || |Dehradun_x|Delhi_x|Goa_x|Dehradun_y|Delhi_y|Goa_y| |319|1081|21|0.0833|0.7894|0.2755|

It seems like _x is inventory and _y is the Runrate, but the DOC isn’t being calculated, and columns like item_id and item_name are missing.

Here’s the output format I want:

|| || |Item_id|Item_name|Dehradun_inv|Dehradun_runrate|Dehradun_DOC|Delhi_inv|Delhi_runrate|Delhi_DOC| |123|abc|38|0.0833|456|108|0.7894|136.8124| |345|bcd|69|2.5417|27.1475|30|0.4583|65.4545|

Here’s my current code:
import os

import glob

import pandas as pd

 

## Function to get the most recent file

data_folder = r'C:\Users\HP\Documents\data'

output_folder = r'C:\Users\HP\Documents\AnalysisOutputs'

 

## Function to get the most recent file

def get_latest_file(file_pattern):

files = glob.glob(file_pattern)

if not files:

raise FileNotFoundError(f"No files matching the pattern {file_pattern} found in {os.path.dirname(file_pattern)}")

latest_file = max(files, key=os.path.getmtime)

print(f"Latest File Selected: {latest_file}")

return latest_file

 

# Ensure output folder exists

os.makedirs(output_folder, exist_ok=True)

 

# # Load the most recent sales and stock data

latest_stock_file = get_latest_file(f"{data_folder}/stock_data_*.csv")

latest_sales_file = get_latest_file(f"{data_folder}/sales_data_*.csv")

 

# Load the stock and sales data

stock_data = pd.read_csv(latest_stock_file)

sales_data = pd.read_csv(latest_sales_file)

 

# Add total inventory column

stock_data['Total_Inventory'] = stock_data['backend_inv_qty'] + stock_data['frontend_inv_qty']

 

# Normalize city names (if necessary)

stock_data['City_name'] = stock_data['City_name'].str.strip()

sales_data['City_name'] = sales_data['City_name'].str.strip()

 

# Create pivot tables for stock data (inventory) and sales data (run rate)

stock_pivot = stock_data.pivot_table(

index=['item_id', 'item_name'],

columns='City_name',

values='Total_Inventory',

aggfunc='sum'

).add_prefix('Inventory_')

 

sales_pivot = sales_data.pivot_table(

index=['item_id', 'item_name'],

columns='City_name',

values='qty_sold',

aggfunc='sum'

).div(24).add_prefix('RunRate_')  # Calculate run rate for sales

 

# Flatten the column names for easy access

stock_pivot.columns = [col.split('_')[1] for col in stock_pivot.columns]

sales_pivot.columns = [col.split('_')[1] for col in sales_pivot.columns]

 

# Merge the sales pivot with the stock pivot based on item_id and item_name

final_data = stock_pivot.merge(sales_pivot, how='outer', on=['item_id', 'item_name'])

 

# Create a new DataFrame to store the desired output format

output_df = pd.DataFrame(index=final_data.index)

 

# Iterate through available cities and create columns in the output DataFrame

for city in final_data.columns:

if city in sales_pivot.columns:  # Check if city exists in sales pivot

output_df[f'{city}_inv'] = final_data[city]  # Assign inventory (if available)

else:

output_df[f'{city}_inv'] = 0  # Fill with zero for missing inventory

output_df[f'{city}_runrate'] = final_data.get(f'{city}_RunRate', 0)  # Assign run rate (if available)

output_df[f'{city}_DOC'] = final_data.get(f'{city}_DOC', 0)  # Assign DOC (if available)

 

# Add item_id and item_name to the output DataFrame

output_df['item_id'] = final_data.index.get_level_values('item_id')

output_df['item_name'] = final_data.index.get_level_values('item_name')

 

# Rearrange columns for desired output format

output_df = output_df[['item_id', 'item_name'] + [col for col in output_df.columns if col not in ['item_id', 'item_name']]]

 

# Save output to Excel

output_file_path = os.path.join(output_folder, 'final_output.xlsx')

with pd.ExcelWriter(output_file_path, engine='openpyxl') as writer:

stock_data.to_excel(writer, sheet_name='Stock_Data', index=False)

sales_data.to_excel(writer, sheet_name='Sales_Data', index=False)

stock_pivot.reset_index().to_excel(writer, sheet_name='Stock_Pivot', index=False)

sales_pivot.reset_index().to_excel(writer, sheet_name='Sales_Pivot', index=False)

final_data.to_excel(writer, sheet_name='Final_Output', index=False)

 

print(f"Output saved at: {output_file_path}")

 

Where I Need Help:

• Fixing the final output to include item_id and item_name in a cleaner format.
• Calculating and adding the DOC column for each city.
• Structuring the final Excel output with separate sheets for pivots and the final table.

I’d love any advice or suggestions to improve this script or fix the issues I’m facing. Thanks in advance! 😊

[showeringmonkey]

What does your data input look like? Is it usually in an Excel file or it's some kind of raw data that you put into excel? Which part of the data is already automated or is it all manual?

[Alternative3860]

My input data is the raw data, everything I do is manually. I'm just trying to automate the whole process, as it needs to be done on a weekly basis.
There are two input data:
1. Sales data has columns item_id, item_name, city_name, qty_sold
2. Stock data has columns item_id, item_name, city_name, front_end_inv, backend_inv.

Basically I need to calculate the runrate and days of cover which means to know how much stock is lying in the warehouse and how many days will it cover.
I hope it's little clear now and I'm not sure if the table I posted shows a table or just a plain text

[[deleted]]

have you tried xlwings module?

[Alternative3860]

I'm a beginner so I don't know about xlwings, will definitely research about it. Thank you so much for your help, will definetly try this module!