Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Build a Warehouse Pick List from Orders

Turn the day's open orders into printable pick lists with pandas: allocate each order line to stock in specific bins, oldest stock first, record shortages instead of over-allocating, combine identical items across orders into one batch pick, sort the picks in a snake path through the aisles so the picker never doubles back, and write one sheet per picker with tick boxes and a put-to-order table.
Report layouts & templatespandas

Every morning a warehouse turns the orders that came in overnight into pick lists: which item to take from which bin, how many, and in what order to walk the aisles. Warehouse management systems do this, but many smaller operations run on a stock spreadsheet and an order export, and the pick lists are put together by hand — slowly, and with stock promised twice when two orders want the last units in a bin. The logic is not complicated, and pandas handles it well. This page builds pick lists from an order file and a bin-level stock file: each order line is allocated to specific bins, oldest stock first, and shortages are recorded rather than allocated; identical items across orders are combined into one pick; the picks are sorted into a snake path through the aisles; and each picker gets a printable Excel sheet with a tick column and a table for sorting the picked items back into orders. It belongs to Building Finance and Operations Reports.

Prerequisites

Bash
pip install pandas openpyxl

Two inputs: the open order lines (order, SKU, quantity) and stock by bin, where each bin code is zone–aisle–position — A-02-05 is zone A, aisle 2, position 5 — with the quantity on hand and the date the stock was received. Twelve order lines from six orders are picked today.

Step 1 — Allocate order lines to bins, oldest stock first

Python
import pandas as pd

orders = pd.read_excel("open_orders.xlsx")
stock = pd.read_excel("bin_stock.xlsx", parse_dates=["Received"]).sort_values(["SKU", "Received"])
available = stock.set_index("Bin")["On hand"].to_dict()

allocations, shortages = [], []
for line in orders.itertuples(index=False):
    need = line.Qty
    for b in stock[stock["SKU"] == line.SKU].itertuples(index=False):
        take = min(need, available[b.Bin])
        if take > 0:
            allocations.append({"Order": line.Order, "SKU": line.SKU, "Bin": b.Bin, "Qty": take})
            available[b.Bin] -= take
            need -= take
        if need == 0:
            break
    if need:
        shortages.append({"Order": line.Order, "SKU": line.SKU, "Ordered": line.Qty, "Short": need})

alloc = pd.DataFrame(allocations)
short = pd.DataFrame(shortages)
assert alloc.groupby("Bin")["Qty"].sum().le(stock.set_index("Bin")["On hand"]).all()
print(alloc.to_string(index=False))
print(short.to_string(index=False))

Each order line takes stock from the bins that hold its SKU, starting with the bin received earliest so older stock leaves first — first in, first out, which matters for anything with a shelf life or a risk of becoming obsolete. A running dictionary of what is still available in each bin is the important part: when order SO-502 asks for ten WID-A, the oldest bin has only six left after SO-501, so it takes those six and the remaining four come from the second bin. That is exactly the double promise a hand-built list gets wrong. When the bins run out before the line is filled, the remainder is recorded as a shortage rather than allocated, and the assertion at the end proves that no bin has been asked for more than it holds. Orders are allocated in the order they appear in the file; sorting the order lines first by promised date or customer priority changes who gets the stock when there is not enough for everyone.

Allocating one order line The bins holding the SKU are taken oldest first. From each bin the line takes as much as it still needs or the bin still has, and the bin's remaining quantity is reduced. If the bins run out first, the rest of the line is recorded as a shortage. Order line SKU, quantity Bins for the SKU oldest first Take min(need, left) update what is left Line filled next line Bins empty first record a shortage

Step 2 — Batch the picks and sort them into a walking path

Python
loc = alloc["Bin"].str.extract(r"(?P<Zone>[A-Z])-(?P<Aisle>\d+)-(?P<Position>\d+)").astype({"Aisle": int, "Position": int})
alloc = pd.concat([alloc, loc], axis=1)

picks = alloc.groupby(["Bin", "Zone", "Aisle", "Position", "SKU"], as_index=False)["Qty"].sum()
aisle_rank = picks[["Zone", "Aisle"]].drop_duplicates().sort_values(["Zone", "Aisle"]).reset_index(drop=True)
aisle_rank["Leg"] = aisle_rank.index
picks = picks.merge(aisle_rank, on=["Zone", "Aisle"])
picks["Walk"] = picks["Position"].where(picks["Leg"] % 2 == 0, -picks["Position"])   # snake: every other aisle backwards
picks = picks.sort_values(["Leg", "Walk"]).reset_index(drop=True)
picks.insert(0, "Stop", picks.index + 1)
print(picks[["Stop", "Bin", "SKU", "Qty"]].to_string(index=False))

Picking order by order means walking past the same bins several times. Batch picking takes every allocation from the same bin together: the picker collects the 300 BOLT-M8 for two orders from bin A-02-05 in one visit, not two, and splits them into orders at the packing bench. Grouping the allocations by bin and SKU does that. The walking order then comes from the bin code. Splitting it into zone, aisle and position allows a snake path: go up the first aisle in increasing position, down the next in decreasing position, up the third, and so on, so the picker never walks back to the start of an aisle. The trick is a sort key that is the position in even legs and the negative position in odd ones.

