Pandas: The Eighty Per Cent

In the real world you are never handed a tidy CSV. The gap between the neat example and what actually lands on your desk is where most of a working data scientist’s time goes.

I do not think that is a failure of the tooling. It is the job. This chapter covers the parts of pandas that matter for real data, and it spends as much space on the ways things go silently wrong as on the API itself.

Two Objects, One Idea

A Series is a one-dimensional NumPy array with labels attached. A DataFrame is a dictionary of Series that all share the same labels.

import pandas as pd
import numpy as np

s = pd.Series([10, 20, 30], index=['a', 'b', 'c'])
# a    10
# b    20
# c    30

df = pd.DataFrame({
    'city': ['Delhi', 'Tokyo', 'Sydney'],
    'population_m': [33.8, 37.1, 5.3],
    'region': ['Asia', 'Asia', 'Oceania'],
})

Those labels are the index, and they are the single most important thing to understand about pandas. Almost every confusing behaviour traces back to the index doing something you did not ask for.

flowchart LR
    subgraph DF["DataFrame"]
        direction TB
        IDX["Index<br/>0 · 1 · 2<br/><i>shared row labels</i>"]
        C1["Series: city<br/>object"]
        C2["Series: population_m<br/>float64"]
        C3["Series: region<br/>object"]
    end
    IDX -.aligns.-> C1
    IDX -.aligns.-> C2
    IDX -.aligns.-> C3
    C1 --> NP1["NumPy array"]
    C2 --> NP2["NumPy array"]
    C3 --> NP3["NumPy array"]
    style IDX fill:#eef1fc,stroke:#2141c8,stroke-width:2px

The index is why this happens:

a = pd.Series([1, 2, 3], index=['x', 'y', 'z'])
b = pd.Series([10, 20, 30], index=['z', 'y', 'x'])

a + b
# x    31    ← 1 + 30, matched by LABEL not position
# y    22
# z    13

NumPy would have added these positionally. Pandas aligns on the index first. This is a feature and it saves you from a category of silent errors, but it means an operation that looks positional is not.

Selecting: Three Ways, Two of Which You Should Use

df['city']            # a column, as a Series
df[['city', 'region']]  # several columns, as a DataFrame
df[df.region == 'Asia']    # rows matching a condition

df.loc[0, 'city']     # by LABEL   → 'Delhi'
df.iloc[0, 0]         # by POSITION → 'Delhi'

The rule: loc is labels, iloc is integer positions. When the index happens to be 0, 1, 2 they look identical, which is exactly why people conflate them and then get burned the moment the index is anything else.

d = df.set_index('city')
d.loc['Delhi']        # works — 'Delhi' is a label
d.iloc[0]             # works — position 0
d.loc[0]              # KeyError — there is no label 0

One more difference that matters: loc slices are inclusive of the endpoint, iloc slices are not, matching Python everywhere else.

df.loc[0:2]    # three rows — 0, 1 and 2
df.iloc[0:2]   # two rows   — 0 and 1

The chained assignment trap

This is the most common pandas bug in existence.

# Wrong. May silently do nothing.
df[df.region == 'Asia']['population_m'] = 0

# Correct.
df.loc[df.region == 'Asia', 'population_m'] = 0

The first version calls __getitem__ twice. The intermediate result may be a copy, in which case you assign into a temporary object that is immediately discarded, and your original data is untouched. Older pandas raised a SettingWithCopyWarning about this; pandas 3 makes the behaviour consistent through copy-on-write. Either way, do the selection and the assignment in one .loc call and the problem never arises.

dtypes, and Why Your Column Is object

An object column means pandas gave up and stored Python objects with a pointer each. It is slow, it uses far more memory, and it usually indicates something went wrong on load.

df.dtypes
df.info(memory_usage='deep')   # the honest memory figure

dtype	What it means	Watch for
int64 / float64	Numeric, NumPy-backed	One missing value turns int into float
object	Python objects, usually strings	Slow, memory-hungry, often a parsing failure
string	Proper nullable string type	Prefer this over object for text
category	Repeated values stored as integer codes	Enormous savings on low-cardinality columns
datetime64[ns]	Real timestamps	Parse explicitly, never leave as strings
bool	True/False	Cannot hold missing values; use boolean
Int64	Nullable integer, capital I	The fix for integers with gaps

Two conversions pay for themselves immediately:

# Low-cardinality strings: often a 10-50x memory reduction
df['region'] = df['region'].astype('category')

# Integers that have missing values, without falling back to float
df['count'] = df['count'].astype('Int64')

Missing Data

Pandas has more than one kind of missing, which is genuinely annoying.

