Performance: .unique / .nunique of categorical series slow on large data set

[joshlk]

I've noticed that Series.unique and Series.nunique when used with a categorical series can be slow on large dataset. Presumably its not utilising the shortcuts:

Series.unique = Series.cat.categories
Series.nunique = len(Series.cat.categories)

Heres an example in iPhython:

s = pd.Series(np.random.choice(['a','b','c'], 100000000)).astype('category')
%time s.nunique()
896 ms
%time len(s.cat.categories)
55.1 µs

Its significantly slower indicating its not using the above shortcut.

pd.show_versions()

pandas: 0.17.1
Cython: 0.23.4
numpy: 1.10.1
IPython: 4.0.1

[jreback]

pls pd.show_versions()
and an example

[joshlk]

Sorry @jreback more details have been added above

[jreback]

so, .unique on a categorical has a couple of guarantees, namely that it is in the order of appearance, and it only includes values that are actually present, e.g.

In [3]: s = Series(list('babc')).astype('category',categories=list('abcd'))

In [4]: s.unique()
Out[4]: 
[b, a, c]
Categories (3, object): [b, a, c]

In [6]: s.cat.categories
Out[6]: Index([u'a', u'b', u'c', u'd'], dtype='object')

so if you want .categories, then just use that.

[jreback]

I suppose it is possible to add an option (to all .unique), not just this one, to do some sort of quick uniqueness like this, but not sure of the utility beyond categoricals.

[joshlk]

I didn't realise that, I thought they were equivalent. Thanks

[jorisvandenbossche]

It would be good to clarify this in the docs I think:

• add a note to the docstring of categories that this includes all categories and not only those present in the data
• add the above example (Performance: .unique / .nunique of categorical series slow on large data set #12278 (comment)) to the tutorial docs categorical.rst

[jreback]

ok, let's repurpose this then. @joshlk want to do a PR?

[Dorozhko-Anton]

Can I get this one?

[jreback]

sure

[joshlk]

Thanks for adding the clarity to the docs

[kodonnell]

I just came across this, and my example also showed 2-3 orders of magnitude performance increase, which:

• is pretty sweet for the user, and
• probably could be extended beyond unique (e.g. in grouping etc.)

I suppose it is possible to add an option (to all .unique), not just this one, to do some sort of quick uniqueness like this, but not sure of the utility beyond categoricals.

I'd take a somewhat different approach so that no API changes are required, and the win is 'free' in the 'standard' use cases (i.e. something like df['col1'] = df.col1.astype('category'), i.e. without manually specifying unused categories). That is, my naive gut feeling is that it shouldn't be too hard to add a flag to a categorical column for whether or not it's amenable to quick uniqing. Then all that's required is changing unique to detect 'if column is categorical and is amenable to quick uniquing, then use the quick method'. No change that I can see for the user (in API or the result) except much better performance. Thoughts @jreback ?

Note:

• would need to check for np.nan - could use .cat.codes for that as opposed to pd.isnull(...).any() or similar.
• (reiterating) the amenable to quicking uniquing flag may be useful in other functions for performance gains e.g. grouping.

[jorisvandenbossche]

@joshlk such a 'flag' would need to check both that all categories are used, that there are no missing values, that it is sorted (so also quite costly to compute), and would be have to be invalidated in many operations (eg that do sorting, selecting data, etc). So I don't think something like this would be worth it (certainly from a code maintenance perspective).

[kodonnell]

I was thinking that the only way to change categories was with a few function calls, and hence it could be easy with e.g. a decorator - but you're right, subsetting data with a select would also cause problems. If there are too many more possibilities, then it wouldn't be worth it.

check ... that all categories are used

I don't see why you need to check. E.g. if categories are created from "astype('category')" then they're all used. Any action which could change the categories used would flip the flag, so you wouldn't use the 'quick' method anyway.

check ... that there are no missing values

Do you mean np.nan? As above, isn't that an easy fix?

check ... that it is sorted

Are the results of unique required to be sorted? If so, couldn't you sort them afterward (i.e. quite quickly)?

So I don't think something like this would be worth it (certainly from a code maintenance perspective).

If it's going to be too painful, then no, it's probably not worth it. Then again, if it's not too painful, it offers some pretty worthwhile performance gains in a large proportion of uses.