Question

每个公司都在各自不同的业务过程中积累了大量文件，在供应商、客户、内部运营和其他业务过程中，可能存在着大量历史文件。正如本书已经讨论过的，这些数据可能被保存在像 CSV 文件这样的带分隔符的平面文件中，可能被保存在 Excel 工作簿和电子表格中，也可能被保存在其他存储系统中。保存这些文件是有价值的，因为它们可以提供数据用来分析，有助于跟踪随着时间发生的变化，还可以为决策提供支持。

但是当你有大量历史数据的时候，要找到真正需要的数据是非常困难的。想象一下，假如你有 300 个 Excel 工作簿和 200 个 CSV 文件（你一直交替使用这两种类型的文件），里面包含着过去 5 年有过购买行为的供应商数据。现在你和一个供应商正在进行谈判，你想找到一些对这次谈判有用的信息的历史记录。

当然，你可以打开每个文件，找出你需要的记录，然后将记录复制粘贴到一个新文件中。但是，这个过程太痛苦了，既浪费时间，又容易出错。这正是展示你刚学会的 Python 编程技能的一个绝好机会，你可以自动化地完成整个过程，既节省时间，又不会出错。

为了模拟在一个包含了几百个 Excel 工作簿和 CSV 文件的历史文件夹中进行搜索，我们需要创建这个历史文件夹，以及一些 Excel 工作簿和 CSV 文件。要完成这个操作，需要按以下步骤进行。

(1) 切换到桌面。

(2) 在桌面上点鼠标右键。

(3) 选择“新建”，然后选择“文件夹”，在桌面上新建一个文件夹。

(4) 将新建的文件夹命名为“file_archive”。

现在你的桌面上应该有一个名为 file_archive 的新文件夹，如图 5-1 所示。

图 5-1：在桌面上新建文件夹 file_archive

(5) 打开 Excel，输入图 5-2 中的数据。

这个 CSV 文件有 5 列：Item Number、Description、Supplier、Cost 和 Date。你可以看到在第一列中是只有部件才有项目编号。部件服务和部件维修有独立的记录，但是服务记录和维修记录中没有项目编号。

图 5-2：CSV 文件 supplies_2012.csv 中的示例数据，显示在 Excel 工作表中

(6) 将文件保存在 file_archive 文件夹中，命名为 supplies_2012.csv。

好了，现在我们有了一个 CSV 文件。下一步，需要创建一个 Excel 工作簿。要快速完成这个操作，可以使用刚建好的 CSV 文件。

(7) 在工作表 supplies_2012 中，将 Date 列中的日期由 2012 改为 2013。

工作表现在应该如图 5-3 所示。你可以看到，只有日期发生了变化。

图 5-3：将 supplies_2012 中的日期由 2012 改成 2013，添加一个 2013 年的工作表

(8) 将工作表的名称改为 supplies_2013。

为了使这个文件成为一个具有多个工作表的工作簿，下面再添加一个新的工作表。

(9) 点击左下角的 + 按钮，添加一个新的工作表。

(10) 将新工作表命名为 supplies_2014。

(11) 将 supplies_2013 工作表中的数据复制粘贴到 supplies_2014 工作表中。

(12) 将 Date 列中的日期由 2013 修改为 2014。

supplies_2014 工作表如图 5-4 所示。

图 5-4：supplies_2014 工作表

你可以看到，只有日期发生了变化，也就是说，两个工作表中的数据除了 Date 列中的日期，其余是完全一样的。

(13) 将 Excel 文件保存在 file_archive 文件夹中，命名为 supplies.xls。

(14) 最后，这是一个可选步骤，如果你可以将文件保存成 Excel 工作簿格式（.xlsx），打开“Save As”对话框，将文件保存为 supplies.xlsx。

现在，在 file_archive 文件夹中，你应该有 3 个文件。

· CSV 文件：supplies_2012.csv

· Excel 文件：supplies.xls

· Excel 工作簿文件（可选）：supplies.xlsx

如果你没有创建可选的 .xlsx 文件，示例代码也可以工作，只是会缺少一些输出。这 3 个文件可以作为我们积累的历史文件，但是请记住，示例代码可以扩展为处理任意多的 CSV 文件和 Excel 文件，只要计算机能力足够 1。如果你有成百上千个 CSV 或 Excel 历史文件，那么你仍然可以使用这个示例中的代码作为具体搜索问题的起点，然后扩展代码。

