13 - Pandas: Data Tables Without Surprises

Machine Learning Engineer course · Module 01 · Python for data · Lesson 6

You have an order log and a customer register, and one line is enough to get the total per city. If you do not know what happens underneath, that line can hand you a wrong total without a single message: a customer missing from the register vanishes from the report, a duplicated key inflates the numbers, a date read backwards moves an order from April to March. This lesson teaches you to read, select, clean, aggregate and join tables with Pandas knowing what happens underneath: the index that aligns by label, the column types, the missing values that propagate, groupby as a three-step procedure and the merge that can multiply rows.

The common wisdom says Pandas is Excel inside Python. In a spreadsheet every cell is free; in a DataFrame each column has a single type and operations align by label, not by position. Anyone who thinks "in cells" produces errors that raise no exception at all. Pandas builds its tables on top of the arrays of the NumPy lesson, adding names, mixed types and holes. The examples use synthetic data, declared as such, generated in the code, and were run with Python 3.14.4, NumPy 2.3.5 and pandas 3.0.3, the version that changed some important behaviours (we flag them where they matter).

The listings form a single session: the first one, which creates ordini (orders) and clienti (customers), must be run first, and the following ones reuse it (the blocks on pivot_table and on category rebuild their own data). Run in the order in which they appear, twice, they gave the same outputs you read here. The identifiers stay in Italian (ordini, clienti, importo = amount, citta = city, segmento = segment, privato = private customer, azienda = business customer) because the text refers to them by name; comments and printed messages are in English.

Series and DataFrame: arrays with labels

The analogy: the ledger with names on the rows

A NumPy array is a ledger whose rows are numbered 0, 1, 2... A Series is the same ledger, but every row has a name (the index): "Anna", "Luca", "2026-01-15". A DataFrame is a cabinet of ledgers sharing the same row names: one Series per column, all with the same index.

The most important consequence is alignment: when you operate on two Series, Pandas pairs the rows that have the same label, not the ones in the same position.

import numpy as np
import pandas as pd

s1 = pd.Series([1, 2, 3], index=["a", "b", "c"])
s2 = pd.Series([10, 20, 30], index=["b", "c", "d"])
print(s1 + s2)

Output obtained:

a     NaN
b    12.0
c    23.0
d     NaN
dtype: float64

a exists only in s1 and d only in s2: where one of the two operands is missing the result is NaN (not a number, the marker for missing values). On b and c the sum is made between the rows with that name, not between the second and the second. This behaviour is a convenience when the indices carry meaning (dates, identifiers) and a trap when the index is a running number that has been "dirtied" after a filter.

Creating, inspecting, understanding types

Let us build two synthetic tables: an order log and a customer register. Customer 5 appears in the orders but not in the register (deliberately incomplete data, as in real life).

rng = np.random.default_rng(7)

clienti = pd.DataFrame({
    "id_cliente": [1, 2, 3, 4],
    "citta": ["Lecce", "Milano", "Lecce", "Torino"],
    "segmento": ["privato", "azienda", "privato", "azienda"],
})

n = 12
ordini = pd.DataFrame({
    "id_ordine": range(100, 100 + n),
    "id_cliente": rng.choice([1, 2, 3, 4, 5], n),
    "importo": np.round(rng.gamma(shape=4, scale=30, size=n), 2),
    "data": pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 120, n), unit="D"),
})
ordini.loc[[2, 7], "importo"] = np.nan      # two missing amounts

print(ordini)
print(ordini.dtypes)
print(clienti.dtypes)

Output obtained:

    id_ordine  id_cliente  importo       data
0         100           5   113.49 2026-04-27
1         101           4    84.08 2026-03-25
2         102           4      NaN 2026-04-16
3         103           5   116.17 2026-01-25
4         104           3   108.33 2026-03-28
5         105           4    49.44 2026-02-14
6         106           5    34.50 2026-02-28
7         107           2      NaN 2026-01-01
8         108           1   119.25 2026-03-16
9         109           2   107.24 2026-04-10
10        110           2    43.43 2026-03-21
11        111           5   118.65 2026-01-19
id_ordine              int64
id_cliente             int64
importo              float64
data          datetime64[us]
dtype: object
id_cliente    int64
citta           str
segmento        str
dtype: object

