Pandas Foundations: Tables, Filtering, Grouping, and Joins

A support team keeps its tickets in a spreadsheet. They want to find tickets taking at least an hour, summarize time by team, combine monthly extracts, and attach each team’s region. pandas lets them express those steps in reusable Python code. Spreadsheets can also automate them; here we use a script to make each selection, calculation, and check explicit.

This article builds one small table and examines four common problems: missing observations excluded by a filter, chained assignment that leaves the source unchanged, an aggregation requested on unsuitable types, and duplicate join keys that multiply rows. The examples show how to distinguish a valid computation from the question we intended to answer.

Each code block continues the same session, so run them in order in one notebook or Python prompt. Outputs were recorded with pandas 3.0.5 on Python 3.12. Version 3 changed several defaults, and the notes below say where an older version behaves differently.

A DataFrame is a table you can question

A DataFrame holds rows and columns, where each column has its own type and its own name. Building one from a dictionary is the quickest way to get a table you can experiment on: each key becomes a column name and each list becomes that column’s values. Every list must be the same length: the values at one position together form a row. pd is the conventional alias, so using it makes examples easier to follow.

import pandas as pd

tickets = pd.DataFrame({
    "ticket_id": ["T-101", "T-102", "T-103", "T-104", "T-105", "T-106"],
    "team": ["billing", "billing", "network", "network", "network", "access"],
    "minutes": [35, 120, 55, 240, 15, 90],
    "reopened": [False, True, False, True, False, False],
})

print(tickets.shape)
# (6, 4)
print(tickets)
#   ticket_id     team  minutes  reopened
# 0     T-101  billing       35     False
# 1     T-102  billing      120      True
# 2     T-103  network       55     False
# 3     T-104  network      240      True
# 4     T-105  network       15     False
# 5     T-106   access       90     False

The leftmost column of the printout has no name. It is the index, the row label pandas assigns automatically, and it matters later: it survives filtering, it is what concatenation stacks, and it is what an alignment uses to decide which value belongs to which row. shape reports rows first and columns second, and reading it out loud after every step is the cheapest way to notice that a filter removed everything or a join doubled the table.

Look at the table before you trust it

info() prints the structure: how many rows, which columns, how many non-null values each column has, and the type pandas inferred. The non-null count is the part to read first, because it is where missing values become visible before they reach an average. The per-column lines and the memory figure that follow depend on the pandas version and the platform, so only the stable lines are recorded here.

tickets.info()
# <class 'pandas.DataFrame'>
# RangeIndex: 6 entries, 0 to 5
# Data columns (total 4 columns):
# dtypes: bool(1), int64(1), str(2)

Inspect inferred types before calculating. A nonnumeric token in a CSV field can cause text inference or an import error, depending on the import options. The two text columns here use the default str dtype in pandas 3; the same constructor normally used object in pandas 2. But object is a general container that can also hold numbers or mixed Python objects. Neither dtype proves that values are clean. For a field expected to be numeric, pd.to_numeric(column, errors="raise") can expose invalid tokens; coercing errors to missing values requires reporting the newly missing entries.

print(tickets.dtypes)
# ticket_id      str
# team           str
# minutes      int64
# reopened      bool
# dtype: object
print(tickets.describe().round(1).to_string())
#        minutes
# count      6.0
# mean      92.5
# std       81.5
# min       15.0
# 25%       40.0
# 50%       72.5
# 75%      112.5
# max      240.0

describe() selects numeric columns by default when the DataFrame contains numeric data. In this table it summarizes minutes, excluding text and boolean columns. With only text columns, its default instead describes those columns using count, unique, top, and frequency. Use describe(include="all") to include all types. Here the mean is 92.5 minutes and the median is 72.5; the 240-minute ticket raises the mean. This small sample describes these tickets, not a stable ranking of team performance.

One column is a Series

Selecting a single column with brackets returns a Series: one dimension, one type, and the same index as the table it came from. The Series is where the arithmetic lives. Asking for a list of columns instead returns a DataFrame, and the difference between one name and a list of names is the difference between a column and a narrower table.

