Grouping and Aggregation with pandas

A group contains rows with the same key value. If the key is region, every North order belongs to one group even when those orders are scattered through the table. Split-apply-combine means identifying these groups, doing the same operation inside each one, and assembling the results.

Start with the order data

The examples use pandas 3 with import pandas as pd and NumPy as np. Create the drink-order table:

sales = pd.DataFrame({
    "region": ["North", "South", "North", "East", "South", "East", "North", "South"],
    "product": ["tea", "tea", "coffee", "coffee", "coffee", "tea", "tea", "coffee"],
    "units": [10, 4, 7, 5, 9, 3, 6, 8],
    "revenue": [30, 12, 28, 20, 36, 9, 18, 32]
})

Revenue has no currency unit here. Tea has revenue 3 per unit and coffee 4 per unit. The default row labels are 0 through 7.

From a GroupBy object to an answer

sales.groupby("region") returns a GroupBy object, which holds the grouping information. It is not a summary table. Choose a column and call a method to calculate a result:

g = sales.groupby("region")
totals = g["units"].sum()

East contains rows 3 and 5, so its total is 5 + 3 = 8. North contains rows 0, 2, and 6, giving 10 + 7 + 6 = 23. South contains rows 1, 4, and 7, giving 4 + 9 + 8 = 21. pandas combines these into a Series named units, with the region names as its index. The totals add to 52, the original column total.

The default output order is East, North, South because group keys are sorted. sales.groupby("region", sort=False) uses first-appearance order: North, South, East. Neither choice sorts the observations inside a group or changes sales.

Inspect and select

g.ngroups is 3. g.size() reports 2, 3, and 3 rows for East, North, and South. g.groups maps keys to row labels, and g.get_group("North") returns the three North rows. You can inspect one group at a time:

for name, group in g:
    print(name, group["units"].tolist())

Iteration helps when inspecting or exporting groups. For a statistic, a built-in grouped method avoids writing a Python loop and collecting the results yourself.

Selecting g["units"] produces a SeriesGroupBy; its sum is a Series. Selecting g[["units"]] produces a DataFrameGroupBy; its sum is a one-column DataFrame. Select g[["units", "revenue"]] when both numeric columns are needed.

Choose the output shape

An aggregation such as sum gives one summary value per group and column. A transformation gives a value for every input row, such as each order’s share of its region’s total. A filtration keeps selected rows or groups. The general apply method handles less regular group results.

Filtering sales[sales["region"] == "North"] retrieves one region’s rows. Grouping lets you process every region with the same operation.

Keys can come from columns, calculations, or the row index. The important question is what value each row receives as its key. Rows with the same combination of keys are processed together.

Use several columns

Pass a list of column names to group by combinations:

pair_totals = sales.groupby(["region", "product"])["units"].sum()

The result has a two-level MultiIndex. Its entries are East–coffee 5, East–tea 3, North–coffee 7, North–tea 16, South–coffee 17, and South–tea 4. North–tea combines the two tea orders, with 10 and 6 units. Grouping by region alone would also include North’s coffee order.

pair_totals.unstack() moves the inner product level into columns. The rows are regions, and the columns are coffee and tea. An absent combination becomes NaN; unstack(fill_value=0) can represent it as zero when that absence means no units.

Use a list for multiple column names. groupby(("region", "product")) treats the tuple as a single column label and looks for a column with that tuple as its name.

Derive a key from values

A Boolean Series can divide orders by size without adding a column:

sales.groupby(sales["units"] >= 7)["revenue"].sum()

The True group contains units 10, 7, 9, and 8; its revenue is 126. The False group contains the other four orders and has revenue 59. The test creates group labels; it does not remove the smaller orders.

A mapping can combine regions into zones:

zones = {"North": "Zone 1", "East": "Zone 1", "South": "Zone 2"}
zone_key = sales["region"].map(zones)
sales.groupby(zone_key)["units"].sum()

Zone 1 receives North’s 23 and East’s 8 units, totaling 31. Zone 2 receives South’s 21. Map the column first: a dictionary passed directly to groupby maps index labels, not region values.

Group using the index

A function passed as a key also receives index labels. For stock indexed by product codes, the first letter can identify the product family:

stock = pd.Series([12, 8, 5, 9, 4],
                  index=["T01", "T02", "C01", "C02", "C03"])
stock.groupby(lambda code: code[0]).sum()

The C total is 18 and the T total is 20. For an existing MultiIndex, pair_totals.groupby(level="product").sum() regroups by the named index level, producing coffee 29 and tea 23.

Check alignment before grouping

