Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 64 ยท Practice and Review

pandas Cheat Sheet

A one-page pandas 3 cheat sheet with runnable examples: create, inspect, select, clean, group, merge, reshape, time series, plotting and performance.

This page packs the whole course into one place: the commands you will use every day, in compact examples you can scan in seconds. Use it to refresh your memory before writing code, while working on the practice exercises, or right before an interview.

Each section links to the lessons that explain the topic in depth, and four live editors let you run the most important patterns. Everything targets pandas 3: Copy-on-Write is always on, text columns have the str dtype, and import pandas as pd is assumed. In the snippets, df is a small table with the columns name, team, city, age, score and joined; the first editor creates it.

flowchart LR
    A["Load<br/>read_csv, read_json"] --> B["Inspect<br/>info, describe"]
    B --> C["Clean<br/>dtypes, missing, text"]
    C --> D["Transform<br/>assign, cut, apply"]
    D --> E["Combine<br/>merge, concat"]
    E --> F["Summarize<br/>groupby, pivot"]
    F --> G["Show<br/>plot, to_csv"]

The usual shape of an analysis. Most real work loops between inspecting and cleaning.

Create and load

Python
import numpy as np
import pandas as pd

s = pd.Series([3, 5, 8], index=["a", "b", "c"], name="visits")
df = pd.DataFrame({"x": [1, 2], "y": ["u", "v"]})        # dict of columns
df = pd.DataFrame([{"x": 1, "y": "u"}, {"x": 2, "y": "v"}])   # list of records
df = pd.DataFrame(np.arange(6).reshape(3, 2), columns=["p", "q"])

import io
csv = "id,price,when\n1,9.5,2025-01-02\n2,,2025-01-03\n"
df = pd.read_csv(
    io.StringIO(csv),      # a path or URL in real life
    usecols=["id", "price", "when"],
    dtype={"id": "int32"},
    parse_dates=["when"],
    na_values=["n/a", "-"],
    index_col="id",
)

Other readers: pd.read_json(...), pd.json_normalize(nested) for nested records, and chunksize= for files that do not fit in memory. Excel, Parquet and SQL readers need extra packages that are not available in the browser, so they are not runnable here. See Series, DataFrame Basics, Reading CSV Data and JSON and Nested Data.

Inspect

Python
df.shape            # (rows, columns)
df.head(3)          # first rows; tail(3) for the last
df.dtypes           # type of every column
df.info()           # dtypes, non-null counts, memory
df.describe()       # count, mean, std, quartiles of numeric columns
df.columns.tolist()
df.isna().sum()     # missing values per column
df.nunique()        # distinct values per column
df["team"].value_counts(normalize=True)
df.duplicated().sum()

Select and filter

Python
df["name"]                          # one column -> Series
df[["name", "score"]]               # several columns -> DataFrame
df.loc[0:2, "name":"city"]          # labels, END INCLUDED
df.iloc[0:2, 0:3]                   # positions, end excluded
df.loc[df["age"] > 30]              # boolean mask
df.loc[(df["team"] == "red") & (df["age"] < 40), ["name", "age"]]
df[df["team"].isin(["red", "blue"])]
df[df["name"].str.startswith("A")]
df.query("score > 80 and team != 'green'")
df.nlargest(3, "score")             # also nsmallest
df.sample(3, random_state=1)

Combine conditions with &, | and ~ (never and / or) and put each comparison in parentheses. To change values, assign with a single loc: df.loc[mask, "score"] = 0. See Selecting Columns and Rows, loc and iloc and Filtering with Conditions.

Python Build a table and look around

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Sort, index and rank

Python
df.sort_values("score", ascending=False)
df.sort_values(["team", "score"], ascending=[True, False])
df["rank"] = df["score"].rank(ascending=False, method="min")
df = df.set_index("name")          # column -> index; reset_index() reverses it
df.sort_index()
df.reindex(["Ana", "Zed"])         # new labels appear as NaN

See Sorting and Ranking, The Index and MultiIndex.

Types and missing data

Python
df["age"] = df["age"].astype("int32")
df["team"] = df["team"].astype("category")
df["price"] = pd.to_numeric(df["price"], errors="coerce")   # bad values -> NaN
df["when"] = pd.to_datetime(df["when"], format="%d/%m/%Y")
df = df.convert_dtypes()                                    # nullable dtypes

