3.5 处理多个工作簿
本章前面的几小节演示了如何为单个工作表、工作簿中所有的工作表和工作簿中的一组工作表筛选出特定的行与特定的列。这些处理工作簿的技术是非常有用的。但是，有时你需要处理多个工作簿。在这种情况下，Python 会给你惊喜，因为它可以让你自动化和规模化地进行数据处理，远远超过手工处理能够达到的限度。
这一节重新引入了 Python 内置的 glob 模块，之前第 2 章中曾介绍过这个模块。在本章前面几个示例的基础上，下面演示一下如何处理多个工作簿。
为了使用多个工作簿，首先需要创建多个工作簿。那么让我们再创建另外两个 Excel 工作簿，这样就一共有 3 个工作簿了。但是，请记住这里介绍的技术可以扩展为处理任意多的文件，只要计算机能力允许。
先从下面这个步骤开始。
(1) 打开现有的工作簿 sales_2013.xlsx。
现在，创建第二个工作簿。
(2) 将现有的 3 个工作表名称改为 january_2014、february_2014 和 march_2014。
(3) 在 3 个工作表中，将 Purchase Date 列中的年份改成 2014。
每个工作表中有 6 行数据，所以你一共需要进行 18 次修改（6 行 *3 个工作表）。除了修改年份以外，不需要修改其他内容。
(4) 将第二个工作簿保存为 sales_2014.xlsx。
图 3-10 展示了修改过日期后的 january_2014 工作表中的内容。
图 3-10：修改第一个工作簿中的数据，创建第二个工作簿
现在，创建第三个工作簿。
(5) 将现有的 3 个工作表名称改为 january_2015、february_2015 和 march_2015。
(6) 在 3 个工作表中，将 Purchase Date 列中的年份改成 2015。
每个工作表中有 6 行数据，所以你一共需要进行 18 次修改（6 行 *3 个工作表）。除了修改年份以外，不需要修改其他内容。
(7) 将第三个工作簿保存为 sales_2015.xlsx。
图 3-11 展示了修改过日期后的 january_2015 工作表中的内容。
图 3-11：修改第二个工作簿中的数据，创建第三个工作簿
3.5.1 工作表计数以及每个工作表中的行列计数
在某些情况下，你知道要处理的工作簿中的内容。但是，有些时候工作簿不是你创建的，所以你不清楚其中的内容。与 CSV 文件不同，Excel 工作簿可以包含多个工作表，所以如果你不清楚这些工作表中的内容，那么在开始处理工作表之前，获取一些关于工作表的描述性信息则是非常重要的。
如果想知道一个文件夹中工作簿的数量，每个工作簿中工作表的数量，以及每个工作表中行与列的数量，在文本编辑器中输入下列代码，然后将文件保存为 12excel_introspect_all_workbooks.py：
1 #!/usr/bin/env python3
2 import glob
3 import os
4 import sys
5 from xlrd import open_workbook
6 input_directory = sys.argv[1]
7 workbook_counter = 0
8 for input_file in glob.glob(os.path.join(input_directory, '*.xls*')):
9 workbook = open_workbook(input_file)
10 print('Workbook: %s' % os.path.basename(input_file))
11 print('Number of worksheets: %d' % workbook.nsheets)
12 for worksheet in workbook.sheets():
13 print('Worksheet name:', worksheet.name, '\tRows:',\
14 worksheet.nrows, '\tColumns:', worksheet.ncols)
15 workbook_counter += 1
16 print('Number of Excel workbooks: %d' % (workbook_counter))第 2 和 3 行代码分别导入 Python 内置的 glob 模块和 os 模块，以使我们可以使用其中的函数识别和解析待处理文件的路径名。
第 8 行代码使用 Python 内置的 glob 模块和 os 模块创建了一个要处理的输入文件列表，并对这个输入文件列表应用 for 循环，这行代码可以使我们对所有要处理的工作簿进行迭代。
第 10~14 行代码在屏幕上打印出每个工作簿的信息。第 10 行代码打印工作簿的名称。第 11 行代码打印工作簿中工作表的数量。第 13 和 14 行代码打印出工作簿中工作表的名称和每个工作表中行与列的数量。
要运行这个脚本，在命令行中输入以下命令，然后按回车键：
python 12excel_introspect_all_workbooks.py "C:\Users\Clinton\Desktop"
你应该可以看到输出被打印到屏幕上，如图 3-12 所示。
图 3-12：处理多个工作簿的 Python 脚本的输出
输出显示，脚本处理了 3 个工作簿，还打印出了 3 个工作簿的名称（例如：sales_2013.xls）、每个工作簿中 3 个工作表的名称（例如：january_2013），以及每个工作表中行与列的数量（例如：7 行和 5 列）。
当你对要处理的文件不太熟悉的时候，打印出文件的一些描述性信息是非常有用的。知道了文件的数量以及每个文件中行与列的数量，你就可以大致了解文件处理任务量和文件内容的一致性了。
3.5.2 从多个工作簿中连接数据
01. 基础Python
要使用基础 Python 将多个工作簿中所有工作表的数据垂直连接成一个输出文件，在文本编辑器中输入下列代码，然后将文件保存为 13excel_concat_data_from_multiple_workbook.py：
1 #!/usr/bin/env python3
2 import glob
3 import os
4 import sys
5 from datetime import date
6 from xlrd import open_workbook, xldate_as_tuple
7 from xlwt import Workbook
8 input_folder = sys.argv[1]
9 output_file = sys.argv[2]
10 output_workbook = Workbook()
11 output_worksheet = output_workbook.add_sheet('all_data_all_workbooks')
12 data = []
13 first_worksheet = True
14 for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
15 print os.path.basename(input_file)
16 with open_workbook(input_file) as workbook:
17 for worksheet in workbook.sheets():
18 if first_worksheet:
19 header_row = worksheet.row_values(0)
20 data.append(header_row)
21 first_worksheet = False
22 for row_index in range(1,worksheet.nrows):
23 row_list = []
24 for column_index in range(worksheet.ncols):
25 cell_value = worksheet.cell_value\
26 (row_index,column_index)
27 cell_type = worksheet.cell_type\
28 (row_index, column_index)
29 if cell_type == 3:
30 date_cell = xldate_as_tuple\
31 (cell_value,workbook.datemode)
32 date_cell = date(*date_cell[0:3])\
33 .strftime('%m/%d/%Y')
34 row_list.append(date_cell)
35 else:
36 row_list.append(cell_value)
37 data.append(row_list)
38 for list_index, output_list in enumerate(data):
39 for element_index, element in enumerate(output_list):
40 output_worksheet.write(list_index, element_index, element)
41 output_workbook.save(output_file)第 13 行代码创建了一个布尔型（就是 True/False）变量 first_worksheet，用来区别要处理的第一个工作表和其他后续工作表。对于要处理的第一个工作表，第 18 行代码为 True，所以我们将标题行追加到 data 中，然后将 first_worksheet 设为 False。
对于第一个工作表中余下的数据行和后续工作表中的所有行，我们跳过标题行，处理数据行。因为第 22 行代码中的 range 函数不是从 0 开始，而是从 1 开始的，所以我们知道是从第二行开始处理的。
要运行这个脚本，在命令行中输入以下命令，然后按回车键：
python 13excel_ concat_data_from_multiple_workbooks.py "C:\Users\Clinton\Desktop"\ output_files\13output.xls
你可以打开输出文件 13output.xls 查看一下结果。
02. pandas
pandas 提供了 concat 函数来连接数据框。如果你想把数据框一个一个地垂直堆叠起来，那么就要设置参数 axis=0。如果你想把数据框一个一个地平行连接起来，那么就要设置参数 axis=1。此外，如果你需要基于某个关键字列连接数据框，pandas 中的 merge 函数可以提供类似 SQL join 的操作（如果你不理解这个，没有关系，接下来的第 4 章中会有更多关于数据库的介绍）。
要使用 pandas 将多个工作簿中所有工作表的数据垂直连接成一个输出文件，在文本编辑器中输入下列代码，然后将文件保存为 pandas_concat_data_from_multiple_workbook.py：
#!/usr/bin/env python3
import pandas as pd
import glob
import os
import sys
input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))
data_frames = []
for workbook in all_workbooks:
all_worksheets = pd.read_excel(workbook, sheetname=None, index_col=None)
for worksheet_name, data in all_worksheets.items():
data_frames.append(data)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks',\
index=False)
writer.save()要运行这个脚本，在命令行中输入以下命令，然后按回车键：
python pandas_concat_data_from_multiple_workbooks.py "C:\Users\Clinton\Desktop"\ output_files\pandas_output.xls
你可以打开输出文件 pandas_output.xls 查看一下结果。
3.5.3 为每个工作簿和工作表计算总数和均值
01. 基础Python
要使用基础 Python 为多个工作簿计算工作表级别和工作簿级别的统计量，在文本编辑器中输入下列代码，然后将文件保存为 14excel_sum_average_multiple_workbook.py：
1 #!/usr/bin/env python3
2 import glob
3 import os
4 import sys
5 from datetime import date
6 from xlrd import open_workbook, xldate_as_tuple
7 from xlwt import Workbook
8 input_folder = sys.argv[1]
9 output_file = sys.argv[2]
10 output_workbook = Workbook()
11 output_worksheet = output_workbook.add_sheet('sums_and_averages')
12 all_data = []
13 sales_column_index = 3
14 header = ['workbook', 'worksheet', 'worksheet_total', 'worksheet_average',\
15 'workbook_total', 'workbook_average']
16 all_data.append(header)
17 for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
18 with open_workbook(input_file) as workbook:
19 list_of_totals = []
20 list_of_numbers = []
21 workbook_output = []
22 for worksheet in workbook.sheets():
23 total_sales = 0
24 number_of_sales = 0
25 worksheet_list = []
26 worksheet_list.append(os.path.basename(input_file))
27 worksheet_list.append(worksheet.name)
28 for row_index in range(1,worksheet.nrows):
29 try:
30 total_sales += float(str(worksheet.cell_value\
31 (row_index,sales_column_index))\
32 .strip('$').replace(',',''))
33 number_of_sales += 1.
34 except:
35 total_sales += 0.
36 number_of_sales += 0.
37 average_sales = '%.2f' % (total_sales / number_of_sales)
38 worksheet_list.append(total_sales)
39 worksheet_list.append(float(average_sales))
40 list_of_totals.append(total_sales)
41 list_of_numbers.append(float(number_of_sales))
42 workbook_output.append(worksheet_list)
43 workbook_total = sum(list_of_totals)
44 workbook_average = sum(list_of_totals)/sum(list_of_numbers)
45 for list_element in workbook_output:
46 list_element.append(workbook_total)
47 list_element.append(workbook_average)
48 all_data.extend(workbook_output)
49
50 for list_index, output_list in enumerate(all_data):
51 for element_index, element in enumerate(output_list):
52 output_worksheet.write(list_index, element_index, element)
53 output_workbook.save(output_file)第 12 行代码创建了一个空列表 all_data，用来保存要写入输出文件的所有行。第 13 行代码创建了一个变量 sales_column_index，保存 Sale Amount 列的索引值。
第 14 行代码为输出文件创建了一个列标题列表，并使用第 16 行代码将其追加到 all_data 中。
在第 19、20 和 21 行代码中，分别创建了 3 个列表。list_of_totals 用来保存工作簿中所有工作表的销售额总计。同样，list_of_numbers 用来保存工作簿的所有工作表中用来计算总销售额的销售额数据个数。第三个列表，workbook_output，用来保存要写入输出文件的所有输出列表。
第 25 行代码创建了一个列表 worksheet_list，用来保存要保留的所有工作表的信息。在第 26 和 27 行代码中，将工作簿名称和工作表名称追加到 worksheet_list 中。同样，在第 38 和 39 行代码中，将销售额总计和均值追加到 worksheet_list 中。在第 42 行代码中，将 worksheet_list 追加到 workbook_output 中，在工作簿级别保存信息。
在第 40 和 41 行代码中，将工作表的销售额总计和销售额数据个数分别追加到 list_of_totals 和 list_of_numbers 中，这样我们可以对所有工作表保存这些值。在第 43 和 44 行代码中，使用这两个列表计算出工作簿的销售额总计和销售额均值。
在第 45~47 行代码中，我们在 workbook_output 的各个列表之间迭代（每个工作簿有 3 个列表，因为每个工作簿有 3 个工作表），并将工作簿级别的销售额总计和均值追加到每个列表中。
当获得了所有要为工作簿保留的信息之后（就是 3 个列表，每个工作表有一个列表），就将这些列表扩展到 all_data 中。我们使用 extend，不是 append，以使 workbook_output 中的每个列表都会成为 all_data 中的一个独立元素。这样的话，在处理完所有工作簿之后，all_data 就是一个具有 9 个元素的列表，每个元素都是一个列表。否则，如果使用 append，all_data 中就会只有 3 个元素，每个元素都是一个列表的列表。
要运行这个脚本，在命令行中输入以下命令，然后按回车键：
python 14excel_sum_average_multiple_workbooks.py "C:\Users\Clinton\Desktop"\ output_files\14output.xls
你可以打开输出文件 14output.xls 查看一下结果。
02. pandas
pandas 可以直接在多个工作簿之间迭代，并可以同时在工作簿级别和工作表级别计算统计量。在下面的脚本中，为工作簿中的每个工作表计算统计量，然后将结果连接成一个数据框。接下来，计算工作簿级别的统计量，将它们转换成一个数据框，然后通过基于工作簿名称的左连接将两个数据框合并在一起，并将结果数据框添加到一个列表中。当所有工作簿级别的数据框都进入列表之后，将这些数据框连接成一个独立数据框，并写入输出文件。
要使用 pandas 计算工作表级别和工作簿级别的统计量，在文本编辑器中输入下列代码，然后将文件保存为 pandas_sum_average_multiple_workbook.py：
#!/usr/bin/env python3
import pandas as pd
import glob
import os
import sys
input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))
data_frames = []
for workbook in all_workbooks:
all_worksheets = pd.read_excel(workbook, sheetname=None, index_col=None)
workbook_total_sales = []
workbook_number_of_sales = []
worksheet_data_frames = []
worksheets_data_frame = None
workbook_data_frame = None
for worksheet_name, data in all_worksheets.items():
total_sales = pd.DataFrame([float(str(value).strip('$').replace(\
',',''))
for value in data.loc[:, 'Sale Amount']]).sum()
number_of_sales = len(data.loc[:, 'Sale Amount'])
average_sales = pd.DataFrame(total_sales / number_of_sales)
workbook_total_sales.append(total_sales)
workbook_number_of_sales.append(number_of_sales)
data = {'workbook': os.path.basename(workbook),
'worksheet': worksheet_name,
'worksheet_total': total_sales,
'worksheet_average': average_sales}
worksheet_data_frames.append(pd.DataFrame(data, \
columns=['workbook', 'worksheet', \
'worksheet_total', 'worksheet_average']))
worksheets_data_frame = pd.concat(\
worksheet_data_frames, axis=0, ignore_index=True)
workbook_total = pd.DataFrame(workbook_total_sales).sum()
workbook_total_number_of_sales = pd.DataFrame(\
workbook_number_of_sales).sum()
workbook_average = pd.DataFrame(\
workbook_total / workbook_total_number_of_sales)
workbook_stats = {'workbook': os.path.basename(workbook),
'workbook_total': workbook_total,
'workbook_average': workbook_average}
workbook_stats = pd.DataFrame(workbook_stats, columns=\
['workbook', 'workbook_total', 'workbook_average'])
workbook_data_frame = pd.merge(worksheets_data_frame, workbook_stats, \
on='workbook', how='left')
data_frames.append(workbook_data_frame)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='sums_and_averages', \
index=False)
writer.save()要运行这个脚本，在命令行中输入以下命令，然后按回车键：
python pandas_sum_average_multiple_workbooks.py "C:\Users\Clinton\Desktop"\ output_files\pandas_output.xls
你可以打开输出文件 pandas_output.xls 查看一下结果。
本章介绍了很多基础性操作，包括读取和分析 Excel 工作簿、在工作表中浏览行与列、处理多个 Excel 工作表、处理多个 Excel 工作簿、以及为多个 Excel 工作表和工作簿计算统计量的方法。如果一直跟随本章内容练习示例代码，你应该完成了 14 个新的 Python 脚本！
练习本章中示例代码的最大收获是，你很好地掌握了浏览和处理 Excel 文件的技术，而 Excel 文件是商业过程中最常用的一种文件。而且，因为很多商业机构将数据保存在 Excel 工作簿中，所以你现在已经掌握了处理这些工作簿中数据的一系列方法，无论工作簿的数量和体积有多大，也无论每个工作簿中有多少工作表，你都可以使用计算机数据处理的强大能力来自动化和规模化地处理和分析 Excel 工作簿中的各种数据。
我们要面对的下一个数据源是数据库。因为数据库是一种非常常用的数据存储，所以知道如何访问其中的数据是非常重要的。只要知道了如何访问其中的数据，你就可以像处理 CSV 文件和 Excel 文件一样，同样以一行接一行的方式来处理这些数据。掌握了第 2 章和本章中的示例代码之后，你已经完全做好了处理数据库中数据的准备。
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论