How to import multiple excel files and manipulate them individually

Question

I have to analyze 13 different Excel files and I want to read them al in Jupyter at once, instead of reading them al individually. Also I want to be able to acces the contents individually. So far I have this:

path = r"C:\Users\giova\PycharmProjects\DAEB_prijzen\data"
filenames = glob.glob(path + "\*.xlsx")

df_list = []
for file in filenames:
    df = pd.read_excel(file, usecols=['Onderhoudsordernr.', 'Oorspronkelijk aantal', 'Bedrag (LV)'])
    print(file)
    print(df)
    df_list.append(df)

When I'm running the code it seems to be like 1 big list, with some data missing, which I dont want. Can anyone help? :(

Answer 1

It seems a problem that can be solved with a for loop and a dictionary.

Read the path location of your files:

path = 'C:/your path'
paths = os.listdir(path)

Initialize an empty dictionary:

my_files = {}

for i, p in enumerate(paths):
    my_files[i] = pd.read_excel(p)

Then you can acces to your files individually simply calling the key in the dictionary:

my_files[i]

Where i = 1, 2 ..., 13

Alternatively, if you want to assign a name to each file, you can either create a list of name or derive it from the filepath through some slice/regex function on the strings. Assuming the first case:

names = ['excel1', ...]

for name, p in zip(names, paths):
    my_files[name] = pd.read_excel(p)