Python数据分析基础之Excel文件(2)

2020-02-03

处理单个工作表——读写Excel文件

1.基础Python

  要使用基础Python读写Excel文件，我们需要导入xlrd和xlwt模块。
  对于上一节创建的Excel工作簿，我们试着来读取其中的january_2013工作表中的数据。代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/usr/bin/env python3

import sys
from xlrd import open_workbook
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    for row_index in range(worksheet.nrows):
        for column_index in range(worksheet.ncols):
            output_worksheet.write(row_index, column_index, worksheet.cell_value(row_index, column_index))
output_workbook.save(output_file)

  我们来解释一下上面的代码。 

1
2
from xlrd import open_workbook
from xlwt import Workbook

  这两行代码导入了xlrd模块的open_workbook()函数以及xlwt模块的Workbook对象。 

1
2
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')

  这里实例化Workbook对象，以使我们可以将结果写入用于输出的Excel文件。接下来一行，add_sheet()函数为输出工作簿添加了一个名为jan_2013_output的工作表。 

1
worksheet = workbook.sheet_by_name('january_2013')

  这行代码使用workbook对象的sheet_by_name()函数引用名称为january_2013的工作表。
  我们在命令行窗口中运行这个脚本，并打开输出文件查看结果。
在这里插入图片描述

2.pandas

  使用pandas模块的代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
#!/usr/bin/env python3

import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, sheet_name='january_2013')
writer = pd.ExcelWriter(output_file)
data_frame.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

  在命令行窗口中运行脚本后得到的输出文件如下：
在这里插入图片描述

处理单个工作表——筛选特定行

  和之前学习CSV文件一样，有的时候我们不需要Excel文件中的所有行，在这种情况下，我们可以使用Python来对工作表中的行进行筛选。

1.1 行中的值满足某个条件（基础Python）

  比如说，我们想对之前的工作表中的行进行筛选，保留Sale Amount大于$1,400.00的行。代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/usr/bin/env python3

import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
sale_amount_column_index = 3
with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    header = worksheet.row_values(0)
    data.append(header)
    for row_index in range(1, worksheet.nrows):
        row_list = []
        sale_amount = worksheet.cell_value(row_index, sale_amount_column_index)
        sale = worksheet.cell_value(row_index, sale_amount_column_index)
        sale_amount = float(str(sale).strip('$').replace(',', ''))
        if sale_amount > 1400.0:
            for column_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index, column_index)
                cell_type = worksheet.cell_type(row_index, column_index)
                if cell_type == 3:
                    date_cell = xldate_as_tuple(cell_value, workbook.datemode)
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
        if row_list:
            data.append(row_list)
    for list_index, output_list in enumerate(data):
        for element_index, element in enumerate(output_list):
            output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)

  我们来解释一下上面的代码。 

1
2
from datetime import date
from xlrd import open_workbook, xldate_as_tuple

  这里我们导入了datetime模块的date()函数，以使我们可以将数值转换成日期并对日期进行格式化。xlrd模块中的xldate_as_tuple()函数可以将Excel中代表日期、时间或日期时间的数值转换为元组。将数值转换成元组可以提取出具体时间元素，并将时间元素格式化成不同的时间格式。 

1
data = []

  这行代码创建了一个空列表data，我们将用要写入输出文件的行来填充它。 

1
sale_amount = worksheet.cell_value(row_index, sale_amount_column_index)

  变量sale_amount用来保存行中的销售额，作为检验销售额是否大于$1,400.00的条件。cell_value()函数使用sale_amount_column_index的值来定位Sale Amount列。 

1
2
3
4
5
6
7
8
9
for column_index in range(worksheet.ncols):
    cell_value = worksheet.cell_value(row_index, column_index)
    cell_type = worksheet.cell_type(row_index, column_index)
    if cell_type == 3:
        date_cell = xldate_as_tuple(cell_value, workbook.datemode)
        date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
        row_list.append(date_cell)
    else:
        row_list.append(cell_value)

  这里创建了一个for循环，来处理Sale Amount大于$1,400.00的那些行。我们先用cell_value()函数来提取出每个单元格的值，赋给变量cell_value，再用cell_type()函数提取出每个单元格的类型，赋给变量cell_type。然后，检验行中的每个值是否为日期类型（即检验cell_type是否等于3），如果是，就将这个值格式化成日期数据，追加进前面创建的空列表row_list中。如果cell_type不是3，则说明其为非日期类型数据，直接追加进列表row_list即可。

xlrd模块的说明文档（https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966#sheet.Cell-class）中写到，单元格类型为3表示这个单元格中包含日期数据。 

1
2
if row_list:
    data.append(row_list)

  我们为输入文件的每一行都创建空列表row_list，但是只使用值填充某些空列表。对于输入文件的每一行，这里的if语句判断row_list是否为空，如果非空，则将其添加到data中。 

1
2
3
for list_index, output_list in enumerate(data):
    for element_index, element in enumerate(output_list):
        output_worksheet.write(list_index, element_index, element)

  我们在data中的各个列表之间和列表中的各个值之间进行迭代，将这些值写入输出文件。
  将要保留的行追加到data中的原因是，这样可以得到新的连续的行索引值。于是，当我们将这些行写入输出文件时，它们看上去就像是一个连续的整体，行与行之间不会出现缺口。否则，如果在主体for循环中处理各行的时候就将它们写入输出文件的话，那么xlwt的write()函数就会使用输入文件中原来的行索引值将行写入输出文件，造成行与行之间的缺口。
  我们在命令行窗口中运行这个脚本，并打开输出文件查看结果。