minutes = tickets["minutes"]
print(type(minutes).__name__, minutes.dtype, minutes.index.tolist())
# Series int64 [0, 1, 2, 3, 4, 5]
print(minutes.sum(), minutes.mean(), minutes.max())
# 555 92.5 240
pair = tickets[["ticket_id", "minutes"]]
print(type(pair).__name__, pair.shape)
# DataFrame (6, 2)

Attribute access, tickets.team, also works and is a trap worth knowing rather than using. A name with a space in it cannot be written that way at all, and a column whose name matches a DataFrame method resolves to the method instead of the data. Brackets are unambiguous for every column name, so prefer them and read the difference here rather than debugging it in a pipeline.

counts = pd.DataFrame({"team": ["billing", "network"], "count": [2, 3]})
print(type(counts.count).__name__)
# method
print(counts["count"].tolist())
# [2, 3]

Labels and positions are different

loc selects by label and iloc selects by position. On a fresh table the labels happen to be 0, 1, 2 and the two look interchangeable, which is why the distinction is usually learned the hard way after a sort or a filter has left the labels out of order. One behavioural difference shows up immediately: a loc slice includes its endpoint, because it is naming labels, while an iloc slice stops before it, like every other Python slice.

print(tickets.loc[1:3, "ticket_id"].tolist())
# ['T-102', 'T-103', 'T-104']
print(tickets.iloc[1:3, 0].tolist())
# ['T-102', 'T-103']
print(tickets.iloc[0, 2], tickets.loc[0, "minutes"])
# 35 35

Positional access by column number, tickets.iloc[0, 2], is also the most fragile thing in a notebook: insert a column upstream and the number quietly points somewhere else. Name the column when you know it, and keep iloc for cases where position is the actual question, such as “show me the first three rows”.

Filtering with a boolean mask

A comparison against a column produces a boolean mask: a Series of True or False with one entry per row and the same index as the table. Passing that mask back into brackets keeps the rows marked True. Combine masks with &, |, and ~ — the element-wise operators — and wrap each comparison in parentheses, because those operators bind more tightly than >= and the expression would otherwise be grouped the wrong way.

long_mask = tickets["minutes"] >= 60
print(long_mask.tolist())
# [False, True, False, True, False, True]
print(tickets[long_mask].shape)
# (3, 4)
print(tickets[(tickets["minutes"] >= 60) & (~tickets["reopened"])]["ticket_id"].tolist())
# ['T-106']
try:
    tickets[(tickets["minutes"] >= 60) and (tickets["reopened"])]
except ValueError as error:
    print("ValueError:", str(error)[:46])
# ValueError: The truth value of a Series is ambiguous. Use

The last block explains a message everyone meets once. Python’s and wants a single true-or-false answer, and a mask of six values cannot give one, so pandas raises rather than guessing. Use & for “both conditions per row”, and keep and for plain Python values.

Missing values change the answer quietly

Now put one gap in the data. In this float-backed example, nan >= 60 is False, so filtering cannot distinguish an unmeasured duration from a measured value below 60. This is not a rule for all comparisons: nan != 60 is True, while a comparison on a nullable Int64 Series can return pd.NA. A nullable boolean mask excludes NA entries when selecting rows. Use isna() to identify missing observations explicitly. By default mean() skips missing values, count() counts present values, and len() counts all rows.

gaps = tickets.copy()
gaps.loc[2, "minutes"] = None
print(gaps["minutes"].tolist())
# [35.0, 120.0, nan, 240.0, 15.0, 90.0]
print((gaps["minutes"] >= 60).tolist())
# [False, True, False, True, False, True]
print(gaps["minutes"].mean(), gaps["minutes"].count(), len(gaps))
# 100.0 5 6

The NumPy-backed integer column became floating point to accommodate nan. Nullable Int64, with a capital I, can retain integer values and represent missing entries with pd.NA. The mean rose from 92.5 to 100.0 solely because the 55-minute observation was removed. Report both present and missing counts, and choose a missing-data policy from the meaning of the gap rather than filling it merely to keep a calculation running.

