📋 Pandas Analyst Path · cheat sheet, built from the lessons

1.1Anatomy of a DataFrame

📘 Concept

• A DataFrame is a table. Each column is a Series (values + an index of row labels).

• Types you'll meet: int64, float64 (numbers), object/str (text), bool, datetime64 (dates).

1.2Selecting columns

📘 Concept

• df["col"] → a Series (one column).
• df[["col1", "col2"]] → a DataFrame. The outer brackets mean "select", the inner ones are a Python list.
• df.col also works for simple names, but fails for names with spaces and clashes with methods. Prefer brackets.
• df.rename(columns={"old": "new"}) returns a renamed copy.

1.3Counting categories: value_counts, unique, nunique

📘 Concept

💡 A boolean trick you'll use daily: (s == "X").mean() = share of rows where s is X (True=1, False=0).

🧩 A Series has two parts. The result of value_counts() looks like a two-column table, but it is a Series:

• the left column is the index: the labels (here: the traffic sources) → .index
• the right column is the values: the counts → .values

So "which ones?" questions (names, top 3, the biggest) are answered by the index, and "how many?" questions by the values. .head(3) keeps the first 3 rows, .index takes their labels, list(...) makes them a plain list (same trick as list(products.columns) in 1.2).

1.4Sorting and top‑N

📘 Concept

• df.sort_values("col") ascending; ascending=False for descending.
• Several keys: df.sort_values(["a", "b"], ascending=[True, False]).
• Top N: df.sort_values("col", ascending=False).head(N) or df.nlargest(N, "col").
• Sorting returns a new DataFrame. The original is unchanged unless you assign: df = df.sort_values(...).

2.1Boolean masks: the filter of pandas

📘 Concept 1. A condition on a column gives a Series of True/False: products["retail_price"] > 100. 2. Put it inside df[...] and only the True rows stay: products[products["retail_price"] > 100]. 3. Combine with & (and), | (or), ~ (not), and each condition goes in parentheses.

⚠️ Traps: and / or → ValueError: The truth value of a Series is ambiguous. Use & / |. Missing parentheses: df["a"] > 1 & df["b"] < 5 is evaluated in the wrong order → wrong rows or an error.

2.2loc and iloc: rows and columns together

📘 Concept

• df.loc[rows, cols] selects by label (index labels, column names, or a boolean mask for rows).
• df.iloc[rows, cols] selects by position (0, 1, 2…), like a Python list.
• df.set_index("col") makes a column the row labels, so df.loc[label] finds a row by, e.g., its id.
• Changing values: df.loc[mask, "col"] = value sets the value only where the mask is True.

⚠️ Biggest trap: loc[0:5] includes 5 (labels), iloc[0:5] excludes 5 (positions). ⚠️ Second trap: df[df["a"] > 1]["b"] = 0 does nothing useful (it changes a temporary copy, and pandas warns with SettingWithCopyWarning). Write df.loc[df["a"] > 1, "b"] = 0.

3.1Calculated columns

📘 Concept

• df["new"] = expression creates (or overwrites) a column. The expression works on whole columns at once (vectorized), so no loops are needed: df["total"] = df["price"] * df["qty"].
• Use .round(2) for display, but keep the full precision for calculations.
• Work on a copy (prod = products.copy()) when you don't want to change the original. b = a is not a copy, just a second name for the same table.

3.2Categories from conditions: np.where, np.select, map, apply

📘 Concept

3.3Binning numbers: pd.cut and pd.qcut

📘 Concept

• pd.cut(s, bins=[0, 18, 35, 100], labels=["kid", "young", "adult"]): fixed edges you choose. Intervals are right-closed by default: (18, 35] contains 35 but not 18.
• pd.qcut(s, q=4, labels=["Q1", "Q2", "Q3", "Q4"]): equal-sized groups by quantiles (each ≈25% of rows).
• The result is a categorical with a natural order, so value_counts(sort=False) keeps that order.

3.4Text columns: the .str accessor

📘 Concept: .str applies string methods to every value of a column: .str.lower() .str.upper() .str.title() .str.strip() .str.len() .str.contains("x", case=False, na=False) .str.startswith("x") .str.replace("a", "b") .str.split(" ") → lists, .str[0] → first element / first character.

⚠️ Trap: brand has missing values. str.contains returns NaN for them and the mask fails with "Cannot mask with non-boolean array containing NA". Fix: na=False.

