single-sheet-reading-and-analysis

name: single-sheet-reading-and-analysis description: "读取并解析单个Excel工作表数据，支持合并单元格处理、数据清洗、交叉分析及多维度可视化，适用于需要从单表中提取关键指标并进行趋势模拟与图表生成的场景。"

Skill Steps

Step1 导入依赖并配置中英文字体，防止图表乱码

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import re
import base64
from IPython.display import HTML

# 设置中英文字体
plt.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans', 'WenQuanYi Zen Hei']
plt.rcParams['axes.unicode_minus'] = False

Step2 加载数据与基础清洗，包含合并单元格处理与正则提取

def load_and_clean_data(file_path, sheet_name=0):
    # 读取数据
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    
    # 处理合并单元格：向前填充并还原
    # df['group_col'] = df['group_col'].ffill()
    
    # 标准化列名：去除首尾空格及换行符
    df.columns = [str(col).strip().replace('\n', '') for col in df.columns]
    
    # 数据清洗正则表达式示例：提取数值
    if 'target_col' in df.columns:
        df['target_col'] = df['target_col'].astype(str).apply(lambda x: re.sub(r'[^\d.]', '', x))
        df['target_col'] = pd.to_numeric(df['target_col'], errors='coerce')
    
    # 处理全空行缺失值
    df = df.dropna(how='all')
    return df