python--pandas读取excel

2021-04-27 本文已影响0人 FTDdata

对excel文件的读取是数据分析中常见的，在python中，pandas库的read_excel方法能够读取excel文件，包括xls和xlsx格式。
本文介绍使用pandas读取excel以及读取过程中一些常见的问题。

环境

Excel文件的格式为xls和xlsx，pandas读取excel文件需要安装依赖库xlrd和openpyxl。

!注意：当xlrd>=2.0时，只支持xls格式，不再支持xlsx。

python3.9
win10 64bit
pandas==1.2.1
xlrd==2.0.1
openpyxl==3.0.7

读取xls

read_excel方法读取xls格式文件，自动使用xlrd引擎。指定io参数为文件路径，文件路径可以是绝对路径或者相对路径。

import pandas as pd
pd.set_option('display.notebook_repr_html',False)
# 读取xls（绝对路径）
pd.read_excel(io=r'E:\blog\Python\pandas\excel\data.xls')

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_4    #N     24  1.507  1.334
4  2017_1_5   NaN     27  1.498  1.325
5  2017_1_6  some     30  1.506  1.329

文件路径字符串前面加r是为了防止字符串中的\转义

# 读取xls（相对路径）
pd.read_excel(io='./data.xls')

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_4    #N     24  1.507  1.334
4  2017_1_5   NaN     27  1.498  1.325
5  2017_1_6  some     30  1.506  1.329

读取xlsx

read_excel方法读取xlsx格式文件，自动使用openpyxl引擎。同样，可以使用绝对或相对路径读取。

# 读取xlsx
pd.read_excel(io='./data.xlsx')

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_4    #N     24  1.507  1.334
4  2017_1_5   NaN     27  1.498  1.325
5  2017_1_6  some     30  1.506  1.329

设置sheet

设置sheet_name参数，可以指定读取excel的sheet。可以根据sheet的名字或者位置设置参数。

sheet_name默认值是0，表示读取第一个sheet。

# 读取xlsx（第二个sheet）（设置sheet位置）
pd.read_excel(io='./data.xlsx',sheet_name=1)

       date    kind  sum   coef  value
0  2019_1_1  pandas  100  2.506  1.530
1  2019_1_2     cat  200  2.533  1.359
2  2019_1_3     dog  300  2.560  1.188
3  2019_1_4    fish  400  2.587  1.017
4  2019_1_5     sky  500  2.614  0.846
5  2019_1_6     git  600  2.641  0.675

# 读取xlsx（第二个sheet）（设置sheet名字）
pd.read_excel(io='./data.xlsx',sheet_name='demo2')

       date    kind  sum   coef  value
0  2019_1_1  pandas  100  2.506  1.530
1  2019_1_2     cat  200  2.533  1.359
2  2019_1_3     dog  300  2.560  1.188
3  2019_1_4    fish  400  2.587  1.017
4  2019_1_5     sky  500  2.614  0.846
5  2019_1_6     git  600  2.641  0.675

设置sheet_name=None，可以读取全部的sheet，返回字典，key为sheet名字，value为sheet表内容。

# 读取xlsx（全部sheet）
pd.read_excel(io='./data.xlsx',sheet_name=None)

{'demo':        date  name  count  socre    sum
 0  2017_1_1   mpg     15  1.506  1.330
 1  2017_1_2   asd     18  1.533  1.359
 2  2017_1_3  puck     20  1.537  1.365
 3  2017_1_4    #N     24  1.507  1.334
 4  2017_1_5   NaN     27  1.498  1.325
 5  2017_1_6  some     30  1.506  1.329,
 'demo2':        date    kind  sum   coef  value
 0  2019_1_1  pandas  100  2.506  1.530
 1  2019_1_2     cat  200  2.533  1.359
 2  2019_1_3     dog  300  2.560  1.188
 3  2019_1_4    fish  400  2.587  1.017
 4  2019_1_5     sky  500  2.614  0.846
 5  2019_1_6     git  600  2.641  0.675}

设置列标签

设置header参数，可以指定目标行的数据为列标签。

header默认值是0，表示第0行为列标签。
设置header为i(整数)，表示设置i行为列标签，i行之前的数据会被舍弃。

demo3.png

可以看出表格有标题，有列名，如果不设置header，读出来的表格为

# 读取xlsx
pd.read_excel(io='./title.xlsx')

                 title Unnamed: 1 Unnamed: 2
0                   id     value1     value2
1  1900-01-01 00:00:00         23         56
2  1900-01-02 00:00:00         33         45
3  1900-01-03 00:00:00         43         34
4  1900-01-04 00:00:00         53         23

如果要舍弃第一行标题，设置header=1即可。

# 读取xlsx（指定第二行为列标签）
pd.read_excel(io='./title.xlsx',header=1)

          id  value1  value2
0 1900-01-01      23      56
1 1900-01-02      33      45
2 1900-01-03      43      34
3 1900-01-04      53      23

时间列解析

在读取excel时，对于数据中有时间列的，一般操作是要把时间列解析成时间格式。

# 读取
df=pd.read_excel(io='./data.xlsx')
# 查看每列数据类型
df.dtypes

date      object
name      object
count      int64
socre    float64
sum      float64
dtype: object