4.1Datetime basics

📘 Concept

• Text → dates: pd.to_datetime(s). European dd/mm/yyyy needs dayfirst=True (or format="%d/%m/%Y"). Bad values → errors="coerce" turns them into NaT (missing date).
• Parts of a date with the .dt accessor: .dt.year .dt.month .dt.day_name() .dt.hour .dt.date .dt.to_period("M") (a month like 2026-09).
• Subtracting dates gives a Timedelta: .dt.days (whole days) or .dt.total_seconds() / 86400 (fractional days).
• Compare with strings directly: o["created_at"] >= "2026-01-01".

4.2Filtering by date ranges

📘 Use half-open intervals: start <= date < first day after the period.

⚠️ Trap: o["created_at"].between("2026-07-01", "2026-09-30") stops at 2026-09-30 00:00:00, so almost the whole last day is lost. Timestamps have hours.

5.1Reading files

📘 pd.read_csv(path, sep=",", decimal=".", encoding="utf-8", parse_dates=[...], dtype={...})

• European Excel exports often use sep=";" and decimal="," (Portugal too!).
• Always look right after reading: .head(), .shape, .dtypes. Is a number column read as text? Did everything land in one column?
• Excel: pd.read_excel("file.xlsx", sheet_name="Sheet1"). Saving: df.to_csv("out.csv", index=False).

5.2Missing values

📘 Concept

Drop or fill? Drop when few rows are affected and they're random. Fill when the value has a clear meaning (missing spend = no purchase → 0) or you'd lose too much data (age → median). Always say which you did.

⚠️ Trap: text like "unknown" or "" in a number column is not NaN to pandas. It makes the whole column text (object). isna() won't count it, and you only see it after converting (5.4).

5.3Duplicates

📘 df.duplicated() marks rows that repeat an earlier row (all columns equal). df.duplicated(subset=["id"]) compares only some columns. df.drop_duplicates(subset=..., keep="first" | "last") removes them.

The export has two kinds of duplicates: exact copy-paste repeats, and customers exported twice whose later row has an updated spend. For those, the last row is the truth.

5.4Fixing text and types

📘 Concept

• Normalise text before comparing it: s.str.strip().str.title() (" united states" → "United States").
• Unify spellings with s.replace({"old": "new"}). Values not in the dict stay as they are. s.map(dict) turns every unmapped value into NaN.
• Text → numbers: pd.to_numeric(s, errors="coerce") turns junk ("unknown") into NaN instead of crashing.
• Impossible values → NaN: s.where(condition) keeps values where the condition is True, NaN elsewhere.
• Text → dates: pd.to_datetime(s, dayfirst=True).

5.5Outliers

📘 Concept: the IQR rule (box-plot rule, a Data Mining exam favourite): IQR = Q3 − Q1, outliers are below Q1 − 1.5·IQR or above Q3 + 1.5·IQR. Quantiles: s.quantile(0.25), s.quantile(0.75).

What to do with them? Investigate first. A $999 coat is real. An age of 230 is an error. Options: keep (and say so) · remove · cap (s.clip(lower, upper), called winsorizing) · analyse separately. For modelling (Part 12) capping is common. For revenue reporting, you never delete real sales.

6.1merge: the SQL JOIN of pandas

📘 Concept: left.merge(right, on="key", how="left")

• Different key names: left_on="user_id", right_on="id".
• Same column name in both tables (not the key) → suffixes _x, _y. Name them: suffixes=("_order", "_item").
• indicator=True adds a _merge column: both / left_only / right_only. That's how you find rows without a match (an anti-join).

⚠️ The #1 merge bug: row explosion. If the key isn't unique on the side you think it is, rows multiply and your revenue doubles silently. Always compare row counts before and after, or let pandas check it: validate="many_to_one" raises an error if the right side has duplicate keys.

6.2concat: stacking tables

📘 pd.concat([df1, df2, df3], ignore_index=True) puts tables under each other (same columns), for example monthly exports. axis=1 puts them side by side (rare; prefer merge). ignore_index=True renumbers the rows 0…n−1.

7.1split → apply → combine

📘 df.groupby("key")["value"].agg_function() = split rows into groups by key, apply a function to each group's values, combine into a result indexed by the key.

