QueryCasequerycase.com
Lab reference guide

Pandas Cheat Sheet

Every pandas move the cases teach, from the first read_csv to writing the clean file back out.

Every example on this sheet runs against the two small tables below, so you can see exactly where each answer after # → comes from. A pattern is the template; swap in your own table and column names.

The tables every example uses
import pandas as pd
df = pd.DataFrame({
    "ref": ["C1", "C2", "C3", "C4", "C5", "C6"],
    "district": ["Harbour", "Dunmore", "Harbour", "Kelby", "Dunmore", "Harbour"],
    "officer": ["Ash", "Bea", "Ash", "Cal", "Bea", "Ash"],
    "value": [120, 450, 80, 300, None, 450],
    "status": ["Open", "Charged", "Open", "Filed", "Open", "Charged"],
    "date": ["03/01/2026", "15/01/2026", "22/02/2026", "03/03/2026", "09/03/2026", "15/03/2026"],
})
officers = pd.DataFrame({"officer": ["Ash", "Bea", "Cal", "Dee"], "station": ["North", "South", "North", "East"]})

Six case returns in df, one with no value, and four officers in officers, one of whom has no returns at all.

forkeywordslenbuilt-in functions"text"text42numbers# → 3what comes back
01

Loading and Looking

read_csv

Reads a CSV file into a DataFrame, a table with named columns. The first line of the file becomes the column names.

Pattern
df = pd.read_csv("file.csv")
Example
returns = pd.read_csv("returns.csv")
returns.shape  # → (6, 6)

shape, len and columns

.shape is (rows, columns), len(df) the rows alone, and .columns the names of the columns.

Example
df.shape  # → (6, 6)
len(df)  # → 6
list(df.columns)[:3]  # → ['ref', 'district', 'officer']

.head() and .tail()

The first or last rows, five unless you say otherwise. The first thing to run on any new file.

Example
df.head()
df.head(3)
df.tail(2)

.info()

Every column with how many values it actually holds and what kind they are. Fewer values than rows means blanks.

Example
df.info()
TipA column of numbers listed as object holds text somewhere in it, a pound sign or a word, and will not add up.

.describe()

Count, mean, spread, quartiles, smallest and largest of a number column, in one call.

Example
df["value"].describe()
02

Columns and Rows

One column, several columns

One name in square brackets gives a single column, a Series. A list of names gives a smaller table.

Example
df["value"].sum()  # → 1400.0
df[["ref", "value"]].shape  # → (6, 2)

Filtering rows

A comparison on a column gives one True or False per row. Put it inside df[ ] and only the True rows are kept.

Pattern
df[df["column"] > value]
Example
big = df[df["value"] > 200]
len(big)  # → 3

& and |

Join two tests with & (both) or | (either). Each test needs its own round brackets.

Example
both = df[(df["district"] == "Harbour") & (df["status"] == "Open")]
len(both)  # → 2
either = df[(df["district"] == "Kelby") | (df["status"] == "Charged")]
len(either)  # → 3
TipThe words and and or do not work on columns. pandas says "The truth value of a Series is ambiguous".

.between()

A range in one call. Both ends are included.

Example
len(df[df["value"].between(100, 300)])  # → 2

.isin() and ~

.isin([...]) is True where the value is any of a list. ~ in front flips the test to "none of them".

Example
len(df[df["district"].isin(["Harbour", "Kelby"])])  # → 4
len(df[~df["district"].isin(["Harbour", "Kelby"])])  # → 2

.loc and .iloc

.loc[rows, columns] picks by test and by name at once. .iloc[n] picks a row by its position.

Example
df.loc[df["value"] > 400, ["ref", "officer"]].shape  # → (2, 2)
df.iloc[0]["ref"]  # → C1

Changing some rows

.loc on the left of = changes only the rows that pass the test, in one column.

Example
df["recall"] = "No"
df.loc[df["value"] > 400, "recall"] = "Yes"
(df["recall"] == "Yes").sum()  # → 2
03

