使用python对excel表格商业数据可视化分析源码及数据.rar

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="销售经理与数量关系统计图")