A NumPy array key supplies labels by position and must have one element per row. A Series key is aligned by index label. If its labels cover only rows 0 and 1, the other rows receive missing keys and are dropped by the default grouping behavior. A reordered Series can still group correctly when its labels match; replacing it with its values changes the operation to positional matching.

A grouped calculation has two parts to inspect: the values and the labels around them. pandas lets you choose where keys appear, how groups are ordered, and whether missing or unused keys appear in the report.

Keep keys in the index or in columns

By default, keys become the result’s index:

sales.groupby("region")["revenue"].sum()

This Series has East 29, North 76, and South 80. For a flat report, use:

report = sales.groupby("region", as_index=False)["revenue"].sum()

report has ordinary region and revenue columns and a new integer index. Calling .reset_index() on the original Series produces the same layout. With several keys, it converts each MultiIndex level into a column. unstack() has a different purpose: it moves an index level across into columns of a grid.

A Series result keeps the selected column’s name. Use .rename("total_revenue") for a clearer Series name or .to_frame("total_revenue") for a one-column DataFrame. Named aggregation can supply descriptive column names during the calculation.

Sort keys or rank values

sort=True sorts the keys, so alphabetical region order remains East, North, South even though South has the largest revenue. To rank totals, sort the result:

sales.groupby("region")["revenue"].sum().sort_values(ascending=False)

The output is South 80, North 76, East 29. Use sort=False to keep first-appearance key order and avoid unnecessary key sorting on a large dataset. It does not sort rows within groups.

Account for missing keys

The default dropna=True excludes rows with a missing group key. Add an order with 5 units but no region: the full units column now totals 57, while the grouped totals still add to 52. The omitted row has no named region.

sales.groupby("region", dropna=False)["units"].sum()

On the enlarged table, this adds a missing-key group totaling 5. Missing keys determine whether a row enters a group; missing values determine what an aggregation counts or calculates within a group. They are separate decisions.

Show unused categories when needed

A categorical region column can list East, North, South, and West even when there are no West orders. Since pandas 3.0, observed=True is the default and shows only categories present in the data. With observed=False, West appears too: its sum and count are 0, while its mean is NaN.

Choose this setting explicitly when a report must show the same categories every time. An unused category and a missing key are different: observed handles declared categorical values; dropna handles absent key values.

as_index and sort shape aggregation results. Transformations and filtrations such as head, nth, and filter retain input-row indexes. For a sum report, comparing the sum of group totals with the original column total can expose excluded keys or an earlier filter.

An aggregation reduces each group to a summary value for each selected column. Choose the measurement from the question: a row count, a total, an average, or a measure of variation describes a different feature of the data.

Select the statistic

Common methods include sum, prod, mean, median, min, max, count, size, nunique, std, var, first, last, and quantile. A direct call such as .mean() and .agg("mean") both request a pandas group mean. nunique() counts distinct non-missing values by default.

sales.groupby("product")["revenue"].agg(["mean", "median"])

Coffee revenues are 28, 20, 36, and 32. Their mean is 29; sorting them gives middle values 28 and 32, so their median is 30. Tea revenues are 30, 12, 9, and 18. Their mean is 17.25 and median 15. The large tea order of 30 pulls the mean above the median.

Count rows or recorded values

size() counts rows. count() counts non-missing values in each selected column. If units are missing for North row 2 and East row 5, sizes remain East 2, North 3, South 3, but units counts are 1, 2, and 3. Different columns can therefore have different counts inside the same group.

Most numeric reductions skip missing values. North’s remaining units 10 and 6 have mean 8, using two recorded values. Dividing their sum by all three rows would give the wrong mean. An all-missing group has sum 0 by default but mean NaN. Since pandas 3.0, reductions such as sum and mean accept skipna=False to return a missing result when a group contains a missing value.

Interpret spread and combined summaries

std() and var() use sample statistics, with ddof=1: the divisor is one less than the number of recorded observations. Values 1 and 3 have sample standard deviation about 1.414. A group with only the value 5 has sample standard deviation NaN.

describe() bundles count, mean, standard deviation, minimum, quartiles, and maximum. For South revenues 12, 36, and 32, the mean is about 26.67 and the median is 32. The 50% column is the median.

Control columns and row order

Selecting numeric columns avoids trying to average text:

sales.groupby("region")[["units", "revenue"]].mean()

A whole-table .mean() fails on the text product column; numeric_only=True is another solution. A whole-table .sum() can concatenate text, producing East’s "coffeetea", which is no useful product summary.