在这里插入图片描述

1.2 行中的值满足某个条件（pandas）

  使用pandas模块的代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/usr/bin/env python3

import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_value_meets_condition = data_frame['Sale Amount'] = data_frame['Sale Amount'].str.replace(r'\$', '')
data_frame['Sale Amount'] = data_frame['Sale Amount'].str.replace(',', '')
data_frame['Sale Amount'] = data_frame['Sale Amount'].astype(float)
data_frame_value_meets_condition = data_frame[data_frame['Sale Amount'].astype(float) > 1400.0]
writer = pd.ExcelWriter(output_file)
data_frame_value_meets_condition.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

  在命令行窗口中运行脚本，得到的输出文件如下：
在这里插入图片描述

2.1 行中的值属于某个集合（基础Python）

  接下来，我们来筛选出january_2013工作表中购买日期属于1/24/2013和1/31/2013的行。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/usr/bin/env python3

import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
important_dates = ['01/24/2013', '01/31/2013']
purchase_date_column_index = 4
with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    header = worksheet.row_values(0)
    data.append(header)
    for row_index in range(1, worksheet.nrows):
        purchase_datetime = xldate_as_tuple(worksheet.cell_value(row_index, purchase_date_column_index),
                                            workbook.datemode)
        purchase_date = date(*purchase_datetime[0:3]).strftime('%m/%d/%Y')
        row_list = []
        if purchase_date in important_dates:
            for column_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index, column_index)
                cell_type = worksheet.cell_type(row_index, column_index)
                if cell_type == 3:
                    date_cell = xldate_as_tuple(cell_value, workbook.datemode)
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
        if row_list:
            data.append(row_list)
    for list_index, output_list in enumerate(data):
        for element_index, element in enumerate(output_list):
            output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)

在跟着书上的代码学习时，书上的代码有一些印刷错误，这让我踩了不少的坑。上面的代码是在PyCharm 2019.3.2(Professional Edition) + Anaconda环境下成功运行的。感谢实验室的学长的指导。

  这个脚本与基于条件筛选行的脚本非常相似，这里只对有差别的部分做一下简单的解释。 

1
important_dates = ['01/24/2013', '01/31/2013']

  这行代码创建了列表important_dates，里面包含了要使用的日期。这里需要注意的一点是，日期不能写成类似于’1/24/2013’的格式，否则会出问题！
  在命令行窗口中运行这个脚本，得到输出文件如下：
在这里插入图片描述

2.2 行中的值属于某个集合（pandas）

  如果使用pandas模块的话，我们想筛选出特定的日期，需要用到函数isin()。代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/usr/bin/env python3

import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
important_dates = ['01/24/2013', '01/31/2013']
data_frame_value_in_set = data_frame[data_frame['Purchase Date'].isin(important_dates)]
writer = pd.ExcelWriter(output_file)
data_frame_value_in_set.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

  在命令行窗口中运行这个脚本，得到输出文件。
在这里插入图片描述

3.1 行中的值匹配于特定模式（基础Python）

  要想使行中的值匹配于特定模式，我们需要引入正则表达式。下面的代码将会筛选出表格中客户姓名以大写字母’J’开头的行。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#!/usr/bin/env python3

import re
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
pattern = re.compile(r'(?P<my_pattern>^J.*)')
customer_name_column_index = 1
with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    header = worksheet.row_values(0)
    data.append(header)
    for row_index in range(1, worksheet.nrows):
        row_list = []
        if pattern.search(worksheet.cell_value(row_index, customer_name_column_index)):
            for column_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index, column_index)
                cell_type = worksheet.cell_type(row_index, column_index)
                if cell_type == 3:
                    date_cell = xldate_as_tuple(cell_value, workbook.datemode)
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
        if row_list:
            data.append(row_list)
    for list_index, output_list in enumerate(data):
        for element_index, element in enumerate(output_list):
            output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)

  这里我们只对正则表达式的部分做一个说明。 

1
pattern = re.compile(r'(?P<my_pattern>^J.*)')

  这行代码使用re模块的compile()函数创建了一个正则表达式pattern。其中，r表示单引号之间的模式是一个原始字符串。元字符?P<my_pattern>捕获了名为<my_pattern>的组中匹配了的子字符串。我们要搜索的实际模式是^J.*。插入符号^表示在字符串开头搜索模式，句点.可以匹配任何除了换行符\n的字符，星号*则表示重复前面的字符0次或更多次。.*组合在一起使用表示除换行符\n之外的任意字符可以在J后面出现任意次。
  有关正则表达式的内容，可以参考我之前写的博客：Python与正则表达式。
  我们在命令行窗口中运行这个脚本，得到输出文件。
在这里插入图片描述

3.2 行中的值匹配于特定模式（pandas）

  pandas模块提供了若干字符串和正则表达式函数，如startswith(), endswith(), match()和search()等。代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
#!/usr/bin/env python3

import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_value_matcher_pattern = data_frame[data_frame['Customer Name'].str.startswith("J")]
writer = pd.ExcelWriter(output_file)
data_frame_value_matcher_pattern.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

  在命令行窗口中运行脚本，得到输出文件如下：
在这里插入图片描述