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.
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 backLoading 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.
df = pd.read_csv("file.csv")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.
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.
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.
df.info()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.
df["value"].describe()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.
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.
df[df["column"] > value]big = df[df["value"] > 200]
len(big) # → 3& and |
Join two tests with & (both) or | (either). Each test needs its own round brackets.
both = df[(df["district"] == "Harbour") & (df["status"] == "Open")]
len(both) # → 2
either = df[(df["district"] == "Kelby") | (df["status"] == "Charged")]
len(either) # → 3and 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.
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".
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.
df.loc[df["value"] > 400, ["ref", "officer"]].shape # → (2, 2)
df.iloc[0]["ref"] # → C1Changing some rows
.loc on the left of = changes only the rows that pass the test, in one column.
df["recall"] = "No"
df.loc[df["value"] > 400, "recall"] = "Yes"
(df["recall"] == "Yes").sum() # → 2Sorting 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.
df.sort_values("column", ascending=False)df.sort_values("value").iloc[0]["ref"] # → C3
top_two = df.sort_values("value", ascending=False).head(2)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.
df["district"].value_counts()
df["district"].value_counts()["Harbour"] # → 3normalize=True
The same counts as shares of the whole, adding up to 1.
df["status"].value_counts(normalize=True)["Open"] # → 0.5.nunique()
How many DIFFERENT values a column holds.
df["officer"].nunique() # → 3sum, mean, median, max
One number from a whole column. Blanks are skipped, not counted as zero.
df["value"].sum() # → 1400.0
df["value"].mean() # → 280.0
df["value"].median() # → 300.0
df["value"].max() # → 450.0New 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.
df["vat"] = df["value"] * 0.2
df["vat"].max() # → 90.0A column of True and False
A comparison stored as a column. Its .sum() counts the Trues.
df["over"] = df["value"] > 250
df["over"].sum() # → 3Groups
Visual reference
Split the rows into a pile per district, sum each pile, and combine the answers into one row per district.
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.
df.groupby("group_column")["value_column"].sum()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.
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.
df.groupby("district")["value"].agg(["count", "sum", "mean"])
report = df.groupby("officer").agg(cases=("ref", "count"), total=("value", "sum"))
report.loc["Ash", "total"] # → 650.0Two columns and .unstack()
Group by a list of columns for one row per pair. .unstack() turns the second one into columns: a grid.
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.
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.
df["place"] = df.groupby("district")["value"].rank(ascending=False)
df.loc[0, "place"] # → 2.0Joining Tables
Visual reference
The left circle is the first table, the right the second. The shaded part is the rows a merge keeps.
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.
pd.merge(left, right, on="key")joined = pd.merge(df, officers, on="officer")
len(joined) # → 6how="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.
everyone = pd.merge(officers, df, on="officer", how="left")
len(everyone) # → 7indicator=True
Adds a _merge column saying where each row came from: both, left_only or right_only.
marked = pd.merge(officers, df, on="officer", how="outer", indicator=True)
(marked["_merge"] == "left_only").sum() # → 1left_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.
staff = officers.rename(columns={"officer": "name"})
joined = pd.merge(df, staff, left_on="officer", right_on="name")
joined = joined.drop(columns="name")
len(joined) # → 6pd.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.
north = df[df["district"] == "Harbour"]
south = df[df["district"] != "Harbour"]
both = pd.concat([north, south], ignore_index=True)
len(both) # → 6Cleaning
.isna()
True where a value is missing (NaN). .sum() on it counts the blanks.
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.
round(df["value"].fillna(0).mean(), 1) # → 233.3
len(df.dropna(subset=["value"])) # → 5.duplicated() and .drop_duplicates()
.duplicated() is True on every row that repeats an earlier one exactly. .drop_duplicates() keeps the first of each.
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.
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.
tags = pd.Series([" vyper", "VYPER ", "Vyper", "Kilo"])
tags.str.contains("vyper", case=False).sum() # → 3Text into numbers
Strip the symbols with .str.replace, then pd.to_numeric. errors="coerce" turns anything unreadable into NaN instead of stopping.
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.
df["value"].dtype # → float64
whole = df["value"].fillna(0).astype(int)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.
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
df["when"].min()The .dt accessor
The parts of a date column, behind .dt: .dt.month, .dt.year, .dt.day_name().
df["when"] = pd.to_datetime(df["date"], dayfirst=True)
df["month"] = df["when"].dt.month
df.groupby("month").size().idxmax() # → 3Days between dates
One date minus another is a gap in time. .dt.days turns the gaps into numbers of days.
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.
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%.
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.
pd.Series([2, 4, 6, 8]).rolling(3).mean().max() # → 6.0Reshaping
.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.
df.pivot_table(index="row_col", columns="col_col", values="v", aggfunc="sum")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.
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.
def band(value):
if value >= 400:
return "Major"
return "Minor"
df["band"] = df["value"].apply(band)
(df["band"] == "Major").sum() # → 2apply 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.
df["size"] = pd.cut(df["value"], bins=[0, 100, 400, float("inf")], labels=["Small", "Medium", "Large"], right=False)
(df["size"] == "Large").sum() # → 2Files 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.
df.to_csv("name.csv", index=False)df.to_csv("clean.csv", index=False)
len(pd.read_csv("clean.csv")) # → 6Only some columns
columns=[...] writes only the columns named, in that order. The table itself keeps them all.
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.
for district, rows in df.groupby("district"):
rows.to_csv(district + ".csv", index=False)
len(pd.read_csv("Harbour.csv")) # → 3A folder of files
glob finds every file matching a pattern, * standing for anything. Read each in a loop and stack them with pd.concat.
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) # → 6sorted(). The order files come back in is not promised.Errors Decoded
| Error | What it means | First 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 ambiguous | and, or or if was given a whole column. | Join column tests with & and |, each in its own brackets. |
| TypeError: unsupported operand | Arithmetic 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 attribute | A method used without its round brackets. | Add (): .sort_values(), not .sort_values. |
