Counting unique index values ​​in Pandas groupby

Question

Pandas has a very clean way to count individual values ​​in a column within a group by operation. for example

ex = pd.DataFrame([[1, 2, 3], [6, 7, 8], [1, 7, 9]], 
                  columns=["A", "B", "C"]).set_index(["A", "B"])
ex.groupby(level="A").C.nunique()

will return

A
1    2
6    1
Name: C, dtype: int64

I would also like to count the various values ​​at the index level B, and the grouping - A. I cannot find a clean way to access levels Bfrom an object groupby. The best I could come up with was:

ex.reset_index("B", drop=False).groupby(level="A").B.nunique()

which correctly returns:

A
1    2
6    1
Name: B, dtype: int64 

Is there a way to do this in a group without resetting the index or using a function apply?

Answer 1

IIUC you can do reset_indexfor all levels, then groupby be 'A' and apply the method nunique:

res = ex.reset_index().groupby('A').agg(lambda x: x.nunique())

In [339]: res
Out[339]:
   B  C
A
1  2  2
6  1  1

pivot_table:

In [341]: ex.reset_index().pivot_table(index='A', aggfunc=lambda x: x.nunique())
Out[341]:
   B  C
A
1  2  2
6  1  1

Answer 2

, , apply reset:)

In [20]: ex.groupby(level="A").agg(lambda x: x.index.get_level_values(1).nunique())
Out[20]:
   C
A
1  2
6  1

FWIW, . , ,

In [24]: ex.groupby(level="A").get_group(1)
Out[24]:
     C
A B
1 2  3
  7  9

:

In [33]: (ex.groupby(level='A')
   ....:    .C.agg({'a': lambda x: x.index.get_level_values(1).nunique(),
   ....:            'b': 'nunique'}))
Out[33]:
   b  a
A
1  2  2
6  1  1

Answer 3

For your pleasure, this is not a very simple read-out loud solution that does not use reset_indexeither apply, or agg, or, or anonymous functions. However, it uses zipand Counterfrom the standard library.

import pandas as pd
from collections import Counter

ex = pd.DataFrame([[1, 2, 3], [6, 7, 8], [1, 7, 9]], 
                  columns=["A", "B", "C"]).set_index(["A", "B"])

A_val, nunique_B = zip(*[(k, len(Counter(v.index.labels[v.index.names.index('B')]))) 
                      for k, v in ex.groupby(level='A')])

pd.Series(nunique_B, index=pd.Int64Index(A_val, name='A'))

returns

A
1    2
6    1
dtype: int32

In addition, for the sake of generality, I do not assume that it Bis at level 1 of the index.