np.nan     # float NaN, the historical default
None       # Python's null, appears in object columns
pd.NaT     # missing timestamp
pd.NA      # the modern, dtype-agnostic missing value

Test for all of them with .isna(), never with ==:

df.isna().sum()                    # count of missing per column
df.isna().mean().sort_values()     # proportion missing — more useful

df.dropna()                        # drop rows with ANY missing
df.dropna(subset=['population_m']) # drop only where this column is missing
df.fillna({'population_m': 0})     # fill per column, deliberately

Resist df.fillna(0) across a whole frame. Zero is a real value in most columns and you have just fabricated data. Decide per column what a gap means, and consider whether “missing” is itself informative. A blank income field on a loan application is frequently the most predictive thing on the form.

Group By: Split, Apply, Combine

This is the operation you will reach for most, and it has exactly three phases.

flowchart TB
    A["DataFrame<br/>city · region · population_m"]
    A -->|"SPLIT<br/>groupby('region')"| B1["region = Asia<br/>Delhi, Tokyo"]
    A --> B2["region = Oceania<br/>Sydney"]
    B1 -->|"APPLY<br/>.mean()"| C1["35.45"]
    B2 -->|"APPLY"| C2["5.30"]
    C1 -->|"COMBINE"| D["Result indexed by region<br/>Asia → 35.45<br/>Oceania → 5.30"]
    C2 --> D
    style A fill:#f0efe9,stroke:#5c6670
    style D fill:#eef1fc,stroke:#2141c8

df.groupby('region')['population_m'].mean()

# Several statistics at once
df.groupby('region')['population_m'].agg(['mean', 'median', 'count'])

# Different functions per column, with names you choose
df.groupby('region').agg(
    avg_pop=('population_m', 'mean'),
    n_cities=('city', 'count'),
)

The named-aggregation form in that last example is worth adopting as a default. It produces flat, readable column names instead of a MultiIndex you then have to flatten.

transform versus agg

The distinction that unlocks a lot of work:

• agg returns one row per group.
• transform returns one row per original row, with the group’s value broadcast back.

# Add each row's group mean as a new column
df['region_avg'] = df.groupby('region')['population_m'].transform('mean')

# Now you can compare each row to its own group
df['vs_region'] = df['population_m'] - df['region_avg']

That pattern, comparing a row against its own group, is one of the most useful feature-engineering moves there is, and doing it with a loop is painful.

Two options worth knowing:

df.groupby('region', dropna=False)   # keep rows where the key is missing
df.groupby('region', observed=True)  # for categoricals, skip unused combinations

By default a missing group key silently drops those rows. On real data that can quietly remove a meaningful slice of your dataset.

Merging, and How It Ruins Your Afternoon

Joins are where the most expensive mistakes happen, because they fail by producing a plausible-looking result with the wrong number of rows.

flowchart TB
    subgraph J["how='...'"]
        direction LR
        I["inner<br/>only matching keys<br/><i>the default</i>"]
        L["left<br/>all of left, matched right"]
        R["right<br/>all of right, matched left"]
        O["outer<br/>everything, gaps filled"]
    end
    style I fill:#eef1fc,stroke:#2141c8

merged = orders.merge(
    customers,
    on='customer_id',
    how='left',
    validate='many_to_one',   # ← the important argument
    indicator=True,           # adds a _merge column showing the source
)

validate is the single most valuable argument in pandas and almost nobody uses it. It states what you believe about the relationship and raises immediately if you are wrong:

• 'one_to_one' — keys unique on both sides
• 'many_to_one' — keys unique on the right
• 'one_to_many' — keys unique on the left
• 'many_to_many' — no guarantee, and you should be suspicious

Without it, a duplicated key on the right silently multiplies your rows. A thousand-row order table joined to a customer table with one accidental duplicate returns 1,001 rows, your revenue total is slightly wrong, and nothing warns you. I have seen this reach a board pack.

Build the check into your reflexes:

before = len(orders)
merged = orders.merge(customers, on='customer_id', how='left',
                      validate='many_to_one')
assert len(merged) == before, f"row count changed: {before} → {len(merged)}"

# And look at what failed to match
merged['_merge'].value_counts()

Two more merge hazards:

Type mismatch on the key. An int64 customer id and a string '1234' describe the same customer and will never match. Modern pandas refuses the merge outright with a ValueError, which is a genuine improvement over older versions that returned zero matches in silence. Check df.dtypes on both sides and cast deliberately.

Whitespace and case. 'Delhi ' and 'Delhi' are different keys. Normalise before joining:

for d in (orders, customers):
    d['key'] = d['key'].str.strip().str.lower()

Reshaping

Data arrives wide when you need it long, and vice versa. Two functions cover almost everything.

