Excel Data Analysis

Overview

A comprehensive skill for analyzing Excel/tabular data using Python. Covers the full data analysis pipeline: loading, cleaning, exploring, analyzing, visualizing, and reporting.

Instructions

Environment Setup

Always begin by ensuring required packages are available:

import subprocess, sys

def ensure_packages():
    pkgs = ["pandas", "openpyxl", "matplotlib", "seaborn", "scipy", "xlsxwriter"]
    for pkg in pkgs:
        try:
            __import__(pkg)
        except ImportError:
            subprocess.check_call([sys.executable, "-m", "pip", "install", pkg])

ensure_packages()

Workflow

Follow this pipeline for every analysis task:

Task Progress:
- [ ] Step 1: Load and inspect data
- [ ] Step 2: Clean and preprocess
- [ ] Step 3: Exploratory analysis
- [ ] Step 4: Deep analysis (statistical / modeling)
- [ ] Step 5: Visualization
- [ ] Step 6: Generate report / export results

Step 1: Load and Inspect Data

import pandas as pd

# Support multiple formats
df = pd.read_excel("data.xlsx", sheet_name=None)  # All sheets -> dict
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")  # Single sheet
df = pd.read_csv("data.csv", encoding="utf-8-sig")  # CSV with BOM

# First look
print(f"Shape: {df.shape}")
print(f"Columns: {list(df.columns)}")
print(f"Dtypes:\n{df.dtypes}")
print(df.head(10))
print(df.describe(include="all"))

Key checks:

• Row/column count
• Column data types (detect mismatches like numeric stored as text)
• Sheet names if multi-sheet workbook
• Duplicate rows: df.duplicated().sum()

Step 2: Clean and Preprocess

# Missing values overview
missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
missing_info = pd.DataFrame({"count": missing, "percent": missing_pct})
print(missing_info[missing_info["count"] > 0])

# Common cleaning operations
df = df.drop_duplicates()  # Remove duplicates
df = df.dropna(subset=["key_column"])  # Drop rows where key is null
df["numeric_col"] = pd.to_numeric(df["numeric_col"], errors="coerce")
df["date_col"] = pd.to_datetime(df["date_col"], errors="coerce")
df["text_col"] = df["text_col"].str.strip().str.lower()

# Fill missing values (choose strategy based on context)
df["col"] = df["col"].fillna(df["col"].median())  # Median fill
df["col"] = df["col"].fillna(method="ffill")  # Forward fill
df["col"] = df["col"].interpolate()  # Interpolation

# Outlier detection (IQR method)
Q1, Q3 = df["col"].quantile([0.25, 0.75])
IQR = Q3 - Q1
mask = (df["col"] >= Q1 - 1.5 * IQR) & (df["col"] <= Q3 + 1.5 * IQR)
df_clean = df[mask]

Always report cleaning actions taken — how many rows dropped, filled, or modified.

Step 3: Exploratory Analysis

# Descriptive statistics
print(df.describe())
print(df.select_dtypes(include="number").corr())  # Correlation matrix

# Group analysis
grouped = df.groupby("category").agg({
    "value": ["mean", "median", "std", "min", "max", "count"]
})
print(grouped)

# Distribution check
from scipy import stats
for col in df.select_dtypes(include="number").columns:
    skew = df[col].skew()
    kurt = df[col].kurtosis()
    print(f"{col}: skew={skew:.2f}, kurtosis={kurt:.2f}")

# Pivot table
pivot = pd.pivot_table(df, values="amount", index="category",
                       columns="month", aggfunc="sum", fill_value=0)

Step 4: Deep Analysis

Select methods based on the user's question:

Comparing groups:

from scipy.stats import ttest_ind, mannwhitneyu

group_a = df[df["group"] == "A"]["value"]
group_b = df[df["group"] == "B"]["value"]

# Normal distribution -> t-test; otherwise -> Mann-Whitney U
stat, p = ttest_ind(group_a, group_b)
print(f"t-test: statistic={stat:.4f}, p-value={p:.4f}")

Correlation & regression:

import numpy as np
from scipy.stats import pearsonr, spearmanr

r, p = pearsonr(df["x"], df["y"])
print(f"Pearson r={r:.4f}, p={p:.4f}")

# Simple linear regression
slope, intercept, r_val, p_val, std_err = stats.linregress(df["x"], df["y"])
print(f"y = {slope:.4f}x + {intercept:.4f}, R²={r_val**2:.4f}")

