Python实现Excel读写与处理本帖最后由Mr吴于2018-10-1610:16编辑本篇文章讲的是Excel文件的读

本帖最后由 Mr吴于 2018-10-16 10:16 编辑

本篇文章讲的是Excel文件的读写和处理。Excel除了是普通生活中应用广泛的数据处理软件和数据保存格式外，在商业分析中也占有非常大的重量，熟练使用Excel软件是数据分析师很关键的技能点，但这篇笔记不是讲各类Excel函数和快捷键，而是讲Python对Excel的 .xls 和 .xlsx 格式数据的读写和处理。
目录

Excel文件简介
Excel文件的读取
Excel文件的写入
pandas库读写Excel
筛选与统计量计算

Excel文件简介Excel其实相信大家都不陌生，一个 .xls（Excel 2013后默认格式为 .xlsx ）文件是一个工作簿（workbook），包含多个表（worksheet），每个表内数据按照行列进行组织，书中第三章的用词中，“文件”和“工作簿”表示同一个对象。拿本篇笔记用到的示例文件 s ales_2015.xlsx 为例：

本篇笔记需要用到两个库： 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数据进行读入。

[Python]

纯文本查看

复制代码

#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')

输出:

[Python]

纯文本查看

复制代码

工作表数量: 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()显式关闭打开的文件。

[Python]

纯文本查看

复制代码

#读取一些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) #调用

输出：

[Python]

纯文本查看

复制代码

type(worksheet): <class 'xlrd.sheet.Sheet'>

write()传入参数类型: <class 'method'>

生成的文件效果如下：

上面写成的函数parsingExcToWrite()就是是一个可以使用在读取、处理、写入的框架，如前所说，这两个库不能直接对Excel进行修改，那么读取相应的数据处理后再写入新表就是很好的解决方案。下面的处理代码如果需要保存处理后的数据就可以基于这段代码进行扩展。
pandas库读写Excel下面看看用pandas进行Excel读取的操作，读只需要一句话（引入库的不算在内），pd.read_excel(in_fname)，和前一篇笔记读取csv的格式一样，都是生成dataframe数据格式。写入Excel通过pd.ExcelWriter()构建一个Excel写入对象，再对这个对象操作，最后调用 .save()进行写入到硬盘。

[Python]

纯文本查看

复制代码

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] 来筛选行中的值满足某个条件的数据。

[Python]

纯文本查看

复制代码

#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')

输出：

[Python]

纯文本查看

复制代码

Customer ID Customer Name Invoice Number Sale Amount Purchase Date

0 1234 John Smith 100-0002 123 2015-01-01

4 5678 Jenny Walters 100-0006 345 2015-01-24

使用 pandas 基于列标题选取特定列，一种方式是在数据框名称后面的方括号中将列名以字符串方式列出。另外一种方式是使用 loc 函数。如果使用 loc 函数，那么需要在列标题列表前面加上一个冒号和一个逗号，表示你想为这些特定的列保留所有行。例如下面的代码：

[Python]

纯文本查看

复制代码

#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')

输出：

[Python]

纯文本查看

复制代码

Customer ID Purchase Date

0 1234 2015-01-01

1 2345 2015-01-06

2 3456 2015-01-11

3 4567 2015-01-18

4 5678 2015-01-24

5 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之后，除了进行筛选，计算一些统计量也是数据分析很重要的工作，描述性统计给我们提供了很多描述数据的指标，下面的代码为工作表的销售数据计算总数和均值。

[Python]

纯文本查看

复制代码

#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)

输出:

[Python]

纯文本查看

复制代码

january_2015 total: 3201.0 num: 6 mean: 533.5

february_2015 total: 55007.0 num: 6 mean: 9167.83333333

march_2015 total: 246045.0 num: 6 mean: 41007.5

更多学习资料可关注：annalin1203获取