Created
May 25, 2017 16:04
-
-
Save mehak-sachdeva/7ed8dc6b96a08e9a9be755665a4ca140 to your computer and use it in GitHub Desktop.
Revisions
-
mehak-sachdeva created this gist
May 25, 2017 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,537 @@ { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Bars around the DOHMH office\n", "\n", "\n", "## Workflow\n", "\n", "Investigate bars around 15 minutes walking distance of the office:\n", "\n", "* Getting data from the liquor liceses dataset\n", "* Querying to narrow down the dataset to only the ones required\n", "* Visualize data with cartoframes\n", "* Drawing a 15-minute walking isochrone from the offie location\n", "* Only keep all the bars that intersect the isochrone\n", "* Visualize data in Builder and add icons for respective uses\n", "\n", "Final dashboard: https://team.carto.com/u/mehak-carto/builder/dd55a48a-415f-11e7-8098-0ecd1babdde5/embed\n", "\n", "Link to download the data from: https://data.ny.gov/Economic-Development/Liquor-Authority-Quarterly-List-of-Active-Licenses/hrvs-fxs2/data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Installing dependencies\n", "\n", "Install [cartoframes](https://github.com/cartodb/cartoframes) (which is currently in beta). I recommend installing in a virtual environment to keep things clean and sandboxed.\n", "\n", "## Getting the data\n", "\n", "While downloading the data from here(https://data.ny.gov/Economic-Development/Liquor-Authority-Quarterly-List-of-Active-Licenses/hrvs-fxs2/data):\n", "* Choose the `Agency Zone Office Name` as `New York`\n", "* Choose the `County Name` as `Queens`" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import pandas as pd\n", "import cartoframes\n", "import json\n", "import warnings\n", "warnings.filterwarnings(\"ignore\")\n", "from cartoframes import credentials" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "USERNAME = '' # <-- Put your carto username here\n", "APIKEY = '' # <-- Put your carto api key here\n", "\n", "# use cartoframes.credentials.set_creds() to save credentials for future use\n", "cc = cartoframes.CartoContext(api_key=APIKEY,\n", " base_url='https://{}.carto.com/'.format(USERNAME))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reading and exploring the dataset" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "all_licenses = pd.read_csv('Liquor_Authority_Quarterly_List_of_Active_Licenses.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "len(all_licenses)\n", "all_licenses.head()\n", "all_licenses['License Type Name'].unique() #gives us the unique values for the column 'License Type Name'\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Writing that table into CARTO to view the bars\n", "\n", "* Categorizing by type of license names" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "table_name = 'all_licenses'" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "cc.write(table_name,'all_license_type')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from cartoframes import Layer\n", "from cartoframes.styling import vivid\n", "\n", "cc.map(layers=Layer('all_license_type',\n", " color={'column': 'license_type_name', 'scheme': vivid(10, 'category')}),\n", " zoom=12, lng=-73.8322, lat=40.7327,\n", " interactive=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reading the DOHMH location layer" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "office = cc.read('dohmh')\n", "cc.map(layers=Layer('dohmh'),\n", " zoom=15, lng=-73.9393, lat=40.7492,\n", " interactive=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Getting a sense of the table" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "all_licenses.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Create isochrones based on walk-time convenient\n", "\n", "Create a derivative table with geometries as isochrones of walk/drive times from the office location.\n", "\n", "**Note:** This functionality is a planned cartoframes method." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "table_name = 'dohmh'" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "%%time\n", "df = cc.query('''\n", " SELECT \n", " (cdb_isochrone(the_geom, 'walk', Array[900])).the_geom as the_geom,\n", " {keep_columns}\n", " FROM\n", " {table_name}\n", " '''.format(table_name=table_name,\n", " keep_columns=', '.join(set(office.columns) - {'the_geom', 'the_geom_webmercator'})))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "iso_table_name = (table_name + '_isochrones')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There is an issue in the repo already to introduce batch_api queries to avoid timeout:\n", "https://github.com/CartoDB/cartoframes/issues/85\n", "\n", "There are bonus points to find bugs and open issues!" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "cc.write(df, iso_table_name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If this fails because of a lack of credits (i.e., reaching quota), then replace the `(cdb_isochrone(the_geom, 'walk', Array[600])).the_geom` pieces with `ST_Buffer(the_geom::geography, 900)::geometry` for an approximate 15 minute walk ('crow flies' distance)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false, "scrolled": false }, "outputs": [], "source": [ "df.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from cartoframes import BaseMap\n", "cc.map(layers=[BaseMap('light'),\n", " Layer(iso_table_name),\n", " Layer(table_name)],\n", " zoom=12, lng=-73.9668, lat=40.7306,\n", " interactive=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from cartoframes.styling import vivid\n", "cc.map(layers=[Layer(iso_table_name),\n", " Layer('bars_around_office', size=6, color={'column': 'license_type_name', 'scheme': vivid(10)})],\n", " zoom=14, lng=-73.9335, lat=40.7486,\n", " interactive=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "bars_office = cc.query('''\n", " SELECT *\n", " FROM\n", " {table_name}\n", " WHERE license_type_name = 'SUMMER RESTAURANT LIQUOR' OR license_type_name='EATING PLACE BEER' OR license_type_name='O.P. FOOD AND BEV' OR license_type_name='HOTEL WINE' OR license_type_name='SUMMER TAVERN WINE' OR license_type_name='RESTAURANT LIQUOR' OR license_type_name='HOTEL LIQUOR' OR license_type_name='SUMMER EATING PLACE BEER' OR license_type_name='RESTAURANT WINE' OR license_type_name='TAVERN WINE'\n", " '''.format(table_name='all_license_type',\n", " keep_columns=', '.join(set(office.columns) - {'the_geom', 'the_geom_webmercator'})))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "cc.write(bars_office,'bars_around_office')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "final_bars = cc.query('''SELECT a.*, b.the_geom\n", " FROM\n", " {table_name} as a, {table_name2} as b\n", " WHERE ST_Within(a.the_geom,b.the_geom)\n", " '''.format(table_name='all_license_type', table_name2 = iso_table_name))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "cc.write(final_bars,'final_bars')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# show bars with liquor license types\n", "from cartoframes.styling import vivid\n", "cc.map(layers=[Layer(iso_table_name),\n", " Layer('final_bars', size=6, color={'column': 'license_type_name', 'scheme': vivid(10)})],\n", " zoom=14, lng=-73.9360, lat=40.7489,\n", " interactive=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### You can also do things like augment your buffer to find out the demographics of the area" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "points = cc.read('random_points')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "%%time\n", "points_iso = cc.query('''\n", " SELECT \n", " (cdb_isochrone(the_geom, 'walk', Array[600])).the_geom as the_geom,\n", " {keep_columns}\n", " FROM\n", " {table_name}\n", " '''.format(table_name='random_points',\n", " keep_columns=', '.join(set(points.columns) - {'the_geom', 'the_geom_webmercator'})))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "cc.write(points_iso,'points_iso')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Data Observatory measures: median income, male age 30-34 (both ACS)\n", "# Male age 30-34: https://cartodb.github.io/bigmetadata/united_states/age_gender.html#male-age-30-to-34\n", "# Median Income: https://cartodb.github.io/bigmetadata/united_states/income.html#median-household-income-in-the-past-12-months\n", "\n", "# Note: this may take a minute or two because all the measures are being calculated based on the custom geographies\n", "# that are passed in using spatially interpolated calculations (area-weighted measures)\n", "\n", "data_obs_measures = [{'numer_id': 'us.census.acs.B01001012'},\n", " {'numer_id': 'us.census.acs.B19013001'}]\n", "df = cc.data_augment('points_iso', data_obs_measures)\n", "df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As you might have already heard, the Data Observatory just launched to help provide CartoDB users with a universe of data. One of the reasons we built the Data Observatory is because getting the third-party data you need is oftentimes the hardest part of analyzing your own data. Data wrangling shouldn't be such a big roadblock to mapping and analyzing your world.\n", "\n", "https://carto.com/blog/create-location-data-easily" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Visualize isochrones based on Data Observatory measure" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false, "scrolled": false }, "outputs": [], "source": [ "cc.map(layers=Layer('points_iso',\n", " color='median_income_prenormalized_2011_2015'),\n", " zoom=13, lng=-73.9370, lat=40.7522,\n", " interactive=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Builder Dashboard\n", "\n", "https://team.carto.com/u/mehak-carto/builder/c9ad4014-40ba-11e7-9301-0e233c30368f/embed" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false, "scrolled": false }, "outputs": [], "source": [ "from IPython.display import HTML\n", "HTML('<iframe width=\"100%\" height=\"520\" frameborder=\"0\" src=\"https://team.carto.com/u/mehak-carto/builder/dd55a48a-415f-11e7-8098-0ecd1babdde5/embed\" allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe>')" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.6.1" } }, "nbformat": 4, "nbformat_minor": 2 }
mehak-sachdeva
created
this gist