Pandas & NumPy Academy · Lesson

Performance Benefits of Sorted Indices

Sort a MultiIndex with sort_index(), measure slice performance with timeit, and use is_monotonic_increasing as a guard.

Performance Benefits of Sorted Indices is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Index Sorting Matters for Performance

A sorted index enables Pandas to use binary search (O(log n)) instead of a full linear scan (O(n)) when looking up label ranges. For a DataFrame with one million rows, binary search finds the target range in about 20 comparisons vs. up to one million comparisons with a linear scan. This makes slice operations on sorted MultiIndexes orders of magnitude faster than on unsorted ones — and the difference becomes critical in production pipelines processing millions of rows.

import pandas as pd
import numpy as np

np.random.seed(42)
# Create a large DataFrame with a MultiIndex
countries = ['DE', 'UK', 'USA', 'FR', 'JP']
dates = pd.date_range('2020-01-01', periods=200)
mi = pd.MultiIndex.from_product([countries, dates], names=['country', 'date'])
df = pd.DataFrame({'value': np.random.randn(len(mi))}, index=mi)

print(f'DataFrame shape: {df.shape}')
print(f'Index is sorted: {df.index.is_monotonic_increasing}')

Checking if an Index Is Sorted

Use df.index.is_monotonic_increasing to check whether the index is sorted in ascending order. This returns a boolean. For a MultiIndex, Pandas checks sorting lexicographically across all levels. Always check this before performing slice operations with .loc[start:end] on a MultiIndex — an unsorted index will either raise UnsortedIndexError or silently return incorrect results depending on the Pandas version.

import pandas as pd

# Sorted MultiIndex
tuples_sorted = [('A', 1), ('A', 2), ('B', 1), ('B', 2)]
mi_sorted = pd.MultiIndex.from_tuples(tuples_sorted)
df_sorted = pd.DataFrame({'v': [10, 20, 30, 40]}, index=mi_sorted)

# Unsorted MultiIndex
tuples_unsorted = [('B', 2), ('A', 1), ('B', 1), ('A', 2)]
mi_unsorted = pd.MultiIndex.from_tuples(tuples_unsorted)
df_unsorted = pd.DataFrame({'v': [10, 20, 30, 40]}, index=mi_unsorted)

print('Sorted index is_monotonic_increasing:', df_sorted.index.is_monotonic_increasing)
print('Unsorted index is_monotonic_increasing:', df_unsorted.index.is_monotonic_increasing)

Sorting with sort_index()

df.sort_index() returns a new DataFrame with rows sorted by index label in ascending order. Use ascending=False for descending. For a MultiIndex, sorting is lexicographic: it sorts by the outermost level first, then by inner levels within each outer group. Always sort after any operation that might disorder the index — such as pd.concat, filtering, or appending new rows.

import pandas as pd
import numpy as np

np.random.seed(0)
countries = ['USA', 'UK', 'DE']
years = [2021, 2022, 2023]
mi = pd.MultiIndex.from_product([countries, years], names=['country', 'year'])
df = pd.DataFrame({'gdp': np.random.randint(3000, 26000, 9)}, index=mi)

print('Before sort_index():')
print(df.head(4))

df_sorted = df.sort_index()
print('\nAfter sort_index():')
print(df_sorted.head(4))
print('Is sorted:', df_sorted.index.is_monotonic_increasing)

Measuring Lookup Time with timeit

Python's timeit module measures how long a statement takes to execute by running it many times and averaging. Use it to benchmark sorted vs. unsorted index lookups. In IPython/Jupyter, the %timeit magic provides the same functionality with nicer output. Benchmarking is the only reliable way to confirm that a performance optimisation actually helped — never assume a change is faster without measuring it.

import pandas as pd
import numpy as np
import timeit

np.random.seed(0)
N = 500000
idx = np.random.choice(['A','B','C','D','E'], N)
df_unsorted = pd.DataFrame({'v': np.random.randn(N)}, index=idx)
df_sorted = df_unsorted.sort_index()

