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表单

简单示例

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
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方法说明

1
2
3
4
5
6
7
8
9
10
11
12
13
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文件,得到的结果是一个二维矩阵

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 默认读取第一个表单
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

1
2
3
4
5
6
7
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:数据导出的编码
1
2
3
4
5
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,使用下列的写法

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

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

1
2
3
4
5
6
7
data = pd.read_excel('1.xlsx') 
print('显示表格的属性:',data.shape) # 打印显示表格的属性,几行几列
print('显示表格的列名:',data.columns) # 打印显示表格有哪些列名
# head() 默认显示前5行,可在括号内填写要显示的条数
print('显示表格前三行:',data.head(1))
# tail() 默认显示后5行,可在括号内填写要显示的条数
print('显示表格后五行:',data.tail())
1
2
3
data = pd.read_excel('lemon.xlsx') 
data.columns=['ID','NAME'] # 设置表头
data.set_index('ID',inplace=True) # inplace=True表示 在当前表上修改。不用再新建表

三、pandas操作Excel的行列

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

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

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

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

3:读取指定的行列:

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

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

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

5:获取所有行的指定列

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

6:获取行号并打印输出

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

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

7:获取列名并打印输出

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

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

8:获取指定行数的值:

1
2
3
4
5
6
7
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:获取指定列的值:

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

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

1
2
3
4
5
6
7
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))

最后得到的结果是:

1
2
3
4
5
最终获取到的数据是:
[{'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"}'}]

Python 第三方模块之 pandas 操作 excel
https://flepeng.github.io/021-Python-31-Python-第三方模块-Python-第三方模块之-pandas-操作-excel/
作者
Lepeng
发布于
2021年4月27日
许可协议