Python 第三方模块之 pandas 操作 excel

python 解析 excel 对比

包	版本	xls读	xlsx读	xls写	xlsx写	备注
xlrd	1.1.0（2017年8月22日）	√	√			2.0 之后不支持xlsx
xlwt	1.3.0（2017年8月22日）			√
openpyxl	2.6.2（2019年3月29日）		√		√
XlsxWriter	1.2.1（2019年9月14日）				√
xlutils	2.0.0（2019年6月9日）	√	√	√		需xlrd/xlwt配合
pandas	0.25.1（2019年8月22日）	√	√	√	√	需xlrd/xlwt/openpyxl/xlsxwriter配合

简介

Pandas是python的一个数据分析包，纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。

Pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

Pandas官方文档：https://pandas.pydata.org/pandas-docs/stable/

Pandas中文文档：https://www.pypandas.cn

安装

1. pandas依赖处理Excel的xlrd模块，所以我们需要提前安装 xlrd，命令：pip install xlrd

2. 安装pandas模块还需要一定的编码环境，所以我们自己在安装的时候，确保你的电脑有这些环境：Net.4 、VC-Compiler以及winsdk_web。

3. 步骤1和2 准备好了之后，就可以开始安装pandas了，命令：pip install pandas

pandas操作Excel表单

简单示例

import pandas as pd
from pandas import DataFrame

# 读
data = pd.read_excel('1.xlsx')

# 查看所有的值
print(data.values)

# 查看第一行的值
print(data.values[0])

# 查看某一列所有的值
print(data['标题列1'].values)

# 新增列
data['标题列3'] = None

# 新增行
data.loc[3] = ['王五', 100, '男']

# 删除行：axis=0
data = data.drop([0,1], axis=0)

# 删除列：axis=1
data.drop('标题列3', axis=1)

# 保存
DataFrame(data).to_excel('1.xlsx', sheet_name='Sheet1', index=False, header=True)

读取excel

read_excel方法说明

pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, 
              usecols=None, squeeze=False,dtype=None, engine=None, 
              converters=None, true_values=None, false_values=None, 
              skiprows=None, nrows=None, na_values=None, parse_dates=False, 
              date_parser=None, thousands=None, comment=None, skipfooter=0, 
              convert_float=True, **kwds)

io：excel文件
sheet_name：返回指定sheet，默认索引0返回第一个，也可用名称，如果返回多个则可用列表，为None则返回全表
header：指定表头，也可用列表指定多行
names：自定义列名，长度和Excel列长度必须一致
index_col：用作索引的列
usecols：读取指定的列，参数为列表，如[0,1]表示第1和第2列

读取Excel文件，得到的结果是一个二维矩阵

# 默认读取第一个表单
df=pd.read_excel('1.xlsx')

# 可以通过sheet_name来指定读取的表单
df=pd.read_excel('1.xlsx',sheet_name='student')

# 表格首行为无效数据 或 表格首行为空 ，header=1
rdexcle = pd.read_excel('1.xlsx',header=1)

data=df.head() # 默认读取前5行的数据
print("获取到所有的值:\n{0}".format(data)) # 格式化输出


# 通过表单索引来指定要访问的表单，0表示第一个表单
# 也可以采用表单名和索引的双重方式来定位表单
# 也可以同时定位多个表单，方式都罗列如下所示
df=pd.read_excel('1.xlsx',sheet_name=['python','student']) # 可以通过表单名同时指定多个
# df=pd.read_excel('1.xlsx',sheet_name=0) # 可以通过表单索引来指定读取的表单
# df=pd.read_excel('1.xlsx',sheet_name=['python',1]) # 可以混合的方式来指定
# df=pd.read_excel('1.xlsx',sheet_name=[1,2])  # 可以通过索引 同时指定多个
data=df.values # 获取所有的数据，注意这里不能用head()方法哦~
print("获取到所有的值:\n{0}".format(data))

写 excel

pd.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

excel_writer：数据存储路径，含文件全名例如'./data.xlsx'
sheet_name：表示数据存储的工作簿名称
header：是否导出列名，True导出，False不导出
index： 是否导出索引，True导出，False不导出
encoding：数据导出的编码

file_name = 'a.xlsx'
sheet_name0 = '1'
sheet_name1 = '2'
df0.to_excel(file_name, sheet_name0, index=False)
df1.to_excel(file_name, sheet_name1, index=False)

