pandas (Python) Cheat Sheet

40 essentials with syntax and working examples. Search or filter by group.

read_csv

Loads a CSV file into a DataFrame. The other examples assume: import pandas as pd; df = pd.read_csv("sales_flat.csv").

pd.read_csv(path, sep=',', usecols=None, dtype=None)

import pandas as pd

df = pd.read_csv("sales_flat.csv")
print(df.shape)

read_csv(parse_dates=)

Converts date columns to datetime while reading, so .dt and resample work right away.

pd.read_csv(path, parse_dates=["col"])

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
print(orders["order_date"].dtype)

read_sql

Runs a SQL query and returns the result as a DataFrame.

pd.read_sql(query, connection)

import sqlite3

con = sqlite3.connect("sunshine_grocers.db")
stores = pd.read_sql("SELECT * FROM stores WHERE region = 'South'", con)
con.close()

to_csv

Saves a DataFrame to CSV. index=False avoids writing the row index as an extra column.

df.to_csv(path, index=False)

df[df["region"] == "South"].to_csv("south_sales.csv", index=False)

to_excel

Writes to an .xlsx file (needs openpyxl). Use pd.ExcelWriter to write several sheets into one file.

df.to_excel(path, sheet_name='Sheet1', index=False)

by_region = df.groupby("region", as_index=False)["net_sales"].sum()
by_cat = df.groupby("category", as_index=False)["net_sales"].sum()

with pd.ExcelWriter("summary.xlsx") as writer:
    by_region.to_excel(writer, sheet_name="Region", index=False)
    by_cat.to_excel(writer, sheet_name="Category", index=False)

head / tail / shape

First or last n rows (5 by default) and the (rows, columns) size.

df.head(n) | df.tail(n) | df.shape

print(df.head(3))
print(df.shape)

info / dtypes

Column names, data types and non-null counts. The first check after loading data.

df.info() | df.dtypes

df.info()
print(df.dtypes)

describe

Summary statistics (count, mean, std, min, quartiles, max) for numeric columns.

df.describe(include=None)

print(df[["quantity", "unit_price", "net_sales"]].describe().round(2))

value_counts

Counts each distinct value of a column; normalize=True returns proportions.

s.value_counts(normalize=False)

print(df["channel"].value_counts())
print(df["segment"].value_counts(normalize=True).round(3))

Select columns

One column returns a Series; a list of columns returns a DataFrame.

df["col"] | df[["col1", "col2"]]

sales = df["net_sales"]
subset = df[["order_date", "store_name", "net_sales"]]

loc

Selects by labels and boolean conditions. Also the safe way to assign values to a filtered subset.

df.loc[row_condition, columns]

south = df.loc[df["region"] == "South", ["store_name", "net_sales"]]

df.loc[df["discount"] > 0, "promo"] = "Yes"

iloc

Selects by integer position (0-based, end excluded).

df.iloc[rows, cols]

first_rows = df.iloc[:5, :4]
last_row = df.iloc[-1]

Boolean filter

Filters rows with conditions. Wrap each condition in parentheses and use &, |, ~ (not and/or).

df[(cond1) & (cond2)] | | for OR | ~ for NOT

big_online = df[(df["channel"] == "Online") & (df["net_sales"] > 30)]
print(len(big_online))

isin

Keeps rows whose value is in a list (like SQL IN).

df[df["col"].isin([v1, v2])]

fresh = df[df["category"].isin(["Produce", "Dairy", "Bakery"])]

query

Filters with a readable text expression. Use @var to reference Python variables.

df.query("expression")

min_sales = 25
result = df.query("region == 'West' and net_sales > @min_sales")

between

TRUE when the value is within a range (inclusive by default). Works with ISO date strings too.

s.between(left, right, inclusive='both')

jan_2025 = df[df["order_date"].between("2025-01-01", "2025-01-31")]

New column / assign

Creates calculated columns with vectorized math. assign returns a new DataFrame, handy for chaining.

df["new"] = expr | df.assign(new=expr)

df["profit"] = df["net_sales"] - df["cost"]
df = df.assign(margin_pct=lambda d: d["profit"] / d["net_sales"] * 100)

rename

Renames columns (or index labels) and returns a new DataFrame.

df.rename(columns={"old": "new"})

out = df.rename(columns={"net_sales": "Net Sales", "store_name": "Store"})

sort_values

Sorts rows by one or more columns.

df.sort_values(by, ascending=True)

top = df.sort_values(["net_sales", "order_date"], ascending=[False, True]).head(10)

drop / drop_duplicates

Removes columns, or removes repeated rows (all columns or a subset).

df.drop(columns=[...]) | df.drop_duplicates(subset=None, keep='first')

dirty = pd.read_csv("sales_dirty.csv")
clean = dirty.drop_duplicates()
print(len(dirty) - len(clean), "duplicates removed")
slim = clean.drop(columns=["brand", "cost"])

str methods

Vectorized text cleaning on a column.

s.str.strip() | .str.title() | .str.upper() | .str.replace() | .str.contains()

dirty = pd.read_csv("sales_dirty.csv")
dirty["store_name"] = dirty["store_name"].str.strip().str.title()
dirty["category"] = dirty["category"].str.title()

to_numeric / astype

Converts text to numbers; errors='coerce' turns bad values into NaN. astype changes a column's type.

pd.to_numeric(s, errors='coerce') | s.astype(type)

dirty = pd.read_csv("sales_dirty.csv")
dirty["net_sales"] = pd.to_numeric(dirty["net_sales"].str.replace("$", "", regex=False))
dirty["quantity"] = dirty["quantity"].str.extract(r"(\d+)", expand=False).astype(int)