How to read the code. clienti is a DataFrame built from a dictionary: the keys become the column names, the lists the values; all the lists have the same length (4). ordini is built the same way but with columns generated with NumPy: rng.choice([1, 2, 3, 4, 5], n) draws the customer for each order (5 does not exist in the register: it is the incomplete data we were talking about), rng.gamma(shape=4, scale=30, size=n) produces positive amounts with a right tail (a plausible distribution for spending) and np.round(..., 2) brings them to the cent. The data (date) column adds a random number of days to a starting date (pd.to_timedelta(..., unit="D") converts numbers into durations). Finally ordini.loc[[2, 7], "importo"] = np.nan sets the amount of the rows labelled 2 and 7 to missing: from here on the table has two holes.

Remember that loc[rows, column] with a single bracket is the correct way to assign (we come back to it with Copy-on-Write).

A quick inspection, to be done always as soon as a table is loaded:

• df.shape: rows and columns.
• df.dtypes and df.info(): the type of each column and the count of non-null values.
• df.head(), df.sample(5): a look at the real data.
• df.describe(): summary statistics of the numeric columns.

Types matter. A column of integers that contains a hole becomes float64, because NaN is a float. If you want to keep integers with missing values there are the nullable types ("Int64" with a capital I):

print(pd.Series([1, 2, None]).dtype)
print(pd.Series([1, 2, None], dtype="Int64").tolist())
print(ordini["importo"].describe().round(2))

Output obtained:

float64
[1, 2, <NA>]
count     10.00
mean      89.46
std       34.11
min       34.50
25%       58.10
50%      107.78
75%      115.50
max      119.25
Name: importo, dtype: float64

In Pandas 3 text columns have by default the str type (a true string) in place of the old object, which is more generic and slower, as the pandas 3.0 release notes describe: you can see it in the output of clienti.dtypes.

Trade-off. The category types (few repeated labels, like the city) save memory and speed up groupings; the nullable types preserve integers but require getting used to pd.NA. If the dataset is small, the defaults are perfectly fine.

Selecting: the four doors

The difference between loc and iloc is the most important thing to keep in mind. An analogy: loc is "give me the row headed Rossi", iloc is "give me the third row from the top". If the index is a running number 0, 1, 2, ... the two coincide, until you filter: after a filter the index keeps the old numbers.

print(ordini.loc[0:2, ["id_ordine", "importo"]])     # labels 0, 1, 2: end INCLUDED
print(ordini.iloc[0:2, [0, 2]])                      # positions 0 and 1: end EXCLUDED

grandi = ordini[ordini["importo"] > 100]
print(grandi[["id_ordine", "importo"]])
print("index after the filter:", grandi.index.tolist())
print("grandi.loc[3]:", grandi.loc[3, "id_ordine"], "| grandi.iloc[3]:", grandi.iloc[3]["id_ordine"])

Output obtained:

   id_ordine  importo
0        100   113.49
1        101    84.08
2        102      NaN
   id_ordine  importo
0        100   113.49
1        101    84.08
    id_ordine  importo
0         100   113.49
3         103   116.17
4         104   108.33
8         108   119.25
9         109   107.24
11        111   118.65
index after the filter: [0, 3, 4, 8, 9, 11]
grandi.loc[3]: 103 | grandi.iloc[3]: 108

After the filter the index keeps the old labels of the surviving rows, [0, 3, 4, 8, 9, 11]: loc[3] looks for the label 3 (order 103), while iloc[3] takes the fourth remaining row (order 108). Same bracket, different rows: you have just discovered why it is better not to mix them.

Combining conditions

In Python and, or, not reason about one truth value; on a whole column you need the bitwise operator, with parentheses (because of operator precedence):

