使用python对excel表格商业数据可视化分析

本文主要是介绍使用python对excel表格商业数据可视化分析，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

今天有同学问能对excel表格的商业数据可视化吗？用python做？于是我就写了一篇关于这个问题的，欢迎交流学习。
1.模块安装：

pip3 install xlrd==1.2.0 -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pyecharts -i https://pypi.tuna.tsinghua.edu.cn/simple

2.可视化代码

import xlrd
import numpy as np
from pyecharts.charts import Bar
from pyecharts.charts import Pie, Grid
from pyecharts import options as opts

if __name__ == "__main__":
    # 读取表格
    data = xlrd.open_workbook("某连锁超市运营数据.xlsx")
    # 获取表格的sheets
    table = data.sheets()[0]
    # 行
    rows = table.nrows
    print("xlsx行数：",rows)
    # 列
    cols = table.ncols
    print("xlsx列数：",cols)
    # 获取第一行数据
    row1data = table.row_values(0)
    print(row1data)  # 输出标题行

    #存储所有表格信息
    tables = []
    for rows in range(1, table.nrows):
        dict_ = {"Order id": "", "Order date": "", "Planned delivery days": "",
                 "Customer name": "", "Partition": "", "City": "",
                 "State / Province": "","Country": "","Area": "",
                 "Price": "","Number": "","Sales volume": "",
                 "Discount": "","Manager": "","Yes/No": ""}
        dict_["Order id"] = table.cell_value(rows, 0)
        dict_["Order date"] = table.cell_value(rows, 1)
        dict_["Planned delivery days"] = table.cell_value(rows, 2)
        dict_["Customer name"] = table.cell_value(rows, 3)
        dict_["Partition"] = table.cell_value(rows, 4)
        dict_["City"] = table.cell_value(rows, 5)
        dict_["State / Province"] = table.cell_value(rows, 6)
        dict_["Country"] = table.cell_value(rows, 7)
        dict_["Area"] = table.cell_value(rows, 8)
        dict_["Price"] = table.cell_value(rows, 9)
        dict_["Number"] = table.cell_value(rows, 10)
        dict_["Sales volume"] = table.cell_value(rows, 11)
        dict_["Discount"] = table.cell_value(rows, 12)
        dict_["Manager"] = table.cell_value(rows, 13)
        dict_["Yes/No"] = table.cell_value(rows, 14)
        tables.append(dict_)

#关于是否退回与省份关系统计图
    # 存储所有城市信息list
    list_city=[]
    for city in tables:
        list_city.append(str(city['State / Province']))
    #去除重复的
    list_city_new=list(set(list_city))
    print("ok")
    print(list_city_new)
    print("城市数目:", len(list_city_new))#城市数目
    #list转tuple
    tuple_city=tuple(list_city_new)
    print("x轴:",tuple_city)

    #是否退订数据统计
    city_yes_no=np.zeros(len(list_city_new))
    print("city_yes_no:",city_yes_no)

    #将各个分段的数量统计
    for i in tables:
        # print(str(i["Yes/No"]))
        if str(i["Yes/No"])=="1.0":
            print(str(i["State / Province"]),str(i["Yes/No"]))
            print(list_city_new.index(i["State / Province"]))
            city_yes_no[list_city_new.index(i["State / Province"])]=city_yes_no[list_city_new.index(i["State / Province"])]+1

    #y轴
    print("city_yes_no处理完的数据:",city_yes_no)
    tuple_city_yes_no=tuple(city_yes_no)
    # 构建是否退回与省份关系柱状统计图

    c = (
        Pie(init_opts=opts.InitOpts(height="800px", width="1200px"))
            .add("是否退回与省份关系统计图",
                 [list(z) for z in zip(tuple_city, tuple_city_yes_no)],
                 center=["35%", "38%"],
                 radius="40%",
                 label_opts=opts.LabelOpts(
                     formatter="{b|{b}: }{c}  {per|{d}%}  ",
                     rich={
                         "b": {"fontSize": 16, "lineHeight": 33},
                         "per": {
                             "color": "#eee",
                             "backgroundColor": "#334455",
                             "padding": [2, 4],
                             "borderRadius": 2,
                         },
                     }
                 ))
            .set_global_opts(title_opts=opts.TitleOpts(title="是否退回与省份关系统计饼图"),
                             legend_opts=opts.LegendOpts(pos_left="0%", pos_top="65%"))
            .render("./是否退回与省份关系统计饼图.html")
    )



