Excel Data Analysis And Report Generation

Skill Steps

This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.

Step1 基于指定列提取有效代码或进行分类映射，生成包含占比与总计行的统计表，并支持交叉分析。

# 分类映射函数骨架
def categorize_item(item_name):
    category_a_keywords = ['keyword1', 'keyword2'] # 占位示例
    if pd.isna(item_name):
        return '未知'
    if any(kw in str(item_name) for kw in category_a_keywords):
        return '类别A'
    return '其他'

target_col = '项目名称' # 替换为实际列名
group_col = '所属区域'  # 替换为实际分组列名

if target_col in combined_df.columns:
    combined_df['分类'] = combined_df[target_col].apply(categorize_item)
    
    # value_counts + 占比 + 总计行
    category_counts = combined_df['分类'].value_counts().reset_index()
    category_counts.columns = ['类别', '数量']
    total = category_counts['数量'].sum()
    category_counts['占比'] = (category_counts['数量'] / total).apply(lambda x: f'{x:.2%}')
    
    total_row = pd.DataFrame([{'类别': '总计', '数量': total, '占比': '100.00%'}])
    category_counts = pd.concat([category_counts, total_row], ignore_index=True)
    
    # 交叉分析 crosstab
    if group_col in combined_df.columns:
        cross_tb = pd.crosstab(combined_df[group_col], combined_df['分类'], margins=True, margins_name='总计')
        print("交叉分析结果:\n", cross_tb)

Step2 识别目标值超过限值的行，基于关键字定位并反向搜索限值以确保数据关联。

import re

exceed_rows = []
df_target = combined_df.copy()