cond = (ordini["importo"] > 50) & (ordini["id_cliente"] != 5)
print(ordini[cond]["id_ordine"].tolist())
print(ordini[~ordini["importo"].isna()].shape[0], "orders with a known amount")
print(ordini.query("importo > 100 and id_cliente == 5")["id_ordine"].tolist())

Output obtained:

[101, 104, 108, 109]
10 orders with a known amount
[100, 103, 111]

Writing ordini["importo"] > 50 and ... raises ValueError: The truth value of a Series is ambiguous: Pandas refuses to decide for you whether a whole column is "true". query accepts the and/or syntax because it interprets a string.

Missing values

A missing value is not "zero" and it is not "nothing": it is a piece of unknown information. Three questions come before any remedy.

1. How many are there and where? df.isna().sum() counts per column.
2. Why are they missing? Rubin (1976) distinguishes three cases. Missing completely at random (the sensor switched off because of a random fault), missing at random given what I observe (young customers leave the income field empty more often), missing not at random (people who earn a lot omit their income precisely because it is high). In the third case no automatic substitution is innocent.
3. What is the effect of ignoring them? It depends on the question.

The order matters: first you count, then you look for the mechanism, only at the end do you choose a remedy. Whoever starts from the remedy (usually fillna) decides without knowing whether the holes are few or many and whether they have a reason. The following block answers the first and the third question; the second requires knowing how the data were collected, and no line of code settles it.

print(ordini.isna().sum())
print("sum ignoring NaN  :", ordini["importo"].sum())
print("sum NOT ignoring them:", ordini["importo"].sum(skipna=False))
print("mean with NaN ignored:", round(ordini["importo"].mean(), 2))

riempito_media = ordini["importo"].fillna(ordini["importo"].mean())
print("mean after fillna(mean):", round(riempito_media.mean(), 2), "(identical: the mean cannot change)")
print("std deviation before/after:", round(ordini["importo"].std(), 2), round(riempito_media.std(), 2))

Output obtained:

id_ordine     0
id_cliente    0
importo       2
data          0
dtype: int64
sum ignoring NaN  : 894.5799999999999
sum NOT ignoring them: nan
mean with NaN ignored: 89.46
mean after fillna(mean): 89.46 (identical: the mean cannot change)
std deviation before/after: 34.11 30.86

The last line shows the side effect of the most popular remedy: filling with the mean leaves the mean unchanged but reduces the variability (you are inserting values that are "all equal, all at the centre"). A model trained on those data will see less uncertainty than really exists. Moreover, if you compute that mean on the whole dataset before separating train and test, you leak information from the test set into the training: the same defect we will see with data leakage in the scikit-learn lesson. The rule: the mean (or any filling statistic) is computed only on the training set and applied to the test set.

Alternatives: dropping the rows (dropna, harmless only if the missing values are few and random), adding an indicator column "was missing" (sometimes being missing is itself informative), using a model that handles holes natively.

groupby: split, apply, combine

The analogy: sorting the mail

You have a pile of letters and you want to know how much you spent per city. First you sort the letters into small heaps, one per city (split). Then for each heap you do a calculation (apply). Finally you collect the results on a single sheet (combine). It is the split-apply-combine strategy, formalised by Wickham (2011), and groupby runs it in one line.

First we need to join the orders with the register (next section); let us anticipate the result:

m = ordini.merge(clienti, on="id_cliente", how="left", validate="m:1", indicator=True)
print(m[["id_ordine", "id_cliente", "citta", "_merge"]])

Output obtained:

    id_ordine  id_cliente   citta     _merge
0         100           5     NaN  left_only
1         101           4  Torino       both
2         102           4  Torino       both
3         103           5     NaN  left_only
4         104           3   Lecce       both
5         105           4  Torino       both
6         106           5     NaN  left_only
7         107           2  Milano       both
8         108           1   Lecce       both
9         109           2  Milano       both
10        110           2  Milano       both
11        111           5     NaN  left_only

