Skip to content

Chapter 5: Cleaning, Reshaping, Grouping and Joining

Turning raw tables into tables you can trust and analyse

Learning outcomes

After this chapter you should be able to:

  • find and count missing values, and choose between dropping, filling and flagging them;
  • remove duplicate rows, with or without a subset of key columns;
  • convert columns to the right types, coercing bad values to missing;
  • clean text columns with the .str methods;
  • detect impossible values and outliers, and decide what to do with them;
  • bin a numeric column into categories with pd.cut and pd.qcut;
  • summarise groups with groupby, agg and transform (split-apply-combine);
  • reshape tables between wide and long forms with pivot_table, melt and crosstab;
  • combine tables with concat and merge, and choose the right kind of join.

Data rarely arrives ready for analysis. Values are missing, rows are repeated, numbers are stored as text, the same city is spelled four ways, and the information you need is split across several tables. This chapter is about the transformations that fix those problems and then summarise and rearrange the clean result. It is the longest chapter in the first half of the book because this is where most of the practical work of data science happens.

import io
import numpy as np
import pandas as pd

5.1 A messy table

A small business has exported its customer list. Here it is, exactly as exported:

raw_text = """id,city,age,joined,spend,plan
1,Pune,34,2023-01-15,"1,200",basic
2, delhi ,29,2023-02-03,850,premium
3,Chennai,,2023-02-20,430,basic
1,Pune,34,2023-01-15,"1,200",basic
5,Mumbai,-1,2023-03-11,2300,premium
6,DELHI,41,2023-03-30,NA,basic
7,Pune ,230,2023-04-02,640,Basic
8,chennai,52,2023-02-30,1100,premium
9,Kolkata,38,2023-05-19,-,basic
10,Mumbai,27,2023-06-01,980,PREMIUM
"""
raw = pd.read_csv(io.StringIO(raw_text))
print(raw)
raw.info()
   id     city    age      joined  spend     plan
0   1     Pune   34.0  2023-01-15  1,200    basic
1   2   delhi    29.0  2023-02-03    850  premium
2   3  Chennai    NaN  2023-02-20    430    basic
3   1     Pune   34.0  2023-01-15  1,200    basic
4   5   Mumbai   -1.0  2023-03-11   2300  premium
5   6    DELHI   41.0  2023-03-30    NaN    basic
6   7    Pune   230.0  2023-04-02    640    Basic
7   8  chennai   52.0  2023-02-30   1100  premium
8   9  Kolkata   38.0  2023-05-19      -    basic
9  10   Mumbai   27.0  2023-06-01    980  PREMIUM
<class 'pandas.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   id      10 non-null     int64  
 1   city    10 non-null     str    
 2   age     9 non-null      float64
 3   joined  10 non-null     str    
 4   spend   9 non-null      str    
 5   plan    10 non-null     str    
dtypes: float64(1), int64(1), str(4)
memory usage: 861.0 bytes

Looking carefully, there are at least eight problems:

  1. Customer 1 appears twice (a duplicate row).
  2. City names have stray spaces and inconsistent capitals (" delhi ", DELHI, chennai, "Pune ").
  3. Customer 3's age is missing.
  4. Two ages are impossible: -1 and 230.
  5. One date does not exist (30 February).
  6. The spend column was read as text (str), because one value has a thousands separator ("1,200") and others are NA and -.
  7. NA was recognised as missing, but - was not.
  8. The plan names have inconsistent capitals.

The info output gives two of these away at once: spend and joined are str rather than numbers and dates, and age has 9 non-null values out of 10. The sections below fix each problem in turn.

5.2 Missing values

pandas marks missing values as NaN (for numbers), NaT ("not a time", for dates) or NA. The method isna gives a mask of missing values, and summing it counts them per column.

print(raw.isna().sum())
print(raw[raw["age"].isna()])
id        0
city      0
age       1
joined    0
spend     1
plan      0
dtype: int64
   id     city  age      joined spend   plan
