title: Unpivot/Flatten pandas pivot table into one level index
body: <p>For an analysis I start of with a dataframe, that looks like this (but is much larger):</p>
  
  <pre><code>      ID1        ID2           type       Number
  0     IE345      E90              I           38
  1     IE345      E92              E           26
  2     IE345      E93              E           21
  3     IE345      E95              R            9
  4     IE346      E94              I           41
  7     IE346      BLK              E            1
  </code></pre>
  
  <p>I would like to unpivot the following dataframe:</p>
  
  <blockquote>
  <pre><code>Df1 = pd.pivot_table(loads, values=['Number'], 
                         index = ['ID1', 'ID2'], 
                         columns=['Type'], margins=True, 
                         aggfunc=[sum] , fill_value=0)
  </code></pre>
  </blockquote>
  
  <p>Df1:</p>
  
  <pre><code>                               sum                                                    
                         Number                                                             
  type                             B     D     E     I    L     R    All                         
  ID1        ID2                                                                         
  IE345      E90                   0     0     0    38    0     0     38    
             E92                   0     0    26     0    0     0     26    
             E93                   0     0    21     0    0     0     21    
             E95                   0     0     0     0    0     9      9    
  IE346      E94                   0     0     0    41    0     0     41    
             BLK                   0     0     1     0    0     0      1  
  </code></pre>
  
  <p>Into:</p>
  
  <p>Df1: </p>
  
  <pre><code>ID1        ID2                   B     D     E     I    L     R    All                                                                         
  IE345      E90                   0     0     0    38    0     0     38    
  IE345      E92                   0     0    26     0    0     0     26    
  IE345      E93                   0     0    21     0    0     0     21    
  IE345      E95                   0     0     0     0    0     9      9    
  IE346      E94                   0     0     0    41    0     0     41    
  IE346      BLK                   0     0     1     0    0     0      1
  </code></pre>
  
  <p>It seems that pandas.melt is what i am looking for, but cant manage to make it work.  </p>
  
  <p>Afterwards I would like to add the column name of highest value of a row, but without the margins of course:</p>
  
  <pre><code>ID1        ID2                   B     D     E     I    L     R    All   Max                                                                        
  IE345      E90                   0     0     0    38    0     0     38    I    
  IE345      E92                   0     0    26     0    0     0     26    E
  IE345      E93                   0     0    21     0    6     0     27    E
  IE345      E95                   0     0     0     0    0     9      9    R
  IE345      E94                   0     0     0    41    0     0     41    I
  IE345      BLK                   0     0     1     0    1     0      2    E
  </code></pre>
  
  <p>For the max value, I used:</p>
  
  <pre><code>df['Max'] = df.idxmax(axis=1, skipna=True)
  </code></pre>
  
  <p>but this takes the All with it unfortunately. Any ideas on how I can achieve my goal the most efficient way?</p>
  
  <p><strong>!!!EDIT!!!!</strong></p>
  
  <p>For the first part, I constructed a solution which returns exacte a unpivot pivot table! See here the code:</p>
  
  <pre><code>df.columns = df.columns.get_level_values('Type')
  df.reset_index(inplace=True)
  </code></pre>
  
  <p>Now I tried the solution of Vmg for the Max value but unfortunately it returns:</p>
  
  <pre><code>ValueError: could not convert string to float: 
  </code></pre>
  
  <p>Any ideas on how to fix this?</p>