Python:pandas读取Excel数据用matplotlib图表呈现

拼搏现实的明天。 2022-12-27 12:36 199阅读 0赞

民航局官网（[http://www.caac.gov.cn/XXGK/XXGK/TJSJ/202003/t20200309\_201358.html][http_www.caac.gov.cn_XXGK_XXGK_TJSJ_202003_t20200309_201358.html]）提供了一份excel表格数据：2019年民航机场吞吐量排名.xls，如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70][]

作为备份，同步存放一份到 [https://download.csdn.net/download/zhangphil/13624153][https_download.csdn.net_download_zhangphil_13624153]，下载后是一份excel数据文件。内容如上。

该份数据包含了2019年各大城市机场客流量数据和排名。用Python的pandas读取该excel表格数据，然后用matplotlib绘制中国各大城市机场客流量的排名。Python代码：

import matplotlib
    import pandas as pd
    import matplotlib.pyplot as plt
    
    KEYS = ['城市机场', '排名', '客流量']
    FONT_SIZE = 18
    
    
    # 数据清洗
    # 数据链接 https://download.csdn.net/download/zhangphil/13624153
    def get_data():
        df = pd.read_excel(io='2019年民航机场吞吐量排名.xls',
                           nrows=20,  # 读取20行
                           skiprows=[1, 2, 3, 4],  # 跳过1,2,3,4行
                           index_col=[0, 1, 2])  # 读取0,1,2列
    
        data = []
        for v in df.index.values:
            bundle = {}
            for i in range(len(KEYS)):
                bundle.setdefault(KEYS[i], v[i])
            data.append(bundle)
    
        return data
    
    
    if __name__ == '__main__':
        myfont = matplotlib.font_manager.FontProperties(fname='C:\Windows\Fonts\msyh.ttc')
        plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
        plt.rc('font', family='YaHei', weight='bold')
    
        data = get_data()
    
        name = []
        client = []
        order = []
        for d in data:
            name.append(d[KEYS[0]])
            order.append(d[KEYS[1]])
            client.append(d[KEYS[2]])
    
        # plt.rcdefaults()
        fig, ax = plt.subplots(figsize=(14, 12))
    
        b = ax.barh(range(len(name)), client, align='center', color='red')
    
        # 为横向水平的柱图右侧添加数据标签。
        i = 0
        for rect in b:
            w = rect.get_width()
            ax.text(x=w, y=rect.get_y() + rect.get_height() / 2, s='%d' % (int(w) / 10000) + "万",
                    horizontalalignment='left', verticalalignment='center',
                    fontproperties=myfont, fontsize=FONT_SIZE, color='green')
            ax.text(x=w / 2, y=rect.get_y() + rect.get_height() / 2, s=str(order[i]),
                    horizontalalignment='left', verticalalignment='center',
                    fontproperties=myfont, fontsize=FONT_SIZE, color='white')
            i = i + 1
    
        ax.set_yticks(range(len(name)))
        ax.set_yticklabels(name, fontsize=FONT_SIZE, fontproperties=myfont)
    
        ax.invert_yaxis()
    
        ax.set_xlabel('数据来源:民航局', fontsize=FONT_SIZE + 5, fontproperties=myfont)
        ax.set_title('2019年中国城市机场客流量排名TOP20', fontsize=FONT_SIZE + 10, fontproperties=myfont)
    
        # 不要横坐标上的label标签。
        plt.xticks(())
    
        # 清除四周的边框线
        ax.get_yaxis().set_visible(True)
        for spine in ["left", "top", "right", "bottom"]:
            ax.spines[spine].set_visible(False)
    
        plt.subplots_adjust(left=0.15)  # 调整左侧边距
    
        #ax.margins(y=0.01) #缩放 zoom in
    
        ax.set_aspect('auto')
    
        plt.show()

上面Python代码生成的统计图表结果输出：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70 1][]

[http_www.caac.gov.cn_XXGK_XXGK_TJSJ_202003_t20200309_201358.html]: http://www.caac.gov.cn/XXGK/XXGK/TJSJ/202003/t20200309_201358.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70]: /images/20221120/07160e53afe24860872de919237708d9.png
[https_download.csdn.net_download_zhangphil_13624153]: https://download.csdn.net/download/zhangphil/13624153
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201210223759313.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA==,size_16,color_FFFFFF,t_70