For large dataframes of numeric data, you may see a significant performance improvement via numpy.lexsort
, which performs an indirect sort using a sequence of keys:
import pandas as pd
import numpy as np
np.random.seed(0)
df1 = pd.DataFrame(np.random.randint(1, 5, (10,2)), columns=['a','b'])
df1 = pd.concat([df1]*100000)
def pdsort(df1):
return df1.sort_values(['a', 'b'], ascending=[True, False])
def lex(df1):
arr = df1.values
return pd.DataFrame(arr[np.lexsort((-arr[:, 1], arr[:, 0]))])
assert (pdsort(df1).values == lex(df1).values).all()
%timeit pdsort(df1) # 193 ms per loop
%timeit lex(df1) # 143 ms per loop
One peculiarity is that the defined sorting order with numpy.lexsort
is reversed: (-'b', 'a')
sorts by series a
first. We negate series b
to reflect we want this series in descending order.
Be aware that np.lexsort
only sorts with numeric values, while pd.DataFrame.sort_values
works with either string or numeric values. Using np.lexsort
with strings will give: TypeError: bad operand type for unary -: 'str'
.