value_counts, cut and crosstab - turning raw values into the categories you actually want to count.
Overview
value_counts
Counting is the first step in understanding any categorical column. Before you compute a mean or build a model, you need to know how many distinct values exist, how they are distributed, and whether any of them are misspelled or unexpectedly rare.
value_counts answers all of those questions in one call. It returns a Series indexed by the distinct values, sorted by frequency, highest first. A quick glance tells you the dominant category, the tail of rare values, and whether anything looks like a data-entry error.
Three parameters change what value_counts tells you. normalize gives proportions instead of raw counts, which is what you need when comparing datasets of different sizes. dropna controls whether missing values appear in the output. By default they are hidden, which is exactly the wrong behaviour when you are looking for data quality problems. sort set to False preserves the natural order of the values, which matters for ordinal categories.
Beyond single-column counts, value_counts on a DataFrame counts distinct combinations of all columns. That is a quick way to find duplicated records or to see which pairs of categories appear together.
Binning extends counting to continuous variables. You cannot count distinct values of a float column because every value is unique. cut divides the range into equal-width bins. qcut divides it into equal-count bins. Both return a categorical Series that you can then pass to value_counts or groupby.
crosstab shows the joint distribution of two categorical columns as a matrix. It is equivalent to a pivot of value_counts, but the syntax is more direct. Adding margins gives row and column totals. Adding normalize gives proportions along rows, columns, or the whole table.
This module covers value_counts with its parameters, cut and qcut for binning, and crosstab for joint distributions. These are the tools you reach for in the first five minutes of any exploratory analysis.
The most-used single method in exploratory work. The first thing to run on any cat column.
By default it counts each distinct value and sorts by rate, descending. Three args change what it tells you:
norm=True gives proportions rather than counts. This Is what you want when checking two datasets of other sizes.
dropna=False includes missing values in the count. Without it, missing data is invisible here. And "invisible" is just the wrong property for the thing you most need to notice.
sort=False keeps the values in their natural order rather than by rate. .sort_index() later does the same job more by name.
On a DataFrame, df.value_counts() counts distinct mixes of all columns. This Is a quick way to find duplicated records.
Worth knowing
value_counts gives counts; normalize=True gives shares and dropna=False reveals the missing values.
pd.cut bands by edges you choose, and bins are (left, right] — right-inclusive.
Values outside the bins become NaN silently — use -np.inf / np.inf edges when the range is unknown.
pd.qcut splits by quantile, giving equal-sized groups rather than equal-width bands.
pd.crosstab counts one variable against another, with margins for totals and normalize for percentages.
groupby(...)[col].value_counts().unstack() is the general form of what crosstab does in one call.
Counting and Binning
value_counts, cut and crosstab.
value_counts is the first thing to run
Counts, shares, and the missing values you would otherwise not see.
example_01.pypandas
import pandas as pd
import numpy as np
s = pd.Series(["a", "b", "a", "c", "a", None])
print(s.value_counts())
print()
print("with missing values included:")
print(s.value_counts(dropna=False))
print()
print("as shares:")
print((s.value_counts(normalize=True) * 100).round(1))
print()
print("sorted by label instead of by count:")
print(s.value_counts().sort_index())
Output
cut makes bands from numbers
You choose the edges, and every value lands in exactly one band.
example_02.pypandas
import pandas as pd
ages = pd.Series([5, 17, 18, 35, 64, 65, 90])
bands = pd.cut(ages, bins=[0, 17, 64, 200],
labels=["child", "adult", "senior"])
print(pd.DataFrame({"age": ages, "band": bands}))
print()
print("bins are (left, right] - right-inclusive by default,")
print("so 17 is a child and 18 is an adult.")
print()
print("counts per band:")
print(bands.value_counts().sort_index())
print()
print("the result is a category, with an order:")
print(" ", list(bands.cat.categories))
Output
Values outside the bins become NaN
Silently, which is how rows disappear from a summary.
example_03.pypandas
import pandas as pd
s = pd.Series([-5, 10, 50, 500])
b = pd.cut(s, bins=[0, 100])
print(pd.DataFrame({"value": s, "bin": b.astype(str)}))
print()
print("counted:", int(b.notna().sum()), "of", len(s))
print(" -5 and 500 fall outside 0-100 and became NaN")
print()
print("use open-ended edges when the range is not known:")
import numpy as np
safe = pd.cut(s, bins=[-np.inf, 100, np.inf], labels=["low", "high"])
print(" ", list(safe))
Output
qcut splits by quantile instead
Equal-sized groups rather than equal-width bands.
example_04.pypandas
import pandas as pd
import numpy as np
s = pd.Series([1, 2, 3, 4, 5, 6, 100, 200])
print("cut - equal WIDTH, so the outliers dominate:")
print(pd.cut(s, 4).value_counts().sort_index())
print()
print("qcut - equal COUNT per bucket:")
print(pd.qcut(s, 4).value_counts().sort_index())
print()
print("with names:")
print(list(pd.qcut(s, 4, labels=["q1", "q2", "q3", "q4"])))
print()
print("qcut raises when there are too many repeats to split evenly -")
print("duplicates='drop' merges the edges instead.")
Output
crosstab counts two things at once
A frequency table of one variable against another.
Bins are right-inclusive by default. The interval is (left, right]. So with an edge at 17, the value 17 is a child and 18 is an adult. right=False flips this to [left, right). This Is what you often want for things like age where the rule is "18 and over".
Getting this backwards makes an off-by-one at each boundary, and it doesn't raise.
The result is a cat with an order. So It sorts right and groups fast.
Values outside the bins vanish
Anything below the first edge or above the last turns NaN.
This is the failure mode to watch. A minus value, an outlier, a sentinel like -1 or 9999. All silently disappear from each count that follows. The totals no longer match the row count.
Two defences. Use -np.inf and np.inf as the outer edges when the range isn't known in advance. And check result.notna().sum() against len(df) after binning. This Takes one line and catches it at once.
Passing an int instead of a list. pd.cut(s, 4). Makes four equal-width bins spanning the data. Nothing falls outside. That is safe but rarely gives real boundaries.
qcut
pd.cut makes bands of equal width. pd.qcut makes bands of equal count.
The gap matters when the data is skewed. With a few large outliers, equal-width bins put almost each row in the first bucket and leave the rest nearly empty. Equal-count bins give you quartiles, deciles, or what split you asked for, each with the same number of rows.
Use cut when the boundaries have external meaning. Age brackets, price tiers, pass marks. Use qcut when you want scaled spot. Top quartile, bottom decile.
qcut raises when repeated values make an even split hopeless. copies="drop" merges the offending edges and returns fewer bins than requested. This Is often acceptable and worth knowing about before it fails on a column of mostly-zeros.
crosstab
pd.crosstab(df["city"], df["plan"]) gives a rate table: cities down the side, plans across the top, counts in the cells.
margins=True adds row and column totals.
norm="index" gives row percentages, "columns" gives column percentages. True gives the share of the grand total. Row percentages are often the notable ones. "what proportion of Delhi users are on the paid plan". And aren't the default.
values= with aggfunc= turns it from a count into any aggregation, at which point it's pivot_table with other spelling.
The general form
crosstab is a shortcut. The general tool is group-by:
That gives the same table. The long form. Before unstack. Is often more handy for further handling. The wide form is better for reading.
Knowing both matters because crosstab runs out of road quickly. More than two vars, custom aggregations, or anything feeding another math is easier with group-by. Reach for crosstab when you want a table to look at. Group-by when the result has one spot else to go.
Counting combinations
df.value_counts() on a whole frame counts distinct row mixes. This Is a fast way to find duplicated records or to see which pairs of groups really occur.
df[["city", "plan"]].value_counts() restricts it to two columns and gives a Series with a MultiIndex. The long form of a crosstab.
norm=True works here too, giving the share of each mix.
df.groupby(["city", "plan"]).size() gives the same numbers with a other name. Is worth knowing because it composes with other group-by steps.
Binning with meaningful edges
The edges are often the notable choice, and there are three sources for them.
External meanings — age brackets, tax bands, grade boundaries. These come from the domain. cut with an clear list is right.
The data's spread — quartiles, deciles. qcut with a count.
Round numbers — np.arange(0, 101, 10) for ten-point bands. Readable. Often better for exchange than quantiles even when quantiles are statistically neater.
cut also accepts an int, giving equal-width bins across the seen range. The boundaries are then any decimals. This Is fine for a quick look and poor for anything anyone reads.
retbins=True returns the edges alongside the result. This Is how you apply the *same* bins to another dataset later. key when checking two periods. Bins found apart aren't close.
Ordered categories from binning
cut and qcut return an sorted cat. That has three handy results.
Sorting works in bin order rather than alphabetically. This Is why value_counts().sort_index() makes a sensible table.
compare works: df[df["band"] > "low"] is real.
Group-by on the result includes each bin, including empty ones. Which is often wanted in a report and at times surprising. seen=True restricts it to occupied bins.
Histograms
np.histogram and plt.hist both bin and count in one step. pd.cut plus value_counts gives the same numbers with labels you control.
The bin count matters more than people expect. Too few hides structure, too many turns the spread into noise. bins="auto" in NumPy applies a rule of thumb. Looking at two or three bin counts is often more helpful than trusting one.
For checking spreads between groups, counts are misleading when the groups are other sizes. norm=True, or crosstab(..., norm="index"), puts them on a close footing.
The checks worth making
After binning, three lines:
binned.isna().sum() # how many fell outside the bins
binned.value_counts().sort_index() # is any bin empty or dominant
len(binned) == len(df) # nothing lost
The first is the one that matters. Values outside the edges become NaN silently. Each count after that is found on a subset without saying so.
And when the bins will be reused. On next month's data, or on a test set. Save the edges rather than recomputing them. Bins derived from other data aren't close. Quantile bins in given will differ each time.
Reusing bins across datasets
Bins found from one dataset don't apply to another. Checking two sets of quantile bins found apart is meaningless — the boundaries differ.
The test data now uses the training boundaries. This Is what makes the two close. Values outside the training range fall outside the bins and become NaN. This Is honest. They're outside what the bins show.
The same applies to any before-and-after compare. To production scoring against a model trained earlier. Saving the edges alongside the model is part of saving the model.
Counting with weights
value_counts counts rows. When each row shows a few things. A quantity column, a sampling weight. Counting rows is the wrong number.
df.groupby("city")["qty"].sum() is the weighted version.
np.bincount-style weighting has no direct value_counts equal, and group-by is the general answer.
For crosstabs, pd.crosstab(a, b, values=c, aggfunc="sum") weights the cells by another column.
Cardinality as a diagnostic
nunique() against len(df) classifies a column in one number:
1 — constant. Carries no data.
2 — binary.
Low, scaled to the rows — a group. Convert it.
Close to the row count — an identifier. Not a feature.
Moderate and surprise — often a sign of unnormalised text. Here The same value appears in a few spellings.
Running df.nunique().sort_values() across a new frame takes a second and tells you what kind of column each one is. This Is the first thing you need to know.
A summary
value_counts first, with dropna=False and at times norm=True.
cut for real boundaries, qcut for equal-sized groups.
Bins are right-inclusive by default. right=False flips it.
Values outside the edges become NaN silently. Use infinite outer edges, and check the count later.
Save the edges when the bins will be reused.
crosstab for a table to look at. groupby plus unstack when the result goes one spot else.
And weight the counts when rows aren't the unit you really mean to count.
A closing note on Counting and Binning
Counting is the least glamorous step in pandas and the one that catches the most problems.
value_counts on each cat column, run before any analysis, finds the typos, the stray capitals, the trailing spaces and the groups no one mentioned. Each of those would else become a silently split group in an aggregation.
Binning turns smooth data into the groups a question is really about. Age brackets, price tiers, quartiles. The two things to hold onto are that bins are right-inclusive by default. That values outside the edges vanish into NaN without a word.
Both are worth checking with one line each. The boundary rule against a value that sits on a boundary. The count of non-missing results against the row count. Together they take a few seconds and prevent an entire class of quietly wrong summaries.
Two more things worth knowing
pd.cut accepts labels=False, which returns the bin number rather than a label. That is what you want when the bins feed a model or a further math rather than a report. It avoids the cat dtype fully.
value_counts has a bins= arg that bins and counts in one step for numeric data: s.value_counts(bins=5) is pd.cut followed by counting. This Is convenient for a quick look at a spread.
And pd.crosstab accepts lists for either axis, giving nested rows or columns. dropna=False keeps mixes that never occurred. The second is worth knowing when two crosstabs must have the same shape to be checked. Without it, a group absent from one dataset simply doesn't appear. The tables no longer line up.
Counting for comparison
Counts answer "how many". Checking two groups needs proportions. Raw counts confound size with rate.
A city with twice the group has twice the customers on any plan. Checking the counts says nothing. norm="index" on a crosstab, or value_counts(norm=True) within a group-by, puts them on the same footing:
That reads as "what fraction of each city's customers are on each plan". This Is often the question people mean.
The reverse scaling — norm="columns" — answers a other question: "what fraction of paid customers are in each city". Both are legitimate and they aren't the same. Stating which one a table shows is worth a line of text next to it. The numbers alone don't say.
Questions people ask about Counting and Binning
How do I count how often each value appears? Use df['col'].value_counts(). Pass normalize=True for proportions and dropna=False to include missing values.
What is the difference between cut and qcut? cut makes bins of equal width or at edges you choose. qcut makes bins with roughly equal numbers of rows, using quantiles.
Why do some values become NaN after cut? Values outside the bin edges fall in no bin. Widen the edges or use include_lowest=True for the first boundary.
How do I count combinations of two columns? Use pd.crosstab(df['a'], df['b']) for a table, or df.groupby(['a', 'b']).size() for a long format.
How do I label my bins? Pass labels=['low', 'mid', 'high'] to cut or qcut. The number of labels must be one fewer than the number of edges.
Does value_counts sort the result? Yes, in descending order by count. Use sort_index() afterwards to order by the values themselves.
Counting and Binning, in summary
value_counts on each cat column is the cheapest diagnostic in pandas. It finds the variants that would else split a group-by silently.
cut bands by boundaries you choose and qcut by quantiles. The choice depends on whether the boundaries have external meaning or you want equal-sized groups.
Two things to check each time: which rule the bin edges use. They're right-inclusive by default. How many values fell outside the edges, since those become NaN without a word and each count later is found on a subset.
Check yourself
0 of 4
Answer without scrolling back up.
In `pd.cut(ages, bins=[0, 17, 64, 200])`, which band does 17 fall into?
Pass right=False for [left, right), which is what age conventions usually want. Getting it backwards is an off-by-one at every boundary, and it does not raise.
What happens to a value below the first bin edge?
Outliers and sentinels disappear from every later count. Use -np.inf/np.inf edges, and check notna().sum() against len(df).
When should you use `qcut` rather than `cut`?
With a few large outliers, equal-width bins put nearly every row in the first bucket. Use cut when boundaries have external meaning.
Why pass `dropna=False` to `value_counts`?
Together with normalize=True for shares, these are the two arguments worth reaching for by default.
Cheat sheet
Counting and Binning
Count category frequencies with value_counts, bin continuous data with cut and qcut, and build cross-tabulations with crosstab in pandas.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.