Pandas reset header, move header to first row

Question

Suppose we have a pandas dataframe that we want to export to excel, but we cannot have multiindex as that is not supported yet:

import pandas as pd

df = pd.DataFrame(
        np.random.uniform(size=(10,5)),
        columns=["col"+str(i) for i in range(1,6)],
        index=["idx"+str(i) for i in range(1,11)]
    )
>>
           col1      col2      col3      col4      col5
idx1   0.953784  0.175881  0.370008  0.479071  0.081742                                                              idx2   0.547507  0.361186  0.451369  0.455099  0.819528
idx3   0.816272  0.257212  0.490702  0.706058  0.346036
idx4   0.436022  0.494841  0.634315  0.646496  0.093829  
idx5   0.765325  0.300295  0.229381  0.784400  0.940571    
idx6   0.087756  0.581965  0.511828  0.169234  0.590827    
idx7   0.709540  0.624182  0.514139  0.496215  0.273366   
idx8   0.432376  0.363550  0.831930  0.378873  0.224397      
idx9   0.060186  0.222222  0.269385  0.269597  0.467292    
idx10  0.841990  0.433233  0.555088  0.382026  0.802151

and we would like to add the header to first row of the dataframe or "reset" the header. eg. obtain the following dataframe after resetting:

df.reset_header()
>>
              0         1         2         3         4        
index      col1      col2      col3      col4      col5      
idx1   0.953784  0.175881  0.370008  0.479071  0.081742    
idx2   0.547507  0.361186  0.451369  0.455099  0.819528    
idx3   0.816272  0.257212  0.490702  0.706058  0.346036      
idx4   0.436022  0.494841  0.634315  0.646496  0.093829     
idx5   0.765325  0.300295  0.229381    0.7844  0.940571      
idx6   0.087756  0.581965  0.511828  0.169234  0.590827    
idx7    0.709547  0.624182  0.514139  0.496215  0.273366     
idx8   0.432376   0.36355   0.83193  0.378873  0.224397    
idx9   0.060186  0.222222  0.269385  0.269597  0.467292     
idx10   0.841992  0.433233  0.555088  0.382026  0.802151

jezrael · Accepted Answer · 2022-09-05 08:40:52Z

1

Create MutliIndex and assign back to columns names:

df.columns = [np.arange(len(df.columns)), df.columns]
print (df)
              0         1         2         3         4
           col1      col2      col3      col4      col5
idx1   0.568617  0.596795  0.475788  0.737513  0.238540
idx2   0.894024  0.442055  0.673552  0.410094  0.759784
idx3   0.288629  0.783821  0.528549  0.813181  0.115838
idx4   0.819945  0.835391  0.514075  0.777364  0.410915
idx5   0.589271  0.431179  0.112365  0.242604  0.381046
idx6   0.886472  0.066028  0.514547  0.265788  0.886736
idx7   0.849599  0.062599  0.559528  0.651613  0.906593
idx8   0.198612  0.263205  0.890967  0.283771  0.578805
idx9   0.388140  0.522279  0.113065  0.505676  0.743253
idx10  0.600133  0.785075  0.903343  0.960463  0.252953

answered Sep 5, 2022 at 8:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Chemnitz-Thomsen · Accepted Answer · 2022-09-05 09:16:38Z

Simply create a new function. This is workaround multi-index in the case you want to save a dataframe to excel.

import pandas as pd

pd.DataFrame.reset_header = lambda df : df.swapaxes(0,1).reset_index().swapaxes(0,1)

df.reset_header()
>>
              0         1         2         3         4        
index      col1      col2      col3      col4      col5      
idx1   0.953784  0.175881  0.370008  0.479071  0.081742    
idx2   0.547507  0.361186  0.451369  0.455099  0.819528    
idx3   0.816272  0.257212  0.490702  0.706058  0.346036      
idx4   0.436022  0.494841  0.634315  0.646496  0.093829     
idx5   0.765325  0.300295  0.229381    0.7844  0.940571      
idx6   0.087756  0.581965  0.511828  0.169234  0.590827    
idx7    0.70954  0.624182  0.514139  0.496215  0.273366     
idx8   0.432376   0.36355   0.83193  0.378873  0.224397    
idx9   0.060186  0.222222  0.269385  0.269597  0.467292     
idx10   0.84199  0.433233  0.555088  0.382026  0.802151

Collectives™ on Stack Overflow

Pandas reset header, move header to first row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related