grouped-statistics

Skill Steps

Note: This sub-skill covers one step of the Excel analysis workflow. For the full pipeline (file reading, row counting, large-file optimization, export), see the parent workflow SKILL.md.

Step1 提取关键维度与指标信息，处理合并单元格缺失值，并进行多表交叉分析与排序。

import pandas as pd

# 设定目标列名
group_col = '行业名称'
target_val_1 = '企业单位数'
target_val_2 = '工业总产值'

# 读取第一个 Sheet 并清洗
df1 = pd.read_excel(file_path, sheet_name=sheet_names[0], header=None)
# 假设数据从第 21 行开始，提取维度列与数值列
data_1 = df1.iloc[21:63, [0, 2]].copy()
data_1.columns = [group_col, target_val_1]

# 处理合并单元格：前向填充维度列
data_1[group_col] = data_1[group_col].ffill()
data_1[target_val_1] = pd.to_numeric(data_1[target_val_1], errors='coerce')

# 读取第二个 Sheet 并提取补充指标
df2 = pd.read_excel(file_path, sheet_name=sheet_names[1], header=None)
data_2 = df2.iloc[5:47, [0, 1]].copy()
data_2.columns = ['temp_dim', target_val_2]
data_2[target_val_2] = pd.to_numeric(data_2[target_val_2], errors='coerce')

# 交叉分析：基于索引或维度列合并
merged_df = pd.merge(data_1, data_2.reset_index(), left_index=True, right_index=True, how='inner')
merged_df = merged_df[[group_col, target_val_1, target_val_2]].dropna(subset=[target_val_1])

# 筛选 Top N 结果