Pandas如何操作Excel

更新時間：2025年01月22日 11:00:18 作者：王小工

Pandas提供了便捷的方法來處理Excel文件,主要通過pandas.read_excel()和DataFrame.to_excel()函數,這些函數支持多種參數來定制讀取和寫入的行為,包括文件路徑、工作表名稱、數據類型、日期解析等

Pandas操作Excel

Pandas 提供了便捷的方法來處理 Excel 文件，這主要得益于 pandas.read_excel() 和 DataFrame.to_excel() 這兩個函數。

語法

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,
              keep_default_na=True, verbose=False,
              parse_dates=False, date_parser=None,
              thousands=None, comment=None, skipfooter=0,
              convert_float=True, mangle_dupe_cols=True, **kwds)

參數

文件 io

讀取Excel 文件

# str, bytes, ExcelFile, xlrd.Book, path object, or file-like object
# 本地相對路徑：
pd.read_excel('data/data.xlsx') # 注意目錄層級
pd.read_excel('data.xls') # 如果文件與代碼文件在同目錄下
# 本地絕對路徑：
pd.read_excel('/user/wfg/data/data.xlsx')
# 使用網址 url
pd.read_excel('https://wfg.com/file/data/dataset/team.xlsx')

表格 sheet_name

可以指定 Excel 文件讀取哪個 sheet，默認取第一個。

# str, int, list, or None, default 0
pd.read_excel('tmp.xlsx', sheet_name=1) # 第二個 sheet
pd.read_excel('tmp.xlsx', sheet_name='總結表') # 按 sheet 的名字

# 取第一個、第二個、名為 Sheet1 的，返回一個 df 組成的字典
dfs = pd.read_excel('tmp.xlsx', sheet_name=[0, 1, "Sheet1"])
dfs = pd.read_excel('tmp.xlsx', sheet_name=None) # 所有的 sheet
dfs['Sheet5'] # 讀取時按 sheet 名

表頭 header

數據的表頭，默認為第一行。

pd.read_excel('tmp.xlsx', header=None)  # 不設表頭
pd.read_excel('tmp.xlsx', header=2)  # 第三行為表頭
pd.read_excel('tmp.xlsx', header=[0, 1])  # 兩層表頭，多層索引

列名/表頭名 names

默認取數據中默認的表頭名稱，可以重新指定。

# array-like, default None
pd.read_excel('tmp.xlsx', names=['姓名', '年齡', '成績'])
pd.read_excel('tmp.xlsx', names=c_list) # 傳入列表變量
# 沒有表頭，需要設置為 None
pd.read_excel('tmp.xlsx', header=None, names=None)

索引列 index_col

作為索引的列，默認不設置，使用自然索引（從 0 開始）。

# int, list of int, default None
pd.read_excel('tmp.xlsx', index_col=0) # 指定第一列
pd.read_excel('tmp.xlsx', index_col=[0,1]) # 前兩列，多層索引

使用列 usecols

指定使用的列，其余的不讀取，默認是全部使用。

# int, str, list-like, or callable default None
pd.read_excel('tmp.xlsx', usecols='A,B')  # 取 A 和 B 兩列
pd.read_excel('tmp.xlsx', usecols='A:H')  # 取 A 到 H 列
pd.read_excel('tmp.xlsx', usecols='A,C,E:H')  # 取 A和C列，再加E到H列
pd.read_excel('tmp.xlsx', usecols=[0,1])  # 取前兩列
pd.read_excel('tmp.xlsx', usecols=['姓名','性別'])  # 取指定列名的列
# 表頭包含 Q 的
pd.read_excel('team.xlsx', usecols=lambda x: 'Q' in x)

返回序列 squeezebool

如果只要一列，則返回一個 Series，默認還是 DataFrame。

# default False
pd.read_excel('tmp.xlsx', usecols='A', squeezebool=True)

數據類型 dtype

數據類型，如果不傳則自動推斷。如果被 converters 處理則不生效。