Updating the source or a filtered table

A filtered table is a new object, not a live view onto the original. This matters when you try to write through one. The assignment below looks like it sets the long tickets to zero; it selects rows, builds a new object, writes to that, and throws it away. Under pandas 3’s copy-on-write rules this raises a ChainedAssignmentError warning and changes nothing, so the warning is captured here to show it explicitly. With copy-on-write disabled in older versions, chained assignments could warn or behave differently depending on the selection; the warning itself did not determine whether the source changed.

import warnings

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    tickets[tickets["minutes"] >= 60]["minutes"] = 0
print(caught[0].category.__name__)
# ChainedAssignmentError
print(tickets["minutes"].tolist())
# [35, 120, 55, 240, 15, 90]

To change the original, use one loc assignment naming rows and column together. Under pandas 3, a distinct filtered object already behaves independently for pandas assignments; the explicit copy() below also makes the intent clear when supporting older versions. This does not isolate two names bound to the same object or recursively copy objects inside cells. The distinction from sharing a mutable object still matters.

breached = tickets[tickets["minutes"] >= 60].copy()
breached["minutes"] = breached["minutes"] * 2
print(breached["minutes"].tolist())
# [240, 480, 180]
print(tickets["minutes"].tolist())
# [35, 120, 55, 240, 15, 90]

Adding columns without a loop

Assigning to a new name adds a column, and the expression on the right runs over the whole column at once instead of row by row. That is what “vectorized” means here: no loop in your code, and the arithmetic handled inside pandas. The same loc form that writes safely also writes conditionally — when creating a new column as below, rows the mask does not select get a missing value; for an existing column they retain their previous values, which fillna() can then turn into a default.

tickets["hours"] = (tickets["minutes"] / 60).round(2)
tickets.loc[tickets["minutes"] >= 60, "priority"] = "high"
print(tickets["hours"].tolist())
# [0.58, 2.0, 0.92, 4.0, 0.25, 1.5]
print(tickets["priority"].fillna("normal").tolist())
# ['normal', 'high', 'normal', 'high', 'normal', 'high']

Column-at-a-time code is usually faster than a Python loop over rows, because many numeric operations use compiled array kernels; not every pandas dtype is contiguous and not every column operation avoids Python-level work, as NumPy vectorization and broadcasting describes for arrays. Treat that as a reason to write it this way, not as a measured number: if speed is the question, time both versions on your own data. The stronger everyday argument is that one expression per rule is easier to read and to correct than a loop with an accumulator.

Grouping: split, apply, combine

groupby splits rows by a key, applies a function to each group, and, when aggregating, combines the results into a table with one row per group — the split-apply-combine pattern, and the same idea as GROUP BY in SQL. Ask for the aggregate on a column you can actually aggregate. Calling mean() across the whole frame raises, because the text columns have no mean; older pandas versions silently dropped those columns instead, which is why old notebooks produce narrower results than their code suggests.

try:
    tickets.groupby("team").mean()
except TypeError as error:
    print("TypeError:", error)
# TypeError: dtype 'str' does not support operation 'mean'
print(tickets.groupby("team")["minutes"].mean().round(1).to_dict())
# {'access': 90.0, 'billing': 77.5, 'network': 103.3}

A single statistic is rarely enough to act on: 103.3 minutes for the network team could be three similar tickets or two quick ones and a disaster. Named aggregation answers several questions in one pass, giving each output column a name you choose and the pair of source column and function it comes from. Any function that takes a Series and returns one value can join the built-in names.

summary = tickets.groupby("team").agg(
    tickets=("ticket_id", "count"),
    avg_minutes=("minutes", "mean"),
    worst=("minutes", "max"),
).round(1)
print(summary.to_string())
#          tickets  avg_minutes  worst
# team
# access         1         90.0     90
# billing        2         77.5    120
# network        3        103.3    240

def p90(series):
    return series.quantile(0.9)