用dtypes属性查看每列的数据类型，发现date列类型为object，并未解析成时间格式，其时间格式为%Y_%m_%d,pandas无法自动识别。

两步完成时间列解析：

设置parse_dates参数，指定需要解析的列；
设置date_parser参数，指定解析器。

# 解析时间列
df=pd.read_excel(io='./data.xls',
                 parse_dates=[0],
                 date_parser=lambda x:pd.to_datetime(x,format='%Y_%m_%d'))
df

        date  name  count  socre    sum
0 2017-01-01   mpg     15  1.506  1.330
1 2017-01-02   asd     18  1.533  1.359
2 2017-01-03  puck     20  1.537  1.365
3 2017-01-04    #N     24  1.507  1.334
4 2017-01-05   NaN     27  1.498  1.325
5 2017-01-06  some     30  1.506  1.329

# 查看每列数据类型
df.dtypes

date     datetime64[ns]
name             object
count             int64
socre           float64
sum             float64
dtype: object

如果需要把解析的时间列设置为索引，需要设置index_col参数，表示索引列。

# 解析时间列，并设置为索引
df=pd.read_excel(io='./data.xls',
                 index_col=[0],
                 parse_dates=[0],
                 date_parser=lambda x:pd.to_datetime(x,format='%Y_%m_%d'))
df

            name  count  socre    sum
date                                 
2017-01-01   mpg     15  1.506  1.330
2017-01-02   asd     18  1.533  1.359
2017-01-03  puck     20  1.537  1.365
2017-01-04    #N     24  1.507  1.334
2017-01-05   NaN     27  1.498  1.325
2017-01-06  some     30  1.506  1.329

读取部分列

设置usecols参数，选择部分列进行读取，可以加快读取速度。可以根据需求灵活设置usecols参数，来选择多列。

usecols默认None，表示全部读取全部列

字符串"A,C:D":表示选择excel字母列的A列，和C到D列；

# 选择部分列读取（字符串形式）
pd.read_excel(io='./data.xlsx',usecols="A,C:D")

       date  count  socre
0  2017_1_1     15  1.506
1  2017_1_2     18  1.533
2  2017_1_3     20  1.537
3  2017_1_4     24  1.507
4  2017_1_5     27  1.498
5  2017_1_6     30  1.506

字符列表["date","name"]:表示选择数据的date列和name列；

# 选择部分列读取（字符列表形式）
pd.read_excel(io='./data.xlsx',usecols=['date','name'])

       date  name
0  2017_1_1   mpg
1  2017_1_2   asd
2  2017_1_3  puck
3  2017_1_4    #N
4  2017_1_5   NaN
5  2017_1_6  some

整数列表[0,2]:表示选择数据的0列和2列；

# 选择部分列读取（整数列表形式）
pd.read_excel(io='./data.xlsx',usecols=[0,2])

       date  count
0  2017_1_1     15
1  2017_1_2     18
2  2017_1_3     20
3  2017_1_4     24
4  2017_1_5     27
5  2017_1_6     30

函数lambda x:x.endswith("e"):表示选择以字母e结尾的所有列

# 选择部分列读取（函数形式）
pd.read_excel(io='./data.xlsx',usecols=lambda x:x.endswith("e"))

       date  name  socre
0  2017_1_1   mpg  1.506
1  2017_1_2   asd  1.533
2  2017_1_3  puck  1.537
3  2017_1_4    #N  1.507
4  2017_1_5   NaN  1.498
5  2017_1_6  some  1.506

读取部分行

设置参数nrows=n，可以读取数据的前n行。

nrows默认None，表示全部读取全部行

# 选择前3行读取
pd.read_excel(io='./data.xlsx',nrows=4)

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_4    #N     24  1.507  1.334

设置skiprows参数，可以跳过部分行不读取。

skiprows默认None，表示不跳过行

# 跳过1，3行不读取
pd.read_excel(io='./data.xlsx',skiprows=[1,3])

       date  name  count  socre    sum
0  2017_1_2   asd     18  1.533  1.359
1  2017_1_4    #N     24  1.507  1.334
2  2017_1_5   NaN     27  1.498  1.325
3  2017_1_6  some     30  1.506  1.329

可以设置skiprows参数为匿名函数，更加灵活的跳过部分行不读取。

# 跳过部分行不读取（行索引包含[4,5]）
pd.read_excel(io='./data.xlsx',skiprows=lambda x:x in [4,5])

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_6  some     30  1.506  1.329

缺失值处理

read_excel会自动把缺失值标记为NaN，但实际的情况千变万化，例如实际中缺失值可能用#N,##等各种异常符号表示，
这时候设置na_values参数，可以填充这些异常符号为缺失值。

# 填充缺失值
pd.read_excel(io='./data.xlsx',na_values='#N')

       date  name  count  socre    sum
0  2017_1_1   mpg     15  1.506  1.330
1  2017_1_2   asd     18  1.533  1.359
2  2017_1_3  puck     20  1.537  1.365
3  2017_1_4   NaN     24  1.507  1.334
4  2017_1_5   NaN     27  1.498  1.325
5  2017_1_6  some     30  1.506  1.329

更多使用细节参考：read_excel