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中使用asyncio實現異步IO實例分析
在本篇文章里小編給大家整理的是一篇關于python中使用asyncio實現異步IO實例分析內容，有興趣的朋友們可以學習下。
2021-02-02
Python函數和文件操作詳情
這篇文章主要介紹了Python函數和文件操作詳情，函數在編程中是一個很重要的角色，我們可以將若干個語句組合形成一個函數，它可以接受傳入參數，并在內部進行相關計算后產生輸出，下文詳細內容需要的小伙伴可以參考一下
2022-05-05
Python中2種常用數據可視化庫Bokeh和Altair使用示例詳解
本文對Python中兩個常用的數據可視化庫?Bokeh?和?Altair?進行了比較和探討,通過對它們的特點、優(yōu)缺點以及使用示例的詳細分析,讀者可以更好地了解這兩個庫的功能和適用場景,從而更好地選擇合適的庫來進行數據可視化工作,感興趣的朋友跟隨小編一起看看吧
2024-04-04
Python中SQLAlchemy庫的使用方法分析
SQLAlchemy 是Python SQL工具包和對象關系映射器(ORM),允許Python開發(fā)者在應用程序中使用SQL來交互,而無需處理數據庫的具體細節(jié),本文給大家詳細分析了Python中SQLAlchemy庫的使用方法,需要的朋友可以參考下
2024-08-08
Python通過模塊化開發(fā)優(yōu)化代碼的技巧分享
模塊化開發(fā)就是把代碼拆成一個個“零件”,該封裝封裝,該拆分拆分,下面小編就來和大家簡單聊聊python如何用模塊化開發(fā)進行代碼優(yōu)化吧
2025-04-04
用Python實現一個簡單的多線程TCP服務器的教程
這篇文章主要介紹了用Python實現一個簡單的多線程TCP服務器的教程,示例的運行環(huán)境為Windows操作系統(tǒng),需要的朋友可以參考下
2015-05-05
使用python判斷jpeg圖片的完整性實例
今天小編就為大家分享一篇使用python判斷jpeg圖片的完整性實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2019-06-06
python 多線程共享全局變量的優(yōu)劣
這篇文章主要介紹了python 多線程共享全局變量的優(yōu)劣，幫助大家更好的理解和學習python多線程，感興趣的朋友可以了解下
2020-09-09
python圖片和二進制轉換的三種實現方式
本文介紹了將PIL格式、數組和圖片轉換為二進制的不同方法,包括使用PIL庫、OpenCV和直接讀取二進制,此外,還提到了數據傳輸中base64格式的應用,這些信息對需要進行圖片數據處理和轉換的開發(fā)者非常有用
2024-09-09
python 實現rolling和apply函數的向下取值操作
這篇文章主要介紹了python 實現rolling和apply函數的向下取值操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2020-06-06