[FIXED] How to run a loop to concatenate columns of multiple excel files(as separate dataframes) in a folder and merge and export into final dataframe

Issue

So basically , I want to run a loop to first import multiple excel files in a folder and convert them to separate data frames . Then I want to run a loop to concatenate specific columns of each data frame and merge the new data frames created as one . I tried a code but its not looping all the files in the folder .

import glob
import pandas as pd
import os

x=input("enter file")
df=pd.read_excel(x)
df["Combin']=df.Pcode.str.cat(df.Icode)


filenames= glob.glob(r'C:\Desktop\*.xlsx')
for idx, fname in enumerate(filenames):
 df2=pd.read_excel(fname,sheet_name="PI",skiprows=4)
 df2[["Combin"]=df2.Pcode.str.cat(df2.Icode)
 merged=df.merge(df2,left_on='Combin', right_on='Combin', how='inner')
 df3=pd.read_excel(fname,sheet_name='PI')
 exc=df3.iat[0,19]
 merged ['Exchange']=exc
final=[[merged]]
excel_merged=pd.concat(final, ignore_index=True)
excel_merged.to_excel('output.xlsx')

Solution

Create empty list before loop and then use append for create list of DataFrames:

filenames= glob.glob(r'C:\Desktop\*.xlsx')

final = []
for idx, fname in enumerate(filenames):
    df2=pd.read_excel(fname,sheet_name="PI",skiprows=4)
    df2[["Combin"]=df2.Pcode.str.cat(df2.Icode)
    merged=df.merge(df2,left_on='Combin', right_on='Combin', how='inner')
    df3=pd.read_excel(fname,sheet_name='PI')
    exc=df3.iat[0,19]
    merged ['Exchange']=exc
    final.append(merged)
    
excel_merged=pd.concat(final, ignore_index=True)
excel_merged.to_excel('output.xlsx')

Answered By - jezrael