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>Another alternative using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html" rel="nofollow"><code>Groupby.agg</code></a> instead:</p>

<pre><code>df.groupby('a', as_index=False).agg({'b': 'nunique'})
</code></pre>

python sql pandas count distinct