Pandas Cheat Sheet

Python pandas cheat sheet for pandas 2.x: read_csv and Parquet IO, loc/iloc selection, cleaning, groupby/agg, merge, pivot_table, melt, datetime, .str and performance tips.

Import & IO

Import

import pandas as pd
import numpy as np

Read CSVdf = pd.read_csv("data.csv")

Read CSV, typed

df = pd.read_csv(
    "data.csv",
    usecols=["id", "price", "created_at"],
    dtype={"id": "int64"},
    parse_dates=["created_at"],
)

Selecting columns and dtypes up front saves memory and parsing time

Arrow-backed dtypesdf = pd.read_csv("data.csv", dtype_backend="pyarrow")pandas 2.0+; faster strings and nullable types (requires pyarrow)

Read in chunks

for chunk in pd.read_csv("big.csv", chunksize=100_000):
    process(chunk)

Stream files larger than memory

Read Parquetdf = pd.read_parquet("data.parquet", columns=["id", "price"])Needs pyarrow (or fastparquet); keeps dtypes, much faster than CSV

Read Exceldf = pd.read_excel("report.xlsx", sheet_name="Sales")Needs openpyxl for .xlsx

Read JSON linesdf = pd.read_json("events.jsonl", lines=True)

Read SQLdf = pd.read_sql("SELECT * FROM orders", con=engine)engine = SQLAlchemy engine or DBAPI connection

From dictdf = pd.DataFrame({"name": ["Ana", "Ben"], "age": [31, 27]})

Write CSVdf.to_csv("out.csv", index=False)index=False avoids an extra unnamed column

Write Parquetdf.to_parquet("out.parquet", index=False)

Inspecting Data

First / last rows

df.head()  # 5 rows
df.tail(3)

Shapedf.shape # (rows, columns)

Summarydf.info()Columns, non-null counts, dtypes, memory

Statisticsdf.describe(include="all")

Column dtypesdf.dtypes

Unique countsdf.nunique()

Value countsdf["city"].value_counts(dropna=False, normalize=True)normalize=True gives proportions

Memory usagedf.memory_usage(deep=True).sum()deep=True measures object/string columns accurately

Selecting & Filtering

One column (Series)df["price"]

Several columnsdf[["name", "price"]]

By label (loc)

df.loc[10, "price"]
df.loc[:, "name":"price"]

Label slices include the end label

By position (iloc)df.iloc[0:5, 0:2]Position slices exclude the end, like Python lists

Boolean filterdf[df["age"] > 30]

Combine conditionsdf[(df["age"] > 30) & (df["city"] == "Pune")]Use & | ~ with parentheses, not and / or

Filter + columnsdf.loc[df["age"] > 30, ["name", "age"]]

isindf[df["city"].isin(["Pune", "Delhi"])]

query()

min_age = 30
df.query("age > @min_age and city == 'Pune'")

@ references local variables

Set a value safelydf.loc[df["qty"] < 0, "qty"] = 0One .loc call; avoid chained df[mask][col] = ...

Cleaning

Count missingdf.isna().sum()

Drop missingdf = df.dropna(subset=["email"])

Fill missingdf = df.fillna({"qty": 0, "note": ""})

Forward filldf["price"] = df["price"].ffill()fillna(method=...) is deprecated; use ffill()/bfill()

Find duplicatesdf.duplicated(subset=["id"]).sum()

Drop duplicatesdf = df.drop_duplicates(subset=["id"], keep="last")

Change dtypesdf = df.astype({"qty": "int64", "status": "category"})

Parse numbersdf["price"] = pd.to_numeric(df["price"], errors="coerce")Unparseable values become NaN

Nullable dtypesdf = df.convert_dtypes()Int64, string, boolean types that support missing values

Rename columnsdf = df.rename(columns={"Order ID": "order_id"})

Normalize headersdf.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

Drop columnsdf = df.drop(columns=["tmp", "debug"])

Transforming Columns

Add column (chainable)df = df.assign(total=lambda d: d["price"] * d["qty"])

Vectorized arithmeticdf["total"] = df["price"] * df["qty"]Far faster than apply()

Map valuesdf["gender"] = df["g"].map({"M": "Male", "F": "Female"})Unmapped values become NaN

Conditional columndf["tier"] = np.where(df["spend"] > 1000, "gold", "std")

Many conditions

df["band"] = np.select(
    [df.age < 18, df.age < 65],
    ["minor", "adult"],
    default="senior",
)

Bin valuesdf["age_band"] = pd.cut(df["age"], bins=[0, 18, 65, 120], labels=["minor", "adult", "senior"])

Series.applydf["slug"] = df["title"].apply(slugify)Python-level loop; use only when no vectorized method exists

Row-wise applydf.apply(lambda r: r.a + r.b, axis=1)Slow on large frames; prefer column math

Element-wise mapdf[["a", "b"]].map(round)DataFrame.map replaces applymap (pandas 2.1+)

