SF Bike Rentals

In [1]:

    

%%capture
%pip install google-cloud-bigquery bokeh
%load_ext google.cloud.bigquery


    

In [3]:

    

params = {
    "start_date": start_date, 
    "end_date": end_date
}
print("Including rides between {} and {}".format(start_date, end_date))


    

    




Including rides between 2000-01-01 and 2020-01-01

In [4]:

    

%%bigquery sf_stations --params $params
SELECT
    stations.station_id,
    stations.name,
    stations.lat,
    stations.lon,
    COUNT(*) as count
FROM
    `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips` as trips
JOIN 
    `bigquery-public-data.san_francisco_bikeshare.bikeshare_station_info` as stations 
ON 
    trips.start_station_id = stations.station_id
WHERE
    stations.lat BETWEEN 37.7 AND 38.5 AND 
    stations.lon BETWEEN -122.5 AND -122.35 AND
    trips.start_date BETWEEN TIMESTAMP( @start_date ) AND TIMESTAMP( @end_date )
GROUP BY
    stations.station_id, stations.name, stations.lat, stations.lon
ORDER By
    count DESC


    

In [5]:

    

top = sf_stations[['name','count']].head(10)
top.style.hide_index()


    

    
Out[5]:




    
        
name
        
count
    

                

                                
Central Ave at Fell St
                        
80370
            
            

                                
2nd St at Townsend St
                        
56570
            
            

                                
San Francisco Caltrain Station 2  (Townsend St at 4th St)
                        
50283
            
            

                                
8th St at Ringold St
                        
46286
            
            

                                
Howard St at 8th St
                        
45166
            
            

                                
Laguna St at Hayes St
                        
43779
            
            

                                
Webster St at Grove St
                        
41647
            
            

                                
11th St at Natoma St
                        
39243
            
            

                                
McCoppin St at Valencia St
                        
33035
            
            

                                
5th St at Brannan St
                        
30195
            
    

In [6]:

    

from bokeh.io import output_file, output_notebook, show
from bokeh.models import (
  GMapPlot, GMapOptions, ColumnDataSource, Circle, LogColorMapper, BasicTicker, ColorBar,
    DataRange1d, PanTool, WheelZoomTool, BoxSelectTool, HoverTool
)
from bokeh.plotting import gmap
from bokeh.models.mappers import ColorMapper, LogColorMapper
from bokeh.palettes import Viridis6

api_key=!curl -s http://metadata/computeMetadata/v1/instance/attributes/api_key -H "Metadata-Flavor: Google"
api_key=api_key[0]

color_mapper = LogColorMapper(palette=Viridis6)
map_options = GMapOptions(lat=37.775, lng=-122.42, map_type="roadmap", zoom=13)
plot = gmap(api_key, map_options=map_options)
source = ColumnDataSource(sf_stations)

plot.circle(x="lon", y="lat", size=10,
            fill_color={'field': 'count', 'transform': color_mapper},
            fill_alpha=0.8, source=source)

hover = HoverTool(
    tooltips = [
        ("name", "@name"),
        ("loc", "(@lat, @lon)"),
        ("count", "@count")
    ],
    mode='mouse'
)
plot.add_tools(hover)

output_notebook(hide_banner=True)
show(plot)


    

In [ ]: