6

I am working with some data and end up with a situation where I want to cut a series like this:

df = pd.DataFrame({'A': 10000*[1], 'B': np.random.randint(0, 1001, 10000)})

df['level'] = pd.cut(df.B, bins = [0, 200, 400, 600, 800, 1000], 
                     labels = ['i', 'ii', 'iii', 'iv', 'v'])

To count the number of values in each level, I find two different answers when I do the following:

df.level.value_counts(sort = False)

i      1934
ii     1994
iii    2055
iv     2056
v      1952
Name: level, dtype: int64

df.pivot_table(index = 'A', columns = 'level', values = 'B', aggfunc = 'count').loc[1]

level
i      1994
ii     2056
iii    1934
iv     1952
v      2055
Name: 1, dtype: int64

Shouldn't both methods give the same results?

Mateus Sampaio
  • 113
  • 5

1 Answers1

2

Yes, they should give the same results. And in fact they do, even in older versions of Pandas:

In [22]: pd.__version__
Out[22]: '0.20.3'

In [23]: df = pd.DataFrame({'A': 10000*[1], 'B': np.random.randint(0, 1001, 10000)})
In [24]: df['level'] = pd.cut(df.B, bins = [0, 200, 400, 600, 800, 1000], 
                              labels = ['i', 'ii', 'iii', 'iv', 'v'])

In [25]: df.level.value_counts(sort = False)
Out[25]: 
i      1968
ii     1936
iii    1992
iv     2073
v      2022
Name: level, dtype: int64

In [26]: df.pivot_table(index = 'A', columns = 'level', values = 'B', aggfunc = 'count').loc[1]
Out[26]: 
level
i      1968
ii     1936
iii    1992
iv     2073
v      2022
Name: 1, dtype: int64

Using df.groupby() also works:

In [27]: df.groupby('level').size()
Out[27]: 
level
i      1968
ii     1936
iii    1992
iv     2073
v      2022
dtype: int64

Thus, the version you are using probably has some bug.