pie-chart-data-analysis

Step1 读取文件并统计所有 Sheet 的行数，确认数据规模以决定处理策略。

import pandas as pd

file_path = input_file
total_rows = 0
sheet_names = []

try:
    if file_path.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file_path)
        sheet_names = excel_file.sheet_names
        # 统计所有工作表总行数
        for sheet in sheet_names:
            df_tmp = pd.read_excel(file_path, sheet_name=sheet)
            total_rows += len(df_tmp)
    elif file_path.endswith('.csv'):
        df = pd.read_csv(file_path)
        total_rows = len(df)
    else:
        raise ValueError("不支持的文件格式，仅支持 .xlsx 或 .csv")
except Exception as e:
    raise RuntimeError(f"文件读取失败: {e}")

is_large_file = total_rows >= 10000

Step2 自动识别分类列与数值列，执行数据清洗与格式转换。

import re

# 加载首个有效数据集
if file_path.endswith('.xlsx'):
    df = pd.read_excel(file_path, sheet_name=sheet_names[0])
else:
    df = pd.read_csv(file_path)

# 1. 识别数值目标列（如：金额、支出、得分、数量）
target_keywords = ['金额', '支出', '造价', '经费', '数量', '得分']
target_cols = [col for col in df.columns if any(k in col for k in target_keywords)]
target_col = target_cols[0] if target_cols else df.select_dtypes(include=['number']).columns[0]

# 2. 识别分类列（支持正则匹配中文序号或特定分类标识）
category_pattern = re.compile(r'[一二三四五六七八九十百]+|地区|类别|类型|状态')
category_cols = [col for col in df.columns if category_pattern.search(col)]
category_col = category_cols[0] if category_cols else df.select_dtypes(include=['object']).columns[0]

# 3. 数据清洗：处理合并单元格填充、缺失值及类型转换
df[category_col] = df[category_col].ffill() # 处理 Excel 合并单元格
df[target_col] = pd.to_numeric(df[target_col], errors='coerce')
clean_df = df[[category_col, target_col]].dropna()
clean_df.columns = ['category', 'value']

Step3 执行多维度聚合分析，计算占比及汇总统计。

# 分类汇总
summary_df = clean_df.groupby('category', as_index=False)['value'].sum()
total_val = summary_df['value'].sum()

# 计算占比并格式化
summary_df['percentage'] = (summary_df['value'] / total_val * 100).round(2)
summary_df = summary_df.sort_values(by='value', ascending=False)

# 构造总计行（可选）
total_row = pd.DataFrame([['总计', total_val, 100.0]], columns=summary_df.columns)
display_df = pd.concat([summary_df, total_row], ignore_index=True)

Step4 生成美化饼图并导出包含图表的 Excel 报告。

import matplotlib.pyplot as plt
from io import BytesIO
import base64
from openpyxl.drawing.image import Image

# 配置中英文字体
plt.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False

fig, ax = plt.subplots(figsize=(10, 7), dpi=120)
colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#FFEAA7', '#DDA0DD']

# 突出显示最大占比项
explode = [0.05 if i == 0 else 0 for i in range(len(summary_df))]

wedges, texts, autotexts = ax.pie(
    summary_df['value'],
    labels=summary_df['category'],
    autopct='%1.1f%%',
    startangle=140,
    colors=colors,
    explode=explode,
    shadow=True,
    pctdistance=0.85
)

# 添加中心白圈（环形图效果）
centre_circle = plt.Circle((0,0), 0.70, fc='white')
fig.gca().add_artist(centre_circle)

plt.title(f'{target_col} 分布分析', fontsize=15, pad=20)
ax.legend(wedges, summary_df['category'], title="分类明细", loc="center left", bbox_to_anchor=(1, 0, 0.5, 1))

# 保存图表到内存
img_buffer = BytesIO()
plt.savefig(img_buffer, format='png', bbox_inches='tight')
plt.close()

# 写入 Excel 并嵌入图表
output_path = 'analysis_report.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    display_df.to_excel(writer, sheet_name='统计汇总', index=False)
    ws = writer.book['统计汇总']
    img_buffer.seek(0)
    img = Image(img_buffer)
    ws.add_image(img, 'E2')

# 生成 Base64 下载链接
with open(output_path, "rb") as f:
    b64 = base64.b64encode(f.read()).decode()
download_url = f"data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}"

print(f"分析完成。总行数: {total_rows}，下载链接已生成。")