Pandas如何操作Excel

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

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

Pandas操作Excel

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

語法

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)

參數(shù)

文件 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')
# 使用網(wǎng)址 url
pd.read_excel('https://wfg.com/file/data/dataset/team.xlsx')

表格 sheet_name

可以指定 Excel 文件讀取哪個 sheet，默認(rèn)取第一個。

# str, int, list, or None, default 0
pd.read_excel('tmp.xlsx', sheet_name=1) # 第二個 sheet
pd.read_excel('tmp.xlsx', sheet_name='總結(jié)表') # 按 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'] # 讀取時(shí)按 sheet 名

表頭 header

數(shù)據(jù)的表頭，默認(rèn)為第一行。

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

列名/表頭名 names

默認(rèn)取數(shù)據(jù)中默認(rèn)的表頭名稱，可以重新指定。

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

索引列 index_col

作為索引的列，默認(rèn)不設(shè)置，使用自然索引（從 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

指定使用的列，其余的不讀取，默認(rèn)是全部使用。

# 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，默認(rèn)還是 DataFrame。

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

數(shù)據(jù)類型 dtype

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

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

處理引擎 engine

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

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

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

列數(shù)據(jù)處理 converters

對列的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，列名與函數(shù)組成的字典。key 可以是列名或者列的序號。

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

# x 應(yīng)用函數(shù), 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

將指定的文本轉(zhuǎn)換為 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

讀取行數(shù) nrows

需要讀取的行數(shù)，從文件開頭算起，經(jīng)常用于較大的數(shù)據(jù)，先取部分進(jìn)行代碼編寫。

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

空值替換 na_values

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

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

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

保留默認(rèn)空值 keep_default_na

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

和 na_values 的關(guān)系是：

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

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

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

丟失值檢查 na_filter

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

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

解析信息 verbose

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

# 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

日期時(shí)間解析 parse_dates

本參數(shù)對時(shí)間日期進(jìn)行解析。

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

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

日期時(shí)間解析器 date_parser

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

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

# function, default None
# 指定時(shí)間解析庫，默認(rèn)是 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=',') # 逗號分隔

注釋標(biāo)識 comment

指示不應(yīng)分析行的部分。如果在一行的開頭找到該行，則將完全忽略該行。此參數(shù)必須是單個字符。像空行一樣（只要skip_blank_lines = True），參數(shù)視為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) # 最后一行不加載

轉(zhuǎn)為浮點(diǎn) convert_float

讀取 Excel 默認(rèn)把數(shù)字轉(zhuǎn)為浮點(diǎn)，設(shè)為 False 將保留整型。

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

mangle_dupe_cols
處理重復(fù)列名 mangle_dupe_cols

當(dāng)列名有重復(fù)時(shí)，解析列名將變?yōu)?‘X’, ‘X.1’…’X.N’而不是 ‘X’…’X’。

如果該參數(shù)為 False ，那么當(dāng)列名中有重復(fù)時(shí)，前列將會被后列覆蓋。

# 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 會報(bào) ValueError 錯誤

存儲選項(xiàng) storage_options

**其他參數(shù) kwds

TextFileReader 處理的其他參數(shù)。

返回：一般情況下，會將讀取到的數(shù)據(jù)返回一個 DataFrame，當(dāng)然按照參數(shù)的要求會返回指定的類型。

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

讀取 Excel 文件

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

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

處理讀取的數(shù)據(jù)

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

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

將數(shù)據(jù)寫回 Excel 文件

處理完數(shù)據(jù)后，你可能希望將結(jié)果保存回 Excel 文件中。這時(shí)，你可以使用 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)

注意事項(xiàng)：

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