Line Chart Visualization Agent Skill

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
import os
import re

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

file_path = 'input_data.xlsx'

# 处理大型Excel文件：统计总行数，若≥1万则转换为Parquet格式提升效率
xls = pd.ExcelFile(file_path)
total_rows = sum(pd.read_excel(xls, sheet_name=s, header=None).shape[0] for s in xls.sheet_names)

if total_rows >= 10000:
    parquet_path = "temp_converted_file.parquet"
    with pd.ExcelWriter(parquet_path, engine='pyarrow') as writer:
        for sheet in xls.sheet_names:
            df_sheet = pd.read_excel(xls, sheet_name=sheet, header=None)
            df_sheet.to_excel(writer, sheet_name=sheet, index=False, header=False)
    df = pd.read_excel(parquet_path, sheet_name='Sheet1', header=None)
else:
    df = pd.read_excel(file_path, sheet_name='Sheet1', header=None)

# 动态识别表头并提取数据
header_row_idx = None
target_cols = ['group_col', 'value_col1', 'value_col2'] # 占位示例列名
for idx, row in df.iterrows():
    row_vals = row.astype(str).tolist()
    if all(col in row_vals for col in target_cols):
        header_row_idx = idx
        break

if header_row_idx is not None:
    df.columns = df.iloc[header_row_idx].tolist()
    df_clean = df.iloc[header_row_idx + 1:].reset_index(drop=True)
else:
    df_clean = df.copy()

# 合并单元格处理 (ffill + 遍历还原)
if 'group_col' in df_clean.columns:
    df_clean['group_col'] = df_clean['group_col'].ffill()

# 数据清洗正则表达式：提取数值
if 'value_col1' in df_clean.columns:
    df_clean['value_col1'] = df_clean['value_col1'].astype(str).str.replace(r'[^\d.]', '', regex=True)
    df_clean['value_col1'] = pd.to_numeric(df_clean['value_col1'], errors='coerce')

df_clean = df_clean.dropna(subset=['value_col1']).reset_index(drop=True)

# 分类映射函数骨架
def map_category(val):
    if pd.isna(val): return 'Unknown'
    if val > 100: return 'High' # 占位示例
    elif val > 50: return 'Medium'
    return 'Low'

if 'value_col1' in df_clean.columns:
    df_clean['level'] = df_clean['value_col1'].apply(map_category)

# 多维度评分/分级算法结构
def calculate_score(row):
    score = 0
    if pd.notna(row.get('value_col1')) and float(row['value_col1']) > 50: # 占位示例
        score += 50
    if pd.notna(row.get('value_col2')) and float(row['value_col2']) < 10: # 占位示例
        score += 50
    return score

df_clean['comprehensive_score'] = df_clean.apply(calculate_score, axis=1)

numeric_cols = ['value_col1', 'comprehensive_score']
existing_num_cols = [c for c in numeric_cols if c in df_clean.columns]

if existing_num_cols:
    # 数值特征标准化
    scaler = StandardScaler()
    numeric_scaled = scaler.fit_transform(df_clean[existing_num_cols].fillna(0))
    
    # 聚类分析识别潜在数据群组结构
    kmeans = KMeans(n_clusters=3, random_state=42)
    df_clean['cluster_label'] = kmeans.fit_predict(numeric_scaled)

# value_counts + 占比计算
if 'level' in df_clean.columns:
    level_counts = df_clean['level'].value_counts()
    level_ratio = df_clean['level'].value_counts(normalize=True) * 100
    summary_df = pd.DataFrame({'频次': level_counts, '占比(%)': level_ratio.round(2)})
    summary_df.loc['总计'] = summary_df.sum()
    print("分类统计汇总:\n", summary_df)

# 交叉分析 crosstab/pivot
if 'cluster_label' in df_clean.columns and 'level' in df_clean.columns:
    cross_tb = pd.crosstab(df_clean['cluster_label'], df_clean['level'], margins=True, margins_name='总计')
    print("\n聚类与等级交叉分析:\n", cross_tb)

# 创建多维度综合可视化图表
fig, axes = plt.subplots(2, 2, figsize=(16, 12), dpi=150)
fig.suptitle('综合数据分析图表', fontsize=16)

group_col = 'group_col' if 'group_col' in df_clean.columns else df_clean.columns[0]

# 1. 趋势对比折线图
if 'value_col1' in df_clean.columns:
    axes[0, 0].plot(df_clean[group_col].astype(str).str[:10], df_clean['value_col1'], marker='o', label='指标1', color='#1f77b4')
    if 'comprehensive_score' in df_clean.columns:
        axes[0, 0].plot(df_clean[group_col].astype(str).str[:10], df_clean['comprehensive_score'], marker='s', label='综合评分', color='#ff7f0e')
    axes[0, 0].set_title('多指标趋势对比')
    axes[0, 0].set_xlabel('分组维度')
    axes[0, 0].set_ylabel('数值')
    axes[0, 0].legend(loc='upper right')
    axes[0, 0].grid(True, alpha=0.3)
    axes[0, 0].tick_params(axis='x', rotation=45)

# 2. 分布特征直方图
if 'value_col1' in df_clean.columns:
    axes[0, 1].hist(df_clean['value_col1'].dropna(), bins=15, alpha=0.7, color='skyblue', edgecolor='black')
    axes[0, 1].set_title('数值分布特征')
    axes[0, 1].set_xlabel('数值区间')
    axes[0, 1].set_ylabel('频次')
    axes[0, 1].grid(True, alpha=0.3)

# 3. 市场份额/占比饼图
if 'level' in df_clean.columns:
    level_counts = df_clean['level'].value_counts()
    colors_pie = plt.cm.Set3(np.linspace(0, 1, len(level_counts)))
    axes[1, 0].pie(level_counts, labels=level_counts.index, autopct='%1.1f%%', colors=colors_pie, startangle=90)
    axes[1, 0].set_title('分类占比分布')

# 4. 参数敏感性分析/聚类结果散点图
if 'cluster_label' in df_clean.columns and 'value_col1' in df_clean.columns:
    sns.scatterplot(data=df_clean, x=group_col, y='value_col1', hue='cluster_label', ax=axes[1, 1], palette='Set1', s=80)
    axes[1, 1].set_title('聚类分组散点图')
    axes[1, 1].tick_params(axis='x', rotation=45)
    axes[1, 1].grid(True, alpha=0.3)

plt.tight_layout(rect=[0, 0.03, 1, 0.95])

# 保存图表与清洗后的数据
chart_path = "output_chart.png"
output_path = "output_table.xlsx"

plt.savefig(chart_path, dpi=300, bbox_inches='tight')
plt.close()

df_clean.to_excel(output_path, index=False)

# 生成下载链接
print(f"分析完成。")
print(f"图表下载链接: file:///{os.path.abspath(chart_path)}")
print(f"数据下载链接: file:///{os.path.abspath(output_path)}")