I am taking data from two different excel files as shown below,

df = pd.read_excel('abc.xlsx')

I'm extracting particular data from above data frame named df as shown below,

df1 = df[df['COMPTYPE'].astype(str).str.contains('MCCB|ACB|Contactor', regex=True)]

after that I've another data-frame named df2 as below,

df2 = pd.read_excel('inputfile.xlsx')

The df has 49 rows, df1 and df2 have 31 rows. I'm concatenating the df1 and df2 on axis=1 as below,

data = pd.concat([df2, df1], axis=1)

So here I expected that the final data frame "data" would have 31 rows however I'm getting 43 rows, apparently, df1 has some empty rows that I'm unable to detect and those empty rows are getting added to the final data frame while performing the pd.concat() operation hence the 43 rows instead of 31 rows. Following images will show you how data is in excel file, abc.xlsx file screenshot inputfile screenshot Kindly help.

CodePudding user response：

Try this

newdf = pd.concat([df2.reset_index(drop=True),df1.reset_index(drop=Tru‌​e)], axis=1)