GroupBy, Aggregation, the MultiIndex and Reshaping

Context

Grouping splits a table into one piece per value of a key column, runs a calculation inside each piece and combines the answers. The answer can be one row per group, a summary, or one value per original row, each row carrying a fact about its group, and a grid can lay two keys out against each other, one as rows and one as columns.

This is how a table of listings becomes statements about neighborhoods. The mean price per neighborhood, the number of listings in each condition, a grid of mean price by neighborhood and condition, and a feature that says how a listing's price compares with its own neighborhood's are all one split followed by a different step. Module 3 builds its group features this way, computing each statistic on the training rows alone.

Almost every failure here is a number that looks fine. One counter includes the rows with no price and another skips them, so the same group reports two sizes and neither is labeled. A row whose key is missing belongs to no group and quietly leaves the totals. A per-group summary assigned where a per-row value belonged fills the column with missing values or with another group's numbers, and an empty cell in a grid reads as a zero it never was. The previous lesson built features one listing at a time, and this lesson builds them from the listing's group.

Goal

• Split a table with groupby, and distinguish size from count and a dropped missing key from a real group.
• Compute several labeled statistics per group with named aggregation, and reconcile the group sizes against len(df).
• Build a group-relative feature with transform, and explain why an agg result assigned as a column does not line up with the rows.
• Read a two-level index with .loc, flatten it with reset_index and widen it with unstack.
• Reshape between long rows and a grid with pivot_table and melt, and tell an empty cell from a zero.

Prerequisites

Optional, and the lesson stands without them. They are here if you want to watch a table being split into groups before you split one.

• YouTube - "Python Pandas Tutorial (Part 8): Grouping and Aggregating - Analyzing and Exploring Your Data" by Corey Schafer. The split, apply and combine loop on a real survey table, agg included.
• Official docs - pandas User Guide, "Group by: split-apply-combine", the "Aggregation" section. Built-in statistics, agg with several functions, and named aggregation.

Table of Contents

• groupby - Split, Apply, Combine
• agg and Named Aggregation
• transform - Group-relative Features
• The MultiIndex
• Reshaping - pivot_table and melt

Theory

Nothing to install. Every Theory block below goes into one script, groups.py, saved in ~/dev/bootcamp beside housing.csv and run with python3 groups.py, each block added under the one before it.

groupby - Split, Apply, Combine

What. df.groupby("neighborhood") splits the rows into one group per distinct value of the key column. A statistic named on it is computed inside each group, and the answers are combined into one result indexed by the key: split, apply, combine. Why. Almost every question asked of a table is about groups, the mean price per neighborhood, the number of listings per condition, and groupby answers it in one line, the way 2.1.3's GROUP BY did in SQL. When. Whenever a question says per, each or by.

The first block loads the course file and repeats Lesson 2.6.1's cleaning, duplicate rows and the four injected extremes dropped, the gaps kept on purpose so that every counter shows what it does with them. The groupby object it then builds is not a result. It holds the table and the key, and nothing is averaged until a statistic is named on the end. type(by_hood).__name__ prints the name of its type, and ngroups counts its groups.

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (df["price"] > 2_000_000)
    | (df["area_sqm"] > 1_000)
    | (df["rooms"] > 20)
)
df = df[~extreme].reset_index(drop=True)

by_hood = df.groupby("neighborhood")
print(type(by_hood).__name__)
print(by_hood.ngroups)
print(by_hood["price"].mean().round(0))

DataFrameGroupBy
7
neighborhood
Downtown     243938.0
Eastgate     420172.0
Hillcrest    185815.0
Lakeview     391259.0
Northend     193654.0
Riverside    298238.0
Southgate    212171.0
Name: price, dtype: float64

["price"] picks the column inside each group, .mean() averages the prices each group has and skips the missing ones, and the result is indexed by neighborhood, sorted by name.

Two counts look alike and answer different questions. size() counts the rows in each group, and count() counts the values present in the column it is applied to.

counts = pd.DataFrame({
    "size": by_hood.size(),
    "count": by_hood["price"].count(),
})
print(counts)
print(counts.sum().to_dict())

              size  count
neighborhood             
Downtown        18     16
Eastgate        32     29
Hillcrest       30     27
Lakeview        29     27
Northend        28     26
Riverside       27     21
Southgate       36     35
{'size': 200, 'count': 181}

Downtown has 18 listings and 16 prices. A report that says Downtown has 16 listings quoted count where it meant size, and neither output says which question it answered.

A row whose key is missing belongs to no group, and groupby leaves it out by default, where 2.1.3's GROUP BY kept NULL as a group of its own. The group sizes then no longer add up to the table. dropna=False brings the row back as a group labeled NaN, or <NA> on a nullable key.

print(df.groupby("condition").size().sum())  # 199
with_gap = df.groupby("condition", dropna=False).size()
# {'excellent': 33, 'fair': 53, 'good': 90, 'poor': 23, nan: 1}
print(with_gap.to_dict())

DON'T:

• DON'T quote count as a group's number of listings, it counts the present values of one column, and size counts the rows.
• DON'T let a missing key take its rows out of every group unnoticed. Compare the summed group sizes with len(df), and group with dropna=False when those rows matter.
• DON'T print a groupby object expecting an answer, a statistic named on it is what computes one.

agg and Named Aggregation

What. agg computes several statistics per group in one call. A list of statistic names gives one column per statistic, and named aggregation, agg(name=(column, statistic)), names each output column and chooses the column it reads. Why. One number rarely describes a group. A mean needs its count beside it to show how much data it rests on, and its median to show whether a few listings pull it, 2.5.1's point made per group. When. Whenever a group needs more than one number, which is whenever a person will read the result.

A list applies every statistic to the one column selected, and names each output column after its statistic.

print(by_hood["price"].agg(["count", "mean", "median"]).round(0))

              count      mean    median
neighborhood                           
Downtown         16  243938.0  254000.0
Eastgate         29  420172.0  409000.0
Hillcrest        27  185815.0  183000.0
Lakeview         27  391259.0  391000.0
Northend         26  193654.0  194500.0
Riverside        21  298238.0  287000.0
Southgate        35  212171.0  212000.0

Named aggregation lifts both limits. Each keyword is an output column, and each (column, statistic) pair says what to compute and from which column, so one call can count rows, count prices and average two different columns.

summary = by_hood.agg(
    listings=("price", "size"),
    priced=("price", "count"),
    mean_price=("price", "mean"),
    mean_area=("area_sqm", "mean"),
).round(1)
print(summary)
print(summary["listings"].sum() == len(df))

              listings  priced  mean_price  mean_area
neighborhood                                         
Downtown            18      16    243937.5      103.2
Eastgate            32      29    420172.4       98.2
Hillcrest           30      27    185814.8      106.1
Lakeview            29      27    391259.3      100.5
Northend            28      26    193653.8      106.4
Riverside           27      21    298238.1      101.4
Southgate           36      35    212171.4      102.0
True

size over price still counts rows, the unpriced ones included, so listings adds up to the table and priced does not. The last line is the reconciliation every grouped report deserves: the group sizes, summed, against len(df). Grouped by condition, the same check prints False, and the one listing with no condition is the reason.

DON'T:

• DON'T report a group's mean without its priced count beside it, a mean over 16 prices and one over 35 print the same way.
• DON'T read the list form's column names as a description, mean does not say which column it summarizes, and a named aggregation does.
• DON'T skip the reconciliation, a sum of group sizes that misses len(df) is the only trace a dropped group leaves.

transform - Group-relative Features

What. df.groupby("neighborhood")["price"].transform("mean") computes each group's mean and writes it on every row of that group. It returns a Series aligned to the original rows, one value per row, where agg returns one per group. Why. A model of how long a listing stays on the market reads a listing's price better against its own neighborhood's, and that comparison needs the group's statistic on the listing's row, which an aggregation has already collapsed away. When. When the group statistic has to sit beside each row as a feature. agg when the summary itself is the answer.

Six listings built in code show the difference whole.

toy = pd.DataFrame({
    "neighborhood": ["Downtown", "Riverside", "Downtown",
                     "Hillcrest", "Riverside", "Hillcrest"],
    "price": [300_000, 250_000, 340_000, 180_000, 270_000, 200_000],
})
print(toy.groupby("neighborhood")["price"].agg("mean"))
toy["nb_mean"] = toy.groupby("neighborhood")["price"].transform("mean")
print(toy)

neighborhood
Downtown     320000.0
Hillcrest    190000.0
Riverside    260000.0
Name: price, dtype: float64
  neighborhood   price   nb_mean
0     Downtown  300000  320000.0
1    Riverside  250000  260000.0
2     Downtown  340000  320000.0
3    Hillcrest  180000  190000.0
4    Riverside  270000  260000.0
5    Hillcrest  200000  190000.0

agg returned three rows, one per neighborhood, sorted by name. transform returned six values in the original order, so Downtown's 320000.0 sits on rows 0 and 2, each listing beside its own group's mean. On the real table, the same line gives every listing its neighborhood's mean price, and dividing by it gives a feature.

nb_mean = df.groupby("neighborhood")["price"].transform("mean")
print(len(nb_mean), nb_mean.index.equals(df.index))
df["price_vs_nb"] = df["price"] / nb_mean
print(df[["neighborhood", "price", "price_vs_nb"]].head(4).round(2))

200 True
  neighborhood     price  price_vs_nb
0     Lakeview  437000.0         1.12
1     Downtown  268000.0         1.10
2    Southgate  268000.0         1.26
3    Hillcrest       NaN          NaN

Rows 1 and 2 hold the same price, 10 percent above Downtown's mean and 26 percent above Southgate's, a difference a raw price column cannot express. Row 3 has no price, so its ratio is missing while its neighborhood mean is still on the row.

Assigning the agg result as a column is the mistake this subtitle exists to prevent. Its index holds the seven neighborhood names, the table's index holds the row labels 0 to 199, and a column assignment matches a Series by its labels, 2.4.1's alignment, so no row finds its value and nothing warns.

df["nb_mean_wrong"] = df.groupby("neighborhood")["price"].mean()
print(df["nb_mean_wrong"].isna().sum())  # 200

DON'T:

• DON'T assign an agg result as a column, its index holds the group keys and not the row labels, so the rows come back missing without an error, or, on a numeric key such as rooms, a row whose label equals a key takes that group's value.
• DON'T expect transform's output in group order, it keeps the original row order, which is what lets it sit beside the table.
• DON'T read a missing ratio as a missing neighborhood mean, the mean is on every row, and the ratio is missing only where the listing's own price is.

The MultiIndex

What. Aggregating a groupby on two keys gives a result whose index has two levels, one per key, so each entry is addressed by a tuple such as ("Downtown", "good"). Why. A two-way question, mean price by neighborhood and condition, has one answer per pair of values, and a two-level index holds them without inventing a column per pair. When. Every two-key groupby aggregation returns one. Reach one entry with .loc[(outer, inner)], flatten it with reset_index(), widen it with unstack().

two = df.groupby(["neighborhood", "condition"])["price"].mean().round(0)
# nlevels counts the levels of the index, one per key
print(two.index.nlevels, len(two))
print(two.head(6))
print(two.loc[("Downtown", "good")])

2 26
neighborhood  condition
Downtown      fair         266750.0
              good         225000.0
              poor         194000.0
Eastgate      excellent    443500.0
              fair         383143.0
              good         431333.0
Name: price, dtype: float64
225000.0

Seven neighborhoods and four conditions could make 28 pairs, and the result has 26, because a group exists only where a listing does: Downtown has no excellent listing and Northend no poor one. The outer label is printed once and left blank beneath itself, a display choice, and every entry still carries both labels, which is why .loc takes the pair as one tuple, outer level first.

flat = two.reset_index()
print(flat.shape, flat.columns.tolist())
print(two.unstack())

