Sort DataFrames with Multi-Index Keys

You’ve built a perfectly tidy DataFrame, then you stack it with groupby or pivot_table, and suddenly your rows are in a seemingly random order. Sorting a single column is easy, but when your index has multiple levels — say, region and product — sort_values won’t help, and sort_index() without arguments gives you a headache. This lesson teaches you exactly how to sort DataFrames with multi-index keys using pandas, so you can control the order of complex hierarchical data with confidence.

The problem this lesson solves

When you work with real-world data, hierarchical indexes are everywhere. A groupby(['region', 'product']) operation, a pivot_table, or a MultiIndex from pd.MultiIndex.from_product all produce DataFrames with multiple index levels. The rows might first be grouped by region, then by product, but the order within each level is alphabetical — not necessarily what you want for reporting, analysis, or visualization.

Trying df.sort_values('sales') fails because 'sales' is a column, not an index level. And df.sort_index() sorts by all levels of the index, which can be confusing if you only care about one level. Without understanding how to slice and dice the index levels, you end up with unsorted output, broken visualizations, or off-by-one errors when selecting rows.

This lesson eliminates the guesswork. By the end, you’ll know how to sort by one or more index levels, control ascending order per level, and avoid the pitfalls that trip up even experienced pandas users.

Core concept / mental model

Think of a multi-index as a tree: each level is a branch, and the leaf rows are the data. Sorting the DataFrame is like arranging leaves by branch. You can decide which branch (level) matters most, and whether to go from smallest to largest (ascending) or largest to smallest (descending).

The key tool is DataFrame.sort_index(), which understands the hierarchical structure. Instead of sorting by column values, you tell pandas which index level(s) to use, and it reorders rows accordingly.

Mental model: sort_index(level=...) is the “sort by index” counterpart to sort_values(by=...). It works for single-level indexes too, but it shines when you have multiple levels.

How it works step by step

Let’s break down the logic behind sorting with multi-index keys.

Step 1 – Recognize your index levels

Every DataFrame index has a name (or multiple names for MultiIndex). To sort correctly, you need to know those names. Use df.index.names to list them.

Step 2 – Choose your sorting level(s)

The level parameter of sort_index() accepts: - A single level name (string) or position (integer) - A list of level names/positions for multi-level sorting

If you pass a single level, pandas sorts by that level only, ignoring the others. If you pass a list, it sorts by all specified levels, in order — like ORDER BY in SQL.

Step 3 – Control the sort direction

The ascending parameter accepts a boolean or a list of booleans. For multiple levels, you can specify different directions per level, e.g., ascending=[True, False].

Step 4 – Apply and verify

After calling sort_index(), always check the result. Use df.head() or df.index to confirm the order matches your intent.

The general pattern looks like this:

# Sort by a single level, ascending
sorted_df = df.sort_index(level='region')

# Sort by multiple levels, with mixed directions
sorted_df = df.sort_index(level=['region', 'product'], ascending=[True, False])

Hands-on walkthrough

Let’s put the theory into practice with real pandas code. We’ll create a sample DataFrame with a MultiIndex, then apply different sorting strategies.

Setting up sample data

The example builds a quarterly sales report across regions and product categories.

import pandas as pd
import numpy as np

# Create a MultiIndex
index = pd.MultiIndex.from_product(
    [['North', 'South', 'West'], ['Widgets', 'Gadgets', 'Gizmos']],
    names=['region', 'product']
)

df = pd.DataFrame({
    'sales': np.random.randint(100, 500, size=len(index)),
    'quarter': 'Q1'
}, index=index)

print(df.head(6))

Expected output (yours will vary due to np.random):

                 sales quarter
region product                
North  Widgets     326      Q1
       Gadgets     484      Q1
       Gizmos      154      Q1
South  Widgets     271      Q1
       Gadgets     132      Q1
       Gizmos      404      Q1

Note the default order: North products are alphabetical (Gadgets, Gizmos, Widgets), which may not be what you want.

Sorting by a single index level

Suppose we want to see all products grouped together, regardless of region. Sort by product level only:

# Sort by 'product' level, ignoring region
sorted_by_product = df.sort_index(level='product')
print(sorted_by_product.head(10))

