pandas操作Excel文件

一、前言

在python语言中，相较于其他操作Excel文件的工具包，pandas提供了高层次的数据操作接口，读取Excel中的数据更方便，且DataFrame数据结构可与numpy数组相互转换，便于后续数据处理与保存。

二、指定读取的工作表与header设置

2.1指定工作表

pandas可以根据工作表的名称或索引指定读取工作表，也可以将工作簿中的工作表全部读取，示例代码如下：

import pandas as pd

excel_data = pd.read_excel('data.xlsx', sheet_name = 'Sheet1')#指定读取名为Sheet1的工作表
excel_data = pd.read_excel('data.xlsx', sheet_name = 1)#指定读取第二个工作表，如只有一个工作表则报错
excel_data = pd.read_excel('data.xlsx', sheet_name = None)#读取所有工作表，返回一个字典，字典中键名为工作表名称，键值为DataFrame格式的工作表内容
excel_data = pd.read_excel('data.xlsx')#不指定读取的工作表时，默认读取第一个工作表

2.2header设置

以读取data.xlsx工作簿的Sheet1工作表为例，表格内容如下图，

示例代码如下：

import pandas as pd

excel_data = pd.read_excel('data.xlsx')
data = excel_data.iloc[0:3, 0:1]#索引从0开始，读取第一行至第三行、第一列的数据
print(data)#输出如下
   0.53
0  0.45
1  0.66
2  0.72

data = excel_data.iloc[1:3, 0:1]#读取第二行至第三行、第一列的数据
print(data)#输出如下
   0.53
1  0.66
2  0.72

上述读取Excel指定区域的代码，默认将读取的excel文件中的第一行数据当做了列标签，所以读取的第一行数据其实是Excel中的第二行数据，即0.45而不是0.53。注意0.45前的0为索引，即认为是第一行数据，而0.53前没有索引，将其认为是列的标签。

如果Excel中第一行不是列标签，那么可以通过如下代码设置取消掉header标识，

excel_data = pd.read_excel('data.xlsx', header = None)
data = excel_data.iloc[0:3, 0:1]#索引从0开始，读取第一行至第三行、第一列的数据
print(data)#输出如下
      0
0  0.53
1  0.45
2  0.66

data = excel_data.iloc[1:3, 0:1]#读取第二行至第三行、第一列的数据
print(data)#输出如下
      0
1  0.45
2  0.66

0.53前索引为0，即认为是第一行数据，0.45前索引为1，即认为是第二行数据，与Excel文件中的实际内容相吻合。

三、读取Excel数据

3.1iloc读取数据

使用iloc函数读取Excel指定区域数据的语法几乎与numpy二维数组的读取语法完全相同，如果对numpy数组切片读取很熟悉那么对iloc的用法可以快速掌握。

iloc读取数据时，先指定行，再指定列，索引从0开始，可指定读取一块区域数据，也可指定读取整行或整列数据，示例代码如下：

excel_data = pd.read_excel('data.xlsx', header = None)
data = excel_data.iloc[0:5, 0:4]#读取第一行至第五行、第一列至第四列的数据
print(data)#输出如下
      0     1     2     3
0  0.53  0.42  0.46  0.63
1  0.45  0.63  0.60  0.56
2  0.66  0.54  0.79  0.61
3  0.72  0.49  0.68  0.43
4  0.73  0.49  0.67  0.66

data = excel_data.iloc[0:, 0:4]#读取第一列至第四列整列的数据，行中如有空格则会被读取为NaN值
data = excel_data.iloc[:, 0:4]#与上行代码结果相同
print(data)#输出如下
        0      1      2      3
0    0.53   0.42   0.46   0.63
1    0.45   0.63   0.60   0.56
2    0.66   0.54   0.79   0.61
3    0.72   0.49   0.68   0.43
4    0.73   0.49   0.67   0.66
5    0.62   0.58   0.80   0.34
6    0.30   0.53   0.44   0.59
7    0.52   0.63   0.56   0.46
8    0.57   0.40   0.52   0.76
9    0.72   0.62   0.33   0.59

data = excel_data.iloc[0:5, 0:]#读取第一行至第五行整行的数据，列中如有空格则会被读取为NaN值
data = excel_data.iloc[0:5, ]#与上行代码结果相同
data = excel_data.iloc[0:5]#与上行代码结果相同
print(data)#输出如下
        0      1      2      3
