Skip to content

Instantly share code, notes, and snippets.

@mehak-sachdeva

mehak-sachdeva/Cartoframes - DOHMH.ipynb

Created May 25, 2017 16:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mehak-sachdeva/7ed8dc6b96a08e9a9be755665a4ca140 to your computer and use it in GitHub Desktop.
Save mehak-sachdeva/7ed8dc6b96a08e9a9be755665a4ca140 to your computer and use it in GitHub Desktop.
Download ZIP
DOHMH Demo
Display the source blob
Display the rendered blob
Raw
Cartoframes - DOHMH.ipynb
{
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment