Pandas and NumPy Cheat Sheet for Real Data Work

Pandas and NumPy Cheat Sheet

Pandas gives you three or four ways to do everything. Two of them are wrong in ways that produce no error message.

This sheet covers the syntax you look up, and spends most of its length on the distinctions that actually decide whether your numbers are correct: loc versus iloc, chained indexing, and why apply is usually the wrong tool.

Updated for 2026. Pandas 2.x and NumPy 2.x.

Loading Data

import pandas as pd
import numpy as np

df = pd.read_csv("data.csv")
df = pd.read_csv("data.csv", parse_dates=["created_at"], dtype={"zip": str})
df = pd.read_csv("big.csv", usecols=["id", "amount"], nrows=1000)

df = pd.read_excel("book.xlsx", sheet_name="Q1")
df = pd.read_parquet("data.parquet")
df = pd.read_json("data.json")

df.to_csv("out.csv", index=False)
df.to_parquet("out.parquet")

dtype={"zip": str} at load time, not after. Pandas will happily read a zip code as an integer and drop the leading zero, and there is no way to recover it afterwards. The same applies to phone numbers, product codes, and any identifier that only looks numeric.

index=False when writing CSV. Otherwise Pandas writes the index as an unnamed first column, and the next person to read the file gets a mystery Unnamed: 0.

Inspecting

df.head(10)          # first rows
df.tail()            # last rows
df.sample(5)         # random rows — better for spotting problems
df.shape             # (rows, columns)
df.info()            # dtypes, non-null counts, memory
df.describe()        # numeric summary
df.describe(include="object")   # counts, unique, top for text columns
df.dtypes
df.columns.tolist()
df["status"].value_counts(dropna=False)
df.isna().sum()      # nulls per column

df.info() is the first command to run, not df.head(). head shows you ten rows that may all be clean. info shows you that a column you assumed was numeric has dtype object, which means one row somewhere contains text and every arithmetic operation on that column is about to fail or silently produce garbage.

value_counts(dropna=False) — the default hides NaN, which is exactly the value you were looking for.

Selection: loc vs iloc

The most important distinction in Pandas, and the one most tutorials cover badly.

df["amount"]                # one column → Series
df[["id", "amount"]]        # several columns → DataFrame

df.loc[5]                   # row with INDEX LABEL 5
df.iloc[5]                  # 6th row, whatever its label

df.loc[0:3]                 # 4 rows — endpoint included
df.iloc[0:3]                # 3 rows — endpoint excluded

df.loc[df["amount"] > 100, "status"]        # rows by mask, one column
df.loc[df["amount"] > 100, ["id", "status"]]
df.iloc[0:5, 2:4]                            # positions only

They look identical on a fresh DataFrame because a default index is 0, 1, 2, ... — label and position coincide. They diverge the instant you filter, sort, or set a date index, and code written on the assumption they are the same starts returning the wrong rows without erroring.

Rule of thumb: use loc by default. Use iloc only when you genuinely mean "the nth row" regardless of what it is called.

Filtering

df[df["amount"] > 100]
df[(df["amount"] > 100) & (df["country"] == "US")]     # & not and
df[(df["status"] == "new") | (df["status"] == "open")]
df[df["status"].isin(["new", "open"])]                  # cleaner
df[~df["status"].isin(["closed"])]                      # ~ is NOT
df[df["email"].isna()]
df[df["name"].str.contains("smith", case=False, na=False)]
df.query("amount > 100 and country == 'US'")

Use &, |, ~ — never and, or, not. The Python keywords ask a whole array for a single truth value and raise "truth value of a Series is ambiguous". The operators work elementwise.

Parentheses around each condition are mandatory. & binds tighter than > in Python, so df["a"] > 1 & df["b"] < 2 parses as something you did not write.

na=False on .str methods. Without it, a null in the column produces NaN in the mask rather than False, and the filter errors.

The Chained Indexing Trap

This one silently loses your edits.

# BROKEN — may modify a temporary copy and change nothing
df[df["amount"] > 100]["status"] = "high"

# CORRECT — one operation, rows and column together
df.loc[df["amount"] > 100, "status"] = "high"

The broken version is two separate operations. The first produces an intermediate object; Pandas cannot guarantee whether that is a view onto the original data or a copy of it. The assignment then targets the intermediate, which may be discarded immediately.