first() and last() choose the first and last non-missing value per column in current row order. They can take values from different rows. Use nth(0) to retrieve the actual first row, including its missing values; sort first when “first” means earliest.

Unequal group sizes also affect interpretation. The regional revenue means average to about 22.17, but the overall mean is 185 divided by 8, or 23.125. Giving every regional mean equal weight gives the two-row East group as much influence as each three-row group.

A useful report often needs several measurements from the same groups. agg, short for aggregate, lets you request those measurements together and control how their output columns are named.

Give one column several functions

sales.groupby("region")["revenue"].agg(["sum", "mean", "max"])

East gives 29, 14.5, and 20; North gives 76, about 25.33, and 30; South gives 80, about 26.67, and 36. Each row represents one region, while the function names become column names.

A list applied to several value columns produces hierarchical columns:

result = sales.groupby("region")[["units", "revenue"]].agg(["sum", "mean"])

The four column labels are the pairs ("units", "sum"), ("units", "mean"), ("revenue", "sum"), and ("revenue", "mean"). The first level identifies the input column; the second identifies the calculation. These are column levels, separate from any grouping levels in the row index.

Assign functions to specific columns

A dictionary selects the input columns and their functions:

sales.groupby("region").agg({
    "units": "sum",
    "revenue": ["mean", "max"]
})

This requests total units but average and maximum revenue. Columns absent from the dictionary do not appear. Different functions can make sense for different measures; summing a quantity and averaging a rate are not interchangeable choices.

Name the outputs directly

Named aggregation gives each result an explicit name:

report = sales.groupby("region").agg(
    total_units=("units", "sum"),
    avg_revenue=("revenue", "mean")
)

Every keyword names an output column. Its tuple contains the source column and the function. total_units is 8, 23, and 21; avg_revenue is 14.5, about 25.33, and about 26.67. pd.NamedAgg(column="units", aggfunc="sum") is an alternative helper for specifying the source and function.

After selecting a single Series, use function keywords without source-column tuples:

sales.groupby("region")["units"].agg(total="sum", most="max")

The totals are 8, 23, and 21, and maxima 5, 10, and 9. A dictionary such as {"total": "sum"} on this selected Series raises a SpecificationError; it is not the dictionary form used on a DataFrame.

A list of named pairs also works on a selected column: .agg([("average", "mean"), ("spread", "std")]). If you already have MultiIndex columns made from string labels, result.columns = ["_".join(col) for col in result.columns] flattens them, producing names such as units_sum.

Choose the function deliberately

String names use pandas’ built-in group calculations. Since pandas 3.0, a NumPy or Python function passed to agg is called as supplied. For East revenues 20 and 9, "std" uses the sample divisor and gives about 7.78, while np.std uses the population divisor and gives 5.5.

A custom aggregation expresses a summary that a built-in name does not provide. Its function receives one group’s values as a Series and should reduce them to one useful result.

Write and pass the function

def value_range(s):
    return s.max() - s.min()

sales.groupby("region")["revenue"].agg(value_range)

East’s range is 20 minus 9, or 11. North’s is 30 minus 18, or 12. South’s is 36 minus 12, or 24. Pass value_range itself; value_range() would call it immediately without the group Series it needs.

A lambda suits a short calculation:

sales.groupby("region")["revenue"].agg(
    share_20=lambda s: (s >= 20).mean()
)

Boolean values act as 1 for True and 0 for False, so their mean is the share passing the condition. East has one qualifying order out of two, or 0.5. North and South each have two out of three, or about 0.667. Naming the result makes its meaning clearer than an automatic lambda label. Lists with several functions label lambdas <lambda_0>, <lambda_1>, and so on; a lone lambda in a list is labeled <lambda>.

Prepare columns to use fast reductions

A Python function runs once for each group. When there are many groups, built-in reductions are usually faster. The share calculation can be rewritten by making its Boolean column first:

sales.assign(big=sales["revenue"] >= 20).groupby("region")["big"].mean()

The comparison runs on the full column, leaving pandas to calculate the group means.

Combine two columns through their totals

An agg function handles one column at a time. For average revenue per unit, first summarize both columns:

totals = sales.groupby("region")[["units", "revenue"]].sum()
avg_price = totals["revenue"] / totals["units"]

The results are East 29/8 = 3.625, North 76/23 ≈ 3.304, and South 80/21 ≈ 3.810. Every unit receives equal weight. Averaging the order prices instead gives East 3.5, North about 3.333, and South about 3.667, because each order receives equal weight regardless of its size. Decide whether the question concerns a typical unit or a typical order before choosing the formula.

Keep the return value and missing data clear