1计算机能处理多少文件主要取决于随机存取存储器（RAM）和中央处理单元（CPU）。Python 将要处理的数据保存在 RAM 中，所以当数据体积大于计算机 RAM 时，计算机就会将数据写在磁盘中，而不是 RAM 中。向磁盘中写数据的速度要明显慢于在 RAM 中存取数据，所以计算机会变得非常慢，甚至看上去没有反应。根据你的数据量，如果你觉得会遇到这种问题，就应该使用有更多 RAM 的机器，或者向机器中加入更多的 RAM，或者将数据分成小块来处理。你也可以使用分布式系统，将很多计算机连接在一起作为一个整体使用，但是这已经超出了本书范围。

现在我们已经有了用来搜索记录的文件夹和文件，还需要以某种方式来识别要搜索的记录。例如，我们要搜索特定的数值项目。如果只想搜索很少的数值项目，就可以使用列表或元组变量在 Python 脚本中将条件写死（例如：items_to_look_for = ['1234', '2345']），但是当要搜索的数值项目增加时，这种方法就会变得笨重甚至不可行。因此，我们要使用以前向脚本中传递输入数据的方法，并将数值项目放在 CSV 输入文件的一列中。使用这种方法，如果你想搜索几十、几百甚至几千个数值项目，都可以将它们写在 CSV 输入文件中，然后将这些输入数据读入 Python 脚本。这种输入方法具有很好的扩展性，特别是与将条件写死在 Python 脚本中的方法相比。

要列出识别搜索记录的数值项目：

(1) 打开 Excel，输入图 5-5 中的数据；

(2) 将文件保存为 item_numbers_to_find.csv。

图 5-5：item_numbers_to_find.csv 中的示例数据，显示在 Excel 工作表中

你可以看到，我们要搜索的 5 个数值项目是 1234、2345、4567、6789 和 7890。它们都被写在第 A 列，没有标题行。其实可以包括标题行，但是没有必要，因为我们知道要使用哪一列，并且知道这列数值的意义。而且，如果添加了标题行，还需要多写一些代码来删除它，因为我们不会搜索输入文件标题行中的数据。如果将来有人或系统提供给你的数值列表中包括标题行，那么你可以使用在前面的章节中学会的去掉标题行的方法，将其读入一个变量，然后不使用这个变量就可以了。如果你自己创建列表，就应该不包括标题行，这样做可以简化进行数据处理的代码，你可以根据文件名称和文件所在的项目文件夹名称回想起数据的意义。

至此，我们已经理解了搜索任务，并且创建了执行示例代码所需的文件和文件夹。简而言之，我们的任务是搜索 file_archive 文件夹，找出包含我们所需的数值项目的文件，当找到一个数值项目时，需要把包含这个项目的整行数据写入输出文件。通过这种方式，你可以在历史信息中找出所有可以用于与供应商谈判的信息，这些信息都与数值项目相关。我们要搜索 3 个历史文件：一个 CSV 文件、一个 Excel 文件（.xls）和一个 Excel 工作簿文件（.xlsx）。这 3 个文件构成了示例代码能够处理的文件的最小集，脚本代码可以扩展为处理任意数量的文件，只要计算机能力足够。我们还建立了一个独立的 CSV 文件，包含要搜索的数值项目。在这个文件中，可以包含成百上千甚至更多的数值项目，这种输入方法同样可以帮助我们扩展搜索能力。

我们已经创建了 file_archive 文件夹和所有输入文件，现在需要做的就是编写 Python 代码来执行搜索任务。要完成这个操作，在文本编辑器中输入下列代码，然后将文件保存为 1search_for_items_write_found.py：

 1 #!/usr/bin/env python3
 2 import csv
 3 import glob
 4 import os
 5 import sys
 6 from datetime import date
 7 from xlrd import open_workbook, xldate_as_tuple
 8 item_numbers_file = sys.argv[1]
 9 path_to_folder = sys.argv[2]
