I have an excel sheet with multiple header like:
_________________________________________________________________________
____|_____| Header1 | Header2 | Header3 |
ColX|ColY |ColA|ColB|ColC|ColD||ColD|ColE|ColF|ColG||ColH|ColI|ColJ|ColDK|
1 | ds | 5 | 6 |9 |10 | .......................................
2 | dh | ..........................................................
3 | ge | ..........................................................
4 | ew | ..........................................................
5 | er | ..........................................................
Now here you can see that first two columns do not have headers they are blank but other columns have headers like Header1, Header2 and Header3. So I want to read this sheet and merge it with other sheet with similar structure.
I want to merge it on first column 'ColX'. Right now I am doing this:
import pandas as pd
totalMergedSheet = pd.DataFrame([1,2,3,4,5], columns=['ColX'])
file = pd.ExcelFile('ExcelFile.xlsx')
for i in range (1, len(file.sheet_names)):
df1 = file.parse(file.sheet_names[i-1])
df2 = file.parse(file.sheet_names[i])
newMergedSheet = pd.merge(df1, df2, on='ColX')
totalMergedSheet = pd.merge(totalMergedSheet, newMergedSheet, on='ColX')
But I don't know its neither reading columns correctly and I think will not return the results in the way I want. So, I want the resulting frame should be like:
________________________________________________________________________________________________________
____|_____| Header1 | Header2 | Header3 | Header4 | Header5 |
ColX|ColY |ColA|ColB|ColC|ColD||ColD|ColE|ColF|ColG||ColH|ColI|ColJ|ColK| ColL|ColM|ColN|ColO||ColP|ColQ|ColR|ColS|
1 | ds | 5 | 6 |9 |10 | ..................................................................................
2 | dh | ...................................................................................
3 | ge | ....................................................................................
4 | ew | ...................................................................................
5 | er | ......................................................................................
Any suggestions please. Thanks.