Skill Steps
Step1 加载数据与环境配置
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import re
# 配置中文字体，确保图表正常显示
plt.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans', 'WenQuanYi Zen Hei']
plt.rcParams['axes.unicode_minus'] = False
def load_excel_data(file_path: str, skip_rows: int = 2):
"""读取并加载Excel文件中的数据，跳过标题行以获取原始数据"""
# 技巧：处理合并单元格可使用 df.ffill() 等方法
df = pd.read_excel(file_path, skiprows=skip_rows)
return df
Step2 定义分类映射函数骨架
def categorize_data(item: str) -> str:
"""将具体项归类到大类中（分类映射函数骨架）"""
if pd.isna(item):
return '未知'
if item in ['类别A1', '类别A2', '类别A3']:
return '大类A'
elif item in ['类别B1', '类别B2']:
return '大类B'
else:
return '其他'
Step3 统一分析与可视化流程（柱状图、饼图、交叉分析）
def analyze_and_visualize(df: pd.DataFrame, category_col: str, group_col: str = None, output_path: str = './', top_n: int = None, custom_categorize=None):
"""统一分析与可视化流程：生成柱状图、饼图、交叉分析堆叠柱状图"""
df_clean = df.copy()
# 应用自定义分类规则
if custom_categorize:
df_clean[f'{category_col}大类'] = df_clean[category_col].apply(custom_categorize)
analyze_col = f'{category_col}大类'
else:
analyze_col = category_col
# value_counts + 占比统计
counts = df_clean[analyze_col].value_counts()
if top_n:
counts = counts.head(top_n)
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 6))
# 柱状图美化
counts.plot(kind='bar', ax=ax1, color='skyblue', edgecolor='black')
ax1.set_title(f'{analyze_col}分布（柱状图）', fontsize=14, fontweight='bold')
ax1.set_xlabel(analyze_col, fontsize=12)
ax1.set_ylabel('数量', fontsize=12)
ax1.tick_params(axis='x', rotation=45)
ax1.grid(axis='y', alpha=0.3)
for i, v in enumerate(counts.values):
ax1.text(i, v + 0.05, str(v), ha='center', va='bottom', fontweight='bold')
# 饼图美化
colors = plt.cm.Set3(np.linspace(0, 1, len(counts)))
wedges, texts, autotexts = ax2.pie(counts.values, labels=counts.index, autopct='%1.1f%%', colors=colors, startangle=90)
ax2.set_title(f'{analyze_col}分布（饼图）', fontsize=14, fontweight='bold')
for text in texts:
text.set_fontsize(10)
for autotext in autotexts:
autotext.set_fontsize(9)
autotext.set_fontweight('bold')
plt.tight_layout()
plt.savefig(f'{output_path}{analyze_col}_分布图.png', dpi=300, bbox_inches='tight')
plt.close()
# 交叉分析 (crosstab)
if group_col and group_col in df_clean.columns:
cross_table = pd.crosstab(df_clean[group_col], df_clean[analyze_col])
if top_n:
cross_table = cross_table.head(top_n)
plt.figure(figsize=(10, 6))
cross_table.plot(kind='bar', stacked=True, colormap='viridis')
plt.title(f'各{group_col}的{analyze_col}分布', fontsize=14, fontweight='bold')
plt.xlabel(group_col, fontsize=12)
plt.ylabel('数量', fontsize=12)
plt.xticks(rotation=45)
plt.legend(title=analyze_col, bbox_to_anchor=(1.05, 1), loc='upper left')
plt.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.savefig(f'{output_path}交叉分析图.png', dpi=300, bbox_inches='tight')
plt.close()
Step4 多维度评分与分级算法结构
def analyze_content_difficulty(content: str) -> tuple:
"""多维度评分/分级算法结构：基于长度、术语、正则匹配等计算综合评分"""
if not isinstance(content, str):
return 0, '低'
length = len(content)
# 关键词匹配
technical_terms = ['专业术语A', '专业术语B', '核心概念C']
tech_count = sum(1 for term in technical_terms if term in content)
# 数据清洗与正则匹配（如提取数值要求）
has_numeric = bool(re.search(r'\d+', content))
complex_concepts = ['复杂流程X', '高阶操作Y']
complex_count = sum(1 for concept in complex_concepts if concept in content)
# 综合评分计算公式
score = (length / 100) * 30 + (tech_count / 10) * 20 + (1 if has_numeric else 0) * 15 + (complex_count / 5) * 35
# 难度/质量分级标准
if score >= 70:
level = '高'
elif score >= 40:
level = '中'
else:
level = '低'
return score, level
Step5 生成综合评分分析图表
def generate_comprehensive_analysis(df: pd.DataFrame, content_col: str, output_path: str = './'):
"""为目标内容生成综合评分分析图表（横向条形图、趋势图）"""
# 过滤空值并重置索引
target_data = df.dropna(subset=[content_col]).reset_index(drop=True)
scores, levels = zip(*target_data[content_col].apply(analyze_content_difficulty))
target_data['综合评分'] = scores
target_data['评级'] = levels
# 评级分布（横向条形图）
level_counts = target_data['评级'].value_counts()
plt.figure(figsize=(10, 6))
bars = plt.barh(level_counts.index, level_counts.values, color='skyblue', edgecolor='black')
plt.title('各评级数量分布（横向条形图）', fontsize=14, fontweight='bold')
plt.xlabel('数量', fontsize=12)
plt.ylabel('评级', fontsize=12)
for bar, count in zip(bars, level_counts.values):
plt.text(bar.get_width() + 0.1, bar.get_y() + bar.get_height()/2, str(count), va='center', fontsize=10)
plt.grid(axis='x', alpha=0.3)
plt.tight_layout()
plt.savefig(f'{output_path}评级分布_横向条形图.png', dpi=300, bbox_inches='tight')
plt.close()
# 长度与评分趋势图（散点图）
plt.figure(figsize=(10, 6))
plt.scatter(target_data[content_col].str.len(), scores, alpha=0.6, color='green')
plt.title('内容长度与综合评分趋势图', fontsize=14, fontweight='bold')
plt.xlabel('内容长度（字符数）', fontsize=12)
plt.ylabel('综合评分', fontsize=12)
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig(f'{output_path}长度与评分趋势图.png', dpi=300, bbox_inches='tight')
plt.close()
return target_data
Step6 执行完整分析流程
if __name__ == '__main__':
file_path = 'input_data.xlsx'
output_path = './output/'
# 1. 加载数据
df = load_excel_data(file_path, skip_rows=2)
# 2. 分类统计与交叉分析
analyze_and_visualize(
df,
category_col='目标列A',
group_col='分组列B',
output_path=output_path,
custom_categorize=categorize_data
)
# 3. 文本内容多维度评分与可视化
content_col = '文本内容列'
if content_col in df.columns:
processed_df = generate_comprehensive_analysis(df, content_col=content_col, output_path=output_path)