A function returning the two smallest values does not create two ordinary summary rows through agg; in pandas 3.1, lambda s: s.nsmallest(2) stores a list-like object in each cell. Use apply when a group needs to return several values or rows.

The input Series still contains missing values. s.mean() skips them, while len(s) counts their rows. If units are missing at rows 2 and 5, .agg(len) gives group sizes 2, 3, and 3, not non-missing counts 1, 2, and 3.

Extra arguments can follow the function: .agg("quantile", 0.9) gives each group’s 90th percentile. East revenues 9 and 20 give 18.9 by linear interpolation. The direct .quantile(0.9) call is easier to read for this single operation.

A summary has one row per group, but sometimes each original row needs its group’s statistic. transform returns a result with the original length and index, making it suitable for a new column.

Broadcast a total to the right rows

g = sales.groupby("region")["revenue"]
regional_total = g.transform("sum")
sales["share"] = sales["revenue"] / regional_total

The total Series contains 76, 80, 76, 29, 80, 29, 76, and 80 in original row order. North’s total 76 is repeated only on North rows. Row 0 receives a share of 30/76 ≈ 0.395; East row 3 receives 20/29 ≈ 0.690. Within each region, these revenue shares sum to 1.

Assigning g.sum() directly would fail to match the rows. Its index contains region names, while sales has row labels 0 through 7. pandas aligns by index label, so the assigned column becomes missing. Either use transform or explicitly merge a summary onto region.

Compare a row with its group

Subtracting the group mean gives a within-group deviation:

x = sales["revenue"]
deviation = x - g.transform("mean")

North row 0 is about 4.67 above its group mean of about 25.33. South row 1 is about 14.67 below its group mean of about 26.67. Each comparison uses the row’s own region, not the whole table’s average.

A z-score divides that deviation by the group’s standard deviation:

z = (x - g.transform("mean")) / g.transform("std")

For North row 0, the sample standard deviation is about 6.43 and the z-score about 0.73. East row 3 has a z-score about 0.71, so it is above its region’s average even though its revenue of 20 is below the overall average of 23.125. A zero standard deviation or too few recorded values leaves a standardized score undefined.

Fill a missing value from its group

Suppose units at rows 2 and 5 are missing. North’s recorded units are 10 and 6, with mean 8; East’s only recorded units value is 5.

with_missing = sales.copy()
with_missing.loc[[2, 5], "units"] = np.nan
means = with_missing.groupby("region")["units"].transform("mean")
filled_units = with_missing["units"].fillna(means)

On that modified table, row 2 receives 8 and row 5 receives 5. Existing values remain unchanged. When groups have different typical values, their own means can provide more realistic replacements than one overall mean. A group with no recorded values still has no mean to supply.

Understand a custom transform

A function given to transform can return a scalar, which pandas repeats within the group, or a Series indexed to the group’s rows. For example, g.transform(lambda s: s - s.mean()) computes the same deviations as the built-in mean followed by subtraction, but invokes Python for each group.

A shorter returned Series is matched by label. g.transform(lambda s: s.head(1)) supplies only the first row’s value in each group; the other rows receive NaN. Keep row-level calculations in the original index when the result will be assigned back to the table.

Running calculations and comparisons with previous observations need both a group and a row order. Grouping prevents a calculation from crossing into another group; sorting determines what “previous” or “so far” means.

Accumulate within each group

Assume the order rows are already in time order:

sales["running_units"] = sales.groupby("region")["units"].cumsum()

North’s units 10, 7, and 6 produce running totals 10, 17, and 23. South’s 4, 9, and 8 produce 4, 13, and 21. East’s 5 and 3 produce 5 and 8. These values remain attached to their original row labels, so the full output in row order is 10, 4, 17, 5, 13, 8, 23, and 21.

Without groupby, sales["units"].cumsum() would give one total across all orders: 10, 14, 21, 26, 35, 38, 44, and 52. The grouped calculation restarts for each region. Related methods include cumprod, cummax, and cummin for running products, maxima, and minima.

cumcount() counts positions within groups from zero. Here it gives 0, 0, 1, 0, 1, 1, 2, and 2. Add 1 for the first, second, and third order numbers within each region.

Rank values without rearranging rows

sales.groupby("region")["revenue"].rank(ascending=False)

The largest revenue receives rank 1 in its own region: North row 0, South row 4, and East row 3. The output keeps the input index; ranking does not move the largest rows to the top of the table.

By default, ties receive their average rank. If two values tie for first place, both get 1.5 and the next gets 3. method="min" gives the tied values 1 and still gives the next 3. method="dense" gives the tied values 1 and the next 2. Choose a tie rule that fits how the ranks will be interpreted.

