Pandas DataFrames: Command Center

NumPy is fast, but every element in an array has the same type. Real data is messy and mixed: a column of strings next to a column of dates next to a column of floats, with missing values here and there. Pandas puts NumPy arrays inside a container with labels, a different type for each column, and grouping. It also adds the operations of a database: join, filter, aggregate, pivot, resample. In Python, most data analysis is done on DataFrames.

You've seen the basics in ds-pandas. This lesson goes deeper: how to think about a DataFrame, the indexing rules that confuse everyone, how groupby works, the kinds of merge, the reshape operations, and the performance traps that turn a 30-second job into a 30-minute job.

1. The Mental Model: An Ordered Dict of Aligned Series

A DataFrame is an ordered dict of Series, all sharing a single Index. Every column is a Series: a NumPy array plus a labelled index. The DataFrame's columns are themselves an Index (of column names), and every row is identified by the row Index.

python

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice", "Bob", "Carol", "Dan"],
    "age":    [30, 25, 28, 40],
    "salary": [80000, 65000, 72000, 95000],
})
print(df)
#     name  age  salary
# 0  Alice   30   80000
# 1    Bob   25   65000
# 2  Carol   28   72000
# 3    Dan   40   95000

print(df.index)         # RangeIndex(start=0, stop=4, step=1)
print(df.columns)       # Index(['name', 'age', 'salary'], dtype='object')
print(type(df["age"]))  # <class 'pandas.core.series.Series'>

Internally, each column is stored as a separate NumPy array (or in modern pandas, possibly a PyArrow-backed array). Operations are vectorised per column. Mixed-dtype operations stay efficient because pandas never widens an int column just because another column is a str.

The Index is not optional. Every Series and DataFrame has one. Choose it on purpose: set it to your real key (date, user_id, ticker), and joins become simple.

2. Index Essentials

python

import pandas as pd

df = pd.DataFrame({
    "user_id": [101, 102, 103, 104],
    "name":    ["Alice", "Bob", "Carol", "Dan"],
    "score":   [88, 92, 75, 81],
})

# Promote a column to the index
df = df.set_index("user_id")
print(df.loc[102])              # the row whose user_id is 102 (Bob, 92)

# Reset back to a default RangeIndex
df = df.reset_index()           # user_id comes back as a regular column

# MultiIndex: an index made of more than one column
panel = pd.DataFrame({
    "year":   [2024, 2024, 2025, 2025],
    "region": ["EU",  "US",   "EU",  "US"],
    "sales":  [100, 200, 130, 250],
}).set_index(["year", "region"])

print(panel)
#              sales
# year region       
# 2024 EU        100
#      US        200
# 2025 EU        130
#      US        250

print(panel.loc[(2024, "US")])       # 200
print(panel.loc[2024])               # all 2024 rows
panel.xs("US", level="region")       # cross-section: all US rows across years

A multi-index is how pandas does N-dimensional labelled data with a 2-D structure. You can also build one explicitly:

python

idx = pd.MultiIndex.from_tuples(
    [(2024, "EU"), (2024, "US"), (2025, "EU")],
    names=["year", "region"],
)

+ setup added so this can run · re-runs 2 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

xs, unstack, and stack (covered later) all rely on multi-indices.

3. Selecting: `.loc`, `.iloc`, `.at`, `.iat`, `.query`

There are several ways to pick rows and columns, and they are not interchangeable.

python

import pandas as pd

df = pd.DataFrame({
    "A": [10, 20, 30, 40],
    "B": [1.1, 2.2, 3.3, 4.4],
    "C": ["x", "y", "z", "w"],
}, index=["a", "b", "c", "d"])

# .loc: by LABEL
df.loc["b"]                 # row labelled "b" (a Series)
df.loc["b", "A"]            # scalar: 20
df.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"
df.loc[:, ["A", "C"]]       # all rows, columns A and C
df.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)

# .iloc: by INTEGER POSITION
df.iloc[1]                  # second row (a Series)
df.iloc[1, 0]               # scalar at row 1, col 0: 20
df.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)
df.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2

# .at / .iat: the fastest way to get one value
df.at["b", "A"]             # 20    like .loc, but only for one cell
df.iat[1, 0]                # 20    like .iloc, but only for one cell

# .query: the condition is a string, often clearer for combined conditions
df.query("A > 15 and C != 'w'")
df.query("A in [10, 30]")

The important rule: .loc includes the upper bound ("a":"c" includes "c"), .iloc is half-open like normal Python slicing (0:2 excludes 2). Mixing them up is a common off-by-one bug.

.query is useful for filters with several conditions. df[(df["A"] > 15) & (df["B"] < 4)] becomes df.query("A > 15 and B < 4"). It is easier to read, and you don't need the extra parentheses for operator precedence.

4. The Chained-Indexing Trap

This is the most common pandas mistake.

python

import pandas as pd

df = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})

# WRONG: chained indexing when assigning
df[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)
print(df["B"].tolist())               # [10, 20, 30]   unchanged!

df[df["A"] > 1] returns a new DataFrame (a copy, in this case). ["B"] = 999 then changes that copy, and the copy is thrown away. The original is not changed. Pandas warns you, but it doesn't raise an error. In pandas 2 the warning is SettingWithCopyWarning. In pandas 3, Copy-on-Write is always on, and the warning is ChainedAssignmentError: this kind of chained assignment never changes the original.

The fix is a single .loc call:

python

df.loc[df["A"] > 1, "B"] = 999
print(df["B"].tolist())               # [10, 999, 999]   correct

+ setup added so this can run · re-runs 4 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "A": [10, 20, 30, 40],\n    "B": [1.1, 2.2, 3.3, 4.4],\n    "C": ["x", "y", "z", "w"],\n}, index=["a", "b", "c", "d"])\n\n# .loc: by LABEL\ndf.loc["b"]                 # row labelled "b" (a Series)\ndf.loc["b", "A"]            # scalar: 20\ndf.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"\ndf.loc[:, ["A", "C"]]       # all rows, columns A and C\ndf.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)\n\n# .iloc: by INTEGER POSITION\ndf.iloc[1]                  # second row (a Series)\ndf.iloc[1, 0]               # scalar at row 1, col 0: 20\ndf.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)\ndf.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2\n\n# .at / .iat: the fastest way to get one value\ndf.at["b", "A"]             # 20    like .loc, but only for one cell\ndf.iat[1, 0]                # 20    like .iloc, but only for one cell\n\n# .query: the condition is a string, often clearer for combined conditions\ndf.query("A > 15 and C != \'w\'")\ndf.query("A in [10, 30]")',
    'import pandas as pd\n\ndf = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})\n\n# WRONG: chained indexing when assigning\ndf[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)\nprint(df["B"].tolist())               # [10, 20, 30]   unchanged!',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Rule of thumb: every assignment to a filtered DataFrame goes through a single .loc[row_mask, col_label] = value. Never df[mask][col] = value. Never df[col][mask] = value. Always one .loc.

