Skip to content
Bamboo Weekly

pandas xs

Take a cross-section at one level of a MultiIndex.

What do you do when the label you want to select by is buried in the middle of the index? A MultiIndex gives a data frame more than one level of row labels — region and country, state and year, indicator and units — and .loc[] is happiest when you start from the outermost one. Ask for every country in Europe and .loc[] is fine. Ask for Poland regardless of which region it sits in, and .loc[] starts requiring slice(None) and tuples that nobody enjoys reading a month later.

xs is the method for that. You name a label, you name the level it lives on, and Pandas hands back everything that matches, with that level removed from the result. It works on rows, it works on columns, and it works on a series as well as a data frame. Read it as "cross-section," because that is exactly what it is: one slice taken through a multi-level index.

Official documentation: DataFrame.xs

The arguments that earn their keep

df.xs('Europe',            # the label you want
      level='Region',      # which index level it lives on; name or position
      axis='index',        # 'index' (default) or 'columns'
      drop_level=True)     # remove that level from the result, or keep it

That is the whole method. level accepts a name or an integer position, so level=1 and level='Coal type' mean the same thing on a two-level index. axis='columns' looks at the column MultiIndex instead of the row one, which is how you slice a wide table that has, say, a year on the outer level and a measurement on the inner. And drop_level=False keeps the level you selected on, which matters when you are about to concatenate several cross-sections back together and need them to line up.

A worked example, on real data

Here is the Global Coal Plant Tracker, the same workbook behind Bamboo Weekly #64, pivoted into a data frame indexed by region and country:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

df = pd.read_excel(url, sheet_name='Units',
                   usecols=['Country', 'Region', 'Status',
                            'Coal type', 'Capacity (MW)'])

capacity = (
    df
    .loc[lambda df_: df_['Status'].isin(['operating', 'construction', 'retired'])]
    .pivot_table(index=['Region', 'Country'], columns='Status',
                 values='Capacity (MW)', aggfunc='sum')
)

capacity.head(4)

Status             construction  operating  retired
Region Country
Africa Botswana             NaN      732.0      NaN
       Madagascar           NaN      120.0      NaN
       Mauritius            NaN      195.0      NaN
       Morocco              NaN     4257.0      NaN

Two index levels, region outside and country inside. Selecting on the outer level is the easy case:

capacity.xs('Europe', level='Region').nlargest(5, 'operating')

Status          construction  operating  retired
Country
Germany                  NaN    40361.5  25180.8
Russia                 285.0    37857.1   9946.0
Poland                 100.0    28509.6   7703.0
Ukraine                  NaN    11138.0   2741.0
Czech Republic           NaN     7444.6   2996.5

Notice that Region is gone from the result. Every row in it was Europe, so keeping the level would have added nothing, and dropping it means the next method in the chain sees an ordinary single-level index.

Now the case that xs exists for — selecting on the inner level, without knowing or caring what is above it:

capacity.xs('Poland', level='Country')

Status  construction  operating  retired
Region
Europe         100.0    28509.6   7703.0

And if you want the region label to survive, say so:

capacity.xs('Poland', level='Country', drop_level=False)

Status          construction  operating  retired
Region Country
Europe Poland          100.0    28509.6   7703.0

The same idea works sideways. Pivot with two column keys and the columns become a MultiIndex, at which point axis='columns' takes a vertical slice through it:

(
    df
    .loc[lambda df_: df_['Status'].isin(['operating', 'construction'])]
    .pivot_table(index='Region', columns=['Status', 'Coal type'],
                 values='Capacity (MW)', aggfunc='sum')
    .xs('operating', level='Status', axis='columns')
)

Coal type  anthracite  bituminous  lignite  subbituminous   unknown  waste coal
Region
Africa            NaN     48014.8      NaN            NaN    3137.8         NaN
Americas          NaN    112599.9  13299.9        85947.9    7864.7      1731.6
Asia         118854.0    718556.2  90151.7        53915.0  625453.5     60535.0
Europe         5184.0     72338.9  72961.6         8616.1    8052.9         NaN
Oceania           NaN     10685.0   4721.0         7315.0     182.0         NaN

Twelve columns became six, and the Status level dropped away, leaving a plain table of coal type by region that you can plot directly.

Where it shows up in Bamboo Weekly

Bamboo Weekly #62: Economic report card is the densest use of xs I have written. The IMF's World Economic Outlook database indexes every row by country and by subject descriptor, so getting at inflation means .xs('Inflation, average consumer prices', level='Subject Descriptor'), over and over, as the opening move in almost every answer.

Bamboo Weekly #42: Plant hardiness compares the USDA's new plant-hardiness map with the 2012 one. The data frame ends up with year on the outer column level and measurement on the inner, so df.xs('trange_min', level=1, axis='columns') pulls the minimum temperature for both years side by side, ready for .diff(axis='columns').

Bamboo Weekly #59: Long covid works with the CDC's pulse survey, where the subgroup level holds both individual states and the national total. .xs('United States', level='Subgroup') is how you get the country-wide numbers without accidentally summing the states.

Bamboo Weekly #122: Economic growth reads the World Bank's growth forecasts into a three-level index, then takes .xs('Advanced economies', level='major_group') to compare rich countries against the emerging ones. With three levels, one of them named in the middle, this is the shape where .loc[] genuinely does get unpleasant.

Four mistakes people make

The result is a copy, so writing to it does nothing. capacity.xs('Europe', level='Region')['operating'] = 0 looks like it edits the original, and under Pandas 3's copy-on-write rules it silently edits nothing at all. You get a ChainedAssignmentError warning, not an exception, and the original data frame is untouched. Confusingly, the returned object may well share memory with the original, which is why people describe xs as returning a view — but it is a read-only view for all practical purposes. If you mean to assign, use a single .loc[] with both a row and a column indexer.

Reaching for xs when .loc[] says it better. Selecting on the outermost level needs no special method: capacity.loc['Europe'] and capacity.xs('Europe', level='Region') return exactly the same thing, and the first is shorter. Similarly, one specific row is capacity.loc[('Europe', 'Poland')]. Save xs for the inner levels, where .loc[] needs slice(None) to say "any value here."

Forgetting axis='columns'. xs defaults to the row index, so if your MultiIndex is on the columns you get TypeError: Index must be a MultiIndex — which is technically accurate and completely unhelpful, since your data frame does have a MultiIndex, just not where Pandas was looking.

Passing a list of labels. xs takes one label per level, not several: capacity.xs(['Europe', 'Asia'], level='Region') raises TypeError: list keys are not supported in xs, pass a tuple instead. A tuple means "one label on each of these levels," not "any of these labels." For several labels on one level, you want .loc[] or a boolean mask on index.get_level_values('Region').

Practice it

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

Go deeper

xs is one of a family of methods for living with a MultiIndex. set_index and reset_index create and remove index levels, pivot_table and unstack are the usual reasons you have several levels in the first place, and loc remains the general tool that xs specializes. The Pandas user guide's chapter on advanced indexing covers the whole picture, including the slice(None) idioms that xs lets you avoid.

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 27 Bamboo Weekly exercises that use xs on real-world data — try each one, then study the worked solution.

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