Four orders (from customer 5) have no city: _merge says left_only, and Pandas puts NaN in place of the city. The left kept all the rows of the orders, and validate="m:1" guaranteed that none was duplicated: twelve rows before, twelve after. Now let us group by city, counting rows and known values in two different ways and computing the total and the mean of the amounts:

tab = m.groupby("citta", dropna=False).agg(
    n_ordini=("id_ordine", "size"),
    n_importi=("importo", "count"),
    totale=("importo", "sum"),
    medio=("importo", "mean"),
)
print(tab.round(2))

Output obtained:

        n_ordini  n_importi  totale   medio
citta                                      
Lecce          2          2  227.58  113.79
Milano         3          2  150.67   75.33
Torino         3          2  133.52   66.76
NaN            4          4  382.81   95.70

Note two details that make reports go wrong:

• size versus count. size counts the rows of the group; count counts the non-missing values. For Milano the orders are 3 but the known amounts are 2: the mean is computed on 2, not on 3.
• dropna=False. By default groupby silently discards (as the groupby guide says) the group whose key is NaN: those 4 orders would vanish from the total. With dropna=False they appear as a group of their own, and the grand total adds up.

Both details have the same nature: groupby silently chooses what to count and what to discard, and the report that comes out looks tidy and complete. The check is arithmetical: the sum of the groups must coincide with the total (here 227.58 + 150.67 + 133.52 + 382.81 = 894.58, verified), and the counts with the rows of the starting table. As with the floats of the floating-point lesson, the comparison between sums must be made with a tolerance.

A statistical trap: the mean of the means

media_delle_medie = tab["medio"].mean()
media_globale = m["importo"].mean()
print("mean of the per-city means:", round(media_delle_medie, 2))
print("global mean of the amounts:", round(media_globale, 2))

Output obtained:

mean of the per-city means: 87.9
global mean of the amounts: 89.46

The two numbers are different: the mean of the means gives the same weight to groups of different size. It is a relative of Simpson's paradox: aggregating at different levels changes the answer. Which number is the right one depends on the question (the average spend per order or the average spend per city?), but you must know it and say it.

transform: one result for every row

agg reduces (one value per group). transform returns a series of the same length as the original: excellent for creating features, such as "share of the order in the customer's total".

m["tot_cliente"] = m.groupby("id_cliente")["importo"].transform("sum")
m["quota"] = (m["importo"] / m["tot_cliente"]).round(3)
print(m.loc[m["id_cliente"] == 5, ["id_ordine", "importo", "tot_cliente", "quota"]])

Output obtained:

    id_ordine  importo  tot_cliente  quota
0         100   113.49       382.81  0.296
3         103   116.17       382.81  0.303
6         106    34.50       382.81  0.090
11        111   118.65       382.81  0.310

merge: joining tables and the danger of duplicates

merge does the equivalent of the SQL JOIN. The main types:

The big risk is not the type of join, it is the cardinality. If the key on the right is not unique, every row on the left is duplicated for every match, and the totals swell without any warning.

clienti_sporca = pd.concat([clienti, clienti.iloc[[0]]], ignore_index=True)   # customer 1 is duplicated
print("rows before:", len(ordini))

gonfiato = ordini.merge(clienti_sporca, on="id_cliente", how="left")
print("rows after (without validate):", len(gonfiato))
print("true total   :", round(ordini["importo"].sum(), 2))
print("inflated total:", round(gonfiato["importo"].sum(), 2))

try:
    ordini.merge(clienti_sporca, on="id_cliente", how="left", validate="m:1")
except Exception as e:
    print("with validate='m:1':", type(e).__name__)

Output obtained:

rows before: 12
rows after (without validate): 13
true total   : 894.58
inflated total: 1013.83
with validate='m:1': MergeError

The validate="m:1" parameter (many-to-one: the key on the right must be unique; the merge guide describes it as a check on the uniqueness of the keys) turns a silent error into an exception. It is the insurance that costs one word and should appear in almost every merge of a pipeline. indicator=True helps too: the _merge column says where each row comes from.

