Skip to content
Bamboo Weekly

pandas reset_index

Move data between the index and the columns — and replace the labels outright.

Have you ever run a groupby, gotten back exactly the numbers you wanted, and then found that you could not merge them, could not chart them, and could not write them out, because the things you grouped by were not columns any more?

That is the single most common reason people reach for reset_index, and it is worth leading with. groupby does not throw the grouping keys away. It promotes them into the index, which is why they print at the left of the result, in bold in a notebook, without a column name above them. reset_index demotes them back into ordinary columns and puts a plain RangeIndex — 0, 1, 2, 3 — in their place.

Once you see the index as a place where data can live, its partner falls out of the same idea. set_index moves in the opposite direction, promoting one or more columns up into the index. Two methods, one question: where do the labels live now, and where should they live instead?

Official documentation: DataFrame.reset_index, and DataFrame.set_index

The arguments that earn their keep

df.reset_index(level=None,     # only these levels; default is all of them
               drop=False,     # True throws the old labels away
               names=None)     # names for the new columns

df.set_index(keys,             # a column name, or a list of them
             drop=True,        # False keeps the column as a column too
             append=False)     # True adds a level instead of replacing

drop= is the argument you will use most, and the one worth pausing over. reset_index() on its own preserves the old labels by turning them into a column; drop=True discards them. Neither is the safe default, which is the whole problem — you have to know which you meant.

level= resets only part of a MultiIndex and leaves the rest alone. names= renames the resulting columns in the same call, which matters more than it sounds like it should, because Pandas invents names like index and level_1 when the index levels are unnamed. One wrinkle: on a series the argument is singular, name=, and it names the column holding the values. Ask a series for names= and you get TypeError: Series.reset_index() got an unexpected keyword argument 'names'. Did you mean 'name'? — one of the friendlier error messages in Pandas.

set_axis has an axis argument with the same trap, and its own page.

A worked example, on real data

The Global Coal Plant Tracker lists every coal-fired generating unit on earth, one per row, with its country, region, capacity, status, and start year. It is the workbook behind Bamboo Weekly #64.

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', 'Start year',
                            'Capacity (MW)'])

Start with the archetype. Group operating capacity by region and country, and both keys land in the index:

capacity = (
    df
    .loc[lambda df_: df_['Status'] == 'operating']
    .groupby(['Region', 'Country'])['Capacity (MW)'].sum()
)

capacity.head(4)

Region  Country
Africa  Botswana       732.0
        Madagascar     120.0
        Mauritius      195.0
        Morocco       4257.0
Name: Capacity (MW), dtype: float64

That is a series with a two-level index, and it is correct. It is also not a table. reset_index makes it one:

capacity.reset_index().head(4)

   Region     Country  Capacity (MW)
0  Africa    Botswana          732.0
1  Africa  Madagascar          120.0
2  Africa   Mauritius          195.0
3  Africa     Morocco         4257.0

Three columns, a fresh RangeIndex, and something px.bar or merge will accept without argument. Because the index levels were named, the new columns inherited those names. The values column keeps the series name, and name= is how you change it in the same breath:

capacity.nlargest(5).reset_index(name='operating_mw')

     Region        Country  operating_mw
0      Asia          China     1136731.0
1      Asia          India      237148.2
2  Americas  United States      200090.2
3      Asia          Japan       55123.0
4      Asia      Indonesia       51556.6

You do not have to reset the whole thing. level= takes one level down and leaves the other where it is, which is often exactly what you want when the inner level is the interesting one:

capacity.reset_index(level='Region').head(4)

            Region  Capacity (MW)
Country
Botswana    Africa          732.0
Madagascar  Africa          120.0
Mauritius   Africa          195.0
Morocco     Africa         4257.0

Now the other direction. set_index takes a column name, or a list of them for a MultiIndex:

df.set_index(['Region', 'Status']).head(3)

                         Country  Capacity (MW)  Start year
Region   Status
Europe   cancelled       Albania          800.0         NaN
Americas operating     Argentina          120.0      2022.0
         construction  Argentina          120.0      2023.0

append=True says "add this to what is already there" rather than "replace it," so the same MultiIndex can be built in two steps, which is what you need when the second column only becomes available later in the chain:

df.set_index('Region').set_index('Status', append=True).head(3)

                         Country  Capacity (MW)  Start year
