Setting a row index on and querying a pandas dataframe with multi-index columns
<p>Starting from a <code>pandas</code> dataframe with a multi-dimensional column heading structure such as the following, is there a way I can transform the <code>Area Names</code> and <code>Area Codes</code> headings so they span each level (i.e. so single <code>Area Names</code> and <code>Area Codes</code> labels spanning the multiple column heading rows?</p>

<p><a href="http://i.stack.imgur.com/6MrXt.png" rel="nofollow"><img src="http://i.stack.imgur.com/6MrXt.png" alt="Multidimensional column heading table"></a></p>

<p>If so, how could I then run a query on the column to just return the row corresponding to a particular value (e.g. an <em>Area Code</em> of <em>E06000047</em>), or the <em>Low</em> and <em>Very High</em> values for <em>ENGLAND</em> in <em>2012/13</em>?</p>

<p>I wonder if it would be easier to define a row index based on either <em>Area Code</em> or <em>Area Names</em>, or a two column row index <code>['*Area Code*', '*Area Names*']</code>. And if so, how can I do this from the current table? <code>set_index</code> seems to balk at this using the current structure?</p>

<p>Code fragment to create the above:</p>

<pre><code>import pandas as pd

df= pd.DataFrame({('2011/12*', 'High', '7-8'): {3: 49.83,
  5: 50.01,
  7: 48.09,
  8: 43.58,
  9: 44.19},
 ('2011/12*', 'Low', '0-4'): {3: 6.51, 5: 6.53, 7: 6.49, 8: 6.41, 9: 6.12},
 ('2011/12*', 'Medium', '5-6'): {3: 17.44,
  5: 17.59,
  7: 18.11,
  8: 19.23,
  9: 20.01},
 ('2011/12*', 'Very High', '9-10'): {3: 26.22,
  5: 25.87,
  7: 27.32,
  8: 30.78,
  9: 29.68},
 ('2012/13*', 'High', '7-8'): {3: 51.16,
  5: 51.35,
  7: 48.47,
  8: 44.67,
  9: 49.39},
 ('2012/13*', 'Low', '0-4'): {3: 5.71, 5: 5.74, 7: 6.73, 8: 8.42, 9: 6.51},
 ('2012/13*', 'Medium', '5-6'): {3: 17.1,
  5: 17.29,
  7: 18.46,
  8: 20.23,
  9: 15.81},
 ('2012/13*', 'Very High', '9-10'): {3: 26.03,
  5: 25.62,
  7: 26.34,
  8: 26.68,
  9: 28.3},
 ('Area Codes', 'Area Codes', 'Area Codes'): {3: 'K02000001',
  5: 'E92000001',
  7: 'E12000001',
  8: 'E06000047',
  9: 'E06000005'},
 ('Area Names', 'Area Names', 'Area Names'): {3: 'UNITED KINGDOM',
  5: 'ENGLAND',
  7: 'NORTH EAST',
  8: 'County Durham',
  9: 'Darlington'}})
</code></pre>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a> with tuples for set by <code>MultiIndex</code>:</p>

<pre><code>df.set_index([('Area Codes','Area Codes','Area Codes'),
              ('Area Names','Area Names','Area Names')], inplace=True)
df.index.names = ['Area Codes','Area Names']
print (df)
                          2011/12*                        2012/13*        \
                              High   Low Medium Very High     High   Low   
                               7-8   0-4    5-6      9-10      7-8   0-4   
Area Codes Area Names                                                      
K02000001  UNITED KINGDOM    49.83  6.51  17.44     26.22    51.16  5.71   
E92000001  ENGLAND           50.01  6.53  17.59     25.87    51.35  5.74   
E12000001  NORTH EAST        48.09  6.49  18.11     27.32    48.47  6.73   
E06000047  County Durham     43.58  6.41  19.23     30.78    44.67  8.42   
E06000005  Darlington        44.19  6.12  20.01     29.68    49.39  6.51   


                          Medium Very High  
                             5-6      9-10  
Area Codes Area Names                       
K02000001  UNITED KINGDOM  17.10     26.03  
E92000001  ENGLAND         17.29     25.62  
E12000001  NORTH EAST      18.46     26.34  
E06000047  County Durham   20.23     26.68  
E06000005  Darlington      15.81     28.30 
</code></pre>

<p>Then need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_index.html" rel="nofollow"><code>sort_index</code></a>, because:</p>

<blockquote>
  <p>KeyError: 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (0)'</p>
</blockquote>

<pre><code>df.sort_index(inplace=True)
</code></pre>

<p>Last use selecting by <a href="http://pandas.pydata.org/pandas-docs/stable/advanced.html#using-slicers" rel="nofollow">slicers</a>:</p>

<pre><code>idx = pd.IndexSlice
print (df.loc[idx['E06000047',:], :])

                        2011/12*                        2012/13*        \
                             High   Low Medium Very High     High   Low   
                              7-8   0-4    5-6      9-10      7-8   0-4   
Area Codes Area Names                                                     
E06000047  County Durham    43.58  6.41  19.23     30.78    44.67  8.42   


                         Medium Very High  
                            5-6      9-10  
Area Codes Area Names                      
E06000047  County Durham  20.23     26.68  
</code></pre>

<hr>

<pre><code>print (df.loc[idx[:,'ENGLAND'], idx['2012/13*',['Low','Very High']]])
                      2012/13*          
                           Low Very High
                           0-4      9-10
Area Codes Area Names                   
E92000001  ENGLAND        5.74     25.62
</code></pre>

python pandas indexing multiple-columns multi-index