Overview
Filter, group, and join exist in both languages. Pandas runs in Python memory. SQL runs in the warehouse.
On this page7 sections
The decision
Pandas and SQL both work on tables. You filter rows, pick columns, group, and join in either language. The verbs match. The place the table lives does not.
Pandas holds the table in your Python process, in memory, on one machine. You write Python that touches a DataFrame. SQL holds the table in a warehouse. You send a query to that warehouse and it sends rows back.
Neither tool replaces the other. Analysts and data engineers use both. The skill is knowing which side of the wall the data is on, and which language fits that side.
What is at stake
An online store keeps years of orders in a warehouse. Finance queries that warehouse with SQL because the data is large, shared, and already loaded. The same week, an engineer receives a vendor CSV that is not in the warehouse yet. That file is a pandas job: load, filter, check, then write a clean table into the warehouse.
Picking the wrong tool wastes time. Running SQL against a laptop CSV does not work if there is no database. Loading a billion-row fact table into pandas will crash the process. Learn the mapping now so later lessons (groupby, merge) feel like SQL you already know, written in Python.
Option A vs Option B
Files and notebooks sit on the pandas side. Shared warehouse tables sit on the SQL side.
A boolean mask is a column of True/False the same length as the DataFrame. df[mask] keeps the True rows. That is WHERE, written as Python.
Same three jobs. SQL sends the work to the warehouse. pandas does the work in Python memory.
| Job | SQL | pandas |
|---|---|---|
| Filter rows | WHERE order_status = 'paid' | df[df['order_status'] == 'paid'] |
| Group and sum | GROUP BY order_status | df.groupby('order_status') |
| Combine tables | JOIN ... ON order_id | df.merge(other, on='order_id') |
Start with a small orders extract and a paid filter. The input table has mixed statuses. The output table should keep only paid rows and the columns you asked for.
Three rows, mixed statuses. The paid filter should keep O-104 and drop the pending row.
A worked comparison
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
mask = df_orders["order_status"] == "paid"
result = df_orders.loc[mask, ["order_id", "order_status", "order_total"]].head(10)
print(result.shape)mask is True on paid rows and False on the rest. .loc[mask, columns] keeps those rows and the named columns. .head(10) caps the preview. The warehouse version of the same idea is SELECT order_id, order_status, order_total FROM orders WHERE order_status = 'paid' LIMIT 10.
Pending rows are gone. Column set matches the SELECT list.
by_status = (
df_orders.groupby("order_status", as_index=False)
.agg(orders=("order_id", "count"), gmv=("order_total", "sum"))
)
result = by_statusgroupby splits the table by order_status. agg counts orders and sums totals. You will practice grouping in a later module. The point here is the mapping: GROUP BY and JOIN have pandas names, and you already know the jobs from SQL.
Rule of thumb: pandas for laptop-sized files and Python cleanup. SQL for warehouse-sized, shared tables.
| Situation | Use pandas | Use SQL |
|---|---|---|
| A file on disk, not yet in a warehouse | Yes | Only after you load it |
| A notebook exploring a sample | Yes | If a warehouse table already exists |
| Years of orders shared by the company | No (too big) | Yes |
| A join that must match warehouse numbers | After a small check | Yes, on the governed tables |
Pandas is not a warehouse
df_orders lives in this editor's memory. There is no database connection here. Do not call read_sql. Filter the preloaded frame. Production SQL runs against a warehouse you do not have in this tab.
SQL connection
WHERE becomes a mask: df[df['order_status'] == 'paid']. GROUP BY becomes groupby. JOIN becomes merge. Learn the mapping once. Every later pandas lesson reuses it.
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
If I know SQL, why learn pandas?
Because a lot of data never starts in a warehouse. Vendor files, API dumps, and notebook checks are Python work. Pandas is how you do table algebra in that world.
Can pandas replace SQL?
Not for shared, large, governed data. Warehouses exist so many people can query the same tables without each person loading a copy into RAM. Use SQL there.
Is df[mask] the same as WHERE?
For a simple equality filter, yes: both keep matching rows. SQL can push that filter into storage and indexes. Pandas scans the column it already holds in memory.
What comes next
The next lesson covers how tables get into pandas in the first place: read_csv, read_excel, and read_json. In this tab the frames are already loaded, so you will inspect df_orders instead of opening a path.
Practice
Run Sample to see a paid filter. Then complete Exercise: keep paid rows from df_orders, project order_id, order_status, and order_total, take at most 10 rows, and assign that DataFrame to result.
Your code should mention paid. The grid should show those three columns.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.