How to create hierarchical columns in pandas?
<p>I have a pandas dataframe that looks like this:</p>

<pre><code>          rank_2015   num_2015   rank_2014   num_2014   ....  num_2008
France    8           1200       9           1216       ....  1171
Italy     11          789        6           788        ....  654
</code></pre>

<p>Now I want to draw a bar chart of the sums just the <code>num_</code> columns, by year. So on the x-axis I would like years from 2008 to 2015, and on the y-axis I would like the sum of the related <code>num_</code> column. </p>

<p>What's the best way to do this? I know how to get the sums for each column:</p>

<pre><code>df.sum()
</code></pre>

<p>But what I don't know is how to chart only the <code>num_</code> columns, and also how to re-label those columns so that the labels are integers rather than strings, in order to get them to chart correctly. </p>

<p>I'm wondering if I want to create hierarchical columns, like this:</p>

<pre><code>          rank               num
          2015        2014   2015     2014   ....  2008
France    8           9      1200     1216   ....  1171
Italy     11          6      789      788    ....  654
</code></pre>

<p>Then I could just chart the columns in the <code>num</code> section. </p>

<p>How can I get my dataframe into this shape?</p>

<p>You could use <a href="http://pandas.pydata.org/pandas-docs/stable/text.html#extracting-substrings" rel="nofollow"><code>str.extract</code></a> with the regex pattern <code>(.+)_(\d+)</code> to convert the columns
to a DataFrame:</p>

<pre><code>cols = df.columns.str.extract(r'(.+)_(\d+)', expand=True)
#       0     1
# 0   num  2008
# 1   num  2014
# 2   num  2015
# 3  rank  2014
# 4  rank  2015
</code></pre>

<p>You can then <a href="http://pandas.pydata.org/pandas-docs/stable/advanced.html#creating-a-multiindex-hierarchical-index-object" rel="nofollow">build a hierarchical (MultiIndex) index</a> from <code>cols</code> and reassign it
to <code>df.columns</code>:</p>

<pre><code>df.columns = pd.MultiIndex.from_arrays((cols[0], cols[1]))
</code></pre>

<p>so that <code>df</code> becomes</p>

<pre><code>         num             rank     
        2008  2014  2015 2014 2015
France  1171  1216  1200    9    8
Italy    654   788   789    6   11
</code></pre>

<hr>

<pre><code>import pandas as pd
import matplotlib.pyplot as plt

df = pd.DataFrame({ 'num_2008': [1171, 654],
 'num_2014': [1216, 788],
 'num_2015': [1200, 789],
 'rank_2014': [9, 6],
 'rank_2015': [8, 11]}, index=['France', 'Italy'])


cols = df.columns.str.extract(r'(.+)_(\d+)', expand=True)
cols[1] = pd.to_numeric(cols[1])
df.columns = pd.MultiIndex.from_arrays((cols[0], cols[1]))
df.columns.names = [None]*2

df['num'].sum().plot(kind='bar')
plt.show()
</code></pre>

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

python pandas