pandas cheat sheet for data analysts

First and last rows

orders.head(10)
orders.tail(3)

Size, columns, types

orders.shape          # (rows, columns)
orders.dtypes
orders.info()

Summary statistics

products.describe()                 # numeric columns
products.describe(include="object")  # text columns

Count each value

orders["status"].value_counts()
orders["status"].value_counts(normalize=True).round(3)  # shares

How many distinct values

orders["customer_id"].nunique()

One column is a Series, a list gives a DataFrame

products["unit_price"]                       # Series
products[["name", "category", "unit_price"]]  # DataFrame

Rows and columns by label

products.loc[products["category"] == "Lighting", ["name", "unit_price"]]

Rows and columns by position

products.iloc[:5, :3]

One condition

orders[orders["channel"] == "marketplace"]

Several conditions

products[(products["category"] == "Decor") & (products["unit_price"] < 50)]

Use & | ~ (not and/or/not) and wrap every condition in brackets.

In a list, in a range

products[products["category"].isin(["Decor", "Kitchen"])]
products[products["unit_price"].between(50, 100)]  # inclusive both ends

Text contains

customers[customers["email"].str.contains("@gmail.com", regex=False, na=False)]

The same filter as a query string

orders.query("channel == 'app' and shipping_fee > 10")

Sort by several columns

products.sort_values(["category", "unit_price"], ascending=[True, False])

Top 10 by a column

products.nlargest(10, "unit_price")[["name", "unit_price"]]

Clean 0..n index after sorting

products.sort_values("unit_price", ascending=False).reset_index(drop=True)

Arithmetic on whole columns

items = order_items.copy()
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount_pct"] / 100)
items.head()

If/else on a column

products["tier"] = np.where(products["unit_price"] >= 200, "premium", "standard")
products["tier"].value_counts()

Bands with pd.cut

products["band"] = pd.cut(products["unit_price"], bins=[0, 50, 200, np.inf],
                          labels=["under 50", "50-199", "200+"], right=False)
products["band"].value_counts()

right=False makes each band include its left edge: 50 lands in "50-199".

Rows per group

orders.groupby("channel").size()

One number per group

orders.groupby("channel")["shipping_fee"].mean().round(2)

Several numbers per group (named aggregation)

products.groupby("category", as_index=False).agg(
    products=("product_id", "count"),
    avg_price=("unit_price", "mean"),
    max_price=("unit_price", "max"),
)

Two keys, spread into a table

orders.groupby(["channel", "status"]).size().unstack(fill_value=0)

Keep only big groups (like HAVING)

per_customer = orders.groupby("customer_id").size()
per_customer[per_customer >= 20]

Pivot table

orders.pivot_table(index="channel", columns="status",
                   values="order_id", aggfunc="count", fill_value=0)

Inner merge, checked

lines = order_items.merge(products[["product_id", "name", "category"]],
                         on="product_id", how="inner", validate="many_to_one")
len(lines) == len(order_items)

validate= raises an error if the key is not unique where you expect it to be.

Rows with no match (anti-join)

m = customers.merge(orders[["customer_id"]].drop_duplicates(),
                    on="customer_id", how="left", indicator=True)
m[m["_merge"] == "left_only"]

Same thing with isin

customers[~customers["customer_id"].isin(orders["customer_id"])]

Different key names, then group

lines = order_items.merge(products, on="product_id", suffixes=("_paid", "_list"))
lines["revenue"] = lines["quantity"] * lines["unit_price_paid"] * (1 - lines["discount_pct"] / 100)
lines.groupby("category")["revenue"].sum().sort_values(ascending=False).round(2)

Both tables have unit_price: suffixes keep both and say which is which.

Missing values per column

customers.isna().sum()

Rows where a value is missing

products[products["unit_cost"].isna()]

Fill or drop

customers["referral_source"].fillna("unknown").value_counts()
products.dropna(subset=["unit_cost"])

mean() skips NaN

products["unit_cost"].mean()            # average of the known costs
products["unit_cost"].fillna(0).mean()  # treats missing as 0: lower

Count includes or skips NaN

customers["referral_source"].count()        # non-missing only
len(customers)                               # every row
customers["referral_source"].value_counts(dropna=False)

Trim and fix case

customers["state_clean"] = customers["state"].str.strip().str.upper()
customers["state_clean"].value_counts().head()

Split text

customers["email"].str.split("@").str[1].value_counts().head()

Find duplicates on a cleaned key

key = customers["email"].str.strip().str.lower()
customers[key.duplicated(keep=False)].sort_values("email")

Drop duplicates, keep the first

customers.assign(key=customers["email"].str.strip().str.lower()) \
         .drop_duplicates(subset="key", keep="first")

Parse text to dates

pd.to_datetime(pd.Series(["2025-03-01", "not a date"]), errors="coerce")

Parts of a date

orders["order_date"].dt.year.value_counts().sort_index()
orders["order_date"].dt.day_name().value_counts()

Monthly totals

orders.groupby(orders["order_date"].dt.to_period("M")).size().tail(12)

Filter a date range

orders[(orders["order_date"] >= "2024-03-01") & (orders["order_date"] < "2024-04-01")]

Days between two dates

r = (returns.merge(order_items[["order_item_id", "order_id"]], on="order_item_id")
           .merge(orders[["order_id", "order_date"]], on="order_id"))
(r["returned_on"] - r["order_date"].dt.normalize()).dt.days.describe()

Rank inside each group

products["price_rank"] = products.groupby("category")["unit_price"].rank(method="dense", ascending=False)
products[products["price_rank"] <= 3].sort_values(["category", "price_rank"])

Number each row in its group

o = orders.sort_values("order_date")
o["order_n"] = o.groupby("customer_id").cumcount() + 1
o[["customer_id", "order_date", "order_n"]].head()

Running total, change and moving average

monthly = orders.groupby(orders["order_date"].dt.to_period("M")).size()
pd.DataFrame({
    "orders": monthly,
    "running": monthly.cumsum(),
    "mom_pct": (monthly.pct_change() * 100).round(1),
    "avg_3m": monthly.rolling(3).mean().round(1),
})

Average order value (AOV)

done = orders.loc[orders["status"] == "completed", ["order_id"]]
lines = order_items.merge(done, on="order_id")
revenue = (lines["quantity"] * lines["unit_price"] * (1 - lines["discount_pct"] / 100)).sum()
round(revenue / lines["order_id"].nunique(), 2)

Conversion rate by source

(web_sessions.groupby("source")["converted"].mean() * 100).round(2).sort_values(ascending=False)

Return rate by category

lines = order_items.merge(products[["product_id", "category"]], on="product_id")
lines["returned"] = lines["order_item_id"].isin(returns["order_item_id"])
(lines.groupby("category")["returned"].mean() * 100).round(1)