(26, 3) ['neighborhood', 'condition', 'price']
condition     excellent      fair      good      poor
neighborhood                                         
Downtown            NaN  266750.0  225000.0  194000.0
Eastgate       443500.0  383143.0  431333.0  419667.0
Hillcrest      162000.0  180429.0  187333.0  195500.0
Lakeview       383500.0  379444.0  410778.0  383667.0
Northend       196857.0  174500.0  200769.0       NaN
Riverside      278000.0  316800.0  278875.0  352667.0
Southgate      188667.0  228400.0  214636.0  207571.0

reset_index() turned both levels back into ordinary columns. unstack() moved the inner level, condition, out into the columns, one row per neighborhood and one column per condition, and the two pairs that never occurred became empty cells.

DON'T:

• DON'T assume a two-key result holds every pair, only pairs that occur become groups, 26 of the 28 here.
• DON'T forget which level unstack() moves, the inner one, so the order of the keys in groupby decides which key becomes the columns.

Reshaping - pivot_table and melt

What. pivot_table(index=, columns=, values=, aggfunc=) builds a grid from raw rows, its rows labeled by the values of one key and its columns by the values of the other, each cell an aggregate of the rows that land in it. melt turns wide columns back into long rows, one row per cell. Why. A grid is how a two-way comparison is read and what a heatmap (a chart that colors each cell of a grid by its value) draws, and long rows are what groupby, a filter and most models take. When. pivot_table to go from records to a grid in one call. melt when a variable is spread across column headers and you need it as a column.

grid = df.pivot_table(
    index="neighborhood",
    columns="condition",
    values="price",
    aggfunc="mean",
).round(0)
print(grid.equals(two.unstack()))  # True

It is the unstack() grid of the previous subtitle, computed from raw rows in one call. Its two empty cells mean no listing exists for that pair, not a price of zero. In a count grid the same pair is a real count of 0, so fill_value=0 is right there and wrong on the mean grid, where it prints a Downtown excellent price of 0.0 that no listing ever had.

counts = df.pivot_table(
    index="neighborhood",
    columns="condition",
    values="price",
    aggfunc="size",
    fill_value=0,
)
print(counts)
zeros = df.pivot_table(
    index="neighborhood",
    columns="condition",
    values="price",
    aggfunc="mean",
    fill_value=0,
)
print(zeros.loc["Downtown", "excellent"])

condition     excellent  fair  good  poor
neighborhood                             
Downtown              0     8     9     1
Eastgate              7     7    15     3
Hillcrest             1     7    17     5
Lakeview              6     9    11     3
Northend              7     6    15     0
Riverside             6     6    11     4
Southgate             6    10    12     7
0.0

pivot is the reshape that never aggregates. It moves each value to its cell, so no index and column pair may occur twice, and on raw listings it refuses.

try:
    df.pivot(index="neighborhood", columns="condition", values="price")
except ValueError as err:
    print(f"ValueError: {err}")  # ValueError: Index contains duplicate entries, cannot reshape

melt goes the other way. id_vars names the columns that stay as identifiers, every other column becomes rows, and var_name and value_name name the two new columns.

long = grid.reset_index().melt(
    id_vars="neighborhood",
    var_name="condition",
    value_name="mean_price",
)
print(long.shape)
print(long.head(4))
back = long.pivot_table(
    index="neighborhood",
    columns="condition",
    values="mean_price",
)
print(back.equals(grid))

(28, 3)
  neighborhood  condition  mean_price
0     Downtown  excellent         NaN
1     Eastgate  excellent    443500.0
2    Hillcrest  excellent    162000.0
3     Lakeview  excellent    383500.0
True

The 7 by 4 grid became 28 rows, one per cell, the two empty cells included as rows with no value, so a row count of the melted table says 28 where the data holds 26 pairs. pivot_table turned the long rows back into the same grid.

DON'T:

• DON'T read an empty pivot cell as zero, it means no listing with a value has that pair, and fill_value=0 belongs on a count grid, never on a mean grid.
• DON'T reach for pivot on raw listings, a repeated index and column pair raises ValueError, and pivot_table aggregates the repeats.
• DON'T count a melted table as observations before dropping its empty cells, the grid made a cell for every pair and melt turned each one into a row.

