Real-World PandasTopic 83 of 837 min read
Pandas Cheat Sheet
The whole Pandas course on one page: tables of the most-used methods for creating, reading, inspecting, selecting, cleaning, transforming, grouping, reshaping, combining and plotting data — with pandas 3 notes.
This page summarises the whole course in quick-reference tables. Each row is one line you will type again and again. Use it while practising, before interviews, and when you forget a method name. The running example is an IPL players DataFrame called df with columns player, team, runs, balls, date.
Definition
Pandas is a Python library for working with labelled, tabular data. Its two core objects are the Series — a one-dimensional labelled array with one dtype — and the DataFrame — a two-dimensional table of labelled rows (the index) and labelled columns, where each column is a Series.
Create & load
| Task | Code |
|---|---|
| Series from a list | pd.Series([741, 417], index=["Virat", "Rohit"], name="runs") |
| DataFrame from a dict | pd.DataFrame({"player": [...], "runs": [...]}) |
| From a list of dicts | pd.DataFrame([{"player": "Virat", "runs": 741}, ...]) |
| Read CSV | pd.read_csv("ipl.csv", usecols=[...], dtype={...}, parse_dates=["date"]) |
| Read JSON | pd.read_json("data.json") |
| Read Excel | pd.read_excel("file.xlsx", sheet_name="2024") (needs openpyxl) |
| Write | df.to_csv("out.csv", index=False) · df.to_json(...) · df.to_excel(...) |
Inspect
| Task | Code |
|---|---|
| Size | df.shape · len(df) · df.size |
| First / last / random rows | df.head() · df.tail(3) · df.sample(2) |
| Types & nulls | df.info() · df.dtypes |
| Summary stats | df.describe() · df.describe(include="str") |
| Column / index labels | df.columns · df.index |
| Distinct values | df["team"].unique() · .nunique() · .value_counts() |
| Missing / duplicates | df.isna().sum() · df.duplicated().sum() |
| Memory | df.memory_usage(deep=True) |
Select & filter
| Task | Code |
|---|---|
| One column (Series) | df["runs"] |
| Several columns (DataFrame) | df[["player", "runs"]] |
| By label | df.loc[row_labels, col_labels] — end label included |
| By position | df.iloc[0:3, 1:3] — end position excluded |
| One cell, fast | df.at[0, "runs"] (label) · df.iat[0, 2] (position) |
| Boolean filter | df[df["runs"] > 400] |
| Combine conditions | df[(df["runs"] > 400) & (df["team"] == "MI")] — & = and, ~ = not, the pipe symbol = or; bracket each condition |
| In a list | df[df["team"].isin(["MI", "CSK"])] |
| Query string | df.query("runs > 400 and team == 'MI'") |
| Top / bottom n | df.nlargest(3, "runs") · df.nsmallest(3, "runs") |
| Index | df.set_index("player") · df.reset_index() |
Clean
| Task | Code |
|---|---|
| Drop missing | df.dropna() · df.dropna(subset=["runs"]) · thresh= |
| Fill missing | df.fillna(0) · df.fillna({"city": "Unknown"}) · s.ffill() |
| Duplicates | df.drop_duplicates() · subset=, keep="first"/"last"/False |
| Change type | df["runs"].astype(int) · pd.to_numeric(s, errors="coerce") |
| Rename | df.rename(columns={"old": "new"}) |
| Replace values | df.replace({"Bengaluru": "Bangalore"}) |
| Drop columns/rows | df.drop(columns=["umpire1"]) · df.drop(index=[0, 1]) |
| Update some rows | df.loc[df["runs"] > 500, "badge"] = "Gold" |
Strings (.str) & dates (.dt)
| Task | Code |
|---|---|
| Case / spaces | s.str.lower() · .upper() · .title() · .strip() |
| Contains / starts | s.str.contains("Kohli", case=False) · .startswith("M") |
| Split / first word | s.str.split(" ").str[0] |
| Replace / length | s.str.replace("-", " ") · s.str.len() |
| Parse dates | pd.to_datetime(df["date"]) · format="%d-%m-%Y" |
| Date parts | s.dt.year · .month · .day_name() · .month_name() |
| Resample time series | df.set_index("date").resample("ME")["runs"].sum() ("ME" = month end in pandas 3) |
Transform
| Task | Code |
|---|---|
| New column | df["sr"] = df["runs"] / df["balls"] * 100 |
| Chainable new column | df.assign(sr=lambda d: d["runs"] / d["balls"] * 100) |
| Per-value function | s.apply(func) · s.map(func) |
| Look-up / replace | s.map({"MI": "Mumbai Indians"}) (unmatched → NaN) |
| Row-wise logic | df.apply(func, axis=1) (slow — vectorise if possible) |
| Every cell | df.map(func) (replaces applymap, removed in pandas 3) |
| Two outcomes | np.where(cond, a, b) |
| Many outcomes | np.select([c1, c2], [v1, v2], default=v) |
| Numeric bands | pd.cut(s, bins=[0, 250, 500, 1000], labels=[...]) · pd.qcut(s, 4) |
| Keep / replace by condition | s.where(cond, other) · s.mask(cond, other) · s.clip(0, 500) |
| Sort / rank | df.sort_values("runs", ascending=False) · s.rank() |
| Running / previous | s.cumsum() · s.diff() · s.shift(1) · s.pct_change() |
Aggregate & group
| Task | Code |
|---|---|
| Single stats | s.sum() · .mean() · .median() · .max() · .std() · .count() |
| Several stats | df[["runs", "balls"]].agg(["min", "max", "mean"]) |
| Group + one stat | df.groupby("team")["runs"].sum() |
| Group + many stats | df.groupby("team")["runs"].agg(["count", "mean", "max"]) |
| Named aggregation | df.groupby("team").agg(total=("runs", "sum"), best=("runs", "max")) |
| Several keys | df.groupby(["season", "team"])["runs"].sum() |
| Same-shape result | df.groupby("team")["runs"].transform("mean") |
| Keep some groups | df.groupby("team").filter(lambda g: len(g) >= 2) |
| Correlation | df.corr(numeric_only=True) · s1.corr(s2) |
Reshape
| Task | Code |
|---|---|
| Long → wide (unique pairs) | df.pivot(index="player", columns="season", values="runs") |
| Long → wide + aggregate | df.pivot_table(index="team", columns="season", values="runs", aggfunc="sum", fill_value=0, margins=True) |
| Wide → long | df.melt(id_vars="player", var_name="season", value_name="runs") |
| Frequency table | pd.crosstab(df["team"], df["season"]) |
| Transpose | df.T |
| Stack / unstack | s.unstack() · df.stack() |
Combine
| Task | Code |
|---|---|
| SQL-style join on a column | pd.merge(left, right, on="player", how="inner") |
| Join types | how="inner" (matches only) · "left" · "right" · "outer" (everything) |
| Different key names | pd.merge(a, b, left_on="pid", right_on="id") |
| Track the source | pd.merge(a, b, on="player", how="outer", indicator=True) |
| Join on the index | left.join(right, how="left") |
| Stack rows | pd.concat([df1, df2], ignore_index=True) |
| Side by side | pd.concat([df1, df2], axis=1) |
Iterate (only when you must)
| Method | Gives | Note |
|---|---|---|
df.items() | (column, Series) | per column |
df.iterrows() | (index, row Series) | slowest, loses dtypes |
df.itertuples() | namedtuple per row | faster, keeps dtypes |
Plot (needs matplotlib; Jupyter/Colab)
| Chart | Code |
|---|---|
| Bar / horizontal bar | s.value_counts().plot(kind="bar") · kind="barh" |
| Line over time | df.plot(x="season", y="runs", kind="line") |
| Distribution | s.plot(kind="hist", bins=20) · kind="box" · kind="kde" |
| Relationship | df.plot(kind="scatter", x="horsepower", y="price") |
| Parts of a whole | s.plot(kind="pie", autopct="%.1f%%") |
Pandas 3 changes to remember
| Old (pandas 1/2 tutorials) | Pandas 3 |
|---|---|
Text columns shown as object | dedicated str dtype |
df["a"][0] = 5 sometimes worked (with SettingWithCopyWarning) | never modifies df (Copy-on-Write); use df.loc[0, "a"] = 5 |
df.applymap(f) | df.map(f) |
inplace=True common | discouraged — reassign: df = df.dropna() |
Frequency "M", "H", "T" | "ME" (month end), "h" (hour), "min" (minute) |
Series.apply(..., convert_dtype=...) | removed — use .astype() |
Common mistakes (whole course)
- ❌
df[df["runs"] > 400 and df["team"] == "MI"]✅&with brackets around each condition. - ❌
df["a"][mask] = x✅df.loc[mask, "a"] = x. - ❌ Forgetting to assign:
df.dropna()alone does nothing todf. ✅df = df.dropna(). - ❌
locwith positions /ilocwith labels. ✅loc= labels (end included),iloc= positions (end excluded). - ❌ Looping with
iterrowsfor maths. ✅ Vectorise. - ❌ Merging on keys with different spellings/types. ✅ Clean and
astypekeys first; check row counts after the merge.
Interview tip
Q: Explain the split–apply–combine idea behind groupby.
A: Split the rows into groups by a key (e.g. team), apply a function to each group independently (sum, mean, a custom function), and combine the per-group results into a new Series/DataFrame indexed by the key. df.groupby("team")["runs"].sum() does all three steps in one line.
Key points to remember
- Series = 1D labelled array with one dtype; DataFrame = 2D table of labelled rows and columns, each column a Series.
- Always inspect first: shape, head, info, describe, isna().sum(), duplicated().sum().
- loc selects by label (end included), iloc by position (end excluded); filter with & | ~ and brackets.
- Clean with dropna/fillna, drop_duplicates, astype/to_datetime, str methods, replace — and reassign the result.
- Transform vectorised first (column maths, np.where, np.select, pd.cut, map); apply/iterrows only when needed.
- groupby = split–apply–combine; pivot_table/melt reshape; merge/join/concat combine tables.
- Pandas 3: str dtype, Copy-on-Write (use loc to assign), DataFrame.map instead of applymap, 'ME'/'h'/'min' aliases.
Try it yourself
The whole course in one script
loc vs iloc, map vs replace, cut — quick check
Practice: build your own summary
Revision Flashcards
Tap a card to flip it and see the answer.