接上篇《64、Pandas如何实现数据的Concat合并》
上一篇我们学习了Pandas如何实现数据的Concat合并,本篇我们来继续学习Pandas如何批量拆分与合并Excel文件。
一、引言
在当今数据驱动的时代,Excel文件作为数据处理和分析的基石,扮演着不可或缺的角色。无论是企业日常运营中的数据记录、财务报表的编制,还是科研领域的数据收集与分析,Excel都以其直观的操作界面和强大的功能,赢得了广大用户的青睐。然而,随着数据量的激增和复杂性的增加,仅仅依靠Excel自带的功能进行数据处理,往往显得力不从心,尤其是在需要批量处理大量Excel文件时,更是效率低下且易出错。
面对Excel文件处理的挑战,Python的Pandas库以其高效、灵活和强大的数据处理能力,成为了众多数据分析师的首选工具。Pandas不仅提供了便捷的数据读取和写入功能,能够轻松地将Excel文件转换为DataFrame对象进行处理,还内置了丰富的数据处理函数和方法,如数据清洗、转换、合并、分组等,极大地提高了数据处理的效率和准确性。
二、环境准备与基础概念
1、安装Pandas、Openpyxl和xlsxwriter
在进行Excel文件的处理之前,我们需要确保Python环境中已经安装了Pandas和Openpyxl这两个库。Pandas是数据处理库,而Openpyxl则是专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的库。xlsxwriter是一个用于创建Excel 2007+XLSX文件的Python库。
(1)Pandas库的安装方法
Pandas库可以通过Python的包管理工具pip来安装。打开你的命令行工具(如CMD、Terminal或Anaconda Prompt),然后输入以下命令:
pip install pandas
等待安装完成后,你就可以在Python代码中导入并使用Pandas库了。
(2)Openpyxl库的安装与简介
同样地,Openpyxl库也可以通过pip来安装。在命令行中输入以下命令:
pip install openpyxl
Openpyxl是一个Python库,用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件。它提供了丰富的API来操作Excel文件,包括读取工作簿、工作表、单元格等,以及写入新的数据到Excel文件中。
(3)xlsxwriter库的安装与简介
同样地,xlsxwriter库也可以通过pip来安装。在命令行中输入以下命令:
pip install xlsxwriter
这条命令会从Python包索引(PyPI)下载xlsxwriter库的安装包,并自动完成安装过程。安装完成后,你就可以在Python脚本中导入并使用xlsxwriter库了。
xlsxwriter是一个用于创建Excel 2007+ XLSX文件的Python库。它提供了简单易用的接口,使得开发者可以轻松地生成Excel文件,并且支持多种高级功能,如格式设置、图表绘制、数据透视表等。xlsxwriter与Excel文件的兼容性非常好,生成的文件可以在Microsoft Excel中完美打开和编辑。
2、Pandas基础
在正式开始处理Excel文件之前,我们先来了解一下Pandas的基础知识,特别是DataFrame和Series这两个核心概念。
(1)DataFrame和Series简介
DataFrame:是Pandas中最重要的数据结构之一,它以表格的形式存储数据,类似于Excel中的工作表。DataFrame由行和列组成,每列可以是不同的数据类型(数值、字符串、布尔值等)。DataFrame提供了丰富的数据处理功能,如数据清洗、转换、合并、分组等。
Series:是Pandas中的一维数据结构,可以看作是DataFrame的一列。Series同样可以存储各种数据类型的数据,但它只有一个轴(即索引轴),用于表示数据的顺序。
(2)读取Excel文件到DataFrame
Pandas提供了read_excel函数来读取Excel文件,并将其内容加载到DataFrame对象中。以下是一个简单的示例代码:
import pandas as pd # 读取Excel文件
df = pd.read_excel('example.xlsx', sheet_name='Sheet1') # 显示DataFrame的前几行数据
print(df.head())
在这个示例中,read_excel函数读取了名为example.xlsx的Excel文件,并指定了sheet_name='Sheet1'来指定读取的工作表。读取完成后,Excel文件中的数据就被加载到了DataFrame对象df中,随后通过head()方法打印出了前几行数据以进行查看。
(3)DataFrame写入Excel文件
与读取Excel文件相对应,Pandas也提供了将DataFrame写入Excel文件的功能。这可以通过DataFrame.to_excel方法来实现。以下是一个简单的示例代码:
# 假设df是一个已经存在的DataFrame对象 # 将DataFrame写入Excel文件
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)
在这个示例中,to_excel方法将DataFrame对象df写入到了名为output.xlsx的Excel文件中,并指定了工作表的名称为Sheet1。index=False参数表示在写入时不包含DataFrame的索引列。这样,你就可以将处理后的数据保存回Excel文件,以便后续的分析或共享。
三、批量拆分Excel文件
1、拆分需求分析
在处理Excel文件时,经常需要根据不同的需求将文件拆分成多个部分。这些需求可能包括按工作表拆分,即将一个包含多个工作表的Excel文件拆分成多个只含有一个工作表的Excel文件;或者按数据条件拆分,即根据数据中的某个字段(如日期、类别等)将单个工作表中的数据拆分成多个文件。
●按工作表拆分:适用于需要将一个大型工作簿拆分成多个小型工作簿,每个小型工作簿只包含一个工作表的情况。
●按数据条件拆分:适用于需要对单个工作表中的数据根据特定条件进行分类,并将每类数据保存到不同文件中的情况。
2、实现步骤
(1)读取Excel文件中的所有工作表
首先,使用Pandas的read_excel函数(结合openpyxl作为引擎)读取Excel文件中的所有工作表。如果只需要按工作表拆分,可以直接遍历工作表名称;如果需要按数据条件拆分,则需要先读取整个工作表到DataFrame中。
(2)遍历工作表或DataFrame,根据条件拆分数据
●按工作表拆分:直接遍历工作表名称,并使用read_excel函数读取每个工作表到一个单独的DataFrame,然后写入新的Excel文件。
●按数据条件拆分:对DataFrame进行遍历或筛选,根据条件将数据分成多个部分,并将每部分数据写入新的Excel文件的不同工作表或不同文件中。
(3)将拆分后的数据保存到新的Excel文件中
使用Pandas的DataFrame.to_excel方法将拆分后的数据写入新的Excel文件。注意设置sheet_name参数以指定工作表名称,以及处理可能的文件命名冲突。
3、示例代码
(1)示例1:按工作表拆分Excel文件
测试Excel:
# _*_ coding : utf-8 _*_
# @Time : 2024-09-22 17:13
# @Author : 光仔December
# @File : Excel分割处理测试1
# @Project : Python_Projectsimport pandas as pd# 读取Excel文件,获取有几个sheet,以及其名称
xls = pd.ExcelFile('E:/Test/Test1.xlsx')
sheet_names = xls.sheet_names
# 遍历这些sheet,拿到每个sheet的数据,单独写到新Excel中:
for sheet_name in sheet_names:df = pd.read_excel(xls, sheet_name=sheet_name)# 写入到新文件中,新文件命名为“Test1_sheet具体名字”excel_name = 'Test1_'+sheet_name+'.xlsx'# 使用ExcelWriter上下文管理器,以openpyxl作为引擎读取Excel文件with pd.ExcelWriter('E:/Test/'+excel_name, engine='openpyxl', mode='w') as writer:df = pd.read_excel(xls, sheet_name=sheet_name)# 将该工作表内容写入到新文件df.to_excel(writer, sheet_name=sheet_name, index=False)print('《Test1.xlsx》中的“'+sheet_name+'”sheet已成功写入《'+excel_name+'》文件中!')
测试效果(分割后的文件):
(2)示例2:按数据条件(如商品)拆分Excel文件中的单个工作表
测试Excel:
# _*_ coding : utf-8 _*_
# @Time : 2024-09-22 17:28
# @Author : 光仔December
# @File : Excel分割处理测试2
# @Project : Python_Projectsimport pandas as pd # 假设我们按'日期'字段拆分数据
df = pd.read_excel('original.xlsx', sheet_name='Sheet1') # 假设'日期'列是字符串类型,并且格式为'YYYY-MM-DD'
# 转换为pandas的datetime类型以便操作
df['日期'] = pd.to_datetime(df['日期']) # 分组数据
grouped = df.groupby(df['日期'].dt.year) # 示例:按年份分组 # 遍历分组,写入新文件
for year, data in grouped: with pd.ExcelWriter(f'split_{year}.xlsx', engine='openpyxl', mode='w') as writer: data.to_excel(writer, sheet_name='Sheet1', index=False)
测试结果(分割后的文件):
4、注意事项
(1)文件命名冲突处理:在按条件拆分并保存文件时,可能会遇到文件名冲突的情况(如多个分组具有相同的名称或标签)。一种解决方案是在文件名中加入唯一标识符(如时间戳、随机数或递增的计数器)。
(2)性能优化(处理大文件时):处理大型Excel文件时,需要注意内存使用和处理时间。可以考虑使用分块读取(chunksize参数)来减少内存消耗,或者并行处理来加快处理速度。另外,对于非常大的文件,可能需要考虑使用数据库或专门的大数据处理工具来进行处理。
下面我们主要介绍一下“chunksize参数”:
在处理大型Excel文件时,由于Pandas默认会将整个文件加载到内存中,这可能会导致内存消耗过高,尤其是在处理包含大量数据的工作表时。为了缓解这个问题,Pandas的read_excel函数提供了一个chunksize参数,允许我们以分块的方式读取文件,即一次只加载文件的一部分到内存中。
以下是一个使用chunksize参数来分块读取大型Excel文件的例子:
import pandas as pd # 假设我们有一个非常大的Excel文件'large_file.xlsx',并且我们只对'Sheet1'中的数据感兴趣
chunksize = 10000 # 定义每个块的大小,这里以10000行为例 # 创建一个空的DataFrame来收集所有块的数据(如果需要的话)
# 注意:如果文件太大,可能不希望将所有数据都加载到内存中,而是直接处理每个块
all_data = pd.DataFrame() # 初始化空DataFrame,但在实际应用中可能不需要 # 使用chunksize参数读取文件
reader = pd.read_excel('large_file.xlsx', sheet_name='Sheet1', chunksize=chunksize) for chunk in reader: # 在这里处理每个块的数据 # 例如,可以直接处理数据(如筛选、转换等),或将其写入到数据库/另一个文件中 print(f"Processing chunk with shape {chunk.shape}") # 如果需要将数据保存到DataFrame中(注意:这可能会消耗大量内存) # all_data = pd.concat([all_data, chunk], ignore_index=True) # 如果需要,可以将每个块写入到不同的Excel文件中 # 注意:这可能会生成大量的文件 # chunk.to_excel(f'chunk_{chunk.iloc[0, some_index_column]}.xlsx', index=False) # 假设some_index_column是区分块的索引列 # 注意:上面的all_data.concat(...)和to_excel(...)部分被注释掉了,
# 因为在实际应用中,你可能不会想要将整个大文件的所有数据都加载到内存中,
# 而是直接对每个块进行处理或保存。
在这个例子中,chunksize被设置为10000,意味着每次迭代中,reader将只从Excel文件中读取10000行数据。然后,你可以在循环中对这些数据进行处理,比如进行数据分析、数据清洗或数据转换等操作。如果你需要将处理后的数据保存到某个地方(比如数据库或另一个文件),你应该在循环内部进行这些操作,而不是试图将所有数据都加载到内存中。
请注意,上面的代码示例中的chunk.iloc[0, some_index_column]部分是一个假设,用于说明如何可能基于块的某些内容来命名文件。在实际应用中,你可能需要根据实际的数据结构和需求来调整这部分代码。如果每个块都有一个唯一的标识符或索引列,你可以使用这些信息来命名输出文件。但是,请注意,如果每个块的第一行(或任何固定位置)都包含相同的标识符,那么所有块都可能会被写入到同一个文件中(这通常不是你想要的结果)。因此,你需要确保每个块的文件名都是唯一的。
四、批量合并Excel文件
1、合并需求分析
(1)合并多个Excel文件到单个文件的不同工作表:
这一需求常见于需要汇总多个相关但独立的Excel数据源时。每个Excel文件包含独特的信息,我们希望将它们各自作为一个工作表保存在一个新的Excel文件中,以便统一管理和分析。
(2)合并多个Excel文件的相同工作表到单个工作表:
当多个Excel文件具有相同结构的工作表(例如,每个文件都有一个名为"销售数据"的工作表),但数据不同时,我们可能希望将这些相同名称的工作表合并成一个单独的工作表,以便进行跨文件的汇总分析。
2、实现步骤
(1)遍历文件夹中的所有Excel文件
使用Python的os或pathlib库来遍历指定文件夹中的所有Excel文件(通常扩展名为.xlsx或.xls)。
(2)读取每个文件的内容
利用pandas库的read_excel函数读取每个Excel文件的内容。如果是合并到单个工作表,还需确定如何处理数据行(如是否需要跳过标题行等)。
(3)根据需求合并到单个DataFrame或不同的工作表中
●对于合并到不同工作表的情况,可以创建一个空的Excel写入器(如使用pandas.ExcelWriter),并在循环中逐个读取文件,将其内容写入不同的工作表。
●对于合并到单个工作表的情况,读取每个文件后,可以选择将DataFrame追加到列表中,最后使用pandas.concat合并所有DataFrame,然后写入新的Excel文件。
(4)将合并后的数据写入新的Excel文件
使用pandas.ExcelWriter的save方法或直接将合并后的DataFrame通过to_excel方法写入新的Excel文件。
3、示例代码
(1)示例1:合并多个Excel文件到单个文件的不同工作表
测试Excel:
# _*_ coding : utf-8 _*_
# @Time : 2024-09-22 17:45
# @Author : 光仔December
# @File : Excel合并处理测试1
# @Project : Python_Projectsimport pandas as pd
# 导入os库,用于处理文件和目录路径
import os# 定义要处理的文件夹路径
folder_path = 'E:/Test/Test3/'
# 定义合并后输出的Excel文件路径
output_file = 'E:/Test/Test3.xlsx'# 使用ExcelWriter上下文管理器,以xlsxwriter作为引擎创建Excel文件
# 这将确保文件在操作完成后被正确关闭
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:# 遍历指定文件夹中的所有文件for filename in os.listdir(folder_path):# 检查文件名是否以.xlsx结尾,以筛选出Excel文件if filename.endswith('.xlsx'):# 拼接文件夹路径和文件名,得到完整的文件路径file_path = os.path.join(folder_path, filename)# 使用pandas的read_excel函数读取Excel文件,并指定引擎为openpyxldf = pd.read_excel(file_path, engine='openpyxl')# 将读取的DataFrame写入到writer对象中,# 以文件名(去掉.xlsx后缀)作为工作表名,且不包括索引列df.to_excel(writer, sheet_name=filename.split('.')[0], index=False)# 输出提示信息,表明合并操作已完成并保存到指定文件
print('合并完成,已保存到', output_file)
测试结果:
(2)示例2:合并多个Excel文件的相同工作表到单个工作表
测试Excel:
# _*_ coding : utf-8 _*_
# @Time : 2024-09-22 18:15
# @Author : 光仔December
# @File : Excel合并处理测试2
# @Project : Python_Projectsimport pandas as pd
import os# 文件夹路径和要合并的工作表名
folder_path = 'E:/Test/Test4/'
sheet_name = '2024年'
output_file = 'E:/Test/Test4.xlsx'# 存储所有DataFrame的列表
all_data = []for filename in os.listdir(folder_path):if filename.endswith('.xlsx'):file_path = os.path.join(folder_path, filename)try:# 读取指定工作表df = pd.read_excel(file_path, sheet_name=sheet_name, engine='openpyxl')all_data.append(df)except Exception as e:print(f"Error reading {filename}: {e}")# 合并所有DataFrame
merged_df = pd.concat(all_data, ignore_index=True)# 写入新的Excel文件
merged_df.to_excel(output_file, index=False)print('合并完成,已保存到', output_file)
测试结果:
4、注意事项
(1)数据格式一致性检查
在合并之前,确保所有要合并的数据在格式上是一致的(如日期格式、数字格式等),否则可能需要进行预处理。
检查是否存在缺失值或异常值,并提前处理。
(2)合并时索引的处理
在合并到单个工作表时,特别是使用pd.concat时,要注意ignore_index参数的使用,它可以帮助重置索引,避免索引冲突。
如果原Excel文件中有索引列,且合并后希望保留作为数据的一部分,应确保在读取时将其作为普通列处理,而不是索引。
至此,有关Pandas如何批量拆分与合并Excel文件的所有内容介绍完毕,下一篇我们继续学习Pandas怎样实现groupby每个分组的apply
转载请注明出处:https://guangzai.blog.csdn.net/article/details/142440862