Styjun

I have a Excel data in below format

Time A Time B NAME A NAME B NAME C
 Type A Type B Type C
 Celcius Meters Kgs
2019-03-01 00:00:00 2019-02-28 23:59:55.560 8.0285 410.1051 410.5469
2019-03-01 00:00:10 2019-03-01 00:00:05.776 8.0439 410.1051 410.5938
2019-03-01 00:00:20 2019-03-01 00:00:14.995 8.0439 410.2134 410.6875
2019-03-01 00:00:30 2019-03-01 00:00:25.226 8.0439 410.0781 410.5469
2019-03-01 00:00:40 2019-03-01 00:00:35.444 8.0285 410.0239 410.5312
2019-03-01 00:00:50 2019-03-01 00:00:45.676 8.0439 410.1592 410.609

Which i want to convert as pandas dataframe as below

Time A, Time B, Name , Type , Unit , Value 

I tried below code

import pandas as pd
xl = pd.ExcelFile('testx.xlsm')
df = xl.parse(xl.sheet_names[0])
df1 = df.set_index(['Time A', 'Time B'])
df1.columns = [df1.columns,df1.iloc[0], df1.iloc[1]]
df1 = df1.iloc[2:].reset_index(drop=False)
df1.unstack(level=-1)

I tried below code and getting something better but memory intensive.

xl = pd.ExcelFile('test2.xlsm', )
df = xl.parse(xl.sheet_names[0],index_col=[0,1], header=[0,1,2] )
df1 = df.stack().stack().stack()

expected result is like this

Time A Time B name Type Unit Value
2019-03-01 00:00:00 2019-02-28 23:59:55.560 NAME A Type A Celcius 8.0285 
 NAME B Type B Meters 410.1051 
 NAME C Type C Kgs 410.5469

I think this should get you there:

import pandas as pd

arrays = [['Time A', 'Time B', 'NAME A ', 'NAME B','NAME C'], ['', '', 'Type A','Type B','Type C'], ['', '', 'Celcius','Meters','Kgs']]

df.columns = pd.MultiIndex.from_arrays(arrays)
df

Asssuming your current dataframe already has the Excel data (without the headers), your output should be:

 Time A Time B NAME A NAME B NAME C
 Type A Type B Type C
 Celcius Meters Kgs
 0 2019-03-0100:00:00 2019-02-2823:59:55.560 8.0285 410.1051 410.5469

Another efficient solution found is

# Generate Data Frame
def load_file_in_df(fileName, filePath):
 logging.info("Loading file : "+fileName)

 if os.path.isfile(filePath +fileName):
 obj_xl = pd.ExcelFile(filePath + fileName )
 df_excel = obj_xl.parse(obj_xl.sheet_names[0],index_col=[0,1], header=[0,1,2] )
 else:
 print("File does not exists: " +filePath + fileName)
 return df_excel 

# Parse Dataframe 
def parse_10sec_df(df_excel): 
 rows, cols = df_excel.shape
 l_excel = []
 for row in df_excel.itertuples():
 for i in range(cols):
 l = []
 l.append (row[0][0])
 l.append (row[0][1] )

 l.append (df_excel.columns.values[i][0])
 l.append (df_excel.columns.values[i][1])
 l.append (df_excel.columns.values[i][2])
 l.append (row[i+1] )
 l_excel.append(tuple(l))
 #print row[i]
 return l_excel

Above will produce a tuple with below data.

Time A Time B name Type Unit Value
2019-03-01 00:00:00 2019-02-28 23:59:55.560 NAME A Type A Celcius 8.0285 
 NAME B Type B Meters 410.1051 
 NAME C Type C Kgs 410.5469