flowchart LR
    W["WIDE<br/>city · 2023 · 2024 · 2025<br/><i>one row per city</i>"]
    L["LONG<br/>city · year · value<br/><i>one row per observation</i>"]
    W -->|"melt()"| L
    L -->|"pivot() / pivot_table()"| W
    style L fill:#eef1fc,stroke:#2141c8

# Wide to long — usually what analysis and plotting want
long = wide.melt(
    id_vars=['city'],
    value_vars=['2023', '2024', '2025'],
    var_name='year',
    value_name='population_m',
)

# Long to wide — usually what humans want to read
wide = long.pivot(index='city', columns='year', values='population_m')

# When duplicates exist, pivot raises; pivot_table aggregates
summary = long.pivot_table(
    index='city', columns='year', values='population_m', aggfunc='mean'
)

If pivot raises about duplicate entries, that is useful information. It means your data has more than one row per index-column pair, and you need to decide what that means before you aggregate it away.

Method Chaining

Once you are comfortable, chaining reads far better than reassigning a variable eight times, and it avoids the intermediate-state confusion that makes notebooks unreliable.

result = (
    df
    .query('population_m > 5')
    .assign(
        log_pop=lambda d: np.log(d.population_m),
        region=lambda d: d.region.astype('category'),
    )
    .groupby('region', observed=True)
    .agg(avg_log=('log_pop', 'mean'), n=('city', 'count'))
    .sort_values('avg_log', ascending=False)
    .reset_index()
)

The lambda d: inside assign refers to the frame as it exists at that point in the chain, which is what lets you use a column you created two lines earlier.

To debug a chain, insert a .pipe:

def show(d, label=''):
    print(label, d.shape)
    return d

result = (df
    .query('population_m > 5').pipe(show, 'after filter')
    .groupby('region').agg(n=('city', 'count')).pipe(show, 'after group')
)

Reading Files Without Regret

pd.read_csv guesses, and its guesses are usually right in a way that hides problems until later. Be explicit about the things that matter.

df = pd.read_csv(
    'data.csv',
    dtype={'customer_id': 'string', 'postcode': 'string'},  # keep leading zeros
    parse_dates=['created_at'],
    na_values=['', 'NA', 'N/A', 'null', 'NULL', '-', 'unknown'],
    thousands=',',
)

The dtype on identifiers is not optional. A customer id of 0012345 read as an integer becomes 12345, and it will never join to anything again. Postcodes, phone numbers, account numbers and product codes are all text that happens to contain digits.

For anything you will read more than once, convert to Parquet. It stores dtypes, compresses well, and loads an order of magnitude faster:

df.to_parquet('data.parquet')
df = pd.read_parquet('data.parquet')   # dtypes preserved exactly

The First Fifteen Minutes With Any Dataset

A routine worth running every single time, before you form any opinion:

df.shape                                    # how much is there
df.head(20)                                 # what does it look like
df.dtypes                                   # what did pandas decide
df.isna().mean().sort_values(ascending=False)  # what is missing
df.describe(include='all').T                # ranges, and impossible values
df.duplicated().sum()                       # exact duplicate rows

# Every categorical column, checked for spelling variants
for c in df.select_dtypes(['object', 'string', 'category']):
    print(f"\n{c}: {df[c].nunique()} unique")
    print(df[c].value_counts().head(10))

That last loop is the one that earns its keep. It is how you discover that your country column contains UK, U.K., United Kingdom, england and a single Uk , all of which your model will treat as five unrelated categories.

Mistakes Worth Knowing In Advance

• Chained assignment. One .loc call, always.
• Merging without validate. Assert the row count too.
• fillna(0) across a whole frame. Decide per column.
• Ignoring object dtype. It is nearly always a parsing problem you have not noticed.
• Losing leading zeros on identifiers. Set dtype='string' at read time.
• Forgetting groupby drops missing keys. Pass dropna=False when that matters.
• inplace=True. It rarely saves memory, it breaks chaining, and it is being wound down. Assign the result instead.
• Iterating with iterrows. It is slow and it hands you a copy. Vectorise, use groupby.transform, or as a last resort apply.
• Trusting a notebook you have not restarted. Kernel restart, run all, then believe the number.

What Carries Forward

The index is doing more than you think. Operations align on labels, not positions. When two frames combine strangely, look at the index first.

Joins fail by succeeding. They give you a result with the wrong number of rows and no error. validate plus a row-count assertion costs two lines and catches nearly all of it.

Look at your data before you model it. The fifteen-minute routine above finds more real problems than any amount of algorithm selection.

Next: cleaning, joining and reshaping in earnest, on a genuinely messy public dataset, from raw file to something you would be willing to put a model on.