Skip to content
Bamboo Weekly

pandas sort_index

Put the rows — or the columns — in order by their labels.

Have you ever grouped by year, gotten back exactly the right numbers, and then watched plot.line draw a line that zig-zagged backwards through time? Nothing was wrong with the calculation. The rows simply came out in whatever order the previous method felt like producing them in — for value_counts, most common first.

sort_index is the fix, and the whole method fits into one sentence: it reorders your data by the index labels. Not by the values in the columns — by the labels. That is the entire difference between it and sort_values, which reorders by the data. If you want the biggest number at the top, you want sort_values. If you want 1942 before 1943, Albania before Algeria, or January before February, you want sort_index.

Official documentation: DataFrame.sort_index

The arguments that earn their keep

df.sort_index(axis='index',         # 'index' (default) or 'columns'
              level=None,           # which MultiIndex level to sort on
              ascending=True,       # False, or a list with one entry per level
              na_position='last',   # 'first' puts missing labels at the top
              sort_remaining=True)  # after `level`, also sort the other levels

Most of the time you call it with no arguments at all, and that is the right call. ascending also accepts a list, so a two-level index can run one way on the outside and the other way on the inside. axis='columns' sorts your column names instead of your rows, which is the most underused trick in the method. level picks one level of a MultiIndex to sort by, and sort_remaining decides whether the other levels get sorted after it. na_position matters because missing labels go to the bottom no matter which direction you sort, until you say otherwise.

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 the year it started running. 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 classic case. How many operating units came online in each year? Start year arrives as a float, since not every unit has one, so I cast it to Int64 to keep the labels looking like years:

(
    df
    .loc[lambda df_: df_['Status'] == 'operating', 'Start year']
    .astype('Int64')
    .value_counts()
    .head(5)
)

Start year
2006    262
2007    257
2011    253
2015    251
2010    241
Name: count, dtype: Int64

Every number there is correct, and the series is still useless for a chart, because value_counts sorts by frequency. One more method and the years line up:

(
    df
    .loc[lambda df_: df_['Status'] == 'operating', 'Start year']
    .astype('Int64')
    .value_counts()
    .sort_index()
    .head(5)
)

Start year
1942    2
1944    1
1951    5
1952    3
1953    9
Name: count, dtype: Int64

Now a MultiIndex, where the arguments start to matter. I am grouping capacity by region and status, and passing sort=False so that groupby leaves the groups in the order it met them — which is the order of the spreadsheet, and a fair picture of what real data hands you:

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

capacity

Region    Status
Americas  operating        221444.0
          construction        120.0
Oceania   operating         22903.0
Asia      operating       1667465.4
          construction     193653.5
Europe    operating        167153.5
Africa    operating         51152.6
          construction       2805.0
Europe    construction        735.0
Name: Capacity (MW), dtype: float64

Europe appears twice, in two different places. sort_index puts the index into proper order, sorting the outer level first and then the inner one within each group:

capacity.sort_index()

Region    Status
Africa    construction       2805.0
          operating         51152.6
Americas  construction        120.0
          operating        221444.0
Asia      construction     193653.5
          operating       1667465.4
Europe    construction        735.0
          operating        167153.5
Oceania   operating         22903.0
Name: Capacity (MW), dtype: float64

ascending=False reverses both levels at once. To reverse only one of them, pass a list with one entry per level — here, regions alphabetically but operating capacity above capacity under construction:

capacity.sort_index(ascending=[True, False])

Region    Status
Africa    operating         51152.6
          construction       2805.0
Americas  operating        221444.0
          construction        120.0
Asia      operating       1667465.4
          construction     193653.5
Europe    operating        167153.5
          construction        735.0
Oceania   operating         22903.0
Name: Capacity (MW), dtype: float64

level reaches past the outer level and sorts by an inner one instead, which regroups the whole series around status rather than region:

capacity.sort_index(level='Status')

Region    Status
Africa    construction       2805.0
Americas  construction        120.0
Asia      construction     193653.5
Europe    construction        735.0
Africa    operating         51152.6
Americas  operating        221444.0
Asia      operating       1667465.4
Europe    operating        167153.5
Oceania   operating         22903.0
Name: Capacity (MW), dtype: float64

Notice that the regions came out alphabetically inside each status block, even though I never asked for that. sort_remaining=True is the default, and it means "after sorting on the level I named, tidy up the rest." Turn it off and the other levels keep the order they already had:

capacity.sort_index(level='Status', sort_remaining=False)

Region    Status
Americas  construction        120.0
Asia      construction     193653.5
Africa    construction       2805.0
Europe    construction        735.0
Americas  operating        221444.0
Oceania   operating         22903.0
Asia      operating       1667465.4
Europe    operating        167153.5
Africa    operating         51152.6
Name: Capacity (MW), dtype: float64