# Time label lookup: sorted vs unsorted
t_unsorted = timeit.timeit(lambda: df_unsorted.loc['C'], number=100)
t_sorted = timeit.timeit(lambda: df_sorted.loc['C'], number=100)

print(f'Unsorted lookup (100 runs): {t_unsorted:.3f}s')
print(f'Sorted lookup  (100 runs): {t_sorted:.3f}s')
print(f'Speedup: {t_unsorted/t_sorted:.1f}x')

PerformanceWarning from Unsorted MultiIndex

Pandas emits a PerformanceWarning when you slice a MultiIndex that is not lexicographically sorted: 'indexing past lexsort depth may impact performance'. This warning means Pandas had to fall back to a linear scan instead of binary search. While it still returns correct results in simple cases, it can return incorrect results when slicing inner levels of an unsorted multi-level index. Treat this warning as an error and fix the root cause by sorting the index.

import pandas as pd
import warnings

# Create an unsorted MultiIndex and trigger the warning
tuples = [('B', 2), ('A', 1), ('B', 1), ('A', 2)]
mi = pd.MultiIndex.from_tuples(tuples, names=['letter', 'num'])
df = pd.DataFrame({'v': [10, 20, 30, 40]}, index=mi)

print('Index sorted?', df.index.is_monotonic_increasing)

# This may trigger PerformanceWarning in some Pandas versions
with warnings.catch_warnings(record=True) as w:
    warnings.simplefilter('always')
    try:
        result = df.loc['A':'B', :]
        print('Result:', result)
        if w:
            print('Warning:', str(w[0].message))
    except Exception as e:
        print('Error (common with newer Pandas):', type(e).__name__)

Benchmarking Sorted vs Unsorted MultiIndex Slicing

Sorted MultiIndex slicing is dramatically faster because Pandas can binary-search both the outer and inner level arrays. Let's benchmark slicing a large MultiIndex with 1 million rows — a common size in production analytics pipelines. The sorted version avoids the linear scan and consistently shows 5-50× speedups depending on the selectivity of the slice.

import pandas as pd
import numpy as np
import timeit

np.random.seed(42)
countries = ['DE', 'UK', 'USA', 'FR', 'JP']
dates = pd.date_range('2010-01-01', periods=200000)

# Sample a random subset for timing test
sample_countries = np.random.choice(countries, 100000)
sample_dates = np.random.choice(dates, 100000)

df = pd.DataFrame({
    'country': sample_countries,
    'date': sample_dates,
    'value': np.random.randn(100000)
}).set_index(['country', 'date'])

df_sorted = df.sort_index()
print('Dataset size:', len(df))
print('Sorted:', df_sorted.index.is_monotonic_increasing)

t = timeit.timeit(lambda: df_sorted.loc['USA'], number=50)
print(f'Sorted lookup (50 runs): {t:.3f}s')

sort_index with level Parameter

For a MultiIndex, you can sort by a specific level rather than all levels using the level parameter: df.sort_index(level='year'). This is useful when you want to preserve the outer-level grouping but reorder rows within each outer group. The sort_remaining=True argument (default) also sorts any unsorted levels beyond the specified one, ensuring full lexicographic ordering.

import pandas as pd
import numpy as np

countries = ['USA', 'UK']
years = [2023, 2021, 2022]  # deliberately unordered
mi = pd.MultiIndex.from_product([countries, years], names=['country', 'year'])
df = pd.DataFrame({'v': range(6)}, index=mi)

print('Before sorting by year level:')
print(df)

# Sort by the inner level (year) only
df_ysorted = df.sort_index(level='year')
print('\nAfter sort_index(level="year"):')
print(df_ysorted)

is_monotonic_increasing as a Pipeline Guard

In production pipelines, add a sorting guard at the start of any function that receives a DataFrame with a MultiIndex and performs slicing. If the index is not sorted, sort it automatically and log a warning. This prevents silent performance degradation or incorrect results when upstream code changes the DataFrame order. A guard at the function boundary is more reliable than assuming callers always pass sorted data.