The batch pick list in walking order The picker starts in zone A aisle 1, then walks back down aisle 2 and up aisle 3, then continues through zones B and C, collecting the combined quantity for all orders at each bin. A B C D E 1 Stop Bin SKU Qty Direction 2 1 A-01-02 WID-A 12 aisle 1 ↑ 3 2 A-02-05 BOLT-M8 300 aisle 2 ↓ 4 3 A-03-04 PUMP-X1 2 aisle 3 ↑ 5 4 B-01-03 BOLT-M8 150 aisle 1 ↓ 6 5 B-02-01 GASKET-3 10 aisle 2 ↑ 7 6 C-01-06 WID-B 11 aisle 1 ↓ 8 7 C-03-01 WID-A 9 aisle 3 ↑ Picking by order or in a batch Picking order by order visits a bin once for each order that needs it and needs no sorting afterwards. Batch picking visits each bin once for all orders, which shortens the walk, but the picked items must be sorted into orders at the packing bench. Order by order one trip per order same bin visited repeatedly no sorting afterwards suits few, large orders Batch one trip for many orders each bin visited once sort into orders at packing suits many small orders

Batch picking pays off when there are many small orders that share items, which is typical of trade and online orders; for a handful of large orders, picking each one separately avoids the sorting step and is often just as quick.

Step 3 — The put-to-order table and the shortage list

Python
put = alloc.pivot_table(index=["SKU", "Bin"], columns="Order", values="Qty", aggfunc="sum", fill_value=0)
print(put.to_string())
print("units picked:", int(alloc["Qty"].sum()), "| units short:", int(short["Short"].sum()) if len(short) else 0)

After a batch pick, the items have to be sorted back into their orders. A small pivot — SKU and bin down the side, orders across the top — tells the packer how many of each picked item go into each order. It is also a check: each row adds up to the quantity picked from that bin. The shortage list goes to whoever manages customer orders, so they can tell customers, split shipments or chase the supplier before the van leaves. Here two order lines cannot be filled completely: SO-505 gets one of its two pumps because only two were in stock and SO-503 took the first, and SO-504 is three gaskets short after SO-502's four left six of the ten. Of 498 units ordered, 494 are picked.

Step 4 — One printable sheet per picker

Python
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font

pickers = ["Picker 1", "Picker 2"]
picks["Picker"] = [pickers[0] if leg < 3 else pickers[1] for leg in picks["Leg"]]     # zone A to picker 1, the rest to picker 2
path = "pick_lists_2026-10-06.xlsx"
with pd.ExcelWriter(path) as xw:
    for name, g in picks.groupby("Picker"):
        g.assign(Done="☐")[["Stop", "Bin", "SKU", "Qty", "Done"]].to_excel(xw, sheet_name=name, index=False, startrow=2)
    put.reset_index().to_excel(xw, sheet_name="Put to order", index=False)
    (short if len(short) else pd.DataFrame(columns=["Order", "SKU", "Ordered", "Short"])).to_excel(xw, sheet_name="Shortages", index=False)

wb = load_workbook(path)
for name in pickers:
    ws = wb[name]
    ws["A1"] = f"{name} — pick list for Tue 6 Oct 2026"
    ws["A1"].font = Font(size=14, bold=True)
    for row in ws.iter_rows(min_row=4):
        row[4].alignment = Alignment(horizontal="center")
        for cell in row:
            cell.font = Font(size=13)
    ws.column_dimensions["B"].width = ws.column_dimensions["C"].width = 14
    ws.page_setup.fitToWidth = 1
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.print_title_rows = "3:3"
wb.save(path)
print(wb.sheetnames)

Each picker gets a sheet with their stops in walking order, the bin, the SKU, the quantity and an empty box to tick, in a large font because the list is read on a trolley, not at a desk. Splitting by zone is the simplest way to divide the work between two pickers without them crossing paths; splitting by number of stops or by weight works the same way with a different rule. The heading row repeats on every printed page, and fit-to-width keeps each list on the paper. The put-to-order and shortage sheets go to the packing bench and the customer service team. For printing sheets from a scheduled job, see Print an Excel Workbook from Python with win32com.

Common pitfalls

  • Allocating against the starting stock. Track what is left in each bin as lines are allocated.
  • Over-allocating silently. Record shortages, and assert no bin is overdrawn.
  • Picking order by order. Combine identical bin and SKU picks into one stop.
  • Sorting bins alphabetically. Use a snake path by aisle so pickers never double back.
  • Losing the link to orders. Keep a put-to-order table for sorting the batch at packing.

Conclusion

A pick list is an allocation, a grouping and a sort. Allocate each order line to its SKU's bins oldest first while tracking what is left in each bin, and record what cannot be filled as a shortage. Combine allocations from the same bin into one stop, order the stops in a snake path through zones and aisles, give each picker a printable sheet with tick boxes, and hand the packers a put-to-order table to split the batch back into orders.

Frequently asked questions

How do I allocate stock to orders first in, first out in pandas? Sort the bins for each SKU by received date and take from each in turn, reducing a running record of what is left in every bin.

How do I sort a pick list to minimise walking? Split bin codes into zone, aisle and position, and sort by aisle with the position ascending in one aisle and descending in the next.

What is batch picking? Collecting the combined quantity of an item for several orders in one visit to its bin, then sorting it into orders afterwards.

How do I record stock shortages when allocating orders? When the bins for a SKU are exhausted before the line is filled, store the remaining quantity in a shortage list instead of allocating it.

More on report layouts & templates

More on report layouts & templates →