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 """

Answer 2

You should use Python's glob module: https://docs.python.org/3/library/glob.html

For example:

import glob
for path in glob.iglob(directory + "file_*.xlsx"):
    pd.read_excel(path)
    # ...

Answer 3

I would recommend glob.

Doing glob.glob('file_*') returns a list which you can iterate on and do work.

Doing glob.iglob('file_*') returns a generator object which is an iterator.

The first one will give you something like:

['file_1991.xlsx','file_1992.xlsx','file_1995.xlsx','file_1996.xlsx']

Answer 4

If you know how your file names can be constructed, you might try to open a file with the 'r' attribute, so that open(..., 'r') fails if the file is non existent.

yearly_data = {}

for year in range(1990,2018):
    try:
        f = open('file_%4.4d.xlsx'%year, 'r')
    except FileNotFoundError:
        continue # to the next year
    yearly_data[year] = ...
    f.close()