title: Python pandas convert multiple headers in excel file into columns
body: <p>I have been looking around for a way to convert an excel file with multiple headers into column headings using the pandas library.</p>
  
  <p>I have been successful in importing the data into a dataframe by reading the file and parsing it using the ExcelFile. I have also been able to identify the headers using the header=[0, 4]. Where I run into issues is reindexing and/or using the melt function to convert the headers into columns. </p>
  
  <p>When I use the melt function I am able to successfully convert the columns into the rows. However, I want the headers to be a single column rather than be stacked with the rest of the data.</p>
  
  <p>Currently, this is how the data is structured:</p>
  
  <p><a href="https://i.stack.imgur.com/ZJTCz.png" rel="nofollow noreferrer">Excel file displaying data with multiple headers</a></p>
  
  <p>After the conversion, the data should look like this:</p>
  
  <p><a href="https://i.stack.imgur.com/NpWd3.png" rel="nofollow noreferrer">Data that is unpivot with headers converted into columns</a></p>
  
  <p>I have been reading about indexing, but not sure I understand how it would apply here.</p>
  
  <p>I'm new to python, like really new, and any support or direction is greatly appreciated. I have been reading the following cheatsheets but haven't found the right way to convert it:</p>
  
  <p><a href="https://www.datacamp.com/community/data-science-cheatsheets" rel="nofollow noreferrer">https://www.datacamp.com/community/data-science-cheatsheets</a></p>
  
  <p>Here is a sample code:</p>
  
  <pre><code>import pandas as pd
  
  xl = pd.ExcelFile('help.xlsx')
  df1 = xl.parse('Sheet1')
  
  df2 = pd.melt(df1,
            id_vars=['PW'],
            value_vars=['Fruit','Conventional'])
  </code></pre>
  
  <p>Also, adding the results after running the code:
  <a href="https://i.stack.imgur.com/cI1TN.png" rel="nofollow noreferrer">df1 the data with multiple headers</a></p>
  
  <p>The following is the error with the data (headers are not converted into columns, headers are stacked with the rest of the data):</p>
  
  <p><a href="https://i.stack.imgur.com/SGKMl.png" rel="nofollow noreferrer">after using pandas melt the headers are stacked with the data and not converted into their own column</a></p>
  
  <p>This is how the final product should look:</p>
  
  <p><a href="https://i.stack.imgur.com/Za3wM.png" rel="nofollow noreferrer">Headers converted into columns</a></p>