Sortdf.sort_values(["city", "sales"], ascending=[True, False])

GroupBy & Aggregation

Group and sumdf.groupby("city")["sales"].sum()

Several keys, flat resultdf.groupby(["city", "year"], as_index=False)["sales"].sum()

Named aggregation

df.groupby("city").agg(
    total=("sales", "sum"),
    avg=("sales", "mean"),
    orders=("order_id", "nunique"),
)

Group sizesdf.groupby("city").size()

Transform (same length)df["share"] = df["sales"] / df.groupby("city")["sales"].transform("sum")Broadcasts group results back to every row

Rank within groupdf["rank"] = df.groupby("city")["sales"].rank(ascending=False)

Running total per groupdf["cum"] = df.groupby("user_id")["amount"].cumsum()

Filter groupsdf.groupby("city").filter(lambda g: len(g) >= 10)

Categorical keysdf.groupby("status", observed=True)["id"].count()observed=True skips unused categories (the future default)

Crosstabpd.crosstab(df["city"], df["plan"], margins=True)

Merge, Join & Concat

Left joinout = orders.merge(users, on="user_id", how="left")how: inner (default), left, right, outer, cross

Different key namesorders.merge(users, left_on="uid", right_on="id")

Validate keysorders.merge(users, on="user_id", validate="many_to_one")Raises if users has duplicate keys

Track match source

m = a.merge(b, on="id", how="outer", indicator=True)
m["_merge"].value_counts()

Suffixesa.merge(b, on="id", suffixes=("_old", "_new"))

Stack rowsdf = pd.concat([df1, df2], ignore_index=True)DataFrame.append was removed in pandas 2.0

Add one rowdf = pd.concat([df, pd.DataFrame([new_row])], ignore_index=True)Collect rows in a list and concat once inside loops

Side by sidepd.concat([left, right], axis=1)Aligns on the index

Join on indexdf.join(other, on="key", how="left")

Reshaping

Pivot table

df.pivot_table(
    index="city", columns="year",
    values="sales", aggfunc="sum", fill_value=0,
)

Pivot (no aggregation)df.pivot(index="date", columns="metric", values="value")Fails on duplicate index/column pairs; use pivot_table

Wide to long (melt)df.melt(id_vars=["id"], var_name="month", value_name="sales")

Stack / unstack

df.stack()
df.unstack()

Move column level to index and back

Explode listsdf.explode("tags")One row per list element

Index in/out

df.set_index("id")
df.reset_index()

Dates & Times

Parse datesdf["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")An explicit format is faster and avoids ambiguity

Date parts

df["date"].dt.year
df["date"].dt.month
df["date"].dt.day_name()

Time zonesdf["ts"].dt.tz_localize("UTC").dt.tz_convert("Asia/Kolkata")

Date rangepd.date_range("2026-01-01", periods=7, freq="D")

Resampledf.set_index("date")["sales"].resample("ME").sum()'ME' = month end; 'M' is deprecated since pandas 2.2

Rolling windowdf.set_index("date")["sales"].rolling("7D").mean()

Shift / change

df["prev"] = df["sales"].shift(1)
df["pct"] = df["sales"].pct_change()

Round timestampsdf["ts"].dt.floor("h")Lowercase 'h' since pandas 2.2

String Methods (.str)

Case & trimdf["name"].str.strip().str.lower()

Containsdf[df["email"].str.contains("@gmail", case=False, na=False)]na=False treats missing values as no match

Replace (regex)df["txt"].str.replace(r"\s+", " ", regex=True)

Split to columnsdf[["first", "last"]] = df["name"].str.split(" ", n=1, expand=True)

Extractdf["num"] = df["code"].str.extract(r"(\d+)", expand=False)

String dtypedf["name"] = df["name"].astype("string[pyarrow]")

Performance & Copy-on-Write

Vectorize firstColumn arithmetic, .str, .dt and np.where beat apply(), iterrows() and Python loops by 10-100x

Iterate if you must

for row in df.itertuples(index=False):
    print(row.name)

itertuples is much faster than iterrows

Category dtypedf["country"] = df["country"].astype("category")Big memory savings for low-cardinality text

Load lessUse usecols, dtype and Parquet with columns= to read only what you need

Enable Copy-on-Writepd.options.mode.copy_on_write = Truepandas 2.x opt-in; the default behaviour in pandas 3.0

CoW in practice

sub = df[df["a"] > 0]
sub["b"] = 1  # never modifies df

With CoW, derived objects act as copies; no SettingWithCopyWarning

Explicit copysub = df[mask].copy()Without CoW, copy subsets you plan to modify

Avoid growing in loops

rows = []
for x in data:
    rows.append({...})
df = pd.DataFrame(rows)

Build once instead of concat in every iteration

Keep the references coming

One email a week: new and trending developer tools, fresh comparisons, and what shipped. Unsubscribe in one click.