pandas reset_index after groupby.value_counts()
<p>I am trying to groupby a column and compute value counts on another column.</p>

<pre><code>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)
</code></pre>

<p>dftest looks like</p>

<pre><code>    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
</code></pre>

<p>perform grouping</p>

<pre><code>grouper = dftest.groupby('A')
df_grouped = grouper['Amt'].value_counts()
</code></pre>

<p>which gives</p>

<pre><code>   A  Amt
1  30     4
   20     3
   40     2
2  40     3
   10     2
Name: Amt, dtype: int64
</code></pre>

<p>what I want is to keep top two rows of each group</p>

<p>Also, I was perplexed by an error when I tried to <code>reset_index</code></p>

<pre><code>df_grouped.reset_index()
</code></pre>

<p>which gives following error</p>

<blockquote>
  <p>df_grouped.reset_index()
  ValueError: cannot insert Amt, already exists</p>
</blockquote>

<p>You need parameter <code>name</code> in <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>, because <code>Series</code> name is same as name of one of levels of <code>MultiIndex</code>:</p>

<pre><code>df_grouped.reset_index(name='count')
</code></pre>

<p>Another solution is <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.rename.html" rel="nofollow"><code>rename</code></a> <code>Series</code> name:</p>

<pre><code>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
</code></pre>

<hr>

<p>More common solution instead <code>value_counts</code> is aggregate <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.size.html" rel="nofollow"><code>size</code></a>:</p>

<pre><code>df_grouped1 =  dftest.groupby(['A','Amt']).size().rename('count').reset_index()

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
</code></pre>

python pandas dataframe data-manipulation data-science