10 output_file = sys.argv[3]
11 item_numbers_to_find = []
12 with open(item_numbers_file, 'r', newline='') as item_numbers_csv_file:
13     filereader = csv.reader(item_numbers_csv_file)
14     for row in filereader:
15          item_numbers_to_find.append(row[0])
16 #print(item_numbers_to_find)
17 filewriter = csv.writer(open(output_file, 'a', newline=''))
18 file_counter = 0
19 line_counter = 0
20 count_of_item_numbers = 0
21 for input_file in glob.glob(os.path.join(path_to_folder, '*.*')):
22     file_counter += 1
23     if input_file.split('.')[1] == 'csv':
24          with open(input_file, 'r', newline='') as csv_in_file:
25               filereader = csv.reader(csv_in_file)
26               header = next(filereader)
27               for row in filereader:
28                    row_of_output = [ ]
29                    for column in range(len(header)):
30                          if column == 3:
31                               cell_value = str(row[column]).lstrip('$').\
32                               replace(',','').strip()
33                               row_of_output.append(cell_value)
34                          else:
35                               cell_value = str(row[column]).strip()
36                               row_of_output.append(cell_value)
37                   row_of_output.append(os.path.basename(input_file))
38                   if row[0] in item_numbers_to_find:
39                        filewriter.writerow(row_of_output)
40                        count_of_item_numbers += 1
41                   line_counter += 1
42     elif input_file.split('.')[1] == 'xls' or \
43     input_file.split('.')[1] == 'xlsx':
44          workbook = open_workbook(input_file)
45          for worksheet in workbook.sheets():
46               try:
47                    header = worksheet.row_values(0)
48               except IndexError:
49                    pass
50               for row in range(1, worksheet.nrows):
51                    row_of_output = [ ]
52                    for column in range(len(header)):
53                         if worksheet.cell_type(row, column) == 3:
54                              cell_value = \
55                              xldate_as_tuple(worksheet.cell(row,column)\
56                              .value,workbook.datemode)
57                              cell_value = str(date(*cell_value[0:3])).strip()
58                              row_of_output.append(cell_value)
59                         else:
60                              cell_value = \
61                              str(worksheet.cell_value(row,column)).strip()
62                              row_of_output.append(cell_value)
63                    row_of_output.append(os.path.basename(input_file))
64                    row_of_output.append(worksheet.name)
65                    if str(worksheet.cell(row,0).value).split('.')[0].strip() \
66                    in item_numbers_to_find:
67                         filewriter.writerow(row_of_output)
68                         count_of_item_numbers += 1
69                    line_counter += 1
70 print('Number of files:', file_counter)
71 print('Number of lines:', line_counter)
72 print('Number of item numbers:', count_of_item_numbers)

这个脚本比前面章节中的任何一个脚本都要长，但是如果你完成了前面章节中的示例代码，那么应该对这个脚本中的所有代码都很熟悉了。第 2~7 行代码导入我们读取和处理输入数据所需的模块和方法。这里需要导入 csv、glob、os、string 和 sys 模块，来分别读写 CSV 文件、读取一个文件夹中的多个文件、在一个特定路径中搜索文件、处理字符串变量和在命令行中输入文件名。像在第 3 章中一样，这里还要导入 datetime 模块的 date 方法和 xlrd 模块的 xldate_as_tuple 方法，确保我们从输入文件中提取的任何日期数据都能以特定的形式保存到输出文件中。

第 8、9、10 行代码读取我们在命令行中提供的 3 个输入参数，分别是包含要搜索的数值项目的 CSV 文件的路径名、包含要搜索的文件的 file_archive 文件夹的路径、包含在历史文件中搜索到的与数值项目相关的信息行的 CSV 输出文件的路径名。这里将这 3 个输入参数赋给 3 个独立的变量，分别是 item_numbers_file、path_to_folder、和 output_file。

要在代码中使用我们想要搜索的数值项目，需要将它们从 CSV 输入文件转换成合适的数据结构，比如一个列表。第 11~15 行代码完成了这个转换。第 11 行代码创建了一个空列表 item_numbers_to_find。第 12 和 13 行代码使用 csv 模块的 reader() 方法打开 CSV 输入文件，并创建了一个 filereader 对象读取文件中的数据。第 14 行代码创建了一个 for 循环，在输入文件的所有行中循环。第 15 行代码使用列表的 append() 方法为在第 11 行中创建的列表添加值。为列表添加的值来自于 CSV 输入文件中的第一列 row[0]。如果你想看一下追加到列表中的数值项目，想在运行脚本时将其打印到屏幕上，那么就可以将第 16 行代码中 print 语句前面的注释符号去掉。

