pandas: DataFrames, filtering & groupby

A DataFrame is a dict of columns

Stop picturing a spreadsheet and picture a dict. A DataFrame maps column names to columns, and each column is a Series: a numpy array of one dtype, plus a labelled index that lines its values up with every other column. Everything from the numpy lesson still applies, one column at a time. The index is the piece that has no list equivalent, and it is what makes rows line up after a filter, a join or a sort.

Loading from a string buffer

read_csv takes a path, but it also takes any file-like object. StringIO makes a string look like a file, which is how you load an API response, or test a parser, without a fixture on disk:

import pandas as pd
from io import StringIO

text = """region,rep,amount
west,Ada,100
east,Sam,250
west,Mo,50
"""
df = pd.read_csv(StringIO(text))
df.dtypes      # region object, rep object, amount int64
df.head()      # the first rows
df.shape       # (3, 3)

read_csv infers a dtype per column from the values it sees, which is convenient right up until a blank cell changes its mind.

Selecting

df["amount"]                 # a Series (one column)
df[["region", "amount"]]     # a DataFrame (a list of columns, hence the double brackets)
df.loc[0, "amount"]          # by label: row index 0, column "amount"
df.iloc[0, 2]                # by position: first row, third column

The double brackets trip everyone once. df["a"] asks for one column and gets a Series; df[["a"]] passes a list of names and gets a one-column DataFrame back.

Filtering with a boolean mask

Compare a column, then index the frame with the result:

big = df["amount"] > 100          # a Series of booleans
df[big]                            # the rows where it is True
df[(df["amount"] > 100) & (df["region"] == "east")]

Two rules for combining masks: use & and | rather than and and or (those ask for one truth value and raise on a Series), and parenthesize each comparison, because & binds tighter than >.

Reading with a mask, writing with .loc

Reading through two selections is fine. Writing through two selections is not, because the first one may hand you a copy. Do the whole thing in one indexer:

df.loc[df["amount"] > 100, "amount"] = 0     # one step, writes into df

groupby: split, apply, combine

df.groupby("region")["amount"].sum()
# region
# east    250
# west    150

groupby splits the rows by key, applies an aggregation to each group, and combines the answers into a new indexed result. In plain Python this is the defaultdict accumulate loop you already know; groupby is that loop with a name and a fast implementation. .agg({"amount": ["sum", "mean"], "rep": "count"}) runs several aggregations at once.

Missing values

A blank cell becomes NaN. NaN is a float, it never equals anything (not even itself), and it propagates through arithmetic. So df[df["amount"] == np.nan] matches nothing at all, no matter how many blanks there are:

df["amount"].isna()          # the correct test, a boolean mask
df["amount"].fillna(0)       # substitute a value
df.dropna(subset=["amount"]) # drop the rows that are missing it

What runs where

pandas is not bundled into the Python build behind this browser sandbox, so import pandas fails here (and it would pull numpy in with it). In a real environment it is one pip install pandas (or uv add pandas) away, using the setup from "Running Python & installing packages". The exercises below build the same three mechanics over lists of dicts, missing values and all, so the model you take to the terminal already accounts for the parts that bite.

Interview nuance: for a data role, the question behind the question is almost always about missing data. Anyone can write groupby("region").sum(). The signal is knowing that a missing group key silently removes the row from the answer, that a missing value is skipped by the aggregation instead of poisoning it, and that one blank cell turns an integer column into float64 and quietly breaks a join. Say what the default does, then say how you would check the total.