数据挖掘 出租车轨迹数据分析Python源码 生成统计图分析图

# -*- coding: utf-8 -*- 支持文件中出现中文字符 ######################################################################### """ Created on Fri Oct 11 22:36:36 2019 @author:Li 代码功能描述： """ ##################################################################### import numpy as np #导入numpy库进行数据分析 import pandas as pd #利用pandas创建矩阵数据表DataFrame import datetime from datetime import datetime import openpyxl #利用openpyxl处理excel文件 from scipy import signal import math import matplotlib import seaborn as sns import matplotlib.pylab as plt #绘图 #from numpy import * import time from openpyxl import * taxi=pd.DataFrame() writerD = pd.ExcelWriter('E:\Personal Files\文档\学习资料\车辆大数据\出租车\ TZZ.xlsx') # 建立Excel表格，存储处理后数据 for i in range(11,12): # 读表格数据 for j in range(0,11): taxi1=pd.DataFrame( pd.read_csv('E:\Personal Files\文档\学习资料\车辆大数据\czc\yellow_tripdata_2016-%.2d_%d.csv'%(i,j))) taxi=taxi.append(taxi1,ignore_index=False,sort=True) #合并表格 # 添加各种标识，下车只有小时，分钟，有乘车时间 taxi['tpep_pickup_datetime']=pd.to_datetime(taxi['tpep_pickup_datetime'],format='%Y-%m-%d %H:%M:%S') taxi['tpep_dropoff_datetime']=pd.to_datetime(taxi['tpep_dropoff_datetime'],format='%Y-%m-%d %H:%M:%S') taxi['month']=taxi['tpep_pickup_datetime'].dt.month taxi['weekday']=taxi['tpep_pickup_datetime'].dt.weekday taxi['day']=taxi['tpep_pickup_datetime'].dt.day taxi['hour']=taxi['tpep_pickup_datetime'].dt.hour taxi['minute']=taxi['tpep_pickup_datetime'].dt.minute taxi["date"]=taxi.tpep_pickup_datetime.values.astype("datetime64[D]") s=pd.to_datetime(taxi['tpep_dropoff_datetime']-taxi['tpep_pickup_datetime']) taxi['Durtime']=s.dt.minute #计算 按月，日，日期统计乘车时间和，均值，次数 #每月订单总量，平均时常，与总时常 month_trip=taxi.groupby(['month'])['Durtime'].agg(["sum","mean","count"]) month_trip=month_trip.reset_index() month_trip.rename(columns={'sum':'month_sum_trip_dur','mean':'month_avg_trip_dur','count':"month_trip_times"}, inplace = True) #每天订单总量，平均时常，与总时常 date_trip=taxi.groupby(['date'])["Durtime"].agg(["sum","mean","count"]) date_trip.rename(columns={'sum':'date_sum_trip_duration','mean':'date_avg_trip_dur','count':"date_trip_times"}, inplace = True) date_trip=date_trip.reset_index() #所有月1-30号订单总量，平均时常，与总时常 day_trip=taxi.groupby(['day'])["Durtime"].agg(["sum","mean","count"]) day_trip.rename(columns={'sum':'day_sum_trip_duration','mean':'day_avg_trip_dur','count':"day_trip_times"}, inplace = True) day_trip=day_trip.reset_index() #为每天加入平均上客数量 x = taxi.groupby(['day'])["passenger_count"].agg(["mean"]) x = x.reset_index() day_trip["avg_passenger_count"] = x["mean"] #周几订单总量，平均时常，与总时常 week_trip=taxi.groupby(['month','weekday','day','hour'])["Durtime"].agg(["mean","count"]) week_trip.rename(columns={'mean':'week_avg_trip_dur','count':"week_trip_times"}, inplace = True) week_trip=week_trip.reset_index() #为周几加入平均上客数量 x = taxi.groupby(['month','weekday','day','hour'])["passenger_count"].agg(["mean"]) x = x.reset_index() week_trip["avg_passenger_count"] = x["mean"] #0-24小时 支付小费统计 x = taxi.groupby(['month','weekday','day','hour'])["tip_amount"].agg(["sum","mean"]) x = x.reset_index() week_trip["sum_tip_amount"] = x["sum"] week_trip["avg_tip_amount"] = x["mean"] plt.figure(figsize=(16,6)) sns.swarmplot(x="avg_tip_amount", y="week_avg_trip_dur", data=week_trip) plt.show() plt.figure(figsize=(16,6)) sns.boxplot(x="week_avg_trip_dur", y="avg_tip_amount", data=week_trip) plt.show() #作图 ''' 做单月内，平均乘车人数与周几的关系 plt.figure(figsize=(10,5)) sns.boxplot(x="weekday", y="avg_passenger_count", hue="month", data=week_trip) plt.show() 24小时内，乘车次数的统计 plt.figure(figsize=(16,6)) sns.swarmplot(x="hour", y="week_trip_times", data=week_trip) plt.show() 30天次数分布 plt.figure() plt.subplot(111) ax1= plt.plot(day_trip.day, day_trip.day_trip_times, color="red", alpha=0.8,marker='*') plt.title("Day Trip Time") plt.ylabel('Day trip time', fontsize=12) plt.xlabel('Pickup_Day', fontsize=12) #taxi[0:5].to_excel(writerD, sheet_name='20151026') # 将各特征值数据输出至表格，113,114分sheet保存 #writerD.save() plt.figure(figsize=(10,8)) sns.swarmplot(x="weekday", y="week_trip_times", hue="month", data=week_trip) plt.show() plt.figure(figsize=(16,6)) sns.boxplot(x="hour", y="week_avg_trip_dur", data=week_trip) plt.show() ''' #plt.savefig(r'C:\Users\Nan\Desktop\车辆大数据\月乘车次数与乘车人数统计.png' ) #plt.close('all') ''' plt.subplot(312) ax2= plt.plot(date_trip.date, date_trip.date_trip_times, color="red", alpha=0.8,marker='o') plt.title("Date Trip Time") plt.ylabel('Date trip time', fontsize=12) plt.xlabel('Pickup_Date', fontsize=12) plt.subplot(313) ax2= plt.plot(day_trip.day, day_trip.day_trip_times, color="red", alpha=0.8,marker='*') plt.title("Day Trip Time") plt.ylabel('Day trip time', fontsize=12) plt.xlabel('Pickup_Day', fontsize=12) plt.show() # print(taxi.head(2)) #taxi.to_excel(writerD, sheet_name='20151026') # 将各特征值数据输出至表格，113,114分sheet保存 #writerD.save() '''