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="https://i.stack.imgur.com/6MrXt.png" rel="nofollow noreferrer"><img src="https://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>