Pandas DataFrame Quick Reference

A compact reference for common Pandas DataFrame operations used in data engineering.

Beginner

Before you use this reference

This page is a quick reference, not an introduction to Pandas. The examples will make significantly more sense if you have already read Data Transformations with Pandas, Data Quality with Pandas, and Batch and Incremental Processing, or already have practical experience with Pandas and basic SQL.

If concepts such as DataFrame, Series, boolean mask, GROUP BY, aggregation, or incremental processing are unfamiliar, start with those learning articles first. For the SQL side, see SQL Querying and Aggregations and the SQL Quick Reference.

SQL knowledge is useful because many Pandas operations have direct SQL equivalents. This page shows those relationships where they improve understanding.

DataFrame mental model#

A DataFrame is a labeled table. A Series is one labeled column:

Text

DataFrame┌──────────┬─────────────┬────────┐│ order_id │ customer_id │ amount │├──────────┼─────────────┼────────┤│ 1        │ 101         │ 120.0  ││ 2        │ 102         │ 80.0   ││ 3        │ 101         │ 250.0  │└──────────┴─────────────┴────────┘

Python

df                       # DataFramedf["amount"]             # Seriesdf[["order_id", "amount"]]  # DataFrame

The object shape matters. Many Pandas mistakes come from expecting a Series when an operation returned a DataFrame, or expecting one row per input row when an aggregation reduced the data.

Creating and inspecting DataFrames#

Operation	What to inspect
`df.head()`	First rows
`df.tail()`	Last rows
`df.info()`	Columns, non-null counts, and types
`df.describe()`	Numeric distribution summary
`df.shape`	`(row_count, column_count)`
`df.columns`	Column labels
`df.dtypes`	Type of every column
`len(df)`	Number of rows
`df.empty`	Whether the DataFrame has no rows

Python

df.head()df.tail()df.info()df.describe()df.shapedf.columnsdf.dtypeslen(df)df.empty

Inspect dtypes and shape before transforming or inserting data. They often explain errors more quickly than inspecting individual values.

Selecting columns#

Python

amount = df["amount"]

Text

DataFrame → Series

Select several columns with a list:

Python

orders = df[    ["order_id", "customer_id", "amount"]]

Text

DataFrame → DataFrame

Filtering rows#

Basic filter:

Python

df = df[df["amount"] > 100]

Multiple conditions:

Python

df = df[    (df["amount"] > 100)    & (df["status"] == "completed")]

OR:

Python

df = df[    (df["status"] == "completed")    | (df["status"] == "processing")]

NOT:

Python

df = df[    ~(df["status"] == "cancelled")]

Membership:

Python

df = df[    df["status"].isin(        ["completed", "processing"]    )]

The operators are & for AND, | for OR, and ~ for NOT. Always put parentheses around individual conditions.

Selecting rows and columns with `.loc`#

.loc[rows, columns] selects both dimensions at once:

Python

result = df.loc[    df["amount"] > 100,    ["order_id", "amount"],]

Text

DataFrame → DataFrame

Missing values#

Python

df["customer_id"].isna()df["customer_id"].notna()

Both return boolean masks. Fill a missing numeric value:

Python

df["amount"] = (    df["amount"].fillna(0))

Drop rows missing a required field:

Python

df = df.dropna(    subset=["customer_id"])

Type conversion#

Python

df["quantity"] = (    df["quantity"].astype(int))
df["amount"] = (    df["amount"].astype(float))
df["created_at"] = pd.to_datetime(    df["created_at"])

For values that may not parse cleanly, use errors="coerce". Invalid values become missing values instead of raising an exception:

Python

df["amount"] = pd.to_numeric(    df["amount"],    errors="coerce",)

Validate or fill the resulting missing values before writing to a strict database schema.

Sorting#

Ascending:

Python

df = df.sort_values(    "created_at")

Descending:

Python

df = df.sort_values(    "amount",    ascending=False,)

Multiple columns:

Python

df = df.sort_values(    ["customer_id", "created_at"])

Duplicates#

Check for duplicates:

Python

duplicated = df.duplicated()

Remove exact duplicates:

Python

df = df.drop_duplicates()

Deduplicate by a business key:

Python

df = df.drop_duplicates(    subset=["order_id"])

For an incremental batch, keep the last occurrence:

Python

df = df.drop_duplicates(    subset=["order_id"],    keep="last",)

Choose keep based on the source ordering and the correction policy. A deduplication call does not by itself explain which record is authoritative.

Creating and modifying columns#

Arithmetic:

Python

df["total"] = (    df["price"] * df["quantity"])

Boolean column:

Python

df["is_large"] = (    df["amount"] > 1000)

Integer flag:

Python

df["is_large"] = (    df["amount"] > 1000).astype(int)

GroupBy#

groupby() creates groups; it does not calculate anything by itself:

Python

grouped = df.groupby(    "customer_id")

Text

customer_id = 101    row    row    row
customer_id = 102    row    row

Aggregation#

Single aggregation:

Python

totals = (    df.groupby("customer_id")["amount"]    .sum())

Text

DataFrame → one value per group

Multiple aggregations:

Python

stats = (    df.groupby("customer_id")["amount"]    .agg(["min", "max", "mean", "sum"]))

Named aggregation:

Python

stats = (    df.groupby("customer_id")    .agg(        order_count=("order_id", "count"),        total_amount=("amount", "sum"),        avg_amount=("amount", "mean"),        first_order=("created_at", "min"),        last_order=("created_at", "max"),    )    .reset_index())

Text

many rows per group → one row per group

SQL equivalent:

SQL

SELECT    customer_id,    count(order_id) AS order_count,    sum(amount) AS total_amountFROM ordersGROUP BY customer_id

First and last row#

Python

first = (    df.groupby("customer_id")    .first())
last = (    df.groupby("customer_id")    .last())

first() and last() depend on row ordering. Sort first when chronological first or last is required:

Python

df = df.sort_values(    "created_at")

Transform#

transform() calculates per group and returns a result aligned with the original DataFrame rows:

Python

df["customer_total"] = (    df.groupby("customer_id")["amount"]    .transform("sum"))

Text

DataFrame → Series with the original row count

Input:

Text

customer  amount101       10101       20102       50

After transform("sum"):

Text

customer  amount  customer_total101       10      30101       20      30102       50      50

Map#

map() uses Series values as lookup keys:

Python

categories = pd.Series({    101: "premium",    102: "standard",})
df["category"] = (    df["customer_id"]    .map(categories))

Text

key → lookup → value

It also attaches an existing group result back to rows:

Python

totals = (    df.groupby("customer_id")["amount"]    .sum())
df["customer_total"] = (    df["customer_id"]    .map(totals))

transform() is simpler for this exact group-sum case, but map() is useful when a lookup Series already exists.

Apply#

apply() runs arbitrary custom Python logic for each group:

Python

def classify(group):    if group["amount"].sum() > 1000:        return "high_value"
    return "regular"

result = (    df.groupby("customer_id")    .apply(classify))

Text

DataFrame → depends on the function

Prefer built-in Pandas operations, agg(), or transform() when they express the logic clearly. apply() is powerful but often slower and harder to reason about.

Critical comparison#

Operation	Mental model	Typical result
`agg`	Reduce each group	One row or value per group
`transform`	Calculate per group and align back	Same row count as input
`apply`	Execute custom logic	Depends on the function
`map`	Key → value lookup	Same Series length

Merge and join#

Merge DataFrames using a key:

Python

result = orders.merge(    customers,    on="customer_id",    how="left",)

Common join types are inner, left, right, and outer. A left join keeps every row from the left DataFrame; an inner join keeps only matching keys on both sides.

SQL equivalent:

SQL

SELECT *FROM ordersLEFT JOIN customers    ON orders.customer_id = customers.customer_id

Different key names:

Python

result = orders.merge(    customers,    left_on="customer_id",    right_on="id",    how="left",)

Check key uniqueness before merging. A many-to-many key can multiply rows.

Concat#

concat() stacks DataFrames:

Python

df = pd.concat(    [batch_a, batch_b],    ignore_index=True,)

Text

merge  → combine using keysconcat → stack DataFrames

Date and time#

Python

df["created_at"] = pd.to_datetime(    df["created_at"])
df["date"] = (    df["created_at"].dt.date)
df["hour"] = (    df["created_at"].dt.hour)
df["day"] = (    df["created_at"].dt.day)

Time difference between two columns:

Python

df["duration"] = (    df["end_time"]    - df["start_time"])

Seconds since the previous row (sort first; first row is NaN):

Python

track = df.sort_values("timestamp")
dt_seconds = track["timestamp"].diff().dt.total_seconds()
# per entity:df["gap_seconds"] = (    df.sort_values(["track_id", "timestamp"])    .groupby("track_id")["timestamp"]    .diff()    .dt.total_seconds())

Boolean masks#

Python

mask = df["amount"] > 1000
anomalies = df[mask]normal = df[~mask]

Text

mask.sum()  → number of True rowsmask.mean() → fraction of True rows

Python

count = mask.sum()rate = mask.mean() * 100

Working with strings#

Common .str operations:

Python

df["name"].str.lower()df["name"].str.upper()df["name"].str.strip()
df["email"].str.contains(    "@example.com",    na=False,)
df["code"].str.startswith("ORD-")

Rename, reset index, copy, and query#

Rename columns:

Python

df = df.rename(    columns={        "old_name": "new_name",    })

Reset the index:

Python

df = df.reset_index(    drop=True)

GroupBy operations often move grouping keys into the index. reset_index() is commonly used when a regular column layout is needed again.

Copy a filtered subset before modifying it independently:

Python

anomalies = df[mask].copy()

.query() is an optional compact filter syntax:

Python

df.query(    "amount > 100 and quantity > 0")

Boolean masks remain the primary syntax because they compose naturally with Python variables and more complex conditions.