第 17 行代码使用 csv 模块的 write() 方法以追加方式（'a'）打开一个 CSV 输出文件，并创建一个 filewriter 对象，准备写入数据到输出文件。

第 18、19 和 20 行代码创建了 3 个计数变量，来跟踪（a）读入脚本的历史文件数量，（b）在所有的输入文件和工作表中读出的行数，和（c）行中数值项目是我们要搜索的数值项目的行数。这 3 个计数变量都初始化为 0。

第 21 行代码是外部 for 循环，在历史文件夹中的所有输入文件中循环。这行代码使用 os.path.join() 函数和 glob.glob() 函数来在 file_archive 文件夹中找到所有匹配于一个特定模式的文件。file_archive 文件夹的路径由我们在命令行中提供，包含在变量 path_to_folder 中。os.path.join() 函数将这个文件夹路径与文件夹中所有文件的文件名连接起来，这些匹配于特定模式的文件名由 glob.glob() 函数进行扩展。这里，我们使用模式 '*.*' 来匹配以任意扩展名结尾的任意文件名。在这个例子中，因为我们创建了输入文件夹和文件，所以知道文件夹中的文件扩展名只有 .csv、.xls 和 .xlsx。如果你只想搜索 CSV 文件，那么可以使用 '*.csv'；如果你只想搜索 .xls 或 .xlsx 文件，那么可以使用 '*.xls*'。这是一个 for 循环，所以这行代码的其他语法就很熟悉了。input_file 是一个占位符名称，代表由 glob.glob() 函数生成的列表中的每个文件。

第 22 行代码对于读入脚本的每一个输入文件，都将 file_counter 变量的值增加 1。在所有输入文件都被读入脚本之后，file_counter 就是读入的输入文件的总数。

第 23 行代码是一个 if 语句，开始了一个处理 CSV 文件的代码块。与这行代码同级的是第 42 行中的 elif 语句，它开始了一个处理 .xls 和 .xlsx 文件的代码块。第 23 行代码使用 string 模块的 split() 方法将每个输入文件的路径名按照路径中的句点（.）进行分割。例如，CSV 输入文件的路径名是 file_archive\supplies_2012.csv。如果这个字符串按照句点分割，句点前面部分的索引值为 [0]，句点后面的索引值为 [1]。这行代码检验句点后面的字符串（索引值为 1）是否为 csv，对于 CSV 输入文件这行代码为真。因此，第 24~41 行代码仅对于 CSV 输入文件执行。

第 24 和 25 行代码我们已经非常熟悉了。它们使用 csv 模块的 reader() 方法打开 CSV 输入文件，并创建了一个 filereader 对象，从文件中读取数据。

第 26 行代码使用 next() 方法读取输入文件中的第一行数据，也就是标题行，并赋给变量 header。

第 27 行代码创建了一个 for 循环，在 CSV 文件中余下的数据行之间循环。对于每一行，如果这行包含我们要搜索的数值项目，那么我们就需要组装一行输出写入输出文件。为了准备组装这行输出，第 28 行代码创建了一个空列表变量 row_of_output。

第 29 行代码创建了一个 for 循环，在输入文件一个给定行的各列之间循环。这行代码使用 range() 函数和 len() 函数创建了一个 CSV 输入文件各列的索引列表。因为输入文件中有 5 列，所以 column 变量的范围是从 0 到 4。

第 30~36 行代码包含一个 if-else 语句，对不同列中的值进行不同的处理。if 代码块处理索引值为 3 的列，也就是第四列 Cost。对于这一列，先使用 lstrip() 方法剥离字符串左侧的美元符号，然后使用 replace() 方法用空字符串替换掉字符串中的逗号（这样可以有效地删除逗号），再使用 strip() 方法剥离字符串两端的空格、制表符和换行符。在这些处理完成之后，第 33 行代码将最后的值追加到列表 row_of_output 中。

else 代码块处理其余各列中的数据。对于这些值，使用 strip() 方法剥离字符串两端的空格、制表符和换行符，然后让结果追加到第 36 行的列表 row_of_output 中。

