I have NOT a problem but a behaviour I would like to understand.
I have an Excel with 12 sheets, I want them all combined in 1 dataframe, with the sheetname as extra column. I can do it but have 1 strange behaviour I can't explain : the final order of columns of the dataframe is changed into and alphabetical order.
This is my flow :
I read through 10 sheets of an excel.
Every sheet is read in a dataframe,
all sheets have the same shape (73 rows x 50 columns) and the same hierarchical column. (2 levels).
When a sheet is read in, I append it to a dataframe, then I read the next sheet etc..
The result is a dataframe of 876rows and 50 columns. That is good. But - as mentioned - with a different column order than the original set.
WORKAROUND : I save the order in a list and reapply it at the final dataframe, but that doesn't explain the behaviour.
If I try to replay this with a sample code (see underneath) but I cannot replay the behaviour, my sample code works as expected and preserves the order. Any idea how I can start to investigate this behaviour?
import pandas as pd
import numpy as np
t0=pd.DataFrame()
t1=pd.DataFrame(np.arange(12).reshape((4,3)),
columns=[['one','two','four'],['five','six','eight']])
t1['Month']='January'
t1.set_index(['Month'], inplace=True)
t2=pd.DataFrame(np.arange(12,24).reshape((4,3)),
columns=[['one','two','four'],['five','six','eight']])
t2['Month']='February'
t2.set_index(['Month'], inplace=True)
t3=pd.concat([t0,t1])
t3=pd.concat([t3,t2])
t3