但是上述代码只能保存最后 1 次 df1 的数据 。

把多个 df 分别写入到一个 Excel 文件的不同 sheet，使用下列的写法

with pd.ExcelWriter(file_name) as writer:
    df0.to_excel(writer, sheet_name0, index=False)
    df1.to_excel(writer, sheet_name1, index=False)

查看属性、列名、显示前几行、后几行

data = pd.read_excel('1.xlsx') 
print('显示表格的属性:',data.shape)   # 打印显示表格的属性，几行几列
print('显示表格的列名:',data.columns) # 打印显示表格有哪些列名
# head() 默认显示前5行，可在括号内填写要显示的条数
print('显示表格前三行:',data.head(1)) 
# tail() 默认显示后5行，可在括号内填写要显示的条数
print('显示表格后五行:',data.tail())

data = pd.read_excel('lemon.xlsx') 
data.columns=['ID','NAME']         # 设置表头
data.set_index('ID',inplace=True)  # inplace=True表示 在当前表上修改。不用再新建表

三、pandas操作Excel的行列

1：读取指定的单行，数据会存在列表里面

# 读取指定行
df=pd.read_excel('lemon.xlsx')  # 这个会直接默认读取到这个Excel的第一个表单
data=df.ix[0].values#0表示第一行 这里读取数据并不包含表头，要注意哦！
print("读取指定行的数据：\n{0}".format(data))

2：读取指定的多行，数据会存在嵌套的列表里面：

df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2]].values  # 读取指定多行的话，就要在ix[]里面嵌套列表指定行数
print("读取指定行的数据：\n{0}".format(data))

3：读取指定的行列：

df=pd.read_excel('lemon.xlsx')
data=df.ix[1,2]#读取第一行第二列的值，这里不需要嵌套列表
print("读取指定行的数据：\n{0}".format(data))

4：读取指定的多行多列值：

df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2],['title','data']].values#读取第一行第二行的title以及data列的值，这里需要嵌套列表
print("读取指定行的数据：\n{0}".format(data))

5：获取所有行的指定列

df=pd.read_excel('lemon.xlsx')
data=df.ix[:,['title','data']].values#读所有行的title以及data列的值，这里需要嵌套列表
print("读取指定行的数据：\n{0}".format(data))

6：获取行号并打印输出

df=pd.read_excel('lemon.xlsx')
print("输出行号列表",df.index.values)

输出结果是：
输出行号列表 [0 1 2 3]

7：获取列名并打印输出

df=pd.read_excel('lemon.xlsx')
print("输出列标题",df.columns.values)

运行结果如下所示：
输出列标题 ['case_id' 'title' 'data']

8：获取指定行数的值：

df=pd.read_excel('lemon.xlsx')
print("输出值",df.sample(3).values)#这个方法类似于head()方法以及df.values方法

输出值
 [[2 '输入错误的密码' '{"mobilephone":"18688773467","pwd":"12345678"}']
 [3 '正常充值' '{"mobilephone":"18688773467","amount":"1000"}']
 [1 '正常登录' '{"mobilephone":"18688773467","pwd":"123456"}']]

9：获取指定列的值：

df=pd.read_excel('lemon.xlsx')
print("输出值\n",df['data'].values)

四：pandas处理Excel数据成为字典

df=pd.read_excel('lemon.xlsx')
test_data=[]
for i in df.index.values:#获取行号的索引，并对其进行遍历：
    #根据i来获取每一行指定的数据 并利用to_dict转成字典
    row_data=df.ix[i,['case_id','module','title','http_method','url','data','expected']].to_dict()
    test_data.append(row_data)
print("最终获取到的数据是：{0}".format(test_data))

最后得到的结果是：

最终获取到的数据是：
[{'title': '正常登录', 'case_id': 1, 'data': '{"mobilephone":"18688773467","pwd":"123456"}'}, 
{'title': '输入错误的密码', 'case_id': 2, 'data': '{"mobilephone":"18688773467","pwd":"12345678"}'}, 
{'title': '正常充值', 'case_id': 3, 'data': '{"mobilephone":"18688773467","amount":"1000"}'}, 
{'title': '充值输入负数', 'case_id': 4, 'data': '{"mobilephone":"18688773467","amount":"-100"}'}]