Sorting and Counting

.sort_values()

Puts the rows in order of a column, smallest first. ascending=False puts the biggest first. Blanks go to the end.

Pattern
df.sort_values("column", ascending=False)
Example
df.sort_values("value").iloc[0]["ref"]  # → C3
top_two = df.sort_values("value", ascending=False).head(2)
TipA sort hands back a NEW table. Without df = ... or a new name, the order is thrown away.

.value_counts()

How many times each value appears in a column, the most common first.

Example
df["district"].value_counts()
df["district"].value_counts()["Harbour"]  # → 3

normalize=True

The same counts as shares of the whole, adding up to 1.

Example
df["status"].value_counts(normalize=True)["Open"]  # → 0.5

.nunique()

How many DIFFERENT values a column holds.

Example
df["officer"].nunique()  # → 3

sum, mean, median, max

One number from a whole column. Blanks are skipped, not counted as zero.

Example
df["value"].sum()  # → 1400.0
df["value"].mean()  # → 280.0
df["value"].median()  # → 300.0
df["value"].max()  # → 450.0
TipWhen a few huge values pull the mean up, the median is the honest "typical" figure.
04

New Columns

A column from arithmetic

Arithmetic on a column runs on every row at once. Assign it to a new name and the table gains a column.

Example
df["vat"] = df["value"] * 0.2
df["vat"].max()  # → 90.0

A column of True and False

A comparison stored as a column. Its .sum() counts the Trues.

Example
df["over"] = df["value"] > 250
df["over"].sum()  # → 3
05

Groups

Visual reference

Split the rows into a pile per district, sum each pile, and combine the answers into one row per district.

Harbour
12080450
650
Dunmore
450NaN
450
Kelby
300
300

Blanks are skipped, not counted as 0: Dunmore sums to 450.

.groupby()

Splits the rows by one column, works out the same thing for each group, and puts the answers together. A label picks one group's answer out.

Pattern
df.groupby("group_column")["value_column"].sum()
Example
totals = df.groupby("district")["value"].sum()
totals["Harbour"]  # → 650.0

.size()

How many rows are in each group. It needs no column, because counting rows does not care what is in them.

Example
df.groupby("officer").size()["Ash"]  # → 3

.agg()

Several answers for each group at once: a list of their names, or named answers of your own.

Example
df.groupby("district")["value"].agg(["count", "sum", "mean"])
report = df.groupby("officer").agg(cases=("ref", "count"), total=("value", "sum"))
report.loc["Ash", "total"]  # → 650.0

Two columns and .unstack()

Group by a list of columns for one row per pair. .unstack() turns the second one into columns: a grid.

Example
pairs = df.groupby(["district", "status"]).size()
grid = pairs.unstack()

.transform()

The group's answer written on every row of that group, so each row can be compared with its own group.

Example
df["district_total"] = df.groupby("district")["value"].transform("sum")
df["share"] = df["value"] / df["district_total"]
df.loc[0, "district_total"]  # → 650.0

.rank() in each group

Each row's place within its own group. ascending=False makes the biggest 1.

Example
df["place"] = df.groupby("district")["value"].rank(ascending=False)
df.loc[0, "place"]  # → 2.0
06

Joining Tables

Visual reference

The left circle is the first table, the right the second. The shaded part is the rows a merge keeps.

how="inner"Only rows that match on both
how="left"Every row of the first table
how="right"Every row of the second table
how="outer"Every row of both

A kept row with no match on the other side gets NaN in that side's columns.

pd.merge()

Joins two tables on a column they share. By default only rows with a match on both sides are kept.

Pattern
pd.merge(left, right, on="key")
Example
joined = pd.merge(df, officers, on="officer")
len(joined)  # → 6

how="left" and how="outer"

how="left" keeps every row of the first table, with blanks where the second has no match. how="outer" keeps every row of both.