Compare consecutive values

g = sales.groupby("region")["units"]
previous = g.shift(1)
change = g.diff()
relative_change = g.pct_change()

North’s previous values are missing, 10, and 7. Its differences are missing, −3, and −1. Its relative changes are missing, −0.3, and about −0.143: the difference is divided by the previous value. pct_change() returns a fraction, so −0.3 means a 30% decrease, not a 0.3% decrease.

The first observation of each group has no previous observation and receives NaN. A plain column shift() would use the previous row of the whole table, which could belong to another region.

Before these ordered calculations, sort the rows by the column that defines the sequence, such as a date or event number. groupby(sort=True) sorts group keys only; it cannot put observations into chronological order. Ranking compares values and handles ties independently of chronology, while cumulative and lagged calculations depend on the chosen sequence.

Keeping a group and summarizing it are different operations. A group filter uses a group-wide condition to decide which original rows remain; row-selection methods choose particular observations from each group.

Keep whole groups

sales.groupby("region").filter(lambda g: len(g) >= 3)

North and South each have three orders, while East has two. The result contains rows 0, 1, 2, 4, 6, and 7, in original order with their original labels. It does not return two regional summary rows.

The condition must produce one Boolean for each group. lambda g: g["units"] > 5 produces a Boolean Series and is invalid for filter. Reduce that condition with all() or any():

sales.groupby("region").filter(lambda g: (g["units"] > 5).all())

Only North passes, because all its units values, 10, 7, and 6, exceed 5. Using any() also keeps South because at least one of its orders exceeds 5.

A transformed group size provides a faster row-aligned mask for the first task:

sizes = sales.groupby("region")["units"].transform("size")
sales[sizes >= 3]

This mask can be combined with other row conditions using & and |. Filtering sales.groupby("region")["revenue"].sum() instead would retain summary entries, not the original orders.

Take rows by position

head(n) takes the first n rows per group, tail(n) the last n, and nth(k) the row at zero-based position k. sales.groupby("region").head(1) returns rows 0, 1, and 3: the first observed order from each region. Missing values do not make head(1) search for a later non-missing row.

To select the largest order per region, first sort by revenue:

sales.sort_values("revenue", ascending=False).groupby("region").head(1)

The selected rows are South row 4 with revenue 36, North row 0 with 30, and East row 3 with 20. Sorting controls which observation is first; the group operation then takes that first row. With unique row labels, sales.loc[sales.groupby("region")["revenue"].idxmax()] retrieves the same maxima by their labels.

Sample within every group

sales.groupby("region").sample(n=1, random_state=0)

This draws one order from each region. Setting random_state makes repeated runs on the same data repeatable. A sample from the entire DataFrame could miss a region, even if it has three rows in total.

Use frac=0.5 to draw about half the rows in each group. The per-group sample size is rounded: with frac=0.2, East’s two rows yield 0.4 rows, rounded to zero. Use an explicit n=1 when every nonempty group must contribute one observation. The requested sample size must be feasible for the groups under the chosen sampling settings.

apply lets a function examine a whole group and return a scalar, a Series, or a DataFrame. That flexibility is useful when columns must interact or the result contains several rows. It also makes the result shape less predictable than agg or transform.

Use several columns together

def price_per_unit(g):
    return g["revenue"].sum() / g["units"].sum()

sales.groupby("region").apply(price_per_unit)

The function receives one region’s rows. It returns one value, so pandas combines the outputs into a Series indexed by region: East 3.625, North about 3.304, and South about 3.810. A ratio of built-in grouped sums produces the same result with less Python overhead. np.average(g["revenue"] / g["units"], weights=g["units"]) expresses the equivalent weighted mean.

For a statistic involving two columns, such as the correlation between study hours and score within each class group, apply can pass the two Series from each group to .corr().

Predict how outputs combine

A function returning a Series with the same labels for every group creates one table row per group:

sales.groupby("region").apply(
    lambda g: pd.Series({
        "orders": len(g),
        "avg_price": g["revenue"].sum() / g["units"].sum()
    })
)

The columns are orders and avg_price. The counts appear as 2.0, 3.0, and 3.0 because the returned Series also holds a floating-point price. If the returned Series use different labels, pandas stacks them into a longer Series, adding group keys as an outer index level.

A DataFrame per group is stacked into a larger DataFrame:

def top(g, n=2, col="revenue"):
    return g.sort_values(col, ascending=False).head(n)

sales.groupby("region").apply(top)

