Skip to content

Instantly share code, notes, and snippets.

@mehak-sachdeva

mehak-sachdeva/JC_Penny_Demographics.ipynb

Created July 12, 2017 13:50
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/958d97cb2242bcf001470b45bf5cb602 to your computer and use it in GitHub Desktop.
Save mehak-sachdeva/958d97cb2242bcf001470b45bf5cb602 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
JC_Penny_Demographics.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# JC Penny Store Closings\n",
"\n",
"\n",
"## Workflow\n",
"\n",
"Investigate JC Penny store closings$^1$by:\n",
"\n",
"* Tagging locations as Urban vs Rural (using population density from the Data Observatory)\n",
"* Draw 10 minutes walk or drive isochrones based on whether the location is urban or not\n",
"* Visualize data with cartoframes\n",
"* Augment isochrones with Data Observatory measures\n",
"* Visualize data in Builder and add widgets for specific measures and store properties\n",
"\n",
"Final dashboard: https://team.carto.com/u/eschbacher/builder/0592fcae-3026-11e7-b861-0e3ebc282e83/embed\n",
"\n",
"1. closing status is real, but the actual close date is chosen randomly from the last five years"
]
},
{
"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",
"Download the JC Penny store location data from here:\n",
"* <http://mehak-carto.carto.com/api/v2/sql?q=select%20*%20from%20jc_penny_stores&format=csv&filename=jc_penny_stores>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Workflow for obtaining data\n",
"\n",
"Pull JC Penny locations from my CARTO account into cartoframes"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>the_geom</th>\n",
" <th>the_geom_webmercator</th>\n",
" <th>name</th>\n",
" <th>address</th>\n",
" <th>phone</th>\n",
" <th>state</th>\n",
" <th>state_abb</th>\n",
" <th>status</th>\n",
" <th>distance</th>\n",
" <th>allhomes_zhvi_predenom_2016_08</th>\n",
" <th>closed_date</th>\n",
" <th>total_pop_area_2011_2015</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",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>0101000020E6100000AB798EC8778055C006103E9468FF...</td>\n",
" <td>0101000020110F000021A21442F24262C1CF471EC78ABB...</td>\n",
" <td>JCPenney - Gadsden Mall</td>\n",
" <td>1001 Rainbow Dr</td>\n",
" <td>(256) 680-3168</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>closing</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>2012-10-04T00:00:00Z</td>\n",
" <td>278.799173</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>0101000020E61000000695021C0C7855C0C019468B7D53...</td>\n",
" <td>0101000020110F0000D1198A71CB3B62C1161D4E5D140A...</td>\n",
" <td>JCPenney - Enterprise S/C</td>\n",
" <td>626 Boll Weevil Cir</td>\n",
" <td>(334) 347-0861</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>118900.0</td>\n",
" <td>None</td>\n",
" <td>133.130677</td>\n",
" </tr>\n",
" <tr>\n",
" <th>470</th>\n",
" <td>0101000020E610000092962595045A55C0832B4597DC22...</td>\n",
" <td>0101000020110F00001026B16C4A2262C1977418CF9902...</td>\n",
" <td>JCPenney - Cadillac S/C</td>\n",
" <td>1550 N Mitchell St</td>\n",
" <td>(231) 775-3200</td>\n",
" <td>Michigan</td>\n",
" <td>MI</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>None</td>\n",
" <td>39.951519</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>0101000020E61000003468E89FE0B355C01475E61E1258...</td>\n",
" <td>0101000020110F0000E4EBC3C39B6E62C139F61E854967...</td>\n",
" <td>JCPenney - Regency Sq in Florence</td>\n",
" <td>301 Cox Creek Pkwy (Rt 133)</td>\n",
" <td>(256) 766-8280</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>None</td>\n",
" <td>6.903157</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>0101000020E6100000DA1D520C90B355C0DEE522BE13CD...</td>\n",
" <td>0101000020110F000051C7BF54576E62C15DAF5A64A954...</td>\n",
" <td>JCPenney - Promenade Fultondale</td>\n",
" <td>3363 Lowery Pkwy</td>\n",
" <td>(205) 994-7330</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>125000.0</td>\n",
" <td>None</td>\n",
" <td>444.019729</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" the_geom \\\n",
"cartodb_id \n",
"10 0101000020E6100000AB798EC8778055C006103E9468FF... \n",
"7 0101000020E61000000695021C0C7855C0C019468B7D53... \n",
"470 0101000020E610000092962595045A55C0832B4597DC22... \n",
"8 0101000020E61000003468E89FE0B355C01475E61E1258... \n",
"9 0101000020E6100000DA1D520C90B355C0DEE522BE13CD... \n",
"\n",
" the_geom_webmercator \\\n",
"cartodb_id \n",
"10 0101000020110F000021A21442F24262C1CF471EC78ABB... \n",
"7 0101000020110F0000D1198A71CB3B62C1161D4E5D140A... \n",
"470 0101000020110F00001026B16C4A2262C1977418CF9902... \n",
"8 0101000020110F0000E4EBC3C39B6E62C139F61E854967... \n",
"9 0101000020110F000051C7BF54576E62C15DAF5A64A954... \n",
"\n",
" name address \\\n",
"cartodb_id \n",
"10 JCPenney - Gadsden Mall 1001 Rainbow Dr \n",
"7 JCPenney - Enterprise S/C 626 Boll Weevil Cir \n",
"470 JCPenney - Cadillac S/C 1550 N Mitchell St \n",
"8 JCPenney - Regency Sq in Florence 301 Cox Creek Pkwy (Rt 133) \n",
"9 JCPenney - Promenade Fultondale 3363 Lowery Pkwy \n",
"\n",
" phone state state_abb status distance \\\n",
"cartodb_id \n",
"10 (256) 680-3168 Alabama AL closing 24140.0 \n",
"7 (334) 347-0861 Alabama AL retaining 24140.0 \n",
"470 (231) 775-3200 Michigan MI retaining 24140.0 \n",
"8 (256) 766-8280 Alabama AL retaining 24140.0 \n",
"9 (205) 994-7330 Alabama AL retaining 24140.0 \n",
"\n",
" allhomes_zhvi_predenom_2016_08 closed_date \\\n",
"cartodb_id \n",
"10 NaN 2012-10-04T00:00:00Z \n",
"7 118900.0 None \n",
"470 NaN None \n",
"8 NaN None \n",
"9 125000.0 None \n",
"\n",
" total_pop_area_2011_2015 \n",
"cartodb_id \n",
"10 278.799173 \n",
"7 133.130677 \n",
"470 39.951519 \n",
"8 6.903157 \n",
"9 444.019729 "
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas as pd\n",
"import cartoframes\n",
"import json\n",
"import warnings\n",
"warnings.filterwarnings(\"ignore\")\n",
"\n",
"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))\n",
"table_name = 'jc_penny_stores'\n",
"\n",
"# load JC Penny locations into DataFrame\n",
"df = cc.read(table_name)\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## JC Penny Store Closings\n",
"\n",
"* Purple = stores closing\n",
"* Orange = stores staying open"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<img src=\"https://mehak-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom0/800/400.png?config=%7B%22basemap_url%22%3A+%22https%3A%2F%2Fcartodb-basemaps-%7Bs%7D.global.ssl.fastly.net%2Fdark_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%2C+%22cartocss_0%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+10%3B+marker-fill%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%2810%29%29%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%2810%29%29%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%2810%29%29%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_0%22%3A+%22SELECT+%2A+FROM+jc_penny_stores%22%7D&anti_cache=0.5106315186813657\" />"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from cartoframes import Layer\n",
"from cartoframes.styling import vivid\n",
"\n",
"cc.map(layers=Layer(table_name,\n",
" color={'column': 'status', 'scheme': vivid(10, 'category')}),\n",
" interactive=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Augment with DO to get 'urban-ness' metric (population density)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>the_geom</th>\n",
" <th>the_geom_webmercator</th>\n",
" <th>name</th>\n",
" <th>address</th>\n",
" <th>phone</th>\n",
" <th>state</th>\n",
" <th>state_abb</th>\n",
" <th>status</th>\n",
" <th>distance</th>\n",
" <th>allhomes_zhvi_predenom_2016_08</th>\n",
" <th>closed_date</th>\n",
" <th>total_pop_area_2011_2015</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",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>0101000020E6100000F1A0D9756FAA55C0A08D5C37A55E...</td>\n",
" <td>0101000020110F0000CD11C7DD966662C1FF2DDB8C867F...</td>\n",
" <td>JCPenney - Madison Sq in Huntsville</td>\n",
" <td>5901 University Dr</td>\n",
" <td>(256) 837-2011</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>200200.0</td>\n",
" <td>None</td>\n",
" <td>161.629968</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>0101000020E61000003468E89FE0B355C01475E61E1258...</td>\n",
" <td>0101000020110F0000E4EBC3C39B6E62C139F61E854967...</td>\n",
" <td>JCPenney - Jasper Mall</td>\n",
" <td>300 Hwy 78 E</td>\n",
" <td>(205) 221-5630</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>closing</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>2014-04-08T00:00:00Z</td>\n",
" <td>6.903157</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>0101000020E61000003468E89FE0B355C01475E61E1258...</td>\n",
" <td>0101000020110F0000E4EBC3C39B6E62C139F61E854967...</td>\n",
" <td>JCPenney - Bel Air Mall in Mobile</td>\n",
" <td>3400 Bell Air Mall</td>\n",
" <td>(251) 476-4560</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>None</td>\n",
" <td>6.903157</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>0101000020E610000060E7A6CD388D55C001A5A1462131...</td>\n",
" <td>0101000020110F00007634953DC74D62C17AB97012D418...</td>\n",
" <td>JCPenney - Eastdale Mall in Montgomery</td>\n",
" <td>1236 Eastdale Mall</td>\n",
" <td>(334) 272-2933</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>167300.0</td>\n",
" <td>None</td>\n",
" <td>454.955132</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>0101000020E61000009964E42CEC7455C036902E36ADCE...</td>\n",
" <td>0101000020110F0000814C2C0F243962C17B2AF483EC57...</td>\n",
" <td>JCPenney - Quintard Mall in Oxford</td>\n",
" <td>700 Quintard Dr Ste 100</td>\n",
" <td>(256) 831-2144</td>\n",
" <td>Alabama</td>\n",
" <td>AL</td>\n",
" <td>retaining</td>\n",
" <td>24140.0</td>\n",
" <td>NaN</td>\n",
" <td>None</td>\n",
" <td>192.702319</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" the_geom \\\n",
"cartodb_id \n",
"12 0101000020E6100000F1A0D9756FAA55C0A08D5C37A55E... \n",
"13 0101000020E61000003468E89FE0B355C01475E61E1258... \n",
"14 0101000020E61000003468E89FE0B355C01475E61E1258... \n",
"15 0101000020E610000060E7A6CD388D55C001A5A1462131... \n",
"16 0101000020E61000009964E42CEC7455C036902E36ADCE... \n",
"\n",
" the_geom_webmercator \\\n",
"cartodb_id \n",
"12 0101000020110F0000CD11C7DD966662C1FF2DDB8C867F... \n",
"13 0101000020110F0000E4EBC3C39B6E62C139F61E854967... \n",
"14 0101000020110F0000E4EBC3C39B6E62C139F61E854967... \n",
"15 0101000020110F00007634953DC74D62C17AB97012D418... \n",
"16 0101000020110F0000814C2C0F243962C17B2AF483EC57... \n",
"\n",
" name address \\\n",
"cartodb_id \n",
"12 JCPenney - Madison Sq in Huntsville 5901 University Dr \n",
"13 JCPenney - Jasper Mall 300 Hwy 78 E \n",
"14 JCPenney - Bel Air Mall in Mobile 3400 Bell Air Mall \n",
"15 JCPenney - Eastdale Mall in Montgomery 1236 Eastdale Mall \n",
"16 JCPenney - Quintard Mall in Oxford 700 Quintard Dr Ste 100 \n",
"\n",
" phone state state_abb status distance \\\n",
"cartodb_id \n",
"12 (256) 837-2011 Alabama AL retaining 24140.0 \n",
"13 (205) 221-5630 Alabama AL closing 24140.0 \n",
"14 (251) 476-4560 Alabama AL retaining 24140.0 \n",
"15 (334) 272-2933 Alabama AL retaining 24140.0 \n",
"16 (256) 831-2144 Alabama AL retaining 24140.0 \n",
"\n",
" allhomes_zhvi_predenom_2016_08 closed_date \\\n",
"cartodb_id \n",
"12 200200.0 None \n",
"13 NaN 2014-04-08T00:00:00Z \n",
"14 NaN None \n",
"15 167300.0 None \n",
"16 NaN None \n",
"\n",
" total_pop_area_2011_2015 \n",
"cartodb_id \n",
"12 161.629968 \n",
"13 6.903157 \n",
"14 6.903157 \n",
"15 454.955132 \n",
"16 192.702319 "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# get population, other measures within 5 minute walk time\n",
"# More info about this Data Observatory measure here:\n",
"# https://cartodb.github.io/bigmetadata/united_states/age_gender.html#total-population\n",
"df = cc.data_augment(table_name, [{'numer_id': 'us.census.acs.B01003001',\n",
" 'normalization': 'area',\n",
" 'numer_timespan': '2011 - 2015'}])\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get a sense of the range of data"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>distance</th>\n",
" <th>allhomes_zhvi_predenom_2016_08</th>\n",
" <th>total_pop_area_2011_2015</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>count</th>\n",
" <td>1078.0</td>\n",
" <td>7.070000e+02</td>\n",
" <td>1078.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>mean</th>\n",
" <td>24140.0</td>\n",
" <td>2.235372e+05</td>\n",
" <td>696.898505</td>\n",
" </tr>\n",
" <tr>\n",
" <th>std</th>\n",
" <td>0.0</td>\n",
" <td>1.588468e+05</td>\n",
" <td>1724.807367</td>\n",
" </tr>\n",
" <tr>\n",
" <th>min</th>\n",
" <td>24140.0</td>\n",
" <td>4.380000e+04</td>\n",
" <td>0.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25%</th>\n",
" <td>24140.0</td>\n",
" <td>1.302000e+05</td>\n",
" <td>57.923748</td>\n",
" </tr>\n",
" <tr>\n",
" <th>50%</th>\n",
" <td>24140.0</td>\n",
" <td>1.830000e+05</td>\n",
" <td>347.338072</td>\n",
" </tr>\n",
" <tr>\n",
" <th>75%</th>\n",
" <td>24140.0</td>\n",
" <td>2.480500e+05</td>\n",
" <td>814.271287</td>\n",
" </tr>\n",
" <tr>\n",
" <th>max</th>\n",
" <td>24140.0</td>\n",
" <td>1.765800e+06</td>\n",
" <td>22221.862461</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" distance allhomes_zhvi_predenom_2016_08 total_pop_area_2011_2015\n",
"count 1078.0 7.070000e+02 1078.000000\n",
"mean 24140.0 2.235372e+05 696.898505\n",
"std 0.0 1.588468e+05 1724.807367\n",
"min 24140.0 4.380000e+04 0.000000\n",
"25% 24140.0 1.302000e+05 57.923748\n",
"50% 24140.0 1.830000e+05 347.338072\n",
"75% 24140.0 2.480500e+05 814.271287\n",
"max 24140.0 1.765800e+06 22221.862461"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.describe()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create isochrones based on travel inferences\n",
"\n",
"Create a derivative table with geometries as isochrones of walk/drive times from store locations. If pop density is above 5000 people / sq. km., assume it's a walkable area. Otherwise, assume cars are the primary mode of transit.\n",
"\n",
"**Note:** This functionality is a planned cartoframes method."
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 48.8 ms, sys: 10.2 ms, total: 59 ms\n",
"Wall time: 407 ms\n"
]
}
],
"source": [
"%%time\n",
"df = cc.query('''\n",
" SELECT \n",
" CASE WHEN total_pop_area_2011_2015 > 5000\n",
" THEN (ST_Buffer(the_geom::geography,800)::geometry)\n",
" ELSE (ST_Buffer(the_geom::geography,1200)::geometry)\n",
" END as the_geom,\n",
" {keep_columns}\n",
" FROM\n",
" {table_name}\n",
" '''.format(table_name=table_name,\n",
" keep_columns=', '.join(set(df.columns) - {'the_geom', 'the_geom_webmercator'})))"
]
},
{
"cell_type": "code",
"execution_count": 14,
"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": 15,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"The following columns were renamed because of PostgreSQL column normalization requirements: `the_geom_webmercator`, `field_1`\n",
"Table written to CARTO: https://mehak-carto.carto.com/dataset/jc_penny_stores_isochrones\n"
]
}
],
"source": [
"cc.write(df, iso_table_name)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Because of quota and credits issues we have replaced the isochrone distances with crow-flight distances. We could use `(cdb_isochrone(the_geom, 'walk', Array[600])).the_geom` pieces instead of `ST_Buffer(the_geom::geography, 800)::geometry` for an exact 10 minute walk (instead of 'crow flies' distance), and `(cdb_isochrone(the_geom, 'car', Array[600])).the_geom` for a 10 minute drive (assuming 45 mph on average for 10 minutes)."
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false,
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>the_geom</th>\n",
" <th>closed_date</th>\n",
" <th>total_pop_area_2011_2015</th>\n",
" <th>distance</th>\n",
" <th>status</th>\n",
" <th>phone</th>\n",
" <th>allhomes_zhvi_predenom_2016_08</th>\n",
" <th>state_abb</th>\n",
" <th>address</th>\n",
" <th>state</th>\n",
" <th>name</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0103000020E61000000100000021000000FC469FAF98A9...</td>\n",
" <td>None</td>\n",
" <td>161.629968</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(256) 837-2011</td>\n",
" <td>200200.0</td>\n",
" <td>AL</td>\n",
" <td>5901 University Dr</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Madison Sq in Huntsville</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>0103000020E6100000010000002100000023EE3AE50EB3...</td>\n",
" <td>2014-04-08T00:00:00Z</td>\n",
" <td>6.903157</td>\n",
" <td>24140.0</td>\n",
" <td>closing</td>\n",
" <td>(205) 221-5630</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>300 Hwy 78 E</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Jasper Mall</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>0103000020E6100000010000002100000023EE3AE50EB3...</td>\n",
" <td>None</td>\n",
" <td>6.903157</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(251) 476-4560</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>3400 Bell Air Mall</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Bel Air Mall in Mobile</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>0103000020E610000001000000210000006E9E9BCC678C...</td>\n",
" <td>None</td>\n",
" <td>454.955132</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(334) 272-2933</td>\n",
" <td>167300.0</td>\n",
" <td>AL</td>\n",
" <td>1236 Eastdale Mall</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Eastdale Mall in Montgomery</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0103000020E61000000100000021000000A403FC451874...</td>\n",
" <td>None</td>\n",
" <td>192.702319</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(256) 831-2144</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>700 Quintard Dr Ste 100</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Quintard Mall in Oxford</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" the_geom closed_date \\\n",
"0 0103000020E61000000100000021000000FC469FAF98A9... None \n",
"1 0103000020E6100000010000002100000023EE3AE50EB3... 2014-04-08T00:00:00Z \n",
"2 0103000020E6100000010000002100000023EE3AE50EB3... None \n",
"3 0103000020E610000001000000210000006E9E9BCC678C... None \n",
"4 0103000020E61000000100000021000000A403FC451874... None \n",
"\n",
" total_pop_area_2011_2015 distance status phone \\\n",
"0 161.629968 24140.0 retaining (256) 837-2011 \n",
"1 6.903157 24140.0 closing (205) 221-5630 \n",
"2 6.903157 24140.0 retaining (251) 476-4560 \n",
"3 454.955132 24140.0 retaining (334) 272-2933 \n",
"4 192.702319 24140.0 retaining (256) 831-2144 \n",
"\n",
" allhomes_zhvi_predenom_2016_08 state_abb address state \\\n",
"0 200200.0 AL 5901 University Dr Alabama \n",
"1 NaN AL 300 Hwy 78 E Alabama \n",
"2 NaN AL 3400 Bell Air Mall Alabama \n",
"3 167300.0 AL 1236 Eastdale Mall Alabama \n",
"4 NaN AL 700 Quintard Dr Ste 100 Alabama \n",
"\n",
" name \n",
"0 JCPenney - Madison Sq in Huntsville \n",
"1 JCPenney - Jasper Mall \n",
"2 JCPenney - Bel Air Mall in Mobile \n",
"3 JCPenney - Eastdale Mall in Montgomery \n",
"4 JCPenney - Quintard Mall in Oxford "
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<img src=\"https://mehak-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers2_time0_baseid0_labels0_zoom1/800/400.png?config=%7B%22basemap_url%22%3A+%22https%3A%2F%2Fcartodb-basemaps-%7Bs%7D.global.ssl.fastly.net%2Flight_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%2C+%22cartocss_0%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+10%3B+marker-fill%3A+%235D69B1%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23FFF%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+%235D69B1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+%235D69B1%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_0%22%3A+%22SELECT+%2A+FROM+jc_penny_stores_isochrones%22%2C+%22cartocss_1%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+10%3B+marker-fill%3A+%2352BCA3%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23FFF%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+%2352BCA3%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+%2352BCA3%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_1%22%3A+%22SELECT+%2A+FROM+jc_penny_stores%22%7D&anti_cache=0.9291264650826853&zoom=12&lat=40.7272&lon=-73.9201\" />"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"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.9201, lat=40.7272,\n",
" interactive=False)"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<img src=\"https://mehak-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers2_time0_baseid1_labels0_zoom1/800/400.png?config=%7B%22basemap_url%22%3A+%22https%3A%2F%2Fcartodb-basemaps-%7Bs%7D.global.ssl.fastly.net%2Fdark_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%2C+%22cartocss_0%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+10%3B+marker-fill%3A+ramp%28%5Btotal_pop_area_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+ramp%28%5Btotal_pop_area_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+ramp%28%5Btotal_pop_area_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_0%22%3A+%22SELECT+%2A+FROM+jc_penny_stores_isochrones%22%2C+%22cartocss_1%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+6%3B+marker-fill%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%282%29%29%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%282%29%29%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+ramp%28%5Bstatus%5D%2C+cartocolor%28Vivid%29%2C+category%282%29%29%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_1%22%3A+%22SELECT+%2A+FROM+jc_penny_stores%22%7D&anti_cache=0.4399756636020724&zoom=10&lat=40.1327&lon=-75.0655\" />"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# show choropleth of isochrones by pop density\n",
"from cartoframes.styling import vivid\n",
"cc.map(layers=[Layer(iso_table_name,\n",
" color='total_pop_area_2011_2015'),\n",
" Layer(table_name, size=6, color={'column': 'status', 'scheme': vivid(2)})],\n",
" zoom=10, lng=-75.0655, lat=40.1327,\n",
" interactive=False)"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>the_geom</th>\n",
" <th>the_geom_webmercator</th>\n",
" <th>field_1</th>\n",
" <th>closed_date</th>\n",
" <th>total_pop_area_2011_2015</th>\n",
" <th>distance</th>\n",
" <th>status</th>\n",
" <th>phone</th>\n",
" <th>allhomes_zhvi_predenom_2016_08</th>\n",
" <th>state_abb</th>\n",
" <th>address</th>\n",
" <th>state</th>\n",
" <th>name</th>\n",
" <th>male_30_to_34_denominated_2011_2015</th>\n",
" <th>median_income_prenormalized_2011_2015</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",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>0106000020E61000000100000001030000000100000021...</td>\n",
" <td>0106000020110F00000100000001030000000100000021...</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>161.629968</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(256) 837-2011</td>\n",
" <td>200200.0</td>\n",
" <td>AL</td>\n",
" <td>5901 University Dr</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Madison Sq in Huntsville</td>\n",
" <td>0.016243</td>\n",
" <td>34968.257908</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>0106000020E61000000100000001030000000100000021...</td>\n",
" <td>0106000020110F00000100000001030000000100000021...</td>\n",
" <td>1</td>\n",
" <td>2014-04-08T00:00:00Z</td>\n",
" <td>6.903157</td>\n",
" <td>24140.0</td>\n",
" <td>closing</td>\n",
" <td>(205) 221-5630</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>300 Hwy 78 E</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Jasper Mall</td>\n",
" <td>0.038049</td>\n",
" <td>37500.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>0106000020E61000000100000001030000000100000021...</td>\n",
" <td>0106000020110F00000100000001030000000100000021...</td>\n",
" <td>2</td>\n",
" <td>None</td>\n",
" <td>6.903157</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(251) 476-4560</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>3400 Bell Air Mall</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Bel Air Mall in Mobile</td>\n",
" <td>0.038049</td>\n",
" <td>37500.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0106000020E61000000100000001030000000100000021...</td>\n",
" <td>0106000020110F00000100000001030000000100000021...</td>\n",
" <td>3</td>\n",
" <td>None</td>\n",
" <td>454.955132</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(334) 272-2933</td>\n",
" <td>167300.0</td>\n",
" <td>AL</td>\n",
" <td>1236 Eastdale Mall</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Eastdale Mall in Montgomery</td>\n",
" <td>0.037607</td>\n",
" <td>34068.059056</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>0106000020E61000000100000001030000000100000021...</td>\n",
" <td>0106000020110F00000100000001030000000100000021...</td>\n",
" <td>4</td>\n",
" <td>None</td>\n",
" <td>192.702319</td>\n",
" <td>24140.0</td>\n",
" <td>retaining</td>\n",
" <td>(256) 831-2144</td>\n",
" <td>NaN</td>\n",
" <td>AL</td>\n",
" <td>700 Quintard Dr Ste 100</td>\n",
" <td>Alabama</td>\n",
" <td>JCPenney - Quintard Mall in Oxford</td>\n",
" <td>0.046856</td>\n",
" <td>31517.090672</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" the_geom \\\n",
"cartodb_id \n",
"1 0106000020E61000000100000001030000000100000021... \n",
"2 0106000020E61000000100000001030000000100000021... \n",
"3 0106000020E61000000100000001030000000100000021... \n",
"4 0106000020E61000000100000001030000000100000021... \n",
"5 0106000020E61000000100000001030000000100000021... \n",
"\n",
" the_geom_webmercator field_1 \\\n",
"cartodb_id \n",
"1 0106000020110F00000100000001030000000100000021... 0 \n",
"2 0106000020110F00000100000001030000000100000021... 1 \n",
"3 0106000020110F00000100000001030000000100000021... 2 \n",
"4 0106000020110F00000100000001030000000100000021... 3 \n",
"5 0106000020110F00000100000001030000000100000021... 4 \n",
"\n",
" closed_date total_pop_area_2011_2015 distance \\\n",
"cartodb_id \n",
"1 None 161.629968 24140.0 \n",
"2 2014-04-08T00:00:00Z 6.903157 24140.0 \n",
"3 None 6.903157 24140.0 \n",
"4 None 454.955132 24140.0 \n",
"5 None 192.702319 24140.0 \n",
"\n",
" status phone allhomes_zhvi_predenom_2016_08 \\\n",
"cartodb_id \n",
"1 retaining (256) 837-2011 200200.0 \n",
"2 closing (205) 221-5630 NaN \n",
"3 retaining (251) 476-4560 NaN \n",
"4 retaining (334) 272-2933 167300.0 \n",
"5 retaining (256) 831-2144 NaN \n",
"\n",
" state_abb address state \\\n",
"cartodb_id \n",
"1 AL 5901 University Dr Alabama \n",
"2 AL 300 Hwy 78 E Alabama \n",
"3 AL 3400 Bell Air Mall Alabama \n",
"4 AL 1236 Eastdale Mall Alabama \n",
"5 AL 700 Quintard Dr Ste 100 Alabama \n",
"\n",
" name \\\n",
"cartodb_id \n",
"1 JCPenney - Madison Sq in Huntsville \n",
"2 JCPenney - Jasper Mall \n",
"3 JCPenney - Bel Air Mall in Mobile \n",
"4 JCPenney - Eastdale Mall in Montgomery \n",
"5 JCPenney - Quintard Mall in Oxford \n",
"\n",
" male_30_to_34_denominated_2011_2015 \\\n",
"cartodb_id \n",
"1 0.016243 \n",
"2 0.038049 \n",
"3 0.038049 \n",
"4 0.037607 \n",
"5 0.046856 \n",
"\n",
" median_income_prenormalized_2011_2015 \n",
"cartodb_id \n",
"1 34968.257908 \n",
"2 37500.000000 \n",
"3 37500.000000 \n",
"4 34068.059056 \n",
"5 31517.090672 "
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"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(table_name + '_isochrones', 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": 28,
"metadata": {
"collapsed": false,
"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></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;user_name&quot;: &quot;mehak-carto&quot;, &quot;maps_api_template&quot;: &quot;https://mehak-carto.carto.com&quot;, &quot;sql_api_template&quot;: &quot;https://mehak-carto.carto.com&quot;, &quot;tiler_protocol&quot;: &quot;https&quot;, &quot;tiler_domain&quot;: &quot;carto.com&quot;, &quot;tiler_port&quot;: &quot;80&quot;, &quot;type&quot;: &quot;namedmap&quot;, &quot;named_map&quot;: {&quot;name&quot;: &quot;cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom1&quot;, &quot;params&quot;: {&quot;basemap_url&quot;: &quot;https://cartodb-basemaps-{s}.global.ssl.fastly.net/dark_all/{z}/{x}/{y}.png&quot;, &quot;cartocss_0&quot;: &quot;#layer[&#92;'mapnik::geometry_type&#92;'=1] { marker-width: 10; marker-fill: ramp([median_income_prenormalized_2011_2015], cartocolor(Mint), quantiles(5)); marker-fill-opacity: 1; marker-allow-overlap: true; marker-line-width: 0.5; marker-line-color: #000; marker-line-opacity: 1;} #layer[&#92;'mapnik::geometry_type&#92;'=2] { line-width: 1.5; line-color: ramp([median_income_prenormalized_2011_2015], cartocolor(Mint), quantiles(5));} #layer[&#92;'mapnik::geometry_type&#92;'=3] { polygon-fill: ramp([median_income_prenormalized_2011_2015], cartocolor(Mint), quantiles(5)); polygon-opacity: 0.9; polygon-gamma: 0.5; line-color: #FFF; line-width: 0.5; line-opacity: 0.25; line-comp-op: hard-light;} &quot;, &quot;sql_0&quot;: &quot;SELECT * FROM jc_penny_stores_isochrones&quot;, &quot;zoom&quot;: 10, &quot;lat&quot;: 40.7275, &quot;lng&quot;: -73.6963}}};\n",
" const bounds = [];\n",
" const options = {&quot;filter&quot;: [&quot;http&quot;, &quot;mapnik&quot;, &quot;torque&quot;], &quot;https&quot;: true};\n",
"\n",
" const adjustLongitude = (lng) => (\n",
" lng - ((Math.ceil((lng + 180) / 360) - 1) * 360)\n",
" );\n",
" const map = L.map('map', {\n",
" zoom: 10,\n",
" center: [40.7275, -73.6963],\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",
" if (bounds.length) {\n",
" map.fitBounds(bounds);\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://mehak-carto.carto.com/api/v1/map/static/named/cartoframes_ver20170406_layers1_time0_baseid1_labels0_zoom1/800/400.png?config=%7B%22basemap_url%22%3A+%22https%3A%2F%2Fcartodb-basemaps-%7Bs%7D.global.ssl.fastly.net%2Fdark_all%2F%7Bz%7D%2F%7Bx%7D%2F%7By%7D.png%22%2C+%22cartocss_0%22%3A+%22%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D1%5D+%7B++marker-width%3A+10%3B+marker-fill%3A+ramp%28%5Bmedian_income_prenormalized_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B+marker-fill-opacity%3A+1%3B+marker-allow-overlap%3A+true%3B+marker-line-width%3A+0.5%3B+marker-line-color%3A+%23000%3B+marker-line-opacity%3A+1%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D2%5D+%7B++line-width%3A+1.5%3B+line-color%3A+ramp%28%5Bmedian_income_prenormalized_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B%7D+%23layer%5B%27mapnik%3A%3Ageometry_type%27%3D3%5D+%7B++polygon-fill%3A+ramp%28%5Bmedian_income_prenormalized_2011_2015%5D%2C+cartocolor%28Mint%29%2C+quantiles%285%29%29%3B+polygon-opacity%3A+0.9%3B+polygon-gamma%3A+0.5%3B+line-color%3A+%23FFF%3B+line-width%3A+0.5%3B+line-opacity%3A+0.25%3B+line-comp-op%3A+hard-light%3B%7D+%22%2C+%22sql_0%22%3A+%22SELECT+%2A+FROM+jc_penny_stores_isochrones%22%7D&anti_cache=0.5612074230678372&zoom=10&lat=40.7275&lon=-73.6963\" /></iframe>"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cc.map(layers=Layer(iso_table_name,\n",
" color='median_income_prenormalized_2011_2015'),\n",
" zoom=10, lng=-73.6963, lat=40.7275,\n",
" interactive=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Builder Dashboard\n",
"\n",
"https://team.carto.com/u/eschbacher/builder/0592fcae-3026-11e7-b861-0e3ebc282e83/embed"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {
"collapsed": false,
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"<iframe width=\"100%\" height=\"520\" frameborder=\"0\" src=\"https://team.carto.com/u/eschbacher/builder/0592fcae-3026-11e7-b861-0e3ebc282e83/embed\" allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe>"
],
"text/plain": [
"<IPython.core.display.HTML object>"
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from IPython.display import HTML\n",
"HTML('<iframe width=\"100%\" height=\"520\" frameborder=\"0\" src=\"https://team.carto.com/u/eschbacher/builder/0592fcae-3026-11e7-b861-0e3ebc282e83/embed\" allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe>')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"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