df.isna()                           # True where missing
df.dropna(subset=["score"])         # drop rows with a missing score
df.fillna({"score": df["score"].median()})
df["score"].ffill()                 # carry the last value forward
df["score"].interpolate()           # straight-line fill

NaN never equals anything, not even itself, so use isna() / notna(). A plain integer column that gets a missing value turns into floats; use Int64 to keep integers. See Data Types and Conversion, Missing Data and Categorical Data.

Text and dates

Python
df["name"].str.lower()
df["name"].str.strip()
df["name"].str.len()
df["name"].str.contains("an", na=False)
df["full"].str.split(" ", n=1, expand=True)       # one column per piece
df["code"].str.extract(r"(\d+)")                  # first regex group
df["name"].str.replace("a", "o", regex=False)

df["joined"].dt.year                # also month, day, dayofweek
df["joined"].dt.day_name()
df["joined"] + pd.Timedelta(days=30)
(df["joined"].max() - df["joined"]).dt.days
pd.date_range("2025-01-01", periods=5, freq="D")

See Working with Text, Dates and Times and Time Zones.

Transform columns

Python
df = df.assign(bonus=df["score"] * 0.1, senior=df["age"] >= 40)    # chainable
df["tier"] = np.where(df["score"] >= 80, "high", "low")
df["band"] = np.select([df["age"] < 30, df["age"] < 40], ["20s", "30s"], default="40+")
df["age_group"] = pd.cut(df["age"], [0, 30, 40, 100], labels=["<30", "30s", "40+"])
df["quintile"] = pd.qcut(df["score"], 5, labels=False)
df["team"].map({"red": "R", "blue": "B"})          # unmatched -> NaN
df.replace({"city": {"Rome": "Roma"}})
df["score"].clip(lower=70, upper=90)
df["score"].round(0)
df.rename(columns={"score": "points"}).drop(columns=["city"])

Vectorized expressions beat apply by a wide margin, so reach for np.where, np.select and the .str / .dt accessors first. See Creating and Changing Columns, Vectorised Operations, apply, map and replace, Binning and Duplicates and Outliers.

Python Clean and transform

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Group and reshape

Python
df.groupby("team")["score"].mean()
df.groupby(["team", "city"]).size()                 # rows per group (includes NaN)
df.groupby("team").agg(
    people=("name", "size"),
    avg_score=("score", "mean"),
    oldest=("age", "max"),
)
df["vs_team"] = df["score"] - df.groupby("team")["score"].transform("mean")
df.groupby("team").filter(lambda g: len(g) >= 2)    # keep whole groups
df.groupby("team", dropna=False)                    # keep rows whose key is NaN

df.pivot_table(index="team", columns="city", values="score", aggfunc="mean", margins=True)
pd.crosstab(df["team"], df["city"], normalize="index")
wide.melt(id_vars="id", var_name="month", value_name="sales")   # wide -> long
long.pivot(index="id", columns="month", values="sales")         # long -> wide
df.explode("tags")                                  # one row per list item
pd.get_dummies(df["team"])                          # one-hot encoding

Choose by output shape: agg gives one row per group, transform keeps the original length, filter drops whole groups. See groupby Basics, agg, transform and filter, Pivot Tables and Crosstabs and Reshaping.

Combine

Python
pd.concat([a, b], ignore_index=True)                # stack rows
pd.concat([a, b], axis=1)                           # side by side, aligned on index
pd.concat({"jan": a, "feb": b}, names=["month"])    # remember the source

df.merge(other, on="team", how="left", validate="many_to_one", indicator=True)
df.merge(other, left_on="team", right_on="club", suffixes=("_l", "_r"))
df.join(other.set_index("team"), on="team")         # join on the index of the right side
pd.merge_asof(trades, quotes, on="time", by="symbol")   # nearest earlier match

Always compare row counts before and after a merge, and use validate= to catch duplicated keys. how can be inner, left, right, outer or cross. See Concatenating DataFrames, Merge and Join and Advanced Merges.

Python Group, join and reshape

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Time series

Python
ts = df.set_index("joined").sort_index()
ts.loc["2023"]                      # a whole year
ts.loc["2023-01":"2023-06"]         # a range, both ends included