# Type name or dict of column -> type, default None
pd.read_excel(data, dtype=np.float64) # 所有數據均為此數據類型
pd.read_excel(data, dtype={'c1':np.float64, 'c2': str}) # 指定字段的類型
pd.read_excel(data, dtype=[datetime, datetime, str, float]) # 依次指定

處理引擎 engine

可接受的參數值是 “xlrd”, “openpyxl” 或者 “odf”，如果文件不是緩沖或路徑，就需要指定，用于處理 excel 使用的引擎，三方庫。

# str, default None
pd.read_excel('tmp.xlsx', engine='xlrd')

在實踐中，默認的 xlrd 引擎不會讀取內容為星號*、百分號 % 等特殊字符的行，可以更換為 openpyxl 解決。

列數據處理 converters

對列的數據進行轉換，列名與函數組成的字典。key 可以是列名或者列的序號。

# dict, default None
def foo(p):
   return p + 's'

# x 應用函數, y 使用 lambda
pd.read_excel('tmp.xlsx', converters={'x': foo,
                                    'y': lambda x: x * 3})
# 使用列索引
pd.read_excel('tmp.xlsx',
            converters={0: foo, 1: lambda x: x * 3})

真假值指定 true_values false_values

將指定的文本轉換為 True 或者 False, 可以用列表指定多個值。

# list, default None
pd.read_excel('tmp.xlsx',
            true_values=['Yes'], false_values=['No'])

跳過指定行 skiprows

# list-like, int or callable, optional
# 跳過前三行
pd.read_excel(data, skiprows=2)
# 跳過前三行
pd.read_excel(data, skiprows=range(2))
# 跳過指定行
pd.read_excel(data, skiprows=[24,234,141])
# 跳過指定行
pd.read_excel(data, skiprows=np.array([2, 6, 11]))
# 隔行跳過
pd.read_excel(data, skiprows=lambda x: x % 2 != 0)
# 跳過最后幾行用 skipfooter=2

讀取行數 nrows

需要讀取的行數，從文件開頭算起，經常用于較大的數據，先取部分進行代碼編寫。

# int, default None
pd.read_excel(data, nrows=1000)

空值替換 na_values

一組用于替換 NA/NaN 的值。如果傳參，需要制定特定列的空值。

# scalar, str, list-like, or dict, default None
# 5 和 5.0 會被認為 NaN
pd.read_excel(data, na_values=[5])
# ? 會被認為 NaN
pd.read_excel(data, na_values='?')
# 空值為 NaN
pd.read_excel(data, keep_default_na=False, na_values=[""])
# 字符 NA 字符 0 會被認為 NaN
pd.read_excel(data, keep_default_na=False, na_values=["NA", "0"])
# Nope 會被認為 NaN
pd.read_excel(data, na_values=["Nope"])
# a、b、c 均會被認為 NaN 等于 na_values=['a','b','c']
pd.read_excel(data, na_values='abc')
# 指定列的指定值會被認為 NaN

pd.read_excel(data, na_values={'c':3, 1:[2,5]})

保留默認空值 keep_default_na

分析數據時是否包含默認的NaN值，是否自動識別。如果指定 na_values 參數，并且 keep_default_na=False，那么默認的NaN將被覆蓋，否則添加。

和 na_values 的關系是：

keep_default_na	na_values	邏輯
True	指定	na_values 的配置附加處理
True	未指定	自動識別
False	指定	使用 na_values 的配置
False	未指定	不做處理

注：如果 na_filter 為 False （默認是 True）, 那么 keep_default_na 和 na_values parameters 均無效。

# boolean, default True
# 不自動識別空值
pd.read_excel(data, keep_default_na=False)

丟失值檢查 na_filter

是否檢查丟失值（空字符串或者是空值）。對于大文件來說數據集中沒有空值，設定na_filter=False 可以提升讀取速度。

# boolean, default True
pd.read_excel(data, na_filter=False) # 不檢查

解析信息 verbose

是否打印各種解析器的輸出信息，例如：“非數值列中缺失值的數量”等。

# boolean, default False
# 可以看到解析信息
pd.read_excel(data, verbose=True)
# Tokenization took: 0.02 ms
# Type conversion took: 0.36 ms
# Parser memory cleanup took: 0.01 ms