print(tickets.groupby("team")["minutes"].agg(["mean", "max", p90]).round(1).to_string())
#           mean  max    p90
# team
# access    90.0   90   90.0
# billing   77.5  120  111.5
# network  103.3  240  203.0

Read the counts next to the averages before drawing a conclusion. The access team’s 90.0 minutes is one ticket, and its 90th percentile is that same ticket seen from another angle. Grouping makes small groups look like findings, and the count column is what stops that.

The group that disappears

Rows whose grouping key is missing are dropped by default, and the result gives no sign that they existed. A total taken from the grouped table then disagrees with a total taken from the original, usually long after anyone would connect the two. Pass dropna=False to keep them as their own group, and reconcile the row counts as a habit.

unassigned = tickets.copy()
unassigned.loc[5, "team"] = None
print(unassigned.groupby("team")["minutes"].size().to_dict())
# {'billing': 2, 'network': 3}
print(unassigned.groupby("team", dropna=False)["minutes"].size().to_dict())
# {'billing': 2, 'network': 3, nan: 1}

size() counts rows per group, including rows whose values are missing, while count() on a column counts only present values there. Both are useful, and picking the wrong one is how “we handled 6 tickets” becomes “we handled 5”.

Stacking tables with concat

Concatenation stacks tables that describe the same kind of thing: this month’s tickets under last month’s. It keeps the original index labels, so the result has repeated labels until you renumber — ignore_index=True during the concatenation, or reset_index(drop=True) afterwards. Repeated labels are not an error, but they make label-based selection ambiguous, which is a confusing thing to discover two steps later.

march = tickets.head(3)[["ticket_id", "team", "minutes"]]
april = pd.DataFrame({
    "ticket_id": ["T-107", "T-108"],
    "team": ["billing", "access"],
    "minutes": [20, 310],
})
print(pd.concat([march, april]).index.tolist())
# [0, 1, 2, 0, 1]
stacked = pd.concat([march, april], ignore_index=True)
print(stacked.index.tolist(), stacked.shape)
# [0, 1, 2, 3, 4] (5, 3)

By default, row-wise concatenation takes the union of column labels rather than rejecting a mismatch. Here a renamed column leaves minutes with three observed values and mins with two, so their means describe different subsets, not all five tickets. Check column names, types, units, and row meaning before appending. Renumbering the index does not remove duplicate tickets.

print(pd.concat([march, april.rename(columns={"minutes": "mins"})], ignore_index=True).to_string())
#   ticket_id     team  minutes   mins
# 0     T-101  billing     35.0    NaN
# 1     T-102  billing    120.0    NaN
# 2     T-103  network     55.0    NaN
# 3     T-107  billing      NaN   20.0
# 4     T-108   access      NaN  310.0

With axis=1, concat aligns rows by index label. Sorting two extracts differently does not break alignment if the labels still identify the same entities. Trouble arises when independently reset row numbers happen to match but refer to different tickets. Use a validated entity key, either as the index or in merge, and check uniqueness before relying on that alignment.

Joining tables with merge

merge matches keys. A left join retains left rows, an inner join retains matches, and an outer join also includes unmatched rows from both sides; multiple matches can repeat a row. Unlike a SQL equality join, pandas matches missing keys to missing keys. Check that both key columns have compatible dtypes. If a missing team means unknown, separate or reject those keys before merging. A missing region can also have existed in a matched lookup row, so use indicator=True to distinguish an unmatched key from a matched row with missing data.

teams = pd.DataFrame({
    "team": ["billing", "network", "payments"],
    "region": ["kr", "us", "kr"],
})
print(pd.merge(stacked, teams, on="team", how="left").to_string(index=False))
# ticket_id    team  minutes region
#     T-101 billing       35     kr
#     T-102 billing      120     kr
#     T-103 network       55     us
#     T-107 billing       20     kr
#     T-108  access      310    NaN
print(pd.merge(stacked, teams, on="team", how="inner").shape)
# (4, 4)
print(pd.merge(stacked, teams, on="team", how="outer", indicator=True)["_merge"].value_counts().to_dict())
# {'both': 4, 'left_only': 1, 'right_only': 1}

