数据分析项目
城市餐饮店铺选址分析
1、从三个维度“口味”、“人均消费”、“性价比”对不同菜系进行比较,并筛选出可开店铺的餐饮类型
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import warnings
warnings.filterwarnings('ignore')
# 不发出警告
from bokeh.io import output_notebook
output_notebook()
# 导入notebook绘图模块
from bokeh.plotting import figure,show
from bokeh.models import ColumnDataSource
# 导入图表绘制、图标展示模块
# 导入ColumnDataSource模块
# 查看数据
import os
os.chdir('C:\\Users\\86183\\Desktop\\')
# 创建工作路径
df1=pd.read_excel('上海餐饮数据.xlsx',sheet_name=0,header=0)
df1_length = len(df1)
df1_columns = df1.columns.tolist()
print('数据量为%i条' % len(df1))
print(df1.head())
# 口味、客单价、性价比指标计算
data1 = df1[['类别','口味','环境','服务','人均消费']]
data1.dropna(inplace = True)
data1 = data1[(data1['口味']>0)&(data1['人均消费']>0)]
# 筛选数据,清除空值、为0的数据
data1['性价比'] = (data1['口味'] + data1['环境'] + data1['服务']) / data1['人均消费']
# 计算性价比指数
fig,axes = plt.subplots(1,3,figsize = (10,4))
data1.boxplot(column=['口味'],ax = axes[0])
data1.boxplot(column=['人均消费'],ax = axes[1])
data1.boxplot(column=['性价比'],ax = axes[2])
# 查看异常值
def f1(data,col):
q1 = data[col].quantile(q = 0.25)
q3 = data[col].quantile(q = 0.75)
iqr = q3-q1
t1 = q1 - 3 * iqr
t2 = q3 + 3 * iqr
return data[(data[col] > t1)&(data[col]<t2)][['类别',col]]
# 创建函数 → 删除异常值
data_kw = f1(data1,'口味')
data_rj = f1(data1,'人均消费')
data_xjb = f1(data1,'性价比')
# 数据异常值处理
def f2(data,col):
col_name = col + '_norm'
data_gp = data.groupby('类别').mean()
data_gp[col_name] = (data_gp[col] - data_gp[col].min())/(data_gp[col].max()-data_gp[col].min())
data_gp.sort_values(by = col_name, inplace = True, ascending=False)
return data_gp
# 创建函数 → 标准化指标并排序
data_kw_score = f2(data_kw,'口味')
data_rj_score = f2(data_rj,'人均消费')
data_xjb_score = f2(data_xjb,'性价比')
# 指标标准化得分
data_final_q1 = pd.merge(data_kw_score,data_rj_score,left_index=True,right_index=True) # 合并口味、人均消费指标得分
data_final_q1 = pd.merge(data_final_q1,data_xjb_score,left_index=True,right_index=True) # 合并性价比指标得分
# 合并数据
data_final_q1.head()
# 制作散点图、柱状图
# x轴为“人均消费”,y轴为“性价比得分”,点的大小为“口味得分”
from bokeh.models import HoverTool
from bokeh.palettes import brewer
from bokeh.models.annotations import BoxAnnotation
from bokeh.layouts import gridplot
# 导入模块
data_final_q1['size'] = data_final_q1['口味_norm'] * 40 # 添加size字段
data_final_q1.index.name = 'type'
data_final_q1.columns = ['kw','kw_norm','price','price_norm','xjb','xjb_norm','size']
# 将中文改为英文
# 添加颜色参数
source = ColumnDataSource(data_final_q1)
# 创建ColumnDataSource数据
hover = HoverTool(tooltips=[("餐饮类型", "@type"),
("人均消费", "@price"),
("性价比得分", "@xjb_norm"),
("口味得分", "@kw_norm")
]) # 设置标签显示内容
result = figure(plot_width=800, plot_height=250,
title="餐饮类型得分情况" ,
x_axis_label = '人均消费', y_axis_label = '性价比得分',
tools=[hover,'box_select,reset,xwheel_zoom,pan,crosshair'])
# 构建绘图空间
result.circle(x = 'price',y = 'xjb_norm',source = source,
line_color = 'black',line_dash = [6,4],fill_alpha = 0.6,
size = 'size')
price_mid = BoxAnnotation(left=40,right=80, fill_alpha=0.1, fill_color='navy')
result.add_layout(price_mid)
# 设置人均消费中间价位区间
result.title.text_font_style = "bold"
result.ygrid.grid_line_dash = [6, 4]
result.xgrid.grid_line_dash = [6, 4]
# 散点图
# 绘制柱状图
data_type = data_final_q1.index.tolist()# 提取横坐标
kw = figure(plot_width=800, plot_height=250, title='口味得分',x_range=data_type,
tools=[hover,'box_select,reset,xwheel_zoom,pan,crosshair'])
kw.vbar(x='type', top='kw_norm', source=source,width=0.9, alpha = 0.8,color = 'red')
kw.ygrid.grid_line_dash = [6, 4]
kw.xgrid.grid_line_dash = [6, 4]
# 柱状图1
price = figure(plot_width=800, plot_height=250, title='人均消费得分',x_range=kw.x_range,
tools=[hover,'box_select,reset,xwheel_zoom,pan,crosshair'])
price.vbar(x='type', top='price_norm', source=source,width=0.9, alpha = 0.8,color = 'green')
price.ygrid.grid_line_dash = [6, 4]
price.xgrid.grid_line_dash = [6, 4]
# 柱状图2
p = gridplot([[result],[kw], [price]])
# 组合图表
show(p)
# 加载数据
df2=pd.read_excel('qgis处理结果.xlsx',sheet_name=0,header=0)
data2 = df2.fillna(0)
data2.head()
# 指标统计
data2['长度_bzh']=(data2['长度']-data2['长度'].min())/(data2['长度'].max()-data2['长度'].min())
data2['cy_count_bzh']=(data2['cy_count']-data2['cy_count'].min())/(data2['cy_count'].max()-data2['cy_count'].min())
data2['sc_count_bzh']=(data2['sc_count']-data2['sc_count'].min())/(data2['sc_count'].max()-data2['sc_count'].min())
data2['z_bzh']=(data2['Z']-data2['Z'].min())/(data2['Z'].max()-data2['Z'].min()) # 道路密度指标标准化
# 指标标准化
data2['zhzb']=data2['z_bzh']*0.4+data2['cy_count_bzh']*0.3+data2['长度_bzh']*0.2+data2['sc_count_bzh']*0.1
data3=data2.sort_values(by='zhzb',ascending=False).reset_index()
# 计算综合评分并查看TOP10的网格ID
# 制作空间散点图
data3['size'] = data3['zhzb'] * 20
data3['color'] = 'green'
data3['color'].iloc[:10] = 'red'
# 添加size字段
source = ColumnDataSource(data3)
# 创建ColumnDataSource数据
hover = HoverTool(tooltips=[("经度", "@lng"),
("纬度", "@lat"),
("最终得分", "@zhzb"),
]) # 设置标签显示内容
p = figure(plot_width=800, plot_height=800,
title="空间散点图" ,
tools=[hover,'box_select,reset,wheel_zoom,pan,crosshair'])
# 构建绘图空间
p.square(x = 'lng',y = 'lat',source = source,
line_color = 'black',fill_alpha = 0.5,
size = 'size',color = 'color')
p.ygrid.grid_line_dash = [6, 4]
p.xgrid.grid_line_dash = [6, 4]
# 散点图
show(p)