{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Using cartoframes to investigate the Citibike system\n", "\n", "What 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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`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/)\n", "\n", "Read the `cartoframes` docs here: http://cartoframes.readthedocs.io/en/latest/\n", "\n", "You can view this notebook best on `nbviewer` here: , however\n", "it 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`.\n", "\n", "To get started, let's load the required packages, and set credentials." ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "import cartoframes\n", "\n", "# For convenience we're getting Credentials, Layer, Basemap, and styling\n", "from cartoframes import Credentials\n", "from cartoframes import Layer, BaseMap, styling\n", "\n", "import pandas as pd\n", "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "USERNAME = 'michellemho' # <-- replace with your username \n", "APIKEY = 'abcdefg' # <-- your CARTO API key\n", "creds = Credentials(username=USERNAME, \n", " key=APIKEY)\n", "cc = cartoframes.CartoContext(creds=creds)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Citibike system data can be found here: https://www.citibikenyc.com/system-data\n", "We're going to use the real time data, which comes in General Bikeshare Feed Specification (GBFS) format as a series of JSON files." ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "# Use Pandas to read a JSON of Citibike stations and their statuses\n", "stations_data = pd.read_json('https://gbfs.citibikenyc.com/gbfs/en/station_information.json')\n", "stations = pd.DataFrame(stations_data.data[0])\n", "status_data = pd.read_json('https://gbfs.citibikenyc.com/gbfs/en/station_status.json')\n", "status = pd.DataFrame(status_data.data[0])" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "# Grab the last updated timestamps\n", "timestamp_stations = stations_data.last_updated[0]\n", "timestamp_status = status_data.last_updated[0]" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "# Join the station and statuses together by 'station_id'\n", "station_status = pd.merge(stations,status,how='left', on='station_id')" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
capacityeightd_has_key_dispensereightd_station_serviceslatlonnameregion_idrental_methodsrental_urlshort_name...eightd_has_available_keysis_installedis_rentingis_returninglast_reportednum_bikes_availablenum_bikes_disablednum_docks_availablenum_docks_disablednum_ebikes_available
039FalseNaN40.767272-73.993929W 52 St & 11 Ave71.0[KEY, CREDITCARD]http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...6926.01...False1111523628435203700
133FalseNaN40.719116-74.006667Franklin St & W Broadway71.0[KEY, CREDITCARD]http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...5430.08...False1111523628157222900
227FalseNaN40.711174-74.000165St James Pl & Pearl St71.0[KEY, CREDITCARD]http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...5167.06...False1111523626231171900
362FalseNaN40.683826-73.976323Atlantic Ave & Fort Greene Pl71.0[KEY, CREDITCARD]http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...4354.07...False11115236276124211900
419FalseNaN40.696089-73.978034Park Ave & St Edwards St71.0[KEY, CREDITCARD]http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...4700.06...False1111523627405601300
\n", "

5 rows × 22 columns

\n", "
" ], "text/plain": [ " capacity eightd_has_key_dispenser eightd_station_services lat \\\n", "0 39 False NaN 40.767272 \n", "1 33 False NaN 40.719116 \n", "2 27 False NaN 40.711174 \n", "3 62 False NaN 40.683826 \n", "4 19 False NaN 40.696089 \n", "\n", " lon name region_id rental_methods \\\n", "0 -73.993929 W 52 St & 11 Ave 71.0 [KEY, CREDITCARD] \n", "1 -74.006667 Franklin St & W Broadway 71.0 [KEY, CREDITCARD] \n", "2 -74.000165 St James Pl & Pearl St 71.0 [KEY, CREDITCARD] \n", "3 -73.976323 Atlantic Ave & Fort Greene Pl 71.0 [KEY, CREDITCARD] \n", "4 -73.978034 Park Ave & St Edwards St 71.0 [KEY, CREDITCARD] \n", "\n", " rental_url short_name \\\n", "0 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 6926.01 \n", "1 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 5430.08 \n", "2 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 5167.06 \n", "3 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 4354.07 \n", "4 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 4700.06 \n", "\n", " ... eightd_has_available_keys is_installed is_renting \\\n", "0 ... False 1 1 \n", "1 ... False 1 1 \n", "2 ... False 1 1 \n", "3 ... False 1 1 \n", "4 ... False 1 1 \n", "\n", " is_returning last_reported num_bikes_available num_bikes_disabled \\\n", "0 1 1523628435 2 0 \n", "1 1 1523628157 22 2 \n", "2 1 1523626231 17 1 \n", "3 1 1523627612 42 1 \n", "4 1 1523627405 6 0 \n", "\n", " num_docks_available num_docks_disabled num_ebikes_available \n", "0 37 0 0 \n", "1 9 0 0 \n", "2 9 0 0 \n", "3 19 0 0 \n", "4 13 0 0 \n", "\n", "[5 rows x 22 columns]" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Preview the dataframe\n", "station_status.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `cc.write`\n", "\n", "`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." ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "scrolled": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/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\n", " 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)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Table successfully written to CARTO: https://michellemho-carto.carto.com/dataset/cb_stations_status_1523628491\n" ] } ], "source": [ "# Write station status data to CARTO, using string-formatting to name the dataset with the timestmap\n", "cc.write(station_status, 'cb_stations_status_{}'.format(timestamp_stations), lnglat=('lon','lat'), overwrite=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `cc.map`\n", "\n", "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.\n", "\n", "`cc.map` takes a `layers` argument which specifies the data layers that are to be visualized. They can be imported from `cartoframes` as below.\n", "\n", "There are different types of layers:\n", "\n", "* `Layer` for visualizing CARTO tables\n", "* `QueryLayer` for visualizing arbitrary queries from tables in user's CARTO account\n", "* `BaseMap` for specifying the base map to be used\n", "\n", "Each 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.\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Bring the data back as a map. Style by number of bikes available at each station\n", "# Replace the name of the table with the correct timestamp!\n", "\n", "cc.map(layers=[Layer('cb_stations_status_1523628491',\n", " color={'column': 'num_bikes_available',\n", " 'scheme': styling.geyser(7, bin_method='quantiles')},\n", " size=6),\n", " BaseMap(source='dark')],\n", " interactive=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `cc.query`\n", "\n", "`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." ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [], "source": [ "# set up SQL query to find all the empty citibike stations\n", "# cdb_isochrone is a function available through CARTO data services\n", "# https://carto.com/docs/carto-engine/dataservices-api/isoline-functions/\n", "\n", "empty_query = '''\n", " SELECT *\n", " FROM cb_stations_status_1523628491\n", " WHERE num_bikes_available = 0\n", " '''" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "# use cartoframes query method, and persist as a new table called empty_stations, also return results as dataframe\n", "new_df = cc.query(empty_query, table_name=\"empty_stations\")" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
capacityeightd_active_station_serviceseightd_has_available_keyseightd_has_key_dispensereightd_station_servicesis_installedis_rentingis_returninglast_reportedlat...num_bikes_disablednum_docks_availablenum_docks_disablednum_ebikes_availableregion_idrental_methodsrental_urlshort_namestation_idthe_geom
cartodb_id
619FalseFalse111152362356640.686768...0190071.0['KEY', 'CREDITCARD']http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...4452.031200101000020E610000020D0FCDE647D52C054A5F302E857...
1229FalseFalse111152362751240.720874...0290071.0['KEY', 'CREDITCARD']http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...5476.031500101000020E610000062516C60C67E52C05F460C96455C...
1429FalseFalse100152336614540.714740...0290071.0['KEY', 'CREDITCARD']http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...5288.091520101000020E6100000ABF57632958052C0673F18997C5B...
2130FalseFalse111152362827240.738177...1290071.0['KEY', 'CREDITCARD']http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...6004.071740101000020E6100000AC1C9C808D7E52C07F164B917C5E...
3131FalseFalse100152354379940.736197...0310071.0['KEY', 'CREDITCARD']http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...5964.012380101000020E6100000EBE9C0C58C8052C029F686B13B5E...
\n", "

