Splitting stacked dataframe in pandas
<p>I have a dataframe like</p>

<pre><code>         age sex      values
time                        
2015      10   F    589628.0
2015      10   M    458390.0
2015      11   F    108018.0
2015      11   M    764350.0
 ....
2000      60   M     34676.0
2000      60   F     45488.0
</code></pre>

<p>I would like to create data frame like</p>

<pre><code>         age   F           M
time                        
2015      10   589628.0  458390.0    
2015      11   
 ....
2000      60   45488.0   34676.0
</code></pre>

<p>reducing the rows by half and adding a column. I have tried to do this with pivot, but no avail.</p>

<pre><code>df.pivot(columns='sex', values='values') 
</code></pre>

<p>but this returns</p>

<pre><code>Index contains duplicate entries, cannot reshape
</code></pre>

<p>Any ideas how can I cleanly split the dataframe without writing a tedious function to do it?</p>

<p>Cheers, Mike</p>

<p>Using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow">pivot_table</a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow">reset_index</a>:</p>

<pre><code>In [17]: df
Out[17]: 
      age sex    values
time                   
2015   10   F  589628.0
2015   10   M  458390.0
2015   11   F  108018.0
2015   11   M  764350.0

In [18]: newdf = df.reset_index().pivot_table('values', ['time', 'age'], 'sex').reset_index()

In [19]: newdf.columns.name = None

In [20]: newdf = newdf.set_index(['time'])

In [21]: newdf
Out[21]: 
      age         F         M
time                         
2015   10  589628.0  458390.0
2015   11  108018.0  764350.0
</code></pre>

python pandas dataframe