Something similar applies when you read a subset that you plan to change later. Use subset = df.loc[mask].copy(), so that it is clear subset is separate data and not a view of the original.

5. Missing Data

NaN (a float) is the older marker for a missing value. NA is the newer pandas marker, which works the same way for every dtype. Both are detected by .isna() / .notna().

python

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "x": [1.0, 2.0, np.nan, 4.0, np.nan],
    "y": ["a",  None, "c",   "d",  "e"],
})

df.isna()                             # element-wise True/False
df.isna().sum()                       # missing count per column: x=2, y=1
df.dropna()                           # drop any row with any NaN
df.dropna(subset=["x"])               # drop rows missing x only
df.dropna(thresh=2)                   # keep rows with >= 2 non-null values

df.fillna(0)                          # replace every NaN with 0
df.fillna({"x": df["x"].mean(),       # different fill per column
           "y": "unknown"})

# Forward-fill (carry the last value forward), common for time series
# df.fillna(method="ffill")           # old syntax: pandas 2.1+ shows a FutureWarning
df.ffill()                            # modern equivalent
df.bfill()                            # backward-fill

Decide how to handle NaN on purpose. Filling with the mean changes the distribution, and dropping rows changes the sample. Whatever you choose, do it once, at a documented step of the pipeline, not in many places across different notebooks.

6. Apply, Map, Transform, Agg

Each of these functions has different rules. Choose one by asking: what shape goes in, and what shape comes out?

Method	Input	Output	Use when
`Series.map(fn)`	one element	one element	element-wise transform of a Series
`DataFrame.apply(fn, axis=1)`	a row Series	scalar or Series	row-wise computation that needs all cols
`DataFrame.apply(fn, axis=0)`	a column Series	scalar or Series	column-wise reduction or transform
`groupby.transform(fn)`	a group's column	same-shape array (per group)	broadcast group-level value back to rows
`groupby.agg({...})`	each group's columns	one scalar per agg per group	multi-column aggregations

python

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice", "Bob", "Carol"],
    "salary": [80000, 65000, 72000],
    "bonus":  [5000, 3000, 4500],
})

# map: apply to each element of a Series
df["name_upper"] = df["name"].map(str.upper)

# apply with axis=1: the function runs once per row
df["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1)

# apply with axis=0: one result per column (rare, built-ins are faster)
df[["salary", "bonus"]].apply(lambda col: col.max() - col.min())

# When a vectorised op exists, USE IT
df["total_comp"] = df["salary"] + df["bonus"]      # 100x faster than apply

apply(axis=1) is the slowest reasonable way to compute a column, because it runs a Python function for each row. Use it only when the operation really can't be vectorised: running a regex, parsing a JSON cell, calling an API. For arithmetic, always use vectorised operators.

7. GroupBy In Depth

groupby is the database GROUP BY clause, as a Python object whose methods you can chain. It follows the split-apply-combine pattern:

1. Split the DataFrame by some key.
2. Apply an operation to each group.
3. Combine the results into a single output.

python

import pandas as pd

df = pd.DataFrame({
    "team":   ["A", "A", "B", "B", "B", "C"],
    "player": ["P1", "P2", "P3", "P4", "P5", "P6"],
    "points": [10, 15, 8, 12, 20, 5],
    "games":  [3, 5, 4, 6, 5, 2],
})

g = df.groupby("team")

g.size()                                # rows per group
# team
# A    2
# B    3
# C    1

g["points"].mean()                      # mean points per team
g["points"].agg(["mean", "sum", "max"]) # several aggregations at once

# Aggregations on several columns, with custom names
g.agg(
    total_pts=("points", "sum"),
    avg_pts=("points", "mean"),
    games_played=("games", "sum"),
)
#       total_pts  avg_pts  games_played
# team                                  
# A            25     12.5             8
# B            40     13.3            15
# C             5      5.0             2

# Custom aggregation function
g["points"].agg(lambda s: s.max() - s.min())    # range per group

`transform`: send per-group results back to each row

agg collapses each group to one row. transform returns the same shape as the input. This is useful for "subtract the group mean from each row":

python

df["pts_vs_team_avg"] = df["points"] - g["points"].transform("mean")
print(df)
#   team player  points  games  pts_vs_team_avg
# 0    A     P1      10      3        -2.5
# 1    A     P2      15      5         2.5
# 2    B     P3       8      4        -5.33
# ...

+ setup added so this can run · re-runs 8 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
g = _AutoMock('g')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "A": [10, 20, 30, 40],\n    "B": [1.1, 2.2, 3.3, 4.4],\n    "C": ["x", "y", "z", "w"],\n}, index=["a", "b", "c", "d"])\n\n# .loc: by LABEL\ndf.loc["b"]                 # row labelled "b" (a Series)\ndf.loc["b", "A"]            # scalar: 20\ndf.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"\ndf.loc[:, ["A", "C"]]       # all rows, columns A and C\ndf.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)\n\n# .iloc: by INTEGER POSITION\ndf.iloc[1]                  # second row (a Series)\ndf.iloc[1, 0]               # scalar at row 1, col 0: 20\ndf.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)\ndf.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2\n\n# .at / .iat: the fastest way to get one value\ndf.at["b", "A"]             # 20    like .loc, but only for one cell\ndf.iat[1, 0]                # 20    like .iloc, but only for one cell\n\n# .query: the condition is a string, often clearer for combined conditions\ndf.query("A > 15 and C != \'w\'")\ndf.query("A in [10, 30]")',
    'import pandas as pd\n\ndf = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})\n\n# WRONG: chained indexing when assigning\ndf[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)\nprint(df["B"].tolist())               # [10, 20, 30]   unchanged!',
    'df.loc[df["A"] > 1, "B"] = 999\nprint(df["B"].tolist())               # [10, 999, 999]   correct',
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "x": [1.0, 2.0, np.nan, 4.0, np.nan],\n    "y": ["a",  None, "c",   "d",  "e"],\n})\n\ndf.isna()                             # element-wise True/False\ndf.isna().sum()                       # missing count per column: x=2, y=1\ndf.dropna()                           # drop any row with any NaN\ndf.dropna(subset=["x"])               # drop rows missing x only\ndf.dropna(thresh=2)                   # keep rows with >= 2 non-null values\n\ndf.fillna(0)                          # replace every NaN with 0\ndf.fillna({"x": df["x"].mean(),       # different fill per column\n           "y": "unknown"})\n\n# Forward-fill (carry the last value forward), common for time series\n# df.fillna(method="ffill")           # old syntax: pandas 2.1+ shows a FutureWarning\ndf.ffill()                            # modern equivalent\ndf.bfill()                            # backward-fill',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol"],\n    "salary": [80000, 65000, 72000],\n    "bonus":  [5000, 3000, 4500],\n})\n\n# map: apply to each element of a Series\ndf["name_upper"] = df["name"].map(str.upper)\n\n# apply with axis=1: the function runs once per row\ndf["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1)\n\n# apply with axis=0: one result per column (rare, built-ins are faster)\ndf[["salary", "bonus"]].apply(lambda col: col.max() - col.min())\n\n# When a vectorised op exists, USE IT\ndf["total_comp"] = df["salary"] + df["bonus"]      # 100x faster than apply',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "team":   ["A", "A", "B", "B", "B", "C"],\n    "player": ["P1", "P2", "P3", "P4", "P5", "P6"],\n    "points": [10, 15, 8, 12, 20, 5],\n    "games":  [3, 5, 4, 6, 5, 2],\n})\n\ng = df.groupby("team")\n\ng.size()                                # rows per group\n# team\n# A    2\n# B    3\n# C    1\n\ng["points"].mean()                      # mean points per team\ng["points"].agg(["mean", "sum", "max"]) # several aggregations at once\n\n# Aggregations on several columns, with custom names\ng.agg(\n    total_pts=("points", "sum"),\n    avg_pts=("points", "mean"),\n    games_played=("games", "sum"),\n)\n#       total_pts  avg_pts  games_played\n# team                                  \n# A            25     12.5             8\n# B            40     13.3            15\n# C             5      5.0             2\n\n# Custom aggregation function\ng["points"].agg(lambda s: s.max() - s.min())    # range per group',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