Useful checks#

Python

df.emptylen(df)df.shapedf.columnsdf.dtypes
df["status"].value_counts()
df["customer_id"].nunique()
df["amount"].min()df["amount"].max()df["amount"].mean()df["amount"].sum()

Use these checks before writing a result or investigating an unexpected batch. They reveal whether the data has rows, the expected shape, the right schema, the expected categories, and plausible numeric values.

Reading complex Pandas expressions#

Read a Pandas chain from left to right and keep track of the object shape. For example:

Python

totals = (    df.groupby("customer_id")["amount"]    .agg(["min", "max", "mean"]))

Break it down:

df — start with a DataFrame.
df.groupby("customer_id") — split rows into customer groups.
["amount"] — work only with the amount column inside each group.
.agg(["min", "max", "mean"]) — reduce each group to statistics.

The result is usually one row per customer, not the original number of rows.

For a lookup chain:

Python

df["customer_total"] = (    df["customer_id"]    .map(totals)    .fillna(0)    .astype(float))

Read it as:

Text

Series  → map lookup  → missing-value handling  → type conversion  → assignment

Break chains when confused#

Long method chains are convenient, but intermediate variables are often better while learning or debugging:

Python

mapped = df["id"].map(values)
filled = mapped.fillna(0)
converted = filled.astype(float)
df["value"] = converted

Inspect each intermediate value with head(), dtypes, or isna().sum(). This makes it clear which operation changed the values or the type.

Pandas ↔ SQL mental map#

These are conceptual equivalents, not always identical implementations:

Pandas	SQL
`df[columns]`	`SELECT columns`
`df[mask]`	`WHERE`
`sort_values()`	`ORDER BY`
`groupby()`	`GROUP BY`
`agg()`	Aggregate functions
`merge()`	`JOIN`
`drop_duplicates()`	`DISTINCT` or deduplication
`head(n)`	`LIMIT n`
`rename()`	`AS`
`isna()`	`IS NULL`
`isin()`	`IN`
`max()` / `min()` / `sum()` / `mean()`	`MAX` / `MIN` / `SUM` / `AVG`

The same mental model helps decide where a transformation should run: in SQL close to stored data, or in Pandas after the required rows have been loaded.

Common mistakes#

Forgetting parentheses in boolean masks.
Using and instead of &.
Using or instead of |.
Confusing a Series with a DataFrame.
Misunderstanding the shape of a groupby() result.
Using apply() when transform() or agg() is enough.
Forgetting to sort before first() or last() when order matters.
Modifying filtered data without .copy().
Allowing merge() to create unexpected duplicate rows.
Forgetting missing values after map().
Converting missing values directly to int.
Assuming the Pandas index is a database primary key.
Writing unreadable one-line chains.
Loading an unnecessarily large dataset into Pandas.

One-page cheat sheet#

Select column#

Python

df["column"]

Select columns#

Python

df[["a", "b"]]

Filter#

Python

df[df["amount"] > 100]

AND#

Python

df[    (df["a"] > 0)    & (df["b"] == "x")]

Group#

Python

df.groupby("key")

Aggregate#

Python

df.groupby("key")["value"].agg(    ["min", "max", "mean"])

Transform#

Python

df.groupby("key")["value"].transform(    "sum")

Map#

Python

df["key"].map(values)

Apply#

Python

def classify_group(group):    ...    return value

result = df.groupby("key").apply(classify_group)
df["result"] = df["key"].map(result)

Mask#

Python

mask = df["value"] > limit

Inverse mask#

Python

df[~mask]

Missing#

Python

df["value"].fillna(0)

Type#

Python

df["value"].astype(float)

Merge#

Python

left.merge(    right,    on="id",    how="left",)

Concat#

Python

pd.concat(    [df1, df2],    ignore_index=True,)

Datetime#

Python

pd.to_datetime(    df["created_at"])

Deduplicate#

Python

df.drop_duplicates(    subset=["id"])

Sort#

Python

df.sort_values(    "created_at")

Empty#

Python

df.empty

Row count#

Python

len(df)

DataFrame mental model#

Creating and inspecting DataFrames#

Selecting columns#

Filtering rows#

Selecting rows and columns with .loc#

Missing values#

Type conversion#

Sorting#

Duplicates#

Creating and modifying columns#

GroupBy#

Aggregation#

First and last row#

Transform#

Map#

Apply#

Critical comparison#

Merge and join#

Concat#

Date and time#

Boolean masks#

Working with strings#

Rename, reset index, copy, and query#

Useful checks#

Reading complex Pandas expressions#

Break chains when confused#

Pandas ↔ SQL mental map#

Common mistakes#

One-page cheat sheet#

Select column#

Select columns#

Filter#

AND#

Group#

Aggregate#

Transform#

Map#

Apply#

Mask#

Inverse mask#

Missing#

Type#

Merge#

Concat#

Datetime#

Deduplicate#

Sort#

Empty#

Row count#

See also#

Selecting rows and columns with `.loc`#