在Python中连接所有工作表文件并使用工作表名称创建一个新列

Question

给定一个包含多张工作表的excel文件test.xlsx：1.DC、2.SH和3.GZ等。

import xlrd
dfs = xlrd.open_workbook('./test.xlsx', on_demand = True)
print(dfs.sheet_names()) 

输出：

['1.DC', '2.SH', '3.GZ']

如何读取和连接所有工作表，并使用工作表名称创建新列，同时删除每个工作表名称开始部分中的1., 2., 3., etc.？

预期的结果如下所示：

​

​

Answer 1

使用带有sheet_name=None的read_excel将所有工作表转换为DataFrame：

dfs = pd.read_excel('test.xlsx', sheet_name=None)

dfs = {k: v.loc[:, ~v.columns.str.contains('Unnamed')] for k, v in dfs.items()}

# print(dfs) 

然后，如果需要一列，请使用列表比较和select此列，此处为name

df = pd.DataFrame([(x, k) for k, v in dfs.items() for x in v['name']], 
                  columns=['name','city'])
df['city'] = df['city'].str.replace('^[0-9\.]+', '')
print (df)
      name    city
0    James      DC
1     Bond      DC
2   Steven      DC
3   Walker      SH
4      Tom      SH
5   Filler      GZ
6   Cooker      GZ
7      Tim      GZ

或者，如果需要所有列，请在此处省略[name]：

dfs = pd.read_excel('test.xlsx', sheet_name=None)
print(dfs) 

df = (pd.concat(dfs)
        .reset_index(level=1, drop=True)
        .rename_axis('city')['name']
        .reset_index())
df['city'] = df['city'].str.replace('^[0-9\.]+', '')
print (df)
      city    name
0       DC   James
1       DC    Bond
2       DC  Steven
3       SH  Walker
4       SH     Tom
5       GZ  Filler
6       GZ  Cooker
7       GZ     Tim