# -*- 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()
'''
