pandas Cheat Sheet

1 Load & create

import pandas as pd

df = pd.read_csv("data.csv")
df = pd.read_excel("book.xlsx", sheet_name=0)
df = pd.read_sql(query, conn)

df = pd.DataFrame({
    "name": ["A", "B"],
    "price": [9.9, 12.0],
})

pd.read_csv args worth knowing: sep, usecols, parse_dates, dtype, nrows.

2 Inspect

3 Select columns & rows

df["price"]              # one column (Series)
df[["name", "price"]]    # many columns

df.loc[0, "name"]        # by label
df.iloc[0, 1]            # by position
df.loc[df.price > 10]    # rows by condition
df.iloc[:5, :2]          # first 5 rows, 2 cols

.loc = labels, .iloc = integer positions. Keep them straight and most indexing pain disappears.

4 Filter rows

df[df.price > 10]
df[(df.price > 10) & (df.category == "A")]
df[df.category.isin(["A", "B"])]
df[df.name.str.contains("wid", case=False)]
df.query("price > 10 and category == 'A'")

Combine conditions with & / | (not and/or) and wrap each in parentheses.

5 New & changed columns

df["tax"]   = df.price * 0.06
df["total"] = df.price + df.tax
df["tier"]  = df.price.apply(
    lambda p: "hi" if p > 100 else "lo")

df = df.rename(columns={"price": "unit_price"})
df = df.assign(margin=lambda d: d.total - d.cost)
df["date"] = pd.to_datetime(df["date"])

6 Missing data

df.isna().sum()          # nulls per column
df.dropna()              # drop rows with any NaN
df.dropna(subset=["price"])
df.fillna(0)
df["price"].fillna(df.price.median())
df.drop_duplicates()

Decide per column: drop, fill with a constant, or fill with a statistic. Don't blanket-dropna() a whole table by reflex.

7 Group & aggregate

df.groupby("category").price.mean()

df.groupby("category").agg(
    n=("price", "count"),
    revenue=("price", "sum"),
    avg=("price", "mean"),
).reset_index()

df.groupby(["region", "category"]).size()

The split → apply → combine workhorse. reset_index() turns the group keys back into normal columns.

8 Merge & concat

pd.merge(orders, customers,
         on="customer_id", how="left")
# how = 'inner' | 'left' | 'right' | 'outer'

pd.concat([df_jan, df_feb])        # stack rows
pd.concat([df_a, df_b], axis=1)    # side by side

merge is a SQL join; concat just glues frames together. Check .shape after a merge to catch accidental row explosions.

9 Reshape & sort

df.sort_values("price", ascending=False)
df.nlargest(10, "price")

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

df.melt(id_vars="date",           # wide → long
        var_name="metric", value_name="value")

10 Export & tips

df.to_csv("out.csv", index=False)
df.to_excel("out.xlsx", index=False)
df.to_parquet("out.parquet")   # fast + small

• Chain operations, but avoid the SettingWithCopyWarning — assign with .loc, not df[df.x][…] =.
• df.pipe(fn) keeps long transformations readable.
• Vectorise: use column maths over for loops — often 100× faster.