`` category sale_price groupby("category")["sale_price"].sum() Jeans 100 ─┐ Jeans 50 ─┴─► Jeans 150 Socks 10 ───► Socks 10 ``

⚠️ Trap: one order has several items, so count() on order_id counts items. Number of orders = nunique().

7.2Several metrics at once: agg and named aggregation

📘 Concept ``python sales.groupby("category").agg( revenue=("sale_price", "sum"), # new_column=(source_column, function) orders=("order_id", "nunique"), items=("order_item_id", "count"), ) ` Named aggregation gives clean column names. Ratios (AOV, margin) are computed **after** aggregating: kpis["aov"] = kpis["revenue"] / kpis["orders"]`.

⚠️ Trap: the average of ratios ≠ the ratio of totals. AOV is total revenue / total orders, not the mean of item prices.

7.3Several keys and unstack

📘 groupby(["a", "b"]) gives a result with a two-level (Multi)Index. .unstack() moves the inner level into columns, turning a long list into a readable matrix. .stack() does the reverse.

7.4transform: group values back on every row

📘 agg gives one row per group. transform gives one value per original row (the group's result repeated), so you can compare each row to its group: ``python sales["cat_avg"] = sales.groupby("category")["sale_price"].transform("mean") sales["share_of_cat"] = sales["sale_price"] / sales.groupby("category")["sale_price"].transform("sum") `` Use it for: share within a group, difference from the group average, ranking inside groups.

7.5pivot_table and crosstab

📘 Concept

• df.pivot_table(index="a", columns="b", values="v", aggfunc="sum", fill_value=0, margins=False) is the Excel pivot table: groupby on two keys + unstack in one call.
• pd.crosstab(df["a"], df["b"]) counts combinations. normalize="index" gives shares per row (each row sums to 1).

💡 Rate trick: the mean of a boolean column is a rate: items.assign(is_returned=items["status"].eq("Returned")).groupby("category")["is_returned"].mean()

8.1Anatomy: figure, axes, and who draws what

📘 Concept

• matplotlib is the engine. A Figure is the canvas, an Axes (ax) is one chart on it. Standard start: fig, ax = plt.subplots(figsize=(8, 4)).
• pandas plots straight from data: series.plot(kind="bar", ax=ax). Fast for quick looks.
• seaborn draws statistical charts from a DataFrame with column names: sns.histplot(data=df, x="col", hue="group", ax=ax). It returns the ax.
• Finish every chart: ax.set_title(...), ax.set_xlabel(...), ax.set_ylabel(...). Many graders and managers mark a chart down when the labels are missing.

In the tasks below, keep your chart in a variable called ax. The check reads it (number of bars, title, labels…). show_me("8.x") draws a reference chart.

8.2Distributions: histogram and box plot

📘 "What do typical values look like? Is it skewed? Outliers?"

• sns.histplot(data=df, x="col", bins=30). Add hue="group" to compare groups, stat="density", common_norm=False to compare shapes of groups of different sizes, log_scale=True for long right tails (prices, revenue).
• sns.boxplot(data=df, x="group", y="col"): median, quartiles, IQR whiskers, outliers as dots (Part 5 rule!).

8.3Comparing categories: bar charts

📘 Aggregate first, then plot (pandas does the math, the chart only shows it).

• Many categories or long names → horizontal bars, sorted, biggest on top. With .plot.barh() the first row is drawn at the bottom, so sort ascending first: s.sort_values().plot.barh(ax=ax).
• sns.countplot(data=df, y="col", order=df["col"].value_counts().index) counts rows for you.
• Bars must start at 0, otherwise small differences look huge.

8.4Relationships: scatter and heatmap

📘 sns.scatterplot(data=df, x="a", y="b", alpha=0.3, s=10): with many points, use transparency (alpha) or a sample (df.sample(5000, random_state=0)). sns.heatmap(matrix, annot=True, fmt=".2f", cmap="Blues") colours a table, for example a correlation matrix (df[cols].corr()) or a pivot table.

8.5Trends: line charts

📘 Time on the x-axis → a line. Aggregate per month first (Part 9 goes deeper): monthly = sales.groupby(sales["created_at"].dt.to_period("M"))["sale_price"].sum(). Drop the incomplete current month. A half month always looks like a crash.

8.6Choosing the chart and making it honest

Checklist before sending: the title states the insight ("Returns doubled in Q3"), not just the topic ("Returns") · units on axes · sorted bars · bars start at zero · no incomplete periods · readable labels (rotation=45 or horizontal bars) · plt.tight_layout().

9.1Grouping by time

📘 Concept

• Month key: df["created_at"].dt.to_period("M") (a Period, e.g. 2026-09), then a normal groupby. Weeks: "W", quarters: "Q", years: "Y".
• Alternative: df.set_index("created_at").resample("MS")["sale_price"].sum() (time index required; "MS" = month start).
• Compare periods with pd.Period("2025-01", "M").
• ⚠️ Our data ends on 30 Sep 2026, so September is complete. In real life, the current month is always incomplete. Cut it off or label it, or your chart shows a fake crash.

9.2Change over time

📘 Concept

⚠️ Rows must be sorted by time and have no missing months, otherwise shift(12) is not "a year ago".

10.2Pareto: do 20% of customers bring 80% of revenue?

📘 Sort customers by revenue (desc), compute the cumulative share s.cumsum() / s.sum(), and read where the top 20% land. The same logic on products is ABC analysis (A = items making the first 80% of revenue, B = next 15%, C = the rest).

10.3Cohorts and retention

📘 Steps: (1) each order's month; (2) each customer's cohort = month of their first order (transform("min")!); (3) months since first order = (year diff) × 12 + (month diff); (4) pivot: cohorts × months-since, counting distinct customers; (5) divide each row by its month-0 size.

10.4RFM segmentation

📘 Three numbers per customer: Recency (days since last order: lower is better), Frequency (number of orders), Monetary (revenue). Score each 1–5 with pd.qcut, then name segments with rules (np.select).

⚠️ Trap: most customers have exactly 1 order, so pd.qcut(frequency, 5) fails with "Bin edges must be unique". The standard fix: rank first, pd.qcut(s.rank(method="first"), 5, ...). Be aware it splits tied customers arbitrarily. Another option is fixed bins with pd.cut (e.g. 1 / 2 / 3+ orders). For recency the labels go reversed ([5, 4, 3, 2, 1]): fewer days = better score.

10.5Churn: who have we lost?

> A customer is churned if they have made no paid order in the last N days before TODAY.

How to pick N? Look at how long returning customers usually wait between orders. If N is shorter than a normal gap, you call loyal customers "churned".

Who can churn? Only customers who have existed for at least N days. Someone whose first order was last week cannot have "stopped buying" yet. Including them makes churn look lower than it is. So: eligible = first_order <= TODAY − N days.

Churn rate = churned eligible customers / all eligible customers. Then compare churn between groups to find levers.

10.6Conversion funnel (website sessions, Jul–Sep 2026)

📘 A funnel counts how many sessions reach each step: visit → product page → cart → purchase. Step conversion = this step / previous step. Where the drop is biggest, there's the biggest opportunity.

12.1Correlation

📘 df.corr() gives a Pearson matrix (linear relationships, sensitive to outliers). df.corr(method="spearman") uses ranks, so it handles monotonic relationships and is robust to outliers. Values run from −1 to 1. Correlation ≠ causation.

12.2Encoding categorical variables

📘 Models need numbers.

• Nominal (no order: country, traffic source) → one-hot: pd.get_dummies(df, columns=[...], drop_first=True). drop_first avoids a redundant column, which matters for linear models.
• Ordinal (has order: S < M < L) → map({"S": 1, "M": 2, "L": 3}).

12.3Scaling

📘 Distance-based methods (k-means, kNN) get dominated by the column with the biggest numbers (monetary in $ vs frequency 1–4).

• Standardisation (z-score): (x − mean) / std → mean 0, std 1. StandardScaler uses the population std (ddof=0), while pandas' .std() defaults to ddof=1. With 60k rows you won't see the difference, but on a 5-row exam example you will.
• Min-max: (x − min) / (max − min) → 0…1.
• Skewed money columns: apply np.log1p first (log of 1 + x), or the clusters will just split big spenders from everyone else.

12.4k-means segmentation

📘 KMeans(n_clusters=k, n_init=10, random_state=42).fit_predict(X) assigns each customer to the nearest of k centres. Choose k with the elbow of the inertia curve (plus business sense: can marketing run 4 campaigns? 10?). Then profile the clusters by averaging the unscaled features per cluster, and name them.

A.2Cheat sheet

A.3Error decoder: "why didn't it work?"

A.4Glossary EN → RU