excel-analyzer

Excel Analysis

Help users analyze and process Excel/CSV files.

Use Cases

The user says "Help me analyze this spreadsheet" or "How many rows of data are in this Excel file"
The user needs to filter, summarize, or pivot the spreadsheet
The user wants to generate charts or reports from Excel

Dependency Installation

Automatically installed on first use:

pip install pandas openpyxl

Execution Method

Use Python scripts with pandas to process Excel/CSV files.

Read File

import pandas as pd

# 读取 Excel
df = pd.read_excel("/path/to/file.xlsx", sheet_name=0)

# 读取 CSV
df = pd.read_csv("/path/to/file.csv")

# 查看基本信息
print(f"行数: {len(df)}, 列数: {len(df.columns)}")
print(f"列名: {list(df.columns)}")
print(df.head())

Data Summary

# 基本统计
print(df.describe())

# 按列汇总
print(df.groupby("类别").agg({"金额": ["sum", "mean", "count"]}))

Data Filtering

# 条件筛选
filtered = df[df["金额"] > 1000]

# 多条件
filtered = df[(df["部门"] == "销售") & (df["金额"] > 500)]

Export Results

# 导出到新 Excel
result.to_excel("/path/to/output.xlsx", index=False)

# 导出到 CSV
result.to_csv("/path/to/output.csv", index=False, encoding="utf-8-sig")

Data Validation (Must Be Performed)

Data validation must be performed both before and after analysis to ensure the results are trustworthy:

Post-Cleaning Validation

# 1. 行数校验：打印清洗前后行数，确认只去了空行/噪音行
print(f"清洗前: {len(df_raw)} 行 → 清洗后: {len(df_clean)} 行 (去除 {len(df_raw)-len(df_clean)} 行)")

# 2. 分类列去重：检查分类列（如地区、产品）是否有近似重复值
for col in categorical_columns:
    unique_vals = df_clean[col].unique()
    print(f"列 '{col}' 唯一值: {unique_vals}")
    # 检查近似重复（如 "华东" vs "华东地区"）
    # 如有近似重复，合并为统一值

# 3. 聚合一致性校验：各分组 sum 必须等于总 sum
total = df_clean["金额"].sum()
group_total = df_clean.groupby("地区")["金额"].sum().sum()
assert abs(total - group_total) < 0.01, f"聚合不一致: 总额 {total} ≠ 分组合计 {group_total}"

Report Validation

The numbers cited in the report must be consistent with the cleaned data
If there are rankings/percentages, the sum of the percentages should be approximately 100%
Clearly inform the user what cleaning was done (how many date formats were unified, how many blank rows were removed, etc.)

Output Specifications

First show a data overview (number of rows, number of columns, column names)
Clearly inform the user of the cleaning steps: what fixes were made, how many rows were removed, what formats were unified
Display analysis results in table format
For large datasets, only show the first 10 rows + summary statistics
When exporting files, inform the user of the save path