You get a SettingWithCopyWarning — a warning, so the script continues, and the data is simply unchanged.

If you deliberately want an independent object, say so:

subset = df[df["amount"] > 100].copy()
subset["status"] = "high"        # no warning, no ambiguity

What people get wrong: treating SettingWithCopyWarning as noise to be suppressed. It is the only signal that an assignment may have done nothing.

Missing Data

df.isna().sum()                       # count per column
df.isna().mean().sort_values()        # proportion per column

df.dropna()                           # drop any row with any null
df.dropna(subset=["email"])           # only where email is null
df.dropna(axis=1, thresh=len(df)*0.9) # drop mostly-empty columns

df["amount"] = df["amount"].fillna(0)
df["amount"] = df["amount"].fillna(df["amount"].median())
df["price"] = df["price"].ffill()     # last observation carried forward

Measure before you drop. df.isna().mean() tells you whether a column is 2% empty or 80% empty — completely different problems with completely different fixes.

dropna() with no arguments is aggressive. One null in one column removes the whole row. On a wide frame that can delete most of your data in a single line.

Do not fill numeric nulls with 0 by reflex. Zero is a real value. It drags every mean down and inflates every count of "customers who spent nothing". The median is usually a safer default; forward fill is correct for time series where the previous value genuinely persists.

GroupBy and Aggregation

df.groupby("country")["amount"].sum()
df.groupby("country")["amount"].agg(["sum", "mean", "count"])
df.groupby(["country", "status"])["amount"].mean()

df.groupby("country").agg(
    total=("amount", "sum"),
    orders=("id", "count"),
    avg=("amount", "mean"),
).reset_index()

df.groupby("country")["amount"].transform("mean")   # keeps original shape

Named aggregation is the readable form. agg(total=("amount", "sum")) produces a column called total rather than a multi-level column you then have to flatten.

transform versus agg is the same distinction as a window function versus GROUP BY in SQL. agg collapses to one row per group. transform returns a value for every original row, which is what you want when adding a "group average" column beside each record.

reset_index() after grouping, unless you specifically want the group keys as the index. Forgetting it is why the next merge mysteriously fails.

Merge vs Join vs Concat

pd.merge(orders, customers, on="customer_id", how="left")
pd.merge(a, b, left_on="user_id", right_on="id", how="inner")
pd.merge(a, b, on="id", how="outer", indicator=True)   # adds _merge column

orders.join(customers, on="customer_id")     # index-based convenience

pd.concat([jan, feb, mar], ignore_index=True)      # stack rows
pd.concat([features, labels], axis=1)              # side by side

how takes the same values as SQL: inner, left, right, outer — plus cross.

Check the row count after every merge. If len(result) > len(left) on a left join, your right-hand key is not unique and rows have been duplicated. This is the most common way a total silently doubles.

indicator=True is the debugging tool for merges. It adds a _merge column saying left_only, right_only, or both, which answers "why did 400 rows come back empty" in one value_counts().

Reshaping

df.pivot_table(index="country", columns="month",
               values="amount", aggfunc="sum", fill_value=0)

df.melt(id_vars=["id"], var_name="metric", value_name="value")

df.sort_values("amount", ascending=False)
df.sort_values(["country", "amount"], ascending=[True, False])
df.rename(columns={"amt": "amount"})
df.drop(columns=["temp"])
df.drop_duplicates(subset=["email"], keep="last")

pivot_table over pivot. pivot errors on duplicate index/column pairs; pivot_table aggregates them, which is nearly always what you meant.

melt is the inverse — wide to long. Most plotting and modelling libraries want long format.

Dates

df["created"] = pd.to_datetime(df["created"], errors="coerce")

df["year"]  = df["created"].dt.year
df["month"] = df["created"].dt.month
df["dow"]   = df["created"].dt.day_name()

df[df["created"].between("2026-01-01", "2026-03-31")]

df.set_index("created").resample("M")["amount"].sum()   # monthly totals

errors="coerce" turns unparseable dates into NaT instead of raising. You then count them with isna().sum() and decide, rather than having the whole pipeline stop on row 40,000.

The .dt accessor is vectorised. Extracting a year with apply(lambda d: d.year) does the same job far more slowly.

Apply vs Vectorised