The rows are East 3 and 5, North 0 and 2, and South 4 and 7. Each group is sorted independently. Extra arguments follow the function: .apply(top, n=1, col="units") changes the selection to the largest units order.

Control keys and alignment

For these returned rows, the default group_keys=True adds the region as an outer index level. sales.groupby("region", group_keys=False).apply(top) keeps just the original row labels. It still assembles results group by group; it does not promise the input table’s row order.

Since pandas 3.0, the DataFrame passed to a grouped apply excludes the grouping columns. In this example it has product, units, and revenue, but not region. Read the current group key from g.name when the function needs it.

Use the narrower method when it fits

apply runs Python code for each group and infers how to assemble the output. Use agg for per-column summary values, transform for results aligned to every input row, and filter for group-wide keep-or-drop decisions.

For example, subtracting each region’s mean through a selected-Series apply returns differences under a region-and-row MultiIndex. transform returns the differences with the original index and is ready for assignment as a column.

Binning, also called discretization, replaces numeric values with interval categories. Use it when a report needs meaningful ranges, such as score bands, instead of a separate group for every number.

Set explicit boundaries

scores = pd.Series([42, 50, 55, 64, 68, 70, 73, 81, 85, 88, 97, 100])
bands = pd.cut(scores, [0, 50, 70, 85, 100])
bands.value_counts(sort=False)

Five edges define four bins: (0, 50], (50, 70], (70, 85], and (85, 100]. They contain 2, 4, 3, and 3 scores. Explicit edges can have unequal widths because they describe meaningful boundaries.

A parenthesis excludes an endpoint and a square bracket includes it. With the default right=True, 50 belongs in the first bin, 70 in the second, 85 in the third, and 100 in the fourth. Read (50, 70] as “greater than 50 and at most 70.”

Handle the outer edges

With these explicit edges, 0 is excluded from the first interval, and 101 lies beyond the last interval. Both receive NaN. Use include_lowest=True to include a value equal to the lowest edge. Values below the lowest edge or above the highest still receive no bin.

For left-closed intervals, use right=False. The same edges then define [0, 50), [50, 70), [70, 85), and [85, 100). Now 50 moves into the second bin and 100 is excluded. The example counts become 1, 4, 3, and 3, with one missing bin assignment. A top edge of 101 would include the score 100 in the last left-closed bin.

Changing the closure changes membership at a boundary. Check a minimum, an internal boundary, and a maximum before using the bands in a report.

Add readable labels

bands = pd.cut(
    scores, [0, 50, 70, 85, 100],
    labels=["fail", "pass", "merit", "distinction"]
)

Provide one label for each interval: four labels for five edges. The output is an ordered categorical, so its order is fail, pass, merit, distinction, not alphabetical order. labels=False instead returns zero-based bin codes 0 through 3 for assigned values.

value_counts() normally sorts by frequency. Use sort=False or sort the resulting index to retain the interval sequence. Otherwise the most frequent band may appear first and make the distribution harder to read.

Ask pandas for equal widths

Passing an integer requests that many equal-width bins:

bands, edges = pd.cut(scores, 4, retbins=True)

The range from 42 to 100 spans 58, giving widths of 14.5. The internal edges are 56.5, 71.0, and 85.5. pandas extends the lower end slightly for these right-closed bins, giving a first edge of 41.942 so the minimum is included. Each bin happens to contain three scores here; equal widths do not generally produce equal counts.

retbins=True returns the actual edges as well as assignments. Explicit edges must be in increasing order: [0, 70, 50, 100] cannot define the intended sequence of intervals.

A quantile describes a position in sorted data. The median is the 0.5 quantile; quartiles divide the distribution into quarters, deciles into tenths, and percentiles into hundredths. qcut uses sample quantiles as bin boundaries.

Compute a quantile

For the scores 42, 50, 55, 64, 68, 70, 73, 81, 85, 88, 97, and 100:

scores.quantile([0.25, 0.5, 0.75])

The results are 61.75, 71.5, and 85.75. pandas uses linear interpolation by default. For the 0.25 quantile, the zero-based position is:

Position 2 holds 55 and position 3 holds 64. Move three quarters of the distance between them:

A sample quantile can therefore fall between observed values. It need not equal a score that someone actually received.

Create quantile groups

quartile = pd.qcut(scores, 4, labels=["Q1", "Q2", "Q3", "Q4"])

This sample gives three scores per quartile group. The internal boundaries are the three computed quartiles. The first interval includes the minimum; the interval display adjusts its lower label slightly below 42. Use labels=False for codes 0 through 3, or retbins=True to retrieve the numeric boundaries.