`filter`: drop whole groups by a condition

python

# Keep only teams with at least 3 players
big_teams = df.groupby("team").filter(lambda g: len(g) >= 3)

+ setup added so this can run · re-runs 9 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
g = _AutoMock('g')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "A": [10, 20, 30, 40],\n    "B": [1.1, 2.2, 3.3, 4.4],\n    "C": ["x", "y", "z", "w"],\n}, index=["a", "b", "c", "d"])\n\n# .loc: by LABEL\ndf.loc["b"]                 # row labelled "b" (a Series)\ndf.loc["b", "A"]            # scalar: 20\ndf.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"\ndf.loc[:, ["A", "C"]]       # all rows, columns A and C\ndf.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)\n\n# .iloc: by INTEGER POSITION\ndf.iloc[1]                  # second row (a Series)\ndf.iloc[1, 0]               # scalar at row 1, col 0: 20\ndf.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)\ndf.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2\n\n# .at / .iat: the fastest way to get one value\ndf.at["b", "A"]             # 20    like .loc, but only for one cell\ndf.iat[1, 0]                # 20    like .iloc, but only for one cell\n\n# .query: the condition is a string, often clearer for combined conditions\ndf.query("A > 15 and C != \'w\'")\ndf.query("A in [10, 30]")',
    'import pandas as pd\n\ndf = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})\n\n# WRONG: chained indexing when assigning\ndf[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)\nprint(df["B"].tolist())               # [10, 20, 30]   unchanged!',
    'df.loc[df["A"] > 1, "B"] = 999\nprint(df["B"].tolist())               # [10, 999, 999]   correct',
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "x": [1.0, 2.0, np.nan, 4.0, np.nan],\n    "y": ["a",  None, "c",   "d",  "e"],\n})\n\ndf.isna()                             # element-wise True/False\ndf.isna().sum()                       # missing count per column: x=2, y=1\ndf.dropna()                           # drop any row with any NaN\ndf.dropna(subset=["x"])               # drop rows missing x only\ndf.dropna(thresh=2)                   # keep rows with >= 2 non-null values\n\ndf.fillna(0)                          # replace every NaN with 0\ndf.fillna({"x": df["x"].mean(),       # different fill per column\n           "y": "unknown"})\n\n# Forward-fill (carry the last value forward), common for time series\n# df.fillna(method="ffill")           # old syntax: pandas 2.1+ shows a FutureWarning\ndf.ffill()                            # modern equivalent\ndf.bfill()                            # backward-fill',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol"],\n    "salary": [80000, 65000, 72000],\n    "bonus":  [5000, 3000, 4500],\n})\n\n# map: apply to each element of a Series\ndf["name_upper"] = df["name"].map(str.upper)\n\n# apply with axis=1: the function runs once per row\ndf["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1)\n\n# apply with axis=0: one result per column (rare, built-ins are faster)\ndf[["salary", "bonus"]].apply(lambda col: col.max() - col.min())\n\n# When a vectorised op exists, USE IT\ndf["total_comp"] = df["salary"] + df["bonus"]      # 100x faster than apply',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "team":   ["A", "A", "B", "B", "B", "C"],\n    "player": ["P1", "P2", "P3", "P4", "P5", "P6"],\n    "points": [10, 15, 8, 12, 20, 5],\n    "games":  [3, 5, 4, 6, 5, 2],\n})\n\ng = df.groupby("team")\n\ng.size()                                # rows per group\n# team\n# A    2\n# B    3\n# C    1\n\ng["points"].mean()                      # mean points per team\ng["points"].agg(["mean", "sum", "max"]) # several aggregations at once\n\n# Aggregations on several columns, with custom names\ng.agg(\n    total_pts=("points", "sum"),\n    avg_pts=("points", "mean"),\n    games_played=("games", "sum"),\n)\n#       total_pts  avg_pts  games_played\n# team                                  \n# A            25     12.5             8\n# B            40     13.3            15\n# C             5      5.0             2\n\n# Custom aggregation function\ng["points"].agg(lambda s: s.max() - s.min())    # range per group',
    'df["pts_vs_team_avg"] = df["points"] - g["points"].transform("mean")\nprint(df)\n#   team player  points  games  pts_vs_team_avg\n# 0    A     P1      10      3        -2.5\n# 1    A     P2      15      5         2.5\n# 2    B     P3       8      4        -5.33\n# ...',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

`apply`: when nothing else fits

When neither agg, transform, nor filter fits, groupby.apply(fn) passes each group's DataFrame to fn. It is slow (Python code runs for each group), but it can do anything.

8. Merging Tables

pd.merge joins two DataFrames on a key, like SQL JOIN. Four how modes:

python

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "user_id":  [101, 102, 101, 999],
    "amount":   [50, 80, 30, 25],
})
users = pd.DataFrame({
    "user_id": [101, 102, 103],
    "name":    ["Alice", "Bob", "Carol"],
})

# INNER: rows where the key exists in BOTH tables (the default)
pd.merge(orders, users, on="user_id", how="inner")
# order_id  user_id  amount   name
#        1      101      50  Alice
#        2      102      80    Bob
#        3      101      30  Alice
# Order 4 is dropped (user 999 is not in users). Carol is dropped (she has no orders).

# LEFT: keep all rows from the left table, and fill missing right-side values with NaN
pd.merge(orders, users, on="user_id", how="left")
# Includes order 4 with name=NaN

# RIGHT: keep all rows from the right table
pd.merge(orders, users, on="user_id", how="right")
# Includes Carol with order_id=NaN

