The pandas Mental Model for People Who Already Know SQL

Most pandas tutorials are feature tours. Here are forty methods, here is pivot_table, here is a chart. You get through one, you can copy the snippets, and a week later you’re still guessing at why df["amount"][0] = 5 sometimes changes the table and sometimes doesn’t. The problem isn’t the method count. It’s that nobody told you what the objects are, so the rules feel arbitrary.

If you already write Python and you’re comfortable in SQL, you have almost everything you need. You know what a table is, what a join is, what a GROUP BY does and what NULL does to it. What you’re missing is a handful of ideas that SQL doesn’t have. Everything below is from the pandas user guide, and I’ve linked the exact pages so you can check me.

The timing is good, because pandas 3.0.0 landed on January 21 and fixed the single most confusing thing about the library. Copy-on-Write is now the default and only behavior, and string columns finally get a dtype of their own. This is written for 3.0, with notes where 2.3 differs, because plenty of people will be on 2.3 for a while yet.

Two objects, and an index you didn’t ask for

There are really only two things. A Series is a one dimensional labeled array that holds one type of data, so think of it as a single column. A DataFrame is a two dimensional table made of Series that share a set of row labels, so think of it as a result set.

The part with no SQL equivalent is the index. Every Series and DataFrame carries labels for its rows, and pandas keeps those labels glued to the data through almost every operation. The docs put it as data alignment being intrinsic: the link between labels and data won’t break unless you break it yourself. Filter a DataFrame and you don’t get rows renumbered from zero like a fresh result set, you get the survivors carrying their original labels. Add two Series and pandas lines them up by label first, rather than adding position by position. Half of the “why is my result full of NaN” confusion is alignment doing what it promised on two objects whose labels don’t match.

Here’s the dataset for the rest of this, small but shaped like something real, with a couple of missing values because real data has those.

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006],
    "customer_id": [1, 2, 2, 3, 1, 4],
    "region": ["us", "us", "eu", "eu", None, "us"],
    "amount": [120.0, 45.5, 300.0, 99.9, 75.0, None],
})

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Ada", "Grace", "Linus"],
    "plan": ["pro", "free", "pro"],
})

Selecting, and the one rule that saves you

Plain square brackets on a DataFrame are a convenience. orders["amount"] gives you that column as a Series, and orders[["order_id", "amount"]] gives you a two column DataFrame, the pandas version of listing columns after SELECT.

The two you should actually reach for are .loc and .iloc, and the indexing guide draws the line clearly. .loc is label based, so orders.loc[0, "amount"] means the row labeled 0 and the column named amount. It raises KeyError on a missing label, and unlike normal Python slicing, a label slice includes both endpoints. .iloc is integer position based, so orders.iloc[0, 3] means the first row and the fourth column whatever they’re called, and it raises IndexError out of bounds. Both accept a boolean array too.

The rule: whenever you are assigning, use a single .loc or .iloc call that names both the rows and the columns. Never two sets of brackets in a row.

Why chained indexing used to bite

orders["amount"][0] = 5 is called chained indexing, and the trap was Python mechanics rather than pandas being difficult. Those are two separate operations. The first hands you some object, and the second writes into whatever that object turned out to be, which pandas had no way of knowing when it built it. Outside of simple cases it was very hard to predict whether the thing in the middle was a view onto the original data or a fresh copy. Assign into a view and the parent changed. Assign into a copy and your write vanished. That unpredictability is why SettingWithCopyWarning existed, and why it got called unhelpful: it fired on a guess about what you meant.

pandas 3.0 settles it. The release notes give the rule plainly: the result of any indexing operation, including pulling a column out as a Series, and of any method that returns a new DataFrame or Series, always behaves as a copy, so the only way to modify an object is to modify that object itself. Chained assignment therefore never works, and the Copy-on-Write guide says pandas raises a ChainedAssignmentError warning when you try. SettingWithCopyWarning is removed entirely, along with the need for the defensive .copy() calls people sprinkled around to silence it. Under the hood pandas still uses views where it can and copies only when it must, which is the actual copy-on-write part and the reason this costs you nothing.

The replacement is the single call you should have written anyway:

orders.loc[orders["amount"] > 100, "region"] = "us-east"

One consequence catches everybody. Updating a column you selected as a Series no longer propagates back to the parent frame, so df["amount"].fillna(0, inplace=True) does nothing useful and you want df["amount"] = df["amount"].fillna(0) instead. Relatedly, .to_numpy() can hand back a read only array, because a frame made of a single NumPy block shares memory with the array it gives you.

If you’re still on 2.3, you can get most of this early with pd.options.mode.copy_on_write = True, which is worth doing before you upgrade. On 3.0 that option has no effect at all and is deprecated for removal in 4.0, so take it back out once you’ve moved.

Filtering is a boolean mask

A WHERE clause in pandas is a Series of booleans used as an index. orders["amount"] > 100 produces a boolean Series, and orders[orders["amount"] > 100] keeps the rows where it’s true.

Two things trip people. The operators are & for and, | for or and ~ for not, because Python’s and and or want a single true or false value and a mask is a whole column of them. And every comparison needs parentheses, because & binds tighter than >. The guide gives the exact failure: df["A"] > 2 & df["B"] < 3 is parsed as df["A"] > (2 & df["B"]) < 3. So write orders[(orders["amount"] > 100) & (orders["region"] == "eu")] and make the parentheses a habit.

GROUP BY and JOIN, with the differences that matter

These map over cleanly, and pandas maintains a Comparison with SQL page that puts the query next to the Python for each one. SELECT region, count(*) FROM orders GROUP BY region is orders.groupby("region").size(), and avg(amount) is orders.groupby("region")["amount"].mean(). The grouping column becomes the index of the result, which is alignment again.

There is one genuine behavioral difference. SQL’s GROUP BY gives NULL its own group. pandas drops it: the groupby guide says any NA value in the grouping key, including NaN, NaT and None, excludes that row, and you get the NA group back with dropna=False. Order 1005 above has no region and vanishes from a plain groupby("region"), which is how a count quietly disagrees with the database.

Joins are merge, and it defaults to an inner join, so orders.merge(customers, on="customer_id") is an INNER JOIN on that key. how="left", how="right" and how="outer" give the outer variants. Columns that collide and aren’t part of the key get suffixed _x and _y, worth renaming before it confuses somebody.

Two arguments in the merging guide deserve to be habits rather than debugging tools. validate="one_to_many" or "one_to_one" checks key uniqueness before the merge runs and raises MergeError if your assumption is wrong, turning a silent row explosion into an exception. indicator=True adds a _merge column labeling each row left_only, right_only or both, the fastest way to see what didn’t match.

Missing data has more than one flavor

SQL has one NULL. pandas has several sentinels, and the missing data guide explains why. NumPy backed columns use numpy.nan, and the cost is coercion: a missing value in an integer column promotes the whole column to float, and in a boolean column promotes it to object. Datetime and timedelta columns use NaT. The nullable extension types, meaning Int64 with a capital I, boolean and the Arrow backed types, use pd.NA and keep the original dtype.

Detection is isna() and notna(), which line up with IS NULL and IS NOT NULL and cover None too. What you must not do is compare. np.nan == np.nan is False, pd.NaT == pd.NaT is False, and pd.NA == pd.NA is itself NA, so df[df["amount"] == np.nan] returns nothing. If you write SQL you already know WHERE x = NULL is wrong. Same instinct, different spelling.

The other big 3.0 change lives here. String data used to land in object dtype, a column that could hold any Python object at all and was slow and memory hungry with it. pandas now infers a dedicated str dtype by default, backed by PyArrow when it’s installed and by NumPy object storage when it isn’t. So the region column above is a str column, it refuses a non string value, and its missing sentinel is plain NaN. The release notes link a migration guide, worth reading first if your code checks for object dtype or tests for an exact sentinel.

Where to start

Start on 3.0 if you can, because the behavior you learn is the behavior that stays. Write every assignment as a single .loc call. Keep the Comparison with SQL page open for a week and translate the queries you’d normally write, because the translating is what builds the map. And when a result surprises you, ask what the index did before you ask what the method did.

pandas gets a lot smaller once you accept that it’s a labeled table with one superpower and one historical wart, and as of last week the wart is gone.