从不同文件中导入多个excel表到python中，并将它们连接成一个数据表

Question

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)

Answer 1

IIUC，你可以在你的目录上做一个列表comp。

如果使用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)

或者如果只能使用OS模块：

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)

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

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

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