# OUTER: keep every row from both tables
pd.merge(orders, users, on="user_id", how="outer")
# Includes order 4 (no name) AND Carol (no order)

Different key names? Use left_on and right_on:

python

pd.merge(orders, users, left_on="user_id", right_on="user_id")

+ setup added so this can run · re-runs 9 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

orders = _AutoMock('orders')
users = _AutoMock('users')
pd = _AutoMock('pd')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
    'idx = pd.MultiIndex.from_tuples(\n    [(2024, "EU"), (2024, "US"), (2025, "EU")],\n    names=["year", "region"],\n)',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "A": [10, 20, 30, 40],\n    "B": [1.1, 2.2, 3.3, 4.4],\n    "C": ["x", "y", "z", "w"],\n}, index=["a", "b", "c", "d"])\n\n# .loc: by LABEL\ndf.loc["b"]                 # row labelled "b" (a Series)\ndf.loc["b", "A"]            # scalar: 20\ndf.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"\ndf.loc[:, ["A", "C"]]       # all rows, columns A and C\ndf.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)\n\n# .iloc: by INTEGER POSITION\ndf.iloc[1]                  # second row (a Series)\ndf.iloc[1, 0]               # scalar at row 1, col 0: 20\ndf.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)\ndf.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2\n\n# .at / .iat: the fastest way to get one value\ndf.at["b", "A"]             # 20    like .loc, but only for one cell\ndf.iat[1, 0]                # 20    like .iloc, but only for one cell\n\n# .query: the condition is a string, often clearer for combined conditions\ndf.query("A > 15 and C != \'w\'")\ndf.query("A in [10, 30]")',
    'import pandas as pd\n\ndf = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})\n\n# WRONG: chained indexing when assigning\ndf[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)\nprint(df["B"].tolist())               # [10, 20, 30]   unchanged!',
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "x": [1.0, 2.0, np.nan, 4.0, np.nan],\n    "y": ["a",  None, "c",   "d",  "e"],\n})\n\ndf.isna()                             # element-wise True/False\ndf.isna().sum()                       # missing count per column: x=2, y=1\ndf.dropna()                           # drop any row with any NaN\ndf.dropna(subset=["x"])               # drop rows missing x only\ndf.dropna(thresh=2)                   # keep rows with >= 2 non-null values\n\ndf.fillna(0)                          # replace every NaN with 0\ndf.fillna({"x": df["x"].mean(),       # different fill per column\n           "y": "unknown"})\n\n# Forward-fill (carry the last value forward), common for time series\n# df.fillna(method="ffill")           # old syntax: pandas 2.1+ shows a FutureWarning\ndf.ffill()                            # modern equivalent\ndf.bfill()                            # backward-fill',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol"],\n    "salary": [80000, 65000, 72000],\n    "bonus":  [5000, 3000, 4500],\n})\n\n# map: apply to each element of a Series\ndf["name_upper"] = df["name"].map(str.upper)\n\n# apply with axis=1: the function runs once per row\ndf["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1)\n\n# apply with axis=0: one result per column (rare, built-ins are faster)\ndf[["salary", "bonus"]].apply(lambda col: col.max() - col.min())\n\n# When a vectorised op exists, USE IT\ndf["total_comp"] = df["salary"] + df["bonus"]      # 100x faster than apply',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "team":   ["A", "A", "B", "B", "B", "C"],\n    "player": ["P1", "P2", "P3", "P4", "P5", "P6"],\n    "points": [10, 15, 8, 12, 20, 5],\n    "games":  [3, 5, 4, 6, 5, 2],\n})\n\ng = df.groupby("team")\n\ng.size()                                # rows per group\n# team\n# A    2\n# B    3\n# C    1\n\ng["points"].mean()                      # mean points per team\ng["points"].agg(["mean", "sum", "max"]) # several aggregations at once\n\n# Aggregations on several columns, with custom names\ng.agg(\n    total_pts=("points", "sum"),\n    avg_pts=("points", "mean"),\n    games_played=("games", "sum"),\n)\n#       total_pts  avg_pts  games_played\n# team                                  \n# A            25     12.5             8\n# B            40     13.3            15\n# C             5      5.0             2\n\n# Custom aggregation function\ng["points"].agg(lambda s: s.max() - s.min())    # range per group',
    'import pandas as pd\n\norders = pd.DataFrame({\n    "order_id": [1, 2, 3, 4],\n    "user_id":  [101, 102, 101, 999],\n    "amount":   [50, 80, 30, 25],\n})\nusers = pd.DataFrame({\n    "user_id": [101, 102, 103],\n    "name":    ["Alice", "Bob", "Carol"],\n})\n\n# INNER: rows where the key exists in BOTH tables (the default)\npd.merge(orders, users, on="user_id", how="inner")\n# order_id  user_id  amount   name\n#        1      101      50  Alice\n#        2      102      80    Bob\n#        3      101      30  Alice\n# Order 4 is dropped (user 999 is not in users). Carol is dropped (she has no orders).\n\n# LEFT: keep all rows from the left table, and fill missing right-side values with NaN\npd.merge(orders, users, on="user_id", how="left")\n# Includes order 4 with name=NaN\n\n# RIGHT: keep all rows from the right table\npd.merge(orders, users, on="user_id", how="right")\n# Includes Carol with order_id=NaN\n\n# OUTER: keep every row from both tables\npd.merge(orders, users, on="user_id", how="outer")\n# Includes order 4 (no name) AND Carol (no order)',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

Joining on the index? Use df.join(other). It is convenient when both tables are indexed by the join key. Multiple keys? Pass a list: on=["user_id", "date"].

Watch for row counts that grow. A "one-to-many" join can multiply rows, and a "many-to-many" join can multiply them enormously. merge(..., validate="one_to_one") (or "one_to_many", "many_to_one") checks the relationship at the join. That way the bug is caught at the join, not three steps later.

9. Concatenating

When you have multiple DataFrames of the same shape (or columns), pd.concat stacks them.

python

import pandas as pd

a = pd.DataFrame({"x": [1, 2], "y": [3, 4]})
b = pd.DataFrame({"x": [5, 6], "y": [7, 8]})

pd.concat([a, b], axis=0)              # stack vertically (adds rows)
#    x  y
# 0  1  3
# 1  2  4
# 0  5  7         the index repeats! Pass ignore_index=True to renumber
# 1  6  8

pd.concat([a, b], axis=0, ignore_index=True)

pd.concat([a, b], axis=1)              # stack horizontally (adds columns)
#    x  y  x  y
# 0  1  3  5  7
# 1  2  4  6  8

pd.concat([df1, df2], axis=0) is how you load N CSV files into one DataFrame:

python

import glob
import pandas as pd

dfs = [pd.read_csv(f) for f in glob.glob("data/*.csv")]
combined = pd.concat(dfs, ignore_index=True)

