What is the performance impact of non-unique indexes in pandas?

29

7

From the pandas documentation, I've gathered that unique-valued indices make certain operations efficient, and that non-unique indices are occasionally tolerated.

From the outside, it doesn't look like non-unique indices are taken advantage of in any way. For example, the following ix query is slow enough that it seems to be scanning the entire dataframe

In [23]: import numpy as np
In [24]: import pandas as pd
In [25]: x = np.random.randint(0, 10**7, 10**7)
In [26]: df1 = pd.DataFrame({'x':x})
In [27]: df2 = df1.set_index('x', drop=False)
In [28]: %timeit df2.ix[0]
1 loops, best of 3: 402 ms per loop
In [29]: %timeit df1.ix[0]
10000 loops, best of 3: 123 us per loop

(I realize the two ix queries don't return the same thing -- it's just an example that calls to ix on a non-unique index appear much slower)

Is there any way to coax pandas into using faster lookup methods like binary search on non-unique and/or sorted indices?

ChrisB

Posted 2013-05-18T15:44:31.040

Reputation: 2 652

Answers

65

When index is unique, pandas use a hashtable to map key to value O(1). When index is non-unique and sorted, pandas use binary search O(logN), when index is random ordered pandas need to check all the keys in the index O(N).

You can call sort_index method:

import numpy as np
import pandas as pd
x = np.random.randint(0, 200, 10**6)
df1 = pd.DataFrame({'x':x})
df2 = df1.set_index('x', drop=False)
df3 = df2.sort_index()
%timeit df1.loc[100]
%timeit df2.loc[100]
%timeit df3.loc[100]

result:

10000 loops, best of 3: 71.2 µs per loop
10 loops, best of 3: 38.9 ms per loop
10000 loops, best of 3: 134 µs per loop

HYRY

Posted 2013-05-18T15:44:31.040

Reputation: 63 638

1Highly recommended Answer! Appreciate it. – Neerav – 2014-09-03T20:22:53.690

1I don't understand the timings at the end. df3 should be faster? – lucid_dreamer – 2018-08-21T08:44:46.317

@lucid_dreamer I was confused too, but df1 uses the default index which goes from 0 to len(df1) - 1 and is unique, so df1.loc[] uses a hashtable. df2 sets the index to 'x' which is not unique and not sorted, so it does a linear scan, O(N). df3 is the same as df2 but sorted and still non-unique, so it does a binary search. – Max Taggart – 2018-10-17T21:26:37.083

So why is the linear scan of df2 faster? – lucid_dreamer – 2018-10-18T09:55:03.807

1@lucid_dreamer please notice the unit of time. – HYRY – 2018-10-19T01:26:44.167

omg I'm sleeping – lucid_dreamer – 2018-10-25T18:03:28.303