2   3  Chennai  NaN  2023-02-20   430  basic

Only two missing values are detected, because read_csv recognises a standard list of markers (NA, empty fields, NaN, null and a few others) but not -. Telling it about extra markers when loading is the cleanest fix:

raw = pd.read_csv(io.StringIO(raw_text), na_values=["-"])
print(raw["spend"].isna().sum(), "missing spend values")
2 missing spend values

Once missing values are found, there are three things you can do with them.

Drop the rows (or columns) that contain them, with dropna. This is simple and honest when few rows are affected and they are missing for reasons unrelated to the question. It wastes data and can bias the result when many rows are affected, or when the missingness is related to the values (people with high incomes may be less likely to report them).

Fill them with a value, with fillna: a constant, the column's mean or median, the previous value in a time series (ffill), or a prediction from other columns. Filling keeps the rows but invents data, and it makes the column look less variable than it really is.

Flag them: keep a column that records which values were missing, so that the information is not lost and the analysis can check whether the missing rows differ from the rest.

demo = pd.DataFrame({"x": [1.0, np.nan, 3.0, np.nan], "y": ["a", "b", None, "d"]})
print(demo.dropna())                      # any missing value drops the row
print(demo.dropna(subset=["x"]))          # only x matters
print(demo.fillna({"x": demo["x"].median(), "y": "unknown"}))
demo["x_missing"] = demo["x"].isna()
print(demo)
     x  y
0  1.0  a
     x    y
0  1.0    a
2  3.0  NaN
     x        y
0  1.0        a
1  2.0        b
2  3.0  unknown
3  2.0        d
     x    y  x_missing
0  1.0    a      False
1  NaN    b       True
2  3.0  NaN      False
3  NaN    d       True

Key idea

There is no correct way to handle missing values in general, only a defensible choice for a particular question. Whatever you choose, count the missing values first, report how many there were and what you did, and check whether the choice changes the answer.

Integers with missing values

A NumPy integer column cannot hold NaN, so an integer column with a gap becomes float64 (the age column above prints as 34.0). pandas has nullable types, written with a capital letter (Int64, Float64, boolean), that can hold integers alongside a missing marker, shown as <NA>:

ages = pd.Series([34, None, 41])
print(ages.dtype, ages.tolist())
print(ages.astype("Int64"))
float64 [34.0, nan, 41.0]
0      34
1    <NA>
2      41
dtype: Int64

5.3 Duplicates

duplicated marks each row that repeats an earlier one; drop_duplicates removes them. By default all columns must match. With subset= only the named columns are compared, which finds records that share an identifier but differ elsewhere: often a more serious problem than an exact copy.

print(raw.duplicated().sum(), "exact duplicate rows")
print(raw[raw.duplicated(subset=["id"], keep=False)])
df = raw.drop_duplicates().reset_index(drop=True)
print(df.shape)
1 exact duplicate rows
   id  city   age      joined  spend   plan
0   1  Pune  34.0  2023-01-15  1,200  basic
3   1  Pune  34.0  2023-01-15  1,200  basic
(9, 6)

keep=False marks every member of a duplicated group, not just the later copies, so that you can see all of them together. Here the two rows for customer 1 are identical, so dropping one is safe. If they had differed (say, in spend), you would have to find out which was right.

5.4 Fixing types

Each column should have the type its meaning requires: numbers as numbers, dates as dates, categories as text or category. pd.to_numeric and pd.to_datetime convert columns, and errors="coerce" turns any value that cannot be converted into a missing value instead of raising an error.

df["spend"] = pd.to_numeric(df["spend"].str.replace(",", ""), errors="coerce")
df["joined"] = pd.to_datetime(df["joined"], format="%Y-%m-%d", errors="coerce")
print(df[["id", "joined", "spend"]])
print(df.dtypes)
   id     joined   spend
