下面我会详细讲解“python按列索引提取文件夹内所有excel指定列汇总”的完整实例教程。在教程中,我会使用Python语言和一些第三方库来实现这个功能。
一、需求分析
首先,我们需要明确我们的需求:从一个文件夹内的所有Excel文件中,提取出指定列的数据,并将其汇总到一个新的文件中。具体来说,我们可以定义如下需求:
- 文件夹路径:我们需要指定需要处理的Excel文件所在的文件夹路径,可以是相对路径或者绝对路径;
- 列索引:我们需要指定需要提取的指定列的列索引,可以是列的数字索引或者A、B、C这样的字母索引;
- 表头:我们需要指定Excel文件中用作表头的行数,以便于在数据汇总时排除表头中的内容;
- 汇总数据到新Excel文件:我们需要将所有提取到的数据汇总到一个新的Excel文件中,这个文件需要放在指定的位置上。
二、技术实现
接下来,我们可以考虑如何使用Python语言来实现这个功能。
1. 获取文件夹内的所有Excel文件
我们可以使用Python的os
和glob
模块来获取指定文件夹内的所有Excel文件。具体实现代码如下:
import os
import glob
def get_excel_files(folder_path):
"""
获取指定文件夹内的所有Excel文件
"""
os.chdir(folder_path)
excel_files = glob.glob("*.xlsx") + glob.glob("*.xls")
return excel_files
在上面的代码中,我们使用了os.chdir()
函数来切换文件夹路径,可以确保后续操作是在指定的文件夹内进行的。然后,我们使用glob.glob()
函数来获取所有的Excel文件,其中*.xlsx
和*.xls
表示通配符,匹配所有后缀名为.xlsx和.xls的文件。最后,我们将所有的Excel文件名称存储到一个列表中,作为后续操作的输入。
2. 读取Excel文件并提取指定列的数据
我们可以使用Python的pandas
和openpyxl
这两个库来读取Excel文件,并提取指定列的数据。具体实现代码如下:
import pandas as pd
from openpyxl import load_workbook
def get_column_data(file_name, sheet_name, column_index, header_row):
"""
从Excel文件中提取指定列的数据
"""
# 读取Excel文件
wb = load_workbook(filename=file_name)
ws = wb[sheet_name]
# 读取数据
df = pd.DataFrame(ws.values)
header = df.iloc[header_row-1]
data = df.iloc[header_row:]
# 提取指定列的数据
column_data = data.iloc[:, column_index-1]
return column_data
在上面的代码中,我们使用了openpyxl.load_workbook()
函数来加载Excel文件,并使用pandas.DataFrame
函数将其转换为一个数据框。然后,我们使用iloc
函数来提取数据框中的指定列数据,并将其返回。
3. 将所有Excel文件的指定列数据汇总到一个数据框中
接下来,我们需要将所有Excel文件的指定列数据汇总到一个数据框中,以便于后续的数据处理。具体实现代码如下:
def merge_data_columns(folder_path, sheet_name, column_index, header_row):
"""
将所有Excel文件的指定列数据汇总到一个数据框中
"""
# 获取所有的Excel文件
excel_files = get_excel_files(folder_path)
# 读取指定列的数据
column_data_list = []
for file_name in excel_files:
column_data = get_column_data(file_name, sheet_name, column_index, header_row)
column_data_list.append(column_data)
all_data = pd.concat(column_data_list, ignore_index=True)
return all_data
在上面的代码中,我们首先使用get_excel_files()
函数获取指定文件夹内的所有Excel文件,然后循环遍历所有的Excel文件,使用get_column_data()
函数提取指定列的数据,并将所有的数据拼接到一个数据框中,最后将数据框返回。
4. 将汇总的数据保存到新的Excel文件中
最后,我们可以使用pandas
库将汇总的数据保存到一个新的Excel文件中。具体实现代码如下:
def save_to_file(data_frame, file_name, sheet_name):
"""
将数据保存到Excel文件中
"""
writer = pd.ExcelWriter(file_name, engine="openpyxl")
sheet_exists = False
try:
# 尝试打开已经存在的工作表
writer.book = load_workbook(file_name)
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
sheet_exists = True
except:
# 如果不存在,则创建一个新工作表
pass
if sheet_exists:
# 如果工作表已经存在,则在现有工作表上追加数据
data_frame.to_excel(writer, sheet_name, index=False, header=False, startrow=writer.sheets[sheet_name].max_row)
else:
# 如果工作表不存在,则新建一个工作表
data_frame.to_excel(writer, sheet_name, index=False)
writer.save()
在上面的代码中,我们使用pandas.ExcelWriter()
函数创建一个Excel写入器,然后使用openpyxl.load_workbook()
函数打开已存在的工作表,或使用pandas.DataFrame.to_excel()
函数在新的工作表上写入数据。
三、示例说明
最后,我可以给出两个示例来介绍如何使用上面的实现代码。假设我们有如下的Excel文件夹:
D:\
└─Excel Files
└─SampleData
├─Jan.xlsx
├─Feb.xlsx
└─Mar.xlsx
每个Excel文件包含如下数据:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | ID | Name | Age | City | Dept |
2 | 001 | Bob | 31 | LDN | HR |
3 | 002 | John | 28 | NYC | IT |
4 | 003 | Kate | 30 | LDN | IT |
5 | 004 | Tom | 35 | NYC | Sales |
6 | 005 | Lucy | 40 | NYC | HR |
7 | 006 | Gary | 42 | LDN | HR |
示例一:提取每个Excel文件的第三列数据,汇总到一个新的Excel文件中
# 读取所有的Excel文件,并将第三列数据汇总到一个数据框中
folder_path = r"D:\Excel Files\SampleData"
header_row = 1
sheet_name = "Sheet1"
column_index = 3
all_data = merge_data_columns(folder_path, sheet_name, column_index, header_row)
# 将汇总的数据保存到一个新的Excel文件中
file_name = r"D:\Excel Files\Summary.xlsx"
save_to_file(all_data, file_name, sheet_name)
在上面的代码中,我们首先设置文件夹路径、表头、工作表名称和需要提取的列索引等参数,然后使用merge_data_columns()
函数读取Excel文件并提取指定列数据,最后使用save_to_file()
函数将汇总的数据保存到一个新的Excel文件中。
示例二:提取每个Excel文件的第二列、第四列数据,汇总到一个新的Excel文件中
# 读取所有的Excel文件,并将第二列和第四列数据汇总到一个数据框中
folder_path = r"D:\Excel Files\SampleData"
header_row = 1
sheet_name = "Sheet1"
column_index_list = [2, 4]
column_data_list = []
for i in column_index_list:
column_data = merge_data_columns(folder_path, sheet_name, i, header_row)
column_data_list.append(column_data)
all_data = pd.concat(column_data_list, axis=1)
# 将汇总的数据保存到一个新的Excel文件中
file_name = r"D:\Excel Files\Summary.xlsx"
save_to_file(all_data, file_name, sheet_name)
在上面的代码中,我们首先设置文件夹路径、表头、工作表名称和需要提取的列索引列表等参数。然后,我们循环遍历每个需要提取的列,使用merge_data_columns()
函数读取Excel文件并提取指定列数据,并将所有的列数据合并到一个数据框中。最后,我们使用save_to_file()
函数将汇总的数据保存到一个新的Excel文件中。
四、总结
通过上面的实例教程,我们学习了如何使用Python语言和一些第三方库来实现按列索引提取文件夹内所有Excel指定列汇总的功能。此外,我们还学习了如何使用函数和循环等技巧,能够提高代码的可重用性和可读性。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:python按列索引提取文件夹内所有excel指定列汇总(示例代码) - Python技术站