Example
everyone = pd.merge(officers, df, on="officer", how="left")
len(everyone)  # → 7

indicator=True

Adds a _merge column saying where each row came from: both, left_only or right_only.

Example
marked = pd.merge(officers, df, on="officer", how="outer", indicator=True)
(marked["_merge"] == "left_only").sum()  # → 1

left_on and right_on

Join when the two tables name the key differently. .drop(columns=...) removes the second copy afterwards, and .rename() is the other way round.

Example
staff = officers.rename(columns={"officer": "name"})
joined = pd.merge(df, staff, left_on="officer", right_on="name")
joined = joined.drop(columns="name")
len(joined)  # → 6

pd.concat()

Stacks tables with the same columns, one under the other. ignore_index=True numbers the rows once instead of keeping each table's own.

Example
north = df[df["district"] == "Harbour"]
south = df[df["district"] != "Harbour"]
both = pd.concat([north, south], ignore_index=True)
len(both)  # → 6
07

Cleaning

.isna()

True where a value is missing (NaN). .sum() on it counts the blanks.

Example
df["value"].isna().sum()  # → 1

.fillna() and .dropna()

.fillna(0) puts a value in every gap. .dropna(subset=[...]) removes the rows with a gap in those columns.

Example
round(df["value"].fillna(0).mean(), 1)  # → 233.3
len(df.dropna(subset=["value"]))  # → 5
TipFilling a gap with 0 changes the average. Decide whether a blank means "nothing" or "not known" first.

.duplicated() and .drop_duplicates()

.duplicated() is True on every row that repeats an earlier one exactly. .drop_duplicates() keeps the first of each.

Example
log = pd.DataFrame({"job": [1, 2, 2, 3], "charge": [50, 80, 80, 40]})
log.duplicated().sum()  # → 1
len(log.drop_duplicates())  # → 3

.str.strip() and .str.title()

Text methods for a whole column, behind .str. Tidy the spelling first, or the same name typed two ways counts twice.

Example
tags = pd.Series([" vyper", "VYPER ", "Vyper", "Kilo"])
tags.nunique()  # → 4
tags.str.strip().str.title().nunique()  # → 2

.str.contains()

True where the text holds a piece of text. case=False ignores capitals.

Example
tags = pd.Series([" vyper", "VYPER ", "Vyper", "Kilo"])
tags.str.contains("vyper", case=False).sum()  # → 3

Text into numbers

Strip the symbols with .str.replace, then pd.to_numeric. errors="coerce" turns anything unreadable into NaN instead of stopping.

Example
cash = pd.Series(["£1,250", "£80", "lost"])
bare = cash.str.replace("£", "", regex=False).str.replace(",", "", regex=False)
pounds = pd.to_numeric(bare, errors="coerce")
pounds.sum()  # → 1330.0

.dtype and .astype()

.dtype says what a column holds. .astype(int) makes a column of whole numbers, once it has no gaps.

Example
df["value"].dtype  # → float64
whole = df["value"].fillna(0).astype(int)
08

Dates

pd.to_datetime()

Turns date-shaped text into real dates that sort, subtract and group. dayfirst=True reads 03/01/2026 as 3 January.

Example
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
df["when"].min()
TipText dates sort letter by letter, so 22/02 comes after 15/03. Convert them before you sort.

The .dt accessor

The parts of a date column, behind .dt: .dt.month, .dt.year, .dt.day_name().

Example
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
df["month"] = df["when"].dt.month
df.groupby("month").size().idxmax()  # → 3

Days between dates

One date minus another is a gap in time. .dt.days turns the gaps into numbers of days.

Example
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
gaps = df["when"].max() - df["when"]
gaps.dt.days.max()  # → 71

.resample()

Groups the rows by calendar period. "ME" is month end, so each calendar month gets one row, empty months included.

Example
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
months = df.resample("ME", on="when").size()
months.max()  # → 3

.shift() and .pct_change()

