Pandas distinct count as a DataFrame
<p>Suppose I have a Pandas DataFrame called <code>df</code> with columns <code>a</code> and <code>b</code> and what I want is the number of distinct values of <code>b</code> per each <code>a</code>. I would do:</p>

<pre><code>distcounts = df.groupby('a')['b'].nunique()
</code></pre>

<p>which gives the desidered result, but it is as Series object rather than another DataFrame. I'd like a DataFrame instead. In regular SQL, I'd do:</p>

<pre><code>SELECT a, COUNT(DISTINCT(b)) FROM df
</code></pre>

<p>and haven't been able to emulate this query in Pandas exactly. How to?</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>:</p>

<pre><code>distcounts = df.groupby('a')['b'].nunique().reset_index()
</code></pre>

<p>Sample:</p>

<pre><code>df = pd.DataFrame({'a':[7,8,8],
                   'b':[4,5,6]})

print (df)
   a  b
0  7  4
1  8  5
2  8  6

distcounts = df.groupby('a')['b'].nunique().reset_index()
print (distcounts)
   a  b
0  7  1
1  8  2
</code></pre>

python sql pandas count distinct