Grouping a pandas dataframe in a suitable format for creating a chart
<p>Suppose I have the following pandas dataframe:</p>

<pre><code>In [1]: df
Out[1]:
  sentiment        date
0       pos  2016-10-08
1       neu  2016-10-08
2       pos  2016-10-09
3       neg  2016-10-09
4       neg  2016-10-09
</code></pre>

<p>I can indeed create a dataframe that makes summary statistics about the sentiment column per day as follows:</p>

<pre><code>gf=df.groupby(["date", "sentiment"]).size().reset_index(name='count')
</code></pre>

<p>which gives</p>

<pre><code>In [2]: gf
Out[2]:
         date sentiment  count
0  2016-10-08       neu      1
1  2016-10-08       pos      1
2  2016-10-09       neg      2
3  2016-10-09       pos      1
</code></pre>

<p>However I need to transform this result in the following tabular format (or new dataframe) so as to be able to make a bar chart (like for example as in <a href="https://drive.google.com/previewtemplate?id=0AoFkkLP2MB8kdDRrWDdLdU5mU1EzZGtTUmhwZXBJclE&amp;mode=public&amp;ddrp=1#" rel="nofollow">this</a> Google bar chart).</p>

<pre><code>  date        pos neg neu  
0  2016-10-08 1    0   1      
1  2016-10-09 1    2   0    
</code></pre>

<p>I tried to go about it by creating a new dataframe</p>

<pre><code>columns = ['date','pos', 'neg', 'neu']

clean_sheet = pd.DataFrame(columns=columns)
</code></pre>

<p>and then iterating over <code>gf</code> looking for unique dates and in turn iterating on those searching for either pos, neg or neu with <code>.loc</code> but it got really messy</p>

<p>Any ideas for a simpler solution?</p>

<p>Thanks</p>

<p>You need add <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unstack.html" rel="nofollow"><code>unstack</code></a>:</p>

<pre><code>gf = df.groupby(["date", "sentiment"]).size().unstack(fill_value=0).reset_index()
#remove column name 'sentiment'
gf.columns.name = None
print (gf)
         date  neg  neu  pos
0  2016-10-08    0    1    1
1  2016-10-09    2    0    1
</code></pre>

<p>Another slowier solution with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a>:</p>

<pre><code>gf = df.pivot_table(index="date", columns="sentiment", aggfunc=len, fill_value=0)
       .reset_index()
gf.columns.name = None
print (gf)
         date  neg  neu  pos
0  2016-10-08    0    1    1
1  2016-10-09    2    0    1
</code></pre>

<p>And last solution is <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.crosstab.html" rel="nofollow"><code>crosstab</code></a>, but in larger <code>DataFrame</code> is slowier:</p>

<pre><code>gf = pd.crosstab( df.date, df.sentiment).reset_index()
gf.columns.name = None
print (gf)
         date  neg  neu  pos
0  2016-10-08    0    1    1
1  2016-10-09    2    0    1
</code></pre>

<p><strong>Timings</strong> (pandas 0.19.0):</p>

<pre><code>#[50000 rows x 2 columns]
df = pd.concat([df]*10000).reset_index(drop=True)

In [197]: %timeit (df.groupby(["date", "sentiment"]).size().unstack(fill_value=0).reset_index())
100 loops, best of 3: 6.3 ms per loop

In [198]: %timeit (df.pivot_table(index="date", columns="sentiment", aggfunc=len, fill_value=0).reset_index())
100 loops, best of 3: 12.2 ms per loop

In [199]: %timeit (pd.crosstab( df.date, df.sentiment).reset_index())
100 loops, best of 3: 11.3 ms per loop
</code></pre>

python pandas dataframe pivot-table reshape