Python iterate over multiple files

Question

I have a series of files that are in the following format:

file_1991.xlsx
file_1992.xlsx
# there are some gaps in the file numbering sequence
file_1995.xlsx
file_1996.xlsx
file_1997.xlsx

For each file I want to do something like:

import pandas as pd
data_1995 = pd.read_excel(open(directory + 'file_1995', 'rb'), sheetname = 'Sheet1')

do some work on the data, and save it as another file:

output_1995 = pd.ExcelWriter('output_1995.xlsx')
data_1995.to_excel(output_1995,'Sheet1')

Instead of doing all these for every single file, how can I iterate through multiple files and repeat the same operation across multiple files? In other words, I would like to iterate over all the files (they mostly following a numerical sequence in their names, but there are some gaps in the sequence).

Thanks for the help in advance.

Answer 1

You can use os.listdir or glob module to list all files in a directory.

With os.listdir, you can use fnmatch to filter files like this (can use a regex too);

import fnmatch
import os

for file in os.listdir('my_directory'):
    if fnmatch.fnmatch(file, '*.xlsx'):
        pd.read_excel(open(file, 'rb'), sheetname = 'Sheet1')
        """ Do your thing to file """

Or with glob module (which is a shortcut for the fnmatch + listdir) you can do the same like this (or with a regex):

import glob
for file in glob.glob("/my_directory/*.xlsx"):
    pd.read_excel(open(file, 'rb'), sheetname = 'Sheet1')
    """ Do your thing to file """