對excel文件的讀取是數(shù)據(jù)分析中常見的螺捐，在python中缎讼，pandas庫的read_excel方法能夠讀取excel文件专执，包括xls和xlsx格式肩祥。
本文介紹使用pandas讀取excel以及讀取過程中一些常見的問題飞几。
環(huán)境
Excel文件的格式為xls和xlsx砚哆，pandas讀取excel文件需要安裝依賴庫xlrd和openpyxl。
!注意：當(dāng)xlrd>=2.0時(shí)屑墨，只支持xls格式躁锁，不再支持xlsx。
- python3.9
- win10 64bit
- pandas==1.2.1
- xlrd==2.0.1
- openpyxl==3.0.7
讀取xls
read_excel方法讀取xls格式文件卵史，自動使用xlrd引擎战转。指定io參數(shù)為文件路徑，文件路徑可以是絕對路徑或者相對路徑以躯。
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是為了防止字符串中的\轉(zhuǎn)義
# 讀取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
設(shè)置sheet
設(shè)置sheet_name參數(shù)，可以指定讀取excel的sheet址晕“蛐福可以根據(jù)sheet的名字或者位置設(shè)置參數(shù)。
sheet_name默認(rèn)值是0谨垃，表示讀取第一個(gè)sheet启搂。
# 讀取xlsx（第二個(gè)sheet）（設(shè)置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（第二個(gè)sheet）（設(shè)置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
設(shè)置sheet_name=None，可以讀取全部的sheet刘陶，返回字典狐血，key為sheet名字，value為sheet表內(nèi)容易核。
# 讀取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}
設(shè)置列標(biāo)簽
設(shè)置header參數(shù)，可以指定目標(biāo)行的數(shù)據(jù)為列標(biāo)簽浪默。
header默認(rèn)值是0牡直，表示第0行為列標(biāo)簽缀匕。
設(shè)置header為i(整數(shù))，表示設(shè)置i行為列標(biāo)簽碰逸，i行之前的數(shù)據(jù)會被舍棄乡小。
可以看出表格有標(biāo)題，有列名饵史，如果不設(shè)置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
如果要舍棄第一行標(biāo)題，設(shè)置header=1即可胳喷。
# 讀取xlsx（指定第二行為列標(biāo)簽）
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
時(shí)間列解析
在讀取excel時(shí)湃番，對于數(shù)據(jù)中有時(shí)間列的，一般操作是要把時(shí)間列解析成時(shí)間格式吭露。
# 讀取
df=pd.read_excel(io='./data.xlsx')
# 查看每列數(shù)據(jù)類型
df.dtypes
date object
name object
count int64
socre float64
sum float64
dtype: object
用dtypes屬性查看每列的數(shù)據(jù)類型吠撮，發(fā)現(xiàn)date列類型為object，并未解析成時(shí)間格式讲竿，其時(shí)間格式為%Y_%m_%d,pandas無法自動識別泥兰。
兩步完成時(shí)間列解析：
- 設(shè)置
parse_dates參數(shù)，指定需要解析的列题禀； - 設(shè)置
date_parser參數(shù)鞋诗，指定解析器。
# 解析時(shí)間列
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
# 查看每列數(shù)據(jù)類型
df.dtypes
date datetime64[ns]
name object
count int64
socre float64
sum float64
dtype: object
如果需要把解析的時(shí)間列設(shè)置為索引迈嘹，需要設(shè)置index_col參數(shù)削彬，表示索引列。
# 解析時(shí)間列江锨，并設(shè)置為索引
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
讀取部分列
設(shè)置usecols參數(shù)吃警，選擇部分列進(jìn)行讀取，可以加快讀取速度啄育∽眯模可以根據(jù)需求靈活設(shè)置usecols參數(shù)，來選擇多列挑豌。
usecols默認(rèn)
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"]:表示選擇數(shù)據(jù)的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
- 整數(shù)列表
[0,2]:表示選擇數(shù)據(jù)的0列和2列址貌；
# 選擇部分列讀取（整數(shù)列表形式）
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
- 函數(shù)
lambda x:x.endswith("e"):表示選擇以字母e結(jié)尾的所有列
# 選擇部分列讀取（函數(shù)形式）
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
讀取部分行
設(shè)置參數(shù)nrows=n练对，可以讀取數(shù)據(jù)的前n行遍蟋。
nrows默認(rèn)
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
設(shè)置skiprows參數(shù)螟凭，可以跳過部分行不讀取虚青。
skiprows默認(rèn)
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
可以設(shè)置skiprows參數(shù)為匿名函數(shù)棒厘，更加靈活的跳過部分行不讀取。
# 跳過部分行不讀认滤怼（行索引包含[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會自動把缺失值標(biāo)記為NaN奢人，但實(shí)際的情況千變?nèi)f化，例如實(shí)際中缺失值可能用#N,##等各種異常符號表示汪拥，
這時(shí)候設(shè)置na_values參數(shù)达传，可以填充這些異常符號為缺失值。
# 填充缺失值
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
更多使用細(xì)節(jié)參考：read_excel
