Pandas read multiple excel sheets with a pattern name

user_13118213 0
Viewed 872

I am using pandas and I am trying to read an excel file with multiple sheets.

pd.read_excel('PATH', sheet_name=)

I only want to read the sheets with the following pattern: An nnnn, where n is a digit number. Additionally this file will be updated in the future, so writing the sheetnames one by one is not a good option.

Is it possible, and if yes, how to read multiple excel sheets with the pattern name I described?

pandas python
2 Answers

You can first get a list of the excel sheet names using the ExcelFile class (and the sheet_names attribute):

xl = pd.ExcelFile('foo.xlsx')

xl.sheet_names  # see all sheet names

Once you have that you can select from there the sheets that match your pattern:

import re
import pandas as pd

dataframes = []
for sheet in xl.sheet_names:
    if re.match('A\d \d{4}', sheet):  # when matching pattern add the dataframe to the list
        dataframes.append(pd.read_excel('foo.xlsx', sheet_name=sheet))

You will have all dataframes in a list and can continue your code from there

user_103102730

You can first search for all files that match a regex pattern, then load each file in with pandas.

from pathlib import Path
import re


directory = Path('your/directory/of/csvs/')

file_list = []
for x in directory.iterdir():
   if re.match('A\d \d{3}', x.name):
    pd.read_excel('PATH', sheet_name=x.name)

Note, I havent tested that regex.

user_63720420
Related