0    0.53   0.42   0.46   0.63
1    0.45   0.63   0.60   0.56
2    0.66   0.54   0.79   0.61
3    0.72   0.49   0.68   0.43
4    0.73   0.49   0.67   0.66

3.2read_excel读取数据

read_excel是读取Excel文件时调用的函数方法，返回的是包含工作表全部内容的DataFrame结构数据，可以通过设置read_excel的参数读取整行或整列数据，但无法像iloc那样可以灵活读取指定区域的数据，示例代码如下：

data = pd.read_excel('data.xlsx', header = None, skiprows = 2, nrows = 2)#跳过前两行，读取第三行和第四行整行数据
print(data)#输出如下
        0      1      2      3
0    0.66   0.54   0.79   0.61
1    0.72   0.49   0.68   0.43

data = pd.read_excel('data.xlsx', header = None, usecols = [0, 2])#读取第一列和第三列整列数据
data = pd.read_excel('data.xlsx', header = None, usecols = 'A,C')#与上行代码结果相同
print(data)#输出如下
        0      2
0    0.53   0.46
1    0.45   0.60
2    0.66   0.79
3    0.72   0.68
4    0.73   0.67
5    0.62   0.80
6    0.30   0.44
7    0.52   0.56
8    0.57   0.52
9    0.72   0.33

data = pd.read_excel('data.xlsx', header = None, usecols = 'A:C')#读取第一列至第三列整列数据
print(data)#输出如下
        0      1      2
0    0.53   0.42   0.46
1    0.45   0.63   0.60
2    0.66   0.54   0.79
3    0.72   0.49   0.68
4    0.73   0.49   0.67
5    0.62   0.58   0.80
6    0.30   0.53   0.44
7    0.52   0.63   0.56
8    0.57   0.40   0.52
9    0.72   0.62   0.33

3.3loc读取数据

loc函数是基于行列标签读取数据，如果工作表中存在行列标签，就可以通过指定标签读取数据，但行列标签名必须唯一不能重复，否则会报错，也可以临时增加列标签和行标签（pandas中称为行索引）。

读取整列数据示例代码如下：

excel_data = pd.read_excel('data.xlsx')
data = excel_data.loc[:, 0.42]#读取以0.42为列标签的整列数据
data = excel_data[0.42]#与上行代码结果相同
print(data)#输出如下
0      0.63
1      0.54
2      0.49
3      0.49
4      0.58
5      0.53
6      0.63
7      0.40
8      0.62

data = excel_data.loc[:, 0.42:0.63]#读取从列标签0.42至0.63的整列数据
print(data)#输出如下
     0.42   0.46   0.63
0    0.63   0.60   0.56
1    0.54   0.79   0.61
2    0.49   0.68   0.43
3    0.49   0.67   0.66
4    0.58   0.80   0.34
5    0.53   0.44   0.59
6    0.63   0.56   0.46
7    0.40   0.52   0.76
8    0.62   0.33   0.59

data = excel_data.loc[:, [0.42, 0.63]]#读取从列标签0.42、0.63的整列数据
print(data)#输出如下
     0.42   0.63
0    0.63   0.56
1    0.54   0.61
2    0.49   0.43
3    0.49   0.66
4    0.58   0.34
5    0.53   0.59
6    0.63   0.46
7    0.40   0.76
8    0.62   0.59

excel_data.columns = ['column01', 'column02', 'column03', 'column04']#临时增加列标签，但会覆盖掉之前已有的列标签
data = excel_data.loc[:, 'column01':'column03']#读取从列标签column01至column3的整列数据
print(data)#输出如下
   column01  column02  column03
0      0.45      0.63      0.60
1      0.66      0.54      0.79
2      0.72      0.49      0.68
3      0.73      0.49      0.67
4      0.62      0.58      0.80
5      0.30      0.53      0.44
6      0.52      0.63      0.56
7      0.57      0.40      0.52
8      0.72      0.62      0.33

读取整行数据示例代码如下：

excel_data = pd.read_excel('data.xlsx', index_col = 0)#将第一列设置为行标签
data = excel_data.loc[0.45]#读取以0.45为行标签的整行数据
print(data)#输出如下
0.42            0.630
0.46            0.600
0.63            0.560

