Skill Steps
This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.
Step1 基于指定列提取有效代码或进行分类映射，生成包含占比与总计行的统计表，并支持交叉分析。
python# 分类映射函数骨架 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 识别目标值超过限值的行，基于关键字定位并反向搜索限值以确保数据关联。
pythonimport re exceed_rows = [] df_target = combined_df.copy() for i, row in df_target.iterrows(): if '共计' in str(row.iloc[0]): try: target_val = float(row.iloc[8]) # 目标值所在列索引 except (ValueError, TypeError): continue limit_val = None structure_name = "未知结构" # 反向搜索限值 for j in range(i-1, max(0, i-15), -1): check_row = df_target.iloc[j, :] check_str = ' '.join([str(x) for x in check_row.values if pd.notna(x)]) if '限值' in check_str: # 数据清洗正则表达式 match = re.search(r'限值([\d.]+)', check_str) if match: limit_val = float(match.group(1)) for k in range(j-1, max(0, j-5), -1): name_row = df_target.iloc[k, 0] if pd.notna(name_row) and '关键字' in str(name_row): structure_name = str(name_row) break break # 多维度评分/分级算法结构 if limit_val is not None and target_val > limit_val: severity = '高' if (target_val - limit_val) > 10 else '中' exceed_rows.append({ 'row_index': i, 'structure_name': structure_name, 'target_val': target_val, 'limit': limit_val, 'exceed_value': target_val - limit_val, 'severity': severity })
Step3 生成高分辨率可视化图表展示分类占比，保存统计结果并生成沙箱下载链接。
pythonimport matplotlib.pyplot as plt import matplotlib # 中英文字体配置 matplotlib.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans'] matplotlib.rcParams['axes.unicode_minus'] = False # 准备图表数据 (排除总计行) plot_data = category_counts[category_counts['类别'] != '总计'] categories = plot_data['类别'].tolist() counts = plot_data['数量'].tolist() # 图表美化（dpi、颜色方案、标签位置） fig, ax = plt.subplots(figsize=(10, 8)) colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#F9A826'] explode = [0.05] * len(categories) wedges, texts, autotexts = ax.pie( counts, labels=categories, autopct='%1.1f%%', startangle=90, colors=colors[:len(categories)], explode=explode, shadow=True, textprops={'fontsize': 12} ) ax.set_title('各类别数量占比分析', fontsize=16, fontweight='bold', pad=20) ax.legend(wedges, categories, title="类别", loc="center left", bbox_to_anchor=(1, 0, 0.5, 1)) # 保存图表 chart_path = os.path.join(output_dir, 'category_analysis.png') plt.savefig(chart_path, dpi=150, bbox_inches='tight') # 保存统计结果并生成下载链接 output_path = os.path.join(output_dir, 'analysis_result.xlsx') category_counts.to_excel(output_path, index=False) print(f"统计结果已保存至: {output_path}") print(f"下载链接: [下载统计结果](sandbox:{output_path})") print(f"图表下载链接: [下载图表](sandbox:{chart_path})")