A list of proportions controls the quantile ranges. pd.qcut(x, [0, 0.1, 0.5, 0.9, 1]) separates the bottom 10%, two middle portions split at the median, and the top 10%.

Choose widths or counts

Delivery times of 5, 6, 7, 7, 8, 9, 10, 12, 15, 20, 35, and 60 minutes show the difference. Three equal-width bins contain 10, 1, and 1 observations. Three quantile bins contain 4 each, with internal edges about 7.67 and 13 minutes. Long delays spread out the equal-width range, leaving most observations in its first bin.

Use fixed edges when the same time ranges must be compared across datasets. Quantile boundaries change when the sample changes.

Keep ties visible

For values 2, 3, 3, 4, 5, 6, 6, 7, 8, 9, 10, and 12, quartile edges are 3.75, 6, and 8.25. Both values of 6 belong together, so bin counts are 3, 4, 2, and 3. Equal-count binning is approximate when values tie.

With many ties, quantile edges can coincide. For 1, 1, 1, 1, 1, 1, 2, and 3, requesting quartiles gives repeated edges. duplicates="drop" removes duplicate edges and leaves only two bins, with counts 6 and 2. Ranking first with rank(method="first") can force four equal-sized groups, but it splits equal values according to row order. That changes the meaning from value intervals to groups based on tie-broken ranks.

A bin label can serve as a group key. This lets you ask how one variable changes across ranges of another, such as average exam score across study-hour bands. The summary describes the observations in each band.

Build a bucket summary

Create the student data:

students = pd.DataFrame({
    "group": ["day", "evening", "day", "evening", "day", "evening", "day", "evening", "day", "day", "evening", "day"],
    "hours": [2, 3, 3, 5, 4, 6, 6, 8, 7, 9, 10, 12],
    "score": [42, 50, 55, 64, 68, 70, 73, 81, 85, 88, 97, 100]
})
hour_band = pd.cut(students["hours"], [0, 3, 6, 9, 12])
students.groupby(hour_band)["score"].agg(["count", "mean"])

The four bands contain 3, 4, 3, and 2 students. Their mean scores are 49, 68.75, about 84.67, and 98.5. In the first band, the scores are 42, 50, and 55; their mean is 147/3 = 49. The count shows how many recorded scores support each mean.

The band Series has one entry per student and the same index, so its assignments match the rows during grouping. The numeric hours column stays available for other calculations.

Keep empty bins when a report needs them

Since pandas 3.0, observed=True is the grouping default for categorical keys. Add an edge of 15 and the unused (12, 15] band will be omitted. observed=False keeps it with count 0 and mean NaN. Zero students is a count; a zero mean would wrongly suggest observed scores averaging zero.

Bins are ordered categories, so the normal grouped output follows low-to-high interval order. Named labels retain the order assigned to their intervals.

Analyze quantile groups and combinations

quartile = pd.qcut(students["hours"], 4,
                   labels=["Q1", "Q2", "Q3", "Q4"])
students.groupby(quartile)["score"].agg(["count", "min", "max", "mean"])

Counts are 3, 4, 2, and 3, with mean scores 49, 68.75, 83, and 95. The tied values of 6 explain the unequal counts. To compare day and evening students inside each quartile, group by [quartile, "group"] and call .mean().unstack(). Q1 means are day 48.5 and evening 50; Q4 means are day 94 and evening 97.

The direction of the analysis matters. Grouping study hours by score bands answers a different question: the mean hours for fail, pass, merit, and distinction bands are 2.5, 4.5, 7, and about 10.33.

Decide which population defines a quantile

An overall median split of scores uses 71.5. A split within each class uses its own median: day 73 and evening 70.

within_class = students.groupby("group")["score"].transform(
    lambda s: pd.qcut(s, 2, labels=["lower", "upper"])
)

The day student scoring 73 is above the overall median but belongs to the lower half within the day group. Both labels are correct because they answer different comparison questions.

A rising mean across hour bands is an association. Students in different bands may differ in other ways, so the table alone cannot establish that extra study time caused the score difference. Small counts also make a mean more sensitive to a single observation.

A pivot table summarizes values for combinations of row and column keys. Each cell collects the observations at one intersection and applies a function, such as a sum or mean.

Assign the roles explicitly

sales.pivot_table(
    index="region", columns="product",
    values="revenue", aggfunc="sum"
)

index supplies the row keys, columns the column keys, values the measured variable, and aggfunc the calculation. The table is:

North–tea combines revenues 30 and 18, so its sum is 48. South–coffee combines 36 and 32, giving 68. The same data can be summarized with sales.groupby(["region", "product"])["revenue"].sum().unstack().