0   1 2023-01-15  1200.0
1   2 2023-02-03   850.0
2   3 2023-02-20   430.0
3   5 2023-03-11  2300.0
4   6 2023-03-30     NaN
5   7 2023-04-02   640.0
6   8        NaT  1100.0
7   9 2023-05-19     NaN
8  10 2023-06-01   980.0
id                 int64
city                 str
age              float64
joined    datetime64[us]
spend            float64
plan                 str
dtype: object

The comma was removed before conversion, so "1,200" became 1200. The impossible date 2023-02-30 became NaT. Giving the format explicitly is good practice: it is faster, and it makes pandas reject dates in an unexpected layout instead of guessing.

Caution: coercion hides problems as well as solving them

errors="coerce" turns every unconvertible value into a missing value, silently. Always count the missing values before and after a coercion; a large jump means the conversion rule is wrong (for example, a column of dates written day-first, read as month-first).

5.5 Cleaning text

The .str accessor applies string methods to every value in a column at once (and leaves missing values missing).

print(df["city"].unique().tolist())
df["city"] = df["city"].str.strip().str.title()
df["plan"] = df["plan"].str.lower()
print(df["city"].value_counts())
print(df["plan"].value_counts())
['Pune', ' delhi ', 'Chennai', 'Mumbai', 'DELHI', 'Pune ', 'chennai', 'Kolkata']
city
Pune       2
Delhi      2
Chennai    2
Mumbai     2
Kolkata    1
Name: count, dtype: int64
plan
basic      5
premium    4
Name: count, dtype: int64

Eight spellings collapsed into five real cities. Other useful methods are str.replace (with a plain string or a regular expression), str.contains (a mask of rows containing a pattern), str.startswith, str.split and str.len. When the variants cannot be fixed by a rule, a dictionary does it:

fix = {"Bombay": "Mumbai", "Madras": "Chennai", "Calcutta": "Kolkata"}
old_names = pd.Series(["Bombay", "Pune", "Madras"])
print(old_names.replace(fix).tolist())
['Mumbai', 'Pune', 'Chennai']

A column with a few distinct values repeated many times can be stored as a category, which saves memory and records the set of allowed values. An ordered category also knows the order of its levels:

df["plan"] = pd.Categorical(df["plan"], categories=["basic", "premium"], ordered=True)
print(df["plan"].dtype, (df["plan"] > "basic").sum(), "premium customers")
category 4 premium customers

5.6 Impossible values and outliers

An impossible value breaks a rule that must hold: an age of -1 or 230, a negative quantity, a percentage above 100, an end date before a start date. These are errors, and the usual treatment is to set them to missing (or correct them, if the right value can be found).

An outlier is a value far from the rest that may well be genuine: one very large order, one very tall person. Outliers should not be removed automatically; they should be looked at, and kept unless there is a reason to believe they are errors.

bad_age = (df["age"] < 0) | (df["age"] > 110)
print(df.loc[bad_age, ["id", "age"]])
df.loc[bad_age, "age"] = np.nan
print(df["age"].describe().round(1))
   id    age
3   5   -1.0
5   7  230.0
count     6.0
mean     36.8
std       9.1
min      27.0
25%      30.2
50%      36.0
75%      40.2
max      52.0
Name: age, dtype: float64

A common rule of thumb for flagging outliers uses the interquartile range (IQR), the distance between the first quartile \(Q_1\) and the third quartile \(Q_3\). Values below \(Q_1 - 1.5\,\text{IQR}\) or above \(Q_3 + 1.5\,\text{IQR}\) are flagged. (These are the points beyond the whiskers of a box plot, Chapter 6.)

