Pandas for SQL Users: A Practical Translation Guide

Pandas often feels harder than SQL to experienced analysts, even when both tools manipulate tabular data. The difficulty is not the data. It is the change in mental model.

SQL describes the result you want and lets the database choose an execution plan. Pandas is a Python library: you create DataFrame objects, call methods on them, and decide which intermediate results to keep. Once you can translate familiar SQL operations into those transformations—and validate the same grain and join assumptions—Pandas becomes much less mysterious.

This guide uses one small sales dataset to translate the SQL patterns analysts use most: selecting, filtering, grouping, joining, handling nulls, ranking, reshaping, and validating results.

Set Up One Realistic Dataset

from math import isclose

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "segment": ["SMB", "Enterprise", "SMB", "Mid-Market"],
    "region": ["West", "East", None, "Central"],
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 1, 2, 2],
    "ordered_at": [
        "2026-07-03 09:15:00Z",
        "2026-07-18 14:20:00Z",
        "2026-07-11 11:00:00Z",
        "2026-08-02 16:45:00Z",
    ],
    "status": ["paid", "refunded", "paid", "paid"],
    "revenue": [120.0, 80.0, 250.0, 175.0],
})

orders["ordered_at"] = pd.to_datetime(orders["ordered_at"], utc=True)

The important habit appears in the last line: assign types deliberately at the boundary. The Z suffix says these sample timestamps are UTC, and utc=True keeps them time-zone-aware. For local timestamps, localize them to their real source zone instead of merely labeling them as UTC. CSV files frequently load identifiers, dates, booleans, and currency as strings. Check before analyzing:

print(orders.dtypes)
print(orders.head())
print(orders.shape)

shape returns (rows, columns). Use it as the Pandas equivalent of a quick row-count check after every major transformation.

What the Pandas index is

A DataFrame index is a row label, not automatically a database primary key. It may contain duplicates, and it may have no business meaning at all. Pandas also aligns Series assignment and arithmetic by index label rather than only by physical row position. That alignment is powerful, but it can create missing or misplaced values when two objects use different indexes. Keep business keys in named columns unless an index clearly improves the operation, and inspect df.index.is_unique before relying on index uniqueness.

SELECT Becomes Column Selection and assign

This SQL:

SELECT order_id, customer_id, revenue * 0.9 AS net_revenue
FROM orders;

translates to:

result = (
    orders
    .assign(net_revenue=lambda df: df["revenue"] * 0.9)
    .loc[:, ["order_id", "customer_id", "net_revenue"]]
)

.loc[rows, columns] is explicit. A colon in the row position means all rows. For a simple column subset, orders[["order_id", "revenue"]] is also fine.

Method chains read like a sequence of transformations. Parentheses let you put one operation per line without backslashes, and naming the final object makes the stage available for inspection.

WHERE Becomes a Boolean Mask

This SQL:

SELECT *
FROM orders
WHERE status = 'paid' AND revenue >= 150;

becomes:

paid_large_orders = orders.loc[
    orders["status"].eq("paid") & orders["revenue"].ge(150)
]

Use & for element-by-element AND, | for OR, and ~ for NOT. Put each condition in parentheses when you use comparison operators directly:

paid_large_orders = orders.loc[
    (orders["status"] == "paid") & (orders["revenue"] >= 150)
]

Python's and and or evaluate a single truth value; a Pandas Series contains one truth value per row. Mixing those models causes the familiar “truth value of a Series is ambiguous” error.

CASE WHEN Becomes mask, where, or map

This SQL creates a binary revenue band:

SELECT
    order_id,
    CASE
        WHEN revenue >= 200 THEN 'high'
        ELSE 'standard'
    END AS revenue_band
FROM orders;

For a binary Pandas rule, use .where(), .mask(), or a boolean Series with .map():

orders = orders.assign(
    revenue_band=lambda df: df["revenue"].ge(200).map({
        True: "high",
        False: "standard",
    })
)

As an alternative with several numeric bands, pd.cut() is usually clearer than nested conditionals. Use a different output name because this is a different classification:

orders["revenue_size"] = pd.cut(
    orders["revenue"],
    bins=[0, 100, 200, float("inf")],
    labels=["small", "medium", "large"],
    right=False,
)

GROUP BY Becomes groupby and agg

This SQL:

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(revenue) AS total_revenue,
    AVG(revenue) AS avg_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

becomes:

customer_summary = (
    orders.loc[orders["status"].eq("paid")]
    .groupby("customer_id", as_index=False)
    .agg(
        order_count=("order_id", "size"),
        total_revenue=("revenue", "sum"),
        avg_revenue=("revenue", "mean"),
    )
)

Named aggregations keep the output column names beside their source columns. as_index=False returns a normal DataFrame with customer_id as a column, which usually feels more natural to SQL users.

Know the null distinction: size counts rows like COUNT(*), while count excludes null values like COUNT(column). SQL normally keeps a NULL grouping key, but Pandas drops missing group keys by default; use groupby(..., dropna=False) when that group belongs in the result. Another difference is that SQL SUM returns NULL for an all-null group, while Pandas returns zero by default. Use sum(min_count=1) when you need SQL-like behavior.

Window Functions Often Become transform or rank

An aggregate collapses each group. A window calculation preserves the original rows. In Pandas, transform returns a result aligned to those rows.

This SQL:

SELECT
    order_id,
    customer_id,
    revenue,
    SUM(revenue) OVER (PARTITION BY customer_id) AS customer_revenue
FROM orders;

becomes:

orders = orders.assign(
    customer_revenue=lambda df: (
        df.groupby("customer_id")["revenue"].transform("sum")
    )
)

For row numbers, sort first and use cumcount():

ranked = orders.sort_values(
    ["customer_id", "ordered_at", "order_id"],
    ascending=[True, False, False],
).copy()

ranked["row_number"] = ranked.groupby("customer_id").cumcount() + 1
latest_order = ranked.loc[ranked["row_number"].eq(1)]

The extra order_id sort is a deterministic tie-breaker. Without it, equal timestamps may produce an arbitrary winner.

JOIN Becomes merge—and Validation Should Be Explicit

This SQL:

SELECT o.*, c.segment, c.region
FROM orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id;

becomes:

orders_with_customer = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)

The validate argument is one of the best reasons to be explicit with Pandas joins. It asserts that many order rows may match one customer row. If customers.customer_id contains duplicates, Pandas raises an error instead of silently multiplying revenue. This is the same cardinality problem explored in the guide to Pandas merges with repeated keys.

One important SQL difference: Pandas can match a missing join key on one side with a missing key on the other, while SQL NULL = NULL is not true. Inspect or reject missing keys before a merge whenever that difference could create false matches.

The temporary _merge column from indicator=True reveals unmatched keys:

print(orders_with_customer["_merge"].value_counts())

orders_with_customer = orders_with_customer.drop(columns="_merge")

Also compare row counts. A many-to-one left join should preserve the number of rows on the left:

assert len(orders_with_customer) == len(orders)

NULL Becomes pd.NA, NaN, None, or NaT

Pandas represents missing values differently depending on dtype. Use isna() and notna() rather than comparing with None or float("nan"):

missing_region = customers.loc[customers["region"].isna()]

customers = customers.assign(
    region=lambda df: df["region"].fillna("Unknown")
)

Do not fill missing values automatically. “Unknown,” zero, and an empty string have different meanings. Decide whether the field is absent, not applicable, not yet observed, or genuinely zero.

ORDER BY and LIMIT Become sort_values and head

top_orders = (
    orders
    .sort_values(["revenue", "order_id"], ascending=[False, True])
    .head(3)
)

Pandas preserves the DataFrame’s current row order, so head() deterministically returns its first rows. For a top-N result, sort by the business metric first and add a stable tie-breaker when equal values are possible.

CTEs Become Named Intermediate DataFrames

SQL users sometimes force an entire analysis into one method chain. You do not have to. Named DataFrames are the Pandas equivalent of readable CTEs:

paid_orders = orders.loc[orders["status"].eq("paid")].copy()

customer_revenue = (
    paid_orders
    .groupby("customer_id", as_index=False)
    .agg(total_revenue=("revenue", "sum"))
)

report = (
    customers
    .merge(
        customer_revenue,
        on="customer_id",
        how="left",
        validate="one_to_one",
    )
    .assign(total_revenue=lambda df: df["total_revenue"].fillna(0))
)

After each stage, inspect the grain, row count, key uniqueness, and a few records. Readability is more valuable than a chain that is difficult to debug.

Pivoting Rows Into Columns

First create a month, then build a table with one row per customer and one column per month:

monthly = orders.assign(
    order_month=lambda df: (
        df["ordered_at"]
        .dt.tz_convert("UTC")
        .dt.tz_localize(None)
        .dt.to_period("M")
    )
)

revenue_pivot = monthly.pivot_table(
    index="customer_id",
    columns="order_month",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
)

A period does not retain time-zone information, so the example first converts timestamps to the reporting zone—UTC here—and only then removes the zone. Use the business reporting zone instead when month boundaries are local. pivot() requires every index-and-column combination to be unique. pivot_table() accepts repeated combinations because you provide an aggregation rule. If duplicates surprise you, investigate them before choosing an aggregation.

A Complete SQL-to-Pandas Analysis

Suppose the business asks: for each customer segment, how many customers placed a paid order in July 2026, and what was their average customer-level revenue?

The phrase “average customer-level revenue” determines the safe sequence: filter orders, calculate one row per customer, join the customer segment, then aggregate by segment.

Here is a PostgreSQL version first; use your engine’s equivalent timestamp-boundary and null-safe comparison syntax where it differs:

WITH july_paid AS (
    SELECT customer_id, revenue
    FROM orders
    WHERE status = 'paid'
      AND ordered_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
      AND ordered_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00'
),
revenue_per_customer AS (
    SELECT
        customer_id,
        SUM(revenue) AS customer_revenue
    FROM july_paid
    GROUP BY customer_id
),
segments AS (
    SELECT DISTINCT segment
    FROM customers
),
active_segment_metrics AS (
    SELECT
        c.segment,
        COUNT(*) AS active_customers,
        AVG(r.customer_revenue) AS avg_customer_revenue
    FROM revenue_per_customer AS r
    JOIN customers AS c
      ON c.customer_id = r.customer_id
    GROUP BY c.segment
)
SELECT
    s.segment,
    COALESCE(a.active_customers, 0) AS active_customers,
    a.avg_customer_revenue
FROM segments AS s
LEFT JOIN active_segment_metrics AS a
  ON a.segment IS NOT DISTINCT FROM s.segment
ORDER BY
    a.avg_customer_revenue DESC NULLS LAST,
    s.segment ASC;

The Pandas version preserves the same grains and stages:

july_start = pd.Timestamp("2026-07-01", tz="UTC")
august_start = pd.Timestamp("2026-08-01", tz="UTC")

july_paid = orders.loc[
    orders["status"].eq("paid")
    & orders["ordered_at"].ge(july_start)
    & orders["ordered_at"].lt(august_start)
].copy()

revenue_per_customer = (
    july_paid
    .groupby("customer_id", as_index=False)
    .agg(customer_revenue=("revenue", "sum"))
)

customer_metrics = revenue_per_customer.merge(
    customers[["customer_id", "segment"]],
    on="customer_id",
    how="left",
    validate="one_to_one",
)

active_segment_metrics = (
    customer_metrics
    .groupby("segment", as_index=False, dropna=False)
    .agg(
        active_customers=("customer_id", "nunique"),
        avg_customer_revenue=("customer_revenue", "mean"),
    )
)

segment_report = (
    customers[["segment"]]
    .drop_duplicates()
    .merge(
        active_segment_metrics,
        on="segment",
        how="left",
        validate="one_to_one",
    )
    .assign(
        active_customers=lambda df: (
            df["active_customers"].fillna(0).astype("int64")
        )
    )
    .sort_values(
        ["avg_customer_revenue", "segment"],
        ascending=[False, True],
        na_position="last",
    )
)

