Flip the sort order of already sorted dataframe

user_3023116 0
Viewed 148

I have a bunch of dataframes with the structure like below

df = pd.DataFrame(
    [[1, 'A', 10], [2, 'A', 20], [3, 'A', 30], 
     [1, 'B', 20], [2, 'B', 20], [3, 'B', 10],
     [1, 'M', 20], [2, 'M', 30], [3, 'M', 30]], 
    columns=['foo', 'bar', 'buzz']
)

the dataframe is initially sorted by columns bar and foo as one can get from

df.sort_values(['bar', 'foo'])

I need to get the df sorted by foo an bar instead. The obvious solution would be

df.sort_values(['foo', 'bar'])

which gives me

   foo bar  buzz
0    1   A    10
3    1   B    20
6    1   M    20
1    2   A    20
4    2   B    20
7    2   M    30
2    3   A    30
5    3   B    10
8    3   M    30

but the real-world dataframe contains about 500,000 rows and I have about 3,000 individual dataframes to be processed.

I was wondering if there is a better, more efficient solution which would take into account the fact that the dataframe is already pre-sorted?

pandas numpy sorting algorithm python
1 Answers

You can take advantage of stable sorting here, since bar is already sorted, which means that you only need to re-sort foo.

This should have a consistent impact on runtime at all sizes of DataFrame (I am seeing about a 2x speedup across the board).

Here is an example solution using numpy's argsort, specifying a stable sort.

df.iloc[np.argsort(df['foo'], kind="stable")]

   foo bar  buzz
0    1   A    10
3    1   B    20
6    1   M    20
1    2   A    20
4    2   B    20
7    2   M    30
2    3   A    30
5    3   B    10
8    3   M    30

Performance and Validation

df = pd.DataFrame(
    {
        "foo": np.random.randint(0, 100, 100_000),
        "bar": np.random.choice(list("ABCDEFGHIJKLMNOP"), 100_000),
        "buzz": np.random.randint(0, 100, 100_000),
    }
).sort_values(["bar", "foo"])

In [42]: %timeit df.iloc[np.argsort(df['foo'], kind="stable")]                                          
3.41 ms ± 22.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [43]: %timeit df.sort_values(["foo", "bar"])                                                         
6.95 ms ± 136 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [48]: a = df.iloc[np.argsort(df['foo'], kind="stable")]                                              

In [49]: b = df.sort_values(["foo", "bar"])                                                             

In [50]: np.all(a == b)                                                                                 
Out[50]: True
user_34832030
Related