读Python数据分析基础之Excel读写与处理
- 2019 年 10 月 8 日
- 筆記
对于业务型数据分析来说，Excel可以说是打交道最多的软件了，可以说没有之一。之前有比较系统地读过《Python数据分析基础》（Foundations for Analysis with Python），写了一些笔记，这里只选取关于Excel的部分。
这篇笔记不是讲各类Excel函数和快捷键，而是讲Python对Excel的 .xls 和 .xlsx 格式数据的读写和处理。《Python数据分析基础》第82页说：
Excel 是商业活动中不可或缺的工具，所以知道如何使用 Python 处理 Excel 数据可以使 你将 Python 加入到数据处理工作流中，进而从其他人那里接收数据，并以他们习惯接受的 方式分享数据处理结果。
目录
- Excel文件简介
- Excel文件的读取
- Excel文件的写入
- pandas库读写Excel
- 筛选与统计量计算
Excel文件简介
Excel其实相信大家都不陌生，一个 .xls（Excel 2013后默认格式为 .xlsx ）文件是一个工作簿（workbook），包含多个表（worksheet），每个表内数据按照行列进行组织，书中第三章的用词中，“文件”和“工作簿”表示同一个对象。拿本篇笔记用到的示例文件 sales_2015.xlsx 为例：
workbookAndWorkSheets
本篇笔记需要用到两个库： xlrd 和 xlwt，这两个库不是内置模块，但安装了Anaconda集成环境的话，这两个库被包含了，可以通过 import xlrd 和 import xlwt 进行测试，如果提示未安装，可以通过命令行下用pip安装，本处不展开。xlrd用来读取Excel文件，xlwt用于构建Workbook对象进行Excel文件的创建和写入数据。这两个库不能对Excel文件进行直接更改，因此思路是复制一份数据到内存进行分析计算，再写入新Excel文件中。需要直接性地修改可以考虑VBA吧，VBA（Visual Basic for Applications）是目前 Office 套件支持的基于 Visual Basic 的宏语言，目前一般在Excel或PPT内进行编程实现高级效果（如Excel数据的批量修改）一般都用VBA，当然以后Office要内置Python了，现在学好Python以后就能很容易理解和定制化Excel的宏了。
Excel文件的读取
通过xlrd库的open_workbook()对Excel数据进行读入。
#lrd读取并输出基本信息# -*- coding: utf-8 -*-def readExcel(fname): from xlrd import open_workbook workbook = open_workbook(fname) print('工作表数量:', workbook.nsheets) for worksheet in workbook.sheets(): #循环输出表名 print("Worksheet name:", worksheet.name, "tRows:",worksheet.nrows, "tColumns:", worksheet.ncols) readExcel('sales_2015.xlsx')
输出：
工作表数量: 3 Worksheet name: january_2015 Rows: 7 Columns: 5 Worksheet name: february_2015 Rows: 7 Columns: 5 Worksheet name: march_2015 Rows: 7 Columns: 5
Excel文件的写入
写入要用到 xlwt 库，如下面的代码，创建一个Workbook对象后，通过worksheet.write()写入数据。因为使用with进行处理，所以不需要写close()显式关闭打开的文件。
#读取一些Excel并写入新表def parsingExcToWrite(in_file,out_file): from xlrd import open_workbook from xlwt import Workbook #导入一个 Workbook对象 output_workbook = Workbook() output_worksheet = output_workbook.add_sheet('jan_2015_output') #加一个新工作表到工作簿对象里 with open_workbook(in_file) as workbook: worksheet = workbook.sheet_by_name('january_2015') #引用上面创建的工作表 print('type(worksheet):',type(worksheet)) print('write()传入参数类型:',type(worksheet.cell_value)) 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(out_file) in_f='sales_2015.xlsx'out_f='save_sales_2015.xlsx'parsingExcToWrite(in_f,out_f) #调用
输出：
type(worksheet): <class 'xlrd.sheet.Sheet'>write()传入参数类型: <class 'method'>
生成的文件效果如下：
save_sales_by_xlwt
上面写成的函数parsingExcToWrite()就是是一个可以使用在读取、处理、写入的框架，如前所说，这两个库不能直接对Excel进行修改，那么读取相应的数据处理后再写入新表就是很好的解决方案。 下面的处理代码如果需要保存处理后的数据就可以基于这段代码进行扩展。
pandas库读写Excel
下面看看用pandas进行Excel读取的操作， 读只需要一句话（引入库的不算在内），pd.read_excel(in_fname)，和前一篇笔记读取csv的格式一样，都是生成dataframe数据格式。写入Excel通过pd.ExcelWriter()构建一个Excel写入对象，再对这个对象操作，最后调用 .save()进行写入到硬盘。
import pandas as pd in_f='sales_2015.xlsx'out_f='save_sales_2015_1.xlsx'data_frame = pd.read_excel(in_f, 'january_2015', index_col=None) #对 data_frame进行一些处理dframe_condition = data_frame[:] writer = pd.ExcelWriter(out_f) dframe_condition.to_excel(writer, sheet_name='sheet_name',index=False) writer.save() print('done')#输出： done
筛选与统计量计算
因为pandas可以简化一些操作，并且多练pandas是很有意义很重要的，所以下面筛选和统计量的计算都是基于pandas的处理。
有些时候，我们并不需要 Excel 文件中的所有行，特别是数据量很大但是我们只关心满足一定条件的数据。例如，可能只需要包含一个特定的词数值的那些行，或者只需要那些与一个具体日期相关联的行数据。这时候我们就需要进行筛选，去掉不需要的行，只保留需要的行。
下面的代码演示了筛选 Sale Amount 大于 $567.00 的行。我们可以通过改变代码data_frame_value_meets_condition = data_frame[data_frame['Sale Amount'].astype(float) > 567.0] 来筛选行中的值满足某个条件的数据。
#import pandas as pd #上面引入了这里就不需要重复引入，如果是独立的文件需要写上这句def valConditionExc(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) data_frame_value_meets_condition = data_frame[data_frame['Sale Amount'].astype(float) > 567.0] writer = pd.ExcelWriter(out_file) data_frame_value_meets_condition.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save()#行中的值匹配于特定模式def valMatchPattern(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) df_value_matp = data_frame[data_frame['Customer Name'].str.startswith("J")] writer = pd.ExcelWriter(out_file) df_value_matp.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save() print(df_value_matp)#选择满足一定条件的特定列数据def selectColByIndex(in_file,out_file): data_frame = pd.read_excel(input_file, 'january_2015', index_col=None) df_col_by_index = data_frame.iloc[:, [1, 4]] writer = pd.ExcelWriter(output_file) df_col_by_index.to_excel(writer, sheet_name='jan_15_output',index=False) writer.save()in_f='sales_2015.xlsx'valConditionExc(in_f,'save_sales_2015_2.xlsx') valMatchPattern(in_f,'save_sales_2015_3.xlsx')
输出：
Customer ID Customer Name Invoice Number Sale Amount Purchase Date0 1234 John Smith 100-0002 123 2015-01-014 5678 Jenny Walters 100-0006 345 2015-01-24
使用 pandas 基于列标题选取特定列，一种方式是在数据框名称后面的方括号中将列名以字符串方式列出。另外一种方式是使用 loc 函数。如果使用 loc 函数，那么需要在列标题列表前面加上一个冒号和一个逗号，表示你想为这些特定的列保留所有行。例如下面的代码：
#import pandas as pd def selectAllColByName(in_file,out_file): data_frame = pd.read_excel(in_file, 'january_2015', index_col=None) selected_columns = data_frame.loc[:, ['Customer ID', 'Purchase Date']] #根据列标题选取特定列 writer = pd.ExcelWriter(out_file) selected_columns.to_excel(writer, sheet_name='jan_13_output',index=False) print(selected_columns) writer.save() in_f='sales_2015.xlsx'selectAllColByName(in_f,'save_sales_2015_4.xlsx')
输出：
Customer ID Purchase Date0 1234 2015-01-011 2345 2015-01-062 3456 2015-01-113 4567 2015-01-184 5678 2015-01-245 6789 2015-01-31
大家可以根据代码的效果区分上上部分代码的data_frame.iloc[:, [1, 4]] 和上面的data_frame.loc[:, ['Customer ID', 'Purchase Date']]
.loc for label based indexing .iloc for positional indexing
数据装入pandas的dataframe之后，除了进行筛选，计算一些统计量也是数据分析很重要的工作，描述性统计给我们提供了很多描述数据的指标，下面的代码为工作表的销售数据计算总数和均值。
#import pandas as pd def getSumAndAverage(in_f): all_worksheets = pd.read_excel(in_f,sheetname=None, index_col=None) workbook_total_sales = [] workbook_number_of_sales = [] workbook_mean_sales = [] for worksheet_name, w_data in all_worksheets.items(): total_sales = pd.DataFrame([float(str(value).strip('$').replace(',','')) for value in w_data.loc[:, 'Sale Amount']]).sum() #算一个表的总体销售额 number_of_sales = len(w_data.loc[:, 'Sale Amount']) workbook_total_sales.append(total_sales) #装入一个列表 workbook_number_of_sales.append(number_of_sales) mean_sales=total_sales/number_of_sales #均值 workbook_mean_sales.append(mean_sales) print(worksheet_name,'t total:',total_sales[0],'t num:',number_of_sales,'t mean:',mean_sales[0]) in_f='sales_2015.xlsx'getSumAndAverage(in_f)
输出：
january_2015 total: 3201.0 num: 6 mean: 533.5february_2015 total: 55007.0 num: 6 mean: 9167.83333333march_2015 total: 246045.0 num: 6 mean: 41007.5
《Python数据分析基础》第三章讲了Excel文件的读写和处理，里面提供了xlrd、xlwt进行读写处理以及对应的pandas库进行读写和处理，本篇笔记基本覆盖了第三章的重点内容，缩减了一些例子，在处理的部分都是用pandas库进行，书中还提供了xlrd、xlwt进行处理的代码，代码比较长，需要理解这部分内容请阅读原书。下一章进入数据库的内容，数据库也是数据分析师经常要操作的工具。之后在可视化部分中还会经常用到pandas，读取数据后进行可视化是很美好的事。
