Skip to content
Bamboo Weekly

pd.col

Refer to a column before the data frame has a name — the piece that makes method chaining work in Pandas 3.

pd.col('mag') does not fetch anything. It is a deferred reference: a description of "the column called mag", which Pandas resolves later, against whatever data frame it is eventually handed. That indirection is what lets you write a filter inside a chain, where the intermediate frame exists but has no variable name to point at.

Official documentation: pandas.col

The problem it solves

Outside a chain, filtering is easy, because there is a df to refer to twice:

df[df['mag'] > 7]

Inside a chain there is no such name. The frame produced by read_csv and then modified by assign has never been assigned to anything:

(
    pd.read_csv(url)
    .assign(shallow=...)
    .loc[???]          # what do we call the frame at this point?
)

pd.col is the answer to ???. You describe the column, and Pandas supplies the frame:

(
    pd.read_csv(url)
    .assign(shallow=pd.col('depth') < 70)
    .loc[pd.col('mag') > 7]
)

Where pd.col works

Anywhere Pandas is expecting a value:

# Comparisons and arithmetic
df.loc[pd.col('mag') > 7]
df.assign(total=pd.col('a') + pd.col('b'))

# One column compared against another
df.loc[pd.col('b') > pd.col('a') * 5]

# Combined conditions -- parenthesize each side
df.loc[(pd.col('mag') > 7) & (pd.col('depth') < 50)]
df.loc[~(pd.col('mag') > 7)]

# Accessors
df.assign(year=pd.col('time').dt.year)
df.assign(shouty=pd.col('place').str.upper())

# The usual series methods
df.loc[pd.col('region').isin(['Asia', 'Europe'])]
df.loc[pd.col('mag').between(6, 7)]
df.assign(capped=pd.col('depth').clip(upper=100))
df.assign(filled=pd.col('mag').fillna(0))
df.assign(as_float=pd.col('mag').astype('float'))

It also works in .loc assignment, which is the correct cure for chained indexing:

df.loc[pd.col('mag') > 7, 'notable'] = True

And one genuinely handy trick: inside a single assign, pd.col can refer to a column created earlier in that same call, because assign evaluates its arguments in order:

df.assign(doubled=pd.col('a') * 2,
          plus_one=pd.col('doubled') + 1)

Where it does not work — and the rule

pd.col describes a value, not a name. Wherever Pandas is asking which column, it wants a plain string, and passing pd.col fails:

df.groupby(pd.col('region'))     # TypeError: boolean value of an expression is ambiguous
df.sort_values(pd.col('mag'))    # KeyError: col('mag')
df.drop(columns=pd.col('mag'))   # TypeError: unhashable type: 'Expression'

All three want names:

df.groupby('region')
df.sort_values('mag')
df.drop(columns='mag')

That groupby error is worth reading twice, because it is unhelpful and you will meet it. "Boolean value of an expression is ambiguous" means Pandas tried to evaluate your deferred expression as a true/false condition and could not. It does not mean your grouping is wrong; it means you passed an expression where a column name belongs.

So: .loc and .assign take pd.col. groupby, sort_values and drop take strings. Once you have that split, the rest is intuition.

A worked example, on real data

The USGS publishes every earthquake it records as CSV, through a public API with no key — the same source Bamboo Weekly #3 worked with. Let's find 2024's largest shallow quakes — shallow ones do far more damage for a given magnitude.

import pandas as pd

url = ('https://earthquake.usgs.gov/fdsnws/event/1/query.csv'
       '?starttime=2024-01-01&endtime=2024-12-31&minmagnitude=6')

(
    pd.read_csv(url, usecols=['time', 'place', 'mag', 'depth'], parse_dates=['time'])
    .assign(month=pd.col('time').dt.strftime('%B'),
            shallow=pd.col('depth') < 70,
            region=pd.col('place').str.split(', ').str[-1])
    .loc[pd.col('mag') >= 7]
    .loc[pd.col('shallow')]
    .sort_values('mag', ascending=False)
    [['month', 'region', 'mag', 'depth']]
    .head(5)
)

Which gives:

   month           region  mag  depth
 January Japan Earthquake  7.5 10.000
   April           Taiwan  7.4 40.000
December          Vanuatu  7.3 54.372
    June             Peru  7.2 24.000
  August Japan Earthquake  7.1 24.000

Four pd.col references, three of them creating columns that did not exist when the chain started — and sort_values taking a plain string, because it wants a name. That mixture is normal, and the rule above tells you which is which. (The scruffy Japan Earthquake region is the USGS data, not a bug: splitting a free-text place field is exactly the kind of real-world mess that toy datasets never teach you to handle.)

The lambda form it replaces

Before Pandas 3, the same deferral was expressed with a lambda, which .loc and .assign still accept:

df.loc[lambda df_: df_['mag'] > 7]

The df_ name was a convention meaning "the frame at this point in the chain". You will see this throughout the older Bamboo Weekly archive, and it continues to work — pd.col simply says the same thing with less ceremony.

The lambda still earns its place in one situation: when you need to operate on the frame as a whole, across every column, rather than on columns you can name. pd.col('a') + pd.col('b') is fine when there are two of them, but summing forty columns you would rather not enumerate still wants a lambda:

df.loc[lambda df_: df_.sum(axis=1) > 100]

For the lambda era in its own right, see Method chaining in Pandas: Cleaner queries with assign, loc, and lambda and Optimizing Pandas queries with method chaining.

Watch it instead

If you would rather see this in action: Cleaner Pandas Queries with pd.col — New in Pandas 3.

It is part of Getting ready for Pandas 3, a series of short videos on what breaks, what changes, and how to prepare for the upgrade.

Practice it

Work through a pd.col exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/pd-col/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

Where you will use it

pd.col shows up wherever you filter or derive columns inside a chain. See it in context on Pandas loc and Pandas assign.

See it on real data

Below are the 28 Bamboo Weekly exercises that use pd.col on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.