Lab Exercises

Lab Exercise 1: Counting by Condition

Start a new script in ~/dev/bootcamp with the loading and cleaning block that opens the Theory, then group the listings by condition.

1. Print size() and the count() of price side by side in one frame, then their two totals and len(df). Add a comment explaining each gap.
2. Group again with dropna=False and print the group sizes.
3. Build a report with named aggregation, grouped with dropna=False: listings, priced, mean_price and median_price, rounded to whole numbers. Print it, and print whether listings adds up to len(df).

Lab Exercise 2: A Feature, a Two-Level Index and a Grid

Start another script with the same opening block.

1. Add area_vs_nb, each listing's area divided by its neighborhood's median area, with transform. Print the first three values rounded to two decimals, the number missing, and how many listings sit above, below and exactly on their neighborhood's median.
2. Compute the median area by neighborhood and condition with groupby. Print the number of index levels and entries, the Riverside and poor entry rounded to one decimal, and the number of empty cells after unstack().
3. Build a grid of listing counts with pivot_table, neighborhoods as rows and rooms as columns, empty pairs filled with 0. Print it and its total. Then melt it into rows named rooms and listings, and print the shape and every row holding 8 or more listings.

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (df["price"] > 2_000_000)
    | (df["area_sqm"] > 1_000)
    | (df["rooms"] > 20)
)
df = df[~extreme].reset_index(drop=True)

by_condition = df.groupby("condition")
side = pd.DataFrame({
    "size": by_condition.size(),
    "count": by_condition["price"].count(),
})
print(side)
print(side.sum().to_dict(), len(df))
# size misses 1 row, the listing with no condition.
# count also misses the 19 listings with no price.

           size  count
condition             
excellent    33     29
fair         53     52
good         90     78
poor         23     21
{'size': 199, 'count': 180} 200

print(df.groupby("condition", dropna=False).size().to_dict())

report = df.groupby("condition", dropna=False).agg(
    listings=("price", "size"),
    priced=("price", "count"),
    mean_price=("price", "mean"),
    median_price=("price", "median"),
).round(0)
print(report)
print(report["listings"].sum() == len(df))

{'excellent': 33, 'fair': 53, 'good': 90, 'poor': 23, nan: 1}
           listings  priced  mean_price  median_price
condition                                            
excellent        33      29    280586.0      228000.0
fair             53      52    277096.0      265000.0
good             90      78    278897.0      240000.0
poor             23      21    280810.0      225000.0
NaN               1       1    196000.0      196000.0
True

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (df["price"] > 2_000_000)
    | (df["area_sqm"] > 1_000)
    | (df["rooms"] > 20)
)
df = df[~extreme].reset_index(drop=True)

median_area = df.groupby("neighborhood")["area_sqm"].transform("median")
df["area_vs_nb"] = df["area_sqm"] / median_area
# [1.45, 1.0, 1.05]
print(df["area_vs_nb"].head(3).round(2).tolist())
print(df["area_vs_nb"].isna().sum())  # 15
above = (df["area_vs_nb"] > 1).sum()
below = (df["area_vs_nb"] < 1).sum()
on = (df["area_vs_nb"] == 1).sum()
print(above, below, on)               # 90 90 5

two = df.groupby(["neighborhood", "condition"])["area_sqm"].median()
print(two.index.nlevels, len(two))       # 2 26
# 84.8
print(round(two.loc[("Riverside", "poor")], 1))
print(two.unstack().isna().sum().sum())  # 2

rooms_grid = df.pivot_table(
    index="neighborhood",
    columns="rooms",
    values="price",
    aggfunc="size",
    fill_value=0,
)
print(rooms_grid)
print(rooms_grid.to_numpy().sum())
long = rooms_grid.reset_index().melt(
    id_vars="neighborhood",
    var_name="rooms",
    value_name="listings",
)
print(long.shape)
print(long[long["listings"] >= 8])

