My Manager Needed to Know What to Reorder: Building an Inventory Report with Python, Pandas, and CSV
My Manager Needed to Know What to Reorder: Building an Inventory Report with Python, Pandas, and CSV
My manager had a simple question: What do we need to reorder?
The answer was somewhere inside an inventory spreadsheet containing product numbers, quantities, reorder levels, and suppliers. The problem was that nobody wanted to sit there comparing every quantity against every reorder level by hand.
That sounded like a job for a small data pipeline.
We already had the inventory data. We knew the business rule. And we knew what the manager needed at the other end: a short list containing only the products that required attention.
The flow looked like this:
Inventory CSV → Python + pandas → Reorder CSV
That's it.
The program doesn't need to place orders, contact suppliers, or manage the warehouse. Its job is simply to take a larger inventory file and turn it into an actionable report.
The Data Going In
Here is a small version of the inventory file:
sku,product,quantity_on_hand,reorder_level,supplier
A100,USB-C Cable,42,20,Metro Supply
A101,Wireless Mouse,12,15,Central Office
A102,Laptop Stand,8,10,Metro Supply
A103,Webcam,27,10,Digital Wholesale
A104,Keyboard,15,15,Central Office
A105,USB Hub,31,12,Digital Wholesale
Every row represents one product.
For this example, we have the SKU, product name, quantity currently on hand, reorder level, and supplier.
Now we need a rule.
We decide that an item belongs on the reorder report whenever its
quantity_on_hand
is
less than or equal to
its
reorder_level.
That means we already know what needs to happen to the data before we've written any Python.
The data comes in.
The program compares two columns.
The rows requiring attention come out.
The Program
We can do the entire job with Python and pandas.
More importantly, we can keep the whole pipeline together so it's easy to see where the data starts, what happens to it, and where it ends.
import pandas as pd
# Input: Read the current inventory.
inventory = pd.read_csv("inventory.csv", dtype={"sku": str})
# Process: Make sure quantities and reorder levels are numbers.
inventory["quantity_on_hand"] = pd.to_numeric(
inventory["quantity_on_hand"]
)
inventory["reorder_level"] = pd.to_numeric(
inventory["reorder_level"]
)
# Process: Find products that have reached
# or fallen below their reorder level.
reorder = inventory[
inventory["quantity_on_hand"] <= inventory["reorder_level"]
].copy()
# Process: Calculate how many units are below the reorder level.
reorder["units_below_level"] = (
reorder["reorder_level"] - reorder["quantity_on_hand"]
)
# Output: Write the reorder report.
reorder.to_csv("reorder_report.csv", index=False)
Read from top to bottom, and there isn't much mystery here.
The inventory CSV comes in. The quantity fields become numbers. pandas compares the current quantity with the reorder level. Products that meet our rule are copied into a new DataFrame.
We also calculate
units_below_level. An item sitting exactly at its reorder level gets a zero, while an item
already below that level shows the difference.
Then the resulting rows go into a new file:
reorder_report.csv
That's the pipeline.
The Data Coming Out
Our original inventory contained six products.
The manager doesn't need all six for this particular task. The manager needs to know which products have reached or fallen below the reorder level.
The resulting
reorder_report.csv
looks like this:
sku,product,quantity_on_hand,reorder_level,supplier,units_below_level
A101,Wireless Mouse,12,15,Central Office,3
A102,Laptop Stand,8,10,Metro Supply,2
A104,Keyboard,15,15,Central Office,0
Now the business question is much easier to answer.
The wireless mouse has fallen three units below its reorder level. The laptop stand is two units below. The keyboard has reached its reorder level, so it appears in the report even though it hasn't fallen below it yet.
The other products disappear from this particular report because they don't currently require attention.
That's our transformation:
inventory.csv
→ Python + pandas →
reorder_report.csv
The original file described the entire inventory.
The new file describes what the manager needs to look at.
That difference is the reason the pipeline exists.
Now It's the Manager's Data
At this point, Python has done its job.
The manager can open
reorder_report.csv
in Excel, review the products and suppliers, decide how much inventory to
purchase, and begin the company's normal ordering process.
Our program doesn't need to know how purchasing works. It doesn't need to generate purchase orders or send anything to a supplier.
That work belongs downstream.
Our little pipeline had a much narrower responsibility: take the inventory data, apply a business rule, and produce a useful artifact for the person making the next decision.
That's an important idea when building small business applications.
We didn't start by asking, What can we do with pandas?
We started with a manager asking:
What do we need to reorder?
From there, the data told us what the program needed to do.
The code is the pipe.
The data, and where it's going, is the story.
