Pandas讀寫(xiě)Excel函數(shù)大全

讀寫(xiě)Excel文件是在做數(shù)據(jù)分析相關(guān)工作時(shí)非常常見(jiàn)的一個(gè)操作，也經(jīng)常有同學(xué)問(wèn)起相關(guān)的問(wèn)題。

今天就跟大家詳細(xì)分享一下，如何通過(guò)Pandas來(lái)讀取Excel文件里的數(shù)據(jù)，以及如何將DataFrame保存到Excel文件中。

官網(wǎng)參數(shù)詳解：

https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html

參數(shù)

read_excel函數(shù)能夠讀取的格式包含：xls, xlsx, xlsm, xlsb, odf, ods 和 odt 文件擴(kuò)展名。支持讀取單一sheet或幾個(gè)sheet。

以下是官方文檔中提供的全部參數(shù)信息：

pandas.read_excel(
  io,    
  sheet_name=0, 
  header=0, 
  names=None, 
  index_col=None, 
  usecols=None, 
  squeeze=None, 
  dtype=None, 
  engine=None, 
  converters=None, 
  true_values=None, 
  false_values=None, 
  skiprows=None, 
  nrows=None, 
  na_values=None,
  keep_default_na=True, 
  na_filter=True, 
  verbose=False, 
  parse_dates=False, 
  date_parser=None, 
  thousands=None, 
  decimal='.', 
  comment=None, 
  skipfooter=0, 
  convert_float=None, 
  mangle_dupe_cols=True, 
  storage_options=None
)

常用參數(shù)的含義：

• io：文件路徑，支持 str, bytes, ExcelFile, xlrd.Book, path object, or file-like object。默認(rèn)讀取第一個(gè)sheet的內(nèi)容。案例："/desktop/student.xlsx"
• sheet_name：sheet表名，支持 str, int, list, or None；默認(rèn)是0，索引號(hào)從0開(kāi)始，表示第一個(gè)sheet。案例：sheet_name=1, sheet_name="sheet1"，sheet_name=[1,2,"sheet3"]。None 表示引用所有sheet
• header：表示用第幾行作為表頭，支持 int, list of int；默認(rèn)是0，第一行的數(shù)據(jù)當(dāng)做表頭。header=None表示不使用數(shù)據(jù)源中的表頭，Pandas自動(dòng)使用0,1,2,3…的自然數(shù)作為索引。
• names：表示自定義表頭的名稱(chēng)，此時(shí)需要傳遞數(shù)組參數(shù)。
• index_col：指定列屬性為行索引列，支持 int, list of int, 默認(rèn)是None，也就是索引為0,1,2,3等自然數(shù)的列用作DataFrame的行標(biāo)簽。如果傳入的是列表形式，則行索引會(huì)是多層索引
• usecols：待解析的列，支持 int, str, list-like, or callable ，默認(rèn)是 None，表示解析全部的列。
• dtype：指定列屬性的字段類(lèi)型。案例：{‘a(chǎn)’: np.float64, ‘b’: np.int32}；默認(rèn)為None，也就是不改變數(shù)據(jù)類(lèi)型。
• engine：解析引擎；可以接受的參數(shù)有"xlrd"、"openpyxl"、"odf"、"pyxlsb"，用于使用第三方的庫(kù)去解析excel文件
• “xlrd”支持舊式 Excel 文件 (.xls)
• “openpyxl”支持更新的 Excel 文件格式
• “odf”支持 OpenDocument 文件格式(.odf、.ods、.odt)
• “pyxlsb”支持二進(jìn)制 Excel 文件
• converters：對(duì)指定列進(jìn)行指定函數(shù)的處理，傳入?yún)?shù)為列名與函數(shù)組成的字典，和usecols參數(shù)連用。key 可以是列名或者列的序號(hào)，values是函數(shù)，可以自定義的函數(shù)或者Python的匿名lambda函數(shù)
• skiprows：跳過(guò)指定的行（可選參數(shù)），類(lèi)型為：list-like, int, or callable
• nrows：指定讀取的行數(shù)，通常用于較大的數(shù)據(jù)文件中。類(lèi)型int, 默認(rèn)是None，讀取全部數(shù)據(jù)
• na_values：指定列的某些特定值為NaN
• keep_default_na：是否導(dǎo)入空值，默認(rèn)是導(dǎo)入，識(shí)別為NaN

模擬數(shù)據(jù)

現(xiàn)在模擬兩個(gè)數(shù)據(jù)：Pandas_Excel.xls 和 Pandas_Excel.xlsx

Pandas_Excel.xls 文件中包含兩個(gè)sheet，第二個(gè)數(shù)據(jù)只比第一個(gè)多個(gè)index的信息

1、sheet1的內(nèi)容

2、sheet2的內(nèi)容

3、Pandas_Excel.xlsx的內(nèi)容，模擬的完整信息：