Rule of thumb. Before and after a merge, check the number of rows. If it changes and you did not expect it, you have run into duplicates.

Dates and time

The dates read from a file are almost always strings. pd.to_datetime converts them into datetime64, and the .dt accessor exposes the parts.

ordini["mese"] = ordini["data"].dt.month
ordini["giorno_sett"] = ordini["data"].dt.day_name()
per_mese = ordini.groupby("mese")["importo"].agg(["count", "sum"]).round(2)
print(per_mese)

print(pd.to_datetime("03/04/2026", dayfirst=True).date(), pd.to_datetime("03/04/2026", dayfirst=False).date())

Output obtained:

      count     sum
mese               
1         2  234.82
2         2   83.94
3         4  355.09
4         2  220.73
2026-04-03 2026-03-04

The last line is a classic: is 03/04/2026 the 3rd of April or the 4th of March? It depends on the convention. Always declare the format (format="%d/%m/%Y") when you read dates from human sources: the ambiguity affects every date with a day from 1 to 12, and the wrong reading produces no error at all.

Summarising in two dimensions: pivot_table and crosstab

When the groupings are two, a double-entry table is convenient: rows for one criterion, columns for the other. pivot_table builds it with an aggregation of your choice; crosstab counts the combinations. Here we cross the customer segment with the month of the order, giving the name "unknown" to the orders without a register entry.

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
clienti = pd.DataFrame({
    "id_cliente": [1, 2, 3, 4],
    "citta": ["Lecce", "Milano", "Lecce", "Torino"],
    "segmento": ["privato", "azienda", "privato", "azienda"],
})
n = 12
ordini = pd.DataFrame({
    "id_ordine": range(100, 100 + n),
    "id_cliente": rng.choice([1, 2, 3, 4, 5], n),
    "importo": np.round(rng.gamma(shape=4, scale=30, size=n), 2),
    "data": pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 120, n), unit="D"),
})
ordini.loc[[2, 7], "importo"] = np.nan

m = ordini.merge(clienti, on="id_cliente", how="left", validate="m:1")
m["segmento"] = m["segmento"].fillna("unknown")
m["mese"] = m["data"].dt.month

print(m.pivot_table(index="segmento", columns="mese", values="importo", aggfunc="sum", fill_value=0).round(1))
print()
print(pd.crosstab(m["segmento"], m["mese"]))
print()
print(m.set_index("data")["importo"].resample("MS").agg(["count", "sum"]).round(2))
print()
m["rango_nel_cliente"] = m.groupby("id_cliente")["importo"].rank(ascending=False, method="min")
print(m.loc[m["id_cliente"] == 5, ["id_ordine", "importo", "rango_nel_cliente"]])

Output obtained:

mese          1     2      3      4
segmento                           
azienda     0.0  49.4  127.5  107.2
privato     0.0   0.0  227.6    0.0
unknown   234.8  34.5    0.0  113.5

mese      1  2  3  4
segmento            
azienda   1  1  2  2
privato   0  0  2  0
unknown   2  1  0  1

            count     sum
data                     
2026-01-01      2  234.82
2026-02-01      2   83.94
2026-03-01      4  355.09
2026-04-01      2  220.73

    id_ordine  importo  rango_nel_cliente
0         100   113.49                3.0
3         103   116.17                2.0
6         106    34.50                4.0
11        111   118.65                1.0

How to read the code. The pivot_table has the segments on the rows, the months on the columns and the sum of the amounts in the cells (fill_value=0 puts zero where there is no order; careful: a zero here means "no order", not "an order of zero euros"). The crosstab counts the orders per combination: comparing it with the previous table you understand that the zeros are empty cells, and you see that the "unknown" segment has orders in three months out of four. The segments are sorted alphabetically, so "unknown" now comes last.

