In [ ]:

    

daily_pca = trip.pivot_table('trip_id', aggfunc='count',
                           index=['date'], columns='hour').fillna(0)
##adding weather data
daily_pca['Mean_Temperature_F']=weather['Mean_Temperature_F']
daily_pca['Precipitation_In ']=weather['Precipitation_In ']


##adding labels to allow visualization
daily_pca['day_of_week']=daily_pca.index.weekday
#Clustering Days
to_week = range(5)
for k in to_week:
    daily_pca.loc[(daily_pca.day_of_week == k),['day_of_week']]= 'week'
to_end = [5,6]
for k in to_end:
    daily_pca.loc[(daily_pca.day_of_week == k),['day_of_week']]= 'end'

daily_pca['Events']=weather['Events'].fillna(0)
#Clustering Events
to_one = ['Fog']
for st in to_one:
    daily_pca.loc[(daily_pca.Events == st),['Events']]=1
to_two = ['Rain','Fog , Rain','Fog-Rain', 'Rain-Thunderstorm','Rain , Thunderstorm']
for st in to_two:
    daily_pca.loc[(daily_pca.Events == st),['Events']]=2
to_three = ['Snow','Rain-Snow','Rain , Snow']
for st in to_three:
    daily_pca.loc[(daily_pca.Events == st),['Events']]=3
    
#Total number of trips per day
daily_pca['total_trips'] = daily_pca.iloc[:,:23].sum(axis=1)

#Last cleaning for one day
daily_pca = daily_pca.dropna(axis=0)

#Vizualization
display(daily_pca.head())