5 rows × 23 columns

\n", "
" ], "text/plain": [ " capacity eightd_active_station_services \\\n", "cartodb_id \n", "6 19 \n", "12 29 \n", "14 29 \n", "21 30 \n", "31 31 \n", "\n", " eightd_has_available_keys eightd_has_key_dispenser \\\n", "cartodb_id \n", "6 False False \n", "12 False False \n", "14 False False \n", "21 False False \n", "31 False False \n", "\n", " eightd_station_services is_installed is_renting is_returning \\\n", "cartodb_id \n", "6 1 1 1 \n", "12 1 1 1 \n", "14 1 0 0 \n", "21 1 1 1 \n", "31 1 0 0 \n", "\n", " last_reported lat \\\n", "cartodb_id \n", "6 1523623566 40.686768 \n", "12 1523627512 40.720874 \n", "14 1523366145 40.714740 \n", "21 1523628272 40.738177 \n", "31 1523543799 40.736197 \n", "\n", " ... \\\n", "cartodb_id ... \n", "6 ... \n", "12 ... \n", "14 ... \n", "21 ... \n", "31 ... \n", "\n", " num_bikes_disabled num_docks_available num_docks_disabled \\\n", "cartodb_id \n", "6 0 19 0 \n", "12 0 29 0 \n", "14 0 29 0 \n", "21 1 29 0 \n", "31 0 31 0 \n", "\n", " num_ebikes_available region_id rental_methods \\\n", "cartodb_id \n", "6 0 71.0 ['KEY', 'CREDITCARD'] \n", "12 0 71.0 ['KEY', 'CREDITCARD'] \n", "14 0 71.0 ['KEY', 'CREDITCARD'] \n", "21 0 71.0 ['KEY', 'CREDITCARD'] \n", "31 0 71.0 ['KEY', 'CREDITCARD'] \n", "\n", " rental_url short_name \\\n", "cartodb_id \n", "6 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 4452.03 \n", "12 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 5476.03 \n", "14 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 5288.09 \n", "21 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 6004.07 \n", "31 http://app.citibikenyc.com/S6Lr/IBV092JufD?sta... 5964.01 \n", "\n", " station_id the_geom \n", "cartodb_id \n", "6 120 0101000020E610000020D0FCDE647D52C054A5F302E857... \n", "12 150 0101000020E610000062516C60C67E52C05F460C96455C... \n", "14 152 0101000020E6100000ABF57632958052C0673F18997C5B... \n", "21 174 0101000020E6100000AC1C9C808D7E52C07F164B917C5E... \n", "31 238 0101000020E6100000EBE9C0C58C8052C029F686B13B5E... \n", "\n", "[5 rows x 23 columns]" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "new_df.head()" ] }, { "cell_type": "code", "execution_count": 80, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# map the empty stations, style by capacity\n", "cc.map(layers=[Layer('empty_stations',\n", " color='capacity'),\n", " BaseMap(source='dark')],\n", " interactive=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "anaconda-cloud": {}, "kernelspec": { "display_name": "Python [default]", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.5.2" } }, "nbformat": 4, "nbformat_minor": 2 }