data = excel_data.loc[0.45:0.66]#读取行标签从0.45至0.66的整行数据
print(data)#输出如下
      0.42  0.46  0.63
0.53
0.45  0.63  0.60  0.56
0.66  0.54  0.79  0.61

data = excel_data.loc[0.45, 0.42]#读取行标签为0.45，列标签为0.42的单元格数据
print(data)#输出如下
0.63

excel_data.index=['row01', 'row02', 'row03', 'row04', 'row05', 'row06', 'row07', 'row08', 'row09']#临时增加行标签，但会覆盖掉之前已有的行标签
data = excel_data.loc['row01':'row05']#读取行标签从row01至row05的整行数据
print(data)#输出如下
       0.42  0.46  0.63
row01  0.63  0.60  0.56
row02  0.54  0.79  0.61
row03  0.49  0.68  0.43
row04  0.49  0.67  0.66
row05  0.58  0.80  0.34

注意，行列标签如果为数值形式，那么在索引时直接以数值作索引，如果标签名为字符串，需要对字符串加上单引号或双引号。

四、DataFrame数据筛选

DataFrame数据筛选主要有以下三类：

4.1根据列标签对整列进行筛选

示例代码如下：

excel_data = pd.read_excel('data.xlsx', header = None)
excel_data.columns = ['column01', 'column02', 'column03', 'column04']
data = excel_data[excel_data['column01'] > 0.7]#筛选出column01列大于0.7的整行数据
data = excel_data.query('column01 > 0.7')#与上行代码结果相同
print(data)#输出如下
   column01  column02  column03  column04
3      0.72      0.49      0.68      0.43
4      0.73      0.49      0.67      0.66
9      0.72      0.62      0.33      0.59

data = excel_data[excel_data['column01'].between(0.6, 0.7)]#筛选出column01列0.6与0.7之间的整行数据
data = excel_data.query('column01 >= 0.6 and column01 <= 0.7')#与上行代码结果相同
print(data)#输出如下
   column01  column02  column03  column04
2      0.66      0.54      0.79      0.61
5      0.62      0.58      0.80      0.34

excel_data['column01'][0]='abc'#将column01列的第一行单元格赋值为abc
data = excel_data[excel_data['column01'].str.contains('a', case = False, na = False)]#筛选column01列中包含a的整行数据
print(data)#输出如下
  column01  column02  column03  column04
0      abc      0.42      0.46      0.63

data = excel_data[excel_data['column01'].isin([0.30,0.45, 'abc'])]#筛选column01列中是否包含指定的值
print(data)#输出如下
  column01  column02  column03  column04
0      abc      0.42      0.46      0.63
1     0.45      0.63      0.60      0.56
6      0.3      0.53      0.44      0.59

4.2使用iloc对区域进行筛选

示例代码如下：

excel_data = pd.read_excel('data.xlsx', header = None)
data = excel_data.iloc[0:5, 0:4]#读取第一行至第五行、第一列至第四列的数据
print(data)#输出如下
      0     1     2     3
0  0.53  0.42  0.46  0.63
1  0.45  0.63  0.60  0.56
2  0.66  0.54  0.79  0.61
3  0.72  0.49  0.68  0.43
4  0.73  0.49  0.67  0.66

data = data[data > 0.7]#筛选出区域内大于0.7的数据
print(data)#输出如下
      0   1     2   3
0   NaN NaN   NaN NaN
1   NaN NaN   NaN NaN
2   NaN NaN  0.79 NaN
3  0.72 NaN   NaN NaN
4  0.73 NaN   NaN NaN

data = excel_data[excel_data.iloc[:, 0] > 0.7]#筛选出第一列大于0.7的整行数据
print(data)#输出如下
      0     1     2     3
3  0.72  0.49  0.68  0.43
4  0.73  0.49  0.67  0.66
9  0.72  0.62  0.33  0.59

从上述代码可看出，对区域进行筛选，有可能出现NaN值。

4.3自定义筛选

自定义筛选适用于筛选条件较为复杂的情况，通过apply函数实现，示例代码如下：

def filter_1(row):
    return row[0] > 0.7

excel_data = pd.read_excel('data.xlsx', header = None)
data = excel_data[excel_data.apply(filter_1, axis = 1)]#筛选出第一列大于0.7的整行数据
print(data)#输出如下
      0     1     2     3
