<jupyter_start><jupyter_text>Using cartoframes to investigate the Citibike systemWhat does the Citibike station system look like right now? Citibike publishes an open feed of station statuses. Let's use cartframes to process this data and send it to your CARTO account and create some maps. `cartoframes` lets you use CARTO in a Python environment so that you can do all of your analysis and mapping in, for example, a Jupyter notebook. `cartoframes` allows you to use CARTO's functionality for data analysis, storage, location services like routing and geocoding, and visualization. `cartoframes` is based on working with data in a Pandas dataframe. Pandas is a handy python library for data analysis (https://pandas.pydata.org/)Read the `cartoframes` docs here: http://cartoframes.readthedocs.io/en/latest/You can view this notebook best on `nbviewer` here: , howeverit is recommended to download this notebook, install cartoframes and dependencies, and use on your computer instead so you can more easily explore the functionality of `cartoframes`.To get started, let's load the required packages, and set credentials.<jupyter_code>import cartoframes

# For convenience we're getting Credentials, Layer, Basemap, and styling
from cartoframes import Credentials
from cartoframes import Layer, BaseMap, styling

import pandas as pd
%matplotlib inline
USERNAME = 'michellemho'  # <-- replace with your username 
APIKEY = 'abcdefg'       # <-- your CARTO API key
creds = Credentials(username=USERNAME, 
                    key=APIKEY)
cc = cartoframes.CartoContext(creds=creds)<jupyter_output><empty_output><jupyter_text>Citibike system data can be found here: https://www.citibikenyc.com/system-dataWe're going to use the real time data, which comes in General Bikeshare Feed Specification (GBFS) format as a series of JSON files.<jupyter_code># Use Pandas to read a JSON of Citibike stations and their statuses
stations_data = pd.read_json('https://gbfs.citibikenyc.com/gbfs/en/station_information.json')
stations = pd.DataFrame(stations_data.data[0])
status_data = pd.read_json('https://gbfs.citibikenyc.com/gbfs/en/station_status.json')
status = pd.DataFrame(status_data.data[0])
# Grab the last updated timestamps
timestamp_stations = stations_data.last_updated[0]
timestamp_status = status_data.last_updated[0]
# Join the station and statuses together by 'station_id'
station_status = pd.merge(stations,status,how='left', on='station_id')
# Preview the dataframe
station_status.head()<jupyter_output><empty_output><jupyter_text>`cc.write``CartoContext` has several methods for interacting with [CARTO](https://carto.com) in a Python environment. The first one we're using is `cc.write` which will send a Pandas dataframe to your CARTO account.<jupyter_code># Write station status data to CARTO, using string-formatting to name the dataset with the timestmap
cc.write(station_status, 'cb_stations_status_{}'.format(timestamp_stations), lnglat=('lon','lat'), overwrite=True)<jupyter_output>/Users/mho/anaconda3/lib/python3.5/site-packages/carto/resources.py:90: FutureWarning: This is part of a non-public CARTO API and may change in the future. Take this into account if you are using this in a production environment
  warnings.warn('This is part of a non-public CARTO API and may change in the future. Take this into account if you are using this in a production environment', FutureWarning)<jupyter_text>`cc.map`Now that we can inspect the data, we can map it to see how the values change over the geography. We can use the `cc.map` method for this purpose.`cc.map` takes a `layers` argument which specifies the data layers that are to be visualized. They can be imported from `cartoframes` as below.There are different types of layers:* `Layer` for visualizing CARTO tables* `QueryLayer` for visualizing arbitrary queries from tables in user's CARTO account* `BaseMap` for specifying the base map to be usedEach of the layers has different styling options. `Layer` and `QueryLayer` take the same styling arguments, and `BaseMap` can be specified to be light/dark and options on label placement.Maps can be `interactive` or not. Set interactivity with the `interactive` with `True` or `False`. If the map is static (not interactive), it will be embedded in the notebook as either a `matplotlib` axis or `IPython.Image`. Either way, the image will be transported with the notebook. Interactive maps will be embedded zoom and pan-able maps.<jupyter_code># Bring the data back as a map. Style by number of bikes available at each station
# Replace the name of the table with the correct timestamp!

cc.map(layers=[Layer('cb_stations_status_1523628491',
                      color={'column': 'num_bikes_available',
                             'scheme': styling.geyser(7, bin_method='quantiles')},
                      size=6),
               BaseMap(source='dark')],
       interactive=True)<jupyter_output><empty_output><jupyter_text>`cc.query``CartoContext` has several methods for retrieving data from your CARTO account into a Pandas dataframe. In this example, we'll use `cc.query` to pass in a SQL query and return the results.<jupyter_code># set up SQL query to find all the empty citibike stations
# cdb_isochrone is a function available through CARTO data services
# https://carto.com/docs/carto-engine/dataservices-api/isoline-functions/

empty_query = '''
        SELECT *
        FROM cb_stations_status_1523628491
        WHERE num_bikes_available = 0
        '''
# use cartoframes query method, and persist as a new table called empty_stations, also return results as dataframe
new_df = cc.query(empty_query, table_name="empty_stations")
new_df.head()
# map the empty stations, style by capacity
cc.map(layers=[Layer('empty_stations',
                    color='capacity'),
               BaseMap(source='dark')],
       interactive=True)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup