Pass Every Excel File In Python From Assigning A Specific Name

I have the following excel files in a directory: excel_sheet_01 excel_sheet_02 . . . excel_sheet_nm How can I do using pandas, that every excel sheet gets stored in a dataframe var

Solution 1:

If you do not want to type out every single variable (and especially if you have unknown number of files) you could think of storing the DataFrames in a List (or Dict). something like:

import os
import pandas as pd
excel_sheets = [f.name for f in os.scandir(path) if not f.is_dir() and 'excel_sheet' in f.name]

my_dataframes = []
for f in excel_sheets:
    my_dataframes.append(pd.read_excel(f))

my_dataframes_dict = {}
for f in excel_sheets:
    my_dataframes_dict.update({f: pd.read_excel(f)})

In the case of the list you can access it through the index. In the case of the dictionary you can choose whatever (unique) name you want.