category-filtering-and-difficulty-analysis

Skill Steps

Step1 加载数据与环境配置

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import re

# 配置中文字体，确保图表正常显示
plt.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans', 'WenQuanYi Zen Hei']
plt.rcParams['axes.unicode_minus'] = False

def load_excel_data(file_path: str, skip_rows: int = 2):
    """读取并加载Excel文件中的数据，跳过标题行以获取原始数据"""
    # 技巧：处理合并单元格可使用 df.ffill() 等方法
    df = pd.read_excel(file_path, skiprows=skip_rows)
    return df

Step2 定义分类映射函数骨架

def categorize_data(item: str) -> str:
    """将具体项归类到大类中（分类映射函数骨架）"""
    if pd.isna(item):
        return '未知'
    if item in ['类别A1', '类别A2', '类别A3']:
        return '大类A'
    elif item in ['类别B1', '类别B2']:
        return '大类B'
    else:
        return '其他'

Step3 统一分析与可视化流程（柱状图、饼图、交叉分析）

def analyze_and_visualize(df: pd.DataFrame, category_col: str, group_col: str = None, output_path: str = './', top_n: int = None, custom_categorize=None):
    """统一分析与可视化流程：生成柱状图、饼图、交叉分析堆叠柱状图"""
    df_clean = df.copy()
    
    # 应用自定义分类规则
    if custom_categorize:
        df_clean[f'{category_col}大类'] = df_clean[category_col].apply(custom_categorize)
        analyze_col = f'{category_col}大类'
    else:
        analyze_col = category_col
    
    # value_counts + 占比统计
    counts = df_clean[analyze_col].value_counts()
    if top_n:
        counts = counts.head(top_n)
    
    fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 6))
    
    # 柱状图美化
    counts.plot(kind='bar', ax=ax1, color='skyblue', edgecolor='black')
    ax1.set_title(f'{analyze_col}分布（柱状图）', fontsize=14, fontweight='bold')
    ax1.set_xlabel(analyze_col, fontsize=12)
    ax1.set_ylabel('数量', fontsize=12)
    ax1.tick_params(axis='x', rotation=45)
    ax1.grid(axis='y', alpha=0.3)
    for i, v in enumerate(counts.values):
        ax1.text(i, v + 0.05, str(v), ha='center', va='bottom', fontweight='bold')
    
    # 饼图美化
    colors = plt.cm.Set3(np.linspace(0, 1, len(counts)))
    wedges, texts, autotexts = ax2.pie(counts.values, labels=counts.index, autopct='%1.1f%%', colors=colors, startangle=90)
    ax2.set_title(f'{analyze_col}分布（饼图）', fontsize=14, fontweight='bold')
    for text in texts:
        text.set_fontsize(10)
    for autotext in autotexts:
        autotext.set_fontsize(9)
        autotext.set_fontweight('bold')
    
    plt.tight_layout()
    plt.savefig(f'{output_path}{analyze_col}_分布图.png', dpi=300, bbox_inches='tight')
    plt.close()
    
    # 交叉分析 (crosstab)
    if group_col and group_col in df_clean.columns:
        cross_table = pd.crosstab(df_clean[group_col], df_clean[analyze_col])
        if top_n:
            cross_table = cross_table.head(top_n)
        plt.figure(figsize=(10, 6))
        cross_table.plot(kind='bar', stacked=True, colormap='viridis')
        plt.title(f'各{group_col}的{analyze_col}分布', fontsize=14, fontweight='bold')
        plt.xlabel(group_col, fontsize=12)
        plt.ylabel('数量', fontsize=12)
        plt.xticks(rotation=45)
        plt.legend(title=analyze_col, bbox_to_anchor=(1.05, 1), loc='upper left')
        plt.grid(axis='y', alpha=0.3)
        plt.tight_layout()
        plt.savefig(f'{output_path}交叉分析图.png', dpi=300, bbox_inches='tight')
        plt.close()