rooms         1  2  3  4  5   6
neighborhood                   
Downtown      6  2  4  1  3   2
Eastgate      7  7  7  4  4   3
Hillcrest     6  4  2  8  8   2
Lakeview      1  6  8  2  7   5
Northend      5  5  4  4  6   4
Riverside     3  6  3  5  4   6
Southgate     5  6  5  4  4  12
200
(42, 3)
   neighborhood rooms  listings
17     Lakeview     3         8
23    Hillcrest     4         8
30    Hillcrest     5         8
41    Southgate     6        12

Theory Assessment

Q1: Three Totals That Disagree

On the cleaned table, df.groupby("condition").size().sum() prints 199, df.groupby("condition")["price"].count().sum() prints 180, and len(df) is 200. Explain each of the two gaps, row by row.

Hint: One listing has a price and no condition. Ask which of the two counters can see it, and then ask what the second counter skips that the first does not.

Q2: Seven Values on Two Hundred Rows

df.groupby("neighborhood")["price"].mean() returns 7 values and df.groupby("neighborhood")["price"].transform("mean") returns 200. For each one, say what its index holds, and predict what each holds once assigned as a new column of df.

Hint: A column assignment pairs a Series' values by index label, never by position.

Q3: Two Grids, Two Kinds of Empty

The mean-price grid by neighborhood and condition has two empty cells, and the count grid built with fill_value=0 holds 0 in the same two places. Explain why each grid is right for its own question, what fill_value=0 would claim on the mean grid, and why df.pivot on the same three columns raises instead of building either grid.

Hint: Ask what the number in each cell measures, and whether "nothing to measure" has a value in that measure.

Q4: The Same Row, Twice

Read this without running it. The cleaned table has 19 listings with no price.

cond_mean = df.groupby("condition")["price"].transform("mean")
ratio = df["price"] / cond_mean
print(ratio.isna().sum())

grid = df.pivot_table(
    index="neighborhood",
    columns="condition",
    values="price",
    aggfunc="size",
    fill_value=0,
)
print(grid.to_numpy().sum())

Predict both numbers, then show that a single listing explains both surprises, and say what dropna=False would change and why it is not the whole fix.

Hint: One Southgate listing has a price of 196000 and no condition. Follow it through the transform first, then through the grid.

Assignment

Assignment: A Neighborhood Feature That Rests on Enough Listings

A neighborhood median computed from a handful of prices moves with every new listing, so the model's group-relative price feature is to be built only for neighborhoods with at least 20 priced listings, and every other listing gets no value for it, rather than a number that looks just as trustworthy as the rest. Produce three things from the cleaned table.

First, a per-neighborhood report built with named aggregation, holding listings, priced and median_price, sorted by priced, so the threshold's victims are visible at a glance.

Second, the subset of listings whose neighborhood clears the threshold, chosen with GroupBy.filter. df.groupby(key).filter(func) calls func once per group, handing it the group's rows as a DataFrame, and returns every row of the groups for which func returned True, in their original order and with their original labels, so whole groups are kept or dropped. Print which neighborhoods survive and how many rows, and prove the kept and dropped rows add up to the table.

Third, the feature price_vs_nb, each listing's price over its neighborhood's median price, computed on the kept rows and put back on the full table so that the dropped neighborhood's listings hold missing values. Count the missing values and account for every one of them. Then lay the feature's median out by neighborhood and condition with pivot_table, and say what happened to the neighborhood that was dropped.

Hints:

• Inside filter, the group is a whole DataFrame, so g["price"].count() is that neighborhood's number of priced listings.
• filter keeps the original row labels, so a Series computed on the kept rows can be assigned to the full table and aligns by label, which is the alignment that ruined the agg assignment in Theory, working in your favor.

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (df["price"] > 2_000_000)
    | (df["area_sqm"] > 1_000)
    | (df["rooms"] > 20)
)
df = df[~extreme].reset_index(drop=True)

report = df.groupby("neighborhood").agg(
    listings=("price", "size"),
    priced=("price", "count"),
    median_price=("price", "median"),
).sort_values("priced")
print(report)

              listings  priced  median_price
