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.

Step1 在数据中动态定位关键字段，通过逐行扫描匹配关键词提取数值，并进行条件筛选与占比计算。

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

# 通用字段查找逻辑：逐行扫描匹配关键词并提取首个正数
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

# 条件筛选与统计
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}")

# 计算占比
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），并在输出中提供可追溯的下载链接。

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)