dynamic-percentage-and-large-file-analysis

Skill Steps

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

Step 1: Dynamically locate key fields in the data, extract values by matching keywords through line-by-line scanning, and perform conditional filtering and proportion calculations.

key_values = {}
target_col = None
value_col = 'target_value_col'

# 动态查找目标分类列
for col in df_analysis.columns:
    if 'keyword1' in col.lower() or 'keyword2' in col.lower():
        target_col = col
        break

Generic field lookup logic: scan line by line to match keywords and extract the first positive number

for idx, row in df_analysis.iterrows(): row_str = str(row.values) if '指标A' in row_str and '指标A' not in key_values: for val in row.values: if isinstance(val, (int, float)) and val > 0: key_values['指标A'] = val break if '指标B' in row_str and '指标B' not in key_values: for val in row.values: if isinstance(val, (int, float)) and val > 0: key_values['指标B'] = val break

Conditional filtering and statistics

if target_col and '特定类别' in df_analysis[target_col].unique(): df_filtered = df_analysis[df_analysis[target_col] == '特定类别'] if value_col in df_filtered.columns: df_filtered[value_col] = pd.to_numeric(df_filtered[value_col], errors='coerce') avg_val = df_filtered[value_col].mean() print(f"特定类别平均值 = {avg_val:.2f}")

Calculate proportion

if '指标A' in key_values and '指标B' in key_values: percentage = (key_values['指标A'] / key_values['指标B']) * 100 print(f"指标A占指标B的百分比: {percentage:.2f}%")


Step2 将计算结果保存为结构化表格文件（.xlsx），并在输出中提供可追溯的下载链接。
```python
output_path = "output_analysis_result.xlsx"
os.makedirs(os.path.dirname(output_path), exist_ok=True)

result_data = {
    '项目': ['指标A', '指标B', '占比'],
    '数值': [key_values.get('指标A', 0), key_values.get('指标B', 0), f"{percentage:.2f}%" if 'percentage' in locals() else "N/A"]
}
df_result = pd.DataFrame(result_data)

with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    df_result.to_excel(writer, sheet_name='汇总结果', index=False)

print(f"结果已保存到: {output_path}")
print(f"下载链接: [点击下载结果表格]({output_path})")

Step 3: Configure Chinese fonts and generate high-resolution visualization charts (such as pie charts) to display the proportion analysis results.

import matplotlib.pyplot as plt
import matplotlib

# 配置中英文字体，防止图表中文乱码
matplotlib.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans', 'WenQuanYi Zen Hei']
matplotlib.rcParams['axes.unicode_minus'] = False

if 'percentage' in locals():
    # 图表美化与高分辨率设置
    plt.figure(figsize=(8, 6), dpi=120)
    labels = ['指标A', '其他']
    sizes = [percentage, 100 - percentage]
    colors = ['#ff9999', '#66b3ff']
    
    plt.pie(sizes, labels=labels, colors=colors, autopct='%1.1f%%', startangle=90)
    plt.title('核心指标占比分析')
    plt.axis('equal')
    
    chart_path = "percentage_chart.png"
    plt.savefig(chart_path, bbox_inches='tight')
    print(f"图表已保存至: {chart_path}")
    print(f"图表下载链接: [点击下载可视化图表]({chart_path})")

About this skill

Skill Steps