examples/Citibike Example.ipynb

{
 "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: <https://nbviewer.jupyter.org/github/CartoDB/cartoframes/blob/master/examples/Citibike%20Example.ipynb>, 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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>capacity</th>\n",
       "      <th>eightd_has_key_dispenser</th>\n",
       "      <th>eightd_station_services</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>region_id</th>\n",
       "      <th>rental_methods</th>\n",
       "      <th>rental_url</th>\n",
       "      <th>short_name</th>\n",
       "      <th>...</th>\n",
       "      <th>eightd_has_available_keys</th>\n",
       "      <th>is_installed</th>\n",
       "      <th>is_renting</th>\n",
       "      <th>is_returning</th>\n",
       "      <th>last_reported</th>\n",
       "      <th>num_bikes_available</th>\n",
       "      <th>num_bikes_disabled</th>\n",
       "      <th>num_docks_available</th>\n",
       "      <th>num_docks_disabled</th>\n",
       "      <th>num_ebikes_available</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>71.0</td>\n",
       "      <td>[KEY, CREDITCARD]</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>6926.01</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523628435</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.719116</td>\n",
       "      <td>-74.006667</td>\n",
       "      <td>Franklin St &amp; W Broadway</td>\n",
       "      <td>71.0</td>\n",
       "      <td>[KEY, CREDITCARD]</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>5430.08</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523628157</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.711174</td>\n",
       "      <td>-74.000165</td>\n",
       "      <td>St James Pl &amp; Pearl St</td>\n",
       "      <td>71.0</td>\n",
       "      <td>[KEY, CREDITCARD]</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>5167.06</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523626231</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>71.0</td>\n",
       "      <td>[KEY, CREDITCARD]</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>4354.07</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523627612</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.696089</td>\n",
       "      <td>-73.978034</td>\n",
       "      <td>Park Ave &amp; St Edwards St</td>\n",
       "      <td>71.0</td>\n",
       "      <td>[KEY, CREDITCARD]</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>4700.06</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523627405</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "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": [
       "<iframe srcdoc=\"<!DOCTYPE html>\n",
       "<html>\n",
       "  <head>\n",
       "    <title>Carto</title>\n",
       "    <meta name='viewport' content='initial-scale=1.0, user-scalable=no' />\n",
       "    <meta http-equiv='content-type' content='text/html; charset=UTF-8' />\n",
       "    <link rel='shortcut icon' href='http://cartodb.com/assets/favicon.ico' />\n",
       "\n",
       "    <style>\n",
       "     html, body, #map {\n",
       "       height: 100%;\n",
       "       padding: 0;\n",
       "       margin: 0;\n",
       "     }\n",
       "     #zoom-center {\n",
       "       position: absolute;\n",
       "       right: 0;\n",
       "       top: 0;\n",
       "       background-color: rgba(255, 255, 255, 0.7);\n",
       "       width: 240px;\n",
       "       z-index: 100;\n",
       "       padding: 4px;\n",
       "     }\n",
       "    </style>\n",
       "\n",
       "    <link rel='stylesheet' href='https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css' />\n",
       "  </head>\n",
       "  <body>\n",
       "    <div id='zoom-center'>\n",
       "      zoom=<span id='zoom'>4</span>,\n",
       "      lng=<span id='lon'>No data</span>, lat=<span id='lat'>No data</span>\n",
       "    </div>\n",
       "    <div id='map'></div>\n",
       "    <script src='https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js'></script>\n",
       "\n",
       "    <script>\n",
       "     const config  = {&quot;maps_api_template&quot;: &quot;https://michellemho-carto.carto.com&quot;, &quot;named_map&quot;: {&quot;params&quot;: {&quot;north&quot;: 40.8143944379158, &quot;sql_0&quot;: &quot;SELECT * FROM cb_stations_status_1523628491&quot;, &quot;cartocss_0&quot;: &quot;#layer {  marker-allow-overlap: true; marker-fill-opacity: 1; marker-line-width: 0.5; marker-width: 6; marker-line-color: #000; marker-line-opacity: 1; marker-fill: ramp([num_bikes_available], cartocolor(Geyser), quantiles(7), >);}#layer[num_bikes_available = null] {  marker-fill: #ccc;}&quot;, &quot;west&quot;: -74.0969366, &quot;east&quot;: -73.9077436, &quot;south&quot;: 40.6553997744783, &quot;basemap_url&quot;: &quot;https://{s}.basemaps.cartocdn.com/rastertiles/dark_all/{z}/{x}/{y}.png&quot;}, &quot;name&quot;: &quot;cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0&quot;}, &quot;user_name&quot;: &quot;michellemho-carto&quot;, &quot;tiler_domain&quot;: &quot;carto.com&quot;, &quot;type&quot;: &quot;namedmap&quot;, &quot;sql_api_template&quot;: &quot;https://michellemho-carto.carto.com&quot;, &quot;tiler_port&quot;: &quot;80&quot;, &quot;tiler_protocol&quot;: &quot;https&quot;};\n",
       "     const bounds  = [[40.8143944379158, -73.9077436], [40.6553997744783, -74.0969366]];\n",
       "     const options = {&quot;https&quot;: true, &quot;filter&quot;: [&quot;mapnik&quot;, &quot;torque&quot;]};\n",
       "     var labels_url = '';\n",
       "\n",
       "     const adjustLongitude = (lng) => (\n",
       "       lng - ((Math.ceil((lng + 180) / 360) - 1) * 360)\n",
       "     );\n",
       "     const map = L.map('map', {\n",
       "       zoom: 3,\n",
       "       center: [0, 0],\n",
       "     });\n",
       "\n",
       "     if (L.Browser.retina) {\n",
       "         var basemap = config.named_map.params.basemap_url.replace('.png', '@2x.png');\n",
       "         labels_url = labels_url.replace('.png', '@2x.png');\n",
       "     } else {\n",
       "         var basemap = config.named_map.params.basemap_url;\n",
       "     }\n",
       "     L.tileLayer(basemap, {\n",
       "         attribution: &quot;&copy; <a href=\\&quot;http://www.openstreetmap.org/copyright\\&quot;>OpenStreetMap</a>&quot;\n",
       "     }).addTo(map);\n",
       "\n",
       "     const updateMapInfo = () => {\n",
       "       $('#zoom').text(map.getZoom());\n",
       "       $('#lat').text(map.getCenter().lat.toFixed(4));\n",
       "       $('#lon').text(adjustLongitude(map.getCenter().lng).toFixed(4));\n",
       "     };\n",
       "\n",
       "     cartodb.createLayer(map, config, options)\n",
       "            .addTo(map)\n",
       "            .done((layer) => {\n",
       "                // add labels layer\n",
       "                if (labels_url) {\n",
       "                    var topPane = L.DomUtil.create('div', 'leaflet-top-pane', map.getPanes().mapPane);\n",
       "                    var topLayer = new L.tileLayer(labels_url).addTo(map);\n",
       "                    topPane.appendChild(topLayer.getContainer());\n",
       "                    topLayer.setZIndex(7);\n",
       "                 }\n",
       "\n",
       "                // fit map to bounds\n",
       "                if (bounds.length) {\n",
       "                  map.fitBounds(bounds);\n",
       "                }\n",
       "\n",
       "                updateMapInfo();\n",
       "                map.on('move', () => {\n",
       "                  updateMapInfo();\n",
       "                });\n",
       "            })\n",
       "            .error((err) => {\n",
       "              console.log('ERROR: ', err);\n",
       "            });\n",
       "    </script>\n",
       "\n",
       "  </body>\n",
       "</html>\n",
       "\" width=800 height=400>  Preview image: <img src=\"https://michellemho-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0/800/400.png?bbox=-74.0969366%2C40.6553997744783%2C-73.9077436%2C40.8143944379158&config=%7B%22sql_0%22%3A+%22SELECT+%2A+FROM+cb_stations_status_1523628491%22%2C+%22cartocss_0%22%3A+%22%23layer+%7B++marker-allow-overlap%3A+true%3B+marker-fill-opacity%3A+1%3B+marker-line-width%3A+0.5%3B+marker-width%3A+6%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B+marker-fill%3A+ramp%28%5Bnum_bikes_available%5D%2C+cartocolor%28Geyser%29%2C+quantiles%287%29%2C+%3E%29%3B%7D%23layer%5Bnum_bikes_available+%3D+null%5D+%7B++marker-fill%3A+%23ccc%3B%7D%22%2C+%22basemap_url%22%3A+%22https%3A%2F%2F%7Bs%7D.basemaps.cartocdn.com%2Frastertiles%2Fdark_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%7D&anti_cache=0.5146757983779697\" /></iframe>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>capacity</th>\n",
       "      <th>eightd_active_station_services</th>\n",
       "      <th>eightd_has_available_keys</th>\n",
       "      <th>eightd_has_key_dispenser</th>\n",
       "      <th>eightd_station_services</th>\n",
       "      <th>is_installed</th>\n",
       "      <th>is_renting</th>\n",
       "      <th>is_returning</th>\n",
       "      <th>last_reported</th>\n",
       "      <th>lat</th>\n",
       "      <th>...</th>\n",
       "      <th>num_bikes_disabled</th>\n",
       "      <th>num_docks_available</th>\n",
       "      <th>num_docks_disabled</th>\n",
       "      <th>num_ebikes_available</th>\n",
       "      <th>region_id</th>\n",
       "      <th>rental_methods</th>\n",
       "      <th>rental_url</th>\n",
       "      <th>short_name</th>\n",
       "      <th>station_id</th>\n",
       "      <th>the_geom</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cartodb_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523623566</td>\n",
       "      <td>40.686768</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>4452.03</td>\n",
       "      <td>120</td>\n",
       "      <td>0101000020E610000020D0FCDE647D52C054A5F302E857...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>29</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523627512</td>\n",
       "      <td>40.720874</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>5476.03</td>\n",
       "      <td>150</td>\n",
       "      <td>0101000020E610000062516C60C67E52C05F460C96455C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>29</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1523366145</td>\n",
       "      <td>40.714740</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>5288.09</td>\n",
       "      <td>152</td>\n",
       "      <td>0101000020E6100000ABF57632958052C0673F18997C5B...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1523628272</td>\n",
       "      <td>40.738177</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>6004.07</td>\n",
       "      <td>174</td>\n",
       "      <td>0101000020E6100000AC1C9C808D7E52C07F164B917C5E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1523543799</td>\n",
       "      <td>40.736197</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "      <td>http://app.citibikenyc.com/S6Lr/IBV092JufD?sta...</td>\n",
       "      <td>5964.01</td>\n",
       "      <td>238</td>\n",
       "      <td>0101000020E6100000EBE9C0C58C8052C029F686B13B5E...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "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": [
       "<iframe srcdoc=\"<!DOCTYPE html>\n",
       "<html>\n",
       "  <head>\n",
       "    <title>Carto</title>\n",
       "    <meta name='viewport' content='initial-scale=1.0, user-scalable=no' />\n",
       "    <meta http-equiv='content-type' content='text/html; charset=UTF-8' />\n",
       "    <link rel='shortcut icon' href='http://cartodb.com/assets/favicon.ico' />\n",
       "\n",
       "    <style>\n",
       "     html, body, #map {\n",
       "       height: 100%;\n",
       "       padding: 0;\n",
       "       margin: 0;\n",
       "     }\n",
       "     #zoom-center {\n",
       "       position: absolute;\n",
       "       right: 0;\n",
       "       top: 0;\n",
       "       background-color: rgba(255, 255, 255, 0.7);\n",
       "       width: 240px;\n",
       "       z-index: 100;\n",
       "       padding: 4px;\n",
       "     }\n",
       "    </style>\n",
       "\n",
       "    <link rel='stylesheet' href='https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css' />\n",
       "  </head>\n",
       "  <body>\n",
       "    <div id='zoom-center'>\n",
       "      zoom=<span id='zoom'>4</span>,\n",
       "      lng=<span id='lon'>No data</span>, lat=<span id='lat'>No data</span>\n",
       "    </div>\n",
       "    <div id='map'></div>\n",
       "    <script src='https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js'></script>\n",
       "\n",
       "    <script>\n",
       "     const config  = {&quot;maps_api_template&quot;: &quot;https://michellemho-carto.carto.com&quot;, &quot;named_map&quot;: {&quot;params&quot;: {&quot;north&quot;: 40.8038654, &quot;sql_0&quot;: &quot;SELECT * FROM empty_stations&quot;, &quot;cartocss_0&quot;: &quot;#layer {  marker-allow-overlap: true; marker-fill-opacity: 1; marker-line-width: 0.5; marker-width: 10; marker-line-color: #000; marker-line-opacity: 1; marker-fill: ramp([capacity], cartocolor(Mint), quantiles(5), >);}#layer[capacity = null] {  marker-fill: #ccc;}&quot;, &quot;west&quot;: -74.0969366, &quot;east&quot;: -73.911239, &quot;south&quot;: 40.6721683, &quot;basemap_url&quot;: &quot;https://{s}.basemaps.cartocdn.com/rastertiles/dark_all/{z}/{x}/{y}.png&quot;}, &quot;name&quot;: &quot;cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0&quot;}, &quot;user_name&quot;: &quot;michellemho-carto&quot;, &quot;tiler_domain&quot;: &quot;carto.com&quot;, &quot;type&quot;: &quot;namedmap&quot;, &quot;sql_api_template&quot;: &quot;https://michellemho-carto.carto.com&quot;, &quot;tiler_port&quot;: &quot;80&quot;, &quot;tiler_protocol&quot;: &quot;https&quot;};\n",
       "     const bounds  = [[40.8038654, -73.911239], [40.6721683, -74.0969366]];\n",
       "     const options = {&quot;https&quot;: true, &quot;filter&quot;: [&quot;mapnik&quot;, &quot;torque&quot;]};\n",
       "     var labels_url = '';\n",
       "\n",
       "     const adjustLongitude = (lng) => (\n",
       "       lng - ((Math.ceil((lng + 180) / 360) - 1) * 360)\n",
       "     );\n",
       "     const map = L.map('map', {\n",
       "       zoom: 3,\n",
       "       center: [0, 0],\n",
       "     });\n",
       "\n",
       "     if (L.Browser.retina) {\n",
       "         var basemap = config.named_map.params.basemap_url.replace('.png', '@2x.png');\n",
       "         labels_url = labels_url.replace('.png', '@2x.png');\n",
       "     } else {\n",
       "         var basemap = config.named_map.params.basemap_url;\n",
       "     }\n",
       "     L.tileLayer(basemap, {\n",
       "         attribution: &quot;&copy; <a href=\\&quot;http://www.openstreetmap.org/copyright\\&quot;>OpenStreetMap</a>&quot;\n",
       "     }).addTo(map);\n",
       "\n",
       "     const updateMapInfo = () => {\n",
       "       $('#zoom').text(map.getZoom());\n",
       "       $('#lat').text(map.getCenter().lat.toFixed(4));\n",
       "       $('#lon').text(adjustLongitude(map.getCenter().lng).toFixed(4));\n",
       "     };\n",
       "\n",
       "     cartodb.createLayer(map, config, options)\n",
       "            .addTo(map)\n",
       "            .done((layer) => {\n",
       "                // add labels layer\n",
       "                if (labels_url) {\n",
       "                    var topPane = L.DomUtil.create('div', 'leaflet-top-pane', map.getPanes().mapPane);\n",
       "                    var topLayer = new L.tileLayer(labels_url).addTo(map);\n",
       "                    topPane.appendChild(topLayer.getContainer());\n",
       "                    topLayer.setZIndex(7);\n",
       "                 }\n",
       "\n",
       "                // fit map to bounds\n",
       "                if (bounds.length) {\n",
       "                  map.fitBounds(bounds);\n",
       "                }\n",
       "\n",
       "                updateMapInfo();\n",
       "                map.on('move', () => {\n",
       "                  updateMapInfo();\n",
       "                });\n",
       "            })\n",
       "            .error((err) => {\n",
       "              console.log('ERROR: ', err);\n",
       "            });\n",
       "    </script>\n",
       "\n",
       "  </body>\n",
       "</html>\n",
       "\" width=800 height=400>  Preview image: <img src=\"https://michellemho-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0/800/400.png?bbox=-74.0969366%2C40.6721683%2C-73.911239%2C40.8038654&config=%7B%22sql_0%22%3A+%22SELECT+%2A+FROM+empty_stations%22%2C+%22cartocss_0%22%3A+%22%23layer+%7B++marker-allow-overlap%3A+true%3B+marker-fill-opacity%3A+1%3B+marker-line-width%3A+0.5%3B+marker-width%3A+10%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B+marker-fill%3A+ramp%28%5Bcapacity%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%2C+%3E%29%3B%7D%23layer%5Bcapacity+%3D+null%5D+%7B++marker-fill%3A+%23ccc%3B%7D%22%2C+%22basemap_url%22%3A+%22https%3A%2F%2F%7Bs%7D.basemaps.cartocdn.com%2Frastertiles%2Fdark_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%7D&anti_cache=0.869014040848166\" /></iframe>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "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
}