title: Tidy data from multilevel Excel file via pandas
body: <p>I want to produce tidy data from an Excel file which looks like this, with three levels of "merged" headers:</p>
  
  <p><a href="https://i.stack.imgur.com/JqWSu.png" rel="nofollow"><img src="https://i.stack.imgur.com/JqWSu.png" alt="enter image description here"></a>    </p>
  
  <p>Pandas reads the file just fine, with multilevel headers:</p>
  
  <pre><code># df = pandas.read_excel('test.xlsx', header=[0,1,2])
  </code></pre>
  
  <p>For repeatability, you can copy-paste this:</p>
  
  <pre><code>df = pandas.DataFrame({('Unnamed: 0_level_0', 'Unnamed: 0_level_1', 'a'): {1: 'aX', 2: 'aY'}, ('Unnamed: 1_level_0', 'Unnamed: 1_level_1', 'b'): {1: 'bX', 2: 'bY'}, ('Unnamed: 2_level_0', 'Unnamed: 2_level_1', 'c'): {1: 'cX', 2: 'cY'}, ('level1_1', 'level2_1', 'level3_1'): {1: 1, 2: 10}, ('level1_1', 'level2_1', 'level3_2'): {1: 2, 2: 20}, ('level1_1', 'level2_2', 'level3_1'): {1: 3, 2: 30}, ('level1_1', 'level2_2', 'level3_2'): {1: 4, 2: 40}, ('level1_2', 'level2_1', 'level3_1'): {1: 5, 2: 50}, ('level1_2', 'level2_1', 'level3_2'): {1: 6, 2: 60}, ('level1_2', 'level2_2', 'level3_1'): {1: 7, 2: 70}, ('level1_2', 'level2_2', 'level3_2'): {1: 8, 2: 80}})
  </code></pre>
  
  <p>I want to normalise this so that the level headings are in variable rows, but retain columns a, b and c as columns:</p>
  
  <p><a href="https://i.stack.imgur.com/T3PSz.png" rel="nofollow"><img src="https://i.stack.imgur.com/T3PSz.png" alt="Desired output"></a></p>
  
  <p>Without the multi-level headers, I would do <code>pandas.melt(df, id_vars=['a', 'b', 'c'])</code> to get what I want. <code>pandas.melt(df)</code> gives me the three variable columns I want, but obviously doesn't retain the a, b, and c columns. </p>