title: Handling multiple column headers and same column names in csv - pandas/python
body: <p>I have a csv file that looks like this </p>
  
  <pre><code>        PROD1   PROD1   PROD2   PROD2
          X         Y       X       Y
  AA  A   1         2       9       10
  BB  B   3         4       11      12
  CC  C   5         6       13      14
  DD  D   7         8       15      16
  </code></pre>
  
  <p>The output I am trying to get has to look like this </p>
  
  <pre><code>                X   Y
  AA  A   PROD1   1   2
  BB  B   PROD1   3   4
  CC  C   PROD1   5   6
  DD  D   PROD1   7   8
  AA  A   PROD2   9   10
  BB  B   PROD2   11  12
  CC  C   PROD2   13  14
  DD  D   PROD2   15  16
  </code></pre>
  
  <p>I tried transposing the csv read with</p>
  
  <pre><code>data=pd.read_csv('transposedata.csv', header=None).T
  </code></pre>
  
  <p>But then I lose column info. I also tried this from another solution provided here at stackoverflow</p>
  
  <pre><code>df = pd.read_csv('transposedata.csv', header=[0,1])
  a = df.columns.get_level_values(0).to_series()
  b = a.mask(a.str.startswith('Unnamed')).ffill().fillna('')
  df.columns = [b, df.columns.get_level_values(1)]
  </code></pre>
  
  <p>I end up with </p>
  
  <pre><code>                                           PROD1    PROD2    
    Unnamed: 0_level_1 Unnamed: 1_level_1     X  Y     X   Y
  0                 AA                  A     1  2     9  10
  1                 BB                  B     3  4    11  12
  2                 CC                  C     5  6    13  14
  3                 DD                  D     7  8    15  16
  </code></pre>
  
  <p>Any Help?</p>
  
  <p>update 
  when I run the solution given </p>
  
  <pre><code>data=pd.read_csv('transposedata1.csv', header=[0,1]).stack(level=0).sort_index(level=1)
  </code></pre>
  
  <p>i get this</p>
  
  <pre><code>        Unnamed:0_level_1   Unnamed:1_level_1   X   Y
  0   PROD1   NaN NaN 1   2
  1   PROD1   NaN NaN 3   4
  2   PROD1   NaN NaN 5   6
  3   PROD1   NaN NaN 7   8
  0   PROD2   NaN NaN 9   10
  1   PROD2   NaN NaN 11  12
  2   PROD2   NaN NaN 13  14
  3   PROD2   NaN NaN 15  16
  0   Unnamed:0_level_0   AA  NaN NaN NaN
  1   Unnamed:0_level_0   BB  NaN NaN NaN
  2   Unnamed:0_level_0   CC  NaN NaN NaN
  3   Unnamed:0_level_0   DD  NaN NaN NaN
  0   Unnamed:1_level_0   NaN A   NaN NaN
  1   Unnamed:1_level_0   NaN B   NaN NaN
  2   Unnamed:1_level_0   NaN C   NaN NaN
  3   Unnamed:1_level_0   NaN D   NaN NaN
  </code></pre>
  
  <p>Thanks</p>