pandas reset_index after groupby.value_counts()
57,868
You need parameter name
in reset_index
, because Series
name is same as name of one of levels of MultiIndex
:
df_grouped.reset_index(name='count')
Another solution is rename
Series
name:
print (df_grouped.rename('count').reset_index())
A Amt count
0 1 30 4
1 1 20 3
2 1 40 2
3 2 40 3
4 2 10 2
More common solution instead value_counts
is aggregate size
:
df_grouped1 = dftest.groupby(['A','Amt']).size().reset_index(name='count')
print (df_grouped1)
A Amt count
0 1 20 3
1 1 30 4
2 1 40 2
3 2 10 2
4 2 40 3
Comments
-
muon almost 4 years
I am trying to groupby a column and compute value counts on another column.
import pandas as pd dftest = pd.DataFrame({'A':[1,1,1,1,1,1,1,1,1,2,2,2,2,2], 'Amt':[20,20,20,30,30,30,30,40, 40,10, 10, 40,40,40]}) print(dftest)
dftest looks like
A Amt 0 1 20 1 1 20 2 1 20 3 1 30 4 1 30 5 1 30 6 1 30 7 1 40 8 1 40 9 2 10 10 2 10 11 2 40 12 2 40 13 2 40
perform grouping
grouper = dftest.groupby('A') df_grouped = grouper['Amt'].value_counts()
which gives
A Amt 1 30 4 20 3 40 2 2 40 3 10 2 Name: Amt, dtype: int64
what I want is to keep top two rows of each group
Also, I was perplexed by an error when I tried to
reset_index
df_grouped.reset_index()
which gives following error
df_grouped.reset_index() ValueError: cannot insert Amt, already exists