.shift(1) lines each value up with the one before it. .pct_change() is the change from it as a fraction: 2.0 means up 200%.

Example
counts = pd.Series([2, 1, 3])
(counts - counts.shift(1)).max()  # → 2.0
counts.pct_change().max()  # → 2.0

.rolling()

A moving window: .rolling(3).mean() is the average of each value and the two before it.

Example
pd.Series([2, 4, 6, 8]).rolling(3).mean().max()  # → 6.0
09

Reshaping

.pivot_table()

Long rows into a grid: one row per value of index, one column per value of columns, and aggfunc deciding what goes in each square.

Pattern
df.pivot_table(index="row_col", columns="col_col", values="v", aggfunc="sum")
Example
grid = df.pivot_table(index="district", columns="status", values="value", aggfunc="sum")
grid.loc["Harbour", "Open"]  # → 200.0

.melt()

A grid back into long rows, one per square. .reset_index() first, so the row labels become a column melt can keep.

Example
grid = df.pivot_table(index="district", columns="status", values="value", aggfunc="sum")
long = grid.reset_index().melt(id_vars="district", var_name="status", value_name="total")
len(long)  # → 9

.apply()

Runs a function of your own on every value of a column. For a rule arithmetic cannot say.

Example
def band(value):
    if value >= 400:
        return "Major"
    return "Minor"

df["band"] = df["value"].apply(band)
(df["band"] == "Major").sum()  # → 2
TipHand apply the function itself, with no brackets: .apply(band). And if a comparison would do the job, use the comparison: it is far faster.

pd.cut()

Puts every value in a band by the edges given. right=False puts a value sitting on an edge into the band above it.

Example
df["size"] = pd.cut(df["value"], bins=[0, 100, 400, float("inf")], labels=["Small", "Medium", "Large"], right=False)
(df["size"] == "Large").sum()  # → 2
10

Files In and Out

.to_csv()

Writes the table to a CSV file. index=False leaves the row labels out; without it they arrive back as a column called Unnamed: 0.

Pattern
df.to_csv("name.csv", index=False)
Example
df.to_csv("clean.csv", index=False)
len(pd.read_csv("clean.csv"))  # → 6

Only some columns

columns=[...] writes only the columns named, in that order. The table itself keeps them all.

Example
df.to_csv("for_audit.csv", index=False, columns=["ref", "value"])
list(pd.read_csv("for_audit.csv").columns)  # → ['ref', 'value']

A file for every group

Walk a groupby with a for loop: each turn gives a group's name and its rows, ready to write.

Example
for district, rows in df.groupby("district"):
    rows.to_csv(district + ".csv", index=False)
len(pd.read_csv("Harbour.csv"))  # → 3

A folder of files

glob finds every file matching a pattern, * standing for anything. Read each in a loop and stack them with pd.concat.

Example
import glob
paths = sorted(glob.glob("books/*.csv"))
frames = []
for path in paths:
    frames.append(pd.read_csv(path))
quarter = pd.concat(frames, ignore_index=True)
len(quarter)  # → 6
TipWrap the glob in sorted(). The order files come back in is not promised.
11

Errors Decoded

ErrorWhat it meansFirst thing to check
KeyError: 'colname'The table has no column by that name.The spelling and the capitals: df.columns lists them.
The truth value of a Series is ambiguousand, or or if was given a whole column.Join column tests with & and |, each in its own brackets.
TypeError: unsupported operandArithmetic on a column that holds text.df.info(): the column is object, so clean it into numbers first.
Can only use .str accessor with string values.str on a column of numbers or dates.Check it is the column of text you meant.
Can only use .dt accessor with datetimelike values.dt on a column that is still text.pd.to_datetime it first, into a new column.
AttributeError: 'function' object has no attributeA method used without its round brackets.Add (): .sort_values(), not .sort_values.
Pandas Cheat Sheet · QueryCaseThe full course is at querycase.com