When the file is too big to fit in memory, use read_csv(..., chunksize=10_000). It gives you the file in chunks. Process each chunk and discard it before reading the next.

10. Reshaping: Pivot, Melt, Stack, Unstack

Data comes in two forms: long (one observation per row, many rows) and wide (one entity per row, with its observations across the columns). You will convert between them often.

python

import pandas as pd

# LONG format: one row per (region, month, metric)
long = pd.DataFrame({
    "region":  ["EU", "EU", "US", "US"],
    "month":   ["Jan", "Feb", "Jan", "Feb"],
    "revenue": [100, 120, 200, 250],
})

# pivot: long to wide (one row per region, months as columns)
wide = long.pivot(index="region", columns="month", values="revenue")
# month   Feb  Jan
# region          
# EU      120  100
# US      250  200

# pivot_table: pivot and aggregate (handles duplicate rows)
long2 = pd.concat([long, long])         # duplicated rows
long2.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum")
# Same shape as pivot, but sums duplicates

# melt: wide back to long
wide.reset_index().melt(id_vars="region", var_name="month", value_name="revenue")
# region month  revenue
#     EU   Feb      120
#     EU   Jan      100
#     US   Feb      250
#     US   Jan      200

# stack / unstack: move an index level to the columns and back
multi = long.set_index(["region", "month"])
multi.unstack("month")                  # the "month" index level becomes columns
multi.unstack("month").stack(future_stack=True)   # back to the original rows (future_stack=True avoids a pandas warning)

When to use which: use pivot for unique index/column pairs, pivot_table when duplicates need aggregating, melt for going wide-to-long, and stack/unstack when you already have a multi-index and want to move one level between rows and columns.

11. Time Series: A Short Overview

python

import pandas as pd

df = pd.DataFrame({
    "ts":    ["2025-01-01 09:00", "2025-01-01 09:30", "2025-01-02 10:00", "2025-01-02 11:00"],
    "value": [10, 12, 8, 14],
})
df["ts"] = pd.to_datetime(df["ts"])     # parse to datetime64
df = df.set_index("ts")                 # time series methods need a DatetimeIndex

df.resample("D").sum()                  # daily sum: 22 for Jan 1 and 22 for Jan 2
df.resample("h").mean()                 # hourly mean (NaN for empty hours)
df.resample("D").agg(["min", "max", "mean"])

# Date arithmetic with offsets
df.index + pd.Timedelta(days=1)
df.index + pd.offsets.BDay(1)           # next business day
df["value"].rolling("1h").mean()        # 1-hour rolling mean (needs a DatetimeIndex)

resample is groupby for time. rolling works on a moving window, and is used for moving averages, Bollinger bands and lag features. With a DatetimeIndex, both understand calendar offsets ("ME" for month-end, "W-MON" for week-Monday, etc.).

12. Performance: `eval`, `query`, Categorical

Three ways to make pandas code faster.

Categorical dtype for low-cardinality strings

python

import pandas as pd

df = pd.DataFrame({"country": ["US", "UK", "US", "FR", "US"] * 1_000_000})
print(df.memory_usage(deep=True).sum())   # ~255 MB because every value is a separate string

df["country"] = df["country"].astype("category")
print(df.memory_usage(deep=True).sum())   # ~5 MB  small integer codes plus a tiny lookup table

If only a small share of a column's values are unique, astype("category") often cuts its memory use a lot and speeds up groupby on that column.

`df.eval` and `df.query`: string expressions, evaluated in C

python

df = pd.DataFrame({"a": range(1_000_000), "b": range(1_000_000)})

# Vectorised Python: creates temporary arrays along the way
df["c"] = df["a"] * 2 + df["b"] ** 2

# Same result with no temporary arrays: faster, and less memory on large frames
df.eval("c = a * 2 + b ** 2", inplace=True)

# query: the condition is evaluated the same way
df.query("a > 500 and b < 1000")

+ setup added so this can run · re-runs 15 earlier blocks on this page first

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
orders = _AutoMock('orders')
users = _AutoMock('users')

# Earlier blocks on this page that this one builds on, re-run quietly first.
try:
    import pandas as pd
    import numpy as np
    import glob
except Exception:
    pass
