How to convert pandas dataframe rows into columns, based on category?
<p>I have a pandas data frame with a category variable and some number variables. Something like this: </p>

<pre><code>ls = [{'count':5, 'module':'payroll', 'id':2}, {'count': 53, 'module': 'general','id':2}, {'id': 5,'count': 35, 'module': 'tax'}, ]
df = pd.DataFrame.from_dict(ls)
</code></pre>

<p>The df looks like this: </p>

<pre><code> df
Out[15]: 
   count  id   module
0      5   2  payroll
1     53   2  general
2     35   5      tax
</code></pre>

<p>I want convert(transpose is the right word?) the module variables into columns and group by the id. So something like:</p>

<pre><code>   general_count  id  payroll_count  tax_count
0           53.0   2            5.0        NaN
1            NaN   5            NaN       35.0
</code></pre>

<p>One approach to this would be to use apply:</p>

<pre><code>df['payroll_count'] = df.id.apply(lambda x: df[df.id==x][df.module=='payroll'])
</code></pre>

<p>However, this suffers from multiple drawbacks:</p>

<ol>
<li><p>Costly, and takes too much time   </p></li>
<li><p>Creates artifacts and empty
    dataframes that need to be cleaned up.</p></li>
</ol>

<p>I sense there's a better way to achieve this with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow">pandas groupby</a>, but can't find a way to this same operation more efficiently. Please help. </p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> by columns which first create new <code>index</code> and last <code>column</code>. then need aggreagate some way - I use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.mean.html" rel="nofollow"><code>mean</code></a>, then convert one column <code>DataFrame</code> to <code>Series</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.squeeze.html" rel="nofollow"><code>DataFrame.squeeze</code></a> (then is not necessary remove top level of Multiindex in columns) and reshape by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unstack.html" rel="nofollow"><code>unstack</code></a>. Last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.add_suffix.html" rel="nofollow"><code>add_suffix</code></a> to column name:</p>

<pre><code>df = df.groupby(['id','module']).mean().squeeze().unstack().add_suffix('_count')
print (df)
module  general_count  payroll_count  tax_count
id                                             
2                53.0            5.0        NaN
5                 NaN            NaN       35.0
</code></pre>

<p>Another solution with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.pivot.html" rel="nofollow"><code>pivot</code></a>, then need remove <code>Multiindex</code> from columns by <code>list comprehension</code>:</p>

<pre><code>df = df.pivot(index='id', columns='module')
df.columns = ['_'.join((col[1], col[0])) for col in df.columns]
print (df)
    general_count  payroll_count  tax_count
id                                         
2            53.0            5.0        NaN
5             NaN            NaN       35.0
</code></pre>

python pandas