neighborhood                                
Downtown            18      16      254000.0
Riverside           27      21      287000.0
Northend            28      26      194500.0
Hillcrest           30      27      183000.0
Lakeview            29      27      391000.0
Eastgate            32      29      409000.0
Southgate           36      35      212000.0

# keep every row of a neighborhood with at least 20 prices
kept = df.groupby("neighborhood").filter(lambda g: g["price"].count() >= 20)
print(sorted(kept["neighborhood"].unique().tolist()))
dropped = df[~df.index.isin(kept.index)]
print(len(kept), len(dropped), len(kept) + len(dropped) == len(df))

nb_median = kept.groupby("neighborhood")["price"].transform("median")
# aligned by label, so Downtown's rows get NaN
df["price_vs_nb"] = kept["price"] / nb_median
print(df["price_vs_nb"].isna().sum())
unpriced_kept = kept["price"].isna().sum()
print(len(dropped), unpriced_kept)

['Eastgate', 'Hillcrest', 'Lakeview', 'Northend', 'Riverside', 'Southgate']
182 18 True
35
18 17

grid = df.pivot_table(
    index="neighborhood",
    columns="condition",
    values="price_vs_nb",
    aggfunc="median",
).round(2)
print(grid)
print(grid.shape)

condition     excellent  fair  good  poor
neighborhood                             
Eastgate           1.06  0.95  1.07  1.04
Hillcrest          0.89  0.95  1.04  1.07
Lakeview           0.99  0.96  1.05  1.06
Northend           1.00  0.92  1.03   NaN
Riverside          1.00  1.07  0.95  1.39
Southgate          0.88  1.07  1.04  1.00
(6, 4)

Additional Resources

Optional, and nothing later depends on them. Worth opening if you want more of what pandas offers for grids and two-level indexes.

• Official docs - pandas User Guide, "Reshaping and pivot tables", the "crosstab()" section. A count grid from two columns in one call, with normalize for each row's shares.
• Official docs - pandas User Guide, "MultiIndex / advanced indexing", the "Cross-section" section. xs, which fixes one level of a two-level index and keeps every value of the other.

Summary

Grouping is the point where a table stops describing listings and starts describing populations, and the lesson kept returning to one question: what shape should the answer have? A summary has one row per group and is read by a person. A feature has one value per listing and is read by a model. A grid holds pairs of groups as rows and columns and is read across and down. The same split feeds all three, and choosing the wrong shape is not caught by pandas, because a summary assigned where a feature belonged fails by alignment, silently, into missing values or another group's numbers.

The other thread was accounting. Most group results here were checked against the table they came from, the sizes summed against len(df), the two counters set side by side, the melted rows set against the pairs that really occurred. The numbers that did not reconcile were never errors in the arithmetic, they were rows the operation had quietly decided not to see, the listing with no condition, the cells nobody listed, the neighborhood excluded on purpose. A grouped number is only as honest as its denominator, and the denominator is the thing a report most often leaves out.

The features built here answer how a listing compares with its own group, which is half of what a model of how long a listing stays on the market wants. The other half is information that lives in a different table altogether, the region a neighborhood belongs to and its distance from the center, and joining that table on without changing the number of listings is the next lesson.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

g = df.groupby("neighborhood")
g.size()                                # rows, gaps included
g["price"].count()                      # present prices only
# keeps the NaN key
df.groupby("condition", dropna=False).size()

summary = g.agg(
    listings=("price", "size"),
    priced=("price", "count"),
    mean_price=("price", "mean"),
)
summary["listings"].sum() == len(df)    # the reconciliation

nb_mean = g["price"].transform("mean")  # one value per row
df["price_vs_nb"] = df["price"] / nb_mean

two = df.groupby(["neighborhood", "condition"])["price"].mean()
two.loc[("Downtown", "good")]           # one tuple
two.reset_index()                       # levels to columns
two.unstack()                           # inner level to columns

grid = df.pivot_table(index="neighborhood", columns="condition",
                      values="price", aggfunc="mean")
long = grid.reset_index().melt(id_vars="neighborhood")