q1, q3 = df["spend"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(f"Q1={q1}, Q3={q3}, IQR={iqr}, fences=({low}, {high})")
print(df.loc[(df["spend"] < low) | (df["spend"] > high), ["id", "spend"]])
Q1=745.0, Q3=1150.0, IQR=405.0, fences=(137.5, 1757.5)
   id   spend
3   5  2300.0

Customer 5's spend of 2300 is flagged. Nothing suggests it is an error, so it stays; but a report on "typical spend" should prefer the median, which it barely affects.

Note: clipping

Sometimes extreme values are kept but limited to a range, with clip(lower, upper). This is called winsorising when the limits are percentiles. It reduces the influence of extremes on means and on some models, at the cost of changing the data; use it deliberately and say so.

Here is the cleaned customer table, which Section 5.9 uses again:

customers = df
print(customers)
   id     city   age     joined   spend     plan
0   1     Pune  34.0 2023-01-15  1200.0    basic
1   2    Delhi  29.0 2023-02-03   850.0  premium
2   3  Chennai   NaN 2023-02-20   430.0    basic
3   5   Mumbai   NaN 2023-03-11  2300.0  premium
4   6    Delhi  41.0 2023-03-30     NaN    basic
5   7     Pune   NaN 2023-04-02   640.0    basic
6   8  Chennai  52.0        NaT  1100.0  premium
7   9  Kolkata  38.0 2023-05-19     NaN    basic
8  10   Mumbai  27.0 2023-06-01   980.0  premium

5.7 Binning

Turning a numeric column into ordered categories ("binning") makes tables and charts easier to read. pd.cut uses edges you choose; pd.qcut makes bins with (roughly) equal numbers of values, using quantiles.

ages = pd.Series([19, 23, 31, 38, 45, 52, 61, 70])
groups = pd.cut(ages, bins=[0, 25, 45, 65, 120], labels=["under 25", "25-44", "45-64", "65+"], right=False)
print(pd.DataFrame({"age": ages, "group": groups}))
print(pd.qcut(ages, q=4, labels=["Q1", "Q2", "Q3", "Q4"]).value_counts().sort_index())
   age     group
0   19  under 25
1   23  under 25
2   31     25-44
3   38     25-44
4   45     45-64
5   52     45-64
6   61     45-64
7   70       65+
Q1    2
Q2    2
Q3    2
Q4    2
Name: count, dtype: int64

right=False makes each bin include its left edge and exclude its right one, so 25 falls in "25-44" and 45 in "45-64". Getting the edges right is the whole difficulty of binning; always check a few values at the boundaries.

5.8 Grouping: split, apply, combine

Many questions have the form "for each group, compute a summary": revenue per city, average mark per subject, number of patients per ward per month. pandas answers them with groupby, which follows a pattern called split-apply-combine (Figure 5.1): split the rows into groups by the values of a key, apply a function to each group, and combine the results into a new table.

A table of seven amounts, each labelled with a city, is split into three groups by city; the sum is applied to each group, giving 928 for Chennai, 532 for Delhi and 672 for Pune, and the three results are combined into one small table indexed by city.
Figure 5.1 Split, apply, combine. A table of seven amounts, each labelled with a city, is split into three groups by city; the sum is applied to each group, giving 928 for Chennai, 532 for Delhi and 672 for Pune, and the three results are combined into one small table indexed by city.

To have enough rows to make grouping interesting, we generate a year of quarterly sales for three regions and three products with a seeded random generator:

rng = np.random.default_rng(5)
regions = ["North", "South", "West"]
products = ["tea", "coffee", "juice"]
quarters = ["Q1", "Q2", "Q3", "Q4"]
rows = []
for r in regions:
    for p in products:
        base = {"tea": 300, "coffee": 200, "juice": 120}[p]
        for q in quarters:
            units = int(base * rng.uniform(0.7, 1.3)) + (60 if (p == "juice" and q == "Q2") else 0)
            rows.append({"region": r, "product": p, "quarter": q, "units": units})
sales = pd.DataFrame(rows)
sales["price"] = sales["product"].map({"tea": 10.0, "coffee": 15.0, "juice": 25.0})
sales["revenue"] = sales["units"] * sales["price"]
print(sales.shape)
print(sales.head(6))
(36, 6)
  region product quarter  units  price  revenue
0  North     tea      Q1    354   10.0   3540.0
1  North     tea      Q2    355   10.0   3550.0
2  North     tea      Q3    302   10.0   3020.0
3  North     tea      Q4    261   10.0   2610.0
4  North  coffee      Q1    146   15.0   2190.0
5  North  coffee      Q2    186   15.0   2790.0

(Juice gets an extra 60 units in the second quarter, the hot season, so that there is a pattern to find.)

Basic grouping

print(sales.groupby("region")["revenue"].sum())
print(sales.groupby(["region", "product"])["units"].sum().head(6))
region
North    36010.0
South    38410.0
West     36940.0
Name: revenue, dtype: float64
region  product
North   coffee      666
        juice       532
        tea        1272
South   coffee      754
        juice       522
        tea        1405
Name: units, dtype: int64

Grouping by two keys gives a result indexed by both, a MultiIndex. reset_index() turns the keys back into ordinary columns, which is usually easier to work with.

Several summaries at once: agg

agg applies several functions at once. Named aggregation gives each result column a name of your choice, as new_name=(column, function):

summary = (sales.groupby("product")
                .agg(total_units=("units", "sum"),
                     mean_units=("units", "mean"),
                     best_quarter_units=("units", "max"),
                     revenue=("revenue", "sum"),
                     n_rows=("units", "size"))
                .sort_values("revenue", ascending=False))
print(summary.round(1))
         total_units  mean_units  best_quarter_units  revenue  n_rows
product                                                              
tea             3996       333.0                 385  39960.0      12
juice           1554       129.5                 215  38850.0      12
coffee          2170       180.8                 244  32550.0      12

Keeping the original rows: transform

agg returns one row per group. transform returns a result for every original row, the group's summary repeated, which is what you need to compare each row with its group. For example, each quarter's share of its region-and-product total:

sales["group_total"] = sales.groupby(["region", "product"])["units"].transform("sum")
sales["share"] = (sales["units"] / sales["group_total"]).round(3)
print(sales.loc[(sales["region"] == "North") & (sales["product"] == "juice"),
                ["quarter", "units", "group_total", "share"]])
   quarter  units  group_total  share
8       Q1     87          532  0.164
9       Q2    215          532  0.404
10      Q3    130          532  0.244
11      Q4    100          532  0.188

The North's juice sales peak in Q2, as built into the data. Two further methods complete the toolkit: filter keeps or drops whole groups by a condition (sales.groupby("region").filter(lambda g: g["revenue"].sum() > 37000)), and apply runs an arbitrary function on each group's DataFrame when nothing simpler fits.

Caution: groups with missing keys

By default groupby leaves out rows whose key is missing. If 50 orders have no city, groupby("city") silently ignores them and the city totals no longer add up to the grand total. Pass dropna=False to keep them as a group of their own, or count the missing keys first.

5.9 Reshaping

Long to wide: pivot_table

The sales table is long: one row per region, product and quarter. For reading, a wide table with one row per product and one column per quarter is easier. pivot_table makes it, aggregating if several rows fall in the same cell:

wide = sales.pivot_table(index="product", columns="quarter", values="units", aggfunc="sum")
print(wide)
print(sales.pivot_table(index="region", columns="product", values="revenue",
                        aggfunc="sum", margins=True, margins_name="Total"))
quarter   Q1    Q2   Q3   Q4
product                     
coffee   577   527  634  432
juice    331   553  375  295
tea      974  1106  923  993
product   coffee    juice      tea     Total
region                                      
North     9990.0  13300.0  12720.0   36010.0
South    11310.0  13050.0  14050.0   38410.0
West     11250.0  12500.0  13190.0   36940.0
Total    32550.0  38850.0  39960.0  111360.0

margins=True adds row and column totals. The juice row of the first table shows the Q2 peak at a glance; this is the value of the wide layout for people.

pd.crosstab is the special case of counting combinations of two categorical columns:

print(pd.crosstab(customers["city"], customers["plan"]))
plan     basic  premium
city                   
Chennai      1        1
Delhi        1        1
Kolkata      1        0
Mumbai       0        2
Pune         2        0

Wide to long: melt

melt reverses the process: it turns column headings into values of a new column. It is how untidy tables like Table 1.2 of Chapter 1 become tidy.

rain_wide = pd.DataFrame({"station": ["North", "South"], "Jan": [12, 30], "Feb": [8, 25], "Mar": [15, 41]})
rain_long = rain_wide.melt(id_vars="station", var_name="month", value_name="rainfall_mm")
print(rain_long)
print(rain_long.pivot(index="station", columns="month", values="rainfall_mm"))
  station month  rainfall_mm
0   North   Jan           12
1   South   Jan           30
2   North   Feb            8
3   South   Feb           25
4   North   Mar           15
5   South   Mar           41
month    Feb  Jan  Mar
station               
North      8   12   15
South     25   30   41

pivot is pivot_table without aggregation; it raises an error if two rows would fill the same cell. Notice that the columns come back in alphabetical order (Feb, Jan, Mar). If the order matters, make month an ordered categorical first, or reorder the columns with wide[["Jan", "Feb", "Mar"]].

5.10 Combining tables

Stacking: concat

pd.concat stacks tables with the same columns on top of each other (or side by side with axis=1). It is used to combine data that arrived in pieces, such as one file per month.

jan = pd.DataFrame({"day": [1, 2], "units": [30, 41]})
feb = pd.DataFrame({"day": [1, 2], "units": [28, 35]})
both = pd.concat([jan.assign(month="Jan"), feb.assign(month="Feb")], ignore_index=True)
print(both)
   day  units month
0    1     30   Jan
1    2     41   Jan
2    1     28   Feb
3    2     35   Feb

Joining: merge

Information about one thing is often spread over several tables: orders in one, customers in another, products in a third. merge joins two tables by matching the values of one or more key columns, like a join in SQL.

orders = pd.DataFrame({
    "order_id": [501, 502, 503, 504, 505],
    "customer": [2, 5, 2, 11, 9],
    "amount": [450.0, 1200.0, 300.0, 80.0, 650.0],
})
print(orders.merge(customers[["id", "city", "plan"]], left_on="customer", right_on="id", how="inner"))
   order_id  customer  amount  id     city     plan
0       501         2   450.0   2    Delhi  premium
1       502         5  1200.0   5   Mumbai  premium
2       503         2   300.0   2    Delhi  premium
3       505         9   650.0   9  Kolkata    basic

Order 504 is for customer 11, who is not in the customer table, so an inner join drops it: only rows with a match on both sides survive. The four kinds of join differ in what happens to rows without a match:

how= Keeps Unmatched rows from the other table
"inner" only keys present in both tables dropped
"left" every row of the left table filled with NaN
"right" every row of the right table filled with NaN
"outer" every key from either table filled with NaN on the missing side

Table 5.1. The four kinds of join in merge.

left = orders.merge(customers[["id", "city"]], left_on="customer", right_on="id",
                    how="left", indicator=True)
print(left)
print(left["_merge"].value_counts())
   order_id  customer  amount   id     city     _merge
0       501         2   450.0  2.0    Delhi       both
1       502         5  1200.0  5.0   Mumbai       both
2       503         2   300.0  2.0    Delhi       both
3       504        11    80.0  NaN      NaN  left_only
4       505         9   650.0  9.0  Kolkata       both
_merge
both          4
left_only     1
right_only    0
Name: count, dtype: int64

indicator=True adds a column _merge saying where each row came from (both, left_only or right_only). It is the quickest way to find keys that failed to match, such as the order for customer 11, which would vanish without trace in an inner join.

Caution: joins can multiply rows

If a key appears more than once on both sides, every combination is produced: three rows for customer 2 on the left and two on the right give six rows. This is right for some questions and a disaster for others (totals counted several times). Check the number of rows before and after every merge, and use validate="many_to_one" (or "one_to_one") to make pandas raise an error if the key is not unique on the side where it should be.

checked = orders.merge(customers[["id", "city"]], left_on="customer", right_on="id",
                       how="left", validate="many_to_one")
print(len(orders), "orders before,", len(checked), "rows after")
5 orders before, 5 rows after

5.11 Worked example: a cleaning function

Cleaning steps should be written once, as a function, and applied to the raw data whenever it is loaded. That way the raw data is never altered, every step is visible and can be checked, and a new export with the same problems is cleaned identically. Here is the whole of this chapter's cleaning as one function, applied to the original text:

def clean_customers(text):
    """Load and clean the raw customer export."""
    df = pd.read_csv(io.StringIO(text), na_values=["-"])
    n_raw = len(df)
    df = df.drop_duplicates()
    df["city"] = df["city"].str.strip().str.title()
    df["plan"] = df["plan"].str.lower()
    df["spend"] = pd.to_numeric(df["spend"].str.replace(",", ""), errors="coerce")
    df["joined"] = pd.to_datetime(df["joined"], format="%Y-%m-%d", errors="coerce")
    df.loc[(df["age"] < 0) | (df["age"] > 110), "age"] = np.nan
    report = {"raw rows": n_raw, "after dedup": len(df),
              "missing": df.isna().sum()[lambda s: s > 0].to_dict()}
    return df.reset_index(drop=True), report

clean, report = clean_customers(raw_text)
print(report)
print(clean.groupby("plan")["spend"].agg(["count", "median"]))
{'raw rows': 10, 'after dedup': 9, 'missing': {'age': 3, 'joined': 1, 'spend': 2}}
         count  median
plan                  
basic        3   640.0
premium      4  1040.0

The report travels with the data: 10 raw rows, 9 after removing the duplicate, three ages, one date and two spend values missing. The final line answers a real question (premium customers spend more than basic ones, at the median), and the count column shows on how few customers that rests.

5.12 Summary

Summary

  • Count missing values with isna().sum(); tell read_csv about extra markers with na_values. Drop, fill or flag them, and report the choice.
  • Find duplicates with duplicated (use subset= for key columns and keep=False to see whole groups); remove them with drop_duplicates.
  • Convert types with pd.to_numeric and pd.to_datetime; errors="coerce" turns bad values into missing ones, so count before and after.
  • Clean text with .str.strip(), .str.lower(), .str.title(), .str.replace(); map variants with a dictionary; store repeated labels as category.
  • Set impossible values to missing; look at outliers (the IQR rule flags them) but keep them unless they are errors.
  • Bin with pd.cut (your edges) and pd.qcut (equal counts).
  • groupby splits, applies and combines: agg gives one row per group, transform one value per original row.
  • pivot_table and crosstab make wide summary tables; melt makes long, tidy ones.
  • concat stacks tables; merge joins them on keys. Know your join (inner, left, right, outer), use indicator=True to find unmatched keys, and validate= to catch duplicated keys.
  • Put cleaning steps in a function that starts from the raw data.

5.13 Exercises

Exercise 1

The column rating holds the strings "4", "5", "n/a", "3", "", "five". Convert it to numbers, with anything unusable as missing. How many missing values result?

Answer
rating = pd.Series(["4", "5", "n/a", "3", "", "five"])
r = pd.to_numeric(rating, errors="coerce")
print(r.tolist(), r.isna().sum())
[4.0, 5.0, nan, 3.0, nan, nan] 3

Three: "n/a", the empty string and "five". (If "five" matters, map it to 5 before converting.)

Exercise 2

Using sales, find the quarter with the highest total revenue across all regions and products.

Answer
by_q = sales.groupby("quarter")["revenue"].sum()
print(by_q)
print(by_q.idxmax())
quarter
Q1    26670.0
Q2    32790.0
Q3    28115.0
Q4    23785.0
Name: revenue, dtype: float64
Q2

Exercise 3

Add a column to sales giving each row's units minus the average units for its product (over all regions and quarters). Which row is furthest above its product's average?

Answer
sales["vs_product_mean"] = sales["units"] - sales.groupby("product")["units"].transform("mean")
print(sales.loc[sales["vs_product_mean"].idxmax(), ["region", "product", "quarter", "units", "vs_product_mean"]])
region             North
product            juice
quarter               Q2
units                215
vs_product_mean     85.5
Name: 9, dtype: object

transform("mean") repeats each product's mean on every row of that product, so the subtraction is row by row.

Exercise 4

Table A has 100 orders with a customer_id; table B has one row per customer, but 3 customers who placed orders are missing from it. How many rows do an inner join and a left join (A on the left) give, if those 3 customers placed 5 orders between them?

Answer

The inner join gives 95 rows (the 5 orders without a matching customer are dropped). The left join gives 100 rows, with NaN in B's columns for the 5 unmatched orders. Both assume customer_id is unique in B; otherwise rows would multiply.

Exercise 5

Turn this wide table of marks into a long one with columns student, subject, mark, and then compute the mean mark per subject.

pd.DataFrame({"student": ["A", "B", "C"], "maths": [70, 55, 90], "physics": [65, 60, 85]})

Answer
marks = pd.DataFrame({"student": ["A", "B", "C"], "maths": [70, 55, 90], "physics": [65, 60, 85]})
long = marks.melt(id_vars="student", var_name="subject", value_name="mark")
print(long)
print(long.groupby("subject")["mark"].mean().round(2))
  student  subject  mark
0       A    maths    70
1       B    maths    55
2       C    maths    90
3       A  physics    65
4       B  physics    60
5       C  physics    85
subject
maths      71.67
physics    70.00
Name: mark, dtype: float64

Exercise 6

Why is replacing missing ages with the mean age a questionable choice for a study of how spending varies with age?

Answer

It puts all the customers with unknown age at one value, the mean, which creates an artificial cluster there and weakens any real relationship between age and spending. It also understates the spread of ages. Dropping those customers (if few), or flagging them and checking whether they differ in spending, is more defensible; whichever is chosen should be reported.

5.14 Quiz

Check your understanding of Chapter 5

Exercise 1

Which call turns unconvertible strings into missing values?

  1. pd.to_numeric(s)
  2. pd.to_numeric(s, errors="coerce")
  3. s.astype(float)
  4. s.fillna(0)
Answer

pd.to_numeric(s, errors="coerce")

Exercise 2

Which groupby method returns one value for every original row?

  1. agg
  2. transform
  3. filter
  4. size
Answer

transform

Exercise 3

Which join keeps every row of the left table, matched or not?

  1. inner
  2. left
  3. right
  4. cross
Answer

left

Exercise 4

Which function turns column headings into values of a new column (wide to long)?

  1. pivot_table
  2. melt
  3. crosstab
  4. concat
Answer

melt

Exercise 5

With Q1 = 10 and Q3 = 30, above which value does the IQR rule flag an outlier?

  1. 40
  2. 45
  3. 60
  4. 70
Answer

60

IQR = 20, so the upper fence is 30 + 1.5 x 20 = 60.

Exercise 6

Which keyword argument of merge adds a column showing where each row came from?

Answer

indicator / indicator=True

Exercise 7

An age of 230 in a customer table is best described as

  1. an outlier to keep
  2. an impossible value to set to missing
  3. a duplicate
  4. a category
Answer

an impossible value to set to missing

Exercise 8

What does drop_duplicates(subset=["id"]) compare?

  1. all columns
  2. only the id column
  3. only the index
  4. nothing
Answer

only the id column