title: Pandas concatenate levels in multiindex
body: <p>I do have following excel file:</p>
  
  <p><a href="https://i.stack.imgur.com/028Yd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/028Yd.png" alt="enter image description here"></a></p>
  
  <pre><code>{0: {0: nan, 1: nan, 2: nan, 3: 'A', 4: 'A', 5: 'B', 6: 'B', 7: 'C', 8: 'C'},
   1: {0: nan, 1: nan, 2: nan, 3: 1.0, 4: 2.0, 5: 1.0, 6: 2.0, 7: 1.0, 8: 2.0},
   2: {0: 'AA1', 1: 'a', 2: 'ng/mL', 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1},
   3: {0: 'AA2', 1: 'a', 2: nan, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1},
   4: {0: 'BB1', 1: 'b', 2: nan, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1},
   5: {0: 'BB2', 1: 'b', 2: 'mL', 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1},
   6: {0: 'CC1', 1: 'c', 2: nan, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1},
   7: {0: 'CC2', 1: 'c', 2: nan, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1}}
  </code></pre>
  
  <p>I would like to create following dataframe:</p>
  
  <pre><code>level_0     AA1   AA2   CB1  BB2   CC1   CC2
  new     a ng/mL a N/A b N/A b mL c N/A c N/A
  0 1                                         
  A 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  B 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  C 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  </code></pre>
  
  <p>What I tried:</p>
  
  <pre><code># read the column index separately to avoid pandas inputting "Unnamed: ..."
  # for the nans
  df = pd.read_excel(file_path, skiprows=3, index_col=None, header=None)
  df.set_index([0, 1], inplace=True)
  
  # the column index
  cols = pd.read_excel(file_path, nrows=3, index_col=None, header=None).loc[:, 2:]
  cols = cols.fillna('N/A')
  idx = pd.MultiIndex.from_arrays(cols.values)
  df.columns = idx
  </code></pre>
  
  <p>The new dataframe:</p>
  
  <pre><code>      AA1 AA2 CB1 BB2 CC1 CC2
          a   a   b   b   c   c
      ng/mL N/A N/A  mL N/A N/A
  0 1                          
  A 1     1   1   1   1   1   1
    2     1   1   1   1   1   1
  B 1     1   1   1   1   1   1
    2     1   1   1   1   1   1
  C 1     1   1   1   1   1   1
    2     1   1   1   1   1   1
  </code></pre>
  
  <p>This approach works but is kind of tedious:</p>
  
  <pre><code>df1 = df.T.reset_index()
  df1['new'] = df1.loc[:, 'level_1'] + ' ' + df1.loc[:, 'level_2']
  df1.set_index(['level_0', 'new']).drop(['level_1', 'level_2'], axis=1).T
  </code></pre>
  
  <p>Which gives me:</p>
  
  <pre><code>level_0     AA1   AA2   CB1  BB2   CC1   CC2
  new     a ng/mL a N/A b N/A b mL c N/A c N/A
  0 1                                         
  A 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  B 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  C 1           1     1     1    1     1     1
    2           1     1     1    1     1     1
  </code></pre>
  
  <p>Is there a simpler solution available ?</p>