Skip to content
Bamboo Weekly

pandas filter

Select columns — or rows — by their labels, rather than by their values.

Have you ever called filter because the name sounded like exactly what you needed, and gotten back a data frame with all of your rows and none of your columns? You are not the first. The name is misleading, and I say that as someone who uses this method constantly: filter never looks at your data. It looks at the labels — your column names, or the values in your index — and keeps the ones that match a pattern you describe.

Once that clicks, filter earns its place, because real data sets love to hand you forty columns when you want the three whose names happen to start with the same word.

Official documentation: DataFrame.filter

Three ways to describe what you want

filter takes exactly one of three keyword arguments, plus an optional axis:

df.filter(items=['emissions_kg', 'land_use_kg'])   # these exact labels
df.filter(like='Bananas')                          # labels containing this substring
df.filter(regex=r'_kg$')                           # labels matching this regular expression
df.filter(like='cheese', axis='rows')              # match the index, not the columns

The three criteria are mutually exclusive. Pass two of them and Pandas says so, in as many words:

df.filter(like='Bananas', regex='^Bananas')
# TypeError: Keyword arguments `items`, `like`, or `regex` are mutually exclusive

Pass none of them and you get a matching complaint: TypeError: Must pass either items, like, or regex. There is no default pattern, which is the right decision on Pandas's part.

The axis argument accepts 'columns' — the default on a data frame — or 'rows', which you may also spell 'index' or 0. On a series there is only one axis, so filter goes straight to the index and you can leave axis alone.

A worked example, on real data

Bamboo Weekly #22 used the Economist's "banana index," which scores foods by how much carbon and land they consume compared with a banana. Every food is a row, and the columns measure that in several different ways:

import pandas as pd

url = ('https://github.com/TheEconomist/banana-index-data/'
       'releases/download/1.0/bananaindex.csv')

df = pd.read_csv(url, index_col='entity')

That is 160 foods and 16 columns. Three of those columns are the index scores, and all three begin with the word "Bananas." I would much rather describe them than type them out:

df.filter(like='Bananas').columns

Index(['Bananas index (kg)', 'Bananas index (1000 kcalories)',
       'Bananas index (100g protein)'], dtype='str')

filter returns a data frame, so it drops into a chain like any other method. Which foods score worst per kilogram?

(
    df
    .filter(like='Bananas')
    .sort_values('Bananas index (kg)', ascending=False)
    .head()
)

                Bananas index (kg)  Bananas index (1000 kcalories)  Bananas index (100g protein)
entity
Beef steak              148.563324                       77.752629                      8.312805
Beef mince              108.816189                       54.049393                      6.878002
Beef meatballs           81.052853                       35.782826                      4.612929
Beef burger              61.803856                       25.011498                      3.730481
Lamb chops               35.383303                       13.505518                      2.225951

Beef, unsurprisingly. Now suppose I only care about cheese. The food names are in the index, not in a column, so str.contains is not available to me — but the index is made of labels, which is precisely what filter understands. I switch axes and chain a second call:

(
    df
    .filter(like='Bananas')
    .filter(like='heese', axis='rows')
    .sort_values('Bananas index (kg)', ascending=False)
    .head()
)

                 Bananas index (kg)  Bananas index (1000 kcalories)  Bananas index (100g protein)
entity
Cottage cheese            28.944313                       33.508657                      2.892767
Parmesan cheese           27.499275                        7.272295                      1.092785
Cheddar cheese            23.758006                        6.172845                      1.129409
Blue cheese               23.021427                        6.980327                      1.819096
Goat’s cheese             22.112650                        7.102344                      1.625015

Note that I asked for 'heese', not 'cheese'. Matching with like is case-sensitive, and this data set contains both "Cheddar cheese" and "Cheesecake." Dropping the first letter catches both. If that feels like a hack, regex='[Cc]heese' says the same thing more honestly.

Four mistakes people make

The first one is the big one, and it is the reason this page exists.

filter selects by label, never by value. If you want the rows where a number is above some threshold, that is loc and a boolean series, not filter. The two look deceptively similar in a chain, and the failure is silent:

df.filter(like='cheese')          # 160 rows x 0 columns -- no error!
df.loc[pd.col('land_use_kg') > 30]  # what you probably meant

The first call searched your column names for "cheese," found nothing, and handed back an empty frame without complaint. There is a clean way to hold the distinction in your head, and it is the same rule that governs pd.col: pd.col describes a value, and filter describes a name. Anything about the contents of your data belongs to loc.

The axis default is columns on a data frame and the index on a series. This catches people going in both directions. Matching row labels on a data frame requires axis='rows' and is easy to forget; then you call the same method on a single column, pass axis='columns' out of habit, and get ValueError: No axis named columns for object type Series.

items silently ignores labels that do not exist. Ask for a column you spelled wrong and Pandas simply leaves it out, where the bracket syntax would have stopped you:

list(df.filter(items=['emissions_kg', 'nope']))   # ['emissions_kg']
df[['emissions_kg', 'nope']]                      # KeyError: "['nope'] not in index"

That silence is occasionally what you want — items is a safe way to request columns that may or may not be present. Just do not lean on it to catch typos.

items also expects a collection. Passing one bare string gets you TypeError: Index(...) must be called with a collection of some kind, because Pandas will not guess whether you meant one label or a list of characters.

Where it shows up in Bamboo Weekly

#22: Banana index is the source of the example above — the Economist's first release of the index, where we hunted for foods with a bigger footprint than a banana, and used like, regex, and axis='rows' in a single issue.

#140: Stack Overflow survey asked what proportion of developers hold a degree. After value_counts, the education levels are index labels on a series, so counting them is .filter(regex='Bachelor|Master|Professional').sum() — about 74 percent.

#167: Oil prices compared WTI and Brent crude. Joining the two FRED series with lsuffix and rsuffix produced a wide frame, and .filter(regex='^value_') kept just the two price columns without naming either one.

#70: Moon missions counted 21st-century missions carrying a cubesat, scraped from Wikipedia. The marker was a symbol at the start of the index label, so .filter(regex='^⚀', axis='rows') did the selecting.

Practice it

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

Go deeper

Two of those four issues lean on regular expressions, and regex is the argument I reach for most. If patterns are still a mystery, my free 14-part crash course is at RegexpCrashCourse.com.

More Pandas videos on Python and Pandas with Reuven Lerner.

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.

See it on real data

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

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