Python编程进阶02-Excel文件操作实战

 Python编程进阶01--输入输出文件操作

Outline:

1、Excel文件格式说明（工作簿、工作表、单元格）

2、openpyxl库操作（常用操作、优缺点）

3、人口数据处理实例（人口普查数据统计整理）

一、Excel文件格式说明

• 一个Excel电子表格文档称为一个工作簿（workbook），一个工作簿保存在扩展名为.xlsx的文件中。
• 每个工作簿都可以包含多个表（Sheet）。
• 用户当前查看的表（或关闭Excel前最后查看的表），称为活动表。
• 每个表都有一些列（地址是从A开始的字母）和一些行（地址是从1开始的数字）
• 在特定行和列的方格称为单元格。
• 每个单元格都包含一个数字或文本值。

至于Excel实操我想就不必了吧，大学生你连Excel基础操作都不会建议回去读初中，如果你是初中生，建议好好学习，扎好基础再来尝试机器学习。

二、openpyxl库操作

【openpyxl库】

【openpyxl库特点】

• 用于读取/写入Excel 2010以后的 xlsx/xlsm/xltx/xltm文件的Python库。
• 使用getter/setter模式。你可以随时读取某个单元格的内容，并根据其内容进行相应的修改，openpyxl会帮助你记住每个人单元格的状态。
• 虽然它支持修改已有文件，但由于其所支持的功能有限，读入文件时会忽略掉它所不支持的内容，再写入时，这些内容就丢失了。

【openpyxl库缺点】

• 不支持07版本以前的XLS格式。
• 不支持Excel中的公式。

安装openpyxl库可以在Anaconda Prompt(anaconda)打开命令行输入一下语句进行安装，我的因为已经安装过了，可能不太一样，但是能正常安装就行。

使用的时候导入语句也很简单

import openpyxl

• 用openpyxl库打开Excel文档

wb = openpyxl.load_workbook()

• 从工作簿中取得工作表

get_sheet_names()

• 从表中取得行和列：可以将Worksheet对象切片，取得Excel表格中一行、一列或一个矩形区域中的所有Cell对象，然后可以循环遍历这个切片中的所有单元格。

【openpyxl库常用操作】

• 新建工作簿：新建工作簿不需要再系统中创建新文件，在内存中即可

from openpyxl import Workbook

# 工作簿实例化
wb = Workbook()

• 从工作簿激活工作表

# 激活获取工作表，此方法调用_active_sheet_index方法，默认索引为0，即第一个工作表
ws = wb.active

• 也可以使用create_sheet()方法创建工作表

# 默认在最后添加工作表
ws1 = wb.create_sheet()

# 在指定位置添加工作表，位置0，即第一个
ws2 = wb.create_sheet(0)

• 可以通过工作表名称获取工作表，以下两种方式效果相同

ws3 = wb["New Title"]
ws4 = wb.get_sheet_by_name("New Title")

• 通过get_sheet_names()获取工作簿所有工作表的名称，返回值为list类型

>> print(wb.get_sheet_names())
['Sheet2','New Title','Sheet1']

• 获取单元格

c = ws['A4']  # 利用坐标获取单元格，若单元格不存在，则被创建
ws['A4'] = 4  # 对单元格的值直接赋值

# 也可以通过以下两种方式获取单元格，效果相同
c = ws.cell('A4')

# 通过指定行和列，从1开始，不从0开始
d = ws.cell(row = 4,column = 1)

• 利用切片方式获取指定区域的单元格

cell_range = ws['A1':'C2']

• 获取单元格后，写数据

c = ws.cell('A4')
c.value = 'hello,world'

• 对实例化的工作簿调用openpyxl.workbook.Workbook.save()进行保存

wb.save('sample.xlsx')

三、人口普查数据处理实例

【问题描述】

• 处理2010年美国人口普查数据文件censuspopdata.xlsx，Excel文件中只有一张表，名为'Population by Census Tract'。每一行都保存了一个普查区的数据。列分别是普查区的编号（A），州的简称（B），县的名称（C），普查区的人口（D）。

【题目要求】

• 编写程序，从人口普查Excel文件中读取数据，并计算每个县的人口统计值。
• 题目要求以字典嵌套字典方式存储统计信息，最外层字典的键是州，州里的键是县，县里的键是pop人口和人口普查区数量。

dict = {'州':{'县1':{'pop':9000,'人口普查区':2},},}

【求解步骤】

• 从Excel电子表格中读取数据
• 计算每个县中普查区的数目
• 计算每个县的总人口
• 打印结果

待处理文件自提链接：https://download.csdn.net/download/MARS_098/12093431

现在来分析下具体的实现代码：

# 导入基本库，pprint是一种美观输出的库，即将输出的数据按格式化
import openpyxl,pprint   
print('Opening worbook...')

# 打开文件时带上文件的绝对路径，不然会提示not exist
wb = openpyxl.load_workbook('E:\Python大战机器学习\第三部分 Python编程进阶\code 2、Excel文件处理实战\censuspopdata.xlsx')
# 通过工作表名称获取人口统计Sheet工作表
sheet = wb.get_sheet_by_name('Population by Census Tract')   
countyData = {}  # 定义统计结果保存字典
print('Reading row...')

for row in range(2,sheet.max_row+1):   # 从Excel文件第二行开始按行读取数据，至max_row行(即最后一行)。
    state = sheet['B' + str(row)].value   # 读取州名
    county = sheet['C' + str(row)].value  # 读取县名
    pop = sheet['D' + str(row)].value     # 读取人口数量值
    
    countyData.setdefault(state,{})  # 先定义一个州默认的字典
    countyData[state].setdefault(county,{'tracts':0,'pop':0}) # 在上面州里面定义一个默认的县字典
    
    countyData[state][county]['tracts'] += 1     # 该县的人口普查次数+1
    countyData[state][county]['pop'] += int(pop) # 该县所有普查次区人口数量增加
    
print('Writing results...')
resultFile = open('E:\Python大战机器学习\第三部分 Python编程进阶\code 2、Excel文件处理实战\census_result.py','w')
resultFile.write('allData = ' + pprint.pformat(countyData))
# pprint生成一个字符串、格式化好的、有效的python代码
resultFile.close()
print('Done.')

这里我把excel文件和生成文件放在同一个文件夹里面，运行成功后如下：

我们来看下生成的文件：

用文本文件格式打开census:

可以看出来，生成的是一个三层嵌套的字典，最外层键为州名，然后预支对应的值又是一个个嵌套，中层嵌套字典的键为县名，最内层的键分别为pop人口数和tracts普查区次

我们可以随便挑两个出来验证，比如第一个'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1}，我们在excel表格中验证，我们查找Aleutians East，发现确实普查区次确实只有一个，人数为3141。

再来找一个普查区次大于1的，在第三行'Bethel': {'pop': 17013, 'tracts': 3}，我们查找Bethel

发现确实普查区次为3，来计算一下数值之和，在excel表格中很容易用公式验证出人口总数为17013，正确。

今天和学院人工智能方向的老师讲了想跟他实践机器学习项目的想法，但是因为我准备读研方向不同，老师不建议我再浪费时间在机器学习上了，再过几天可能要开始重点记载更新网安的相关知识，一起加油。