日期時間解析 parse_dates

本參數對時間日期進行解析。

# boolean or list of ints or names or list of lists or dict, default False.
pd.read_excel(data, parse_dates=True) # 自動解析日期時間格式
pd.read_excel(data, parse_dates=['年份']) # 指定日期時間字段進行解析

# 將 1、4 列合并解析成名為 時間的 時間類型列
pd.read_excel(data, parse_dates={'時間':[1,4]})

日期時間解析器 date_parser

用于解析日期的函數，默認使用dateutil.parser.parser來做轉換。Pandas 嘗試使用三種不同的方式解析，如果遇到問題則使用下一種方式。

使用一個或者多個arrays（由parse_dates指定）作為參數；
連接指定多列字符串作為一個列作為參數；
每行調用一次date_parser函數來解析一個或者多個字符串（由parse_dates指定）作為參數。

# function, default None
# 指定時間解析庫，默認是 dateutil.parser.parser
date_parser=pd.io.date_converters.parse_date_time
date_parser=lambda x: pd.to_datetime(x, utc=True, format='%d%b%Y')
date_parser = lambda d: pd.datetime.strptime(d, '%d%b%Y')
# 使用
pd.read_excel(data, parse_dates=['年份'], date_parser=date_parser)

千分位分割符 thousands

千位分隔符。

# str, default None
pd.read_excel(data, thousands=',') # 逗號分隔

注釋標識 comment

指示不應分析行的部分。如果在一行的開頭找到該行，則將完全忽略該行。此參數必須是單個字符。像空行一樣（只要skip_blank_lines = True），參數視為header會忽略完全注釋的行，而skiprows 行會忽略。例如，如果comment =‘＃’，則解析header= 0的’#empty \ na，b，c \ n1,2,3’會將’a，b，c’視為header。

# str, default None
s = '# notes\na,b,c\n# more notes\n1,2,3' # 僅為示例
pd.read_excel(data, sep=',', comment='#', skiprows=1)

尾部跳過 skipfooter

從文件尾部開始忽略。 (c引擎不支持)

# int, default 0
pd.read_excel(filename, skipfooter=1) # 最后一行不加載

轉為浮點 convert_float

讀取 Excel 默認把數字轉為浮點，設為 False 將保留整型。

# bool, default True
pd.read_excel('tmp.xlsx', convert_float=False)

mangle_dupe_cols
處理重復列名 mangle_dupe_cols

當列名有重復時，解析列名將變?yōu)?‘X’, ‘X.1’…’X.N’而不是 ‘X’…’X’。

如果該參數為 False ，那么當列名中有重復時，前列將會被后列覆蓋。

# bool, default True
data = 'a,b,a\n0,1,2\n3,4,5' # 僅為示例
pd.read_excel(data, mangle_dupe_cols=True)
# 表頭為 a b a.1
# False 會報 ValueError 錯誤

存儲選項 storage_options

**其他參數 kwds

TextFileReader 處理的其他參數。

返回：一般情況下，會將讀取到的數據返回一個 DataFrame，當然按照參數的要求會返回指定的類型。

示例：Pandas 提供了便捷的方法來處理 Excel 文件，這主要得益于 pandas.read_excel() 和 DataFrame.to_excel() 這兩個函數。以下是使用 Pandas 操作 Excel 文件的一些關鍵步驟和示例：

讀取 Excel 文件

要讀取 Excel 文件中的數據，你可以使用 pandas.read_excel() 函數。這個函數能夠讀取指定工作表中的數據，并將其轉換為一個 Pandas DataFrame 對象。

import pandas as pd
 
# 讀取 Excel 文件中的特定工作表
df = pd.read_excel('example.xlsx', sheet_name='Sheet1')
 
# 如果需要讀取所有工作表，可以將 sheet_name 設置為 None，這將返回一個包含所有工作表數據的字典
sheets = pd.read_excel('example.xlsx', sheet_name=None)

處理讀取的數據

一旦數據被讀取到 DataFrame 中，你就可以使用 Pandas 提供的各種函數和方法來處理這些數據了。例如，你可以對數據進行篩選、排序、分組、聚合等操作。

