Extract data from multiple excel files in multiple directories in python pandas

Question

I am new to Python and I am posting the question in stack overflow for the first time. Please help in solving the problem.

My main directory is 'E:\Data Science\Macros\ZBILL_Dump', containing month-wise folders and each folder contains date-wise excel data.

I was able to extract data from a single folder:

import os
import pandas as pd
import numpy as np

# Find file names in the specified directory
loc = 'E:\Data Science\Macros\ZBILL_Dump\Apr17\\'
files = os.listdir(loc)

# Find the ONLY Excel files 
files_xlsx = [f for f in files if f[-4:] == 'xlsx']

# Create empty dataframe and read in new data
zbill = pd.DataFrame()
for f in files_xlsx:
    New_data = pd.read_excel(os.path.normpath(loc + f), 'Sheet1')
    zbill = zbill.append(New_data)

zbill.head()

I am trying to extract data from my main directory i.e "ZBILL_Dump" which contains many sub folders, but I could not do it. Please somebody help me.

Thanks a lot.

Andrew · Accepted Answer · 2017-09-06 14:15:53Z

4

You can use glob.

import glob
import pandas as pd

# grab excel files only
pattern = 'E:\Data Science\Macros\ZBILL_Dump\Apr17\\*.xlsx'

# Save all file matches: xlsx_files
xlsx_files = glob.glob(pattern)

# Create an empty list: frames
frames = []

#  Iterate over csv_files
for file in xlsx_files:

    #  Read xlsx into a DataFrame
    df = pd.read_xlsx(file)

    # Append df to frames
    frames.append(df)

# Concatenate frames into dataframe
zbill = pd.concat(frames)

You can use regex if you want to look in different sub-directories. Use 'filepath/*/*.xlsx' to search the next level. More info here https://docs.python.org/3/library/glob.html

edited Sep 6, 2017 at 14:15

answered Sep 6, 2017 at 14:04

Andrew

9708 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Debayan Over a year ago

@keshava please confirm if this answer is correct

jack6e · Accepted Answer · 2017-09-06 14:06:51Z

1

Use glob with its recursive feature for searching sub-directories:

import glob
files = glob.glob('E:\Data Science\Macros\ZBILL_Dump\**\*.xlsx', recursive=True)

Docs: https://docs.python.org/3/library/glob.html

answered Sep 6, 2017 at 14:06

jack6e

1,51211 silver badges12 bronze badges

Collectives™ on Stack Overflow

Extract data from multiple excel files in multiple directories in python pandas

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related