apply

Runs a function on each value (Series) or each row (axis=1). Flexible but slower than vectorized code.

s.apply(func) | df.apply(func, axis=1)

def size_band(x):
    return "Large" if x >= 30 else "Small"

df["basket"] = df["net_sales"].apply(size_band)
print(df["basket"].value_counts())

where (numpy)

Fast vectorized IF for creating a column from a condition.

np.where(condition, value_if_true, value_if_false)

import numpy as np

df["is_promo"] = np.where(df["discount"] > 0, "Promo", "Regular")

isna / notna

Detects missing values; .sum() gives the count per column.

df.isna().sum() | s.notna()

dirty = pd.read_csv("sales_dirty.csv")
print(dirty.isna().sum())

fillna

Replaces missing values. Assign the result back (inplace on a single column doesn't work with copy-on-write in pandas 3).

s.fillna(value) | df.fillna({"col": value})

dirty = pd.read_csv("sales_dirty.csv")
dirty["region"] = dirty["region"].fillna("Unknown")

dropna

Removes rows with missing values (optionally only in some columns).

df.dropna(subset=None, how='any')

dirty = pd.read_csv("sales_dirty.csv")
with_region = dirty.dropna(subset=["region"])

groupby + sum

Splits rows into groups and aggregates each one. as_index=False returns a regular DataFrame.

df.groupby(keys)[col].sum()

by_region = (df.groupby("region", as_index=False)["net_sales"].sum()
               .sort_values("net_sales", ascending=False))
print(by_region)

groupby + agg (named)

Several aggregations at once with clear output names.

df.groupby(key).agg(new=("col", "func"), ...)

summary = df.groupby("store_name").agg(
    sales=("net_sales", "sum"),
    orders=("order_id", "nunique"),
    avg_line=("net_sales", "mean"),
).round(2)
print(summary.head())

groupby + transform

Returns a group result aligned to every original row, e.g. each line's share of its region.

df.groupby(key)[col].transform(func)

df["region_total"] = df.groupby("region")["net_sales"].transform("sum")
df["pct_of_region"] = df["net_sales"] / df["region_total"] * 100

nlargest / nsmallest

Top or bottom n values without sorting the whole dataset.

s.nlargest(n) | df.nlargest(n, col)

top5 = df.groupby("product_name")["net_sales"].sum().nlargest(5)
print(top5)

merge

Joins two DataFrames on key columns (like SQL JOIN). Use validate= to catch unexpected duplicates.

pd.merge(left, right, on=key, how='inner'|'left'|'right'|'outer')

orders = pd.read_csv("orders.csv")
stores = pd.read_csv("stores.csv")
m = orders.merge(stores[["store_id", "store_name", "region"]],
                 on="store_id", how="left", validate="many_to_one")

concat

Stacks DataFrames vertically (or side by side with axis=1).

pd.concat([df1, df2], ignore_index=True)

y2024 = df[df["order_date"] < "2025-01-01"]
y2025 = df[df["order_date"] >= "2025-01-01"]
all_years = pd.concat([y2024, y2025], ignore_index=True)

pivot_table

Builds an Excel-style pivot table.

df.pivot_table(index, columns, values, aggfunc='sum', fill_value=0, margins=False)

pt = df.pivot_table(index="region", columns="category",
                    values="net_sales", aggfunc="sum",
                    fill_value=0, margins=True).round(0)
print(pt)

melt

Unpivots wide columns into long rows (the opposite of pivot).

df.melt(id_vars, value_vars, var_name, value_name)

budget = pd.read_csv("budget.csv")
long = budget.melt(id_vars=["month", "store_id"],
                   value_vars=["revenue_budget", "expense_budget"],
                   var_name="type", value_name="amount")

crosstab

Frequency table of two columns. normalize='index' gives row percentages.

pd.crosstab(index, columns, normalize=False)

print(pd.crosstab(df["segment"], df["channel"], normalize="index").round(3))

to_datetime

Converts text to datetime. format='mixed' handles columns with several date formats.

pd.to_datetime(s, format=None, errors='raise')

df["order_date"] = pd.to_datetime(df["order_date"])

dirty = pd.read_csv("sales_dirty.csv")
dirty["order_date"] = pd.to_datetime(dirty["order_date"], format="mixed")

.dt accessor

Extracts parts of datetime values.

s.dt.year | .dt.month | .dt.day_name() | .dt.to_period('M')

df["order_date"] = pd.to_datetime(df["order_date"])
df["year"] = df["order_date"].dt.year
df["weekday"] = df["order_date"].dt.day_name()
df["month"] = df["order_date"].dt.to_period("M")

resample

Groups a time series by period: 'D' day, 'W' week, 'MS' month start, 'QS' quarter start, 'YS' year start.

df.resample(rule, on=date_col).agg(...)

df["order_date"] = pd.to_datetime(df["order_date"])
monthly = df.resample("MS", on="order_date")["net_sales"].sum()
print(monthly.head())

rolling / pct_change

Moving windows (e.g. 3-month average) and period-over-period growth.

s.rolling(window).mean() | s.pct_change()

df["order_date"] = pd.to_datetime(df["order_date"])
monthly = df.resample("MS", on="order_date")["net_sales"].sum()
trend = pd.DataFrame({
    "sales": monthly,
    "ma_3m": monthly.rolling(3).mean(),
    "mom_pct": monthly.pct_change() * 100,
}).round(1)