{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "g0pFW7zACCPt" }, "source": [ "## Building a dashboard to plan a marketing campaign leveraging CARTO Data Observatory\n", "\n", "Combining different data sources to identify some patterns or understand some behavior in a specific location is a very typical use case in Spatial Data Science.\n", "\n", "In this notebook, we will build a dashboard combining different data from CARTO's Data Observatory to help identify the locations with specific characteristics described below.\n", "\n", "**Note:** This use case leverages premium datasets from [CARTO's Data Observatory](https://carto.com/spatial-data-catalog/).\n", "\n", "\n", "### Use case description\n", "\n", "A pharmaceutical lab wants to launch a new marketing campaign to promote a new line of personal care products for senior people in the city of Philadelphia, PA. They know their target group is characterized by:\n", " - People over 60\n", " - Medium-high to high income\n", " - High expenditure in personal care products and services\n", "\n", "\n", "Given these characteristics, they would like to know which pharmacies and drug stores in the city of Philadelphia they should focus their efforts on.\n", "\n", "In order to identify the target drug stores and pharmacies, we will follow the following steps:\n", " - [Get all pharmacies in Philadelphia](#section1)\n", " - [Calculate their cathment areas using isochrones](#section2)\n", " - [Enrich the isochrones with demographic, POI's, and consumption data](#section3)\n", " - [Build the dashboard to help identify the pharmacies where the campaign can be more successful given the characteristics of the population within their catchment area](#section4)\n", " \n" ] }, { "cell_type": "markdown", "metadata": { "id": "UtO6XoGqCCPu" }, "source": [ "### 0. Setup" ] }, { "cell_type": "markdown", "metadata": { "id": "aGwjaIPPpnMU" }, "source": [ "Import the packages we'll use." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "AgYjVJHmCCPv" }, "outputs": [], "source": [ "import geopandas as gpd\n", "import pandas as pd\n", "\n", "from cartoframes.auth import set_default_credentials\n", "from cartoframes.data.services import Isolines\n", "from cartoframes.data.observatory import *\n", "from cartoframes.viz import *\n", "from shapely.geometry import box\n", "\n", "pd.set_option('display.max_columns', None)" ] }, { "cell_type": "markdown", "metadata": { "id": "7JpDF3DKpsfT" }, "source": [ "In order to be able to use the Data Observatory via CARTOframes, you need to set your CARTO account credentials first.\n", "\n", "Please, visit the [Authentication guide](https://carto.com/developers/cartoframes/guides/Authentication/) for further detail." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "from cartoframes.auth import set_default_credentials\n", "\n", "set_default_credentials('creds.json')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Note about credentials**\n", "\n", "For security reasons, we recommend storing your credentials in an external file to prevent publishing them by accident when sharing your notebooks. You can get more information in the section _Setting your credentials_ of the [Authentication guide](https://carto.com/developers/cartoframes/guides/Authentication/)." ] }, { "cell_type": "markdown", "metadata": { "id": "gBl3tk8Cpw0B" }, "source": [ "\n", "### 1. Download all pharmacies in Philadelphia from the Data Observatory" ] }, { "cell_type": "markdown", "metadata": { "id": "NSbXgVDEp5_h" }, "source": [ "Below is the bounding box of the area of study." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "id": "B9FHBLrbCCP5" }, "outputs": [], "source": [ "dem_bbox = box(-75.229353,39.885501,-75.061124,39.997898)" ] }, { "cell_type": "markdown", "metadata": { "id": "Ymtjq8sgqCja" }, "source": [ "We can get the pharmacies from [Pitney Bowes' Consumer Points of Interest](https://carto.com/spatial-data-catalog/browser/dataset/pb_consumer_po_62cddc04/) dataset. This is a premium dataset, so we first need to check that we are subscribed to it.\n", "\n", "Take a look at this template for more details on how to access and download a premium dataset." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 72 }, "id": "mRXdJjZMVmtu", "outputId": "5cf2ebe8-3ad0-4038-a0ec-a832d5bed7fb" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
slugnamedescriptioncategory_idcountry_iddata_source_idprovider_idgeography_namegeography_descriptiontemporal_aggregationtime_coverageupdate_frequencyis_public_datalangversioncategory_nameprovider_namegeography_idid
0ags_sociodemogr_a7e14220Sociodemographics - United States of America (...Census and ACS sociodemographic data estimated...demographicsusasociodemographicsagsCensus Block Group - United States of AmericaNoneyearlyNoneyearlyFalseeng2020DemographicsApplied Geographic Solutionscarto-do.ags.geography_usa_blockgroup_2015carto-do.ags.demographics_sociodemographics_us...
1ags_retailpoten_aaf25a8cRetail Potential - United States of America (C...The retail potential database consists of aver...demographicsusaretailpotentialagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2018-01-01, 2019-01-01)yearlyFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_retailpotential_usa_...
2pb_consumer_po_62cddc04Points Of Interest - Consumer - United States ...Consumer Point of interest database per catego...points_of_interestusaconsumer_points_of_interestpitney_bowesLatitude/Longitude - United States of AmericaLocation of Points of InterestmonthlyNonemonthlyFalseengv1Points of InterestPitney Bowescarto-do.pitney_bowes.geography_usa_latlon_v1carto-do.pitney_bowes.pointsofinterest_consume...
\n", "
" ], "text/plain": [ " slug \\\n", "0 ags_sociodemogr_a7e14220 \n", "1 ags_retailpoten_aaf25a8c \n", "2 pb_consumer_po_62cddc04 \n", "\n", " name \\\n", "0 Sociodemographics - United States of America (... \n", "1 Retail Potential - United States of America (C... \n", "2 Points Of Interest - Consumer - United States ... \n", "\n", " description category_id \\\n", "0 Census and ACS sociodemographic data estimated... demographics \n", "1 The retail potential database consists of aver... demographics \n", "2 Consumer Point of interest database per catego... points_of_interest \n", "\n", " country_id data_source_id provider_id \\\n", "0 usa sociodemographics ags \n", "1 usa retailpotential ags \n", "2 usa consumer_points_of_interest pitney_bowes \n", "\n", " geography_name \\\n", "0 Census Block Group - United States of America \n", "1 Census Block Group - United States of America ... \n", "2 Latitude/Longitude - United States of America \n", "\n", " geography_description temporal_aggregation \\\n", "0 None yearly \n", "1 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "2 Location of Points of Interest monthly \n", "\n", " time_coverage update_frequency is_public_data lang version \\\n", "0 None yearly False eng 2020 \n", "1 [2018-01-01, 2019-01-01) yearly False eng 2019 \n", "2 None monthly False eng v1 \n", "\n", " category_name provider_name \\\n", "0 Demographics Applied Geographic Solutions \n", "1 Demographics Applied Geographic Solutions \n", "2 Points of Interest Pitney Bowes \n", "\n", " geography_id \\\n", "0 carto-do.ags.geography_usa_blockgroup_2015 \n", "1 carto-do-public-data.carto.geography_usa_block... \n", "2 carto-do.pitney_bowes.geography_usa_latlon_v1 \n", "\n", " id \n", "0 carto-do.ags.demographics_sociodemographics_us... \n", "1 carto-do.ags.demographics_retailpotential_usa_... \n", "2 carto-do.pitney_bowes.pointsofinterest_consume... " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().subscriptions().datasets.to_dataframe()" ] }, { "cell_type": "markdown", "metadata": { "id": "uvOsBkncqwCT" }, "source": [ "#### Download and explore sample\n", "\n", "Pitney Bowes POI's are hierarchically classified (levels: trade division, group, class, sub class). \n", "\n", "Since we might not know which level can help us identify all pharmacies, we can start by downloading a sample for a smaller area to explore the dataset. For calculating the bounding box we use [bboxfinder](http://bboxfinder.com).\n", "\n", "We start by selecting our dataset and taking a quick look at its first 10 rows." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "id": "rRCz-jiYVmrg" }, "outputs": [], "source": [ "dataset = Dataset.get('pb_consumer_po_62cddc04')" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 354 }, "id": "1o4TmFUAVmo7", "outputId": "f2e15514-5946-4523-b86e-2015831d7afb" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
HTTPISO3NAMESIC1SIC2SIC8CLASSEMAILGROUPPB_IDSTABBgeoidFAXNUMMICODETEL_NUMdo_dateLATITUDEOPEN_24HPOSTCODEdo_labelAREANAME1AREANAME2AREANAME3AREANAME4BRANDNAMEGEORESULTLONGITUDESUB_CLASSAGENT_CODETRADE_NAMEYEAR_STARTSTATUS_CODEBUSINESS_LINECURRENCY_CODEEMPLOYEE_HEREEXCHANGE_NAMETICKER_SYMBOLEMPLOYEE_COUNTFAMILY_MEMBERSFRANCHISE_NAMEHIERARCHY_CODEPARENT_ADDRESSPARENT_COUNTRYTRADE_DIVISIONADDRESSLASTLINECONFIDENCE_CODEMAINADDRESSLINEPARENT_POSTCODEFORMATTEDADDRESSPARENT_AREANAME1PARENT_AREANAME3SIC8_DESCRIPTIONALT_INDUSTRY_CODELEGAL_STATUS_CODESALES_VOLUME_LOCALCOUNTRY_ACCESS_CODEPARENT_BUSINESS_NAMESUBSIDIARY_INDICATORPARENT_STREET_ADDRESSGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_COUNTRYSALES_VOLUME_US_DOLLARSGLOBAL_ULTIMATE_POSTCODEDOMESTIC_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_INDICATORDOMESTIC_ULTIMATE_POSTCODEDOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_STREET_ADDRESS
0WWW.IDAHO.GOVUSACOAST TO COAST LOCATORS7841None91210402MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2204806297NE2204806297#144.7436111#13.2777778None10050191(208) 334-38472020-08-0133.788524NoneNoneONOPENNSYLVANIADOUGLASORANGENonePIP PRINTINGS5HPNTSCZA-117.876634SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGNone2000NoneNoneNone15NoneNone1501354NoneNoneNoneNoneDIVISION G. - RETAIL TRADEOREM, UT, 84097HIGHNoneNoneGUIDAHOROCKVILLELIFE INSURANCE CARRIERS812930003706388NoneNone0None6000 EXECUTIVE BLVD # 700, ROCKVILLE, MARYLAND...NoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
1WWW.MARKSFUNERALSERVICE.COMUSAFLOYD'S GENERAL STORES7841653191110403MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2041145109GA2041145109#144.6722222#13.3155556None10050191(970) 686-95252020-08-0141.232673NoneNoneCHALAN DAOGOHIOGLYNNOMAHANoneNoneS8HPNTSCZA-84.820387SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGNone1965NoneNoneNone13NoneNone1300007NoneNoneNoneNoneDIVISION G. - RETAIL TRADELYNCHBURG, VA, 24503-3872HIGHNoneNoneGUNoneNoneMORTGAGE BANKERS812930003600000NoneNone0None1101 ELLER DR, FORT LAUDERDALE, FLORIDA, 33316...NoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
2WWW.HEAVENLYDAYS.COMUSALITTLE GENERAL STORE7841None94410402MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2041107895TX2041107895#144.7163889#13.2494444None10050191(240) 699-00342020-08-0144.293525NoneNoneAJAYANCALIFORNIAANGELINASISTERSNoneNoneS8HPNTSCZA-85.848654PSYCHIATRIC HOSPITALSGNone1952NoneNoneNone50NoneNone5001995NoneNoneNoneNoneDIVISION G. - RETAIL TRADEVIRGINIA BEACH, VA, 23464-6500HIGHNone283418534GUNoneNoneLOAN AGENTS8129300034230106NoneNone0NoneNoneNoneNoneNoneNoneNoneNoneNNoneNoneNoneNone711 D ST NW STE 200NoneNone
3WWW.DUSCKAS-TAYLORFUNERALHOME.COMUSAM STREET CARDS & VARIETY7841None97119906MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2041107894AZ2041107894#144.7338889#13.28None10050191(814) 899-76562020-08-0132.660750NoneNoneGUGAGONCALIFORNIAMOHAVEDALLASNoneNoneS8HPNTSCZA-90.095639DATA PROCESSING SCHOOLSGNone2005NoneNoneNone11NoneNone1100999NoneNoneNoneNoneDIVISION G. - RETAIL TRADEBURLINGTON, WI, 53105-2380HIGHNoneNoneGUPENNSYLVANIANoneASSESSMENT ASSOCIATIONS, ACCIDENT AND HEALTH I...812930000508581NoneNone0None222 3RD AVE SE STE 285, CEDAR RAPIDS, IOWA, 52...NoneNoneNoneNoneNoneNoneYNoneNoneNoneNoneNoneNoneNone
4WWW.OREGONCREMATION.COMUSAFAMILY DOLLAR STORE7841None94410402MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2041107893SC2041107893#144.7463889#13.2883333None10050191(503) 235-31042020-08-0142.303732NoneNoneADAIQUECALIFORNIAFLORENCESIDNEYNoneNoneS8HPNTSCZA-88.301804SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGADVANCE AMERICA2012NoneNoneNone20NoneNone2001765NoneNoneNoneUSADIVISION G. - RETAIL TRADEALBION, ME, 04910-6236HIGHNoneNoneGUNoneNonePENSION FUNDS812930000269036NoneNone0NoneNoneUSANoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
5WWW.JOSEPHHBROWN.COMUSAGL VARIETY7841None92110404MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2200200082PR2200200082#-95.5710222#42.3192199None10050191(410) 383-27002020-08-0135.029179NoneNonePLEASANT VALLEYNEW YORKISABELACHICKASHANoneNoneS8HPNTSCZA-93.388506SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGCHECK 'N GO1936NoneNoneNone11NoneNone1100025NoneNoneNoneNoneDIVISION G. - RETAIL TRADEHONOLULU, HI, 96816-2637HIGHNoneNoneIANoneNoneWARRANTY INSURANCE, AUTOMOBILE812930003218834NoneNone0NoneNoneNoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
6WWW.SCARBOROUGHHISTORICALSOCIETY.ORGUSAREDBOX FAMILY DOLLAR STORE7841None94410200MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2209023773AZ2209023773#-91.6108966#40.5474737None10050191(207) 885-99972020-08-0141.908609NoneNoneGHOST HOLLOWWASHINGTONPIMACHICAGONoneCARTRIDGE WORLDS8HP-TSCZA-156.456725SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGACE CASH EXPRESS2011NoneNoneNone18NoneNone1800952NoneNoneNoneNoneDIVISION G. - RETAIL TRADECULVER CITY, CA, 90232-3652HIGHNoneNoneIANoneNoneACCIDENT INSURANCE CARRIERS8129300001179493NoneNone0None1050 THMAS JFFERSON ST NW, WASHINGTON, DISTRIC...NoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
7WWW.PHILALANDMARKS.ORGUSATOP DOLLAR STORE7841651391999901MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2197355937WA2197355937#-91.7453042#41.9165678None10050191(215) 925-22512020-08-0140.718822NoneNoneTISSEL HOLLOWCALIFORNIAPIERCEWEST VALLEYNoneNoneS8HPNTSCZA-80.317952SPECIALTY HOSPITALS, EXCEPT PSYCHIATRICGNone1981NoneNoneNone20NoneNone2000002NoneNoneNoneNoneDIVISION G. - RETAIL TRADEBRANDON, FL, 33510-2314HIGHNoneNoneIANoneGALTHEALTH INSURANCE CARRIERS812930003702600NoneNone0NoneNoneNoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
8NoneUSAOLLIE'S BARGIN OUTLET7841None94510402MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2119978561AZ2119978561#-90.9290128#42.2789979None10050191(715) 758-80502020-08-0127.916668NoneNoneWHITEWATER CANYONDISTRICT OF COLUMBIACOCONINOTAMPANoneSIR SPEEDYS8HPNTSCZA-87.657751VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAININGGCHECK 'N GO2003NoneNoneNone30NoneNone3002908NoneNoneNoneNoneDIVISION G. - RETAIL TRADESALEM, UT, 84653-9453HIGHNoneNoneIANoneNonePENSION FUNDS812930003176397NoneNone0None1325 N 10TH ST, SAINT LOUIS, MISSOURI, 6310645...NoneNoneNoneNoneNoneNoneNNoneNoneNoneNoneNoneNoneNone
9NoneUSADOLLAR PLUS AND BEAUTY7841None93110102MISCELLANEOUS APPAREL AND ACCESSORY STORESNoneMOTION PICTURES2200200055TN2200200055#-91.2169511#42.0423068None10050191(239) 643-63752020-08-0128.636325NoneNoneHOGS DEN HOLLOWLOUISIANADAVIDSONMAITLANDNoneNoneS8HPNTSCZA-93.299198VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAININGGADVANCE AMERICA2010NoneNoneNone20NoneNone2000010NoneNoneNoneUSADIVISION G. - RETAIL TRADEALBUQUERQUE, NM, 87114-3809HIGHNoneNoneIANoneNoneLOAN AGENTS8129300031806921NoneNone0NoneNoneUSANoneNoneNoneNoneNoneNNoneNoneNoneNone820 1ST ST NE STE 740NoneNone
\n", "
" ], "text/plain": [ " HTTP ISO3 NAME \\\n", "0 WWW.IDAHO.GOV USA COAST TO COAST LOCATORS \n", "1 WWW.MARKSFUNERALSERVICE.COM USA FLOYD'S GENERAL STORES \n", "2 WWW.HEAVENLYDAYS.COM USA LITTLE GENERAL STORE \n", "3 WWW.DUSCKAS-TAYLORFUNERALHOME.COM USA M STREET CARDS & VARIETY \n", "4 WWW.OREGONCREMATION.COM USA FAMILY DOLLAR STORE \n", "5 WWW.JOSEPHHBROWN.COM USA GL VARIETY \n", "6 WWW.SCARBOROUGHHISTORICALSOCIETY.ORG USA REDBOX FAMILY DOLLAR STORE \n", "7 WWW.PHILALANDMARKS.ORG USA TOP DOLLAR STORE \n", "8 None USA OLLIE'S BARGIN OUTLET \n", "9 None USA DOLLAR PLUS AND BEAUTY \n", "\n", " SIC1 SIC2 SIC8 CLASS EMAIL \\\n", "0 7841 None 91210402 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "1 7841 6531 91110403 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "2 7841 None 94410402 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "3 7841 None 97119906 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "4 7841 None 94410402 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "5 7841 None 92110404 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "6 7841 None 94410200 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "7 7841 6513 91999901 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "8 7841 None 94510402 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "9 7841 None 93110102 MISCELLANEOUS APPAREL AND ACCESSORY STORES None \n", "\n", " GROUP PB_ID STABB geoid \\\n", "0 MOTION PICTURES 2204806297 NE 2204806297#144.7436111#13.2777778 \n", "1 MOTION PICTURES 2041145109 GA 2041145109#144.6722222#13.3155556 \n", "2 MOTION PICTURES 2041107895 TX 2041107895#144.7163889#13.2494444 \n", "3 MOTION PICTURES 2041107894 AZ 2041107894#144.7338889#13.28 \n", "4 MOTION PICTURES 2041107893 SC 2041107893#144.7463889#13.2883333 \n", "5 MOTION PICTURES 2200200082 PR 2200200082#-95.5710222#42.3192199 \n", "6 MOTION PICTURES 2209023773 AZ 2209023773#-91.6108966#40.5474737 \n", "7 MOTION PICTURES 2197355937 WA 2197355937#-91.7453042#41.9165678 \n", "8 MOTION PICTURES 2119978561 AZ 2119978561#-90.9290128#42.2789979 \n", "9 MOTION PICTURES 2200200055 TN 2200200055#-91.2169511#42.0423068 \n", "\n", " FAXNUM MICODE TEL_NUM do_date LATITUDE OPEN_24H POSTCODE \\\n", "0 None 10050191 (208) 334-3847 2020-08-01 33.788524 None None \n", "1 None 10050191 (970) 686-9525 2020-08-01 41.232673 None None \n", "2 None 10050191 (240) 699-0034 2020-08-01 44.293525 None None \n", "3 None 10050191 (814) 899-7656 2020-08-01 32.660750 None None \n", "4 None 10050191 (503) 235-3104 2020-08-01 42.303732 None None \n", "5 None 10050191 (410) 383-2700 2020-08-01 35.029179 None None \n", "6 None 10050191 (207) 885-9997 2020-08-01 41.908609 None None \n", "7 None 10050191 (215) 925-2251 2020-08-01 40.718822 None None \n", "8 None 10050191 (715) 758-8050 2020-08-01 27.916668 None None \n", "9 None 10050191 (239) 643-6375 2020-08-01 28.636325 None None \n", "\n", " do_label AREANAME1 AREANAME2 AREANAME3 AREANAME4 \\\n", "0 ONO PENNSYLVANIA DOUGLAS ORANGE None \n", "1 CHALAN DAOG OHIO GLYNN OMAHA None \n", "2 AJAYAN CALIFORNIA ANGELINA SISTERS None \n", "3 GUGAGON CALIFORNIA MOHAVE DALLAS None \n", "4 ADAIQUE CALIFORNIA FLORENCE SIDNEY None \n", "5 PLEASANT VALLEY NEW YORK ISABELA CHICKASHA None \n", "6 GHOST HOLLOW WASHINGTON PIMA CHICAGO None \n", "7 TISSEL HOLLOW CALIFORNIA PIERCE WEST VALLEY None \n", "8 WHITEWATER CANYON DISTRICT OF COLUMBIA COCONINO TAMPA None \n", "9 HOGS DEN HOLLOW LOUISIANA DAVIDSON MAITLAND None \n", "\n", " BRANDNAME GEORESULT LONGITUDE \\\n", "0 PIP PRINTING S5HPNTSCZA -117.876634 \n", "1 None S8HPNTSCZA -84.820387 \n", "2 None S8HPNTSCZA -85.848654 \n", "3 None S8HPNTSCZA -90.095639 \n", "4 None S8HPNTSCZA -88.301804 \n", "5 None S8HPNTSCZA -93.388506 \n", "6 CARTRIDGE WORLD S8HP-TSCZA -156.456725 \n", "7 None S8HPNTSCZA -80.317952 \n", "8 SIR SPEEDY S8HPNTSCZA -87.657751 \n", "9 None S8HPNTSCZA -93.299198 \n", "\n", " SUB_CLASS AGENT_CODE TRADE_NAME \\\n", "0 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G None \n", "1 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G None \n", "2 PSYCHIATRIC HOSPITALS G None \n", "3 DATA PROCESSING SCHOOLS G None \n", "4 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G ADVANCE AMERICA \n", "5 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G CHECK 'N GO \n", "6 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G ACE CASH EXPRESS \n", "7 SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC G None \n", "8 VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING G CHECK 'N GO \n", "9 VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING G ADVANCE AMERICA \n", "\n", " YEAR_START STATUS_CODE BUSINESS_LINE CURRENCY_CODE EMPLOYEE_HERE \\\n", "0 2000 None None None 15 \n", "1 1965 None None None 13 \n", "2 1952 None None None 50 \n", "3 2005 None None None 11 \n", "4 2012 None None None 20 \n", "5 1936 None None None 11 \n", "6 2011 None None None 18 \n", "7 1981 None None None 20 \n", "8 2003 None None None 30 \n", "9 2010 None None None 20 \n", "\n", " EXCHANGE_NAME TICKER_SYMBOL EMPLOYEE_COUNT FAMILY_MEMBERS FRANCHISE_NAME \\\n", "0 None None 15 01354 None \n", "1 None None 13 00007 None \n", "2 None None 50 01995 None \n", "3 None None 11 00999 None \n", "4 None None 20 01765 None \n", "5 None None 11 00025 None \n", "6 None None 18 00952 None \n", "7 None None 20 00002 None \n", "8 None None 30 02908 None \n", "9 None None 20 00010 None \n", "\n", " HIERARCHY_CODE PARENT_ADDRESS PARENT_COUNTRY TRADE_DIVISION \\\n", "0 None None None DIVISION G. - RETAIL TRADE \n", "1 None None None DIVISION G. - RETAIL TRADE \n", "2 None None None DIVISION G. - RETAIL TRADE \n", "3 None None None DIVISION G. - RETAIL TRADE \n", "4 None None USA DIVISION G. - RETAIL TRADE \n", "5 None None None DIVISION G. - RETAIL TRADE \n", "6 None None None DIVISION G. - RETAIL TRADE \n", "7 None None None DIVISION G. - RETAIL TRADE \n", "8 None None None DIVISION G. - RETAIL TRADE \n", "9 None None USA DIVISION G. - RETAIL TRADE \n", "\n", " ADDRESSLASTLINE CONFIDENCE_CODE MAINADDRESSLINE \\\n", "0 OREM, UT, 84097 HIGH None \n", "1 LYNCHBURG, VA, 24503-3872 HIGH None \n", "2 VIRGINIA BEACH, VA, 23464-6500 HIGH None \n", "3 BURLINGTON, WI, 53105-2380 HIGH None \n", "4 ALBION, ME, 04910-6236 HIGH None \n", "5 HONOLULU, HI, 96816-2637 HIGH None \n", "6 CULVER CITY, CA, 90232-3652 HIGH None \n", "7 BRANDON, FL, 33510-2314 HIGH None \n", "8 SALEM, UT, 84653-9453 HIGH None \n", "9 ALBUQUERQUE, NM, 87114-3809 HIGH None \n", "\n", " PARENT_POSTCODE FORMATTEDADDRESS PARENT_AREANAME1 PARENT_AREANAME3 \\\n", "0 None GU IDAHO ROCKVILLE \n", "1 None GU None None \n", "2 283418534 GU None None \n", "3 None GU PENNSYLVANIA None \n", "4 None GU None None \n", "5 None IA None None \n", "6 None IA None None \n", "7 None IA None GALT \n", "8 None IA None None \n", "9 None IA None None \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE \\\n", "0 LIFE INSURANCE CARRIERS 812930 \n", "1 MORTGAGE BANKERS 812930 \n", "2 LOAN AGENTS 812930 \n", "3 ASSESSMENT ASSOCIATIONS, ACCIDENT AND HEALTH I... 812930 \n", "4 PENSION FUNDS 812930 \n", "5 WARRANTY INSURANCE, AUTOMOBILE 812930 \n", "6 ACCIDENT INSURANCE CARRIERS 812930 \n", "7 HEALTH INSURANCE CARRIERS 812930 \n", "8 PENSION FUNDS 812930 \n", "9 LOAN AGENTS 812930 \n", "\n", " LEGAL_STATUS_CODE SALES_VOLUME_LOCAL COUNTRY_ACCESS_CODE \\\n", "0 003 706388 None \n", "1 003 600000 None \n", "2 003 4230106 None \n", "3 000 508581 None \n", "4 000 269036 None \n", "5 003 218834 None \n", "6 000 1179493 None \n", "7 003 702600 None \n", "8 003 176397 None \n", "9 003 1806921 None \n", "\n", " PARENT_BUSINESS_NAME SUBSIDIARY_INDICATOR PARENT_STREET_ADDRESS \\\n", "0 None 0 None \n", "1 None 0 None \n", "2 None 0 None \n", "3 None 0 None \n", "4 None 0 None \n", "5 None 0 None \n", "6 None 0 None \n", "7 None 0 None \n", "8 None 0 None \n", "9 None 0 None \n", "\n", " GLOBAL_ULTIMATE_ADDRESS GLOBAL_ULTIMATE_COUNTRY \\\n", "0 6000 EXECUTIVE BLVD # 700, ROCKVILLE, MARYLAND... None \n", "1 1101 ELLER DR, FORT LAUDERDALE, FLORIDA, 33316... None \n", "2 None None \n", "3 222 3RD AVE SE STE 285, CEDAR RAPIDS, IOWA, 52... None \n", "4 None USA \n", "5 None None \n", "6 1050 THMAS JFFERSON ST NW, WASHINGTON, DISTRIC... None \n", "7 None None \n", "8 1325 N 10TH ST, SAINT LOUIS, MISSOURI, 6310645... None \n", "9 None USA \n", "\n", " SALES_VOLUME_US_DOLLARS GLOBAL_ULTIMATE_POSTCODE DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 None None None \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "5 None None None \n", "6 None None None \n", "7 None None None \n", "8 None None None \n", "9 None None None \n", "\n", " GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_AREANAME3 \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " GLOBAL_ULTIMATE_INDICATOR DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 N None \n", "1 N None \n", "2 N None \n", "3 Y None \n", "4 N None \n", "5 N None \n", "6 N None \n", "7 N None \n", "8 N None \n", "9 N None \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " GLOBAL_ULTIMATE_BUSINESS_NAME GLOBAL_ULTIMATE_STREET_ADDRESS \\\n", "0 None None \n", "1 None None \n", "2 None 711 D ST NW STE 200 \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None 820 1ST ST NE STE 740 \n", "\n", " DOMESTIC_ULTIMATE_BUSINESS_NAME DOMESTIC_ULTIMATE_STREET_ADDRESS \n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dataset.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's now download a small sample to help us identify which of the four hierarchy variables gives us the pharmacies." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "id": "0sMcmlbEVmmN" }, "outputs": [], "source": [ "sql_query = \"SELECT * except(do_label) FROM $dataset$ WHERE ST_IntersectsBox(geom, -75.161723,39.962019,-75.149535,39.968071)\"\n", "sample = dataset.to_dataframe(sql_query=sql_query)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 417 }, "id": "7LDBunMhVmjQ", "outputId": "d989406d-49c0-478c-99b6-70fac54eae67" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
geoiddo_dateNAMEBRANDNAMEPB_IDTRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASSEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeom
01116489911#-75.157314#39.9628572019-11-01DRINKER DOWNS, INC.NaN1116489911NaNNaNUSANaNPHILADELPHIANaNPENNSYLVANIAPA191233332549 N 12TH ST, PHILADELPHIA, PA, 191233332549 N 12TH STPHILADELPHIA, PA, 191233332-75.15731439.962857S8HPNTSCZAHIGH1.0(267) 318-7772NaNNaNWWW.INSTITUTEBAR.COM/NaNDRINKING PLACES, NSK5813.0NaN58130200.0NIGHT CLUBS722410.010130261DIVISION G. - RETAIL TRADEEATING AND DRINKING PLACESDRINKING PLACESDRINKING PLACES/CAFE, PUB4.04.02008.0165700.0165700.020.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.15731 39.96286)
12019661767#-75.160361#39.965872019-10-01DIVING BELL SCUBA SHOPNaN2019661767NaNNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19123681 N BROAD ST, PHILADELPHIA, PA, 19123681 N BROAD STPHILADELPHIA, PA, 19123-75.16036139.965870S8HPNTSCZAHIGHNaN(215) 763-6868NaNHOWARD_PRUYN@DIVINGBELL.COMNaNNaNNaNNaNNaNNaNWATER SPORTNaN10120300DIVISION M. - SPORTSSPORTSWATER SPORTWATER SPORTNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.16036 39.96587)
21116489911#-75.157314#39.9628572019-09-01DRINKER DOWNS, INC.NaN1116489911NaNNaNUSANaNPHILADELPHIANaNPENNSYLVANIAPA191233332549 N 12TH ST, PHILADELPHIA, PA 191233332549 N 12TH STPHILADELPHIA, PA 191233332-75.15731439.962857S8HPNTSCZAHIGH1.0(267) 318-7772NaNNaNWWW.INSTITUTEBAR.COM/NaNDRINKING PLACES, NSK5813.0NaN58130200.0NIGHT CLUBS722410.010130261DIVISION G. - RETAIL TRADEEATING AND DRINKING PLACESDRINKING PLACESDRINKING PLACES/CAFE, PUB4.04.02008.0165700.0165700.020.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.15731 39.96286)
31249192804#-75.157314#39.9628572019-11-01THE INSTITUTE BARNaN1249192804NaNNaNUSANaNPHILADELPHIANaNPENNSYLVANIAPA191233332549 N 12TH ST, PHILADELPHIA, PA, 191233332549 N 12TH STPHILADELPHIA, PA, 191233332-75.15731439.962857S8HPNTSCZAHIGH1.0(215) 787-0888NaNCHARLIE@INSTITUTEBAR.COMWWW.INSTITUTEBAR.COMNaNDRINKING PLACES, NSK5813.0NaN58130105.0TAVERN (DRINKING PLACES)722410.010786105DIVISION G. - RETAIL TRADEEATING AND DRINKING PLACESDRINKING PLACESDRINKING PLACES/CAFE, PUB4.04.02013.0101129.0101129.020.0G0.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.15731 39.96286)
41243566100#-75.15985#39.964912019-09-01LIUNA LOCAL UNION 332NaN1243566100NaNNaNUSANaNPHILADELPHIANaNPENNSYLVANIAPA1912324111310 WALLACE ST, PHILADELPHIA, PA 1912324111310 WALLACE STPHILADELPHIA, PA 191232411-75.15985039.964910S5HPNTSCZAHIGH1.0(215) 765-6272NaNNaNWWW.LDC-PHILA-VIC.ORGNaNLABOR ORGANIZATIONS, NSK8631.0NaN86310000.0LABOR ORGANIZATIONS813930.010248631DIVISION I. - SERVICESMEMBERSHIP ORGANIZATIONSLABOR UNIONS AND SIMILAR LABOR ORGANIZATIONSLABOR ORGANIZATIONS3.03.02010.03033044.03033044.020.0G0.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.15985 39.96491)
\n", "
" ], "text/plain": [ " geoid do_date NAME \\\n", "0 1116489911#-75.157314#39.962857 2019-11-01 DRINKER DOWNS, INC. \n", "1 2019661767#-75.160361#39.96587 2019-10-01 DIVING BELL SCUBA SHOP \n", "2 1116489911#-75.157314#39.962857 2019-09-01 DRINKER DOWNS, INC. \n", "3 1249192804#-75.157314#39.962857 2019-11-01 THE INSTITUTE BAR \n", "4 1243566100#-75.15985#39.96491 2019-09-01 LIUNA LOCAL UNION 332 \n", "\n", " BRANDNAME PB_ID TRADE_NAME FRANCHISE_NAME ISO3 AREANAME4 \\\n", "0 NaN 1116489911 NaN NaN USA NaN \n", "1 NaN 2019661767 NaN NaN USA NaN \n", "2 NaN 1116489911 NaN NaN USA NaN \n", "3 NaN 1249192804 NaN NaN USA NaN \n", "4 NaN 1243566100 NaN NaN USA NaN \n", "\n", " AREANAME3 AREANAME2 AREANAME1 STABB POSTCODE \\\n", "0 PHILADELPHIA NaN PENNSYLVANIA PA 191233332 \n", "1 PHILADELPHIA PHILADELPHIA PENNSYLVANIA PA 19123 \n", "2 PHILADELPHIA NaN PENNSYLVANIA PA 191233332 \n", "3 PHILADELPHIA NaN PENNSYLVANIA PA 191233332 \n", "4 PHILADELPHIA NaN PENNSYLVANIA PA 191232411 \n", "\n", " FORMATTEDADDRESS MAINADDRESSLINE \\\n", "0 549 N 12TH ST, PHILADELPHIA, PA, 191233332 549 N 12TH ST \n", "1 681 N BROAD ST, PHILADELPHIA, PA, 19123 681 N BROAD ST \n", "2 549 N 12TH ST, PHILADELPHIA, PA 191233332 549 N 12TH ST \n", "3 549 N 12TH ST, PHILADELPHIA, PA, 191233332 549 N 12TH ST \n", "4 1310 WALLACE ST, PHILADELPHIA, PA 191232411 1310 WALLACE ST \n", "\n", " ADDRESSLASTLINE LONGITUDE LATITUDE GEORESULT \\\n", "0 PHILADELPHIA, PA, 191233332 -75.157314 39.962857 S8HPNTSCZA \n", "1 PHILADELPHIA, PA, 19123 -75.160361 39.965870 S8HPNTSCZA \n", "2 PHILADELPHIA, PA 191233332 -75.157314 39.962857 S8HPNTSCZA \n", "3 PHILADELPHIA, PA, 191233332 -75.157314 39.962857 S8HPNTSCZA \n", "4 PHILADELPHIA, PA 191232411 -75.159850 39.964910 S5HPNTSCZA \n", "\n", " CONFIDENCE_CODE COUNTRY_ACCESS_CODE TEL_NUM FAXNUM \\\n", "0 HIGH 1.0 (267) 318-7772 NaN \n", "1 HIGH NaN (215) 763-6868 NaN \n", "2 HIGH 1.0 (267) 318-7772 NaN \n", "3 HIGH 1.0 (215) 787-0888 NaN \n", "4 HIGH 1.0 (215) 765-6272 NaN \n", "\n", " EMAIL HTTP OPEN_24H \\\n", "0 NaN WWW.INSTITUTEBAR.COM/ NaN \n", "1 HOWARD_PRUYN@DIVINGBELL.COM NaN NaN \n", "2 NaN WWW.INSTITUTEBAR.COM/ NaN \n", "3 CHARLIE@INSTITUTEBAR.COM WWW.INSTITUTEBAR.COM NaN \n", "4 NaN WWW.LDC-PHILA-VIC.ORG NaN \n", "\n", " BUSINESS_LINE SIC1 SIC2 SIC8 \\\n", "0 DRINKING PLACES, NSK 5813.0 NaN 58130200.0 \n", "1 NaN NaN NaN NaN \n", "2 DRINKING PLACES, NSK 5813.0 NaN 58130200.0 \n", "3 DRINKING PLACES, NSK 5813.0 NaN 58130105.0 \n", "4 LABOR ORGANIZATIONS, NSK 8631.0 NaN 86310000.0 \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE MICODE \\\n", "0 NIGHT CLUBS 722410.0 10130261 \n", "1 WATER SPORT NaN 10120300 \n", "2 NIGHT CLUBS 722410.0 10130261 \n", "3 TAVERN (DRINKING PLACES) 722410.0 10786105 \n", "4 LABOR ORGANIZATIONS 813930.0 10248631 \n", "\n", " TRADE_DIVISION GROUP \\\n", "0 DIVISION G. - RETAIL TRADE EATING AND DRINKING PLACES \n", "1 DIVISION M. - SPORTS SPORTS \n", "2 DIVISION G. - RETAIL TRADE EATING AND DRINKING PLACES \n", "3 DIVISION G. - RETAIL TRADE EATING AND DRINKING PLACES \n", "4 DIVISION I. - SERVICES MEMBERSHIP ORGANIZATIONS \n", "\n", " CLASS SUB_CLASS \\\n", "0 DRINKING PLACES DRINKING PLACES/CAFE, PUB \n", "1 WATER SPORT WATER SPORT \n", "2 DRINKING PLACES DRINKING PLACES/CAFE, PUB \n", "3 DRINKING PLACES DRINKING PLACES/CAFE, PUB \n", "4 LABOR UNIONS AND SIMILAR LABOR ORGANIZATIONS LABOR ORGANIZATIONS \n", "\n", " EMPLOYEE_HERE EMPLOYEE_COUNT YEAR_START SALES_VOLUME_LOCAL \\\n", "0 4.0 4.0 2008.0 165700.0 \n", "1 NaN NaN NaN NaN \n", "2 4.0 4.0 2008.0 165700.0 \n", "3 4.0 4.0 2013.0 101129.0 \n", "4 3.0 3.0 2010.0 3033044.0 \n", "\n", " SALES_VOLUME_US_DOLLARS CURRENCY_CODE AGENT_CODE LEGAL_STATUS_CODE \\\n", "0 165700.0 20.0 G 3.0 \n", "1 NaN NaN NaN NaN \n", "2 165700.0 20.0 G 3.0 \n", "3 101129.0 20.0 G 0.0 \n", "4 3033044.0 20.0 G 0.0 \n", "\n", " STATUS_CODE SUBSIDIARY_INDICATOR PARENT_BUSINESS_NAME PARENT_ADDRESS \\\n", "0 0.0 0.0 NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 0.0 0.0 NaN NaN \n", "3 0.0 0.0 NaN NaN \n", "4 0.0 0.0 NaN NaN \n", "\n", " PARENT_STREET_ADDRESS PARENT_AREANAME3 PARENT_AREANAME1 PARENT_COUNTRY \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " PARENT_POSTCODE DOMESTIC_ULTIMATE_BUSINESS_NAME DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_INDICATOR GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 N NaN \n", "1 NaN NaN \n", "2 N NaN \n", "3 N NaN \n", "4 N NaN \n", "\n", " GLOBAL_ULTIMATE_ADDRESS GLOBAL_ULTIMATE_STREET_ADDRESS \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_AREANAME3 GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " GLOBAL_ULTIMATE_POSTCODE FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL \\\n", "0 NaN 0.0 0.0 NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN 0.0 0.0 NaN \n", "3 NaN 0.0 0.0 NaN \n", "4 NaN 0.0 0.0 NaN \n", "\n", " EXCHANGE_NAME geom \n", "0 NaN POINT (-75.15731 39.96286) \n", "1 NaN POINT (-75.16036 39.96587) \n", "2 NaN POINT (-75.15731 39.96286) \n", "3 NaN POINT (-75.15731 39.96286) \n", "4 NaN POINT (-75.15985 39.96491) " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample.head()" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 141 }, "id": "V0nvkfg6ZKdF", "outputId": "88969105-7029-412a-e38e-75622fc0eb82" }, "outputs": [ { "data": { "text/plain": [ "array(['DIVISION G. - RETAIL TRADE', 'DIVISION M. - SPORTS',\n", " 'DIVISION I. - SERVICES',\n", " 'DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATE',\n", " 'DIVISION C. - CONSTRUCTION',\n", " 'DIVISION J. - PUBLIC ADMINISTRATION',\n", " 'DIVISION E. - TRANSPORTATION AND PUBLIC UTILITIES',\n", " 'DIVISION L. - TOURISM'], dtype=object)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample['TRADE_DIVISION'].unique()" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 123 }, "id": "maJzFw3pZKaX", "outputId": "e3162bc9-b919-4b7d-b25d-c5f1adb9b2b9" }, "outputs": [ { "data": { "text/plain": [ "array(['EATING AND DRINKING PLACES',\n", " 'AUTOMOTIVE DEALERS AND GASOLINE SERVICE STATIONS',\n", " 'MISCELLANEOUS RETAIL',\n", " 'BUILDING MATERIALS, HARDWARE, GARDEN SUPPLIES AND MOBILE HOMES',\n", " 'APPAREL AND ACCESSORY STORES',\n", " 'HOME FURNITURE, FURNISHINGS AND EQUIPMENT STORES', 'FOOD STORES'],\n", " dtype=object)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample.loc[sample['TRADE_DIVISION'] == 'DIVISION G. - RETAIL TRADE', 'GROUP'].unique()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 212 }, "id": "feTf6hYZZKXs", "outputId": "601305d0-3f70-4a16-aceb-f8743257e53b" }, "outputs": [ { "data": { "text/plain": [ "array(['DRINKING PLACES', 'EATING PLACES/RESTAURANTS',\n", " 'AUTO AND HOME SUPPLY STORES', 'GASOLINE SERVICE STATIONS',\n", " 'MOTOR VEHICLE DEALERS (NEW AND USED)',\n", " 'RETAIL STORES, NOT ELSEWHERE CLASSIFIED',\n", " 'PAINT, GLASS, AND WALLPAPER STORES',\n", " 'MISCELLANEOUS APPAREL AND ACCESSORY STORES',\n", " 'FAMILY CLOTHING STORES', 'MISCELLANEOUS SHOPPING GOODS STORES',\n", " 'LUMBER AND OTHER BUILDING MATERIALS DEALERS',\n", " 'HOME FURNITURE AND FURNISHINGS STORES',\n", " 'DRUG STORES AND PROPRIETARY STORES', 'NONSTORE RETAILERS',\n", " 'GROCERY STORES', 'FRUIT AND VEGETABLE MARKETS', 'LIQUOR STORES'],\n", " dtype=object)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample.loc[sample['TRADE_DIVISION'] == 'DIVISION G. - RETAIL TRADE', 'CLASS'].unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The class `DRUG STORES AND PROPRIETARY STORES` is the one we're looking for." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 34 }, "id": "HGNBhEtzZKUp", "outputId": "2841f1bb-0c24-4eb2-fcec-429312ac4157" }, "outputs": [ { "data": { "text/plain": [ "array(['DRUG STORES AND PROPRIETARY STORES/PHARMACY'], dtype=object)" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample.loc[sample['CLASS'] == 'DRUG STORES AND PROPRIETARY STORES', 'SUB_CLASS'].unique()" ] }, { "cell_type": "markdown", "metadata": { "id": "_hefGvb2r6cJ" }, "source": [ "#### Download all pharmacies in the area of study" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "id": "giSmBp0harWJ" }, "outputs": [], "source": [ "sql_query = \"\"\"SELECT * except(do_label) \n", " FROM $dataset$\n", " WHERE CLASS = 'DRUG STORES AND PROPRIETARY STORES'\n", " AND ST_IntersectsBox(geom, -75.229353,39.885501,-75.061124,39.997898)\"\"\"\n", "\n", "ph_pharmacies = dataset.to_dataframe(sql_query=sql_query)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 467 }, "id": "a1B66RyjbotY", "outputId": "44176523-8e36-4111-b5e1-10bf10e96f61" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
geoiddo_dateNAMEBRANDNAMEPB_IDTRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASSEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeom
02128682600#-75.156071#39.9537382019-08-01HERBALIFENaN2128682600NaNNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19107933 ARCH ST, PHILADELPHIA, PA, 19107933 ARCH STPHILADELPHIA, PA, 19107-75.15607139.953738S8HPNTSCZAHIGHNaN(267) 687-8065NaNNaNWWW.HERBALIFE.COMNaNNaN5912NaN59129901DRUG STORESNaN10010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACYNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.15607 39.95374)
12154275062#-75.15871#39.9358682019-08-01HERBALIFENaN2154275062NaNNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA191471116 S 9TH ST, PHILADELPHIA, PA, 191471116 S 9TH STPHILADELPHIA, PA, 19147-75.15871039.935868S7HPNTSCZAHIGHNaN(267) 519-3122NaNNaNWWW.HERBALIFE.COMNaNNaN5912NaN59129901DRUG STORESNaN10010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACYNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.15871 39.93587)
22038322702#-75.063221#39.9465452019-08-01SHAKLEE DISTRIBUTORNaN2038322702NaNNaNUSANaNMERCHANTVILLECAMDENNEW JERSEYNJ08109226 POPLAR AVE, MERCHANTVILLE, NJ, 08109226 POPLAR AVEMERCHANTVILLE, NJ, 08109-75.06322139.946545S7HPNTSCZAHIGHNaN(856) 767-9203NaNNaNWWW.SHAKLEE.COMNaNNaN5912NaN59129901DRUG STORESNaN10010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACYNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.06322 39.94655)
32038337811#-75.063855#39.9492019-08-01RYLA COMMUNITY PHARMACYNaN2038337811NaNNaNUSANaNMERCHANTVILLECAMDENNEW JERSEYNJ081094301 MAPLE AVE, MERCHANTVILLE, NJ, 081094301 MAPLE AVEMERCHANTVILLE, NJ, 08109-75.06385539.949000S8HPNTSCZAHIGHNaN(856) 320-2609NaNNaNNaNNaNNaN5912NaN59120000DRUG STORES AND PROPRIETARY STORESNaN10230030DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACYNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.06386 39.94900)
42017798948#-75.119533#39.9417572019-08-01LINCOLN DRUG CONaN2017798948NaNNaNUSANaNCAMDENCAMDENNEW JERSEYNJ08103221 S BROADWAY, CAMDEN, NJ, 08103221 S BROADWAYCAMDEN, NJ, 08103-75.11953339.941757T16MEDIUMNaN(856) 365-3400NaNNaNNaNNaNNaN5912NaN59120000DRUG STORES AND PROPRIETARY STORESNaN10230030DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACYNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-75.11953 39.94176)
\n", "
" ], "text/plain": [ " geoid do_date NAME \\\n", "0 2128682600#-75.156071#39.953738 2019-08-01 HERBALIFE \n", "1 2154275062#-75.15871#39.935868 2019-08-01 HERBALIFE \n", "2 2038322702#-75.063221#39.946545 2019-08-01 SHAKLEE DISTRIBUTOR \n", "3 2038337811#-75.063855#39.949 2019-08-01 RYLA COMMUNITY PHARMACY \n", "4 2017798948#-75.119533#39.941757 2019-08-01 LINCOLN DRUG CO \n", "\n", " BRANDNAME PB_ID TRADE_NAME FRANCHISE_NAME ISO3 AREANAME4 \\\n", "0 NaN 2128682600 NaN NaN USA NaN \n", "1 NaN 2154275062 NaN NaN USA NaN \n", "2 NaN 2038322702 NaN NaN USA NaN \n", "3 NaN 2038337811 NaN NaN USA NaN \n", "4 NaN 2017798948 NaN NaN USA NaN \n", "\n", " AREANAME3 AREANAME2 AREANAME1 STABB POSTCODE \\\n", "0 PHILADELPHIA PHILADELPHIA PENNSYLVANIA PA 19107 \n", "1 PHILADELPHIA PHILADELPHIA PENNSYLVANIA PA 19147 \n", "2 MERCHANTVILLE CAMDEN NEW JERSEY NJ 08109 \n", "3 MERCHANTVILLE CAMDEN NEW JERSEY NJ 08109 \n", "4 CAMDEN CAMDEN NEW JERSEY NJ 08103 \n", "\n", " FORMATTEDADDRESS MAINADDRESSLINE \\\n", "0 933 ARCH ST, PHILADELPHIA, PA, 19107 933 ARCH ST \n", "1 1116 S 9TH ST, PHILADELPHIA, PA, 19147 1116 S 9TH ST \n", "2 226 POPLAR AVE, MERCHANTVILLE, NJ, 08109 226 POPLAR AVE \n", "3 4301 MAPLE AVE, MERCHANTVILLE, NJ, 08109 4301 MAPLE AVE \n", "4 221 S BROADWAY, CAMDEN, NJ, 08103 221 S BROADWAY \n", "\n", " ADDRESSLASTLINE LONGITUDE LATITUDE GEORESULT CONFIDENCE_CODE \\\n", "0 PHILADELPHIA, PA, 19107 -75.156071 39.953738 S8HPNTSCZA HIGH \n", "1 PHILADELPHIA, PA, 19147 -75.158710 39.935868 S7HPNTSCZA HIGH \n", "2 MERCHANTVILLE, NJ, 08109 -75.063221 39.946545 S7HPNTSCZA HIGH \n", "3 MERCHANTVILLE, NJ, 08109 -75.063855 39.949000 S8HPNTSCZA HIGH \n", "4 CAMDEN, NJ, 08103 -75.119533 39.941757 T16 MEDIUM \n", "\n", " COUNTRY_ACCESS_CODE TEL_NUM FAXNUM EMAIL HTTP \\\n", "0 NaN (267) 687-8065 NaN NaN WWW.HERBALIFE.COM \n", "1 NaN (267) 519-3122 NaN NaN WWW.HERBALIFE.COM \n", "2 NaN (856) 767-9203 NaN NaN WWW.SHAKLEE.COM \n", "3 NaN (856) 320-2609 NaN NaN NaN \n", "4 NaN (856) 365-3400 NaN NaN NaN \n", "\n", " OPEN_24H BUSINESS_LINE SIC1 SIC2 SIC8 \\\n", "0 NaN NaN 5912 NaN 59129901 \n", "1 NaN NaN 5912 NaN 59129901 \n", "2 NaN NaN 5912 NaN 59129901 \n", "3 NaN NaN 5912 NaN 59120000 \n", "4 NaN NaN 5912 NaN 59120000 \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE MICODE \\\n", "0 DRUG STORES NaN 10010700 \n", "1 DRUG STORES NaN 10010700 \n", "2 DRUG STORES NaN 10010700 \n", "3 DRUG STORES AND PROPRIETARY STORES NaN 10230030 \n", "4 DRUG STORES AND PROPRIETARY STORES NaN 10230030 \n", "\n", " TRADE_DIVISION GROUP \\\n", "0 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "1 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "2 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "3 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "4 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "\n", " CLASS \\\n", "0 DRUG STORES AND PROPRIETARY STORES \n", "1 DRUG STORES AND PROPRIETARY STORES \n", "2 DRUG STORES AND PROPRIETARY STORES \n", "3 DRUG STORES AND PROPRIETARY STORES \n", "4 DRUG STORES AND PROPRIETARY STORES \n", "\n", " SUB_CLASS EMPLOYEE_HERE EMPLOYEE_COUNT \\\n", "0 DRUG STORES AND PROPRIETARY STORES/PHARMACY NaN NaN \n", "1 DRUG STORES AND PROPRIETARY STORES/PHARMACY NaN NaN \n", "2 DRUG STORES AND PROPRIETARY STORES/PHARMACY NaN NaN \n", "3 DRUG STORES AND PROPRIETARY STORES/PHARMACY NaN NaN \n", "4 DRUG STORES AND PROPRIETARY STORES/PHARMACY NaN NaN \n", "\n", " YEAR_START SALES_VOLUME_LOCAL SALES_VOLUME_US_DOLLARS CURRENCY_CODE \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " AGENT_CODE LEGAL_STATUS_CODE STATUS_CODE SUBSIDIARY_INDICATOR \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " PARENT_BUSINESS_NAME PARENT_ADDRESS PARENT_STREET_ADDRESS PARENT_AREANAME3 \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " PARENT_AREANAME1 PARENT_COUNTRY PARENT_POSTCODE \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_BUSINESS_NAME DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_INDICATOR GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_ADDRESS GLOBAL_ULTIMATE_STREET_ADDRESS \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_AREANAME3 GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " GLOBAL_ULTIMATE_POSTCODE FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " EXCHANGE_NAME geom \n", "0 NaN POINT (-75.15607 39.95374) \n", "1 NaN POINT (-75.15871 39.93587) \n", "2 NaN POINT (-75.06322 39.94655) \n", "3 NaN POINT (-75.06386 39.94900) \n", "4 NaN POINT (-75.11953 39.94176) " ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ph_pharmacies.head()" ] }, { "cell_type": "markdown", "metadata": { "id": "7k0cIht6sDUR" }, "source": [ "The dataset contains different versions of the POI's tagged by the _do_date_ column. We are only inetrested in the latest version of each POI." ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "id": "rVwNF4cakE1W" }, "outputs": [], "source": [ "ph_pharmacies = ph_pharmacies.sort_values(by='do_date', ascending=False).groupby('PB_ID').first().reset_index()" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 34 }, "id": "qmN37R821xuA", "outputId": "8714967b-d02d-4c4e-c77b-2285992800cb" }, "outputs": [ { "data": { "text/plain": [ "(477, 74)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ph_pharmacies.shape" ] }, { "cell_type": "markdown", "metadata": { "id": "KBCirDMtsYjT" }, "source": [ "#### Visualize the dataset" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "HeZ9LEkibjWM", "outputId": "19b69a5d-ff91-4293-9196-5e0366b98699" }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", " None\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", "\n", "\n", " Static map image\n", " \n", " \n", "
\n", "
\n", "
\n", " \n", " \n", "
\n", "
\n", " \n", "\n", " \n", "\n", "
\n", "
\n", " :\n", "
\n", " \n", " \n", "
\n", "
\n", "\n", "
\n", " StackTrace\n", "
    \n", "
    \n", "
    \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Layer(ph_pharmacies, \n", " geom_col='geom', \n", " style=basic_style(opacity=0.75), \n", " popup_hover=popup_element('NAME'))" ] }, { "cell_type": "markdown", "metadata": { "id": "3SWS9BAusgu6" }, "source": [ "\n", "### 2. Calculate catchment areas" ] }, { "cell_type": "markdown", "metadata": { "id": "QNJV-qqVsofB" }, "source": [ "In order to know the characteristics of the potential customers of every pharmacy, we assume the majority of their clients live closeby. Therefore we will calculate **5-minute-by-car isochrones** and take them as their cathment areas.\n", "\n", "_Note_ catchment areas usually depend on whether it is a store in the downtown area or in the suburbs, or if it is reachable on foot or only by car. For this example, we will not make such distiction between pharmacies, but we strongly encourage you to do so on your analyses. As an example, [here](https://carto.com/blog/calculating-catchment-human-mobility-data/) we describe how to calculate catchment areas using human mobility data." ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 34 }, "id": "tNCwQIxLF3xi", "outputId": "0507ffc2-908e-45c8-8b90-e5806158b676" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Success! Isolines created correctly\n" ] } ], "source": [ "iso_service = Isolines()\n", "isochrones_gdf, _ = iso_service.isochrones(ph_pharmacies, [300], mode='car', geom_col='geom')" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "id": "CX-hmSBCF3vc" }, "outputs": [], "source": [ "ph_pharmacies['iso_5car'] = isochrones_gdf.sort_values(by='source_id')['the_geom'].values" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Visualize isochrones\n", "\n", "We'll only visualize the ten first isochrones to get a clean visualization." ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "xGbYeg0PF3r9", "outputId": "7e265d80-8a06-483e-ae02-55294c7de274" }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", " None\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", "\n", "\n", " Static map image\n", " \n", " \n", "
    \n", "
    \n", "
    \n", " \n", " \n", "
    \n", "
    \n", " \n", "\n", "
    \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", "     \n", "
    \n", "
    \n", "
    \n", " \n", "
    \n", "
    \n", "     \n", "\n", " \n", "\n", "
    \n", "
    \n", " :\n", "
    \n", " \n", " \n", "
    \n", "
    \n", "\n", "
    \n", " StackTrace\n", "
      \n", "
      \n", "
      \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Map([Layer(ph_pharmacies.iloc[:10], \n", " geom_col='iso_5car', \n", " style=basic_style(opacity=0.1),\n", " legends=basic_legend('Catchment Areas')),\n", " Layer(ph_pharmacies.iloc[:10],\n", " geom_col='geom',\n", " popup_hover=popup_element('NAME'),\n", " legends=basic_legend('Pharmacies'))])" ] }, { "cell_type": "markdown", "metadata": { "id": "M5QD7ECiu9H2" }, "source": [ "\n", "### 3. Enrichment: Chacacterize catchment areas\n", "\n", "We'll now enrich the pharmacies catchment areas with demographics, POI's, and consumer spending data.\n", "\n", "For the enrichment, we will use the CARTOframes Enrichment class. This class contains the functionality to enrich polygons and points. \n", "\n", "Visit [CARTOframes Guides](https://carto.com/developers/cartoframes/guides/) for further detail." ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "id": "Az9LQ78AvUUm" }, "outputs": [], "source": [ "enrichment = Enrichment()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Demographics\n", "\n", "We will use AGS premium data. In particular, we will work with the dataset `ags_sociodemogr_f510a947` which contains [yearly demographics data from 2019](https://carto.com/spatial-data-catalog/browser/dataset/ags_sociodemogr_f510a947/)." ] }, { "cell_type": "markdown", "metadata": { "id": "vWtBbn6TvHAK" }, "source": [ "##### Variable selection\n", "\n", "Here we will enrich the pharmacies isochrones with:\n", " - Population aged 60+\n", " - Household income\n", " - Household income for population ages 65+" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 514 }, "id": "uqt0lEOyIvZ2", "outputId": "29124c1a-0dc9-440a-846e-47ade732641f" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "You can find more entities with the Global country filter. To apply that filter run:\n", "\tCatalog().country('glo')\n" ] }, { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      slugnamedescriptioncategory_idcountry_iddata_source_idprovider_idgeography_namegeography_descriptiontemporal_aggregationtime_coverageupdate_frequencyis_public_datalangversioncategory_nameprovider_namegeography_idid
      0ags_consumer_pr_9f337eb8Consumer Profiles - United States of America (...Segmentation of the population in sixty-eight ...demographicsusaconsumer_profilesagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2018-01-01, 2019-01-01)yearlyFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_consumerprofiles_usa...
      1ags_sociodemogr_f510a947Sociodemographics - United States of America (...Census and ACS sociodemographic data estimated...demographicsusasociodemographicsagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2019-01-01, 2020-01-01)yearlyFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_sociodemographics_us...
      2ags_businesscou_df363a87Business Counts - United States of America (Ce...Business Counts database is a geographic summa...demographicsusabusinesscountsagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2018-01-01, 2019-01-01)yearlyFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_businesscounts_usa_b...
      3ags_consumer_sp_895a369cConsumer Spending - United States of America (...The Consumer Expenditure database consists of ...demographicsusaconsumer_spendingagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2018-01-01, 2019-01-01)yearlyFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_consumerspending_usa...
      4ags_crimerisk_bb3e4fb3Crime Risk - United States of America (Census ...Using advanced statistical methodologies and a...demographicsusacrimeriskagsCensus Block Group - United States of AmericaNoneyearlyNoneyearlyFalseeng2020DemographicsApplied Geographic Solutionscarto-do.ags.geography_usa_blockgroup_2015carto-do.ags.demographics_crimerisk_usa_blockg...
      \n", "
      " ], "text/plain": [ " slug \\\n", "0 ags_consumer_pr_9f337eb8 \n", "1 ags_sociodemogr_f510a947 \n", "2 ags_businesscou_df363a87 \n", "3 ags_consumer_sp_895a369c \n", "4 ags_crimerisk_bb3e4fb3 \n", "\n", " name \\\n", "0 Consumer Profiles - United States of America (... \n", "1 Sociodemographics - United States of America (... \n", "2 Business Counts - United States of America (Ce... \n", "3 Consumer Spending - United States of America (... \n", "4 Crime Risk - United States of America (Census ... \n", "\n", " description category_id country_id \\\n", "0 Segmentation of the population in sixty-eight ... demographics usa \n", "1 Census and ACS sociodemographic data estimated... demographics usa \n", "2 Business Counts database is a geographic summa... demographics usa \n", "3 The Consumer Expenditure database consists of ... demographics usa \n", "4 Using advanced statistical methodologies and a... demographics usa \n", "\n", " data_source_id provider_id \\\n", "0 consumer_profiles ags \n", "1 sociodemographics ags \n", "2 businesscounts ags \n", "3 consumer_spending ags \n", "4 crimerisk ags \n", "\n", " geography_name \\\n", "0 Census Block Group - United States of America ... \n", "1 Census Block Group - United States of America ... \n", "2 Census Block Group - United States of America ... \n", "3 Census Block Group - United States of America ... \n", "4 Census Block Group - United States of America \n", "\n", " geography_description temporal_aggregation \\\n", "0 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "1 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "2 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "3 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "4 None yearly \n", "\n", " time_coverage update_frequency is_public_data lang version \\\n", "0 [2018-01-01, 2019-01-01) yearly False eng 2019 \n", "1 [2019-01-01, 2020-01-01) yearly False eng 2019 \n", "2 [2018-01-01, 2019-01-01) yearly False eng 2019 \n", "3 [2018-01-01, 2019-01-01) yearly False eng 2019 \n", "4 None yearly False eng 2020 \n", "\n", " category_name provider_name \\\n", "0 Demographics Applied Geographic Solutions \n", "1 Demographics Applied Geographic Solutions \n", "2 Demographics Applied Geographic Solutions \n", "3 Demographics Applied Geographic Solutions \n", "4 Demographics Applied Geographic Solutions \n", "\n", " geography_id \\\n", "0 carto-do-public-data.carto.geography_usa_block... \n", "1 carto-do-public-data.carto.geography_usa_block... \n", "2 carto-do-public-data.carto.geography_usa_block... \n", "3 carto-do-public-data.carto.geography_usa_block... \n", "4 carto-do.ags.geography_usa_blockgroup_2015 \n", "\n", " id \n", "0 carto-do.ags.demographics_consumerprofiles_usa... \n", "1 carto-do.ags.demographics_sociodemographics_us... \n", "2 carto-do.ags.demographics_businesscounts_usa_b... \n", "3 carto-do.ags.demographics_consumerspending_usa... \n", "4 carto-do.ags.demographics_crimerisk_usa_blockg... " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().country('usa').category('demographics').provider('ags').datasets.to_dataframe().head()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "id": "YBMKA2m2IvW4" }, "outputs": [], "source": [ "dataset = Dataset.get('ags_sociodemogr_f510a947')" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 367 }, "id": "hjUYGb3s4DLJ", "outputId": "d73b3c91-02a3-44ac-8882-daaf750716c5" }, "outputs": [ { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      DWLCYHHDCYPOPCYVPHCY1AGECYMEDHHDCYFAMHOOEXMEDHUSEXAPTLBFCYARMLBFCYLBFLBFCYNLFMARCYSEPPOPCYGRPRNTEXMEDSEXCYFEMSEXCYMALVPHCYGT1AGECY0004AGECY0509AGECY1014AGECY1519AGECY2024AGECY2529AGECY3034AGECY3539AGECY4044AGECY4549AGECY5054AGECY5559AGECY6064AGECY6569AGECY7074AGECY7579AGECY8084AGECYGT15AGECYGT25AGECYGT85DWLCYRENTEDUCYBACHEDUCYGRADEDUCYHSCHHINCY1015HINCY1520HINCY2025HINCY2530HINCY3035HINCY3540HINCY4045HINCY4550HINCY5060HINCY6075HINCYLT10HISCYHISPHUSEX1DETINCCYPCAPLBFCYEMPLLBFCYUNEMLNIEXISOLLNIEXSPANMARCYMARRPOPCYGRPIUNECYRATEVPHCYNONEBLOCKGROUPDWLCYOWNEDDWLCYVACNTEDUCYASSOCEDUCYLTGR9EDUCYSCOLLEDUCYSHSCHHHDCYAVESZHHDCYMEDAGHHSCYLPFCHHHSCYLPMCHHHSCYMCFCHHINCY10025HINCY12550HINCY15020HINCY75100HINCYGT200HINCYMED24HINCYMED25HINCYMED35HINCYMED45HINCYMED55HINCYMED65HINCYMED75INCCYAVEHHINCCYMEDFAINCCYMEDHHLBFCYPOP16MARCYDIVORMARCYNEVERMARCYWIDOWRCHCYAMNHSRCHCYASNHSRCHCYBLNHSRCHCYHANHSRCHCYMUNHSRCHCYOTNHSRCHCYWHNHS
      0556064.0016374900060005150000000001011200066100050000000005002537540000600.0000101598190115001001.261.5000000000006750067500675006750064504674996749960000000006
      1225136.50212499900210003210020000100002000033000000000000000002330262000200.0000101598190212001202.554.01000002000875000875000082566874998749930100030002
      200000.00000000000000000000000000000000000000000000000000000000000.0000101598190310000000.00.000000000000000000000000000000
      3211122464.006749990010101044991350111100103120711020182611811111000222091664710000000.00201097000401151010342.061.5100000000050006000030000500001250033294499992749920413200220000
      400959018.9100003785810959058537400004884303150030000000959412073140000000000036037533463200008.47001097003605100301040.00.0000000000000000000959095905532300250609
      500000.00000000000000000000000000000000000000000000000000000000000.0000209000180010000000.00.000000000000000000000000000000
      600000.00000000000000000000000000000000000000000000000000000000000.0000400300140210000000.00.000000000000000000000000000000
      700000.00000000000000000000000000000000000000000000000000000000000.0000400300140220000000.00.000000000000000000000000000000
      800000.00000000000000000000000000000000000000000000000000000000000.0000401298000010000000.00.000000000000000000000000000000
      900000.00000000000000000000000000000000000000000000000000000000000.0000401398010010000000.00.000000000000000000000000000000
      \n", "
      " ], "text/plain": [ " DWLCY HHDCY POPCY VPHCY1 AGECYMED HHDCYFAM HOOEXMED HUSEXAPT \\\n", "0 5 5 6 0 64.00 1 63749 0 \n", "1 2 2 5 1 36.50 2 124999 0 \n", "2 0 0 0 0 0.00 0 0 0 \n", "3 21 11 22 4 64.00 6 74999 0 \n", "4 0 0 959 0 18.91 0 0 0 \n", "5 0 0 0 0 0.00 0 0 0 \n", "6 0 0 0 0 0.00 0 0 0 \n", "7 0 0 0 0 0.00 0 0 0 \n", "8 0 0 0 0 0.00 0 0 0 \n", "9 0 0 0 0 0.00 0 0 0 \n", "\n", " LBFCYARM LBFCYLBF LBFCYNLF MARCYSEP POPCYGRP RNTEXMED SEXCYFEM \\\n", "0 0 0 6 0 0 0 5 \n", "1 0 2 1 0 0 0 3 \n", "2 0 0 0 0 0 0 0 \n", "3 0 10 10 1 0 449 9 \n", "4 0 378 581 0 959 0 585 \n", "5 0 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 0 \n", "\n", " SEXCYMAL VPHCYGT1 AGECY0004 AGECY0509 AGECY1014 AGECY1519 AGECY2024 \\\n", "0 1 5 0 0 0 0 0 \n", "1 2 1 0 0 2 0 0 \n", "2 0 0 0 0 0 0 0 \n", "3 13 5 0 1 1 1 1 \n", "4 374 0 0 0 0 488 430 \n", "5 0 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 0 \n", "\n", " AGECY2529 AGECY3034 AGECY3539 AGECY4044 AGECY4549 AGECY5054 \\\n", "0 0 0 0 0 1 0 \n", "1 0 0 1 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 0 0 1 0 3 1 \n", "4 31 5 0 0 3 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " AGECY5559 AGECY6064 AGECY6569 AGECY7074 AGECY7579 AGECY8084 \\\n", "0 1 1 2 0 0 0 \n", "1 0 2 0 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 2 0 7 1 1 0 \n", "4 0 0 0 0 0 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " AGECYGT15 AGECYGT25 AGECYGT85 DWLCYRENT EDUCYBACH EDUCYGRAD \\\n", "0 6 6 1 0 0 0 \n", "1 3 3 0 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 20 18 2 6 1 1 \n", "4 959 41 2 0 7 3 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " EDUCYHSCH HINCY1015 HINCY1520 HINCY2025 HINCY2530 HINCY3035 \\\n", "0 5 0 0 0 0 0 \n", "1 0 0 0 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 8 1 1 1 1 1 \n", "4 14 0 0 0 0 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " HINCY3540 HINCY4045 HINCY4550 HINCY5060 HINCY6075 HINCYLT10 \\\n", "0 0 0 0 0 5 0 \n", "1 0 0 0 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 0 0 0 2 2 2 \n", "4 0 0 0 0 0 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " HISCYHISP HUSEX1DET INCCYPCAP LBFCYEMPL LBFCYUNEM LNIEXISOL \\\n", "0 0 2 53754 0 0 0 \n", "1 0 2 33026 2 0 0 \n", "2 0 0 0 0 0 0 \n", "3 0 9 16647 10 0 0 \n", "4 36 0 3753 346 32 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " LNIEXSPAN MARCYMARR POPCYGRPI UNECYRATE VPHCYNONE BLOCKGROUP \\\n", "0 0 6 0 0.00 0 010159819011 \n", "1 0 2 0 0.00 0 010159819021 \n", "2 0 0 0 0.00 0 010159819031 \n", "3 0 0 0 0.00 2 010970004011 \n", "4 0 0 0 8.47 0 010970036051 \n", "5 0 0 0 0.00 0 020900018001 \n", "6 0 0 0 0.00 0 040030014021 \n", "7 0 0 0 0.00 0 040030014022 \n", "8 0 0 0 0.00 0 040129800001 \n", "9 0 0 0 0.00 0 040139801001 \n", "\n", " DWLCYOWNED DWLCYVACNT EDUCYASSOC EDUCYLTGR9 EDUCYSCOLL EDUCYSHSCH \\\n", "0 5 0 0 1 0 0 \n", "1 2 0 0 1 2 0 \n", "2 0 0 0 0 0 0 \n", "3 5 10 1 0 3 4 \n", "4 0 0 3 0 10 4 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " HHDCYAVESZ HHDCYMEDAG HHSCYLPFCH HHSCYLPMCH HHSCYMCFCH HINCY10025 \\\n", "0 1.2 61.5 0 0 0 0 \n", "1 2.5 54.0 1 0 0 0 \n", "2 0.0 0.0 0 0 0 0 \n", "3 2.0 61.5 1 0 0 0 \n", "4 0.0 0.0 0 0 0 0 \n", "5 0.0 0.0 0 0 0 0 \n", "6 0.0 0.0 0 0 0 0 \n", "7 0.0 0.0 0 0 0 0 \n", "8 0.0 0.0 0 0 0 0 \n", "9 0.0 0.0 0 0 0 0 \n", "\n", " HINCY12550 HINCY15020 HINCY75100 HINCYGT200 HINCYMED24 HINCYMED25 \\\n", "0 0 0 0 0 0 0 \n", "1 0 0 2 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 0 0 0 0 0 0 \n", "4 0 0 0 0 0 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " HINCYMED35 HINCYMED45 HINCYMED55 HINCYMED65 HINCYMED75 INCCYAVEHH \\\n", "0 0 67500 67500 67500 67500 64504 \n", "1 87500 0 87500 0 0 82566 \n", "2 0 0 0 0 0 0 \n", "3 5000 60000 30000 50000 12500 33294 \n", "4 0 0 0 0 0 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " INCCYMEDFA INCCYMEDHH LBFCYPOP16 MARCYDIVOR MARCYNEVER MARCYWIDOW \\\n", "0 67499 67499 6 0 0 0 \n", "1 87499 87499 3 0 1 0 \n", "2 0 0 0 0 0 0 \n", "3 49999 27499 20 4 13 2 \n", "4 0 0 959 0 959 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " RCHCYAMNHS RCHCYASNHS RCHCYBLNHS RCHCYHANHS RCHCYMUNHS RCHCYOTNHS \\\n", "0 0 0 0 0 0 0 \n", "1 0 0 3 0 0 0 \n", "2 0 0 0 0 0 0 \n", "3 0 0 22 0 0 0 \n", "4 5 53 230 0 25 0 \n", "5 0 0 0 0 0 0 \n", "6 0 0 0 0 0 0 \n", "7 0 0 0 0 0 0 \n", "8 0 0 0 0 0 0 \n", "9 0 0 0 0 0 0 \n", "\n", " RCHCYWHNHS \n", "0 6 \n", "1 2 \n", "2 0 \n", "3 0 \n", "4 609 \n", "5 0 \n", "6 0 \n", "7 0 \n", "8 0 \n", "9 0 " ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dataset.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We explore the variables to identify the ones we're interested in. \n", "\n", "Variables in a dataset are uniquely identified by their slug." ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "BQ7HvT5L4PSA", "outputId": "b86413c0-a0c4-4e70-ec47-836ca615bd43" }, "outputs": [ { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      slugnamedescriptiondb_typeagg_methodcolumn_namevariable_group_iddataset_idid
      0BLOCKGROUP_108673f9BLOCKGROUPGeographic IdentifierSTRINGNoneBLOCKGROUPNonecarto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...
      1POPCY_5e23b8f4Total PopulationPopulation (2019A)INTEGERSUMPOPCYNonecarto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...
      2POPCYGRP_55c4a2e5POPCYGRPPopulation in Group Quarters (2019A)INTEGERSUMPOPCYGRPNonecarto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...
      3POPCYGRPI_d37c4ecPOPCYGRPIInstitutional Group Quarters Population (2019A)INTEGERSUMPOPCYGRPINonecarto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...
      4AGECY0004_a67ddb4fAGECY0004Population age 0-4 (2019A)INTEGERSUMAGECY0004carto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...carto-do.ags.demographics_sociodemographics_us...
      \n", "
      " ], "text/plain": [ " slug name \\\n", "0 BLOCKGROUP_108673f9 BLOCKGROUP \n", "1 POPCY_5e23b8f4 Total Population \n", "2 POPCYGRP_55c4a2e5 POPCYGRP \n", "3 POPCYGRPI_d37c4ec POPCYGRPI \n", "4 AGECY0004_a67ddb4f AGECY0004 \n", "\n", " description db_type agg_method \\\n", "0 Geographic Identifier STRING None \n", "1 Population (2019A) INTEGER SUM \n", "2 Population in Group Quarters (2019A) INTEGER SUM \n", "3 Institutional Group Quarters Population (2019A) INTEGER SUM \n", "4 Population age 0-4 (2019A) INTEGER SUM \n", "\n", " column_name variable_group_id \\\n", "0 BLOCKGROUP None \n", "1 POPCY None \n", "2 POPCYGRP None \n", "3 POPCYGRPI None \n", "4 AGECY0004 carto-do.ags.demographics_sociodemographics_us... \n", "\n", " dataset_id \\\n", "0 carto-do.ags.demographics_sociodemographics_us... \n", "1 carto-do.ags.demographics_sociodemographics_us... \n", "2 carto-do.ags.demographics_sociodemographics_us... \n", "3 carto-do.ags.demographics_sociodemographics_us... \n", "4 carto-do.ags.demographics_sociodemographics_us... \n", "\n", " id \n", "0 carto-do.ags.demographics_sociodemographics_us... \n", "1 carto-do.ags.demographics_sociodemographics_us... \n", "2 carto-do.ags.demographics_sociodemographics_us... \n", "3 carto-do.ags.demographics_sociodemographics_us... \n", "4 carto-do.ags.demographics_sociodemographics_us... " ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dataset.variables.to_dataframe().head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We'll select:\n", " - Population and population by age variables to identify number of people aged 60+ as a percentage of total population\n", " - Average household income\n", " - Average household income for porpulation aged 65+" ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "id": "tZQQJkh34DX2" }, "outputs": [], "source": [ "vars_enrichment = ['POPCY_5e23b8f4', 'AGECY6064_d54c2315', 'AGECY6569_ad369d43', 'AGECY7074_74eb7531',\n", " 'AGECY7579_c91cb67', 'AGECY8084_ab1079a8', 'AGECYGT85_a0959a08', 'INCCYMEDHH_b80a7a7b',\n", " 'HINCYMED65_37a430a4', 'HINCYMED75_2ebf01e5']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Isochrone enrichment" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "id": "khyZ9U9G4Dla" }, "outputs": [], "source": [ "ph_pharmacies_enriched = enrichment.enrich_polygons(\n", " ph_pharmacies,\n", " variables=vars_enrichment,\n", " geom_col='iso_5car'\n", ")" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 467 }, "id": "eYUGxuLtDfjK", "outputId": "ac957f77-a2b2-4f7c-fde7-a3b1910d472f" }, "outputs": [ { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      PB_IDgeoiddo_dateNAMEBRANDNAMETRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASSEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeomiso_5carPOPCYAGECY8084AGECYGT85AGECY6569AGECY7579AGECY7074AGECY6064HINCYMED65HINCYMED75INCCYMEDHH
      011146893271114689327#-75.186601#39.9183012020-08-01WALGREEN EASTERN CO., INC.WALGREENSWALGREENSNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19145-41222310 W OREGON AVE, PHILADELPHIA, PA, 19145-41222310 W OREGON AVEPHILADELPHIA, PA, 19145-4122-75.18660139.918301S8HPNTSCZAHIGH1.0(215) 468-2481NaNNaNWWW.WALLGREENSBOOTSALLIANCE.COMNaNDRUG STORES AND PROPRIETARY STORES5912NaN59129901DRUG STORES446110.010010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY40.00.00.00.00.0NaNG0.02.00.0WALGREEN EASTERN CO., INC.200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,...200 WILMOT RDDEERFIELDILLINOISUSA600154620.0WALGREENS BOOTS ALLIANCE, INC.108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145108 WILMOT RDDEERFIELDILLINOIS600155145.0NWALGREENS BOOTS ALLIANCE, INC.108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,...108 WILMOT RDDEERFIELDILLINOISUSA600155145.09551.03.0NaNNaNPOINT (-75.18660 39.91830)MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ...15037.340234293.522613310.290651783.930533454.807673599.859593922.26417050981.22727338971.63636455397.409091
      111149424691114942469#-75.160179#39.9704782020-08-01TANG PHARMACY IVNaNTANG PHARMACY IVNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19130-1604900 N BROAD ST, PHILADELPHIA, PA, 19130-1604900 N BROAD STPHILADELPHIA, PA, 19130-1604-75.16017939.970478S8HPNTSCZAHIGH1.0(215) 235-1400NaNNaNNaNNaNDRUG STORES AND PROPRIETARY STORES5912NaN59120000DRUG STORES AND PROPRIETARY STORES446110.010230030DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY4.04.02012.0610000.0610000.020.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.16018 39.97048)MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ...54623.530980813.192570983.7033762391.8635571246.6551191799.5278052877.78271546537.36842137788.38596549895.807018
      211150246091115024609#-75.067035#39.9357052020-08-01AMERICARE 1 PHARMACYNaNNaNNaNUSANaNMERCHANTVILLECAMDENNEW JERSEYNJ08109-48035115 ROUTE 38, MERCHANTVILLE, NJ, 08109-48035115 ROUTE 38MERCHANTVILLE, NJ, 08109-4803-75.06703539.935705S8HPNTSCZAHIGH1.0(215) 821-2720NaNNaNWWW.ACAREPHARMACY.COMNaNDRUG STORES AND PROPRIETARY STORES5912NaN59129901DRUG STORES446110.010010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY5.05.02011.0616316.0616316.020.0G13.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.06704 39.93570)MULTIPOLYGON (((-75.10743 39.94440, -75.10717 ...11454.933682194.094570219.712464510.703182295.695152426.563198636.48608241940.34615434227.65384650875.269231
      311152893801115289380#-75.148596#39.9701772020-08-01GET WELL PHARMACYLEADER DRUG STORELEADER DRUG STORELEADER DRUG STORESUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19123-1313708 W GIRARD AVE, PHILADELPHIA, PA, 19123-1313708 W GIRARD AVEPHILADELPHIA, PA, 19123-1313-75.14859639.970177S8HPNTSCZAHIGH1.0(215) 629-5566(215) 629-5567NaNNaNNaNDRUG STORES AND PROPRIETARY STORES5912NaN59129901DRUG STORES446110.010010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY6.00.00.00.00.0NaNG0.02.00.0GET WELL PHARMACY4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191...4511 N 5TH STPHILADELPHIAPENNSYLVANIAUSA191402309.0GET WELL PHARMACY4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191...4511 N 5TH STPHILADELPHIAPENNSYLVANIA191402309.0NGET WELL PHARMACY4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191...4511 N 5TH STPHILADELPHIAPENNSYLVANIAUSA191402309.02.01.0NaNNaNPOINT (-75.14860 39.97018)MULTIPOLYGON (((-75.17043 39.97375, -75.16983 ...47722.338755754.767397894.0952942154.8511891133.5203121662.0609432561.43392845865.36734736400.42857147234.571429
      411160030621116003062#-75.096649#39.9066292020-08-01CVS PHARMACY, INC.CVSCVSNaNUSANaNHADDON TOWNSHIPCAMDENNEW JERSEYNJ081043002 MOUNT EPHRAIM AVE, HADDON TOWNSHIP, NJ, 0...3002 MOUNT EPHRAIM AVEHADDON TOWNSHIP, NJ, 08104-75.09664939.906629S5HPNTSCZAHIGH1.0(856) 854-9163NaNNaNWWW.CVS.COMNaNDRUG STORES AND PROPRIETARY STORES5912NaN59120000DRUG STORES AND PROPRIETARY STORES446110.010230030DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY25.00.00.00.00.0NaNG0.02.00.0CVS PHARMACY, INC.1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,...1 CVS DRWOONSOCKETRHODE ISLANDUSA28956195.0CVS HEALTH CORPORATION1 CVS DR, WOONSOCKET, RHODE ISLAND, 0289561951 CVS DRWOONSOCKETRHODE ISLAND28956195.0NCVS HEALTH CORPORATION1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,...1 CVS DRWOONSOCKETRHODE ISLANDUSA28956195.013290.02.0NaNNaNPOINT (-75.09665 39.90663)MULTIPOLYGON (((-75.12031 39.90440, -75.11971 ...13244.490216153.927121179.820079533.225761236.796307409.091195686.86169148903.48484840511.36363653548.757576
      \n", "
      " ], "text/plain": [ " PB_ID geoid do_date \\\n", "0 1114689327 1114689327#-75.186601#39.918301 2020-08-01 \n", "1 1114942469 1114942469#-75.160179#39.970478 2020-08-01 \n", "2 1115024609 1115024609#-75.067035#39.935705 2020-08-01 \n", "3 1115289380 1115289380#-75.148596#39.970177 2020-08-01 \n", "4 1116003062 1116003062#-75.096649#39.906629 2020-08-01 \n", "\n", " NAME BRANDNAME TRADE_NAME \\\n", "0 WALGREEN EASTERN CO., INC. WALGREENS WALGREENS \n", "1 TANG PHARMACY IV NaN TANG PHARMACY IV \n", "2 AMERICARE 1 PHARMACY NaN NaN \n", "3 GET WELL PHARMACY LEADER DRUG STORE LEADER DRUG STORE \n", "4 CVS PHARMACY, INC. CVS CVS \n", "\n", " FRANCHISE_NAME ISO3 AREANAME4 AREANAME3 AREANAME2 \\\n", "0 NaN USA NaN PHILADELPHIA PHILADELPHIA \n", "1 NaN USA NaN PHILADELPHIA PHILADELPHIA \n", "2 NaN USA NaN MERCHANTVILLE CAMDEN \n", "3 LEADER DRUG STORES USA NaN PHILADELPHIA PHILADELPHIA \n", "4 NaN USA NaN HADDON TOWNSHIP CAMDEN \n", "\n", " AREANAME1 STABB POSTCODE \\\n", "0 PENNSYLVANIA PA 19145-4122 \n", "1 PENNSYLVANIA PA 19130-1604 \n", "2 NEW JERSEY NJ 08109-4803 \n", "3 PENNSYLVANIA PA 19123-1313 \n", "4 NEW JERSEY NJ 08104 \n", "\n", " FORMATTEDADDRESS MAINADDRESSLINE \\\n", "0 2310 W OREGON AVE, PHILADELPHIA, PA, 19145-4122 2310 W OREGON AVE \n", "1 900 N BROAD ST, PHILADELPHIA, PA, 19130-1604 900 N BROAD ST \n", "2 5115 ROUTE 38, MERCHANTVILLE, NJ, 08109-4803 5115 ROUTE 38 \n", "3 708 W GIRARD AVE, PHILADELPHIA, PA, 19123-1313 708 W GIRARD AVE \n", "4 3002 MOUNT EPHRAIM AVE, HADDON TOWNSHIP, NJ, 0... 3002 MOUNT EPHRAIM AVE \n", "\n", " ADDRESSLASTLINE LONGITUDE LATITUDE GEORESULT \\\n", "0 PHILADELPHIA, PA, 19145-4122 -75.186601 39.918301 S8HPNTSCZA \n", "1 PHILADELPHIA, PA, 19130-1604 -75.160179 39.970478 S8HPNTSCZA \n", "2 MERCHANTVILLE, NJ, 08109-4803 -75.067035 39.935705 S8HPNTSCZA \n", "3 PHILADELPHIA, PA, 19123-1313 -75.148596 39.970177 S8HPNTSCZA \n", "4 HADDON TOWNSHIP, NJ, 08104 -75.096649 39.906629 S5HPNTSCZA \n", "\n", " CONFIDENCE_CODE COUNTRY_ACCESS_CODE TEL_NUM FAXNUM EMAIL \\\n", "0 HIGH 1.0 (215) 468-2481 NaN NaN \n", "1 HIGH 1.0 (215) 235-1400 NaN NaN \n", "2 HIGH 1.0 (215) 821-2720 NaN NaN \n", "3 HIGH 1.0 (215) 629-5566 (215) 629-5567 NaN \n", "4 HIGH 1.0 (856) 854-9163 NaN NaN \n", "\n", " HTTP OPEN_24H \\\n", "0 WWW.WALLGREENSBOOTSALLIANCE.COM NaN \n", "1 NaN NaN \n", "2 WWW.ACAREPHARMACY.COM NaN \n", "3 NaN NaN \n", "4 WWW.CVS.COM NaN \n", "\n", " BUSINESS_LINE SIC1 SIC2 SIC8 \\\n", "0 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59129901 \n", "1 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59120000 \n", "2 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59129901 \n", "3 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59129901 \n", "4 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59120000 \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE MICODE \\\n", "0 DRUG STORES 446110.0 10010700 \n", "1 DRUG STORES AND PROPRIETARY STORES 446110.0 10230030 \n", "2 DRUG STORES 446110.0 10010700 \n", "3 DRUG STORES 446110.0 10010700 \n", "4 DRUG STORES AND PROPRIETARY STORES 446110.0 10230030 \n", "\n", " TRADE_DIVISION GROUP \\\n", "0 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "1 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "2 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "3 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "4 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "\n", " CLASS \\\n", "0 DRUG STORES AND PROPRIETARY STORES \n", "1 DRUG STORES AND PROPRIETARY STORES \n", "2 DRUG STORES AND PROPRIETARY STORES \n", "3 DRUG STORES AND PROPRIETARY STORES \n", "4 DRUG STORES AND PROPRIETARY STORES \n", "\n", " SUB_CLASS EMPLOYEE_HERE EMPLOYEE_COUNT \\\n", "0 DRUG STORES AND PROPRIETARY STORES/PHARMACY 40.0 0.0 \n", "1 DRUG STORES AND PROPRIETARY STORES/PHARMACY 4.0 4.0 \n", "2 DRUG STORES AND PROPRIETARY STORES/PHARMACY 5.0 5.0 \n", "3 DRUG STORES AND PROPRIETARY STORES/PHARMACY 6.0 0.0 \n", "4 DRUG STORES AND PROPRIETARY STORES/PHARMACY 25.0 0.0 \n", "\n", " YEAR_START SALES_VOLUME_LOCAL SALES_VOLUME_US_DOLLARS CURRENCY_CODE \\\n", "0 0.0 0.0 0.0 NaN \n", "1 2012.0 610000.0 610000.0 20.0 \n", "2 2011.0 616316.0 616316.0 20.0 \n", "3 0.0 0.0 0.0 NaN \n", "4 0.0 0.0 0.0 NaN \n", "\n", " AGENT_CODE LEGAL_STATUS_CODE STATUS_CODE SUBSIDIARY_INDICATOR \\\n", "0 G 0.0 2.0 0.0 \n", "1 G 3.0 0.0 0.0 \n", "2 G 13.0 0.0 0.0 \n", "3 G 0.0 2.0 0.0 \n", "4 G 0.0 2.0 0.0 \n", "\n", " PARENT_BUSINESS_NAME \\\n", "0 WALGREEN EASTERN CO., INC. \n", "1 NaN \n", "2 NaN \n", "3 GET WELL PHARMACY \n", "4 CVS PHARMACY, INC. \n", "\n", " PARENT_ADDRESS PARENT_STREET_ADDRESS \\\n", "0 200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,... 200 WILMOT RD \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... 4511 N 5TH ST \n", "4 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,... 1 CVS DR \n", "\n", " PARENT_AREANAME3 PARENT_AREANAME1 PARENT_COUNTRY PARENT_POSTCODE \\\n", "0 DEERFIELD ILLINOIS USA 600154620.0 \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 PHILADELPHIA PENNSYLVANIA USA 191402309.0 \n", "4 WOONSOCKET RHODE ISLAND USA 28956195.0 \n", "\n", " DOMESTIC_ULTIMATE_BUSINESS_NAME \\\n", "0 WALGREENS BOOTS ALLIANCE, INC. \n", "1 NaN \n", "2 NaN \n", "3 GET WELL PHARMACY \n", "4 CVS HEALTH CORPORATION \n", "\n", " DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145 \n", "1 NaN \n", "2 NaN \n", "3 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... \n", "4 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195 \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 108 WILMOT RD DEERFIELD \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 4511 N 5TH ST PHILADELPHIA \n", "4 1 CVS DR WOONSOCKET \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 ILLINOIS 600155145.0 \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 PENNSYLVANIA 191402309.0 \n", "4 RHODE ISLAND 28956195.0 \n", "\n", " GLOBAL_ULTIMATE_INDICATOR GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 N WALGREENS BOOTS ALLIANCE, INC. \n", "1 N NaN \n", "2 N NaN \n", "3 N GET WELL PHARMACY \n", "4 N CVS HEALTH CORPORATION \n", "\n", " GLOBAL_ULTIMATE_ADDRESS \\\n", "0 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,... \n", "1 NaN \n", "2 NaN \n", "3 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... \n", "4 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,... \n", "\n", " GLOBAL_ULTIMATE_STREET_ADDRESS GLOBAL_ULTIMATE_AREANAME3 \\\n", "0 108 WILMOT RD DEERFIELD \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 4511 N 5TH ST PHILADELPHIA \n", "4 1 CVS DR WOONSOCKET \n", "\n", " GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY GLOBAL_ULTIMATE_POSTCODE \\\n", "0 ILLINOIS USA 600155145.0 \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 PENNSYLVANIA USA 191402309.0 \n", "4 RHODE ISLAND USA 28956195.0 \n", "\n", " FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL EXCHANGE_NAME \\\n", "0 9551.0 3.0 NaN NaN \n", "1 0.0 0.0 NaN NaN \n", "2 0.0 0.0 NaN NaN \n", "3 2.0 1.0 NaN NaN \n", "4 13290.0 2.0 NaN NaN \n", "\n", " geom \\\n", "0 POINT (-75.18660 39.91830) \n", "1 POINT (-75.16018 39.97048) \n", "2 POINT (-75.06704 39.93570) \n", "3 POINT (-75.14860 39.97018) \n", "4 POINT (-75.09665 39.90663) \n", "\n", " iso_5car POPCY \\\n", "0 MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ... 15037.340234 \n", "1 MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ... 54623.530980 \n", "2 MULTIPOLYGON (((-75.10743 39.94440, -75.10717 ... 11454.933682 \n", "3 MULTIPOLYGON (((-75.17043 39.97375, -75.16983 ... 47722.338755 \n", "4 MULTIPOLYGON (((-75.12031 39.90440, -75.11971 ... 13244.490216 \n", "\n", " AGECY8084 AGECYGT85 AGECY6569 AGECY7579 AGECY7074 AGECY6064 \\\n", "0 293.522613 310.290651 783.930533 454.807673 599.859593 922.264170 \n", "1 813.192570 983.703376 2391.863557 1246.655119 1799.527805 2877.782715 \n", "2 194.094570 219.712464 510.703182 295.695152 426.563198 636.486082 \n", "3 754.767397 894.095294 2154.851189 1133.520312 1662.060943 2561.433928 \n", "4 153.927121 179.820079 533.225761 236.796307 409.091195 686.861691 \n", "\n", " HINCYMED65 HINCYMED75 INCCYMEDHH \n", "0 50981.227273 38971.636364 55397.409091 \n", "1 46537.368421 37788.385965 49895.807018 \n", "2 41940.346154 34227.653846 50875.269231 \n", "3 45865.367347 36400.428571 47234.571429 \n", "4 48903.484848 40511.363636 53548.757576 " ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ph_pharmacies_enriched.head()" ] }, { "cell_type": "code", "execution_count": 31, "metadata": { "id": "Ywu6fFj7FyXE" }, "outputs": [], "source": [ "ph_pharmacies = ph_pharmacies_enriched.copy()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": { "id": "SZwLsCxWUlUA" }, "outputs": [], "source": [ "ph_pharmacies['pop_60plus'] = ph_pharmacies[['AGECY8084', 'AGECYGT85', 'AGECY6569', 'AGECY7579', 'AGECY7074', 'AGECY6064']].sum(1)\n", "ph_pharmacies.drop(columns=['AGECY8084', 'AGECYGT85', 'AGECY6569', 'AGECY7579', 'AGECY7074', 'AGECY6064'], inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Points of Interest\n", "\n", "We will use [Pitney Bowes' Consumer Points of Interest](https://carto.com/spatial-data-catalog/browser/dataset/pb_consumer_po_62cddc04/) premium dataset." ] }, { "cell_type": "markdown", "metadata": { "id": "wXrgsacjvZ8M" }, "source": [ "##### Variable selection\n", "\n", "We are interested in knowing how many of the following POIs can be found in each isochrone:\n", " - Beauty shops and beauty salons\n", " - Gyms and other sports centers\n", "\n", "These POI's will be considered as an indicator of personal care awareness in a specific area." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The hierarchy classification variable `SUB_CLASS` variable allows us to identify beaty shops and salons (`BEAUTY SHOPS/BEAUTY SALON`) and gyms (`MEMBERSHIP SPORTS AND RECREATION CLUBS/CLUB AND ASSOCIATION - UNSPECIFIED`)." ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 692 }, "id": "bZdJKVsaDssS", "outputId": "8daadc49-49e8-4109-bd28-00bec4e6cbf6" }, "outputs": [ { "data": { "text/plain": [ "array(['LABOR ORGANIZATIONS', 'MEMBERSHIP ORGANIZATIONS, NEC',\n", " 'FLATS, APARTMENT COMPLEX', 'HOTEL, MOTEL - UNSPECIFIED',\n", " 'MEDIA FACILITY', 'COLLEGES AND UNIVERSITIES',\n", " 'VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING', 'LIBRARIES',\n", " 'SCHOOL', 'GENERAL MEDICAL AND SURGICAL HOSPITALS',\n", " 'OFFICES AND CLINICS OF DENTISTS/ DENTIST',\n", " 'CIVIC AND SOCIAL ASSOCIATIONS',\n", " 'OFFICES AND CLINICS OF MEDICAL DOCTORS',\n", " 'RELIGIOUS ORGANIZATIONS',\n", " 'PARK AND RECREATION AREA - UNSPECIFIED',\n", " 'SKILLED NURSING CARE FACILITIES',\n", " 'NURSING AND PERSONAL CARE, NEC', 'RETIREMENT COMMUNITY',\n", " 'SPECIALTY OUTPATIENT CLINICS, NEC',\n", " 'PASSENGER CAR RENTAL/RENT-A-CAR FACILITY',\n", " 'GENERAL AUTOMOTIVE REPAIR SHOPS/GENERAL CAR REPAIR AND SERVICING',\n", " 'LEGAL SERVICES', 'TOP AND BODY REPAIR AND PAINT SHOPS/BODYSHOPS',\n", " 'CARWASHES', 'HEALTH AND ALLIED SERVICES, NEC',\n", " 'SCHOOLS AND EDUCATIONAL SERVICES/SCHOOL - UNSPECIFIED',\n", " 'LAUNDRY AND GARMENT SERVICES, NEC',\n", " 'MISCELLANEOUS PERSONAL SERVICES',\n", " 'PHOTOGRAPHIC STUDIOS, PORTRAIT', 'PRIMARY SCHOOL',\n", " 'INDIVIDUAL AND FAMILY SERVICES', 'RESIDENTIAL CARE',\n", " 'CHILD DAY CARE SERVICES/CHILD CARE CENTER',\n", " 'BARBER SHOPS/HAIRDRESSERS AND BARBERS',\n", " 'BEAUTY SHOPS/BEAUTY SALON', 'FUNERAL SERVICE AND CREMATORIES',\n", " 'OFFICES OF HEALTH PRACTITIONER',\n", " 'ENTERTAINERS AND ENTERTAINMENT GROUPS',\n", " 'AUTO EXHAUST SYSTEM REPAIR SHOPS',\n", " 'THEATRICAL PRODUCERS AND SERVICES',\n", " 'PHYSICAL FITNESS FACILITIES/FITNESS CLUB AND CENTER',\n", " 'AMUSEMENT AND RECREATION, NEC/AMUSEMENT PLACE',\n", " 'AUTOMOBILE PARKING/OPEN PARKING AREA',\n", " 'MUSEUMS AND ART GALLERIES', 'ELEMENTARY AND SECONDARY SCHOOLS',\n", " 'MOTION PICTURE THEATERS, EXCEPT DRIVE-IN',\n", " 'HOME HEALTH CARE SERVICES',\n", " 'OFFICES AND CLINICS OF OSTEOPATHIC PHYSICIANS',\n", " 'SOCIAL SERVICES, NEC', 'DANCE STUDIOS, SCHOOLS, AND HALLS',\n", " 'ENTERTAINMENT'], dtype=object)" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample.loc[sample['TRADE_DIVISION'] == 'DIVISION I. - SERVICES', 'SUB_CLASS'].unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Isochrone enrichment" ] }, { "cell_type": "markdown", "metadata": { "id": "OpjHz3iGway_" }, "source": [ "In order to count only Beauty Shops/Salons and Gyms, we will apply a filter to the enrichment. All filters are applied with an AND-like relationship. This means we need to run two independent enrichment calls, one for the beauty shops/salons and another one for the gyms." ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "id": "SsSAQXI1Dsp6" }, "outputs": [], "source": [ "ph_pharmacies_enriched = enrichment.enrich_polygons(\n", " ph_pharmacies,\n", " variables=['SUB_CLASS_10243439'],\n", " aggregation='COUNT',\n", " geom_col='iso_5car',\n", " filters={Variable.get('SUB_CLASS_10243439').id : \"= 'BEAUTY SHOPS/BEAUTY SALON'\"}\n", ")" ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "id": "muDXaFakDsmm" }, "outputs": [], "source": [ "ph_pharmacies = ph_pharmacies_enriched.rename(columns={'SUB_CLASS_y':'n_beauty_pois'})" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "id": "djUSIiXuDsj5" }, "outputs": [], "source": [ "ph_pharmacies_enriched = enrichment.enrich_polygons(\n", " ph_pharmacies,\n", " variables=['SUB_CLASS_10243439'],\n", " aggregation='COUNT',\n", " geom_col='iso_5car',\n", " filters={Variable.get('SUB_CLASS_10243439').id : \"= 'MEMBERSHIP SPORTS AND RECREATION CLUBS/CLUB AND ASSOCIATION - UNSPECIFIED'\"}\n", ")" ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "id": "JJYbu4k1Dsf_" }, "outputs": [], "source": [ "ph_pharmacies = ph_pharmacies_enriched.rename(columns={'SUB_CLASS':'n_gym_pois'})" ] }, { "cell_type": "code", "execution_count": 38, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 34 }, "id": "D5x3UTeBMgBz", "outputId": "07be18f1-d92c-4901-ada4-2c0c14c97c5b" }, "outputs": [], "source": [ "ph_pharmacies['n_pois_personal_care'] = ph_pharmacies['n_beauty_pois'] + ph_pharmacies['n_gym_pois']\n", "ph_pharmacies.drop(columns=['n_beauty_pois', 'n_gym_pois'], inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Consumer spending\n", "\n", "For consumer spending, we will use AGS premium data. In particular, we will work with the dataset `ags_consumer_sp_dbabddfb` which contains the [latest version of yearly consumer data](https://carto.com/spatial-data-catalog/browser/dataset/ags_consumer_sp_dbabddfb/)." ] }, { "cell_type": "markdown", "metadata": { "id": "9CTgbC4rwiMk" }, "source": [ "##### Variable selection\n", "\n", "We are interested in spending in:\n", " - Personal care services\n", " - Personal care products\n", " - Health care services" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "id": "e37Yqug9Mf7f" }, "outputs": [], "source": [ "dataset = Dataset.get('ags_consumer_sp_dbabddfb')" ] }, { "cell_type": "code", "execution_count": 42, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "5EtgN_ElO1GH", "outputId": "c01502a5-2f01-4d7b-8b36-8e66398a413d" }, "outputs": [ { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      slugnamedescriptiondb_typeagg_methodcolumn_namevariable_group_iddataset_idid
      0BLOCKGROUP_9c78947bBLOCKGROUPGeographic IdentifierSTRINGNoneBLOCKGROUPNonecarto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...
      1HHDCY_11b160d2Number of householdsHouseholds (2019A)INTEGERSUMHHDCYNonecarto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...
      2XCYAP1_ef233ec0XCYAP1Men's Apparel (Ave Hhd Exp)FLOATAVGXCYAP1carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...
      3XCYAP2_762a6f7aXCYAP2Boys Apparel (Ave Hhd Exp)FLOATAVGXCYAP2carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...
      4XCYAP3_12d5fecXCYAP3Women's Apparel (Ave Hhd Exp)FLOATAVGXCYAP3carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...carto-do.ags.demographics_consumerspending_usa...
      \n", "
      " ], "text/plain": [ " slug name description \\\n", "0 BLOCKGROUP_9c78947b BLOCKGROUP Geographic Identifier \n", "1 HHDCY_11b160d2 Number of households Households (2019A) \n", "2 XCYAP1_ef233ec0 XCYAP1 Men's Apparel (Ave Hhd Exp) \n", "3 XCYAP2_762a6f7a XCYAP2 Boys Apparel (Ave Hhd Exp) \n", "4 XCYAP3_12d5fec XCYAP3 Women's Apparel (Ave Hhd Exp) \n", "\n", " db_type agg_method column_name \\\n", "0 STRING None BLOCKGROUP \n", "1 INTEGER SUM HHDCY \n", "2 FLOAT AVG XCYAP1 \n", "3 FLOAT AVG XCYAP2 \n", "4 FLOAT AVG XCYAP3 \n", "\n", " variable_group_id \\\n", "0 None \n", "1 None \n", "2 carto-do.ags.demographics_consumerspending_usa... \n", "3 carto-do.ags.demographics_consumerspending_usa... \n", "4 carto-do.ags.demographics_consumerspending_usa... \n", "\n", " dataset_id \\\n", "0 carto-do.ags.demographics_consumerspending_usa... \n", "1 carto-do.ags.demographics_consumerspending_usa... \n", "2 carto-do.ags.demographics_consumerspending_usa... \n", "3 carto-do.ags.demographics_consumerspending_usa... \n", "4 carto-do.ags.demographics_consumerspending_usa... \n", "\n", " id \n", "0 carto-do.ags.demographics_consumerspending_usa... \n", "1 carto-do.ags.demographics_consumerspending_usa... \n", "2 carto-do.ags.demographics_consumerspending_usa... \n", "3 carto-do.ags.demographics_consumerspending_usa... \n", "4 carto-do.ags.demographics_consumerspending_usa... " ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dataset.variables.to_dataframe().head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The variables we're interested in are:\n", " - `XCYHC2` Health care services expenditure\n", " - `XCYPC3` Personal care services expenditure\n", " - `XCYPC4` Personal care products expenditure" ] }, { "cell_type": "code", "execution_count": 43, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 176 }, "id": "4hnIrl9gO1CZ", "outputId": "000283d5-423d-427c-9269-c726c5288323" }, "outputs": [ { "data": { "text/plain": [ "{'slug': 'XCYHC2_18141567',\n", " 'name': 'XCYHC2',\n", " 'description': 'Health Care Services (Ave Hhd Exp)',\n", " 'db_type': 'FLOAT',\n", " 'agg_method': 'AVG',\n", " 'column_name': 'XCYHC2',\n", " 'variable_group_id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020.consumer_spending_by_product_category',\n", " 'dataset_id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020',\n", " 'id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020.XCYHC2'}" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Variable.get('XCYHC2_18141567').to_dict()" ] }, { "cell_type": "code", "execution_count": 44, "metadata": { "id": "vtbXm1ayO0_g" }, "outputs": [], "source": [ "ph_pharmacies_enriched = enrichment.enrich_polygons(\n", " ph_pharmacies,\n", " variables=['XCYPC3_7d26d739', 'XCYPC4_e342429a', 'XCYHC2_18141567'],\n", " geom_col='iso_5car'\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We rename the new columns to give them a more descriptive name." ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "id": "cw68th5dQa3r" }, "outputs": [], "source": [ "ph_pharmacies = ph_pharmacies_enriched.rename(columns={'XCYHC2':'health_care_services_exp',\n", " 'XCYPC3':'personal_care_services_exp',\n", " 'XCYPC4':'personal_care_products_exp'})" ] }, { "cell_type": "code", "execution_count": 46, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 227 }, "id": "bdOlUKo4TPQs", "outputId": "e684af95-31b0-467e-b216-f6b1d8b09553" }, "outputs": [ { "data": { "text/html": [ "
      \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      PB_IDgeoiddo_dateNAMEBRANDNAMETRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASS_xEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeomiso_5carPOPCYHINCYMED65HINCYMED75INCCYMEDHHpop_60plusn_pois_personal_carehealth_care_services_exppersonal_care_services_exppersonal_care_products_exp
      011146893271114689327#-75.186601#39.9183012020-08-01WALGREEN EASTERN CO., INC.WALGREENSWALGREENSNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19145-41222310 W OREGON AVE, PHILADELPHIA, PA, 19145-41222310 W OREGON AVEPHILADELPHIA, PA, 19145-4122-75.18660139.918301S8HPNTSCZAHIGH1.0(215) 468-2481NaNNaNWWW.WALLGREENSBOOTSALLIANCE.COMNaNDRUG STORES AND PROPRIETARY STORES5912NaN59129901DRUG STORES446110.010010700DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY40.00.00.00.00.0NaNG0.02.00.0WALGREEN EASTERN CO., INC.200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,...200 WILMOT RDDEERFIELDILLINOISUSA600154620.0WALGREENS BOOTS ALLIANCE, INC.108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145108 WILMOT RDDEERFIELDILLINOIS600155145.0NWALGREENS BOOTS ALLIANCE, INC.108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,...108 WILMOT RDDEERFIELDILLINOISUSA600155145.09551.03.0NaNNaNPOINT (-75.18660 39.91830)MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ...15037.34023450981.22727338971.63636455397.4090913364.675232541.0925.483182349.088636302.686364
      111149424691114942469#-75.160179#39.9704782020-08-01TANG PHARMACY IVNaNTANG PHARMACY IVNaNUSANaNPHILADELPHIAPHILADELPHIAPENNSYLVANIAPA19130-1604900 N BROAD ST, PHILADELPHIA, PA, 19130-1604900 N BROAD STPHILADELPHIA, PA, 19130-1604-75.16017939.970478S8HPNTSCZAHIGH1.0(215) 235-1400NaNNaNNaNNaNDRUG STORES AND PROPRIETARY STORES5912NaN59120000DRUG STORES AND PROPRIETARY STORES446110.010230030DIVISION G. - RETAIL TRADEMISCELLANEOUS RETAILDRUG STORES AND PROPRIETARY STORESDRUG STORES AND PROPRIETARY STORES/PHARMACY4.04.02012.0610000.0610000.020.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-75.16018 39.97048)MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ...54623.53098046537.36842137788.38596549895.80701810112.7251431442.0833.376667321.834211279.067719
      \n", "
      " ], "text/plain": [ " PB_ID geoid do_date \\\n", "0 1114689327 1114689327#-75.186601#39.918301 2020-08-01 \n", "1 1114942469 1114942469#-75.160179#39.970478 2020-08-01 \n", "\n", " NAME BRANDNAME TRADE_NAME FRANCHISE_NAME \\\n", "0 WALGREEN EASTERN CO., INC. WALGREENS WALGREENS NaN \n", "1 TANG PHARMACY IV NaN TANG PHARMACY IV NaN \n", "\n", " ISO3 AREANAME4 AREANAME3 AREANAME2 AREANAME1 STABB POSTCODE \\\n", "0 USA NaN PHILADELPHIA PHILADELPHIA PENNSYLVANIA PA 19145-4122 \n", "1 USA NaN PHILADELPHIA PHILADELPHIA PENNSYLVANIA PA 19130-1604 \n", "\n", " FORMATTEDADDRESS MAINADDRESSLINE \\\n", "0 2310 W OREGON AVE, PHILADELPHIA, PA, 19145-4122 2310 W OREGON AVE \n", "1 900 N BROAD ST, PHILADELPHIA, PA, 19130-1604 900 N BROAD ST \n", "\n", " ADDRESSLASTLINE LONGITUDE LATITUDE GEORESULT \\\n", "0 PHILADELPHIA, PA, 19145-4122 -75.186601 39.918301 S8HPNTSCZA \n", "1 PHILADELPHIA, PA, 19130-1604 -75.160179 39.970478 S8HPNTSCZA \n", "\n", " CONFIDENCE_CODE COUNTRY_ACCESS_CODE TEL_NUM FAXNUM EMAIL \\\n", "0 HIGH 1.0 (215) 468-2481 NaN NaN \n", "1 HIGH 1.0 (215) 235-1400 NaN NaN \n", "\n", " HTTP OPEN_24H \\\n", "0 WWW.WALLGREENSBOOTSALLIANCE.COM NaN \n", "1 NaN NaN \n", "\n", " BUSINESS_LINE SIC1 SIC2 SIC8 \\\n", "0 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59129901 \n", "1 DRUG STORES AND PROPRIETARY STORES 5912 NaN 59120000 \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE MICODE \\\n", "0 DRUG STORES 446110.0 10010700 \n", "1 DRUG STORES AND PROPRIETARY STORES 446110.0 10230030 \n", "\n", " TRADE_DIVISION GROUP \\\n", "0 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "1 DIVISION G. - RETAIL TRADE MISCELLANEOUS RETAIL \n", "\n", " CLASS \\\n", "0 DRUG STORES AND PROPRIETARY STORES \n", "1 DRUG STORES AND PROPRIETARY STORES \n", "\n", " SUB_CLASS_x EMPLOYEE_HERE EMPLOYEE_COUNT \\\n", "0 DRUG STORES AND PROPRIETARY STORES/PHARMACY 40.0 0.0 \n", "1 DRUG STORES AND PROPRIETARY STORES/PHARMACY 4.0 4.0 \n", "\n", " YEAR_START SALES_VOLUME_LOCAL SALES_VOLUME_US_DOLLARS CURRENCY_CODE \\\n", "0 0.0 0.0 0.0 NaN \n", "1 2012.0 610000.0 610000.0 20.0 \n", "\n", " AGENT_CODE LEGAL_STATUS_CODE STATUS_CODE SUBSIDIARY_INDICATOR \\\n", "0 G 0.0 2.0 0.0 \n", "1 G 3.0 0.0 0.0 \n", "\n", " PARENT_BUSINESS_NAME \\\n", "0 WALGREEN EASTERN CO., INC. \n", "1 NaN \n", "\n", " PARENT_ADDRESS PARENT_STREET_ADDRESS \\\n", "0 200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,... 200 WILMOT RD \n", "1 NaN NaN \n", "\n", " PARENT_AREANAME3 PARENT_AREANAME1 PARENT_COUNTRY PARENT_POSTCODE \\\n", "0 DEERFIELD ILLINOIS USA 600154620.0 \n", "1 NaN NaN NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_BUSINESS_NAME \\\n", "0 WALGREENS BOOTS ALLIANCE, INC. \n", "1 NaN \n", "\n", " DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145 \n", "1 NaN \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 108 WILMOT RD DEERFIELD \n", "1 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 ILLINOIS 600155145.0 \n", "1 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_INDICATOR GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 N WALGREENS BOOTS ALLIANCE, INC. \n", "1 N NaN \n", "\n", " GLOBAL_ULTIMATE_ADDRESS \\\n", "0 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,... \n", "1 NaN \n", "\n", " GLOBAL_ULTIMATE_STREET_ADDRESS GLOBAL_ULTIMATE_AREANAME3 \\\n", "0 108 WILMOT RD DEERFIELD \n", "1 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY GLOBAL_ULTIMATE_POSTCODE \\\n", "0 ILLINOIS USA 600155145.0 \n", "1 NaN NaN NaN \n", "\n", " FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL EXCHANGE_NAME \\\n", "0 9551.0 3.0 NaN NaN \n", "1 0.0 0.0 NaN NaN \n", "\n", " geom \\\n", "0 POINT (-75.18660 39.91830) \n", "1 POINT (-75.16018 39.97048) \n", "\n", " iso_5car POPCY \\\n", "0 MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ... 15037.340234 \n", "1 MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ... 54623.530980 \n", "\n", " HINCYMED65 HINCYMED75 INCCYMEDHH pop_60plus \\\n", "0 50981.227273 38971.636364 55397.409091 3364.675232 \n", "1 46537.368421 37788.385965 49895.807018 10112.725143 \n", "\n", " n_pois_personal_care health_care_services_exp personal_care_services_exp \\\n", "0 541.0 925.483182 349.088636 \n", "1 1442.0 833.376667 321.834211 \n", "\n", " personal_care_products_exp \n", "0 302.686364 \n", "1 279.067719 " ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ph_pharmacies.head(2)" ] }, { "cell_type": "markdown", "metadata": { "id": "NSrbnR9qxGiT" }, "source": [ "\n", "### 4. Dashboard\n", "\n", "Finally, with all the data gathered, we will build the dashboard and publish it so we can share it with our client/manager/colleague for them to explore it.\n", "\n", "This dashboard allows you to select a range of desired expenditure in care products, people aged 60+, household income, and so forth. Selecting the desired ranges will filter out pharmacies, so that in the end you can identify the target pharmacies for your marketing campaign." ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "id": "SCT0p3tbQq8b" }, "outputs": [], "source": [ "cmap = Map(Layer(ph_pharmacies, \n", " geom_col='geom', \n", " style=color_category_style('SIC8_DESCRIPTION', size=4, opacity=0.85, palette='safe', stroke_width=0.15), \n", " widgets=[formula_widget(\n", " 'PB_ID', \n", " operation='COUNT', \n", " title='Total number of pharmacies', \n", " description='Keep track of the total amount of pharmacies that meet the ranges selected on the widgets below'),\n", " histogram_widget(\n", " 'pop_60plus',\n", " title='Population 60+',\n", " description='Select a range of values to filter',\n", " buckets=15\n", " ),\n", " histogram_widget(\n", " 'HINCYMED65',\n", " title='Household income 65-74',\n", " buckets=15\n", " ),\n", " histogram_widget(\n", " 'HINCYMED75',\n", " title='Household income 75+',\n", " buckets=15\n", " ),\n", " histogram_widget(\n", " 'n_pois_personal_care',\n", " title='Number of personal care POIs',\n", " buckets=15\n", " ),\n", " histogram_widget(\n", " 'personal_care_products_exp',\n", " title='Expenditure in personal care products ($)',\n", " buckets=15\n", " )], \n", " legends=color_category_legend(\n", " title='Pharmacies',\n", " description='Type of store'), \n", " popup_hover=[popup_element('NAME', title='Name')]\n", " ), \n", " viewport={'zoom': 11}\n", ")" ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "eYvmWDnsXoQK", "outputId": "81fcf9bf-75bd-4c10-a67e-0139a3389474" }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", " None\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", "\n", "\n", " Static map image\n", " \n", " \n", " \n", "\n", "\n", " \n", "
      \n", "
      \n", "
      \n", " \n", " \n", "
      \n", "
      \n", " \n", "\n", "
      \n", " \n", " \n", " \n", " \n", " \n", "
      \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
      \n", " \n", " \n", "       \n", "
      \n", "
      \n", "
      \n", " \n", "
      \n", "
      \n", "       \n", "\n", " \n", "\n", "
      \n", "
      \n", " :\n", "
      \n", " \n", " \n", "
      \n", "
      \n", "\n", "
      \n", " StackTrace\n", "
        \n", "
        \n", "
        \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cmap" ] }, { "cell_type": "markdown", "metadata": { "id": "7A4fyiG1xWVp" }, "source": [ "#### Publish dashboard" ] }, { "cell_type": "code", "execution_count": 49, "metadata": { "id": "hu6lMFmvxWnM" }, "outputs": [ { "data": { "text/plain": [ "{'id': '6765be0f-02da-4bec-b80c-63ec9bcd5624',\n", " 'url': 'https://cartoframes-org.carto.com/u/cartoframes/kuviz/6765be0f-02da-4bec-b80c-63ec9bcd5624',\n", " 'name': 'ph_pharmacies_dashboard',\n", " 'privacy': 'password'}" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cmap.publish('ph_pharmacies_dashboard', password='MY_PASS', if_exists='replace')" ] } ], "metadata": { "colab": { "collapsed_sections": [], "name": "[CARTO] Building a dashboard.ipynb", "provenance": [], "toc_visible": true }, "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.7.3" } }, "nbformat": 4, "nbformat_minor": 4 }