s.resample("W").sum()               # also "D", "MS" (month start), "QS", "YS"
s.resample("MS").agg(["sum", "mean"])
s.rolling(7).mean()                 # 7-row window; min_periods=1 allows partial
s.rolling("7D").mean()              # time-based window
s.expanding().max()
s.ewm(span=3).mean()
s.shift(1)                          # previous value
s.diff()                            # change since the previous row
s.pct_change()                      # relative change

s.index.tz_localize("UTC").tz_convert("Europe/Berlin")   # naive -> aware -> other zone

Resampling needs a DatetimeIndex (or on="column"). Store timestamps in UTC and convert them only for display. See Time Series Basics, Resampling and Time Windows, Rolling, Expanding and EWM and Time Zones.

Statistics

Python
df["score"].agg(["mean", "median", "std", "min", "max"])
df["score"].quantile([0.25, 0.75])
df[["age", "score"]].corr()
df["team"].mode()
df["score"].skew()
np.average(df["score"].dropna(), weights=df.loc[df["score"].notna(), "age"])

See Descriptive Statistics, Exploratory Data Analysis Workflow and Data Cleaning in Practice.

Plot and display

Python
import matplotlib.pyplot as plt

df.plot.bar(x="name", y="score")
df["score"].plot.hist(bins=5)
df.plot.scatter(x="age", y="score")
df.groupby("team")["score"].mean().plot.barh(title="Mean score")
plt.tight_layout()
plt.show()

pd.set_option("display.max_rows", 20)
pd.set_option("display.precision", 2)
df.to_string(index=False)

See Plotting with pandas, Charts for Analysis and Styling and Displaying Tables.

Python Time series and a chart

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Write and test

Python
df.to_csv(index=False)              # returns a string when no path is given
df.to_json(orient="records")
df.to_dict("records")               # list of dicts

from pandas.testing import assert_frame_equal
assert_frame_equal(result, expected, check_exact=False, rtol=1e-6)

Excel and Parquet writers (to_excel, to_parquet) need extra packages and are not available in the browser. See Writing and Formatting Data and Testing and Validating DataFrames.

Performance and pitfalls

Python
df.memory_usage(deep=True)                                  # measure before optimizing
pd.read_csv(path, usecols=["a", "b"], dtype={"a": "int32"}, chunksize=100_000)
df["team"] = df["team"].astype("category")                  # repeated text

df.query("age > @limit")                                    # @ reads a Python variable
rows = [{"x": i} for i in range(1000)]
df = pd.DataFrame(rows)                                     # build once, do not grow in a loop

A few rules of thumb that prevent most bugs:

  • Never chain assignment (df[mask]["b"] = 0); write df.loc[mask, "b"] = 0.
  • Do not loop over rows when a vectorized expression exists; avoid iterrows, and apply(axis=1) for simple math.
  • Check row counts after merges and totals after groupbys.
  • Assign results (df = df.dropna()) instead of using inplace=True.
  • Keep raw data untouched and write cleaning steps as functions you can re-run.

See Common pandas Pitfalls, Memory Usage and Smaller Dtypes, Writing Fast pandas Code, When Data Gets Big and Building Data Pipelines.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which line selects the rows where team is 'red' and age is below 40?

    Masks combine with & and each comparison needs parentheses. 'and' raises a ValueError for Series, and the other forms are not valid selections.
  2. 2. You need each row to show its team's average score. Which call returns the right shape?

    transform returns one value per original row, aligned with the DataFrame. agg returns one row per team.
  3. 3. What does pd.to_numeric(col, errors='coerce') do with a value like 'n/a'?

    coerce turns anything that cannot be parsed into NaN so the column becomes numeric and you can inspect the problem rows afterwards.
  4. 4. Which is the safest way to see whether a left merge multiplied your rows because of duplicate keys?

    Row counts reveal unexpected growth, and validate= makes pandas raise an error when the key relationship is not what you assumed.

Summary

  • Create and inspect with DataFrame, read_csv, shape, dtypes, info, describe and isna().sum().
  • Select with loc (labels) and iloc (positions), filter with parenthesized masks, and assign through a single loc.
  • Clean in this order: fix text, convert dtypes, handle missing values, remove duplicates.
  • Summarize with groupby (agg, transform, filter), pivot_table and crosstab; reshape with melt and pivot.
  • Combine with concat and merge, and verify row counts; use resample, rolling and shift for time series.
  • Measure before optimizing, vectorize instead of looping, and treat chained assignment as a bug.

You have reached the end of the course. Revisit the introduction any time, or test yourself again with the practice exercises.