3  0.72  0.49  0.68  0.43
4  0.73  0.49  0.67  0.66
9  0.72  0.62  0.33  0.59

apply函数还可用于数据处理操作，示例代码如下：

excel_data = pd.read_excel('data.xlsx', header = None)
print(excel_data)#输出如下
      0     1     2     3
0  0.53  0.42  0.46  0.63
1  0.45  0.63  0.60  0.56
2  0.66  0.54  0.79  0.61
3  0.72  0.49  0.68  0.43
4  0.73  0.49  0.67  0.66
5  0.62  0.58  0.80  0.34
6  0.30  0.53  0.44  0.59
7  0.52  0.63  0.56  0.46
8  0.57  0.40  0.52  0.76
9  0.72  0.62  0.33  0.59

new_data = excel_data.iloc[:, 0].apply(lambda x: x * 2)
print(new_data)#输出如下
0    1.06
1    0.90
2    1.32
3    1.44
4    1.46
5    1.24
6    0.60
7    1.04
8    1.14
9    1.44

五、DataFrame类型与numpy数组相互转换

5.1DataFrame类型转换为numpy数组

pandas读取Excel数据返回的是DataFrame数据结构，将其转换为numpy数组代码如下：

import numpy as np

data = excel_data.iloc[0:3, 0:4]#读取第一行至第三行、第一列至第四列的数据
print(type(data))#输出如下
<class 'pandas.core.frame.DataFrame'>

print(data)#输出如下
      0     1     2     3
0  0.53  0.42  0.46  0.63
1  0.45  0.63  0.60  0.56
2  0.66  0.54  0.79  0.61

a01 = np.array(data)#转换为numpy数组
print(a01)#输出如下
[[0.53 0.42 0.46 0.63]
 [0.45 0.63 0.6  0.56]
 [0.66 0.54 0.79 0.61]]

data = excel_data.iloc[0:10, 0]#读取第一行至第十行、第一列的数据
print(type(data))#输出如下
<class 'pandas.core.series.Series'>

print(data)#输出如下
0    0.53
1    0.45
2    0.66
3    0.72
4    0.73
5    0.62
6    0.30
7    0.52
8    0.57
9    0.72
Name: 0, dtype: float64

a01 = np.array(data)#转换为numpy数组
print(a01)#输出如下
[0.53 0.45 0.66 0.72 0.73 0.62 0.3  0.52 0.57 0.72]

data = excel_data.iloc[0:10, 0:1]#读取第一行至第十行、第一列的数据
print(type(data))#输出如下
<class 'pandas.core.frame.DataFrame'>

print(data)#输出如下
      0
0  0.53
1  0.45
2  0.66
3  0.72
4  0.73
5  0.62
6  0.30
7  0.52
8  0.57
9  0.72

a01 = np.array(data)#转换为numpy数组
print(a01)#输出如下
[[0.53]
 [0.45]
 [0.66]
 [0.72]
 [0.73]
 [0.62]
 [0.3 ]
 [0.52]
 [0.57]
 [0.72]]

通过上述代码可看出，pandas不总是返回DataFrame类型，有时也返回Series类型，这与读取数据时指定单行单列或多行多列有关，而在转换为numpy数组时，DataFrame类型转换成二维数组，Series类型转换成一维数组。

另外，如果DataFrame中包含标签，标签并不会被一起转换为numpy数组。

5.2numpy数组转换为DataFrame类型

示例代码如下：

np_array = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
data = pd.DataFrame(np_array)
print(data)#输出如下
   0  1  2
0  1  2  3
1  4  5  6
2  7  8  9

data = pd.DataFrame(np_array, columns=['column01', 'column02', 'column03'])#指定列标签
print(data)#输出如下
   column01  column02  column03
0         1         2         3
1         4         5         6
2         7         8         9

六、写入Excel文件

pandas是将DataFrame类型数据写入Excel文件中，可以向新文件写入，也可追加工作表写入，示例代码如下：

np_array = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
data = pd.DataFrame(np_array)
data.to_excel('test01.xlsx', sheet_name = 'data', index = False, header = False)#写入新Excel文件，index控制是否写入行索引，header控制是否写入列标签

with pd.ExcelWriter('test01.xlsx', mode = 'a', engine = 'openpyxl') as writer:#追加写入
	data.to_excel(writer, sheet_name = 'data02', index = False, header = False)