Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Bin Values into Ranges Like Excel FREQUENCY with pd.cut

Group order values, ages or delivery times into bands such as "0-10", "10-50" and "over 100". Use pd.cut with explicit edges and labels, decide which side of each band is closed, stop values outside the edges from silently becoming NaN, match Excel's FREQUENCY counts exactly, and use qcut for equal-sized groups.
Pivots & summariesExcel functions in pandaspandas

Reports turn continuous numbers into bands all the time: order values into "small, medium, large", ages into brackets, delivery times into "same day, 1-3 days, later". Excel users do it with FREQUENCY, a nested IF, or a lookup against a band table; in pandas it is pd.cut. The function is simple, but three details decide whether its answer is right: which side of each band includes its edge, what happens to values outside the outermost edges — they silently become NaN — and whether the bands keep their order in summaries. This page, part of Creating Pivot Tables from Excel Data with Pandas, bins a column of order values, matches Excel's FREQUENCY exactly and covers quantile bins.

Twelve order values counted into four bands Bars counting order values in four bands with edges at 10, 50 and 100: three values up to 10, five from over 10 to 50, three from over 50 to 100, and one above 100. These are the same counts Excel's FREQUENCY function returns for the same bins. Edges 10, 50, 100 — same counts as FREQUENCY up to 10 3 10 – 50 5 50 – 100 3 over 100 1

Prerequisites

Bash
pip install pandas openpyxl

orders.xlsx holds twelve order values chosen to sit on and around the band edges: 0, 5, 10, 10.5, 20, 25, 49, 50, 51, 99, 100 and 150.

Step 1 — Bin with explicit edges and labels

Give pd.cut the band edges and, ideally, labels a reader will understand. len(labels) must be one less than len(bins):

Python
import numpy as np
import pandas as pd

orders = pd.read_excel("orders.xlsx")
edges = [0, 10, 50, 100]
orders["band"] = pd.cut(orders["value"], bins=edges, labels=["0-10", "10-50", "50-100"])
print(orders.to_string(index=False))

Look at the first and last rows. The value 0 and the value 150 have no band — both are NaN. 150 is above the last edge, so no band contains it; 0 sits exactly on the first edge, and with right-closed intervals the first band is (0, 10], which excludes 0. Neither case raises a warning, which is why checking for NaN after binning is essential.

Step 2 — Decide which side of each band is closed

By default, intervals are closed on the right: (10, 50] includes 50 and excludes 10. Excel's FREQUENCY and most "up to" bands use this convention. Bands described as "from … to under …" are closed on the left, which right=False gives:

Python
for right in (True, False):
    bands = pd.cut(orders["value"], bins=[0, 10, 50, 100, np.inf], right=right)
    print("right=%s" % right, bands.value_counts(sort=False, dropna=False).to_dict())

With right=True, 10 falls in the first band and 50 in the second; with right=False, 10 moves up to the second band and 50 to the third. Neither is wrong, but the labels must match: "10-50" with right-closed intervals means "over 10, up to and including 50". Write the convention into the labels — "10 < x ≤ 50" — when readers might care.

Where edge values land under each convention A table of values that sit on band edges. With right-closed intervals, 0 is outside unless include_lowest is set, 10 goes in the first band, 50 in the second, 100 in the third and 150 outside unless the last edge is infinity. With left-closed intervals, 0 goes in the first band, 10 in the second, 50 in the third and 100 in the last, open-ended band. right=True (default) right=False 0 (lowest edge) NaN unless include_lowest first band 10 (0, 10] [10, 50) 50 (10, 50] [50, 100) 100 (50, 100] [100, inf) 150 (above last edge) NaN unless last edge is inf [100, inf)

Step 3 — Stop values falling outside the bins

Two tools cover the gaps. include_lowest=True closes the first band on the left too, so a value exactly on the lowest edge is included. Open-ended outer edges — -np.inf and np.inf — catch everything below the first edge and above the last:

Python
edges = [-np.inf, 10, 50, 100, np.inf]
labels = ["up to 10", "10-50", "50-100", "over 100"]
orders["band"] = pd.cut(orders["value"], bins=edges, labels=labels)
print(orders["band"].isna().sum(), "values without a band")
print(orders["band"].value_counts(sort=False).to_dict())

Every value now has a band. The open-ended form is the safer default for reports, because next month's data will contain a value nobody expected; an "over 100" band shows it, where a fixed last edge would drop it. Keep the check on isna() anyway — missing input values stay NaN whatever the edges.

Step 4 — Match Excel's FREQUENCY exactly

FREQUENCY(data, bins) returns one count per bin boundary — values up to and including each boundary, after the previous one — plus a final count for values above the last boundary. That is precisely right-closed intervals with open outer edges:

Python
bins_array = [10, 50, 100]                       # the bins_array you would give FREQUENCY
counts = pd.cut(orders["value"], bins=[-np.inf, *bins_array, np.inf]).value_counts(sort=False)
print(counts.tolist())
# [3, 5, 3, 1]

Entering =FREQUENCY(A2:A13, {10;50;100}) over the same twelve values and recalculating the workbook in LibreOffice gives the same four counts. value_counts(sort=False) keeps the bands in their natural order; the default sorts by count, which scrambles a histogram.