The third block uses resample("MS"), which groups a date-indexed column into regular intervals (here Month Start, one group per month) and applies the count and sum aggregations: it is groupby for time series. The last block solves the mini-project request on the rank: inside each group (id_cliente) it sorts the amounts from the largest and assigns the position (rank); missing values stay NaN and receive no rank. Customer 5 has four orders and, among those with a known amount, the largest has rank 1.

A second, contrasting example: the category type versus text

Text columns with few distinct values and many rows (the city, the segment, the country) take up a lot of space if every row keeps its own string. The category type stores the list of distinct values once and, for every row, only a small integer code (like the drawer numbers of a hash table, seen in lists, tuples, dictionaries and sets).

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
grande = pd.DataFrame({"citta": rng.choice(["Lecce", "Milano", "Torino", "Roma", "Napoli"], 200_000)})

come_testo = grande.memory_usage(deep=True)["citta"]
come_categoria = grande.astype({"citta": "category"}).memory_usage(deep=True)["citta"]
print("starting type:", grande["citta"].dtype)
print(f"memory as text     : {come_testo:>9,} bytes")
print(f"memory as category : {come_categoria:>9,} bytes")
print(f"ratio              : {come_testo / come_categoria:.1f} times")
print("distinct values stored only once:", list(grande.astype({"citta": "category"})["citta"].cat.categories))

Output obtained:

starting type: str
memory as text     : 2,679,698 bytes
memory as category :   200,068 bytes
ratio              : 13.4 times
distinct values stored only once: ['Lecce', 'Milano', 'Napoli', 'Roma', 'Torino']

How to read the code. The DataFrame has 200,000 rows with only five possible values. memory_usage(deep=True) measures the bytes occupied including the content of the strings; astype({"citta": "category"}) converts the column. The saving is an order of magnitude (about 13 times), and groupby on a category column is also faster, because it groups by integer codes. Trade-off: category makes sense when the distinct values are few compared with the rows; with a column where every value is almost unique (an identifier, an email address) it saves nothing and complicates the operations. Also, new categories must be declared: assigning an unexpected value to a category column raises an error.

Copy-on-Write and the end of chained assignment

Up to Pandas 2, writing df[df.a > 1]["b"] = 9 might or might not modify df, depending on whether the intermediate operation returned a view or a copy: unpredictable behaviour, flagged by the infamous SettingWithCopyWarning. From Pandas 3 the Copy-on-Write rule applies: any result of an operation behaves like an independent copy, and modifying it never touches the original. Chained assignment therefore no longer has any effect (and Pandas flags it with a warning).

import warnings

x = pd.DataFrame({"a": [1, 2, 3], "b": [0, 0, 0]})
with warnings.catch_warnings(record=True) as avvisi:
    warnings.simplefilter("always")
    x[x["a"] > 1]["b"] = 9           # CHAINED assignment: modifies a temporary copy
    print("after the chain:", x["b"].tolist(), "| warning:", [a.category.__name__ for a in avvisi])

x.loc[x["a"] > 1, "b"] = 9           # a single step, with loc: modifies x
print("after loc      :", x["b"].tolist())

Output obtained:

after the chain: [0, 0, 0] | warning: ['ChainedAssignmentError']
after loc      : [0, 9, 9]

The rule to remember: to modify a subset, use a single loc[rows, column] = value. The form with two brackets in a row creates a temporary copy and writes to that.

Typical errors (symptom, cause, remedy)

• A total that "does not add up" after a merge. Cause: a non-unique key that duplicates rows. Remedy: validate="m:1" and a check of the number of rows before/after.
• A group vanishes from the report. Cause: a NaN key discarded by groupby. Remedy: dropna=False and a check that the sum of the groups coincides with the total.
• NaN polluting the means. Cause: comparison or sum with missing values. Remedy: know skipna (see the missing-data guide), decide an explicit strategy, never fillna(0) by reflex (a zero is a value, not an absence).
• and/or on columns. Symptom: ValueError about the ambiguous truth value. Remedy: &, |, ~ with parentheses, or query.
• loc and iloc confused after a filter. Remedy: use reset_index(drop=True) when the index has no meaning, or always reason in labels.
• Dates read backwards. Remedy: an explicit format=.
• A for loop over the rows. Symptom: slowness (same problem as in the NumPy lesson). Remedy: operations on columns, groupby, merge.