import pandas as pd
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def safe_slice(df, key):
    '''Slice a MultiIndex DataFrame, sorting if necessary.'''
    if not df.index.is_monotonic_increasing:
        logger.warning('Index not sorted — sorting now. This is a performance cost.')
        df = df.sort_index()
    return df.loc[key]

# Test with an unsorted DataFrame
tuples = [('B', 2), ('A', 1), ('B', 1), ('A', 2)]
mi = pd.MultiIndex.from_tuples(tuples, names=['letter', 'num'])
df = pd.DataFrame({'v': [10, 20, 30, 40]}, index=mi)

result = safe_slice(df, 'A')
print('Slice result for A:')
print(result)

Sorted Index for Binary Search on Simple Indices

The performance benefits of sorting apply to regular (non-multi) indices too. A DatetimeIndex used in time series analysis performs much faster date range slicing when sorted. A string index sorted alphabetically allows binary search for label lookups. For a large Series of stock prices indexed by timestamp, sorting the DatetimeIndex can turn a 100ms slice into a sub-millisecond operation.

import pandas as pd
import numpy as np
import timeit

np.random.seed(0)
# Random timestamps — unsorted
timestamps = pd.date_range('2020-01-01', periods=500000, freq='min')
shuffled = np.random.permutation(timestamps)

prices = pd.Series(np.random.randn(500000), index=shuffled)
prices_sorted = prices.sort_index()

# Time a date range slice
t_unsorted = timeit.timeit(lambda: prices['2020-06-01':'2020-06-30'], number=20)
t_sorted = timeit.timeit(lambda: prices_sorted['2020-06-01':'2020-06-30'], number=20)

print(f'Unsorted: {t_unsorted:.3f}s')
print(f'Sorted:   {t_sorted:.3f}s')
print(f'Speedup: {t_unsorted/t_sorted:.0f}x')

Memory Cost of Sorting

Sorting is not free — sort_index() creates a new copy of the DataFrame (unless using inplace=True which modifies in place). For very large DataFrames, this doubles peak memory usage temporarily. A pragmatic strategy is to sort once at load time and keep the sorted version throughout the pipeline, rather than sorting repeatedly. If memory is tight, sort in place with df.sort_index(inplace=True) to avoid the temporary copy.

import pandas as pd
import numpy as np

np.random.seed(0)
countries = ['DE', 'UK', 'USA']
years = [2021, 2022, 2023]
mi = pd.MultiIndex.from_product([countries, years], names=['country', 'year'])
df = pd.DataFrame({'v': np.random.randn(9)}, index=mi)

# Sort once at load time — best practice
df.sort_index(inplace=True)  # no temporary copy
assert df.index.is_monotonic_increasing, 'Index must be sorted!'
print('Pipeline-ready DataFrame (sorted in place):')
print(df)

Summary of Sorted Index Best Practices

Key rules for index performance: 1) Always call sort_index() after any operation that may disorder the index (concat, merge, append, filter). 2) Use is_monotonic_increasing as a guard in functions that slice the index. 3) Sort at load time and keep the sorted DataFrame throughout the pipeline to avoid repeated sorting. 4) For MultiIndex DataFrames, ensure all levels are sorted, not just the outermost. 5) Use timeit to verify that sorting actually provides the expected speedup in your specific pipeline.

Quick Check

Test your understanding of sorted index performance from this lesson.

Lesson Recap

In this lesson you learned: is_monotonic_increasing checks whether an index is sorted and binary search is available, sort_index() sorts in place or returns a sorted copy, and timeit measures the actual speedup to confirm the benefit. Next up we explore window functions — rolling and expanding statistics for time series and financial data.

Frequently asked questions

Is the “Performance Benefits of Sorted Indices” lesson free?

Yes — the full text of “Performance Benefits of Sorted Indices” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.

What will I learn in “Performance Benefits of Sorted Indices”?

Sort a MultiIndex with sort_index(), measure slice performance with timeit, and use is_monotonic_increasing as a guard. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Pandas & NumPy Academy?

No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Performance Benefits of Sorted Indices” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Pandas & NumPy Academy lesson?

Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Pandas & NumPy Academy