FREQUENCY and pd.cut side by side Excel's FREQUENCY takes a data range and a bins array of 10, 50 and 100 and returns four counts. In pandas the same bins become edges from minus infinity through 10, 50 and 100 to infinity; pd.cut with right-closed intervals followed by value_counts with sort=False returns the same four counts: 3, 5, 3 and 1. Bins 10, 50, 100 FREQUENCY's bins_array Edges -inf … inf add the open ends pd.cut, right=True values ≤ each edge Count in order value_counts, sort=False: 3, 5, 3, 1

Keep the band definitions in a sheet

When the business changes its bands — "medium" now starts at 25 — the edges should not be buried in code. Keep them in a small table, one row per band with its upper limit, and build the edges and labels from it:

Python
bands = pd.DataFrame({"label": ["up to 10", "10-50", "50-100", "over 100"],
                      "upper": [10, 50, 100, None]})
bands.to_excel("bands.xlsx", index=False)

table = pd.read_excel("bands.xlsx")
edges = [-np.inf, *table["upper"].dropna().tolist(), np.inf]
if not pd.Series(edges).is_monotonic_increasing:
    raise ValueError("band limits must increase from row to row")
orders["band"] = pd.cut(orders["value"], bins=edges, labels=table["label"].tolist())
print(orders["band"].value_counts(sort=False).to_dict())

The last band's empty upper limit means "no limit", which maps naturally onto inf. Checking that the limits increase catches the most likely editing mistake — a row typed out of order — before it produces a confusing error from pd.cut.

Step 5 — Use qcut for equal-sized groups

Sometimes the question is not "how many orders are under 50?" but "which quarter of orders is each one in?". pd.qcut picks the edges from the data so each band holds about the same number of values:

Python
orders["quartile"] = pd.qcut(orders["value"], 4, labels=["Q1", "Q2", "Q3", "Q4"])
print(orders["quartile"].value_counts(sort=False).to_dict())

skewed = pd.Series([1, 1, 1, 1, 1, 2, 3])
try:
    pd.qcut(skewed, 4)
except ValueError as exc:
    print("qcut:", str(exc).splitlines()[0])
print(pd.qcut(skewed, 4, duplicates="drop").value_counts(sort=False).to_dict())

Twelve values give three per quartile. On data with many repeated values, several quantiles coincide and qcut raises "Bin edges must be unique"; duplicates="drop" merges the repeated edges and returns fewer, unequal bands. Remember that quantile edges move with the data — "Q4" this month and "Q4" last month are different ranges — so use cut with fixed edges for anything compared over time.

Step 6 — Summarise bands in order

A binned column is a pandas ordered categorical, so pivots and groupbys keep the bands in their defined order and include empty bands when asked. That is exactly what a banded report needs:

Python
summary = (orders.groupby("band", observed=False)
                 .agg(orders=("order", "count"), total=("value", "sum")))
summary["share"] = (summary["orders"] / summary["orders"].sum()).round(3)
print(summary)
summary.to_excel("value_bands.xlsx")

observed=False keeps bands with no orders in the table as zero rows — a band that is empty this month is information, not noise. Written to Excel, the bands appear in order from "up to 10" to "over 100", ready for a column chart. Create a Histogram in Excel with Python builds the chart from exactly this kind of table.

Common pitfalls

  • Values on the lowest edge becoming NaN. Use include_lowest=True or -inf as the first edge.
  • Values above the last edge becoming NaN. End the edges with inf and an "over …" label.
  • value_counts() without sort=False. Bands come out ordered by count.
  • Labels that contradict the interval side. "10-50" is ambiguous; say which edge is included.
  • qcut for comparisons over time. Its edges change with the data.

Performance notes

pd.cut is vectorised and bins millions of values in a fraction of a second; the categorical it returns is also compact in memory, one small integer per value. qcut first computes quantiles, which requires a sort, but is still fast for any report-sized column.

Conclusion

Bin with pd.cut(values, bins=edges, labels=...), choose right- or left-closed intervals deliberately, and use -inf/inf outer edges (or include_lowest=True) so no value silently becomes NaN. Right-closed bins with open outer edges and value_counts(sort=False) reproduce Excel's FREQUENCY exactly; qcut gives equal-count groups when ranking matters more than fixed ranges; and grouping on the resulting ordered categorical keeps bands, including empty ones, in order.

Frequently asked questions

Which values fall into which bin with pd.cut? By default intervals are closed on the right: with edges 10 and 50, a value of 10 falls in the band ending at 10 and 50 falls in the band ending at 50. Pass right=False to close them on the left instead, so each band includes its lower edge.

Why did some values become NaN? They lie outside the outermost edges. pd.cut does not extend the first or last band, so a value below the lowest edge or above the highest is simply not assigned. Use -inf and inf as the outer edges, or include_lowest=True for a value exactly on the lowest edge.

How do I reproduce Excel's FREQUENCY function? FREQUENCY counts values up to and including each bin boundary, plus one extra count for values above the last. pd.cut with edges -inf, b1, b2, …, inf and the default right-closed intervals, followed by value_counts(sort=False), gives the same numbers.

What is the difference between cut and qcut? cut uses edges you choose, so bands have fixed widths and uneven counts. qcut chooses edges from the data so each band holds roughly the same number of values — quartiles, deciles — which is useful for ranking but gives band edges that change with the data.

More on pivots & summaries

More on pivots & summaries →