迭代点
这段代码的主要功能是优化前两天发的版本数据合并【Python】Python3.6处理数据实例：批量处理xlsx，适用于电商单品数据合并成类目/品牌数据
用于处理位于指定目录下的多个Excel文件，并将处理结果汇总到一个新的Excel文件中。以下是对其功能、实现模块以及优缺点的分析。
功能概述
- 文件处理：遍历指定文件夹中的所有Excel文件（除了汇总文件），并对这些文件进行处理。
- 类目映射：根据定义好的类目映射规则更新每个文件中的“新苏宁品类名称”列。
- 数据汇总：计算每个文件的汇总统计数据，并将其存储到一个队列中，以备后续合并。
- 多进程并发处理：利用
multiprocessing模块并行处理文件，提高处理效率。 - 汇总文件创建与合并：创建一个汇总文件，并将所有文件的汇总数据合并到该文件中。
- 日志记录：记录处理过程中的重要信息和错误信息。
- 状态管理：记录并管理最后处理过的文件的时间戳，以确保只处理新的或修改过的文件。
实现模块
- pandas: 用于数据处理、文件读写等。
- numpy: 用于数值计算。
- os: 用于文件路径操作。
- logging: 用于日志记录。
- json: 用于状态文件的读写。
- openpyxl: 用于读写Excel文件。
- multiprocessing: 用于多进程并发处理。
- datetime: 用于日期和时间的处理。
优点
- 高效处理大量文件：通过多进程并发处理提高了处理大量文件的速度。
- 数据预处理：对数据进行了适当的预处理，如类目映射和数据汇总，提高了数据质量。
- 状态管理：通过状态文件记录最后处理过的文件的时间戳，避免重复处理旧文件。
- 异常处理：对于处理过程中可能出现的异常进行了捕获，并记录了错误信息。
- 日志记录：记录处理过程中的关键信息，便于追踪处理流程和调试问题。
缺点
- 内存消耗：每个进程都需要加载整个文件到内存中进行处理，当文件非常大时可能会导致内存不足。
- 进程间通信开销：使用
multiprocessing.Manager创建的队列在进程间传递数据时会产生一定的性能开销。 - 文件锁问题：如果两个进程试图同时写入同一个文件（例如汇总文件），可能会导致文件锁冲突。
- 资源管理：虽然代码使用了上下文管理器来管理资源，但在异常情况下可能仍需要更精细的资源释放机制。
- 可读性和维护性：代码较为复杂，尤其是在多进程部分，这可能增加了理解和维护的难度。
改进方向
- 内存优化：可以考虑使用
pandas的分块读取功能来减少内存占用。 - 日志管理：可以将日志输出到单独的日志文件中，避免进程间的文件锁问题。
- 错误处理：增加更详细的错误处理逻辑，比如重试机制。
- 并行度调整：根据实际硬件配置调整进程池的最大工作进程数，以达到最优性能。
- 代码结构改进：可以通过封装函数、类等方式提高代码的可读性和可维护性。
以下是完整代码
import pandas as pd
import numpy as np
import os
import logging
import json
import openpyxl
from datetime import datetime
from openpyxl import load_workbook
import threading
import multiprocessing as mp
from openpyxl.styles import Font, Border, Side, Alignment
# 日志配置
logging.basicConfig(level=logging.INFO)
# 定义字体和边框样式
font_style = Font(name='SimSun', size=10)
border_style = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# 文件路径
folder_path = 'inputpath' # 这里可以换成你需要批量处理文件夹的存储路径
summary_file_path = os.path.join(folder_path, f'汇总_{datetime.now().strftime("%Y%m%d")}.xlsx')
state_file_path = os.path.join(folder_path, 'last_processed.json')
# 定义品类映射规则
category_mapping = {
("厨房卫浴电器",): "厨卫",
("厨房工具",): "厨具",
("家装建材及五金",): "卫浴定制",
("生活电器", "厨房电器"): "家用厨电",
}
#全局定义列名
columns = ['统计周期', '新品类名称', '品牌名称', '一级类目', '二级类目', '三级类目',
'四级类目', '商品访客数', '商品浏览量', '平均停留时长', '商品收藏人数', '商品加购件数',
'商品加购人数', '下单买家数', '下单件数', '下单金额', '下单转化率', '支付买家数', '支付件数',
'支付金额', '支付转化率', '支付新买家数', '支付老买家数', '老买家支付金额', '聚划算支付金额',
'客单价', '访客平均价值', '售中售后成功退款金额', '退款占比', '年累计支付金额', '月累计支付金额',
'月累计支付件数', '搜索引导支付转化率', '搜索引导访客数', '搜索引导支付买家数']
# 初始化汇总文件
def initialize_summary_file():
df = pd.DataFrame(columns=columns)
with pd.ExcelWriter(summary_file_path, engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='汇总', index=False)
# 获取待处理的文件列表
def get_files_to_process(last_processed_time):
files_to_process = []
for filename in os.listdir(folder_path):
if filename.endswith('.xlsx') and filename != os.path.basename(summary_file_path):
filepath = os.path.join(folder_path, filename)
file_mtime = os.path.getmtime(filepath)
if not last_processed_time or file_mtime > last_processed_time:
files_to_process.append(filepath)
files_to_process.sort(key=lambda x: os.path.getmtime(x))
return files_to_process
# 读取状态文件
def read_last_processed():
try:
with open(state_file_path, 'r') as f:
state = json.load(f)
return state.get('last_processed_time', None)
except Exception:
return None
# 写入状态文件
def write_last_processed(filepath):
state = {'last_processed_time': os.path.getmtime(filepath)}
try:
with open(state_file_path, 'w') as f:
json.dump(state, f)
except Exception:
logging.error(f"{datetime.now()}:写入状态文件时出错")
# 类目映射规则
def map_category(row):
categories = [
str(row["一级类目"]),
str(row["二级类目"]),
str(row["三级类目"]),
str(row["四级类目"])
]
for keys, new_category in category_mapping.items():
if all([cat == key for cat, key in zip(categories, keys) if key]):
return new_category
return ''
# 计算汇总统计数据
def calculate_summary_stats(df):
numeric_columns = ['商品访客数', '商品浏览量', '平均停留时长', '商品收藏人数', '商品加购件数',
'商品加购人数', '下单买家数', '下单件数', '下单金额',
'支付买家数', '支付件数', '支付金额', '支付新买家数',
'支付老买家数', '老买家支付金额', '聚划算支付金额',
'售中售后成功退款金额', '年累计支付金额', '月累计支付金额',
'月累计支付件数', '搜索引导访客数', '搜索引导支付买家数']
for col in numeric_columns:
df[col] = pd.to_numeric(df[col], errors='coerce')
# 保留所有列，即使在汇总操作中未直接使用
grouping_columns = ['新品类名称', '品牌名称', '一级类目', '二级类目', '三级类目', '四级类目']
summary_stats = df.groupby(grouping_columns).agg({
'统计周期': 'first',
'商品访客数': 'sum',
'商品浏览量': 'sum',
'平均停留时长': 'mean',
'商品收藏人数': 'sum',
'商品加购件数': 'sum',
'商品加购人数': 'sum',
'下单买家数': 'sum',
}).reset_index()
# 处理分母为0的情况
summary_stats.fillna(0, inplace=True)
summary_stats.replace([np.inf, -np.inf], np.nan, inplace=True)
# 将结果除以10000转换成万
for col in numeric_columns:
summary_stats[col] /= 10000
# 添加额外的比率和指标
summary_stats['下单转化率'] = summary_stats['下单买家数'] / summary_stats['商品访客数']
summary_stats['支付转化率'] = summary_stats['支付买家数'] / summary_stats['商品访客数']
summary_stats['客单价'] = summary_stats['支付金额'] / summary_stats['支付买家数']
summary_stats['访客平均价值'] = summary_stats['支付金额'] / summary_stats['商品访客数']
summary_stats['退款占比'] = summary_stats['售中售后成功退款金额'] / summary_stats['支付金额']
summary_stats['搜索引导支付转化率'] = summary_stats['搜索引导支付买家数'] / summary_stats['搜索引导访客数']
return summary_stats
# 处理单个文件类目映射
def process_file(filepath, queue):
try:
df = pd.read_excel(filepath)
if '品类编码' in df.columns:
df.rename(columns={'品类编码': '新品类名称'}, inplace=True)
df['新品类名称'] = ''
# 用映射函数填充新品类名称
df['新品类名称'] = df.apply(map_category, axis=1).fillna('')
summary_stats = calculate_summary_stats(df)
# 保存修改后的Excel文件
df.to_excel(filepath, index=False)
# 存到序列里面等待存到汇总数据里
queue.put((filepath, summary_stats))
# 将分类汇总数据保存回原文件
with pd.ExcelWriter(filepath, engine='openpyxl', mode='a') as writer:
summary_stats.to_excel(writer, sheet_name='分类汇总', index=False, columns=columns)
# 在处理完文件后，立即保存当前文件的最后修改时间
write_last_processed(filepath)
logging.info(f"{datetime.now()}:类目映射处理完成: {filepath}")
except Exception as e:
logging.error(f"{datetime.now()}:类目映射处理 {filepath} 时出错: {e}")
# 定义格式化单元格的函数
def format_cells(ws):
# 定义不同格式的字典，键是格式，值是需要此格式的列名列表
formats = {
'0.00%': ['下单转化率', '支付转化率', '搜索引导支付转化率'],
'0%': ['退款占比'],
'0.00':['商品访客数', '商品浏览量', '商品收藏人数', '商品加购件数', '商品加购人数',
'下单买家数', '下单件数', '支付买家数', '支付件数', '支付新买家数',
'支付老买家数', '月累计支付件数', '搜索引导访客数', '搜索引导支付买家数'],
'0': ['平均停留时长', '下单金额', '支付金额','老买家支付金额', '聚划算支付金额',
'售中售后成功退款金额', '年累计支付金额', '月累计支付金额', '客单价', '访客平均价值'],
'@': ['统计周期', '新品类名称', '品牌名称', '一级类目', '二级类目',
'三级类目', '四级类目'] # @ 表示文本格式
}
# 遍历工作表中的每一列
for column_cells in ws.columns:
header = column_cells[0].value
# 设置标题行的样式
column_cells[0].font = Font(name='SimSun', size=12, bold=True)
column_cells[0].border = border_style
column_cells[0].alignment = Alignment(horizontal='center') # 居中对齐标题
# 根据列名决定使用哪种格式
for fmt, column_names in formats.items():
if header in column_names:
for cell in column_cells[1:]:
cell.number_format = fmt
cell.font = font_style
cell.border = border_style
break
# 如果列头不在格式字典中，那么应用默认格式（即字体和边框，无数字格式）
elif column_cells.index(column_cells[0]) > 0: # 确保不是第一列（假设第一列不需要格式化）
for cell in column_cells[1:]:
cell.font = font_style
cell.border = border_style
# 合并所有文件的汇总数据
def merge_summary_stats(queue):
summary_df_list = []
while not queue.empty():
_, summary_stats = queue.get()
summary_df_list.append(summary_stats)
# 直接合并所有汇总数据，不需要再次分组和聚合
final_summary = pd.concat(summary_df_list, ignore_index=True)
# 按统计周期排序
final_summary = final_summary.sort_values(by='统计周期')
# 写入Excel
try:
with pd.ExcelWriter(summary_file_path, mode='a', if_sheet_exists='replace', engine='openpyxl') as writer:
# 读取现有工作簿
book = load_workbook(summary_file_path)
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
# 获取汇总Sheet，如果不存在，则初始化
if '汇总' not in writer.sheets:
summary_stats.to_excel(writer, sheet_name='汇总', index=False)
else:
# 获取现有汇总Sheet的最后一行，确保追加数据时不覆盖原始数据
existing_data = pd.read_excel(summary_file_path, sheet_name='汇总')
startrow = existing_data.shape[0] + 1
final_summary.to_excel(writer, sheet_name='汇总', index=False, columns=columns) #往后缩进了一列
# 加载工作簿并获取汇总Sheet
wb = load_workbook(summary_file_path)
ws = wb['汇总']
# 调用格式化函数
format_cells(ws)
# 保存更改
wb.save(summary_file_path)
logging.info(f"{datetime.now()}:所有文件的汇总数据合并并格式化完成。")
except Exception as e:
logging.error(f"{datetime.now()}:追加数据至汇总文件并格式化时出错: {e}")
# 主函数
def main():
manager = mp.Manager()
queue = manager.Queue()
# 确保汇总文件在开始处理之前已经被初始化
initialize_summary_file()
last_processed = read_last_processed()
files_to_process = get_files_to_process(last_processed)
total_files = len(files_to_process)
logging.info(f"{datetime.now()}:开始处理 {len(files_to_process)} 个文件...")
processed_files = 0
# 并行处理
with mp.Pool(processes=mp.cpu_count()) as pool:
results = [pool.apply_async(process_file, args=(file, queue)) for file in files_to_process]
for res in results:
res.get()
processed_files += 1
logging.info(f"{datetime.now()}:已处理 {processed_files}/{total_files} 个文件。")
merge_summary_stats(queue)
logging.info(f"{datetime.now()}:所有文件处理完成。")
if __name__ == '__main__':
main()
扫一扫
3310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