The first two rows and the fourth have the same look: a report that seems correct and has a wrong number. That is why the defence is not in cleverer code but in the arithmetic check downstream (sum of the groups equal to the total, rows before and after). The last two are errors of habit and show up immediately: an exception or an execution time that explodes.

Best practices

• Inspect before computing: shape, dtypes, isna().sum(), head().
• Check the cardinalities of every merge and every groupby (rows before and after; sum of the groups equal to the total).
• One operation per line of code, with column names that make sense: a method chain is elegant if it stays readable.
• Do not modify the original: create new columns with assign or on a copy, and keep the raw data.
• Document how you treat the missing values: it is an analysis decision, and whoever reads must be able to challenge it.
• Fix the types on entry (dtype=, parse_dates=) when you read files.

These are six habits with a single reason: Pandas gives you an answer even when the question was wrong. Inspecting, checking the cardinalities and fixing the types move the error from the moment the report is already in the meeting to the moment you load the data, which is the only one at which fixing it costs little.

Mini-project

With the ordini and clienti tables of this lesson:

1. Compute the monthly revenue per segment (privato/azienda), including the orders without a register entry as the segment "unknown".
2. Add the column rango_nel_cliente: the position of the order by decreasing amount within each customer (hint: groupby + rank).
3. Verify with an assert that the sum of the revenue per segment coincides with the total of the known amounts.
4. Write two lines on why, in this synthetic dataset, you would not do fillna(0) on the amounts.

Points 1 and 2 are solved, in a different form, in the block on pivot_table and rank above: try first on your own and then compare. Point 3 is the check discussed in the section on typical errors, point 4 asks for a written justification, because a decision on missing data that cannot be challenged is not an analysis.

Challenges

• Easy. Find the order with the maximum amount and the customer who placed it, without loops (idxmax).
• Medium. For each customer compute the days elapsed between two consecutive orders (sort_values, groupby, diff).
• Hard. Build an example of a merge in which the duplicated key on the right doubles the total, then fix it in two ways (deduplicate first, or aggregate on the right) and explain when you prefer one or the other.
• Expert. Write a function controlla_merge(sinistra, destra, chiave) that returns a summary (left rows, keys not found, duplicated keys on the right, rows after the merge) and raises an exception if the cardinality is not the expected one.
• Enterprise. Take a real export from your organisation. List for each column: type, percentage of missing values, a plausible hypothesis on why they are missing (completely at random, conditionally at random, not at random), and the strategy you would adopt.

The levels climb from the single command (Easy) to the reusable checking function (Expert) up to an analysis on your own data (Enterprise). For Hard what counts is the comparison between the two fixes: deduplicating loses information on the right-hand side, aggregating keeps it but requires deciding how, and it is this choice, not the code, that you have to justify.

What you can do now

You can predict what Pandas will do when you operate on two objects with different indices, choose between loc and iloc knowing that after a filter the two diverge, and treat missing values as an analysis decision and not as a nuisance. You can build a groupby that does not lose the group with the missing key, check a merge with validate and with the row count, and modify a subset with a single loc[...] = ... under Copy-on-Write. The next step is visualisation with Matplotlib: summary numbers hide a lot, and a chart is the first way to notice it.

The same series also includes: 10 - Python for Data 3: Functions, Files, Exceptions, Objects, 16 - Sets and Logic: the Language of Mathematics (which refer back to this lesson).

Sources

Bibliographic references cited in the text, not consulted online in this revision: D. B. Rubin, Inference and Missing Data, Biometrika 63(3), 581-592, 1976; H. Wickham, The Split-Apply-Combine Strategy for Data Analysis, Journal of Statistical Software 40(1), 2011. All the outputs were recomputed with Python 3.14.4, NumPy 2.3.5 and pandas 3.0.3.