Import multiple excel sheets from different files into python and concatenate them into one dataframe

Question

I am trying to combine multiple sheets from different excel files into one dataframe. All the files have multiple sheets, and one of these sheets has the same name in all the files - this is the sheet that I am interested in combining into one dataframe. All the files are in the same directory.

import pandas as pd
import os, glob
os.chdir(r'c:\Users\Documents\Files')

def files():                                 #to select the files that have RMP and WE on their name
    list_files= pd.Series()
    for file in glob.glob('RMP*WE*'):
        data= pd.Series(file)
        list_files= list_files.append(data, ignore_index=True)
    return list_files
a= files()
print("This is the variable a\n", a)

def extract_tab():                       #to concatenate the sheet called Metrics that all files have
    frame_files= pd.DataFrame()
    try:
        for file in a:
            data= pd.read_excel(file,sheet_name='Metrics')
            frame_files= frame_files.append(data, ignore_index=True)
    except:
        pass
    return  frame_files

b= extract_tab()

print("This is b\n",b)

The result of variable a (files function) is a list of the files that meet the naming criteria. But the result of variable b (extract_tab function) is an empty dataframe. What am I doing wrong?

I looked at this postImport multiple excel files into python pandas and concatenate them into one dataframe but it doesn't work... although I did take a couple of ideas from it.

Datanovice’s answer is great. But I think the real problem with your implementation is that you convert the file paths to pd.series — theletz, Jan 03 '20 at 20:05
@theletz what would you suggest to fix the way that I am selecting the files? — Leo, Jan 03 '20 at 20:29

Umar.H · Accepted Answer · 2020-01-04T01:00:43.517

IIUC, you can just do a list comp over your directory.

If using Python 3.4 +

from Pathlib import Path
path_ = 'c:\Users\Documents\Files'

dfs = [pd.read_excel(f,sheet_name='metrics') for f in Path(path_).glob('RMP*WE*')]

df = pd.concat(dfs)

or if you can only use the os module :

os.chdir('c:\Users\Documents\Files')
files = glob.glob('RMP*WE*')
dfs = [pd.read_excel(f,sheet_name='metrics') for f in files]
df = pd.concat(dfs)

Update.

If you need to handle missing sheets, this would be a nice way to do so.

def exlude_sheet(excel_list, sheet):
    """
    takes two arguments:
    1. A list of excel documents
    2. The name of your sheet.
    3. Returns a single data frame after 
    working through your list of excel objects. 
    """
    from xlrd import XLRDError
    df_lists = []
    for file in excel_list:
        try:
            file_df = pd.read_excel(file, sheet_name=sheet)
            df_lists.append(file_df)
        except (XLRDError) as e:
            print(f"{e} skipping")
            continue
    try:
        return pd.concat(df_lists)
    except ValueError as err:
        print("No Objects Matched")

Test.

xlsx = [f for f in Path(path_).glob('RMP*WE*')]
df = exlude_sheet(xlsx,sheet='Metrics')
out:
No sheet named <'Metrics'> for doc_1 skipping
No sheet named <'Metrics'> for doc_final skipping
print(df)
      Column_A  data
0           0     0
1           1     1
2           2     2
3           3     3
4           4     4

Test 2

Testing when no matching sheets are found at all :

exlude_sheet(xlsx,'foobar')
No sheet named <'foobar'> skipping
No sheet named <'foobar'> skipping
No sheet named <'foobar'> skipping
No Objects Matched

How would you modify this code if not all files had the 'metrics' tab? 95% of the files have it, but the remainder don't and it's throwing an error. — Leo, Jan 03 '20 at 20:49
I tried the following to deal with the fact that not all files actually have the sheet named 'metrics: `import pandas as pd import glob, os from glob import glob os.chdir('c:\Users\Documents\Files') def noncom(): for i in glob('RMP*WE*'): try: df= pd.concat(pd.read_excel(i, sheet_name='Metrics')) except: pass return df a = noncom() print(a)` but I get the following error: UnboundLocalError: local variable 'df' referenced before assignment I'll post my answer once I — Leo, Jan 03 '20 at 21:05
thanks for the updated code. It seems to be missing the definition of df_lists, so I added that. But then it tells me 'xlrd.biffh.XLRDError: No sheet named <'Metrics'>' and then 'AttributeError: 'str' object has no attribute 'stem' — Leo, Jan 04 '20 at 00:29
@Leo that's your issue, df_list needs to be a list and not a single `DataFrame`, don't define and use the function above, it creates the list inside the function and returns a single data frame. — Umar.H, Jan 04 '20 at 00:45

score 1 · Answer 2 · answered Jan 03 '20 at 20:04

1

Try

import pandas as pd
from glob import glob

os.chdir('c:\Users\Documents\Files')
df = pd.concat([pd.read_excel(i, sheet_name='Metrics') for i in glob('RMP*WE*')])

answered Jan 03 '20 at 20:04

Kenan

13,156
8
43
50

Import multiple excel sheets from different files into python and concatenate them into one dataframe

2 Answers2

Update.

Test.

Test 2

Linked