Finally, the sideways version, which almost nobody reaches for and almost everybody could use. Column names are labels too, and a spreadsheet's column order is whatever the people who built it happened to choose. usecols does not change that — ask for five columns in one order and you get them back in the file's order:

df.columns

Index(['Country', 'Capacity (MW)', 'Status', 'Start year', 'Region'], dtype='str')

df.sort_index(axis='columns').head(3)

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

On a five-column frame that is a nicety. On the full 40-column version of this same sheet, alphabetical column names are the difference between finding Coal type in a second and hunting for it in a scrollbar.

Where it shows up in Bamboo Weekly

Bamboo Weekly #65: Microplastics is the purest example of the archetype above: df['Date'].dt.year.value_counts() gives the number of ocean samples per year in frequency order, and .sort_index() before .plot.line() is what turns it into a chart that reads left to right through time.

Bamboo Weekly #71: Holidays sets the date column as the index and sorts it, so that a table of public holidays from every country in the world runs in chronological order before a groupby plus idxmin picks out the first time each country celebrated each holiday.

Bamboo Weekly #59: Long covid shows the defensive use. The CDC pulse survey arrives with a three-level index in no particular order, and .loc[(4, 'By Age')] warns before it answers. .sort_index() in front of the .loc[] makes the warning go away.

Bamboo Weekly #20: World inflation is the same problem, one degree harder. I could not get rid of the warning even after sorting, and traced it to a Pandas bug in which sort_index did not set the internal flag that records how deeply the index is sorted. The workaround was to name the levels explicitly, sort_index(level=df.index.names). In Pandas 3 a plain sort_index() sets that flag correctly, so that workaround is no longer needed.

Four mistakes people make

Expecting it to sort the data. sort_index never looks at your values. A grouped total sorted with sort_index comes out in alphabetical order by group name, which is almost never the answer to "who is the biggest?" That question is sort_values, or nlargest.

A MultiIndex that is not lexsorted. This is the mistake that costs the most time, because Pandas complains in two entirely different ways. Take the coal data in file order, indexed by region and country:

units = df.set_index(['Region', 'Country'])
units.loc[('Europe', 'Poland')]

PerformanceWarning: indexing past lexsort depth may impact performance.

That one is a warning, and the answer is still correct. Ask for a slice rather than an exact label, though, and it becomes fatal:

units.loc[('Europe', slice('Poland', 'Spain')), :]

UnsortedIndexError: 'MultiIndex slicing requires the index to be lexsorted:
                     slicing on levels [1], lexsort depth 0'

Both go away the moment you put .sort_index() in front of the .loc[]. Make it a habit: the line after set_index with more than one column is sort_index.

A date index that is really a string index. Labels sort by their type, and a date that was never parsed is a string. The Philadelphia Fed's Business Outlook Survey, the data behind Bamboo Weekly #116, stores its dates as May-68, Jun-68, and so on:

bos = pd.read_csv('https://www.bambooweekly.com/content/files/2025/04/'
                  'bos_history.csv',
                  usecols=['DATE', 'gacdna'])

bos.set_index('DATE')['gacdna'].sort_index().head(5)

DATE
Apr-00     9.7
Apr-01    29.1
Apr-02    15.3
Apr-03    22.9
Apr-04     7.5
Name: gacdna, dtype: float64

That is a perfect sort — of strings. Every April in 57 years of data comes first, because A comes before F, J, and M. The same thing happens to dates written as 2026-1-5, which sorts before 2026-12-1 for exactly the reason Apr sorts before May. Parse before you sort, with to_datetime or with parse_dates in read_csv, and check .index.dtype if you are unsure which kind of index you have.

Labels of mixed types. Filling in the missing values before you set the index is a sensible instinct, but a word like "unknown" in a column of floats leaves Pandas with nothing it can compare:

(
    df
    .assign(**{'Start year': df['Start year'].fillna('unknown')})
    .set_index('Start year')
    .sort_index()
)

TypeError: '<' not supported between instances of 'float' and 'str'

Leave the missing values as NaN and let na_position decide where they go. Sorting knows what to do with NaN; it does not know what to do with the word "unknown."

Practice it

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

Go deeper

The companion method is sort_values, and between them the two cover every ordering question you will have: labels here, data there. The reason you have an index worth sorting is usually set_index, groupby, or value_counts; the reason a sorted index matters is usually loc or xs, both of which want a lexsorted MultiIndex before they will slice. The Pandas user guide's chapter on advanced indexing explains why.

How to sort in Pandas covers the ground in a few minutes. 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 27 Bamboo Weekly exercises that use sort_index on real-world data — try each one, then study the worked solution.

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