Validate the result with invariants:

assert revenue_per_customer["customer_id"].is_unique
assert customer_metrics["customer_id"].is_unique
assert segment_report["segment"].is_unique
assert len(segment_report) == customers["segment"].nunique(dropna=False)
assert segment_report["active_customers"].sum() == len(customer_metrics)
assert isclose(
    july_paid["revenue"].sum(),
    revenue_per_customer["customer_revenue"].sum(),
    rel_tol=1e-9,
    abs_tol=1e-9,
)

Starting the final report from the distinct customer segments preserves Mid-Market with zero active customers; its average is missing because there are no active customers to average. The result is:

segment      active_customers  avg_customer_revenue
Enterprise  1                 250.0
SMB         1                 120.0
Mid-Market  0                   NaN

Those checks protect the same concepts you would validate in SQL: grain, join cardinality, population counts, and conserved totals. Python can remove assert statements when run with optimization, so production pipelines should enforce critical invariants with explicit exceptions or automated tests.

Common Mistakes SQL Users Make in Pandas

• Chained assignment: code such as df[mask]["column"] = value does not reliably update the original DataFrame and, under Copy-on-Write, never does. Assign in one step with df.loc[mask, "column"] = value; use .copy() when you intentionally want an independent filtered DataFrame.
• Ignoring dtypes: string dates and numeric-looking text produce wrong comparisons or aggregations. Parse at ingestion, and keep time zones explicit. The Pandas datetime and time-zone guide covers localization and conversion in depth.
• Merging without validation: repeated keys can multiply rows exactly as they do in SQL. Use validate, indicator, and row-count assertions.
• Using apply for everything: built-in vectorized methods are usually clearer and faster. Reach for apply only when a direct Series or DataFrame operation does not express the rule.
• Losing the grain: every DataFrame has a grain even though Python does not declare it. Name intermediate objects after what one row represents.
• Building one giant chain: break transformations into testable stages when the business logic changes grain.

A Practical Learning Order

If Pandas feels overwhelming, do not memorize the entire API. Translate one familiar SQL operation at a time on the same dataset:

1. load data and inspect types, rows, and columns,
2. select columns and filter rows,
3. create calculated columns,
4. group and aggregate,
5. join with cardinality validation,
6. use transform, sorting, and ranking,
7. reshape only after the long-form result is correct,
8. add assertions for grain and totals.

Rebuild one analysis you already trust in SQL. Compare intermediate row counts and final totals between the database result and Pandas. That gives you immediate feedback without inventing a new business problem at the same time.

FAQ

Should I use SQL or Pandas for data analysis?

Push filtering, joining, and aggregation into the database when the data is large and the logic is naturally relational. Use Pandas for local files, exploratory work, specialized Python libraries, and transformations that are easier to express in code. Many reliable workflows use SQL to produce a well-defined dataset and Pandas for the final analysis.

What is the Pandas equivalent of a SQL window function?

There is no single equivalent. Use groupby().transform() for group-level values repeated on every row, cumcount() for row numbers, rank() for ranking, shift() for lag or lead, and rolling or expanding methods for windowed calculations.

Why did my Pandas merge create duplicate rows?

The join keys are repeated on one or both sides. State the intended relationship with validate="one_to_one", "one_to_many", or "many_to_one". Then inspect duplicate keys instead of dropping rows after the merge.

Do I need to memorize Pandas syntax for interviews?

Memorize a small working vocabulary: selection, boolean filtering, groupby, agg, transform, merge, sorting, null checks, and datetime accessors. More importantly, practice explaining grain, join cardinality, edge cases, and validation while you use them.

Pandas becomes manageable when you stop treating it as a bag of unrelated methods. It is a sequence of table transformations. Keep the grain explicit, validate joins and totals, and translate the SQL patterns you already know one operation at a time.