在数据分析和处理领域,Excel文件是常见的数据存储格式之一。Pandas库提供了强大的功能来读取、处理和写入Excel文件。本文将详细介绍如何使用Pandas操作Excel文件,包括读取、数据清洗、数据操作和写入等步骤。
首先,确保你已经安装了Pandas库以及用于读写Excel文件的库(如 openpyxl
或 xlrd
)。你可以使用以下命令进行安装:
pip install pandas openpyxl
import pandas as pd
# 读取Excel文件
df = pd.read_excel('data.xlsx')
print(df.head())
read_excel()
函数说明
pandas.read_excel(io, sheet_name=0, *, header=0, names=None, index_col=None, usecols=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=<no_default>, date_format=None, thousands=None, decimal='.', comment=None, skipfooter=0, storage_options=None, dtype_backend=<no_default>, engine_kwargs=None)
参数说明:
io
:这是必需的参数,指定了要读取的 Excel 文件的路径或文件对象。sheet_name=0
:指定要读取的工作表名称或索引。默认为0,即第一个工作表。header=0
:指定用作列名的行。默认为0,即第一行。names=None
:用于指定列名的列表。如果提供,将覆盖文件中的列名。index_col=None
:指定用作行索引的列。可以是列的名称或数字。usecols=None
:指定要读取的列。可以是列名的列表或列索引的列表。dtype=None
:指定列的数据类型。可以是字典格式,键为列名,值为数据类型。engine=None
:指定解析引擎。默认为None
,pandas 会自动选择。converters=None
:用于转换数据的函数字典。true_values=None
:指定应该被视为布尔值True
的值。false_values=None
:指定应该被视为布尔值False
的值。skiprows=None
:指定要跳过的行数或要跳过的行的列表。nrows=None
:指定要读取的行数。na_values=None
:指定应该被视为缺失值的值。keep_default_na=True
:指定是否要将默认的缺失值(例如NaN
)解析为NA
。na_filter=True
:指定是否要将数据转换为NA
。verbose=False
:指定是否要输出详细的进度信息。parse_dates=False
:指定是否要解析日期。date_parser=<no_default>
:用于解析日期的函数。date_format=None
:指定日期的格式。thousands=None
:指定千位分隔符。decimal='.'
:指定小数点字符。comment=None
:指定注释字符。skipfooter=0
:指定要跳过的文件末尾的行数。storage_options=None
:用于云存储的参数字典。dtype_backend=<no_default>
:指定数据类型后端。engine_kwargs=None
:传递给引擎的额外参数字典。如果Excel文件包含多个工作表,可以使用 sheet_name
参数指定要读取的工作表
# 读取名为 'Sheet1' 的工作表
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())
可以使用 usecols
参数指定要读取的列范围,使用 skiprows
和 nrows
参数指定要跳过的行和读取的行数
# 读取第1到第3列,跳过前2行,读取10行
df = pd.read_excel('data.xlsx', usecols="A:C", skiprows=2, nrows=10)
print(df.head())
使用 head()
、tail()
、info()
和 describe()
函数可以查看数据的基本信息:
print(df.head()) # 显示前5行
print(df.tail()) # 显示后5行
print(df.info()) # 显示数据类型和缺失值信息
print(df.describe()) # 显示统计信息
可以使用 dtypes
属性查看数据类型,并使用 astype()
函数进行类型转换
print(df.dtypes)
df['Column1'] = df['Column1'].astype('int')
使用 isnull()
和 sum()
函数检查缺失值
print(df.isnull().sum())
可以使用 fillna()
函数填充缺失值,或使用 dropna()
函数删除包含缺失值的行或列
# 填充缺失值
df.fillna(0, inplace=True)
# 删除包含缺失值的行
df.dropna(inplace=True)
使用 rename()
函数重命名列
df.rename(columns={'OldName': 'NewName'}, inplace=True)
使用 drop_duplicates()
函数删除重复数据
df.drop_duplicates(inplace=True)
使用 replace()
函数进行数据替换
df['Column1'].replace(10, 20, inplace=True)
使用 sort_values()
函数进行数据排序:
df.sort_values(by='Column1', ascending=False, inplace=True)
使用 groupby()
和 agg()
函数进行数据分组与聚合
grouped = df.groupby('Category')
result = grouped['Value'].agg(['mean', 'sum'])
print(result)
使用 loc
按标签选择数据
subset = df.loc[df['Column1'] > 10]
print(subset)
subset = df.iloc[0:5, 1:3]
print(subset)
使用布尔索引进行数据过滤
subset = df[df['Column1'] > 10]
print(subset)
subset = df[(df['Column1'] > 10) & (df['Column2'] < 20)]
print(subset)
使用 insert()
函数添加行、列,使用 drop()
函数删除列
df.insert(1, 'NewColumn', [1, 2, 3, 4, 5])
# 删除行
df.drop(1,axis=0)
# 删除列
df.drop(columns=['OldColumn'], inplace=True)
df.drop(labels = None, axis = 0, index = None, columns = None, level = None, inplace = False,errors = 'raise')
参数解释:
使用 concat()
、merge()
和 join()
函数进行数据框合并
# 使用 concat() 合并
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})
result = pd.concat([df1, df2], ignore_index=True)
# 使用 merge() 合并
df1 = pd.DataFrame({'key': ['K0', 'K1'], 'A': [1, 2]})
df2 = pd.DataFrame({'key': ['K0', 'K1'], 'B': [3, 4]})
result = pd.merge(df1, df2, on='key')
# 使用 join() 合并
df1 = pd.DataFrame({'A': [1, 2]}, index=['K0', 'K1'])
df2 = pd.DataFrame({'B': [3, 4]}, index=['K0', 'K1'])
result = df1.join(df2)
使用 pivot_table()
函数创建数据透视表:
pivot_table = df.pivot_table(values='Value', index='Category', columns='Year', aggfunc='mean')
print(pivot_table)
使用 crosstab()
函数创建交叉表:
crosstab = pd.crosstab(df['Category'], df['Year'])
print(crosstab)
使用 pd.DataFrame.to_excel()
函数将DataFrame写入Excel文件
df.to_excel('output.xlsx', index=False)
to_excel()
函数说明
DataFrame.to_excel(excel_writer, *, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, inf_rep='inf', freeze_panes=None, storage_options=None, engine_kwargs=None)
参数说明:
excel_writer
:这是必需的参数,指定了要写入的 Excel 文件路径或文件对象。sheet_name='Sheet1'
:指定写入的工作表名称,默认为 'Sheet1'
。na_rep=''
:指定在 Excel 文件中表示缺失值(NaN)的字符串,默认为空字符串。float_format=None
:指定浮点数的格式。如果为 None
,则使用 Excel 的默认格式。columns=None
:指定要写入的列。如果为 None
,则写入所有列。header=True
:指定是否写入列名作为第一行。如果为 False
,则不写入列名。index=True
:指定是否写入索引作为第一列。如果为 False
,则不写入索引。index_label=None
:指定索引列的标签。如果为 None
,则不写入索引标签。startrow=0
:指定开始写入的行号,默认从第0行开始。startcol=0
:指定开始写入的列号,默认从第0列开始。engine=None
:指定写入 Excel 文件时使用的引擎,默认为 None
,pandas 会自动选择。merge_cells=True
:指定是否合并单元格。如果为 True
,则合并具有相同值的单元格。inf_rep='inf'
:指定在 Excel 文件中表示无穷大值的字符串,默认为 'inf'
。freeze_panes=None
:指定冻结窗格的位置。如果为 None
,则不冻结窗格。storage_options=None
:用于云存储的参数字典。engine_kwargs=None
:传递给引擎的额外参数字典。可以使用 sheet_name
参数指定工作表名称
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)
df.to_excel('output.xlsx', startrow=1, startcol=2, index=False)
使用 ExcelWriter
类处理多个工作表
with pd.ExcelWriter('output.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
可以使用 openpyxl
库设置单元格样式和格式
from openpyxl import Workbook
from openpyxl.styles import Font
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = workbook['Sheet1']
for cell in worksheet['A'] + worksheet['B']:
cell.font = Font(bold=True)