# 关于销售数量与省份关系统计图
    city_num = np.zeros(len(list_city_new))
    print("city_num:", city_num)

    # 将各个分段的数量统计
    for i in tables:
        # print(str(i["Number"]), str(i["State / Province"]))
        # print(list_city_new.index(i["State / Province"]))
        city_num[list_city_new.index(i["State / Province"])] = city_num[list_city_new.index(i["State / Province"])] + i["Number"]

    # y轴
    print("city_num处理完的数据:", city_num)
    tuple_city_num = tuple(city_num)
    # 构建销售数量与省份关系柱状统计图

    c = (
        Pie(init_opts=opts.InitOpts(height="800px", width="1200px"))
            .add("销售数量与省份关系统计图",
                 [list(z) for z in zip(tuple_city, tuple_city_num)],
                 center=["35%", "38%"],
                 radius="40%",
                 label_opts=opts.LabelOpts(
                     formatter="{b|{b}: }{c}  {per|{d}%}  ",
                     rich={
                         "b": {"fontSize": 16, "lineHeight": 33},
                         "per": {
                             "color": "#eee",
                             "backgroundColor": "#334455",
                             "padding": [2, 4],
                             "borderRadius": 2,
                         },
                     }
                 ))
            .set_global_opts(title_opts=opts.TitleOpts(title="销售数量与省份关系统计饼图"),
                             legend_opts=opts.LegendOpts(pos_left="0%", pos_top="65%"))
            .render("./销售数量与省份关系统计饼图.html")
    )

#销售经理与退回关系统计图
    # 存储所有销售经理信息list
    list_manager = []
    for manager in tables:
        list_manager.append(str(manager['Manager']))
    # 去除重复的
    list_manager_new = list(set(list_manager))
    print("ok2")
    print(list_manager_new)
    print("销售经理数目:", len(list_manager_new))  # 销售经理数目
    # list转tuple
    tuple_manager_new = tuple(list_manager_new)
    print("x轴:", tuple_manager_new)

    # 是否退订数据统计
    manager_yes_no = np.zeros(len(list_manager_new))
    print("manager_yes_no:", manager_yes_no)

    for i in tables:
        if str(i["Yes/No"]) == "1.0":
            print(str(i["Manager"]), str(i["Yes/No"]))
            print(list_manager_new.index(i["Manager"]))
            manager_yes_no[list_manager_new.index(i["Manager"])] = manager_yes_no[list_manager_new.index(i["Manager"])] + 1

    # y轴
    print("manager_yes_no处理完的数据:", manager_yes_no)
    tuple_manager_yes_no = tuple(manager_yes_no)
    # 构建是否退回与销售经理关系柱状统计图
    c = (
        Bar()
            .add_xaxis(tuple_manager_new)
            .add_yaxis("退回数量", tuple_manager_yes_no, color="#af00ff")
            .set_global_opts(title_opts=opts.TitleOpts(title="是否退回与销售经理关系统计图"))
            .render("./是否退回与销售经理关系统计图.html")
    )

#销售经理与销售数量关系统计图

    # 销售数量统计
    num_add = np.zeros(len(list_manager_new))
    print("num_add:", num_add)

    for i in tables:
        # print(str(i["Number"]), str(i["Manager"]))
        # print(list_manager_new.index(i["Manager"]))
        num_add[list_manager_new.index(i["Manager"])] = num_add[list_manager_new.index(i["Manager"])] + float(i["Number"])

    # y轴
    print("num_add处理完的数据:", num_add)
    tuple_num_add = tuple(num_add)
    # 构建是否退回与销售经理关系柱状统计图
    c = (
        Bar()
            .add_xaxis(tuple_manager_new)
            .add_yaxis("数量", tuple_num_add, color="#af00ff")
            .set_global_opts(title_opts=opts.TitleOpts(title="销售经理与数量关系统计图"))
            .render("./销售经理与数量关系统计图.html")
    )

3.结果图片
在这里插入图片描述

源码及数据：
https://download.csdn.net/download/visual_eagle/63361689
使用说明：