import pandas as pd

默認(rèn)情況

此時(shí)文件剛好在當(dāng)前目錄下，讀取的時(shí)候指定文件名即可，可以看到讀取的是第一個(gè)sheet

df = pd.read_excel("Pandas-Excel.xls")
df

參數(shù)io

填寫(xiě)完整的文件路徑作為io的取值。也可以使用相對(duì)路徑

pd.read_excel(r"/Users/peter/Desktop/pandas/Pandas-Excel.xls")

參數(shù)sheet_name

# pd.read_excel("Pandas-Excel.xls", sheet_name=0) # 效果同上

# 直接指定sheet的名字
pd.read_excel("Pandas-Excel.xls", sheet_name="Sheet1") # 效果同上

換成讀取第二個(gè)sheet：名稱(chēng)是Sheet2

pd.read_excel("Pandas-Excel.xls", sheet_name="Sheet2") 

結(jié)果中多了一列index的取值

參數(shù)header

# 和默認(rèn)情況相同

pd.read_excel("Pandas-Excel.xls", header=[0]) 

pd.read_excel("Pandas-Excel.xls", header=[1])  # 單個(gè)元素

第一行的數(shù)據(jù)當(dāng)做列屬性：

傳入多個(gè)元素會(huì)形成多層索引：

pd.read_excel("Pandas-Excel.xls", header=[0,1])   # 多個(gè)元素

參數(shù)names

# 指定列名稱(chēng)

pd.read_excel("Pandas-Excel.xls", names=["a","b","c","d","e"])   

參數(shù)index_col

# 指定單個(gè)元素作為索引
pd.read_excel("Pandas-Excel.xls", index_col=[0]) 

# 多個(gè)元素
pd.read_excel("Pandas-Excel.xls", index_col=[0,1])   

參數(shù)usecols

pd.read_excel("Pandas-Excel.xls", usecols=[0])   # 單個(gè)字段

pd.read_excel("Pandas-Excel.xls", usecols=[0,2,4])   # 多個(gè)字段

# 直接指定名稱(chēng)
    
pd.read_excel("Pandas-Excel.xls", usecols=["age","sex"])  

# 傳入匿名函數(shù)，字段中包含a，結(jié)果sex沒(méi)有了

pd.read_excel("Pandas-Excel.xls", usecols=lambda x: "a" in x)

參數(shù)dtype

df.dtypes  

name               object
age                 int64
sex                object
address            object
date       datetime64[ns]
dtype: object

從上面的結(jié)果中看到age字段，在默認(rèn)情況下讀取的是int64類(lèi)型：

# 指定數(shù)據(jù)類(lèi)型
df1 = pd.read_excel("Pandas-Excel.xls", dtype={"age":"float64"})

# 查看字段信息
df1.dtypes

name               object
age               float64  # 修改
sex                object
address            object
date       datetime64[ns]
dtype: object

參數(shù)engine

# xls 結(jié)尾

pd.read_excel("Pandas-Excel.xls", engine="xlrd")

# xlsx 結(jié)尾
pd.read_excel("Pandas-Excel.xlsx", engine="openpyxl")

參數(shù)converters

pd.read_excel("Pandas-Excel.xlsx")  # 默認(rèn)操作

pd.read_excel("Pandas-Excel.xlsx", 
              usecols=[1,3],  # 1-age  3-address 數(shù)值為原索引號(hào)
              converters={0:lambda x: x+5,  # 0代表上面[1,3]中的索引號(hào)
                          1:lambda x: x + "市"
                         })

參數(shù)skiprows

pd.read_excel("Pandas-Excel.xls")   # 默認(rèn)情況

把張三和李四所在的行直接跳過(guò)：

pd.read_excel("Pandas-Excel.xls", skiprows=2)

# 跳過(guò)偶數(shù)行

pd.read_excel("Pandas-Excel.xls", skiprows=lambda x: x%2 == 0)

參數(shù)nrows

# 指定讀取的行數(shù)

pd.read_excel("Pandas-Excel.xls", nrows=2)

參數(shù)na_values

pd.read_excel("Pandas-Excel.xls")  # 默認(rèn)

pd.read_excel("Pandas-Excel.xls", 
              na_values={"sex":"未知"})

sex字段中的未知顯示成了NaN：

參數(shù)keep_default_na

pd.read_excel("Pandas-Excel.xls")  # 默認(rèn)keep_default_na=True

pd.read_excel("Pandas-Excel.xls", keep_default_na=False)

輸出到excel文件

簡(jiǎn)單模擬一份數(shù)據(jù)：

df2 = pd.DataFrame({"num1":[1,2,3],
                   "num2":[4,5,6],
                   "num3":[7,8,9]})
df2

df2.to_excel("newdata_1.xlsx")

效果如下：

df2.to_excel("newdata_2.xlsx",index=False)

不會(huì)帶上索引號(hào)