6、pandas-excel

在数据分析和处理领域，Excel文件是常见的数据存储格式之一。Pandas库提供了强大的功能来读取、处理和写入Excel文件。本文将详细介绍如何使用Pandas操作Excel文件，包括读取、数据清洗、数据操作和写入等步骤。

安装

首先，确保你已经安装了Pandas库以及用于读写Excel文件的库（如 openpyxl 或 xlrd）。你可以使用以下命令进行安装：

pip install pandas openpyxl

读取 Excel

基本用法

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)

drop函数详解

df.drop(labels = None, axis = 0, index = None, columns = None, level = None, inplace = False,errors = 'raise')

参数解释：

• **labels：**要删除的行或列的标签。如果要删除行，就传入行的标签；如果要删除列，就传入列的标签。就像你想丢掉房间里的一件衣服，labels就告诉你要丢掉哪一件。
• **axis：**指定删除的方向，axis=0表示删除行，axis=1表示删除列。默认是axis=0，如果想清理掉无用的行，就设置成0；如果想去掉某些列，就设置成1。
• **level：**当数据有多重索引时，可以指定要删除的层级，通常我们在处理简单的DataFrame时不太需要关心这个参数，它就像是多层文件夹中指定删除哪一层。
• **inplace：**如果设置为True，原DataFrame将被直接修改；如果是False（默认值），则会返回一个新的DataFrame，原始数据保持不变。就像你在整理文件夹时，如果选择“保存”，文件会直接被更新；如果选择“另存为”，原文件不变，保存一个新版本。
• **errors：**如果为'raise'（默认），当你试图删除的标签不存在时会抛出错误；如果设置为'ignore'，则忽略这些错误。这个参数就像是你在丢东西时，如果放错了地方，有人会提醒你“这件衣服不存在”；如果设置成忽略，你就不会收到任何提醒。

数据框合并

使用 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)

写入Excel文件

基本用法

使用 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)