【计算机设计大赛近年获奖信息】数据分析及可视化

import pandas as pd
df_2018 = pd.read_excel('2018年决赛正式结果.xlsx', sheet_name='123等奖')
df_2019 = pd.read_excel('2019年大赛获奖作品名单公示20190907.xlsx')
复制代码

df_2019.info()
复制代码

import pdfplumber
def read_pdf_2020(read_path):
    pdf_2020 = pdfplumber.open(read_path)
    result_df = pd.DataFrame()
    for page in pdf_2020.pages:
        table = page.extract_table()
        df_detail = pd.DataFrame(table[1:], columns=table[0])
        # 合并每页的数据集
        result_df = pd.concat([df_detail, result_df], ignore_index=True)
    # 删除值全部是 NaN 的列
    result_df.dropna(axis=1, how='all', inplace=True)
    # 重置列名
    result_df.columns = ['奖项', '作品编号', '作品名称', '参赛学校', '作者', '指导老师']
    return result_df
df_2020 = read_pdf_2020('2020年中国大学生计算机设计大赛参赛作品获奖名单.pdf')
复制代码

# 2020年数据集处理
clean_df_2020 = df_2020.copy()
# 部分信息过长导致在分页处被分割，分别出现在两页上，下面将奖项为空的数据添加到上一条数据的信息中。
clean_df_2020.iloc[609]['参赛学校'] += '医大学'
clean_df_2020.iloc[1330]['作品名称'] += '丹霞'
clean_df_2020.iloc[2121]['作品名称'] += '现'
clean_df_2020.iloc[2997]['作品名称'] += '云平台'
del_index = clean_df_2020.loc[clean_df_2020['奖项'] == ''].index
clean_df_2020.drop(del_index, inplace=True)
clean_df_2020.reset_index(drop=True, inplace=True)
clean_df_2020['年份'] = [2020 for _ in range(len(clean_df_2020))]
复制代码

college_info = pd.read_csv('college_info.csv')
college_name = college_info['学校名称'].values.tolist()
college_level = []
for college in all_df['参赛学校']:
    if college not in college_name:
        college_level.append('暂无数据')
    else:
        college_level.append(college_info['学校层次'][college_name.index(college)])
all_df['学校层次'] = college_level
all_df['参赛学校'] = all_df['参赛学校'].str.replace('\n|\r', '')
all_df['作品名称'] = all_df['作品名称'].str.replace('\n|\r', '')
# 删除作者为空的列
all_df.dropna(subset=['作者'], axis=0, inplace=True)
# 添加 参数人数 列来记录各作品作者人数
all_df['参赛人数'] = all_df['作者'].apply(lambda x: len(x.split('、')))
count_list = []
for index, row in all_df.iterrows():
    try:
        count_list.append(len(row['指导老师'].split('、')))
    except:
        count_list.append(0)
all_df['指导老师人数'] = count_list
all_df.to_csv('all_df.csv', index=False)
all_df
复制代码

'''
数据统计省略
'''
from pyecharts import options as opts
from pyecharts.charts import Bar
from pyecharts.commons.utils import JsCode
from pyecharts.globals import ThemeType
c = Bar(init_opts=opts.InitOpts(theme=ThemeType.CHALK))
c.add_xaxis([2018, 2019, 2020])
c.add_yaxis("三等奖", list1, stack="stack1", category_gap="70%")
c.add_yaxis("二等奖", list2, stack="stack1", category_gap="70%")
c.add_yaxis("一等奖", list3, stack="stack1", category_gap="70%")
c.set_series_opts(label_opts=opts.LabelOpts(
                    position="right",
                    formatter=JsCode(
                        "function(x){return Number(x.data.percent * 100).toFixed() + '%';}"
                    ),
                )
            )
c.render("./images/各年奖项数量分布堆叠条形图.html")
c.render_notebook()
复制代码

from collections import Counter
all_school = []
for year in [2018, 2019, 2020]:
    school_set = set(all_df.loc[all_df['年份'] == year, '参赛学校'].values.tolist())
    all_school += list(school_set)
value_count = Counter(all_school)
count_list = ['参赛' + str(n) + '次' for n in value_count.values()]
counter = Counter(count_list)
from pyecharts.charts import Pie
c = Pie(init_opts=opts.InitOpts(theme=ThemeType.CHALK))
c.add("", [list(z) for z in zip(counter.keys(), counter.values())])
c.set_global_opts(title_opts=opts.TitleOpts(title="Pie-基本示例"))
c.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
c.render("./images/各学校参加次数统计饼图.html")
c.render_notebook()
复制代码

def load_stopwords(read_path):
    '''
    读取文件每行内容并保存到列表中
    :param read_path: 待读取文件的路径
    :return: 保存文件每行信息的列表
    '''
    result = []
    with open(read_path, "r", encoding='utf-8') as f:
        for line in f.readlines():
            line = line.strip('\n')  # 去掉列表中每一个元素的换行符
            result.append(line)
    return result
# 加载中文停用词
stopwords = load_stopwords('wordcloud_stopwords.txt')
复制代码

import jieba
# 添加自定义词典
jieba.load_userdict("自定义词典.txt")
token_list = []
# 对标题内容进行分词，并将分词结果保存在列表中
for name in all_df['作品名称']:
    tokens = jieba.lcut(name, cut_all=False)
    token_list += [token for token in tokens if token not in stopwords]
len(token_list)
复制代码

from pyecharts.charts import WordCloud
from collections import Counter
token_count_list = Counter(token_list).most_common(100)
new_token_list = []
for token, count in token_count_list:
    new_token_list.append((token, str(count)))
c = WordCloud()   
c.add(series_name="热词", data_pair=new_token_list, word_size_range=[20, 200])
c.set_global_opts(
    title_opts=opts.TitleOpts(
        title="获奖作品热词", title_textstyle_opts=opts.TextStyleOpts(font_size=23)
    ),
    tooltip_opts=opts.TooltipOpts(is_show=True),
)
c.render("./images/获奖作品热词.html")
c.render_notebook()
复制代码