image.png (73.51 KB, 下载次数: 0)
下载附件
2024-12-31 23:00 上传
[Python] 纯文本查看 复制代码import tkinter as tk
from tkinter import filedialog, ttk
import pandas as pd
from typing import Dict, Optional
from pathlib import Path
from datetime import datetime
import os
class ExcelDataAnalyzer:
# 类常量定义
WINDOW_TITLE = "Excel数据浏览器与分析器"
WINDOW_SIZE = "800x1000"
BUTTON_STYLE = {
'foreground': 'black',
'background': 'lightblue',
'font': ('Arial', 10, 'bold')
}
EXPORT_FOLDER = "数据分析结果"
def __init__(self, root: tk.Tk):
self.root = root
self.root.title(self.WINDOW_TITLE)
self.root.geometry(self.WINDOW_SIZE)
self.data: Optional[pd.DataFrame] = None
self.setup_ui()
def setup_ui(self):
"""设置UI布局"""
self._configure_root()
main_frame = self._create_main_frame()
self._setup_buttons(main_frame)
self._setup_date_selectors(main_frame)
self._setup_labels(main_frame)
def _configure_root(self):
"""配置根窗口布局"""
self.root.grid_rowconfigure(0, weight=1)
self.root.grid_columnconfigure(0, weight=1)
def _create_main_frame(self) -> ttk.Frame:
"""创建主框架"""
main_frame = ttk.Frame(self.root)
main_frame.grid(sticky='nsew', padx=10, pady=10)
return main_frame
def _setup_buttons(self, main_frame: ttk.Frame):
"""设置按钮"""
button_style = ttk.Style()
button_style.configure('TButton', **self.BUTTON_STYLE)
self.open_button = ttk.Button(
main_frame,
text="打开Excel文件",
command=self.open_file,
style='TButton'
)
self.open_button.grid(row=0, column=0, pady=20, sticky='w')
self.analyze_button = ttk.Button(
main_frame,
text="分析数据",
command=self.analyze_data,
style='TButton'
)
self.analyze_button.grid(row=5, column=0, pady=20, sticky='w')
self.export_button = ttk.Button(
main_frame,
text="导出分析结果",
command=self.export_results,
style='TButton'
)
self.export_button.grid(row=5, column=1, pady=20, padx=10, sticky='w')
def open_file(self):
"""打开Excel文件并加载数据"""
try:
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
if not file_path:
self.file_label.config(text="未选择文件")
return
self.data = pd.read_excel(file_path)
self.file_label.config(text=f"已选择文件：{Path(file_path).name}")
self._update_date_menus()
except Exception as e:
self.file_label.config(text=f"文件读取错误: {str(e)}")
def _update_date_menus(self):
"""更新日期选择菜单"""
if self.data is not None and '日期' in self.data.columns:
dates = self.data['日期'].dt.strftime('%Y-%m-%d').tolist()
self.date_var.set(dates[0])
self.date_menu['values'] = dates
self.end_date_menu['values'] = dates
def analyze_data(self):
"""分析数据入口点"""
if self.data is None:
self.result_label.config(text="请先选择文件")
return
start_date = self.date_var.get()
end_date = self.end_date_var.get() or start_date
self.handle_date_range(start_date, end_date)
def calculate_and_display(self, date_data: pd.DataFrame):
"""计算并显示结果"""
try:
results = self._calculate_metrics(date_data)
self._current_results = results # 保存当前分析结果
self.display_results(results)
except Exception as e:
self.result_label.config(text=f"计算错误: {str(e)}")
self._current_results = None
def _calculate_metrics(self, date_data: pd.DataFrame) -> Dict[str, float]:
"""计算所有指标"""
try:
# 基础数据获取
impressions = date_data['展现量'].sum()
clicks = date_data['点击量'].sum()
customer_price = date_data['客单价'].mean()
conversion_rate = date_data['转化率'].mean()
revenue = date_data['营业额'].sum()
uv = date_data['UV'].sum()
promotion_cost = date_data['推广花费'].sum()
next_bid = date_data['下一名出价'].mean()
next_quality_score = date_data['下一名质量得分'].mean()
quality_score = date_data['质量得分'].mean()
profit_per_sale = date_data['成交一笔的利润'].mean()
required_traffic_per_sale = date_data['成交一笔所需的流量'].mean()
bid = date_data['出价'].mean()
direct_cost = date_data['直通车花费'].sum()
gross_margin = date_data['毛利率'].mean()
daily_sales_amount = date_data['当日成交金额'].sum()
cpc = date_data['平均点击费用'].mean()
# 计算各项指标
click_rate = (clicks / impressions * 100) if impressions else 0
traffic = impressions * (click_rate / 100)
sales = traffic * customer_price * conversion_rate
uv_value = revenue / uv if uv else 0
pv_value = uv_value * (click_rate / 100)
roi = sales / promotion_cost if promotion_cost else 0
actual_cost = next_bid * next_quality_score * quality_score + 0.01
traffic_value = profit_per_sale / required_traffic_per_sale if required_traffic_per_sale else 0
number_of_sales = conversion_rate * traffic
direct_ranking = bid * quality_score
traffic2 = direct_cost / cpc if cpc else 0
profit = revenue * gross_margin
direct_benefit = daily_sales_amount * gross_margin - direct_cost
direct_display = clicks / (click_rate / 100) if click_rate else 0
average_click_cost_ppc = direct_cost / clicks if clicks else 0
average_click_cost_cpc = direct_cost / clicks if clicks else 0
cost = average_click_cost_ppc * clicks
return {
"销售额": sales,
"流量": traffic,
"点击率": click_rate,
"UV价值": uv_value,
"PV价值": pv_value,
"ROI": roi,
"实际扣费": actual_cost,
"流量价值": traffic_value,
"成交笔数": number_of_sales,
"直通车排名": direct_ranking,
"营业额": revenue,
"流量2": traffic2,
"利润": profit,
"直通车效益": direct_benefit,
"直通车展现量": direct_display,
"平均点击花费（PPC）": average_click_cost_ppc,
"平均点击费用（CPC）": average_click_cost_cpc,
"花费": cost
}
except Exception as e:
self._current_results = None
raise Exception(f"计算指标时出错: {str(e)}")
def display_results(self, results: Dict[str, float]):
"""显示计算结果"""
for key, value in results.items():
if key in self.labels:
self.labels[key].config(text=f"{key}：{value:.2f}")
def _setup_date_selectors(self, main_frame: ttk.Frame):
"""设置日期选择器"""
# 文件标签
self.file_label = ttk.Label(main_frame, text="未选择文件", font=('Arial', 10))
self.file_label.grid(row=1, column=0, pady=10, sticky='w')
# 开始日期选择器
self.date_var = tk.StringVar(self.root)
self.date_menu = ttk.Combobox(
main_frame,
textvariable=self.date_var,
state='readonly'
)
self.date_menu.grid(row=2, column=0, pady=10, sticky='w')
# 结束日期选择器
self.end_date_var = tk.StringVar(self.root)
self.end_date_menu = ttk.Combobox(
main_frame,
textvariable=self.end_date_var,
state='readonly'
)
self.end_date_menu.grid(row=3, column=0, pady=10, sticky='w')
def _setup_labels(self, main_frame: ttk.Frame):
"""设置结果显示标签"""
# 结果标签
self.result_label = ttk.Label(
main_frame,
text="",
wraplength=400,
font=('Arial', 10)
)
self.result_label.grid(row=6, column=0, pady=10, sticky='w')
# 创建标签框架
labels_frame = ttk.Frame(main_frame)
labels_frame.grid(row=7, column=0, pady=10, sticky='w')
# 初始化所有指标标签
self.labels = {
"销售额": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"流量": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"点击率": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"UV价值": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"PV价值": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"ROI": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"实际扣费": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"流量价值": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"成交笔数": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"直通车排名": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"营业额": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"流量2": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"利润": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"直通车效益": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"直通车展现量": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"平均点击花费（PPC）": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"平均点击费用（CPC）": ttk.Label(labels_frame, text="", font=('Arial', 10)),
"花费": ttk.Label(labels_frame, text="", font=('Arial', 10))
}
# 布局所有标签
for index, (label, widget) in enumerate(self.labels.items()):
widget.grid(row=index, column=0, pady=5, sticky='w')
def handle_date_range(self, start_date: str, end_date: str):
"""处理日期范围的数据分析"""
if start_date and end_date:
if start_date > end_date:
self.result_label.config(text="开始日期应在结束日期之前")
return
date_data = self.data[
(self.data['日期'] >= pd.to_datetime(start_date)) &
(self.data['日期'] end_date:
self.result_label.config(text="开始日期应在结束日期之前")
return
# 创建导出文件夹
if not os.path.exists(self.EXPORT_FOLDER):
os.makedirs(self.EXPORT_FOLDER)
# 生成文件名
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
filename = f"数据分析_{start_date.strftime('%Y%m%d')}_{end_date.strftime('%Y%m%d')}_{timestamp}.xlsx"
filepath = os.path.join(self.EXPORT_FOLDER, filename)
# 存储每天的分析结果
daily_results = []
# 遍历日期范围内的每一天
current_date = start_date
while current_date max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = (max_length + 2)
worksheet.column_dimensions[column[0].column_letter].width = adjusted_width
self.result_label.config(text=f"分析结果已导出到：{filepath}")
except Exception as e:
self.result_label.config(text=f"导出失败：{str(e)}")
def _calculate_summary(self, daily_data: pd.DataFrame) -> Dict[str, float]:
"""计算区间汇总数据"""
summary = {}
# 需要求和的指标
sum_metrics = [
"销售额", "营业额", "利润", "直通车效益", "花费",
"直通车展现量", "流量", "流量2", "成交笔数"
]
# 需要求平均的指标
avg_metrics = [
"点击率", "UV价值", "PV价值", "ROI", "实际扣费",
"流量价值", "直通车排名", "平均点击花费（PPC）",
"平均点击费用（CPC）"
]
# 计算汇总值
for metric in sum_metrics:
if metric in daily_data.columns:
summary[metric] = daily_data[metric].sum()
for metric in avg_metrics:
if metric in daily_data.columns:
summary[metric] = daily_data[metric].mean()
return summary
if __name__ == "__main__":
root = tk.Tk()
app = ExcelDataAnalyzer(root)
root.mainloop()
演示数据 - 副本.rar
(10 KB, 下载次数: 65)
2024-12-31 22:57 上传
点击文件名下载附件
下载积分: 吾爱币 -1 CB
数据分析_20240101_20240106_20241230_212516.rar
(6.95 KB, 下载次数: 89)
2024-12-31 22:58 上传
点击文件名下载附件
下载积分: 吾爱币 -1 CB