# SLOW — one Python call per row
df["total"] = df.apply(lambda r: r["price"] * r["qty"], axis=1)

# FAST — one compiled loop over the whole column
df["total"] = df["price"] * df["qty"]

# SLOW
df["band"] = df["amount"].apply(lambda x: "high" if x > 100 else "low")

# FAST
df["band"] = np.where(df["amount"] > 100, "high", "low")

# multiple conditions
df["band"] = np.select(
    [df["amount"] > 1000, df["amount"] > 100],
    ["huge", "high"],
    default="low",
)

Why the difference is so large: a vectorised operation hands the entire column to compiled C code operating on one contiguous block of memory. apply calls back into the Python interpreter once per row, paying function-call and object-boxing overhead every time. On a million rows that is commonly 10x to 100x.

Vectorised equivalents exist for more than you think: arithmetic, comparisons, .str methods, .dt parts, np.where, np.select, .map for dictionary lookups, and .clip.

Keep apply for genuinely row-wise logic that cannot be expressed columnwise — and accept the cost knowingly.

NumPy Essentials

a = np.array([1, 2, 3])
np.zeros((2, 3)); np.ones(5); np.arange(0, 10, 2); np.linspace(0, 1, 5)

a.shape; a.dtype; a.ndim
a.reshape(3, 1)
a.astype(np.float64)

a.sum(); a.mean(); a.std(); a.max(); a.argmax()
m.sum(axis=0)      # down the columns
m.sum(axis=1)      # across the rows

a[a > 1]                       # boolean mask
np.where(a > 1, a, 0)

axis=0 operates down rows, producing one value per column. axis=1 operates across columns, producing one value per row. Getting this backwards is the most common NumPy error, and the result is a plausible-looking array of the wrong length.

Broadcasting

np.array([1, 2, 3]) * 2                    # → [2, 4, 6]

m = np.ones((3, 4))
m + np.array([1, 2, 3, 4])                 # row vector applied to each row
m + np.array([[1], [2], [3]])              # column vector applied to each column

Broadcasting stretches the smaller array across the larger one without copying it. Shapes are compared from the right; each pair of dimensions must be equal or one of them must be 1.

That rule is the whole of broadcasting, and (3,4) + (3,) failing while (3,4) + (4,) works is it in action.

Float comparison

0.1 + 0.2 == 0.3            # False
np.isclose(0.1 + 0.2, 0.3)  # True
np.allclose(arr1, arr2)     # whole-array version

Never compare floats with ==. Binary floating point cannot represent most decimal fractions exactly, so 0.1 + 0.2 lands a hair above 0.3. This is IEEE 754, not a Python quirk — every language does it. Use np.isclose with a tolerance.

The Five Mistakes

1. Chained indexing for assignment. df[mask]["col"] = x may change nothing. Use df.loc[mask, "col"] = x.

2. Assuming loc and iloc are interchangeable. They coincide only on a default index, and loc slices include the endpoint.

3. apply where a vectorised operation exists. 10x to 100x slower for identical output.

4. Relying on inplace=True. It rarely saves memory, does not chain, returns None — so df = df.dropna(inplace=True) sets your DataFrame to None — and is being phased out. Assign the result instead.

5. Comparing floats with ==. Use np.isclose.

Print This Section

INSPECT     df.info()  ← run FIRST     df.isna().sum()
            value_counts(dropna=False)

SELECT      loc = label (endpoint INCLUSIVE)   iloc = position (exclusive)
            df.loc[mask, "col"] = x     ← never df[mask]["col"] = x

FILTER      &  |  ~   (never and/or/not) · parenthesise every condition
            .str methods need na=False

GROUP       agg  = one row per group      transform = one row per record
            reset_index() after groupby

COMBINE     merge = by key (SQL join)   concat = stack   join = by index
            merge → check len() after · indicator=True to debug

SPEED       vectorise: * / np.where / np.select / .str / .dt
            apply = one Python call per row = 10-100x slower

NUMPY       axis=0 down columns · axis=1 across rows
            broadcasting: compare shapes right-to-left, equal or 1
            floats: np.isclose, never ==

The difference between a fast Pandas script and a slow one is rarely the algorithm — it is whether the work happens inside NumPy or inside a Python loop wearing a apply costume.

🔗 Next in this collection: Python Cheat Sheet, or return to The Complete Developer Cheat Sheet Collection.