简单的思路就是通过下载的上网历史记录进行分析，分析出来的数据可以用于辅助网吧运营。
从晚饭后一直到十二点，通过一遍遍地和AI对话，最终搞出来的小工具。
接下来看看分析的效果吧。
[color=]代码（三段）贴到后面。
image.png (37.05 KB, 下载次数: 0)
下载附件
2024-12-28 14:18 上传
image.png (12.48 KB, 下载次数: 0)
下载附件
2024-12-28 14:19 上传
代码分享：
[Python] 纯文本查看 复制代码# App.py
import tkinter as tk
from tkinter import filedialog, messagebox, LabelFrame, Scrollbar, Text, ttk
import pandas as pd
import threading
import logging
# 配置日志
logging.basicConfig(filename='app.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
class App:
def __init__(self, root):
self.root = root
self.root.title("上网历史记录分析")
self.root.geometry("800x1000")
self.create_widgets()
def create_widgets(self):
self.top_frame = LabelFrame(self.root, text="文件选择")
self.top_frame.pack(side='top', fill='x', padx=10, pady=10)
self.file_path_label = tk.Label(self.top_frame, text="请选择一个Excel文件")
self.file_path_label.pack(side='left', padx=10, pady=10)
self.browse_button = tk.Button(self.top_frame, text="浏览", command=self.browse_file)
self.browse_button.pack(side='left', padx=10, pady=10)
self.analyze_button = tk.Button(self.top_frame, text="分析", command=self.run_analysis)
self.analyze_button.pack(side='left', padx=10, pady=10)
self.notebook = ttk.Notebook(self.root)
self.notebook.pack(side='top', fill='both', expand=True, padx=10, pady=10)
self.missing_seats_tab = tk.Frame(self.notebook)
self.detailed_stats_tab = tk.Frame(self.notebook)
self.notebook.add(self.missing_seats_tab, text='缺失座位号分析')
self.notebook.add(self.detailed_stats_tab, text='详细统计分析')
self.missing_seats_text = Text(self.missing_seats_tab, width=80, height=100)
self.missing_seats_text.pack(side='left', fill='x', padx=10, pady=5)
self.detailed_stats_text = Text(self.detailed_stats_tab, width=80, height=100)
self.detailed_stats_text.pack(side='left', fill='x', padx=10, pady=5)
self.missing_seats_scrollbar = Scrollbar(self.missing_seats_tab)
self.missing_seats_scrollbar.pack(side='right', fill='y')
self.detailed_stats_scrollbar = Scrollbar(self.detailed_stats_tab)
self.detailed_stats_scrollbar.pack(side='right', fill='y')
self.missing_seats_text.config(yscrollcommand=self.missing_seats_scrollbar.set)
self.detailed_stats_text.config(yscrollcommand=self.detailed_stats_scrollbar.set)
self.missing_seats_scrollbar.config(command=self.missing_seats_text.yview)
self.detailed_stats_scrollbar.config(command=self.detailed_stats_text.yview)
def browse_file(self):
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xls;*.xlsx")])
if file_path:
self.file_path_label.config(text=file_path)
def run_analysis(self):
file_path = self.file_path_label.cget("text")
if not file_path:
messagebox.showwarning("提示", "请先选择一个文件。")
return
try:
df = pd.read_excel(file_path, dtype={'证件号': str})
self.display_missing_seats_analysis(df)
self.display_detailed_stats_analysis(df)
except FileNotFoundError:
messagebox.showerror("错误", "文件未找到，请重新选择。")
logging.error("File not found: %s", file_path)
except pd.errors.ParserError as e:
messagebox.showerror("错误", f"解析Excel时出错：{e}")
logging.error("Excel parsing error: %s", e)
except Exception as e:
messagebox.showerror("错误", f"发生未知错误：{e}")
logging.error("Unhandled exception: %s", e)
def display_missing_seats_analysis(self, df):
from MissingSeatAnalyzer import MissingSeatAnalyzer
result, success = MissingSeatAnalyzer.find_missing_seats(df)
self.missing_seats_text.delete(1.0, tk.END)
self.missing_seats_text.insert(tk.END, result)
def display_detailed_stats_analysis(self, df):
from DetailedStatsAnalyzer import DetailedStatsAnalyzer
(df, total_consumption, unique_seat_count, start_time, end_time, unique_user_count,
male_count, female_count, top_users, bottom_five_seats, top_five_seats, gen_counts,
gen_consumptions, total_records) = DetailedStatsAnalyzer.analyze_detailed_stats(df)
self.detailed_stats_text.delete(1.0, tk.END)
self.detailed_stats_text.insert(tk.END, f"共有 {unique_seat_count} 台机器被使用\n")
self.detailed_stats_text.insert(tk.END, f"数据时间范围：{start_time} 到 {end_time}\n")
self.detailed_stats_text.insert(tk.END, f"共有 {unique_user_count} 位顾客到达本店\n")
self.detailed_stats_text.insert(tk.END, f"男性：{male_count}人，女性：{female_count}人\n")
self.detailed_stats_text.insert(tk.END, f"总消费笔数：{total_records}笔\n")
self.detailed_stats_text.insert(tk.END, f"总消费金额：{total_consumption}元\n")
top_users_str = "消费前二十名顾客：\n" + \
"\n".join(f"证件号：{row['证件号']}，姓名：{row['姓名']}，总消费：{row['总费用']}" for index, row in top_users.iterrows())
self.detailed_stats_text.insert(tk.END, top_users_str + "\n\n")
bottom_five_seats_str = "收入最少的五台机器：\n" + \
"\n".join(f"座位号：{row['座号']}，总收入：{row['总费用']}" for index, row in bottom_five_seats.iterrows())
self.detailed_stats_text.insert(tk.END, bottom_five_seats_str + "\n\n")
top_five_seats_str = "收入最多的五台机器：\n" + \
"\n".join(f"座位号：{row['座号']}，总收入：{row['总费用']}" for index, row in top_five_seats.iterrows())
self.detailed_stats_text.insert(tk.END, top_five_seats_str + "\n\n")
generation_label_text = f"80后：{gen_counts['80后']}人，90后：{gen_counts['90后']}人，00后：{gen_counts['00后']}人\n"
self.detailed_stats_text.insert(tk.END, generation_label_text)
generation_consumption_label_text = f"80后总消费：{gen_consumptions['80后']}元，90后总消费：{gen_consumptions['90后']}元，00后总消费：{gen_consumptions['00后']}元\n"
self.detailed_stats_text.insert(tk.END, generation_consumption_label_text)
if __name__ == "__main__":
root = tk.Tk()
app = App(root)
root.mainloop()
[Python] 纯文本查看 复制代码# DetailedStatsAnalyzer.py
import pandas as pd
class DetailedStatsAnalyzer:
@staticmethod
def analyze_detailed_stats(df):
df.dropna(subset=['座号', '刷卡时间', '下机时间', '证件号', '性别', '总费用', '附加费'], inplace=True)
df['总费用'] = pd.to_numeric(df['总费用'], errors='coerce')
df['附加费'] = pd.to_numeric(df['附加费'], errors='coerce')
df['总费用'] += df['附加费']
total_consumption = df['总费用'].sum()
df['刷卡时间'] = pd.to_datetime(df['刷卡时间'], format='%Y-%m-%d %H:%M:%S', errors='coerce')
df['下机时间'] = pd.to_datetime(df['下机时间'], format='%Y-%m-%d %H:%M:%S', errors='coerce')
df['出生年份'] = df['证件号'].str[6:10].astype(int)
bins = [1979, 1989, 1999, 2009]
labels = ['80后', '90后', '00后']
df_users = df.drop_duplicates('证件号', keep='first')
df_users['年龄段'] = pd.cut(df_users['出生年份'], bins=bins, labels=labels, include_lowest=True)
generation_counts = df_users['年龄段'].value_counts()
gen_counts = {label: generation_counts.get(label, 0) for label in labels}
df['年龄段'] = pd.cut(df['出生年份'], bins=bins, labels=labels, include_lowest=True)
grouped_consumption = df.groupby('年龄段')['总费用'].sum()
gen_consumptions = {label: grouped_consumption.get(label, 0) for label in labels}
total_records = df.shape[0]
unique_seat_numbers = df['座号'].unique()
unique_seat_count = len(unique_seat_numbers)
start_time = df['刷卡时间'].min()
end_time = df['下机时间'].max()
unique_user_documents = df['证件号'].unique()
unique_user_count = len(unique_user_documents)
df_gender = df.drop_duplicates('证件号', keep='first')
df_gender['性别'] = df_gender['性别'].astype(str)
male_count = df_gender[df_gender['性别'] == '男'].shape[0]
female_count = df_gender[df_gender['性别'] == '女'].shape[0]
grouped_users = df.groupby(['证件号', '姓名'])['总费用'].sum().reset_index()
top_users = grouped_users.sort_values('总费用', ascending=False).head(20)
top_users_consumption = top_users['总费用'].sum()
seats_revenue = df.groupby('座号')['总费用'].sum().reset_index()
bottom_five_seats = seats_revenue.sort_values('总费用', ascending=True).head(5)
top_five_seats = seats_revenue.sort_values('总费用', ascending=False).head(5)
return (df, total_consumption, unique_seat_count, start_time, end_time, unique_user_count,
male_count, female_count, top_users, bottom_five_seats, top_five_seats, gen_counts,
gen_consumptions, total_records)
[Python] 纯文本查看 复制代码# MissingSeatAnalyzer.py
import pandas as pd
import string
class MissingSeatAnalyzer:
@staticmethod
def find_missing_seats(df):
seat_numbers = df['座号'].dropna().unique()
if not seat_numbers.size:
return "文件中没有座位号数据。", False
min_seat = min(seat_numbers)
max_seat = max(seat_numbers)
all_seats = MissingSeatAnalyzer.generate_seats(min_seat, max_seat)
missing_seats = set(all_seats) - set(seat_numbers)
if missing_seats:
missing_seats_str = ', '.join(sorted(missing_seats))
return f"最小座位号：{min_seat}\n最大座位号：{max_seat}\n缺失的座位号：{missing_seats_str}\n", True
else:
return f"最小座位号：{min_seat}\n最大座位号：{max_seat}\n没有缺失的座位号。\n", True
@staticmethod
def generate_seats(min_seat, max_seat):
min_letter = min_seat[0]
max_letter = max_seat[0]
min_number = int(min_seat[1:])
max_number = int(max_seat[1:])
all_seats = []
for letter in string.ascii_uppercase:
if min_letter