Time series / trend:

df["date"] = pd.to_datetime(df["date"])
df = df.sort_values("date")
df = df.set_index("date")
df["rolling_mean"] = df["value"].rolling(window=7).mean()
df["pct_change"] = df["value"].pct_change()

Step 5: Visualization

Use matplotlib + seaborn. Always set Chinese font support when labels contain Chinese:

import matplotlib.pyplot as plt
import seaborn as sns

# Chinese font support (IMPORTANT for Chinese data)
plt.rcParams["font.sans-serif"] = ["SimHei", "Microsoft YaHei", "Arial Unicode MS"]
plt.rcParams["axes.unicode_minus"] = False

sns.set_style("whitegrid")
fig, axes = plt.subplots(2, 2, figsize=(14, 10))
fig.suptitle("数据分析概览", fontsize=16)

Common chart types:

# Bar chart
sns.barplot(data=df, x="category", y="value", ax=axes[0, 0])
axes[0, 0].set_title("分类对比")

# Line chart (time series)
axes[0, 1].plot(df["date"], df["value"], marker="o", markersize=3)
axes[0, 1].set_title("趋势分析")

# Histogram + KDE
sns.histplot(df["value"], kde=True, ax=axes[1, 0])
axes[1, 0].set_title("分布分析")

# Heatmap (correlation)
sns.heatmap(df.select_dtypes(include="number").corr(),
            annot=True, cmap="RdBu_r", center=0, ax=axes[1, 1])
axes[1, 1].set_title("相关性热力图")

plt.tight_layout()
plt.savefig("analysis_output.png", dpi=150, bbox_inches="tight")
plt.show()

Chart rules:

• Always add title, axis labels, and units
• Use dpi=150 for clear output
• Save charts as PNG before displaying
• Use colorblind-friendly palettes: sns.set_palette("colorblind")
• For pie charts, limit categories to ≤8; group the rest as "Other"

Step 6: Export Results

# Export to Excel with multiple sheets
with pd.ExcelWriter("analysis_report.xlsx", engine="xlsxwriter") as writer:
    df_clean.to_excel(writer, sheet_name="清洗后数据", index=False)
    summary_stats.to_excel(writer, sheet_name="统计摘要")
    pivot.to_excel(writer, sheet_name="透视表")

    # Format headers
    workbook = writer.book
    header_fmt = workbook.add_format({"bold": True, "bg_color": "#4472C4",
                                      "font_color": "white"})
    for sheet_name in writer.sheets:
        worksheet = writer.sheets[sheet_name]
        for col_num, col_name in enumerate(df_clean.columns):
            worksheet.write(0, col_num, col_name, header_fmt)
            worksheet.set_column(col_num, col_num, 15)

print("Report saved to analysis_report.xlsx")

Output Format

When generating a text/markdown report, use this structure:

# [分析主题]

## 数据概况
- 数据量: X 行 × Y 列
- 时间范围: [start] ~ [end]
- 数据质量: 缺失率 X%, 重复行 X 行

## 关键发现
1. [发现1]: [具体数据支撑]
2. [发现2]: [具体数据支撑]
3. [发现3]: [具体数据支撑]

## 详细分析
[按分析维度展开，配合图表]

## 建议与结论
1. [可操作的建议]
2. [可操作的建议]

Error Handling

• Encoding errors: If utf-8 fails, try encoding="gbk" or encoding="gb2312" for Chinese Excel files.
• Large files: For files > 100MB, use chunksize parameter in pd.read_csv() or read specific columns with usecols.
• Missing packages: The ensure_packages() function auto-installs missing dependencies. If pip fails, prompt the user to install manually.
• Corrupted files: If openpyxl raises BadZipFile, the file may be .xls (legacy format). Use xlrd engine: pd.read_excel("file.xls", engine="xlrd").
• Type conversion failures: Use errors="coerce" to convert unparseable values to NaN instead of raising errors.

Important Guidelines

1. Always show sample data before and after cleaning so the user can verify.
2. Explain every step — don't just run code, describe what you're doing and why.
3. Never modify the original file — always save results to a new file.
4. Present numbers in context — don't just say "mean is 42.5", say "average sales per month is ¥42,500".

Examples

• For complete chart examples, see examples.md
• For pandas/openpyxl API patterns, see reference.md