网约车Uber（优步）交通出行数据分析（数据分析实战项目 | SQL + PowerBI）
工具：MySQL、PowerBI
数据来源：https://www.kaggle.com/fivethirtyeight/uber-pickups-in-new-york-city/home
注：个人实战锻炼项目，欢迎交流
一. 项目背景
1. 项目概览：根据Uber真实订单数据对基地运营及乘客出行特征进行分析，总结网约车运营过程中应关注的点，指导企业进行精细化运营。
2. 数据来源与理解：https://www.kaggle.com/fivethirtyeight/uber-pickups-in-new-york-city/home
- uber旗下雇佣基地2014年4-9月订单表（含经纬度）-- 4,534,327条数据
合并为同一张表
- uber旗下雇佣基地2015年1-2月的订单表（含活跃车辆数、行程数）-- 354条数据
- 非雇佣基地2015年1-8月的订单表（含活跃车辆数、行程数）-- 334条数据
- federal基地订单表（含订单状态）-- 276条数据
二. 分析目的
本项目作为个人练习项目：①进一步了解交通出行行业；②运用已掌握的技能及数据分析方法
三.分析思路
注：使用工具为MySQL、PowerBI
四.数据清洗
1. 选择子集：将CSV文件导入到数据库中
（此处表名默认使用源数据集CSV文件名，实际业务中需根据业务对表名进行命名）
2. 列名重命名
3. 一致化处理
源数据中date格式不满足SQL处理要求
ALTER TABLE `uber-raw-data` ADD uberdate date; update `uber-raw-data` set uberdate = STR_TO_DATE(Date,'%m/%d/%Y'); ALTER TABLE `uber-jan-feb-foil` ADD uberdate date; update `uber-jan-feb-foil` set uberdate = STR_TO_DATE(date,'%m/%d/%Y'); ALTER TABLE `other-fhv-services_jan-aug` ADD uberdate date; update`other-fhv-services_jan-aug` set uberdate = STR_TO_DATE(PickUpDate,'%m/%d/%Y');
4. 重复值处理
未发现重复值
#重复值处理（表中不存在唯一ID，对整行数据进行判断） SELECT COUNT(*) FROM `other-federal_02216`;-- 有订单状态信息的federal基地表 SELECT DISTINCT COUNT(*) FROM `other-federal_02216`; SELECT COUNT(*) FROM `other-fhv-services_jan-aug`;-- 非雇佣基地2015/1-8月的订单表 SELECT DISTINCT COUNT(*) FROM `other-fhv-services_jan-aug`; SELECT COUNT(*) FROM `uber-jan-feb-foil`;-- uber旗下雇佣基地2015/1-2的订单表（车辆数/订单数） SELECT DISTINCT COUNT(*) FROM `uber-jan-feb-foil`; SELECT COUNT(*) FROM `uber-raw-data`;-- uber旗下雇佣基地2014/4-9的经纬度表 SELECT DISTINCT COUNT(*) FROM `uber-raw-data`;
5. 缺失值处理
未发现缺失值
6. 异常值处理
#federal基地订单表 SELECT `Status`,count(`Status`) as snum,count(`Status`)/276 as spercent FROM `other-federal_02216` GROUP BY `Status`;-- 订单状态，无异常值
#非雇佣基地2015/1-8月的订单表 SELECT * FROM `other-fhv-services_jan-aug` WHERE `PickUpDate` < '01/01/2015' or `PickUpDate` > '31/08/2015';-- 订单日期无异常值 SELECT NumberofVehicles,count(NumberofVehicles) FROM `other-fhv-services_jan-aug` GROUP BY NumberofVehicles ORDER BY NumberofVehicles;-- 车辆数存在异常值“-”，进一步查询源数据集是因为未录入 SELECT NumberofTrips,count(NumberofTrips) FROM `other-fhv-services_jan-aug` GROUP BY NumberofTrips ORDER BY NumberofTrips;-- 订单数存在异常值，csv文件中的千位分隔符,在数据库中无法识别 UPDATE `other-fhv-services_jan-aug` SET NumberofTrips = REPLACE(NumberofTrips,',','');-- 此处NumberofTrips为varchar类型，使用字符串替换方法，否则使用乘法
#uber旗下雇佣基地2015/1-2的订单表 SELECT *,trips/active_vehicles as avg_utv FROM `uber-jan-feb-foil` ORDER BY avg_utv;-- 无异常值
#uber旗下雇佣基地2014/4-9的订单表 SELECT Base,COUNT(base) FROM `uber-raw-data` GROUP BY Base;-- 无异常值
五.数据分析
（一）基地分析
#对uber-raw-data表中的date、time进行处理，提取月份、周几、小时 CREATE VIEW uberorder_LL2014 AS SELECT Base,lat,lon,uberdate,month(uberdate) as m,(weekday(uberdate)+1) as w,Time,HOUR(Time) as h FROM `uber-raw-data`;
创建视图数据如下
1. 总体运营情况
①业务增长情况（基于2014年4-9月数据）
SELECT *,((sorder - lead_sorder)/lead_sorder) as '环比增长率' FROM( SELECT m,sorder,lead(sorder,1) over(ORDER BY m DESC) as lead_sorder FROM ( SELECT month(uberdate) as m,COUNT(base) as sorder FROM `uber-raw-data` GROUP BY month(uberdate))t )s;-- 按月份计算环比增长率
2014年4-9月，Uber业务一直在增长，7月、9月的环比增长最大，9月业务环比增长23.98%
②不同基地订单总量比较（基于2014年4-9月数据）
数据显示，在2014年4-9月期间，基地B02617、B02598、B02682订单量更多，业务量排名第一的基地B02617贡献了32.17%的订单
③基地运营效率（基于2015年1-2月数据）
基于行程数、活跃车辆数分析基地运营效率（由于2014年数据中缺少车辆数据，此处基于2015年1-2月数据分析）
#计算各个基地总行程数、总活跃车辆数、每辆车每日平均接单数 SELECT dispatching_base_number,SUM(trips),SUM(active_vehicles),SUM(trips)/SUM(active_vehicles) as avg_butv FROM `uber-jan-feb-foil` GROUP BY dispatching_base_number ORDER BY avg_butv DESC;
基地B02764订单量是最大的，但其运营效率排第四，基地B02682每辆车每日平均接单数最多。应结合基地地理位置、实际订单时间及区域分布进行进一步分析，提高车辆利用率。
2. 订单变化趋势
①基地订单增长趋势（基于2014年4-9月数据）
2014年4--9月份的Uber旗下五家基地：
出现大幅增长的是B02617（深褐色） B02764（黄色），订单量几乎不变的基地是B02512（绿色）B02598（蓝色），而基地B02682（红色）出现了较大幅度下滑，需结合业务实际具体分析原因
②业绩下滑基地后续订单情况
进一步查看2015年1-2月的数据，原本业务下滑较严重的基地B02682（红色）订单量已回升。
3. 订单区域分布（基于2014年4-9月数据）
①椭圆区域7月、8月订单量急剧增长：
结合该区域地理位置（新泽西海岸线）及业务实际情况，推测是由于旅游带来的业务增长。实际业务中可根据旅游行业相关数据对旅游旺季及区域进行预测，并采取与商家合作等业务策略，提升业务量并对车辆进行合理调度。
②矩形区域6月开始无订单出现：
对6月的订单分布进行进一步分析，分别查看5个下属基地的订单分布，均未在此区域产生订单，结合该区域行政及地理位置，推断可能是优步的业务策略调整（跨城订单），由于数据集中缺少这部分数据，暂无法分析。
③除因旅游带来的小范围订单增长，从整体区域分布看，4-9月，uber的业务一直在扩张
4. 订单满足率分析
以federal基地为例对Uber的订单满足率进行分析
①订单满足情况分析
SELECT `Status`,count(`Status`) as snum,count(`Status`)/276 as spercent FROM `other-federal_02216` GROUP BY `Status`;
federal基地的完成订单占46.38%，订单取消率为10.51%，需优化调度策略，提升订单满足率，降低订单取消率
②订单取消分析
进一步分析取消部分订单：时间、地点
SELECT *,LEFT(Time,2) as h FROM `other-federal_02216` WHERE `Status` = 'Cancelled';
取消订单发生时间集中在1-12点之间，根据取消订单信息及词云分析，取消订单主要目的地都是机场（La Guardia Airport、John F Kennedy International Airport），这说明夜间前往机场的订单难以得到满足，需结合业务对凌晨及上午的活跃车辆进行合理调度并采取一定措施（如夜间补贴、空车费等）引导司机接前往机场的订单。
5. Uber VS for-hire vehicle (FHV) companies
数据集中还提供了其他出租车公司的订单数据，我们对Uber与FHV的订单进行对比
-- uber旗下基地订单量（2015/1-2） SELECT *,ROUND((trips/active_vehicles),2) as avg_utv FROM `uber-jan-feb-foil` ORDER BY avg_utv DESC;
Uber下属基地每辆车平均每天接单量大部分都在8单以上
-- 非雇佣基地订单量（2015/1-8） SELECT *,ROUND((`NumberofTrips`/`NumberofVehicles`),2) as avg_tv FROM `other-fhv-services_jan-aug` WHERE NumberofVehicles >= 1 HAVING avg_tv < 100 ORDER BY avg_tv DESC;-- 日接单量>100的数据视为异常数据，需具体分析,此处仅剔除后进行分析
其他出租车公司的车辆日接单量明显低于Uber，且业务总量有下降趋势
（二）用户出行特征分析
1. 用户出行时间偏好
①按小时（一天的出行规律）
SELECT h,count(h) as sh FROM (SELECT uberdate,h,count(Base) FROM uberorder_LL2014 GROUP BY uberdate,h ORDER BY uberdate)t GROUP BY h;-- 计算结果count(h)为183 SELECT h,count(Base)/183 as sorder FROM uberorder_LL2014 GROUP BY h;-- 按小时
一天中乘客的出行高峰出现在早上7点和下午5点，考虑主要为上下班高峰，针对上下班高峰需提前调度车辆，以满足高峰期出行订单需求
②按周（一周的出行规律）
SELECT t1.w,t1.sorder,t2.sw,t1.sorder/t2.sw as avgorder FROM (SELECT w,count(Base) as sorder FROM uberorder_2014 GROUP BY w)t1 INNER JOIN (SELECT w,count(w) as sw FROM(SELECT DISTINCT uberdate,w FROM uberorder_2014)t GROUP BY w)t2 ON t1.w=t2.w ORDER BY t1.w;-- 按周
出行高峰出现在周四、周五
③按月
按月订单主要源于业务增长，除此之外，主要考虑由于旅游旺季、节假日等带来的季节性业务增长。
2. 用户出行区域分布
①日出行高峰时间段区域分布（7点、17点）
7点、17点的出行订单分布较为集中，主要集中在办公楼集中区域、商圈、住宅区，可根据办公集中区域、住宅集中区域的经纬度进行车辆的合理调度
②日常出行区域主要集中在城市中心区，旅游旺季会向旅游区扩张
建议结合用户不同出行需求，调整业务策略，最大化车辆的利用率，提升业务量。
六. 总结
1. 基地运营：
2014年、2015年，Uber业务一直处于增长状态，但其下属基地运营情况参差不齐，在提升业务量的同时，需注意调整各基地的运营策略，提升车辆利用率及订单满足率；
2. 提升订单满足率：
针对凌晨机场订单等取消率较高的订单，需根据实际业务进一步分析，从地理位置、时间、取消原因等角度考虑，降低订单取消率；
3. 季节性业务：
从时间、区域维度，可利用外部数据，对旅游旺季、节假日订单进行预测，同时可采取策略与商家达成合作，以合理调度活跃车辆，提升业务量；
4. 用户出行特征：
合理利用用户行为数据，根据日常用户出行时间（7点、17点为出行高峰（日）；周四、周五（周））和区域（办公集中区、住宅集中区）及季节性出行特征构建模型并进行预测，指导车辆调度及业务策略。