_pm_earlier = [
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol", "Dan"],\n    "age":    [30, 25, 28, 40],\n    "salary": [80000, 65000, 72000, 95000],\n})\nprint(df)\n#     name  age  salary\n# 0  Alice   30   80000\n# 1    Bob   25   65000\n# 2  Carol   28   72000\n# 3    Dan   40   95000\n\nprint(df.index)         # RangeIndex(start=0, stop=4, step=1)\nprint(df.columns)       # Index([\'name\', \'age\', \'salary\'], dtype=\'object\')\nprint(type(df["age"]))  # <class \'pandas.core.series.Series\'>',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "user_id": [101, 102, 103, 104],\n    "name":    ["Alice", "Bob", "Carol", "Dan"],\n    "score":   [88, 92, 75, 81],\n})\n\n# Promote a column to the index\ndf = df.set_index("user_id")\nprint(df.loc[102])              # the row whose user_id is 102 (Bob, 92)\n\n# Reset back to a default RangeIndex\ndf = df.reset_index()           # user_id comes back as a regular column\n\n# MultiIndex: an index made of more than one column\npanel = pd.DataFrame({\n    "year":   [2024, 2024, 2025, 2025],\n    "region": ["EU",  "US",   "EU",  "US"],\n    "sales":  [100, 200, 130, 250],\n}).set_index(["year", "region"])\n\nprint(panel)\n#              sales\n# year region       \n# 2024 EU        100\n#      US        200\n# 2025 EU        130\n#      US        250\n\nprint(panel.loc[(2024, "US")])       # 200\nprint(panel.loc[2024])               # all 2024 rows\npanel.xs("US", level="region")       # cross-section: all US rows across years',
    'idx = pd.MultiIndex.from_tuples(\n    [(2024, "EU"), (2024, "US"), (2025, "EU")],\n    names=["year", "region"],\n)',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "A": [10, 20, 30, 40],\n    "B": [1.1, 2.2, 3.3, 4.4],\n    "C": ["x", "y", "z", "w"],\n}, index=["a", "b", "c", "d"])\n\n# .loc: by LABEL\ndf.loc["b"]                 # row labelled "b" (a Series)\ndf.loc["b", "A"]            # scalar: 20\ndf.loc["a":"c"]             # rows "a" through "c", INCLUDING "c"\ndf.loc[:, ["A", "C"]]       # all rows, columns A and C\ndf.loc[df["A"] > 15]        # rows where A > 15 (a boolean mask)\n\n# .iloc: by INTEGER POSITION\ndf.iloc[1]                  # second row (a Series)\ndf.iloc[1, 0]               # scalar at row 1, col 0: 20\ndf.iloc[0:2]                # first two rows (the end is EXCLUDED, as in Python)\ndf.iloc[:, [0, 2]]          # all rows, columns at positions 0 and 2\n\n# .at / .iat: the fastest way to get one value\ndf.at["b", "A"]             # 20    like .loc, but only for one cell\ndf.iat[1, 0]                # 20    like .iloc, but only for one cell\n\n# .query: the condition is a string, often clearer for combined conditions\ndf.query("A > 15 and C != \'w\'")\ndf.query("A in [10, 30]")',
    'import pandas as pd\n\ndf = pd.DataFrame({"A": [1, 2, 3], "B": [10, 20, 30]})\n\n# WRONG: chained indexing when assigning\ndf[df["A"] > 1]["B"] = 999            # warning: SettingWithCopyWarning (pandas 2), ChainedAssignmentError (pandas 3)\nprint(df["B"].tolist())               # [10, 20, 30]   unchanged!',
    'import pandas as pd\nimport numpy as np\n\ndf = pd.DataFrame({\n    "x": [1.0, 2.0, np.nan, 4.0, np.nan],\n    "y": ["a",  None, "c",   "d",  "e"],\n})\n\ndf.isna()                             # element-wise True/False\ndf.isna().sum()                       # missing count per column: x=2, y=1\ndf.dropna()                           # drop any row with any NaN\ndf.dropna(subset=["x"])               # drop rows missing x only\ndf.dropna(thresh=2)                   # keep rows with >= 2 non-null values\n\ndf.fillna(0)                          # replace every NaN with 0\ndf.fillna({"x": df["x"].mean(),       # different fill per column\n           "y": "unknown"})\n\n# Forward-fill (carry the last value forward), common for time series\n# df.fillna(method="ffill")           # old syntax: pandas 2.1+ shows a FutureWarning\ndf.ffill()                            # modern equivalent\ndf.bfill()                            # backward-fill',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "name":   ["Alice", "Bob", "Carol"],\n    "salary": [80000, 65000, 72000],\n    "bonus":  [5000, 3000, 4500],\n})\n\n# map: apply to each element of a Series\ndf["name_upper"] = df["name"].map(str.upper)\n\n# apply with axis=1: the function runs once per row\ndf["total_comp"] = df.apply(lambda r: r["salary"] + r["bonus"], axis=1)\n\n# apply with axis=0: one result per column (rare, built-ins are faster)\ndf[["salary", "bonus"]].apply(lambda col: col.max() - col.min())\n\n# When a vectorised op exists, USE IT\ndf["total_comp"] = df["salary"] + df["bonus"]      # 100x faster than apply',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "team":   ["A", "A", "B", "B", "B", "C"],\n    "player": ["P1", "P2", "P3", "P4", "P5", "P6"],\n    "points": [10, 15, 8, 12, 20, 5],\n    "games":  [3, 5, 4, 6, 5, 2],\n})\n\ng = df.groupby("team")\n\ng.size()                                # rows per group\n# team\n# A    2\n# B    3\n# C    1\n\ng["points"].mean()                      # mean points per team\ng["points"].agg(["mean", "sum", "max"]) # several aggregations at once\n\n# Aggregations on several columns, with custom names\ng.agg(\n    total_pts=("points", "sum"),\n    avg_pts=("points", "mean"),\n    games_played=("games", "sum"),\n)\n#       total_pts  avg_pts  games_played\n# team                                  \n# A            25     12.5             8\n# B            40     13.3            15\n# C             5      5.0             2\n\n# Custom aggregation function\ng["points"].agg(lambda s: s.max() - s.min())    # range per group',
    'import pandas as pd\n\norders = pd.DataFrame({\n    "order_id": [1, 2, 3, 4],\n    "user_id":  [101, 102, 101, 999],\n    "amount":   [50, 80, 30, 25],\n})\nusers = pd.DataFrame({\n    "user_id": [101, 102, 103],\n    "name":    ["Alice", "Bob", "Carol"],\n})\n\n# INNER: rows where the key exists in BOTH tables (the default)\npd.merge(orders, users, on="user_id", how="inner")\n# order_id  user_id  amount   name\n#        1      101      50  Alice\n#        2      102      80    Bob\n#        3      101      30  Alice\n# Order 4 is dropped (user 999 is not in users). Carol is dropped (she has no orders).\n\n# LEFT: keep all rows from the left table, and fill missing right-side values with NaN\npd.merge(orders, users, on="user_id", how="left")\n# Includes order 4 with name=NaN\n\n# RIGHT: keep all rows from the right table\npd.merge(orders, users, on="user_id", how="right")\n# Includes Carol with order_id=NaN\n\n# OUTER: keep every row from both tables\npd.merge(orders, users, on="user_id", how="outer")\n# Includes order 4 (no name) AND Carol (no order)',
    'pd.merge(orders, users, left_on="user_id", right_on="user_id")',
    'import pandas as pd\n\na = pd.DataFrame({"x": [1, 2], "y": [3, 4]})\nb = pd.DataFrame({"x": [5, 6], "y": [7, 8]})\n\npd.concat([a, b], axis=0)              # stack vertically (adds rows)\n#    x  y\n# 0  1  3\n# 1  2  4\n# 0  5  7         the index repeats! Pass ignore_index=True to renumber\n# 1  6  8\n\npd.concat([a, b], axis=0, ignore_index=True)\n\npd.concat([a, b], axis=1)              # stack horizontally (adds columns)\n#    x  y  x  y\n# 0  1  3  5  7\n# 1  2  4  6  8',
    'import glob\nimport pandas as pd\n\ndfs = [pd.read_csv(f) for f in glob.glob("data/*.csv")]\ncombined = pd.concat(dfs, ignore_index=True)',
    'import pandas as pd\n\n# LONG format: one row per (region, month, metric)\nlong = pd.DataFrame({\n    "region":  ["EU", "EU", "US", "US"],\n    "month":   ["Jan", "Feb", "Jan", "Feb"],\n    "revenue": [100, 120, 200, 250],\n})\n\n# pivot: long to wide (one row per region, months as columns)\nwide = long.pivot(index="region", columns="month", values="revenue")\n# month   Feb  Jan\n# region          \n# EU      120  100\n# US      250  200\n\n# pivot_table: pivot and aggregate (handles duplicate rows)\nlong2 = pd.concat([long, long])         # duplicated rows\nlong2.pivot_table(index="region", columns="month", values="revenue", aggfunc="sum")\n# Same shape as pivot, but sums duplicates\n\n# melt: wide back to long\nwide.reset_index().melt(id_vars="region", var_name="month", value_name="revenue")\n# region month  revenue\n#     EU   Feb      120\n#     EU   Jan      100\n#     US   Feb      250\n#     US   Jan      200\n\n# stack / unstack: move an index level to the columns and back\nmulti = long.set_index(["region", "month"])\nmulti.unstack("month")                  # the "month" index level becomes columns\nmulti.unstack("month").stack(future_stack=True)   # back to the original rows (future_stack=True avoids a pandas warning)',
    'import pandas as pd\n\ndf = pd.DataFrame({\n    "ts":    ["2025-01-01 09:00", "2025-01-01 09:30", "2025-01-02 10:00", "2025-01-02 11:00"],\n    "value": [10, 12, 8, 14],\n})\ndf["ts"] = pd.to_datetime(df["ts"])     # parse to datetime64\ndf = df.set_index("ts")                 # time series methods need a DatetimeIndex\n\ndf.resample("D").sum()                  # daily sum: 22 for Jan 1 and 22 for Jan 2\ndf.resample("h").mean()                 # hourly mean (NaN for empty hours)\ndf.resample("D").agg(["min", "max", "mean"])\n\n# Date arithmetic with offsets\ndf.index + pd.Timedelta(days=1)\ndf.index + pd.offsets.BDay(1)           # next business day\ndf["value"].rolling("1h").mean()        # 1-hour rolling mean (needs a DatetimeIndex)',
    'import pandas as pd\n\ndf = pd.DataFrame({"country": ["US", "UK", "US", "FR", "US"] * 1_000_000})\nprint(df.memory_usage(deep=True).sum())   # ~255 MB because every value is a separate string\n\ndf["country"] = df["country"].astype("category")\nprint(df.memory_usage(deep=True).sum())   # ~5 MB  small integer codes plus a tiny lookup table',
]
def _pm_run_earlier(_srcs, _g):
    import sys as _s, io as _io, warnings as _w
    for _src in _srcs:
        _o, _e = _s.stdout, _s.stderr
        _s.stdout = _s.stderr = _io.StringIO()
        try:
            with _w.catch_warnings():
                _w.simplefilter('ignore')
                exec(compile(_src, '<earlier block>', 'exec'), _g)
        except (Exception, SystemExit):
            pass
        finally:
            _s.stdout, _s.stderr = _o, _e
    if 'matplotlib.pyplot' in _s.modules:
        _s.modules['matplotlib.pyplot'].close('all')   # their plots belong to their own blocks
