ประโยชน์ด้านประสิทธิภาพของดัชนีที่เรียงแล้ว

เหตุใดการเรียงลำดับดัชนีจึงสำคัญต่อประสิทธิภาพ

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}')

ตรวจสอบว่าดัชนีเรียงลำดับแล้วหรือไม่

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)

การเรียงลำดับด้วย sort_index()

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)

การวัดเวลาค้นหาด้วย timeit

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 จาก MultiIndex ที่ไม่ได้เรียงลำดับ

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__)

การเปรียบเทียบเวลาการตัดช่วง MultiIndex ที่เรียงลำดับกับไม่ได้เรียงลำดับ

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 กับพารามิเตอร์ level

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 ในฐานะตัวป้องกันของไปป์ไลน์

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)

ดัชนีที่เรียงลำดับเพื่อการค้นหาแบบทวิภาคบนดัชนีทั่วไป

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')

ต้นทุนหน่วยความจำของการเรียงลำดับ

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)

สรุปแนวปฏิบัติที่ดีสำหรับดัชนีที่เรียงลำดับแล้ว

ตรวจสอบความเข้าใจอย่างรวดเร็ว

สรุปบทเรียน