The counts say what the tables disagree about: one ticket belongs to a team the lookup table has never heard of, and one team in the lookup table has no tickets. An inner join would have removed the first quietly and dropped the row count from five to four. Neither is wrong, but choosing between them is a decision about the data, not a default to accept.

The other join hazard is duplicates on the key side. If the right table has two rows for one team, every matching left row is repeated once per match, and five tickets become eight. That is correct join behaviour and almost never what a lookup was meant to do. validate="many_to_one" states the expectation and fails loudly when the lookup is not unique, which is the same reasoning as join cardinality in SQL.

owners = pd.DataFrame({"team": ["billing", "billing"], "owner": ["ada", "leo"]})
print(len(stacked), len(pd.merge(stacked, owners, on="team", how="left")))
# 5 8
try:
    pd.merge(stacked, owners, on="team", how="left", validate="many_to_one")
except pd.errors.MergeError as error:
    print("MergeError:", str(error).splitlines()[0])
# MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Putting it together

The pieces make one short report: read a CSV, attach the region, derive a flag for at least 60 minutes, and summarize by region. The synthetic inputs have unique ticket IDs and measured nonnegative durations; real inputs need those checks before this report. Explicit text dtypes preserve identifier strings, validate="many_to_one" guards the lookup, and size counts rows. A temporary directory confines file creation and cleanup to this exercise, including when an exception occurs. The unmatched team remains as a missing-region group.

from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory(prefix="pandas-foundations-") as workdir:
    csv_path = Path(workdir) / "tickets.csv"
    stacked.to_csv(csv_path, index=False)
    loaded = pd.read_csv(csv_path, dtype={"ticket_id": "str", "team": "str"})
    report = (
        pd.merge(loaded, teams, on="team", how="left", validate="many_to_one")
        .assign(breached=lambda frame: frame["minutes"] >= 60)
        .groupby("region", dropna=False)
        .agg(tickets=("ticket_id", "size"), breached=("breached", "sum"), avg_minutes=("minutes", "mean"))
        .round(1)
    )
    print(report.to_string())
    #         tickets  breached  avg_minutes
    # region
    # kr            3         1         58.3
    # us            1         0         55.0
    # NaN           1         1        310.0
print(csv_path.exists())
# False

This is the shape of most analysis work: load, join, derive, group, and read the result with the row counts in view. It stays in memory, so suitability depends on the data and intermediate memory requirements, not on whether the source is a spreadsheet or a warehouse — working with tabular data at scale covers what changes when the data no longer fits, and Python for data engineers covers turning a notebook like this into code that runs on a schedule.

Exercises

1. From tickets, list the ticket ids handled by the network team in under an hour. Use one expression, and say why each comparison needs its own parentheses.

print(tickets[(tickets["team"] == "network") & (tickets["minutes"] < 60)]["ticket_id"].tolist())
# ['T-103', 'T-105']

2. The code below is meant to cap any ticket over 300 minutes at 300, and it does nothing. Show what pandas reports, then write the version that works.

fix = pd.DataFrame({"ticket_id": ["T-201", "T-202"], "minutes": [30, 400]})
with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    fix[fix["minutes"] > 300]["minutes"] = 300
print(caught[0].category.__name__, fix["minutes"].tolist())
# ChainedAssignmentError [30, 400]
fix.loc[fix["minutes"] > 300, "minutes"] = 300
print(fix["minutes"].tolist())
# [30, 300]

3. After a left merge of stacked with teams, report how many rows matched and which team did not, without reading the table by eye.

check = pd.merge(stacked, teams, on="team", how="left", indicator=True)
print(check["_merge"].value_counts().to_dict())
# {'both': 4, 'left_only': 1, 'right_only': 0}
print(check.loc[check["_merge"] == "left_only", "team"].tolist())
# ['access']

References: pandas indexing and selecting data, pandas group by, pandas merge, join, and concatenate, pandas copy-on-write.