Level 2 · Clean and rank the locations in pandas

What you are checking, and why

Customer addresses are typed by customers, so the same place arrives in several spellings. Before you group by anything, measure how bad it is. nunique() on the raw column against the same count on the cleaned values tells you in two lines:

customers["state"].nunique()                         # 69 labels
customers["state"].str.strip().str.upper().nunique() # 39 states

.str.strip() removes the stray spaces and .str.upper() puts every label in one case. People are matched the same way, on the cleaned email, because an email is the closest thing the table has to a person’s identity: the customer_id changes when someone signs up again.

.str methods leave NaN as NaN and nunique() skips it, so the 14 blank cities stay out of the count instead of becoming a city called “NAN”. isna().sum() counts them; they cannot be placed in a metro and drop out of step 3.

States first, then metros

Step 2 gives the board the familiar state view. It mixes two sources: customers for how many people live in each state, and the order lines for who bought and how much. Each side is aggregated on its own, as a Series indexed by state, and pd.DataFrame({...}) lines them up by that index.

Step 3 is the answer to the showroom question. It keeps consumers only, because business accounts order online and would not visit, and it ranks metros, because a showroom serves a city:

c = customers[(customers["segment"] == "consumer") & customers["city"].notna()]
r = lines.pivot_table(index=["city", "state"], columns="yr", values="revenue", aggfunc="sum")

The pivot columns are the years as integers, so r[2025] - r[2024] subtracts row by row. Compute the growth on these unrounded totals and round last.

Reading the result

city	buyers_2025	revenue_2025	growth_pct
NEW YORK	104	96,984.37	−5.9
LOS ANGELES	78	71,631.90	30.7
DALLAS	58	66,362.59	17.2

New York is the largest and Los Angeles the fastest growing of the leaders. The state table would have pointed at California as a whole, and its top-line growth of 0.3% would have hidden that Los Angeles is growing fast.

Pitfalls

• Cleaning a copy and then merging the raw table splits the groups again. Clean once, on customers, and merge the cleaned columns.
• customers["city"] != customers["city"].str.strip() is True for every NaN, because NaN never equals anything. Count messy values with .notna() in the mask.
• head(10) without sort_values returns the first ten states in alphabetical order.