第 37 行代码将输入文件的基础文件名追加到列表 row_of_output 中。对于 CSV 文件，变量 input_file 中包含的字符串是 file_archive\supplies_2012.csv。os.path.basename 确保代码只将 supplies_2012.csv 追加到列表 row_of_output 中。

至此，CSV 输入文件中的第一行数据被读入到脚本中，这行数据中的每一列都进行了处理，然后追加到列表 row_of_output 中。现在应该检验这一行中的数值项目是否就是我们要搜索的数值项目。第 38 行代码进行了这个判断。这行代码检验这一行数据中第一列的值（是个数值项目）是否在我们要搜索的数值项目列表中，即是否包含在列表变量 item_numbers_to_find 中。如果这个数值项目是我们要搜索的数值项目中的一个，那么我们就在第 39 行中使用 filewriter 的 writerow() 方法，将这一行写入 CSV 输出文件。在第 40 行代码中，我们还要将 count_of_item_numbers 变量的值增加 1，来跟踪在所有输入文件中找到的数值项目的数量。

最后，在转到处理 CSV 输入文件中的下一行数据之前，在第 41 行代码中，要将 line_counter 变量的值增加 1，来跟踪我们在所有输入文件中找到的数据行的数量。

从第 42 行开始到第 69 行是另一个代码块，它与前一个代码块非常相似，区别在于它处理的是 Excel 文件（.xls 和 .xlsx），不是 CSV 文件。因为这个“Excel”代码块中的逻辑与“CSV”代码块中的逻辑是一样的，唯一区别是处理 Excel 文件的语法和 CSV 文件不一样，所以这里就不像前面那样详细地解释每行代码了。

第 42 行是一个 elif 语句，开始了一个处理扩展名为 .xls 或 .xlsx 的 Excel 文件的代码块。第 42 行使用一个“or”条件来检验文件扩展名是否为 .xls 或 .xlsx。因此，第 43~69 行代码执行的是扩展名为 .xls 和 .xlsx 的 Excel 输入文件。

第 43 行代码使用 xlsx 模块的 open_workbook() 方法打开一个 Excel 工作簿，并将其中的内容赋给变量 workbook。

第 45 行代码创建了一个 for 循环，在工作簿的所有工作表之间循环。对于每个工作表，第 46~49 行代码先试图将工作表中的第一行（也就是标题行）读入变量 header。如果出现了 IndexError，就说明序列下标越界，那么就执行 Python 关键字 pass，实际上就是什么都不做，然后代码继续执行第 50 行操作。

第 50 行代码创建了一个 for 循环，在 Excel 输入文件的其余数据行之间循环。循环范围不是从 0 开始，而是从 1 开始，也就是从工作表中的第二行开始（成功地跳过了标题行）。

这个“Excel”代码块中其余各行代码与“CSV”代码块中的代码基本相同，除了使用 Excel 解析语法代替了 CSV 解析语法。if 代码块处理单元格类型为 3 的列，也就是包含代表日期的数值的列。这个代码块使用 xlrd 模块的 xldate_as_tuple() 方法和 datetime 模块的 date() 方法来保证这个列中的日期值在输出文件中保持原来的格式。只要这个值被转换为具有日期形式的文本字符串，就使用 strip() 方法剥离字符串两端的空格、制表符和换行符，然后第 58 行代码使用列表的 append() 方法将这个值追加到列表 row_of_output 中。

else 代码块处理所有其他列中的值。对于每个值，使用 strip() 方法剥离字符串两端的空格、制表符和换行符，然后第 62 行代码将这个值追加到列表 row_of_output 中。

第 63 行代码将输入文件的基本文件名追加到列表 row_of_output 中。与 CSV 文件不同， Excel 文件中可以包含多个工作表。因此，第 64 行代码还将工作表名称追加到列表中。这个 Excel 文件的附加信息可以更加清楚地表示出脚本在哪里找到了数值项目。

第 65~69 行代码和处理 CSV 文件的代码一样，第 65 行检验行中第一列的值（数值项目）是否在我们要搜索的数值项目列表中，即是否包含在列表变量 item_numbers_to_find 中。如果数值项目是我们要搜索的数值项目中的一个，那么就在第 67 行中使用 filewriter 的 writerow() 方法将这一行写入 CSV 输出文件。在第 68 行代码中，我们还要将 count_of_item_numbers 变量的值增加 1，来跟踪在所有输入文件中找到的数值项目的数量。

