name: grouped-statistics description: "对多 Sheet 的 Excel 文件进行行数统计、数据合并与前向填充。"
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 结果
top5_df = merged_df.nlargest(5, target_val_1).reset_index(drop=True)
top5_df.index = top5_df.index + 1
print(top5_df)