The default aggfunc is "mean". Omitting it would give North–tea 24 and South–coffee 34. State aggfunc="sum" when the question asks for totals; a table’s layout does not tell you which statistic it contains.

Fill absent combinations carefully

Combinations without observations appear as NaN. Remove East’s tea order and that cell becomes missing. fill_value=0 can represent no orders in a sum or count report. For a mean, replacing an empty cell with 0 suggests an observed average that was never measured. Keep its missing status when no average exists.

Categorical keys follow the observed choice; since pandas 3.0, the default shows observed categories. Set it explicitly when a report must retain unused categories.

Add margins using the same function

sales.pivot_table(
    index="region", columns="product", values="revenue",
    aggfunc="sum", margins=True, margins_name="Total"
)

Row totals are East 29, North 76, and South 80. Column totals are coffee 116 and tea 69; the grand total is 185. margins_name replaces the default label All.

Margins are recomputed from underlying rows using the aggregation function. For a mean pivot, the corner is 23.125, the mean of all eight orders. Averaging the six displayed cell means would give about 21.17 because the cells contain different numbers of observations. A margin is not a general instruction to add or average whatever numbers happen to be displayed.

Extend or count the table

aggfunc=["sum", "mean"] makes a block of columns for each statistic. With units, North–tea has sum 16 and mean 8. A function dictionary such as {"units": "sum", "revenue": "max"} assigns functions to separate value columns. Multiple keys can also create hierarchical rows or columns, and binned values work as categorical keys.

aggfunc="size" counts rows per cell, while "count" counts non-missing values in the chosen value column. North has one coffee order and two tea orders.

pivot does no aggregation and requires unique row-and-column combinations. The sales table has repeated North–tea and South–coffee combinations, so pivot raises a duplicate-entry error. Use pivot_table when several observations must be combined into one cell.

A cross-tabulation, or contingency table, counts observations for combinations of categorical variables. Proportions then let you compare groups that contain different numbers of observations.

Count combinations

The survey data has six basic-plan users: four use phones and two use laptops. Its four premium users include one phone user and three laptop users.

survey = pd.DataFrame({
    "plan": ["basic"] * 6 + ["premium"] * 4,
    "device": ["phone", "phone", "phone", "laptop", "phone", "laptop",
               "laptop", "laptop", "phone", "laptop"],
    "minutes": [20, 35, 15, 40, 25, 50, 60, 45, 30, 55]
})
counts = pd.crosstab(survey["plan"], survey["device"], margins=True)

Pass the actual Series, not the strings "plan" and "device". The first variable supplies rows and the second supplies columns. The basic row contains laptop 2 and phone 4; the premium row contains laptop 3 and phone 1. Row totals are 6 and 4, column totals are 5 and 5, and the grand total is 10.

For raw counts, an absent combination is shown as zero. Equivalent counts can be obtained with survey.groupby(["plan", "device"]).size().unstack(fill_value=0) or a pivot table using aggfunc="size". survey.value_counts(["plan", "device"]) gives a long frequency list sorted by count.

Choose the denominator from the question

“What share of premium users use a laptop?” starts with the premium users, so the denominator is 4. Use row normalization; the answer is 3/4 = 0.75. “What share of phone users have the basic plan?” starts with phone users, so the denominator is 5. Use column normalization; the answer is 4/5 = 0.8.

A count of three premium laptop users exceeds the basic count of two, but the within-plan comparison is 75% against about 33.3%. Identify the population being compared before turning a count into a share. Multiply normalized values by 100 for percentages.

With margins=True, normalized margins also have meanings. Row normalization adds an All row showing the overall device shares, 0.5 and 0.5. Column normalization adds an All column showing the overall plan shares, 0.6 and 0.4.

Summarize a third variable

Supply values and aggfunc to replace frequencies with a numeric statistic:

pd.crosstab(survey["plan"], survey["device"],
            values=survey["minutes"], aggfunc="mean")

Basic laptop sessions of 40 and 50 minutes average 45. Basic phone sessions of 20, 35, 15, and 25 minutes average 23.75. Premium laptop sessions of 60, 45, and 55 minutes average about 53.33, while the single premium phone session lasts 30 minutes. These cells describe session length, not user counts.

A list of Series can provide multiple row or column levels. Binned scores also work as categories: crossing fail, pass, merit, and distinction with class group gives day counts 1, 2, 2, 2 and evening counts 1, 2, 1, 1. Every cell’s meaning still follows from its row key, column key, and chosen statistic.