Reputation: 126
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
Upvotes: 0
Views: 1948
Reputation: 126
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
Upvotes: 0
Reputation: 862511
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
Upvotes: 1