最后，在转到处理 Excel 工作表中的下一行数据之前，在第 69 行代码中，要将 line_counter 变量的值增加 1，来跟踪我们在所有输入文件中找到的数据行的数量。

第 70、71 和 72 行代码是 print 语句。当脚本处理完成所有输入文件时，这些 print 语句将摘要信息打印在命令行窗口或终端窗口中。第 70 行打印出脚本处理的文件数。第 71 行打印出在所有输入文件和工作表中读取的行数。第 72 行打印出带有我们要搜索的数值项目的行数，这个数值可能包含重复计数。例如，如果数值项目“1234”在一个文件中出现了两次或者在两个文件中分别出现一次，那么这个项目在这行代码打印到命令行窗口或终端窗口中的数值中就被计算了两次。

现在我们已经完成了 Python 脚本，可以使用这个脚本在一堆历史文件中搜索特定的数据行，并将输出写入一个 CSV 格式的输出文件了。要完成这个操作，在命令行中输入以下命令，然后按回车键：

python 1search_for_items_write_found.py item_numbers_to_find.csv file_archive\
output_files\1app_output.csv

在 Windows 系统中，你可以看到输出被打印到命令行窗口中，如图 5-6 所示。

图 5-6：1search_for_items_write_found.py 的运行结果，使用 item_numbers_to_find.csv 文件和 file_archive 文件夹中的文件

从命令行窗口中的输出你可以看到，脚本处理了 3 个输入文件，从输入文件中读入了 50 行数据，并找到了 25 行包含我们需要的数值项目的数据。这个输出没有表示出脚本找到了多少个数值项目，也没有表示出每个数值项目被找到了多少次。但是，这就是我们要将输出写在 CSV 输出文件中的原因。

要查看输出文件内容，需要打开文件 1app_output.csv。内容如图 5-7 所示。

图 5-7：1search_for_items_write_found.py 写入到 1app_output.csv 中的数据

这些记录来自于 3 个输入文件中的行数据，其中的数值项目都与 CSV 文件中的数值项目相匹配。最后第二列给出了数据所在的文件名。如果数据包含在这两个 Excel 工作簿中，最后一列就给出了数据所在的工作表名称。

从输出文件内容可以看出，我们找到了 25 行包含我们需要的数值项目的数据。这个输出文件与命令行窗口中打印出的“25”是一致的。特别需要注意的是，我们发现每个数值项目在所有输入文件中都一共被找到了 5 次。例如，数值项目“1234”在 .xls 文件中被找到了 2 次（一次在 supplies_2013 工作表中，一次在 supplies_2014 工作表中），在 .xlsx 文件中被找到了 2 次（一次在 supplies_2013 工作表中，一次在 supplies_2014 工作表中），在 CSV 输入文件中被找到了 1 次。

与来自于 CSV 输入文件的行相比，来自于 Excel 工作表的行中多出了一列（就是找到数据行的工作表名称）。第四列中的成本数据只包括输入文件中的成本数值部分。最后，第五列中的日期数据已经被格式化了，与 CSV 和 Excel 输入文件中的格式保持一致。

这个应用程序综合运用了我们在前几章中学习的技术，解决了一个常见的实际问题。商业分析师们经常会遇到需要将分布在多个不同类型文件中的历史数据组合成一个完整数据集这样的问题。在很多情况下，历史数据文件会有几十、几百甚至上千个，从这些文件中搜索并提取出特定数据想想就令人望而生畏。

这一节演示了一种可扩展的从历史记录集合中提取特定记录的方法。为了使这个示例尽量简单，这里仅仅使用了很少的数值项目和 3 个历史记录文件。但是，这种方法具有很好的扩展性，你可以在更多的数值项目和更大的历史文件集合中使用这种方法。

至此，我们已经解决了在一个大的历史文件集合中搜索特定记录的问题。下面开始解决为一个未知数目的分类计算统计量的问题。这个目标此刻听起来有点抽象，我将在下一节对这个问题进行详细讨论，并给出解决方法。