Region   Status
Europe   cancelled       Albania          800.0         NaN
Americas operating     Argentina          120.0      2022.0
         construction  Argentina          120.0      2023.0

And drop=False leaves the column in place while also making it the index, which is how you index a frame for a join without losing the column that a chart still needs to label by:

df.set_index('Country', drop=False).head(3)

             Country  Capacity (MW)        Status  Start year    Region
Country
Albania      Albania          800.0     cancelled         NaN    Europe
Argentina  Argentina          120.0     operating      2022.0  Americas
Argentina  Argentina          120.0  construction      2023.0  Americas

There is a fuller treatment of set_index — including why a DatetimeIndex lets you slice with .loc['2024-04'] — on the set_index page.

set_axis, which has its own page

There is a third way to change labels, for the case where you have a list of correct names and no mapping to get there from — often because the current names are Unnamed: 1 and Unnamed: 2 and there is nothing to map from. That is set_axis, and it has enough of its own arguments and traps to deserve a page: set_axis.

Four mistakes people make

Calling reset_index() on a filtered frame without drop=True. This is the one that follows you around. Filter a frame and the surviving rows keep their original row numbers, so a bare reset_index turns those numbers into a column called index:

poland = df.loc[lambda df_: df_['Country'] == 'Poland']
poland.reset_index().head(3)

   index Country  Capacity (MW)   Status  Start year  Region
0  10643  Poland          120.0  retired      1964.0  Europe
1  10644  Poland          120.0  retired      1964.0  Europe
2  10645  Poland          120.0  retired      1964.0  Europe

Nothing complains. The column rides along into to_csv, where it shows up in somebody's spreadsheet as a mystery first column, and into merge, where two frames that were both reset this way produce index_x and index_y alongside your real data. When the old row numbers are meaningless, say drop=True and mean it.

Reaching for drop=True reflexively, and losing the labels. The opposite mistake, and it is quieter, because there is no leftover column to notice. Sometimes the index is the data. Bamboo Weekly #54 is the case in point: the NY Fed's household debt report puts the year and quarter in the index, so the solution calls reset_index(), slices the year out of the resulting index column, groups by it, and only then drops the column. drop=True at the top of that chain would have thrown away the only copy of the dates.

Setting an index on a column with duplicates. set_index does not object, and it should not — a non-unique index is legal and sometimes deliberate. But .loc[] changes shape underneath you. Countries repeat in this file, once per generating unit:

df.set_index('Country').index.is_unique

False

df.set_index('Country').loc['Poland'].shape

(231, 4)

The reader who wrote .loc['Poland'] expecting one row got 231, and a data frame where they were counting on a series. Check before you rely on it. Note that verify_integrity=True is not the answer any more: in Pandas 3 it raises Pandas4Warning: The 'verify_integrity' keyword in DataFrame.set_index is deprecated and will be removed in a future version. Directly check the result.index.is_unique instead. Take the advice — is_unique is a one-line assertion and it reads better anyway.

Where it shows up in Bamboo Weekly

Fifty-two Bamboo Weekly solutions call reset_index. Four worth studying, all of them free to read:

Bamboo Weekly #60: Iceland is the round trip in a single chain: .loc[2019].reset_index().set_index('Country') takes a year out of a MultiIndex, flattens what is left into columns, and then re-indexes on the column the next join needs to align on. That pattern — reset, then set again on a different key — is how most joins between two awkwardly shaped frames actually get done.

Bamboo Weekly #70: Moon missions is the clearest level= example on the site. The table of 21st-century moon missions has a two-part index, and df.reset_index(level=0)['Mission.1'] moves only the outer level down into a column, leaving the spacecraft names in the index so that a filter(regex=...) can match on them.

Bamboo Weekly #54: Household debt is the one described above, and the best argument against reflexive drop=True. The index holds year-and-quarter labels from a badly formatted Excel export; reset_index is what makes them reachable with .str.

Practice it

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

Go deeper

The index is the thing that most other Pandas methods quietly depend on, so this page has a lot of neighbors. set_index goes into why a good index makes .loc[] and time-based slicing easy; sort_index puts those labels in order, which a MultiIndex needs before it will slice; xs selects on an inner level of one; and unstack moves a level sideways into the columns rather than down into the data. For changing labels rather than moving them, rename handles the mapping case and covers rename_axis as well. The Pandas user guide on indexing and selecting data is the canonical treatment.

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

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