# 假設我們有一個名為 'df' 的 DataFrame
# 篩選出某列值滿足特定條件的行
filtered_df = df[df['column_name'] > some_value]
 
# 對數據進行排序
sorted_df = df.sort_values(by='column_name')

將數據寫回 Excel 文件

處理完數據后，你可能希望將結果保存回 Excel 文件中。這時，你可以使用 DataFrame.to_excel() 方法。

# 將 DataFrame 寫入新的 Excel 文件
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)
 
# 如果你想將多個 DataFrame 寫入同一個 Excel 文件的不同工作表，可以使用 ExcelWriter
with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

注意事項：

文件路徑：確保你提供的文件路徑是正確的，并且程序有足夠的權限去讀取和寫入文件。
工作表名稱：在讀取或寫入工作表時，確保指定的工作表名稱是存在的，或者你已經正確地處理了工作表不存在的情況。
數據類型：在讀取和寫入數據時，注意數據類型的兼容性。例如，如果 Excel 文件中的日期是以文本格式存儲的，你可能需要在讀取后進行類型轉換。
性能：對于大型數據集，讀取和寫入 Excel 文件可能會比較慢，并且可能會受到內存限制。在這種情況下，你可以考慮將數據分批處理或使用更適合大數據集的格式（如 CSV）。
依賴項：Pandas 使用 openpyxl 或 xlrd 庫來讀取和寫入 Excel 文件（xlrd 從版本 2.0.0 開始不再支持 .xlsx 格式，因此推薦使用 openpyxl）。確保你已經安裝了這些庫。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

Pandas
Excel

Python異常處理：try、except、else、finally的全面解析
在Python中,異常是程序在運行時發(fā)生的錯誤,當Python解釋器遇到一個它不能處理的錯誤時,它會拋出一個異常,異常處理的主要目的是在程序遇到錯誤時,提供一種方法來處理這些錯誤,而不是簡單地讓程序崩潰,本文介紹了Python異常處理：try、except、else、finally的全面解析
2024-07-07
python 處理pdf加密文件的操作代碼
這篇文章主要介紹了python 處理pdf加密文件的操作代碼,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧
2024-01-01
Python入門篇之正則表達式
正則表達式是一個很有用的工具，可處理復雜的字符匹配和替換工作。在Python中內置了一個re模塊以支持正則表達式。本文我們就來詳細探討下Python中正則表達式。
2014-10-10
快速了解Python相對導入
這篇文章主要介紹了快速了解Python相對導入，具有一定借鑒價值,需要的朋友可以參考下
2018-01-01
tensorflow之變量初始化(tf.Variable)使用詳解
今天小編就為大家分享一篇tensorflow之變量初始化(tf.Variable)使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2020-02-02
Python使用pathlib進行文件系統(tǒng)操作
pathlib?是?Python?的一個標準庫,它提供了一個面向對象的文件系統(tǒng)路徑操作接口,本文主要介紹了Python使用pathlib進行文件系統(tǒng)操作的相關知識,有需要的可以了解下
2024-11-11
Python+numpy實現矩陣的行列擴展方式
今天小編就為大家分享一篇Python+numpy實現矩陣的行列擴展方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2019-11-11
python實現畫循環(huán)圓
今天小編就為大家分享一篇python實現畫循環(huán)圓，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2019-11-11
使用OpenCV對運動員的姿勢進行檢測功能實現
2022年奧林匹克運動會如期舉行，以不正確的方式進行運動風險在增加，人體姿勢估計是計算機視覺領域的重要問題，接下來通過本文給大家介紹下使用OpenCV對運動員的姿勢進行檢測功能，感興趣的朋友一起看看吧
2022-02-02
python密碼學庫pynacl功能介紹
PyNaCI能夠提供數字簽名、密鑰加密、公鑰加密、哈希和消息身份驗證、基于密碼的密鑰派生和密碼散列功能，這篇文章主要介紹了python密碼學庫pynacl，感興趣的朋友一起看看吧
2022-05-05