2018研究生数学建模成绩分析

    #coding:utf-8
    import numpy as np
    import pandas as pd
    import matplotlib.pyplot as plt
    import seaborn as sns
    from geopy.geocoders.baidu import Baidu
    import os
    import time
    sns.set(style="whitegrid")
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 设置字体为黑体
    plt.rcParams['axes.unicode_minus'] = False  # 解决中文字体负号显示不正常问题
    
    path='../data/'
    save_dir='../tmp/'
    data=pd.read_excel(path+'2018建模成绩汇总.xlsx')
    data['奖项']=data['奖项'].apply(lambda x:'一等奖'if x=='一等奖（华为）' else x)

    # 按'队长所在单位'分别统计获奖数量
    def school_cnt(df):
    school_cnt_df=df[df['奖项']!='成功参与奖'][['奖项','队长所在单位']].groupby(['队长所在单位','奖项']).size().unstack()
    school_cnt_df['获奖数量']=school_cnt_df[['一等奖','二等奖','三等奖']].sum(axis=1)
    school_cnt_df.sort_values(by=['获奖数量','一等奖','二等奖','三等奖'],axis=0,ascending = False,inplace=True)
    school_cnt_df=school_cnt_df[['一等奖','二等奖','三等奖','获奖数量']]
    school_cnt_df=school_cnt_df.reset_index().rename(columns={'队长所在单位':'学校名称'})
    print(school_cnt_df.head(5))
    # school_cnt_df.to_excel(path+'school_cnt.xlsx',encoding='gbk')
    # 用柱状图给出获奖数量最多的前20个学校
    plt.figure(figsize=(15, 10)).subplotpars.update(bottom=0.25)
    sns.barplot(x="学校名称", y="获奖数量", data=school_cnt_df.loc[0:20],
                palette="muted")
    plt.xticks(ha='right', rotation=40)
    plt.savefig(path+'school_cnt.png')
    plt.show()

    # 统计每个学校的获奖人数
    # 思路1：使用双层字典嵌套逐个统计
    # 思路2：groupby
    def team_member_cnt(df):
    team_leader_cnt_df=df[['奖项','队长所在单位']].groupby(['队长所在单位','奖项']).size().unstack()
    team_member_df=team_leader_cnt_df[['一等奖','二等奖','三等奖']]
    team_member_cnt_df1=df[['奖项','第一队友所在单位']].groupby(['第一队友所在单位','奖项']).size().unstack()
    team_member_df=team_member_df.join(team_member_cnt_df1[['一等奖','二等奖','三等奖']],how='outer',rsuffix='_2')
    team_member_cnt_df2=df[['奖项','第二队友所在单位']].groupby(['第二队友所在单位','奖项']).size().unstack()
    team_member_df=team_member_df.join(team_member_cnt_df2[['一等奖','二等奖','三等奖']],how='outer',rsuffix='_3')
    
    team_member_df.rename(columns={'一等奖':'一等奖_1','二等奖':'二等奖_1','三等奖':'三等奖_1'},inplace=True)
    team_member_df['获奖总人数']=team_member_df.sum(axis=1)
    sort_cols=['获奖总人数','一等奖_1','一等奖_2','一等奖_3','二等奖_1','二等奖_2','二等奖_3','三等奖_1','三等奖_2','三等奖_3']
    team_member_df.sort_values(by=sort_cols,ascending=False,inplace=True)
    
    team_member_df=team_member_df.reset_index().rename(columns={'index':'学校名称'})
    print(team_member_df.head())
    team_member_df.to_excel(path+'各学校获奖人数统计.xlsx',encoding='gbk',index=False)
    # 用柱状图给出获奖人数最多的前20个学校
    plt.figure(figsize=(15, 10)).subplotpars.update(bottom=0.25)
    sns.barplot(x="学校名称", y="获奖总人数", data=team_member_df.loc[0:20],
                palette="muted")
    plt.xticks(ha='right', rotation=40)
    plt.savefig(path+'school_cnt_amount.png')
    plt.show()
    
    temp_df=data[data['奖项']!='成功参与奖'][['奖项','队长所在单位','第一队友所在单位','第二队友所在单位']]
    
    
    if __name__=="__main__":
    school_cnt(data)
    team_member_cnt(temp_df)

    # 分析A、B、C、D、E、F每个题的获奖数量及占比
    def get_question_prize_ration(df):
    question_df=df[['队伍编号','题号']].groupby(['题号']).count()
    question_df=question_df.reset_index().rename(columns={'index':'题号','队伍编号':'获奖数量'})
    # prize_sum=question_df['获奖数量'].sum()
    question_df['获奖比例']=question_df['获奖数量']/(question_df['获奖数量'].sum())
    print(question_df)
    question_df.to_excel(save_dir+'每题获奖数量比例.xlsx',index=False,encoding='gbk')
    plt.figure(figsize=(12,6))
    N,width=6,0.35
    ind=np.arange(N)
    plt.subplot(121)
    plt.title('A、B、C、D、E、F每个题的获奖数量')
    plt.bar(ind,question_df['获奖数量'],width=width)
    plt.ylabel('获奖数量')
    plt.xticks(ind,('A','B','C','D','E','F'))
    plt.subplot(122)
    plt.title('A、B、C、D、E、F题获奖比例')
    plt.pie(question_df['获奖比例'],labels=('A','B','C','D','E','F'),startangle=0,radius=0.8,labeldistance=0.5)
    plt.savefig(save_dir+'每个题的获奖数量和比例.png')
    plt.show()

    # 按地图省份绘制每个省份的获奖人数热力图
    # 调用百度地图API获取每个学校的经纬度信息
    def get_school_info():
    df = pd.read_excel(save_dir+'各学校获奖人数统计.xlsx',encoding='gbk')
    lng_set = []
    lat_set=[]
    for school in df['学校名称']:
        print(school)
        try:
            location = geolocator.geocode(school, timeout=300)
            lng_lat=location.raw['location']
            lng,lat=lng_lat['lng'],lng_lat['lat']
            print(lng,lat)
            lng_set.append(lng)
            lat_set.append(lat)
        except Exception as e:
            print(e)
            time.sleep(5)
            lng_set.append(np.nan)
            lat_set.append(np.nan)
    
    df['lng']=lng_set
    df['lat']=lat_set
    df=df[['学校名称','获奖总人数','lng','lat']]
    df.to_excel(save_dir+'school_loc_prize_nums.xlsx',encoding='gbk', index=False,float_format='%.6f')
    print(df.head())

    # 绘制热力地图
    def draw_heatmap(input_path,out_path):
    df=pd.read_excel(input_path,encoding='gbk')
    df=df[df['lng']>0]
    rows=df.shape[0]
    fout=open(out_path,'w',encoding="utf-8")
    fout.write(
    '''<!DOCTYPE html>
    <html>
    <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no, width=device-width">
    <title>2018研究生建模成绩地理位置分析</title>
    <link rel="stylesheet" href="http://cache.amap.com/lbs/static/main1119.css"/>
    <script src="http://webapi.amap.com/maps?v=1.3&key=your-personal-key"></script>
    <script type="text/javascript" src="http://cache.amap.com/lbs/static/addToolbar.js"></script>
    <script type="text/javascript" src="http://a.amap.com/jsapi_demos/static/resource/heatmapData.js"></script>
    </head>
    <body>
    <div id="container">
    </div>
    <div class="button-group">
        <input type="button" class="button" value="显示热力图" onclick="heatmap.show()"/>
        <input type="button" class="button" value="关闭热力图" onclick="heatmap.hide()"/>
    </div>
    <script>
        var map = new AMap.Map("container", {
            resizeEnable:false,
            center: [121.5197936,31.2874348],
            zoom: 7
        });
        if (!isSupportCanvas()) {
            alert('热力图仅对支持canvas的浏览器适用,您所使用的浏览器不能使用热力图功能,请换个浏览器试试~')
        }'''
    )
    fout.write("\n\t\t var points = [\n")
    for i in range(0,rows-1):
        fout.write("\t\t\t{'lat':%f,'lng':%f,'count':%d},\n"%(df.iat[i,3],df.iat[i,2],df.iat[i,1]))
    fout.write("\t\t\t{'lat':%f,'lng':%f,'count':%d}];"%(df.iat[-1,3],df.iat[-1,2],df.iat[-1,1]))
    fout.write(
    '''
        //详细的参数,可以查看heatmap.js的文档 http://www.patrick-wied.at/static/heatmapjs/docs.html
        //参数说明如下:
        /* visible 热力图是否显示,默认为true
         * opacity 热力图的透明度,分别对应heatmap.js的minOpacity和maxOpacity
         * radius 势力图的每个点的半径大小   
         * gradient  {JSON} 热力图的渐变区间 . gradient如下所示
         *	{
         .2:'rgb(0, 255, 255)',
         .5:'rgb(0, 110, 255)',
         .8:'rgb(100, 0, 255)'
         }
         其中 key 表示插值的位置, 0-1 
         value 为颜色值 
         */
        var heatmap;
        map.plugin(["AMap.Heatmap"], function() {
            //初始化heatmap对象
            heatmap = new AMap.Heatmap(map, {
                radius: 15, //给定半径
                opacity: [0, 0.8]
                /*,gradient:{
                 0.5: 'blue',
                 0.65: 'rgb(117,211,248)',
                 0.7: 'rgb(0, 255, 0)',
                 0.9: '#ffea00',
                 1.0: 'red'
                 }*/
            });
            //设置数据集
            heatmap.setDataSet({
                data: points,
                max: 5
            });
        });
        //判断浏览区是否支持canvas
        function isSupportCanvas() {
            var elem = document.createElement('canvas');
            return !!(elem.getContext && elem.getContext('2d'));
        }
    </script>
    </body>
    </html>
    '''
    )
    fout.close()

    if __name__=="__main__":
    school_cnt(data)
    
    temp_df=data[data['奖项']!='成功参与奖'][['奖项','队长所在单位','第一队友所在单位','第二队友所在单位']]
    team_member_cnt(temp_df)
    
    prize_df=data[data['奖项']!='成功参与奖']
    get_question_prize_ration(prize_df)
    
    # 此处需要替换成你自己的百度地图api_key，去百度地图上申请吧
    api_key = 'your-personal-key' 
    geolocator = Baidu(api_key=api_key)
    get_school_info()
    input_path=save_dir+'school_loc_prize_nums.xlsx'
    out_path=save_dir+'heat_map.html'
    draw_heatmap(input_path,out_path)