如何将通过正则匹配Excel工作表读取的三维字典转换为指定格式的Pandas DataFrame

首先咱们直接解决你的核心需求：不用循环，用Pandas原生函数就能把匹配到的工作表数据合并成单一DataFrame，或者直接修改读取逻辑避免生成字典。

方案1：修改读取函数，直接返回合并后的DataFrame #

你原来的函数因为给pd.read_excel传了sheet_name=sheets（列表），所以返回的是工作表名: DataFrame的字典。如果想跳过字典这一步，直接得到包含所有Col1、Col2数据的DataFrame，可以修改函数逻辑：

import pandas as pd
import re

def gettingSheetName(_directory,_regex):
    '''reads the file using regex as a pattern for Sheet name'''
    xl = pd.ExcelFile(_directory)
    regex = re.compile(_regex)
    sheets = [n for n in xl.sheet_names if regex.match(n)]
    if sheets:
        # 逐个读取匹配的工作表，存入列表后合并
        df_collection = [pd.read_excel(xl, sheet_name=s, index_col=False) for s in sheets]
        # ignore_index=True 重置行索引，避免保留原工作表的索引
        return pd.concat(df_collection, ignore_index=True)

这样返回的就是你想要的结果：只有Col1、Col2列，所有工作表的数据合并在一起，没有多余的工作表名字段或索引。

方案2：如果已经拿到了工作表字典，直接合并字典里的DataFrame #

如果你不想修改原函数，已经得到了那个{'SheetName': DataFrame}的字典，也可以用Pandas原生的concat直接合并所有值（也就是各个工作表的DataFrame）：

# 假设你的字典叫sheet_dict
final_df = pd.concat(sheet_dict.values(), ignore_index=True)

这行代码会把字典里所有的DataFrame按行合并，自动对齐列名（这里都是Col1、Col2），最后得到符合要求的单一DataFrame。

解释你之前遇到的问题 #

• 用pd.DataFrame.from_dict(Dic)报错：这个方法默认处理的是键值对为标量/一维数组的字典，而你的字典值是二维的DataFrame，所以会触发"If using all scalar values..."错误，它不适合这种场景。
• 用pd.DataFrame(list(Sach.items()), columns=['Col1', 'Col2'])结果混乱：因为items()返回的是(工作表名, DataFrame)这样的元组，DataFrame会被当作一个整体放进新DataFrame的列里，自然会出现工作表名、列名和数据混在一起的情况，这方法只适合简单键值对的字典。

关于你尝试的concat方法 #

你之前用pd.concat(pd.read_excel(...))得到了带工作表名索引的DataFrame，只需要加一个ignore_index=True参数，就能去掉那个工作表名索引，重置为连续的行索引：

# 修改后的代码
df = pd.concat(pd.read_excel(xl, sheet_name=sheets, index_col=False), ignore_index=True)

扩展：如果需要保留工作表名作为一列（可选） #

要是之后你需要知道每条数据来自哪个工作表，可以在concat时加上keys参数，再把索引转成列：

df = pd.concat(pd.read_excel(xl, sheet_name=sheets, index_col=False), keys=sheets)
# 把工作表名从索引转成普通列，并重命名
df = df.reset_index(level=0).rename(columns={'level_0': 'SourceSheet'})

这样会多一列SourceSheet，记录每条数据对应的工作表名称。

内容的提问来源于stack exchange，提问作者Mostafa Bouzari