# 制作不同渠道对应的销量占比的饼图import openpyxl
# 获取数据对应的工作表
sheet = openpyxl.open('files/2020年销售数据.xlsx')['data']# 获取最大行数
m_r = sheet.max_row
# 收集每一行第三列和第七列的数据# ================比较笨的方法================
data =[]for row inrange(3, m_r+1):
cell3 = sheet.cell(row,3)
cell7 = sheet.cell(row,7)
data.append((cell3.value, cell7.value))# {'拼多多': xxx, '抖音': xxx}# {'拼多多': 174, '抖音': 29, '天猫': 99, '京东':68}
new_data ={}for x in data:
value = new_data.get(x[0],0)
new_data[x[0]]= value + x[1]print(new_data)print(new_data.items())# ==============比较聪明的方法==================
data ={}for row inrange(3, m_r+1):
name = sheet.cell(row,3).value
num = sheet.cell(row,7).value
value = data.get(name,0)
data[name]= value + num
# print(data) # {'拼多多': 22398, '抖音': 12545, '天猫': 37027, '京东': 21522, '实体': 13911}
2.作图
from pyecharts.charts import Pie
from pyecharts import options
pie = Pie()
pie.add('销量',list(data.items()),
radius=(80,200),
rosetype='radius')
pie.set_series_opts(
label_opts=options.LabelOpts(
formatter='{b}:{d}%'))
pie.set_global_opts(
legend_opts=options.LegendOpts(
is_show=False),
title_opts=options.TitleOpts(
title='2020年各大渠道商品销量占比'))
pie.render('files/各渠道销量占比.html')
柱状图
1.准备数据
# 不同品牌商品的平均售价import openpyxl
sheet = openpyxl.open('files/2020年销售数据.xlsx').active
m_r = sheet.max_row
# ==================比较笨的方法==============
data =[]for row inrange(3, m_r+1):
value1 = sheet.cell(row,5).value
value2 = sheet.cell(row,6).value
data.append((value1, value2))print(data)# [('八匹马', 99), ('八匹马', 219), ('八匹马', 169), ('八匹马', 169), ('皮皮虾', 249), ...]# {'八匹马': (总价格, 总次数), '皮皮虾': (总价格, 总次数), ...}
new_data ={}for x in data:# x : ('八匹马', 99)
result = new_data.get(x[0],(0,0))
price = result[0]
num = result[1]
new_data[x[0]]=(price + x[1], num +1)# [('八匹马', 平均价格),]
new_data2 =[]for key in new_data:
value = new_data[key]
new_data2.append((key, value[0]//value[1]))print(new_data2)# [('八匹马', 197), ('皮皮虾', 444), ('壁虎', 310), ('花花姑娘', 484), ('啊哟喂', 207)]# ===================比较聪明的做法=====================# {'八匹马': 99}
data ={}for row inrange(3, m_r+1):
value1 = sheet.cell(row,5).value
value2 = sheet.cell(row,6).value
result = data.get(value1,(0,0))
data[value1]=(result[0]+value2, result[1]+1)print(data)
x_data =[]
y_data =[]for key in data:
x_data.append(key)
value = data[key]
y_data.append(value[0]// value[1])print(x_data, y_data)
2.作图
from pyecharts.charts import Bar
from pyecharts import options
bar = Bar()
bar.add_xaxis(x_data)
bar.add_yaxis('平均价格',
y_data
)
bar.render('files/品牌平均价格.html')
折线图
# 时间处理import openpyxl
sheet = openpyxl.open('files/2020年销售数据.xlsx')['data']
m_r = sheet.max_row
for row inrange(3, m_r+1):
t =str(sheet.cell(row,1).value)
month =int(t[5]+t[6])print(month)