import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
#数据加载
data1 = pd.read_excel('meal_order_detail.xlsx',sheet_name='meal_order_detail1')
data2 = pd.read_excel('meal_order_detail.xlsx',sheet_name='meal_order_detail2')
data3 = pd.read_excel('meal_order_detail.xlsx',sheet_name='meal_order_detail3')
# 数据预处理
# 数据合并
data = pd.concat([data1,data2,data3],axis=0)
data.head(10)
![](https://img-blog.csdnimg.cn/401cb5af01a747debbaeba53fa5eaf52.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 查看数据详细信息
data.info()
![](https://img-blog.csdnimg.cn/c77d1c8228554e3cbf54bd3bbd97742e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 观察可得需要删除空值列,dropna删除空值,axis=1表示删除空值列,inplace=True表示在原来的数据上进行删除不需要重新赋值
data.dropna(axis=1,inplace=True)
data.info()
![](https://img-blog.csdnimg.cn/7ad1d265328643ba850677edb7d0d692.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 统计卖出菜品的平均价格
round(data['amounts'].mean(),2) #方法一:pandas自带函数
round(np.mean(data.amounts),2) #方法二:numpy函数,计算比较快(底层是C++),如果数据量比较多的情况下建议采用
![](https://img-blog.csdnimg.cn/1fffd41eb6414855a980024b7bd9267b.png)
# 频数统计,什么菜最受欢迎,统计菜名出现次数
# 统计频数用value_counts函数,默认是降序排列相当于加上参数ascending=False,然后通过切片取前10名即可
dishes_count = data['dishes_name'].value_counts()[:10]
dishes_count
![](https://img-blog.csdnimg.cn/8974f5eca9074b0c9301ab6411cbf5fa.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_16,color_FFFFFF,t_70,g_se,x_16)
#可视化
# 设置显示中文
plt.rcParams['font.sans-serif'] = ['SimHei']
dishes_count.plot(kind='line',color='r')
dishes_count.plot(kind='bar',fontsize=16)
#在图每个柱子上添加注释
for x,y in enumerate(dishes_count):
print(x,y)
plt.text(x,y+2,y,ha='center',fontsize=12) #x,y+2表示的是这个注释写在什么位置,y表示注释写什么内容,ha参数设置注释居中靠左或右
plt.title('8月份最受欢迎的十道菜品')
![](https://img-blog.csdnimg.cn/592fda9fdc614b179fa0e1798cb662ac.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 订单点菜的种类最多
data_group = data['order_id'].value_counts()[:10]
data_group.plot(kind='bar',fontsize=16,color=['r','m','b','y','g'])
plt.title('订单点菜的种类最多top10',fontsize=16)
plt.xlabel('订单id',fontsize=16)
plt.ylabel('点菜种类',fontsize=16)
![](https://img-blog.csdnimg.cn/aba42d08b3e04f16a7cccfb5764085b5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 订单id点菜数量top10
data['total_prices'] = data['counts'] * data['amounts'] #统计单道菜总价
dataGroup = data[['order_id','counts','amounts','total_prices']].groupby('order_id')
Group_sum = dataGroup.sum() #分组求和
sort_counts = Group_sum.sort_values(by='counts',ascending=False)
sort_counts['counts'][:10].plot(kind='bar',fontsize=16)
plt.xlabel('订单id')
plt.ylabel('点菜数量')
plt.title('订单id点菜数量top10')
![](https://img-blog.csdnimg.cn/dc19d54e27c1446abf16d425c00e4da6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 哪个订单id消费最多
price = Group_sum.sort_values('total_prices',ascending=False)
price['total_prices'][:10].plot(kind='bar')
plt.xlabel('订单id')
plt.ylabel('消费金额')
plt.title('消费金额top10')
![](https://img-blog.csdnimg.cn/58710c7abfa14f678545d036e3f14314.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 哪个订单平均消费最贵
Group_sum['average'] = Group_sum['total_prices'] / Group_sum['counts']
sort_average = Group_sum.sort_values('average',ascending=False)
sort_average['average'][0:10].plot(kind='bar',fontsize=16)
plt.xlabel('订单id')
plt.ylabel('平均消费')
plt.title('单菜品平均消费top10')
![](https://img-blog.csdnimg.cn/29817822487b44d08e2f55805bee2d5d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 一天当中什么时间段,点菜量比较集中(hour)
data['hourcount'] = 1 # 计数
data['time'] = pd.to_datetime(data['place_order_time']) # 将时间转换为日期格式存储
data['hour'] = data['time'].map(lambda x:x.hour)
group_by_hour = data.groupby('hour').count()['hourcount']
group_by_hour.plot(kind='bar')
plt.xlabel('小时')
plt.ylabel('点菜数量')
plt.title('点菜数量与小时关系')
![](https://img-blog.csdnimg.cn/734675bb689041c89a63daa416531490.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 哪一天点菜数量最多
data['daycount'] = 1
data['day'] = data['time'].map(lambda x:x.day)
group_by_day = data.groupby('day').count()['daycount']
group_by_day.plot(kind='bar')
plt.xlabel('8月份日期')
plt.ylabel('点菜数量')
plt.title('点菜数量与日期关系')
![](https://img-blog.csdnimg.cn/deb37e5b3fe64d05922b715c51e0f8a9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)
# 星期几点菜最多
data['weekcount'] = 1
data['weekday'] = data['time'].map(lambda x:x.weekday())
group_by_weekday = data.groupby('weekday').count()['weekcount']
group_by_weekday.plot(kind='bar')
plt.xlabel('星期')
plt.ylabel('点菜数量')
plt.title('点菜数量与星期关系')
![](https://img-blog.csdnimg.cn/2a8078eb0ea24a1f865287237abb4bf2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAUVlpUmVu,size_20,color_FFFFFF,t_70,g_se,x_16)