How to read multiple excel files in Different Pandas Dataframes

Question

I have collection of excel files containing similar datasets. I want it to be read by different Pandas dataframes.

import glob
import pandas as pd

path=r"C:users/me/desktop/ExcelData"

files=glob.glob('*.xls')
for f in files:
      df[f]=pd.read_excel(f)

create a list(say lst=[]) outside the for loop and then append your dataframe in it.....then access your dataframe by lst[0],lst[1].....so where you stucked? — Anurag Dabas
– Anurag Dabas, Commented Jul 21, 2021 at 5:41

mac_online · Accepted Answer · 2021-07-21 06:42:26Z

2

import glob
import pandas as pd
import os

path=r"C:\\users\\me\\desktop\\ExcelData\\"
csv_files = glob.glob(os.path.join(path, "*.xls"))

dfl=[]
for f in csv_files:
    x= pd.read_excel(f)
    dfl.append(x)

edited Jul 21, 2021 at 6:42

answered Jul 21, 2021 at 6:01

mac_online

3701 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9003011 Over a year ago

I executed the code. However, df_list is empty. Please note that 'files' variable is non-empty and contains 80 xls files

mac_online Over a year ago

made an edit! make sure that you are able to read the data into your code(made changes to the file path as per windows)

JJ Moradel · Accepted Answer · 2021-07-21 11:22:38Z

0

import pandas as pd
import os
import glob

path = r"C:users/me/desktop/ExcelData"
files = glob.glob(path + "\*.xls")

finalexcelsheet = pd.DataFrame()

for file in files:
    df = pd.concat(pd.read_excel(file, sheet_name = None), ignore_index=True, 
    sort=False)
    finalexcelsheet = finalexcelsheet.append(df, ignore_index = True)

print(finalexcelsheet)

answered Jul 21, 2021 at 11:22

JJ Moradel

193 bronze badges

2 Comments

user9003011 Over a year ago

Ultimately, I wanted Excel output, but not this way. It creates many NaN values. In between different Pandas operations are involved. Now I am done. Math operations, Dataframe operations including horizontal concat are involved.

JJ Moradel Over a year ago

for this solution, I assumed that all the Data Frames had the same columns. If this is that is the case the code will work smoothly.

Joaquim · Accepted Answer · 2024-06-05 15:10:32Z

0

This reads multiple excel files into a directory into separate dataframes. The dataframes are named after the source excel file name.

upload_path = "../test_files/sample"
files = os.listdir(upload_path)
files_xls = [f for f in files if f[-3:] == 'xls']
for f in files_xls:
    dataframe_name = f[:-4]
    dataframe_name = pd.read_excel(os.path.join(upload_path,f), header=1)

answered Jun 5, 2024 at 15:10

Joaquim

4506 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to read multiple excel files in Different Pandas Dataframes

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related