_pm_run_earlier(_pm_earlier, globals())
del _pm_earlier, _pm_run_earlier

eval parses the string, combines the operations into one expression, and evaluates it without creating intermediate arrays. It helps on more than about 1M rows. On smaller frames, the time spent parsing can be larger than the time saved.

Read efficiently

python

# Bad: pandas guesses every dtype and uses 64-bit numbers, so a 10 GB file is slow and large
df = pd.read_csv("big.csv")

# Good: declare the dtypes up front
df = pd.read_csv(
    "big.csv",
    dtype={"user_id": "int32", "country": "category", "amount": "float32"},
    parse_dates=["created_at"],
    usecols=["user_id", "country", "amount", "created_at"],   # only what you need
)

# For very large files: read them in chunks
for chunk in pd.read_csv("big.csv", chunksize=100_000):
    process(chunk)

+ setup added so this can run · defines pd, process

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
def process(*_a, **_kw):
    print('-> process() called')
    return _AutoMock('process()')

A newer alternative is Parquet. pd.read_parquet is much faster than reading CSV, and it keeps the dtypes. Convert once with df.to_parquet("data.parquet"), and every later load is much faster.

Common Mistakes

1. Chained-indexing assignment

python

df[df["A"] > 1]["B"] = 999          # warning (pandas 2 and 3), and the original is not changed
df.loc[df["A"] > 1, "B"] = 999      # correct: a single .loc call

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

This was covered above, but it is worth repeating: one .loc, never two indexing operations.

2. iterrows instead of vectorised ops

python

# 100x slower than necessary
for i, row in df.iterrows():
    df.at[i, "total"] = row["a"] + row["b"]

# Vectorised: takes milliseconds
df["total"] = df["a"] + df["b"]

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

iterrows is one of the slowest pandas APIs. Each iteration builds a Series, so a million rows means a million short-lived objects. If you really need Python code for each row, use itertuples, which is much faster than iterrows. But first, try again to vectorise.

3. apply over a column when a vectorised op exists

python

df["upper"] = df["name"].apply(lambda x: x.upper())   # Python per row
df["upper"] = df["name"].str.upper()                  # vectorised

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

Every string method has a .str.* accessor that runs in C: .str.lower, .str.contains, .str.split, .str.replace, .str.strip. Dates have .dt.* (.dt.year, .dt.month, .dt.day_name()). Use the accessor before you try .apply.

4. Reading huge CSVs without dtype or chunksize

If you read a 5 GB CSV with pd.read_csv(path) and no hints, pandas has to guess every column's dtype, it stores every string column as object, and it can need several times the file size in RAM. Always pass dtype=, parse_dates= and usecols=. If you read the same file again and again, convert it to Parquet once.

5. Forgetting that groupby preserves the index

python

g = df.groupby("team")["points"].mean()
g.loc["A"]                              # works, because team is now the index

# To get a flat DataFrame back
g = df.groupby("team")["points"].mean().reset_index()

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

After groupby(...).agg(...), the group keys become the index. If later code expects a flat table with integer indexing, call .reset_index() afterwards.

6. Ignoring SettingWithCopyWarning

In pandas 2, this warning means pandas can't tell whether your assignment changed the original frame or a copy. Don't hide the warning: fix the code. The cause is almost always a chained-indexing assignment, or a missing .copy() on a slice that you plan to change. In pandas 3 (Copy-on-Write), this warning no longer exists. A slice always behaves as a copy, so changing it never changes the original, and chained assignment gives a ChainedAssignmentError warning instead.

🎯 Your Turn: Monthly Revenue by Region

Given a sales DataFrame with columns date, product, region, revenue, compute monthly revenue per region, returned as a DataFrame where:

Rows are months (a Period or month-end date),
Columns are regions,
Cells are total revenue for that month/region combination.

Expected shape: if there are 12 months and 3 regions, the result is (12, 3).

python

import pandas as pd

sample = pd.DataFrame({
    "date":    ["2025-01-15", "2025-01-20", "2025-02-05", "2025-02-12",
                "2025-03-01", "2025-03-08"],
    "product": ["A", "B", "A", "C", "B", "A"],
    "region":  ["EU", "US", "EU", "US", "EU", "US"],
    "revenue": [100, 200, 150, 250, 120, 300],
})

monthly_by_region(sample)
# region        EU   US
# month                
# 2025-01      100  200
# 2025-02      150  250
# 2025-03      120  300

+ setup added so this can run · defines monthly_by_region

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

def monthly_by_region(*_a, **_kw):
    print('-> monthly_by_region() called')
    return _AutoMock('monthly_by_region()')

Constraints:

Parse date to datetime with pd.to_datetime.
Extract the month as a Period (.dt.to_period("M")). This is cleaner than a date string.
Use groupby + sum, then unstack to pivot region into columns.
Fill any missing region/month combinations with 0 (some months may have no sales in a region).

Skeleton:

python

import pandas as pd