Expected output (sample):

                 sales quarter
region product                
North  Gadgets     484      Q1
South  Gadgets     132      Q1
West   Gadgets     383      Q1
North  Gizmos      154      Q1
...

Now all Gadgets appear first, then Gizmos, then Widgets — but the region order within each product group may still follow the default (alphabetical) order, because we didn’t specify it.

Sorting by multiple index levels (multi-index keys)

Now we want a more meaningful order: within each region, products sorted by sales (descending), so we can see the top performer per region.

Use level with a list and ascending with a matching list:

# Sort by region, then by sales (numeric column) using a secondary sort
# First, reset index to make sales sortable, then sort_values works
sorted_df = df.reset_index().sort_values(['region', 'sales'], ascending=[True, False]).set_index(['region', 'product'])
print(sorted_df.head(10))

Expected output (sample, sales vary):

                 sales quarter
region product                
North  Gadgets     484      Q1
       Widgets     326      Q1
       Gizmos      154      Q1
South  Gizmos      404      Q1
       Widgets     271      Q1
       Gadgets     132      Q1
West   Widgets     383      Q1
       Gadgets     284      Q1
       Gizmos      245      Q1

Why not sort_index() here? To sort by a column value within a group, you need sort_values after resetting the index. If your sort key is an index level, use sort_index. If it’s a column, reset, sort, then re-set the index.

Sorting only by index levels with sort_index()

If you want to order by the product name only (not by sales), sort_index is the right tool:

# Sort by product name only
sorted_by_product = df.sort_index(level='product')
print(sorted_by_product.head(6))

Check your work

After any sort, verify the index order:

print(sorted_df.index)
print(sorted_df.loc[('North', 'Gadgets')])

Compare options / when to choose what

When to choose what: - Need hierarchical order from the index alone → sort_index(level=...) - Need to sort by column values within groups → reset index, sort_values, then set index back - Need top N records → nlargest or nsmallest

For most multi-index sorting tasks, sort_index is your primary tool. Use the column-based approach only when the sort key lives in a column.

Troubleshooting & edge cases

1. sort_index(level='sales') raises an error

sales is not an index level; it’s a column. Use the column-based approach, or first set sales as part of the index if appropriate.

Fix:

# Reset index to turn index levels into columns, then sort_values
sorted_df = df.reset_index().sort_values('sales')

2. Sorting only by the second level doesn’t regroup the primary level

When you pass a single level name to sort_index(level='product'), only that level’s order changes. The primary level (e.g., region) remains in its original grouping. If you want to sort by two levels together, pass both.

# Sort by both region and product
df.sort_index(level=['region', 'product'], ascending=[True, True])

3. ascending list length mismatch

If you provide a list of levels but a single boolean, pandas will broadcast it. If you provide a list of booleans that doesn’t match the number of levels, you get a ValueError.

Fix: Ensure len(ascending) == len(level).

# Correct: two levels, two booleans
df.sort_index(level=['region', 'product'], ascending=[True, False])

4. Performance on large DataFrames

Sorting a multi-index can be slower than sorting a single column. For huge DataFrames, consider using sort_values with kind='mergesort' for a stable sort, or pre-sort your data at write time.

5. Mixed-type index levels

If index levels contain mixed types (e.g., strings and numbers), sorting may raise a TypeError. Convert the level to a consistent type first.

# Convert to string if needed
df.index = df.index.set_levels(df.index.levels[0].astype(str), level=0)

What you learned & what's next

You now know how to sort DataFrames with multi-index keys using pandas’ sort_index and column-based alternatives. You can: - Identify index levels with df.index.names - Sort by one or more index levels with level= and control direction with ascending - Sort by column values when the sort key isn’t an index level - Diagnose common mistakes like mismatched level lists or wrong parameter usage

This skill is foundational for preparing tidy data for analysis. Your next step in this track is group-by aggregation with multi-indexes — when you collapse rows into summary statistics, the same sorting principles will help you present results clearly. Practice what you learned by sorting a multi-index DataFrame in your own projects, and you’ll find that even complex hierarchies become easy to manage.