reindex some DataFrame columns to multi index
<p>At some point in my workflow I end up with a regular pandas DataFrame with some columns and some rows. I want to export this DataFrame into a latex table,using <code>df.to_latex()</code>. This worked great, however, I know want to use multicolumn where some columns are part of a multi table. For instance a DataFrame with columns a,b,c,d,e I would want to leave column a as it is, but group up b and c, as well as d and e.</p>

<pre><code>import numpy as np
import pandas as pd

# where I am
data = np.arange(15).reshape(3, 5)
df = pd.DataFrame(data=data, columns=['a', 'b', 'c', 'd', 'e'])
</code></pre>

<p>It looks like this:</p>

<pre><code>In [161]: df
Out[161]: 
    a   b   c   d   e
0   0   1   2   3   4
1   5   6   7   8   9
2  10  11  12  13  14
</code></pre>

<p>I would like to group columns b and c, as well as d and e, but leave a alone. So my desired output should look like this.</p>

<pre><code># where I want to be: leave column 'a' alone, group b&amp;c as well as d&amp;e
multi_index = pd.MultiIndex.from_tuples([
    ('a', ''),
    ('bc', 'b'),
    ('bc', 'c'),
    ('de', 'd'),
    ('de', 'e'),
])
desired = pd.DataFrame(data, columns=multi_index)
</code></pre>

<p>It looks like this:</p>

<pre><code>In [162]: desired
Out[162]: 
    a  bc      de    
        b   c   d   e
0   0   1   2   3   4
1   5   6   7   8   9
2  10  11  12  13  14
</code></pre>

<p>In order to get there, i tried a simple reindex. This give me the desired shape, but all columns only got NaN as value.</p>

<pre><code># how can use df and my multiindexreindex to multi column DataFrame
result = df.reindex(columns=multi_index)
</code></pre>

<p>The result looks like described, correct indices but all NaN</p>

<pre><code>In [166]: result
Out[166]: 
    a  bc      de    
        b   c   e   e
0 NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN
</code></pre>

<p>How can I get my desired result?</p>

<pre><code>pd.concat([df.set_index('a')[['b', 'c']],
           df.set_index('a')[['d', 'e']]],
          axis=1, keys=['bc', 'de']).reset_index(col_level=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/oOFSv.png" rel="nofollow"><img src="http://i.stack.imgur.com/oOFSv.png" alt="enter image description here"></a></p>

python pandas dataframe multi-index reindex