def monthly_by_region(df):
    # TODO 1: convert df["date"] to datetime
    # TODO 2: create a "month" column via .dt.to_period("M")
    # TODO 3: groupby ["month", "region"], sum revenue
    # TODO 4: unstack the "region" level so it becomes columns
    # TODO 5: fill NaN with 0 (months with no sales in some region)
    ...

Hint 1: Extracting the month as a Period

After df["date"] = pd.to_datetime(df["date"]), you can do df["month"] = df["date"].dt.to_period("M"). A Period like 2025-01 is more compact than a date string and sorts correctly. Alternatively, df["date"].dt.strftime("%Y-%m") gives a string. It sorts correctly as text, but it is no longer a date type.

Hint 2: Group then unstack

df.groupby(["month", "region"])["revenue"].sum() returns a Series with a MultiIndex of (month, region). Calling .unstack("region") moves the region level from the index to the columns, which gives you the wide shape you want. Chain .fillna(0) at the end so missing combinations show as 0 rather than NaN.

Show full solution

python

import pandas as pd

def monthly_by_region(df):
    """Monthly revenue per region, regions as columns, months as rows."""
    df = df.copy()                                  # do not change the caller's DataFrame
    df["date"]  = pd.to_datetime(df["date"])
    df["month"] = df["date"].dt.to_period("M")

    return (
        df.groupby(["month", "region"])["revenue"]
          .sum()
          .unstack("region")
          .fillna(0)
    )


sample = pd.DataFrame({
    "date":    ["2025-01-15", "2025-01-20", "2025-02-05", "2025-02-12",
                "2025-03-01", "2025-03-08"],
    "product": ["A", "B", "A", "C", "B", "A"],
    "region":  ["EU", "US", "EU", "US", "EU", "US"],
    "revenue": [100, 200, 150, 250, 120, 300],
})

print(monthly_by_region(sample))
# region        EU   US
# month                
# 2025-01    100.0  200.0
# 2025-02    150.0  250.0
# 2025-03    120.0  300.0


# Harder test: some month and region pairs have no sales
gappy = pd.DataFrame({
    "date":    ["2025-01-15", "2025-02-20", "2025-03-01"],
    "region":  ["EU", "US", "EU"],
    "revenue": [100, 200, 300],
})

print(monthly_by_region(gappy))
# region        EU     US
# month                  
# 2025-01    100.0    0.0
# 2025-02      0.0  200.0
# 2025-03    300.0    0.0

Why .copy() at the top: without it, df["month"] = ... changes the caller's DataFrame. A function that adds a column to its input without saying so causes bugs that are hard to find. One line of .copy() prevents this.

Why Period instead of strftime: Period is a real time type. You can do arithmetic on it (period + 1 = next month), sort it numerically, and resample to it. A string is only text. It sorts alphabetically (mostly correct, but "2025-12" < "2025-2" if you forget zero-padding) and can't be incremented.

The general pattern: groupby([time_bucket, category]).agg(...).unstack(category) is the standard recipe for "show me X over time, split by Y". Swap sum for mean, count, nunique as the question requires. Swap unstack for pivot_table if you have duplicates within a (time, category) cell that need aggregating. The steps are always the same: put the time into buckets, group by the category, aggregate the metric, and unstack to pivot.

BI tools, data warehouses and dashboards do the same thing at a much larger scale: split by date, group by category, aggregate, pivot. Pandas lets you do it in three lines.

What You Learned

A DataFrame is an ordered dict of Series sharing one Index. Operations are vectorised, column by column.
The Index matters: set_index, reset_index and multi-indexes (pd.MultiIndex.from_tuples) make joins and pivots simple.
.loc (labels, inclusive), .iloc (positions, half-open), .at/.iat (scalar, fastest), .query (string predicate). Mixing them up causes many bugs.
The chained-indexing trap: df[mask][col] = v fails without an error. Always one .loc[mask, col] = v.
Missing data via .isna, .dropna, .fillna, .ffill / .bfill. Choose the strategy on purpose.
Pick the right apply: Series.map (element), DataFrame.apply(axis=1) (row), groupby.transform (per-group broadcast), groupby.agg (per-group reduce).
groupby is split-apply-combine. Multi-column aggs via the kwarg form: g.agg(total=("col", "sum"), ...).
Four merge modes: inner, left, right, outer. Use validate= to catch joins that multiply rows.
Reshape: pivot (unique pairs), pivot_table (with aggregation), melt (wide → long), stack/unstack (move multi-index levels).
Time series: pd.to_datetime → DatetimeIndex → resample and rolling understand calendar offsets.
Performance: use categorical dtype for low-cardinality strings and eval/query for big-frame expressions. Declare dtype= on read_csv, and prefer Parquet for repeated reads.
Never iterrows when you can vectorise. Never .apply when there's a .str.* or .dt.* accessor.

Next: Visualisation with Matplotlib (and a Touch of Seaborn): turn the DataFrame you just built into a chart people can read.

1 · The lesson

1. The Mental Model: An Ordered Dict of Aligned Series

2. Index Essentials

3. Selecting: `.loc`, `.iloc`, `.at`, `.iat`, `.query`

4. The Chained-Indexing Trap

5. Missing Data

6. Apply, Map, Transform, Agg

7. GroupBy In Depth

`transform`: send per-group results back to each row

`filter`: drop whole groups by a condition

`apply`: when nothing else fits

8. Merging Tables

9. Concatenating

10. Reshaping: Pivot, Melt, Stack, Unstack

11. Time Series: A Short Overview

12. Performance: `eval`, `query`, Categorical

Categorical dtype for low-cardinality strings

`df.eval` and `df.query`: string expressions, evaluated in C

Read efficiently

Common Mistakes

🎯 Your Turn: Monthly Revenue by Region

What You Learned

Practice this

Pandas DataFrames: Command Center

1. The Mental Model: An Ordered Dict of Aligned Series

2. Index Essentials

3. Selecting: .loc, .iloc, .at, .iat, .query

4. The Chained-Indexing Trap

5. Missing Data

6. Apply, Map, Transform, Agg

7. GroupBy In Depth

transform: send per-group results back to each row

filter: drop whole groups by a condition

apply: when nothing else fits

8. Merging Tables

9. Concatenating

10. Reshaping: Pivot, Melt, Stack, Unstack

11. Time Series: A Short Overview

12. Performance: eval, query, Categorical

Categorical dtype for low-cardinality strings

df.eval and df.query: string expressions, evaluated in C

Read efficiently

Common Mistakes

🎯 Your Turn: Monthly Revenue by Region

What You Learned

3. Selecting: `.loc`, `.iloc`, `.at`, `.iat`, `.query`

`transform`: send per-group results back to each row

`filter`: drop whole groups by a condition

`apply`: when nothing else fits

12. Performance: `eval`, `query`, Categorical

`df.eval` and `df.query`: string expressions, evaluated in C