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.
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.
Note: This use case leverages premium datasets from CARTO's Data Observatory.
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:
Given these characteristics, they would like to know which pharmacies and drug stores in the city of Philadelphia they should focus their efforts on.
In order to identify the target drug stores and pharmacies, we will follow the following steps:
Import the packages we'll use.
import geopandas as gpd
import pandas as pd
from cartoframes.auth import set_default_credentials
from cartoframes.data.services import Isolines
from cartoframes.data.observatory import *
from cartoframes.viz import *
from shapely.geometry import box
pd.set_option('display.max_columns', None)
In order to be able to use the Data Observatory via CARTOframes, you need to set your CARTO account credentials first.
Please, visit the Authentication guide for further detail.
from cartoframes.auth import set_default_credentials
set_default_credentials('creds.json')
Note about credentials
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.
Below is the bounding box of the area of study.
dem_bbox = box(-75.229353,39.885501,-75.061124,39.997898)
We can get the pharmacies from Pitney Bowes' Consumer Points of Interest dataset. This is a premium dataset, so we first need to check that we are subscribed to it.
Take a look at this template for more details on how to access and download a premium dataset.
Catalog().subscriptions().datasets.to_dataframe()
slug | name | description | category_id | country_id | data_source_id | provider_id | geography_name | geography_description | temporal_aggregation | time_coverage | update_frequency | is_public_data | lang | version | category_name | provider_name | geography_id | id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | ags_sociodemogr_a7e14220 | Sociodemographics - United States of America (... | Census and ACS sociodemographic data estimated... | demographics | usa | sociodemographics | ags | Census Block Group - United States of America | None | yearly | None | yearly | False | eng | 2020 | Demographics | Applied Geographic Solutions | carto-do.ags.geography_usa_blockgroup_2015 | carto-do.ags.demographics_sociodemographics_us... |
1 | ags_retailpoten_aaf25a8c | Retail Potential - United States of America (C... | The retail potential database consists of aver... | demographics | usa | retailpotential | ags | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | yearly | [2018-01-01, 2019-01-01) | yearly | False | eng | 2019 | Demographics | Applied Geographic Solutions | carto-do-public-data.carto.geography_usa_block... | carto-do.ags.demographics_retailpotential_usa_... |
2 | pb_consumer_po_62cddc04 | Points Of Interest - Consumer - United States ... | Consumer Point of interest database per catego... | points_of_interest | usa | consumer_points_of_interest | pitney_bowes | Latitude/Longitude - United States of America | Location of Points of Interest | monthly | None | monthly | False | eng | v1 | Points of Interest | Pitney Bowes | carto-do.pitney_bowes.geography_usa_latlon_v1 | carto-do.pitney_bowes.pointsofinterest_consume... |
Pitney Bowes POI's are hierarchically classified (levels: trade division, group, class, sub class).
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.
We start by selecting our dataset and taking a quick look at its first 10 rows.
dataset = Dataset.get('pb_consumer_po_62cddc04')
dataset.head()
HTTP | ISO3 | NAME | SIC1 | SIC2 | SIC8 | CLASS | GROUP | PB_ID | STABB | geoid | FAXNUM | MICODE | TEL_NUM | do_date | LATITUDE | OPEN_24H | POSTCODE | do_label | AREANAME1 | AREANAME2 | AREANAME3 | AREANAME4 | BRANDNAME | GEORESULT | LONGITUDE | SUB_CLASS | AGENT_CODE | TRADE_NAME | YEAR_START | STATUS_CODE | BUSINESS_LINE | CURRENCY_CODE | EMPLOYEE_HERE | EXCHANGE_NAME | TICKER_SYMBOL | EMPLOYEE_COUNT | FAMILY_MEMBERS | FRANCHISE_NAME | HIERARCHY_CODE | PARENT_ADDRESS | PARENT_COUNTRY | TRADE_DIVISION | ADDRESSLASTLINE | CONFIDENCE_CODE | MAINADDRESSLINE | PARENT_POSTCODE | FORMATTEDADDRESS | PARENT_AREANAME1 | PARENT_AREANAME3 | SIC8_DESCRIPTION | ALT_INDUSTRY_CODE | LEGAL_STATUS_CODE | SALES_VOLUME_LOCAL | COUNTRY_ACCESS_CODE | PARENT_BUSINESS_NAME | SUBSIDIARY_INDICATOR | PARENT_STREET_ADDRESS | GLOBAL_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_COUNTRY | SALES_VOLUME_US_DOLLARS | GLOBAL_ULTIMATE_POSTCODE | DOMESTIC_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_AREANAME1 | GLOBAL_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_INDICATOR | DOMESTIC_ULTIMATE_POSTCODE | DOMESTIC_ULTIMATE_AREANAME1 | DOMESTIC_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_BUSINESS_NAME | GLOBAL_ULTIMATE_STREET_ADDRESS | DOMESTIC_ULTIMATE_BUSINESS_NAME | DOMESTIC_ULTIMATE_STREET_ADDRESS | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | WWW.IDAHO.GOV | USA | COAST TO COAST LOCATORS | 7841 | None | 91210402 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2204806297 | NE | 2204806297#144.7436111#13.2777778 | None | 10050191 | (208) 334-3847 | 2020-08-01 | 33.788524 | None | None | ONO | PENNSYLVANIA | DOUGLAS | ORANGE | None | PIP PRINTING | S5HPNTSCZA | -117.876634 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | None | 2000 | None | None | None | 15 | None | None | 15 | 01354 | None | None | None | None | DIVISION G. - RETAIL TRADE | OREM, UT, 84097 | HIGH | None | None | GU | IDAHO | ROCKVILLE | LIFE INSURANCE CARRIERS | 812930 | 003 | 706388 | None | None | 0 | None | 6000 EXECUTIVE BLVD # 700, ROCKVILLE, MARYLAND... | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
1 | WWW.MARKSFUNERALSERVICE.COM | USA | FLOYD'S GENERAL STORES | 7841 | 6531 | 91110403 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2041145109 | GA | 2041145109#144.6722222#13.3155556 | None | 10050191 | (970) 686-9525 | 2020-08-01 | 41.232673 | None | None | CHALAN DAOG | OHIO | GLYNN | OMAHA | None | None | S8HPNTSCZA | -84.820387 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | None | 1965 | None | None | None | 13 | None | None | 13 | 00007 | None | None | None | None | DIVISION G. - RETAIL TRADE | LYNCHBURG, VA, 24503-3872 | HIGH | None | None | GU | None | None | MORTGAGE BANKERS | 812930 | 003 | 600000 | None | None | 0 | None | 1101 ELLER DR, FORT LAUDERDALE, FLORIDA, 33316... | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
2 | WWW.HEAVENLYDAYS.COM | USA | LITTLE GENERAL STORE | 7841 | None | 94410402 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2041107895 | TX | 2041107895#144.7163889#13.2494444 | None | 10050191 | (240) 699-0034 | 2020-08-01 | 44.293525 | None | None | AJAYAN | CALIFORNIA | ANGELINA | SISTERS | None | None | S8HPNTSCZA | -85.848654 | PSYCHIATRIC HOSPITALS | G | None | 1952 | None | None | None | 50 | None | None | 50 | 01995 | None | None | None | None | DIVISION G. - RETAIL TRADE | VIRGINIA BEACH, VA, 23464-6500 | HIGH | None | 283418534 | GU | None | None | LOAN AGENTS | 812930 | 003 | 4230106 | None | None | 0 | None | None | None | None | None | None | None | None | N | None | None | None | None | 711 D ST NW STE 200 | None | None |
3 | WWW.DUSCKAS-TAYLORFUNERALHOME.COM | USA | M STREET CARDS & VARIETY | 7841 | None | 97119906 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2041107894 | AZ | 2041107894#144.7338889#13.28 | None | 10050191 | (814) 899-7656 | 2020-08-01 | 32.660750 | None | None | GUGAGON | CALIFORNIA | MOHAVE | DALLAS | None | None | S8HPNTSCZA | -90.095639 | DATA PROCESSING SCHOOLS | G | None | 2005 | None | None | None | 11 | None | None | 11 | 00999 | None | None | None | None | DIVISION G. - RETAIL TRADE | BURLINGTON, WI, 53105-2380 | HIGH | None | None | GU | PENNSYLVANIA | None | ASSESSMENT ASSOCIATIONS, ACCIDENT AND HEALTH I... | 812930 | 000 | 508581 | None | None | 0 | None | 222 3RD AVE SE STE 285, CEDAR RAPIDS, IOWA, 52... | None | None | None | None | None | None | Y | None | None | None | None | None | None | None |
4 | WWW.OREGONCREMATION.COM | USA | FAMILY DOLLAR STORE | 7841 | None | 94410402 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2041107893 | SC | 2041107893#144.7463889#13.2883333 | None | 10050191 | (503) 235-3104 | 2020-08-01 | 42.303732 | None | None | ADAIQUE | CALIFORNIA | FLORENCE | SIDNEY | None | None | S8HPNTSCZA | -88.301804 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | ADVANCE AMERICA | 2012 | None | None | None | 20 | None | None | 20 | 01765 | None | None | None | USA | DIVISION G. - RETAIL TRADE | ALBION, ME, 04910-6236 | HIGH | None | None | GU | None | None | PENSION FUNDS | 812930 | 000 | 269036 | None | None | 0 | None | None | USA | None | None | None | None | None | N | None | None | None | None | None | None | None |
5 | WWW.JOSEPHHBROWN.COM | USA | GL VARIETY | 7841 | None | 92110404 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2200200082 | PR | 2200200082#-95.5710222#42.3192199 | None | 10050191 | (410) 383-2700 | 2020-08-01 | 35.029179 | None | None | PLEASANT VALLEY | NEW YORK | ISABELA | CHICKASHA | None | None | S8HPNTSCZA | -93.388506 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | CHECK 'N GO | 1936 | None | None | None | 11 | None | None | 11 | 00025 | None | None | None | None | DIVISION G. - RETAIL TRADE | HONOLULU, HI, 96816-2637 | HIGH | None | None | IA | None | None | WARRANTY INSURANCE, AUTOMOBILE | 812930 | 003 | 218834 | None | None | 0 | None | None | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
6 | WWW.SCARBOROUGHHISTORICALSOCIETY.ORG | USA | REDBOX FAMILY DOLLAR STORE | 7841 | None | 94410200 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2209023773 | AZ | 2209023773#-91.6108966#40.5474737 | None | 10050191 | (207) 885-9997 | 2020-08-01 | 41.908609 | None | None | GHOST HOLLOW | WASHINGTON | PIMA | CHICAGO | None | CARTRIDGE WORLD | S8HP-TSCZA | -156.456725 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | ACE CASH EXPRESS | 2011 | None | None | None | 18 | None | None | 18 | 00952 | None | None | None | None | DIVISION G. - RETAIL TRADE | CULVER CITY, CA, 90232-3652 | HIGH | None | None | IA | None | None | ACCIDENT INSURANCE CARRIERS | 812930 | 000 | 1179493 | None | None | 0 | None | 1050 THMAS JFFERSON ST NW, WASHINGTON, DISTRIC... | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
7 | WWW.PHILALANDMARKS.ORG | USA | TOP DOLLAR STORE | 7841 | 6513 | 91999901 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2197355937 | WA | 2197355937#-91.7453042#41.9165678 | None | 10050191 | (215) 925-2251 | 2020-08-01 | 40.718822 | None | None | TISSEL HOLLOW | CALIFORNIA | PIERCE | WEST VALLEY | None | None | S8HPNTSCZA | -80.317952 | SPECIALTY HOSPITALS, EXCEPT PSYCHIATRIC | G | None | 1981 | None | None | None | 20 | None | None | 20 | 00002 | None | None | None | None | DIVISION G. - RETAIL TRADE | BRANDON, FL, 33510-2314 | HIGH | None | None | IA | None | GALT | HEALTH INSURANCE CARRIERS | 812930 | 003 | 702600 | None | None | 0 | None | None | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
8 | None | USA | OLLIE'S BARGIN OUTLET | 7841 | None | 94510402 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2119978561 | AZ | 2119978561#-90.9290128#42.2789979 | None | 10050191 | (715) 758-8050 | 2020-08-01 | 27.916668 | None | None | WHITEWATER CANYON | DISTRICT OF COLUMBIA | COCONINO | TAMPA | None | SIR SPEEDY | S8HPNTSCZA | -87.657751 | VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING | G | CHECK 'N GO | 2003 | None | None | None | 30 | None | None | 30 | 02908 | None | None | None | None | DIVISION G. - RETAIL TRADE | SALEM, UT, 84653-9453 | HIGH | None | None | IA | None | None | PENSION FUNDS | 812930 | 003 | 176397 | None | None | 0 | None | 1325 N 10TH ST, SAINT LOUIS, MISSOURI, 6310645... | None | None | None | None | None | None | N | None | None | None | None | None | None | None |
9 | None | USA | DOLLAR PLUS AND BEAUTY | 7841 | None | 93110102 | MISCELLANEOUS APPAREL AND ACCESSORY STORES | None | MOTION PICTURES | 2200200055 | TN | 2200200055#-91.2169511#42.0423068 | None | 10050191 | (239) 643-6375 | 2020-08-01 | 28.636325 | None | None | HOGS DEN HOLLOW | LOUISIANA | DAVIDSON | MAITLAND | None | None | S8HPNTSCZA | -93.299198 | VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING | G | ADVANCE AMERICA | 2010 | None | None | None | 20 | None | None | 20 | 00010 | None | None | None | USA | DIVISION G. - RETAIL TRADE | ALBUQUERQUE, NM, 87114-3809 | HIGH | None | None | IA | None | None | LOAN AGENTS | 812930 | 003 | 1806921 | None | None | 0 | None | None | USA | None | None | None | None | None | N | None | None | None | None | 820 1ST ST NE STE 740 | None | None |
Let's now download a small sample to help us identify which of the four hierarchy variables gives us the pharmacies.
sql_query = "SELECT * except(do_label) FROM $dataset$ WHERE ST_IntersectsBox(geom, -75.161723,39.962019,-75.149535,39.968071)"
sample = dataset.to_dataframe(sql_query=sql_query)
sample.head()
geoid | do_date | NAME | BRANDNAME | PB_ID | TRADE_NAME | FRANCHISE_NAME | ISO3 | AREANAME4 | AREANAME3 | AREANAME2 | AREANAME1 | STABB | POSTCODE | FORMATTEDADDRESS | MAINADDRESSLINE | ADDRESSLASTLINE | LONGITUDE | LATITUDE | GEORESULT | CONFIDENCE_CODE | COUNTRY_ACCESS_CODE | TEL_NUM | FAXNUM | HTTP | OPEN_24H | BUSINESS_LINE | SIC1 | SIC2 | SIC8 | SIC8_DESCRIPTION | ALT_INDUSTRY_CODE | MICODE | TRADE_DIVISION | GROUP | CLASS | SUB_CLASS | EMPLOYEE_HERE | EMPLOYEE_COUNT | YEAR_START | SALES_VOLUME_LOCAL | SALES_VOLUME_US_DOLLARS | CURRENCY_CODE | AGENT_CODE | LEGAL_STATUS_CODE | STATUS_CODE | SUBSIDIARY_INDICATOR | PARENT_BUSINESS_NAME | PARENT_ADDRESS | PARENT_STREET_ADDRESS | PARENT_AREANAME3 | PARENT_AREANAME1 | PARENT_COUNTRY | PARENT_POSTCODE | DOMESTIC_ULTIMATE_BUSINESS_NAME | DOMESTIC_ULTIMATE_ADDRESS | DOMESTIC_ULTIMATE_STREET_ADDRESS | DOMESTIC_ULTIMATE_AREANAME3 | DOMESTIC_ULTIMATE_AREANAME1 | DOMESTIC_ULTIMATE_POSTCODE | GLOBAL_ULTIMATE_INDICATOR | GLOBAL_ULTIMATE_BUSINESS_NAME | GLOBAL_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_STREET_ADDRESS | GLOBAL_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_AREANAME1 | GLOBAL_ULTIMATE_COUNTRY | GLOBAL_ULTIMATE_POSTCODE | FAMILY_MEMBERS | HIERARCHY_CODE | TICKER_SYMBOL | EXCHANGE_NAME | geom | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1116489911#-75.157314#39.962857 | 2019-11-01 | DRINKER DOWNS, INC. | NaN | 1116489911 | NaN | NaN | USA | NaN | PHILADELPHIA | NaN | PENNSYLVANIA | PA | 191233332 | 549 N 12TH ST, PHILADELPHIA, PA, 191233332 | 549 N 12TH ST | PHILADELPHIA, PA, 191233332 | -75.157314 | 39.962857 | S8HPNTSCZA | HIGH | 1.0 | (267) 318-7772 | NaN | NaN | WWW.INSTITUTEBAR.COM/ | NaN | DRINKING PLACES, NSK | 5813.0 | NaN | 58130200.0 | NIGHT CLUBS | 722410.0 | 10130261 | DIVISION G. - RETAIL TRADE | EATING AND DRINKING PLACES | DRINKING PLACES | DRINKING PLACES/CAFE, PUB | 4.0 | 4.0 | 2008.0 | 165700.0 | 165700.0 | 20.0 | G | 3.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.15731 39.96286) |
1 | 2019661767#-75.160361#39.96587 | 2019-10-01 | DIVING BELL SCUBA SHOP | NaN | 2019661767 | NaN | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19123 | 681 N BROAD ST, PHILADELPHIA, PA, 19123 | 681 N BROAD ST | PHILADELPHIA, PA, 19123 | -75.160361 | 39.965870 | S8HPNTSCZA | HIGH | NaN | (215) 763-6868 | NaN | HOWARD_PRUYN@DIVINGBELL.COM | NaN | NaN | NaN | NaN | NaN | NaN | WATER SPORT | NaN | 10120300 | DIVISION M. - SPORTS | SPORTS | WATER SPORT | WATER SPORT | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.16036 39.96587) |
2 | 1116489911#-75.157314#39.962857 | 2019-09-01 | DRINKER DOWNS, INC. | NaN | 1116489911 | NaN | NaN | USA | NaN | PHILADELPHIA | NaN | PENNSYLVANIA | PA | 191233332 | 549 N 12TH ST, PHILADELPHIA, PA 191233332 | 549 N 12TH ST | PHILADELPHIA, PA 191233332 | -75.157314 | 39.962857 | S8HPNTSCZA | HIGH | 1.0 | (267) 318-7772 | NaN | NaN | WWW.INSTITUTEBAR.COM/ | NaN | DRINKING PLACES, NSK | 5813.0 | NaN | 58130200.0 | NIGHT CLUBS | 722410.0 | 10130261 | DIVISION G. - RETAIL TRADE | EATING AND DRINKING PLACES | DRINKING PLACES | DRINKING PLACES/CAFE, PUB | 4.0 | 4.0 | 2008.0 | 165700.0 | 165700.0 | 20.0 | G | 3.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.15731 39.96286) |
3 | 1249192804#-75.157314#39.962857 | 2019-11-01 | THE INSTITUTE BAR | NaN | 1249192804 | NaN | NaN | USA | NaN | PHILADELPHIA | NaN | PENNSYLVANIA | PA | 191233332 | 549 N 12TH ST, PHILADELPHIA, PA, 191233332 | 549 N 12TH ST | PHILADELPHIA, PA, 191233332 | -75.157314 | 39.962857 | S8HPNTSCZA | HIGH | 1.0 | (215) 787-0888 | NaN | CHARLIE@INSTITUTEBAR.COM | WWW.INSTITUTEBAR.COM | NaN | DRINKING PLACES, NSK | 5813.0 | NaN | 58130105.0 | TAVERN (DRINKING PLACES) | 722410.0 | 10786105 | DIVISION G. - RETAIL TRADE | EATING AND DRINKING PLACES | DRINKING PLACES | DRINKING PLACES/CAFE, PUB | 4.0 | 4.0 | 2013.0 | 101129.0 | 101129.0 | 20.0 | G | 0.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.15731 39.96286) |
4 | 1243566100#-75.15985#39.96491 | 2019-09-01 | LIUNA LOCAL UNION 332 | NaN | 1243566100 | NaN | NaN | USA | NaN | PHILADELPHIA | NaN | PENNSYLVANIA | PA | 191232411 | 1310 WALLACE ST, PHILADELPHIA, PA 191232411 | 1310 WALLACE ST | PHILADELPHIA, PA 191232411 | -75.159850 | 39.964910 | S5HPNTSCZA | HIGH | 1.0 | (215) 765-6272 | NaN | NaN | WWW.LDC-PHILA-VIC.ORG | NaN | LABOR ORGANIZATIONS, NSK | 8631.0 | NaN | 86310000.0 | LABOR ORGANIZATIONS | 813930.0 | 10248631 | DIVISION I. - SERVICES | MEMBERSHIP ORGANIZATIONS | LABOR UNIONS AND SIMILAR LABOR ORGANIZATIONS | LABOR ORGANIZATIONS | 3.0 | 3.0 | 2010.0 | 3033044.0 | 3033044.0 | 20.0 | G | 0.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.15985 39.96491) |
sample['TRADE_DIVISION'].unique()
array(['DIVISION G. - RETAIL TRADE', 'DIVISION M. - SPORTS', 'DIVISION I. - SERVICES', 'DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATE', 'DIVISION C. - CONSTRUCTION', 'DIVISION J. - PUBLIC ADMINISTRATION', 'DIVISION E. - TRANSPORTATION AND PUBLIC UTILITIES', 'DIVISION L. - TOURISM'], dtype=object)
sample.loc[sample['TRADE_DIVISION'] == 'DIVISION G. - RETAIL TRADE', 'GROUP'].unique()
array(['EATING AND DRINKING PLACES', 'AUTOMOTIVE DEALERS AND GASOLINE SERVICE STATIONS', 'MISCELLANEOUS RETAIL', 'BUILDING MATERIALS, HARDWARE, GARDEN SUPPLIES AND MOBILE HOMES', 'APPAREL AND ACCESSORY STORES', 'HOME FURNITURE, FURNISHINGS AND EQUIPMENT STORES', 'FOOD STORES'], dtype=object)
sample.loc[sample['TRADE_DIVISION'] == 'DIVISION G. - RETAIL TRADE', 'CLASS'].unique()
array(['DRINKING PLACES', 'EATING PLACES/RESTAURANTS', 'AUTO AND HOME SUPPLY STORES', 'GASOLINE SERVICE STATIONS', 'MOTOR VEHICLE DEALERS (NEW AND USED)', 'RETAIL STORES, NOT ELSEWHERE CLASSIFIED', 'PAINT, GLASS, AND WALLPAPER STORES', 'MISCELLANEOUS APPAREL AND ACCESSORY STORES', 'FAMILY CLOTHING STORES', 'MISCELLANEOUS SHOPPING GOODS STORES', 'LUMBER AND OTHER BUILDING MATERIALS DEALERS', 'HOME FURNITURE AND FURNISHINGS STORES', 'DRUG STORES AND PROPRIETARY STORES', 'NONSTORE RETAILERS', 'GROCERY STORES', 'FRUIT AND VEGETABLE MARKETS', 'LIQUOR STORES'], dtype=object)
The class DRUG STORES AND PROPRIETARY STORES
is the one we're looking for.
sample.loc[sample['CLASS'] == 'DRUG STORES AND PROPRIETARY STORES', 'SUB_CLASS'].unique()
array(['DRUG STORES AND PROPRIETARY STORES/PHARMACY'], dtype=object)
sql_query = """SELECT * except(do_label)
FROM $dataset$
WHERE CLASS = 'DRUG STORES AND PROPRIETARY STORES'
AND ST_IntersectsBox(geom, -75.229353,39.885501,-75.061124,39.997898)"""
ph_pharmacies = dataset.to_dataframe(sql_query=sql_query)
ph_pharmacies.head()
geoid | do_date | NAME | BRANDNAME | PB_ID | TRADE_NAME | FRANCHISE_NAME | ISO3 | AREANAME4 | AREANAME3 | AREANAME2 | AREANAME1 | STABB | POSTCODE | FORMATTEDADDRESS | MAINADDRESSLINE | ADDRESSLASTLINE | LONGITUDE | LATITUDE | GEORESULT | CONFIDENCE_CODE | COUNTRY_ACCESS_CODE | TEL_NUM | FAXNUM | HTTP | OPEN_24H | BUSINESS_LINE | SIC1 | SIC2 | SIC8 | SIC8_DESCRIPTION | ALT_INDUSTRY_CODE | MICODE | TRADE_DIVISION | GROUP | CLASS | SUB_CLASS | EMPLOYEE_HERE | EMPLOYEE_COUNT | YEAR_START | SALES_VOLUME_LOCAL | SALES_VOLUME_US_DOLLARS | CURRENCY_CODE | AGENT_CODE | LEGAL_STATUS_CODE | STATUS_CODE | SUBSIDIARY_INDICATOR | PARENT_BUSINESS_NAME | PARENT_ADDRESS | PARENT_STREET_ADDRESS | PARENT_AREANAME3 | PARENT_AREANAME1 | PARENT_COUNTRY | PARENT_POSTCODE | DOMESTIC_ULTIMATE_BUSINESS_NAME | DOMESTIC_ULTIMATE_ADDRESS | DOMESTIC_ULTIMATE_STREET_ADDRESS | DOMESTIC_ULTIMATE_AREANAME3 | DOMESTIC_ULTIMATE_AREANAME1 | DOMESTIC_ULTIMATE_POSTCODE | GLOBAL_ULTIMATE_INDICATOR | GLOBAL_ULTIMATE_BUSINESS_NAME | GLOBAL_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_STREET_ADDRESS | GLOBAL_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_AREANAME1 | GLOBAL_ULTIMATE_COUNTRY | GLOBAL_ULTIMATE_POSTCODE | FAMILY_MEMBERS | HIERARCHY_CODE | TICKER_SYMBOL | EXCHANGE_NAME | geom | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2128682600#-75.156071#39.953738 | 2019-08-01 | HERBALIFE | NaN | 2128682600 | NaN | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19107 | 933 ARCH ST, PHILADELPHIA, PA, 19107 | 933 ARCH ST | PHILADELPHIA, PA, 19107 | -75.156071 | 39.953738 | S8HPNTSCZA | HIGH | NaN | (267) 687-8065 | NaN | NaN | WWW.HERBALIFE.COM | NaN | NaN | 5912 | NaN | 59129901 | DRUG STORES | NaN | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.15607 39.95374) |
1 | 2154275062#-75.15871#39.935868 | 2019-08-01 | HERBALIFE | NaN | 2154275062 | NaN | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19147 | 1116 S 9TH ST, PHILADELPHIA, PA, 19147 | 1116 S 9TH ST | PHILADELPHIA, PA, 19147 | -75.158710 | 39.935868 | S7HPNTSCZA | HIGH | NaN | (267) 519-3122 | NaN | NaN | WWW.HERBALIFE.COM | NaN | NaN | 5912 | NaN | 59129901 | DRUG STORES | NaN | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.15871 39.93587) |
2 | 2038322702#-75.063221#39.946545 | 2019-08-01 | SHAKLEE DISTRIBUTOR | NaN | 2038322702 | NaN | NaN | USA | NaN | MERCHANTVILLE | CAMDEN | NEW JERSEY | NJ | 08109 | 226 POPLAR AVE, MERCHANTVILLE, NJ, 08109 | 226 POPLAR AVE | MERCHANTVILLE, NJ, 08109 | -75.063221 | 39.946545 | S7HPNTSCZA | HIGH | NaN | (856) 767-9203 | NaN | NaN | WWW.SHAKLEE.COM | NaN | NaN | 5912 | NaN | 59129901 | DRUG STORES | NaN | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.06322 39.94655) |
3 | 2038337811#-75.063855#39.949 | 2019-08-01 | RYLA COMMUNITY PHARMACY | NaN | 2038337811 | NaN | NaN | USA | NaN | MERCHANTVILLE | CAMDEN | NEW JERSEY | NJ | 08109 | 4301 MAPLE AVE, MERCHANTVILLE, NJ, 08109 | 4301 MAPLE AVE | MERCHANTVILLE, NJ, 08109 | -75.063855 | 39.949000 | S8HPNTSCZA | HIGH | NaN | (856) 320-2609 | NaN | NaN | NaN | NaN | NaN | 5912 | NaN | 59120000 | DRUG STORES AND PROPRIETARY STORES | NaN | 10230030 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.06386 39.94900) |
4 | 2017798948#-75.119533#39.941757 | 2019-08-01 | LINCOLN DRUG CO | NaN | 2017798948 | NaN | NaN | USA | NaN | CAMDEN | CAMDEN | NEW JERSEY | NJ | 08103 | 221 S BROADWAY, CAMDEN, NJ, 08103 | 221 S BROADWAY | CAMDEN, NJ, 08103 | -75.119533 | 39.941757 | T16 | MEDIUM | NaN | (856) 365-3400 | NaN | NaN | NaN | NaN | NaN | 5912 | NaN | 59120000 | DRUG STORES AND PROPRIETARY STORES | NaN | 10230030 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | POINT (-75.11953 39.94176) |
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.
ph_pharmacies = ph_pharmacies.sort_values(by='do_date', ascending=False).groupby('PB_ID').first().reset_index()
ph_pharmacies.shape
(477, 74)
Layer(ph_pharmacies,
geom_col='geom',
style=basic_style(opacity=0.75),
popup_hover=popup_element('NAME'))
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.
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 we describe how to calculate catchment areas using human mobility data.
iso_service = Isolines()
isochrones_gdf, _ = iso_service.isochrones(ph_pharmacies, [300], mode='car', geom_col='geom')
Success! Isolines created correctly
ph_pharmacies['iso_5car'] = isochrones_gdf.sort_values(by='source_id')['the_geom'].values
We'll only visualize the ten first isochrones to get a clean visualization.
Map([Layer(ph_pharmacies.iloc[:10],
geom_col='iso_5car',
style=basic_style(opacity=0.1),
legends=basic_legend('Catchment Areas')),
Layer(ph_pharmacies.iloc[:10],
geom_col='geom',
popup_hover=popup_element('NAME'),
legends=basic_legend('Pharmacies'))])
We'll now enrich the pharmacies catchment areas with demographics, POI's, and consumer spending data.
For the enrichment, we will use the CARTOframes Enrichment class. This class contains the functionality to enrich polygons and points.
Visit CARTOframes Guides for further detail.
enrichment = Enrichment()
We will use AGS premium data. In particular, we will work with the dataset ags_sociodemogr_f510a947
which contains yearly demographics data from 2019.
Here we will enrich the pharmacies isochrones with:
Catalog().country('usa').category('demographics').provider('ags').datasets.to_dataframe().head()
You can find more entities with the Global country filter. To apply that filter run: Catalog().country('glo')
slug | name | description | category_id | country_id | data_source_id | provider_id | geography_name | geography_description | temporal_aggregation | time_coverage | update_frequency | is_public_data | lang | version | category_name | provider_name | geography_id | id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | ags_consumer_pr_9f337eb8 | Consumer Profiles - United States of America (... | Segmentation of the population in sixty-eight ... | demographics | usa | consumer_profiles | ags | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | yearly | [2018-01-01, 2019-01-01) | yearly | False | eng | 2019 | Demographics | Applied Geographic Solutions | carto-do-public-data.carto.geography_usa_block... | carto-do.ags.demographics_consumerprofiles_usa... |
1 | ags_sociodemogr_f510a947 | Sociodemographics - United States of America (... | Census and ACS sociodemographic data estimated... | demographics | usa | sociodemographics | ags | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | yearly | [2019-01-01, 2020-01-01) | yearly | False | eng | 2019 | Demographics | Applied Geographic Solutions | carto-do-public-data.carto.geography_usa_block... | carto-do.ags.demographics_sociodemographics_us... |
2 | ags_businesscou_df363a87 | Business Counts - United States of America (Ce... | Business Counts database is a geographic summa... | demographics | usa | businesscounts | ags | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | yearly | [2018-01-01, 2019-01-01) | yearly | False | eng | 2019 | Demographics | Applied Geographic Solutions | carto-do-public-data.carto.geography_usa_block... | carto-do.ags.demographics_businesscounts_usa_b... |
3 | ags_consumer_sp_895a369c | Consumer Spending - United States of America (... | The Consumer Expenditure database consists of ... | demographics | usa | consumer_spending | ags | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | yearly | [2018-01-01, 2019-01-01) | yearly | False | eng | 2019 | Demographics | Applied Geographic Solutions | carto-do-public-data.carto.geography_usa_block... | carto-do.ags.demographics_consumerspending_usa... |
4 | ags_crimerisk_bb3e4fb3 | Crime Risk - United States of America (Census ... | Using advanced statistical methodologies and a... | demographics | usa | crimerisk | ags | Census Block Group - United States of America | None | yearly | None | yearly | False | eng | 2020 | Demographics | Applied Geographic Solutions | carto-do.ags.geography_usa_blockgroup_2015 | carto-do.ags.demographics_crimerisk_usa_blockg... |
dataset = Dataset.get('ags_sociodemogr_f510a947')
dataset.head()
DWLCY | HHDCY | POPCY | VPHCY1 | AGECYMED | HHDCYFAM | HOOEXMED | HUSEXAPT | LBFCYARM | LBFCYLBF | LBFCYNLF | MARCYSEP | POPCYGRP | RNTEXMED | SEXCYFEM | SEXCYMAL | VPHCYGT1 | AGECY0004 | AGECY0509 | AGECY1014 | AGECY1519 | AGECY2024 | AGECY2529 | AGECY3034 | AGECY3539 | AGECY4044 | AGECY4549 | AGECY5054 | AGECY5559 | AGECY6064 | AGECY6569 | AGECY7074 | AGECY7579 | AGECY8084 | AGECYGT15 | AGECYGT25 | AGECYGT85 | DWLCYRENT | EDUCYBACH | EDUCYGRAD | EDUCYHSCH | HINCY1015 | HINCY1520 | HINCY2025 | HINCY2530 | HINCY3035 | HINCY3540 | HINCY4045 | HINCY4550 | HINCY5060 | HINCY6075 | HINCYLT10 | HISCYHISP | HUSEX1DET | INCCYPCAP | LBFCYEMPL | LBFCYUNEM | LNIEXISOL | LNIEXSPAN | MARCYMARR | POPCYGRPI | UNECYRATE | VPHCYNONE | BLOCKGROUP | DWLCYOWNED | DWLCYVACNT | EDUCYASSOC | EDUCYLTGR9 | EDUCYSCOLL | EDUCYSHSCH | HHDCYAVESZ | HHDCYMEDAG | HHSCYLPFCH | HHSCYLPMCH | HHSCYMCFCH | HINCY10025 | HINCY12550 | HINCY15020 | HINCY75100 | HINCYGT200 | HINCYMED24 | HINCYMED25 | HINCYMED35 | HINCYMED45 | HINCYMED55 | HINCYMED65 | HINCYMED75 | INCCYAVEHH | INCCYMEDFA | INCCYMEDHH | LBFCYPOP16 | MARCYDIVOR | MARCYNEVER | MARCYWIDOW | RCHCYAMNHS | RCHCYASNHS | RCHCYBLNHS | RCHCYHANHS | RCHCYMUNHS | RCHCYOTNHS | RCHCYWHNHS | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 5 | 5 | 6 | 0 | 64.00 | 1 | 63749 | 0 | 0 | 0 | 6 | 0 | 0 | 0 | 5 | 1 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 2 | 0 | 0 | 0 | 6 | 6 | 1 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 2 | 53754 | 0 | 0 | 0 | 0 | 6 | 0 | 0.00 | 0 | 010159819011 | 5 | 0 | 0 | 1 | 0 | 0 | 1.2 | 61.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 67500 | 67500 | 67500 | 67500 | 64504 | 67499 | 67499 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
1 | 2 | 2 | 5 | 1 | 36.50 | 2 | 124999 | 0 | 0 | 2 | 1 | 0 | 0 | 0 | 3 | 2 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 33026 | 2 | 0 | 0 | 0 | 2 | 0 | 0.00 | 0 | 010159819021 | 2 | 0 | 0 | 1 | 2 | 0 | 2.5 | 54.0 | 1 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 87500 | 0 | 87500 | 0 | 0 | 82566 | 87499 | 87499 | 3 | 0 | 1 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 2 |
2 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 010159819031 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
3 | 21 | 11 | 22 | 4 | 64.00 | 6 | 74999 | 0 | 0 | 10 | 10 | 1 | 0 | 449 | 9 | 13 | 5 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 3 | 1 | 2 | 0 | 7 | 1 | 1 | 0 | 20 | 18 | 2 | 6 | 1 | 1 | 8 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 2 | 2 | 2 | 0 | 9 | 16647 | 10 | 0 | 0 | 0 | 0 | 0 | 0.00 | 2 | 010970004011 | 5 | 10 | 1 | 0 | 3 | 4 | 2.0 | 61.5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5000 | 60000 | 30000 | 50000 | 12500 | 33294 | 49999 | 27499 | 20 | 4 | 13 | 2 | 0 | 0 | 22 | 0 | 0 | 0 | 0 |
4 | 0 | 0 | 959 | 0 | 18.91 | 0 | 0 | 0 | 0 | 378 | 581 | 0 | 959 | 0 | 585 | 374 | 0 | 0 | 0 | 0 | 488 | 430 | 31 | 5 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 959 | 41 | 2 | 0 | 7 | 3 | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 36 | 0 | 3753 | 346 | 32 | 0 | 0 | 0 | 0 | 8.47 | 0 | 010970036051 | 0 | 0 | 3 | 0 | 10 | 4 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 959 | 0 | 959 | 0 | 5 | 53 | 230 | 0 | 25 | 0 | 609 |
5 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 020900018001 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
6 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 040030014021 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
7 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 040030014022 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
8 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 040129800001 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
9 | 0 | 0 | 0 | 0 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | 0 | 040139801001 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
We explore the variables to identify the ones we're interested in.
Variables in a dataset are uniquely identified by their slug.
dataset.variables.to_dataframe().head()
slug | name | description | db_type | agg_method | column_name | variable_group_id | dataset_id | id | |
---|---|---|---|---|---|---|---|---|---|
0 | BLOCKGROUP_108673f9 | BLOCKGROUP | Geographic Identifier | STRING | None | BLOCKGROUP | None | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... |
1 | POPCY_5e23b8f4 | Total Population | Population (2019A) | INTEGER | SUM | POPCY | None | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... |
2 | POPCYGRP_55c4a2e5 | POPCYGRP | Population in Group Quarters (2019A) | INTEGER | SUM | POPCYGRP | None | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... |
3 | POPCYGRPI_d37c4ec | POPCYGRPI | Institutional Group Quarters Population (2019A) | INTEGER | SUM | POPCYGRPI | None | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... |
4 | AGECY0004_a67ddb4f | AGECY0004 | Population age 0-4 (2019A) | INTEGER | SUM | AGECY0004 | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... | carto-do.ags.demographics_sociodemographics_us... |
We'll select:
vars_enrichment = ['POPCY_5e23b8f4', 'AGECY6064_d54c2315', 'AGECY6569_ad369d43', 'AGECY7074_74eb7531',
'AGECY7579_c91cb67', 'AGECY8084_ab1079a8', 'AGECYGT85_a0959a08', 'INCCYMEDHH_b80a7a7b',
'HINCYMED65_37a430a4', 'HINCYMED75_2ebf01e5']
ph_pharmacies_enriched = enrichment.enrich_polygons(
ph_pharmacies,
variables=vars_enrichment,
geom_col='iso_5car'
)
ph_pharmacies_enriched.head()
PB_ID | geoid | do_date | NAME | BRANDNAME | TRADE_NAME | FRANCHISE_NAME | ISO3 | AREANAME4 | AREANAME3 | AREANAME2 | AREANAME1 | STABB | POSTCODE | FORMATTEDADDRESS | MAINADDRESSLINE | ADDRESSLASTLINE | LONGITUDE | LATITUDE | GEORESULT | CONFIDENCE_CODE | COUNTRY_ACCESS_CODE | TEL_NUM | FAXNUM | HTTP | OPEN_24H | BUSINESS_LINE | SIC1 | SIC2 | SIC8 | SIC8_DESCRIPTION | ALT_INDUSTRY_CODE | MICODE | TRADE_DIVISION | GROUP | CLASS | SUB_CLASS | EMPLOYEE_HERE | EMPLOYEE_COUNT | YEAR_START | SALES_VOLUME_LOCAL | SALES_VOLUME_US_DOLLARS | CURRENCY_CODE | AGENT_CODE | LEGAL_STATUS_CODE | STATUS_CODE | SUBSIDIARY_INDICATOR | PARENT_BUSINESS_NAME | PARENT_ADDRESS | PARENT_STREET_ADDRESS | PARENT_AREANAME3 | PARENT_AREANAME1 | PARENT_COUNTRY | PARENT_POSTCODE | DOMESTIC_ULTIMATE_BUSINESS_NAME | DOMESTIC_ULTIMATE_ADDRESS | DOMESTIC_ULTIMATE_STREET_ADDRESS | DOMESTIC_ULTIMATE_AREANAME3 | DOMESTIC_ULTIMATE_AREANAME1 | DOMESTIC_ULTIMATE_POSTCODE | GLOBAL_ULTIMATE_INDICATOR | GLOBAL_ULTIMATE_BUSINESS_NAME | GLOBAL_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_STREET_ADDRESS | GLOBAL_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_AREANAME1 | GLOBAL_ULTIMATE_COUNTRY | GLOBAL_ULTIMATE_POSTCODE | FAMILY_MEMBERS | HIERARCHY_CODE | TICKER_SYMBOL | EXCHANGE_NAME | geom | iso_5car | POPCY | AGECY8084 | AGECYGT85 | AGECY6569 | AGECY7579 | AGECY7074 | AGECY6064 | HINCYMED65 | HINCYMED75 | INCCYMEDHH | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1114689327 | 1114689327#-75.186601#39.918301 | 2020-08-01 | WALGREEN EASTERN CO., INC. | WALGREENS | WALGREENS | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19145-4122 | 2310 W OREGON AVE, PHILADELPHIA, PA, 19145-4122 | 2310 W OREGON AVE | PHILADELPHIA, PA, 19145-4122 | -75.186601 | 39.918301 | S8HPNTSCZA | HIGH | 1.0 | (215) 468-2481 | NaN | NaN | WWW.WALLGREENSBOOTSALLIANCE.COM | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59129901 | DRUG STORES | 446110.0 | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 40.0 | 0.0 | 0.0 | 0.0 | 0.0 | NaN | G | 0.0 | 2.0 | 0.0 | WALGREEN EASTERN CO., INC. | 200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,... | 200 WILMOT RD | DEERFIELD | ILLINOIS | USA | 600154620.0 | WALGREENS BOOTS ALLIANCE, INC. | 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145 | 108 WILMOT RD | DEERFIELD | ILLINOIS | 600155145.0 | N | WALGREENS BOOTS ALLIANCE, INC. | 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,... | 108 WILMOT RD | DEERFIELD | ILLINOIS | USA | 600155145.0 | 9551.0 | 3.0 | NaN | NaN | POINT (-75.18660 39.91830) | MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ... | 15037.340234 | 293.522613 | 310.290651 | 783.930533 | 454.807673 | 599.859593 | 922.264170 | 50981.227273 | 38971.636364 | 55397.409091 |
1 | 1114942469 | 1114942469#-75.160179#39.970478 | 2020-08-01 | TANG PHARMACY IV | NaN | TANG PHARMACY IV | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19130-1604 | 900 N BROAD ST, PHILADELPHIA, PA, 19130-1604 | 900 N BROAD ST | PHILADELPHIA, PA, 19130-1604 | -75.160179 | 39.970478 | S8HPNTSCZA | HIGH | 1.0 | (215) 235-1400 | NaN | NaN | NaN | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59120000 | DRUG STORES AND PROPRIETARY STORES | 446110.0 | 10230030 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 4.0 | 4.0 | 2012.0 | 610000.0 | 610000.0 | 20.0 | G | 3.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.16018 39.97048) | MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ... | 54623.530980 | 813.192570 | 983.703376 | 2391.863557 | 1246.655119 | 1799.527805 | 2877.782715 | 46537.368421 | 37788.385965 | 49895.807018 |
2 | 1115024609 | 1115024609#-75.067035#39.935705 | 2020-08-01 | AMERICARE 1 PHARMACY | NaN | NaN | NaN | USA | NaN | MERCHANTVILLE | CAMDEN | NEW JERSEY | NJ | 08109-4803 | 5115 ROUTE 38, MERCHANTVILLE, NJ, 08109-4803 | 5115 ROUTE 38 | MERCHANTVILLE, NJ, 08109-4803 | -75.067035 | 39.935705 | S8HPNTSCZA | HIGH | 1.0 | (215) 821-2720 | NaN | NaN | WWW.ACAREPHARMACY.COM | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59129901 | DRUG STORES | 446110.0 | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 5.0 | 5.0 | 2011.0 | 616316.0 | 616316.0 | 20.0 | G | 13.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.06704 39.93570) | MULTIPOLYGON (((-75.10743 39.94440, -75.10717 ... | 11454.933682 | 194.094570 | 219.712464 | 510.703182 | 295.695152 | 426.563198 | 636.486082 | 41940.346154 | 34227.653846 | 50875.269231 |
3 | 1115289380 | 1115289380#-75.148596#39.970177 | 2020-08-01 | GET WELL PHARMACY | LEADER DRUG STORE | LEADER DRUG STORE | LEADER DRUG STORES | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19123-1313 | 708 W GIRARD AVE, PHILADELPHIA, PA, 19123-1313 | 708 W GIRARD AVE | PHILADELPHIA, PA, 19123-1313 | -75.148596 | 39.970177 | S8HPNTSCZA | HIGH | 1.0 | (215) 629-5566 | (215) 629-5567 | NaN | NaN | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59129901 | DRUG STORES | 446110.0 | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 6.0 | 0.0 | 0.0 | 0.0 | 0.0 | NaN | G | 0.0 | 2.0 | 0.0 | GET WELL PHARMACY | 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... | 4511 N 5TH ST | PHILADELPHIA | PENNSYLVANIA | USA | 191402309.0 | GET WELL PHARMACY | 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... | 4511 N 5TH ST | PHILADELPHIA | PENNSYLVANIA | 191402309.0 | N | GET WELL PHARMACY | 4511 N 5TH ST, PHILADELPHIA, PENNSYLVANIA, 191... | 4511 N 5TH ST | PHILADELPHIA | PENNSYLVANIA | USA | 191402309.0 | 2.0 | 1.0 | NaN | NaN | POINT (-75.14860 39.97018) | MULTIPOLYGON (((-75.17043 39.97375, -75.16983 ... | 47722.338755 | 754.767397 | 894.095294 | 2154.851189 | 1133.520312 | 1662.060943 | 2561.433928 | 45865.367347 | 36400.428571 | 47234.571429 |
4 | 1116003062 | 1116003062#-75.096649#39.906629 | 2020-08-01 | CVS PHARMACY, INC. | CVS | CVS | NaN | USA | NaN | HADDON TOWNSHIP | CAMDEN | NEW JERSEY | NJ | 08104 | 3002 MOUNT EPHRAIM AVE, HADDON TOWNSHIP, NJ, 0... | 3002 MOUNT EPHRAIM AVE | HADDON TOWNSHIP, NJ, 08104 | -75.096649 | 39.906629 | S5HPNTSCZA | HIGH | 1.0 | (856) 854-9163 | NaN | NaN | WWW.CVS.COM | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59120000 | DRUG STORES AND PROPRIETARY STORES | 446110.0 | 10230030 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 25.0 | 0.0 | 0.0 | 0.0 | 0.0 | NaN | G | 0.0 | 2.0 | 0.0 | CVS PHARMACY, INC. | 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,... | 1 CVS DR | WOONSOCKET | RHODE ISLAND | USA | 28956195.0 | CVS HEALTH CORPORATION | 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195 | 1 CVS DR | WOONSOCKET | RHODE ISLAND | 28956195.0 | N | CVS HEALTH CORPORATION | 1 CVS DR, WOONSOCKET, RHODE ISLAND, 028956195,... | 1 CVS DR | WOONSOCKET | RHODE ISLAND | USA | 28956195.0 | 13290.0 | 2.0 | NaN | NaN | POINT (-75.09665 39.90663) | MULTIPOLYGON (((-75.12031 39.90440, -75.11971 ... | 13244.490216 | 153.927121 | 179.820079 | 533.225761 | 236.796307 | 409.091195 | 686.861691 | 48903.484848 | 40511.363636 | 53548.757576 |
ph_pharmacies = ph_pharmacies_enriched.copy()
ph_pharmacies['pop_60plus'] = ph_pharmacies[['AGECY8084', 'AGECYGT85', 'AGECY6569', 'AGECY7579', 'AGECY7074', 'AGECY6064']].sum(1)
ph_pharmacies.drop(columns=['AGECY8084', 'AGECYGT85', 'AGECY6569', 'AGECY7579', 'AGECY7074', 'AGECY6064'], inplace=True)
We will use Pitney Bowes' Consumer Points of Interest premium dataset.
We are interested in knowing how many of the following POIs can be found in each isochrone:
These POI's will be considered as an indicator of personal care awareness in a specific area.
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
).
sample.loc[sample['TRADE_DIVISION'] == 'DIVISION I. - SERVICES', 'SUB_CLASS'].unique()
array(['LABOR ORGANIZATIONS', 'MEMBERSHIP ORGANIZATIONS, NEC', 'FLATS, APARTMENT COMPLEX', 'HOTEL, MOTEL - UNSPECIFIED', 'MEDIA FACILITY', 'COLLEGES AND UNIVERSITIES', 'VOCATIONAL SCHOOLS, NEC/VOCATIONAL TRAINING', 'LIBRARIES', 'SCHOOL', 'GENERAL MEDICAL AND SURGICAL HOSPITALS', 'OFFICES AND CLINICS OF DENTISTS/ DENTIST', 'CIVIC AND SOCIAL ASSOCIATIONS', 'OFFICES AND CLINICS OF MEDICAL DOCTORS', 'RELIGIOUS ORGANIZATIONS', 'PARK AND RECREATION AREA - UNSPECIFIED', 'SKILLED NURSING CARE FACILITIES', 'NURSING AND PERSONAL CARE, NEC', 'RETIREMENT COMMUNITY', 'SPECIALTY OUTPATIENT CLINICS, NEC', 'PASSENGER CAR RENTAL/RENT-A-CAR FACILITY', 'GENERAL AUTOMOTIVE REPAIR SHOPS/GENERAL CAR REPAIR AND SERVICING', 'LEGAL SERVICES', 'TOP AND BODY REPAIR AND PAINT SHOPS/BODYSHOPS', 'CARWASHES', 'HEALTH AND ALLIED SERVICES, NEC', 'SCHOOLS AND EDUCATIONAL SERVICES/SCHOOL - UNSPECIFIED', 'LAUNDRY AND GARMENT SERVICES, NEC', 'MISCELLANEOUS PERSONAL SERVICES', 'PHOTOGRAPHIC STUDIOS, PORTRAIT', 'PRIMARY SCHOOL', 'INDIVIDUAL AND FAMILY SERVICES', 'RESIDENTIAL CARE', 'CHILD DAY CARE SERVICES/CHILD CARE CENTER', 'BARBER SHOPS/HAIRDRESSERS AND BARBERS', 'BEAUTY SHOPS/BEAUTY SALON', 'FUNERAL SERVICE AND CREMATORIES', 'OFFICES OF HEALTH PRACTITIONER', 'ENTERTAINERS AND ENTERTAINMENT GROUPS', 'AUTO EXHAUST SYSTEM REPAIR SHOPS', 'THEATRICAL PRODUCERS AND SERVICES', 'PHYSICAL FITNESS FACILITIES/FITNESS CLUB AND CENTER', 'AMUSEMENT AND RECREATION, NEC/AMUSEMENT PLACE', 'AUTOMOBILE PARKING/OPEN PARKING AREA', 'MUSEUMS AND ART GALLERIES', 'ELEMENTARY AND SECONDARY SCHOOLS', 'MOTION PICTURE THEATERS, EXCEPT DRIVE-IN', 'HOME HEALTH CARE SERVICES', 'OFFICES AND CLINICS OF OSTEOPATHIC PHYSICIANS', 'SOCIAL SERVICES, NEC', 'DANCE STUDIOS, SCHOOLS, AND HALLS', 'ENTERTAINMENT'], dtype=object)
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.
ph_pharmacies_enriched = enrichment.enrich_polygons(
ph_pharmacies,
variables=['SUB_CLASS_10243439'],
aggregation='COUNT',
geom_col='iso_5car',
filters={Variable.get('SUB_CLASS_10243439').id : "= 'BEAUTY SHOPS/BEAUTY SALON'"}
)
ph_pharmacies = ph_pharmacies_enriched.rename(columns={'SUB_CLASS_y':'n_beauty_pois'})
ph_pharmacies_enriched = enrichment.enrich_polygons(
ph_pharmacies,
variables=['SUB_CLASS_10243439'],
aggregation='COUNT',
geom_col='iso_5car',
filters={Variable.get('SUB_CLASS_10243439').id : "= 'MEMBERSHIP SPORTS AND RECREATION CLUBS/CLUB AND ASSOCIATION - UNSPECIFIED'"}
)
ph_pharmacies = ph_pharmacies_enriched.rename(columns={'SUB_CLASS':'n_gym_pois'})
ph_pharmacies['n_pois_personal_care'] = ph_pharmacies['n_beauty_pois'] + ph_pharmacies['n_gym_pois']
ph_pharmacies.drop(columns=['n_beauty_pois', 'n_gym_pois'], inplace=True)
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.
We are interested in spending in:
dataset = Dataset.get('ags_consumer_sp_dbabddfb')
dataset.variables.to_dataframe().head()
slug | name | description | db_type | agg_method | column_name | variable_group_id | dataset_id | id | |
---|---|---|---|---|---|---|---|---|---|
0 | BLOCKGROUP_9c78947b | BLOCKGROUP | Geographic Identifier | STRING | None | BLOCKGROUP | None | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... |
1 | HHDCY_11b160d2 | Number of households | Households (2019A) | INTEGER | SUM | HHDCY | None | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... |
2 | XCYAP1_ef233ec0 | XCYAP1 | Men's Apparel (Ave Hhd Exp) | FLOAT | AVG | XCYAP1 | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... |
3 | XCYAP2_762a6f7a | XCYAP2 | Boys Apparel (Ave Hhd Exp) | FLOAT | AVG | XCYAP2 | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... |
4 | XCYAP3_12d5fec | XCYAP3 | Women's Apparel (Ave Hhd Exp) | FLOAT | AVG | XCYAP3 | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... | carto-do.ags.demographics_consumerspending_usa... |
The variables we're interested in are:
XCYHC2
Health care services expenditureXCYPC3
Personal care services expenditureXCYPC4
Personal care products expenditureVariable.get('XCYHC2_18141567').to_dict()
{'slug': 'XCYHC2_18141567', 'name': 'XCYHC2', 'description': 'Health Care Services (Ave Hhd Exp)', 'db_type': 'FLOAT', 'agg_method': 'AVG', 'column_name': 'XCYHC2', 'variable_group_id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020.consumer_spending_by_product_category', 'dataset_id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020', 'id': 'carto-do.ags.demographics_consumerspending_usa_blockgroup_2015_yearly_2020.XCYHC2'}
ph_pharmacies_enriched = enrichment.enrich_polygons(
ph_pharmacies,
variables=['XCYPC3_7d26d739', 'XCYPC4_e342429a', 'XCYHC2_18141567'],
geom_col='iso_5car'
)
We rename the new columns to give them a more descriptive name.
ph_pharmacies = ph_pharmacies_enriched.rename(columns={'XCYHC2':'health_care_services_exp',
'XCYPC3':'personal_care_services_exp',
'XCYPC4':'personal_care_products_exp'})
ph_pharmacies.head(2)
PB_ID | geoid | do_date | NAME | BRANDNAME | TRADE_NAME | FRANCHISE_NAME | ISO3 | AREANAME4 | AREANAME3 | AREANAME2 | AREANAME1 | STABB | POSTCODE | FORMATTEDADDRESS | MAINADDRESSLINE | ADDRESSLASTLINE | LONGITUDE | LATITUDE | GEORESULT | CONFIDENCE_CODE | COUNTRY_ACCESS_CODE | TEL_NUM | FAXNUM | HTTP | OPEN_24H | BUSINESS_LINE | SIC1 | SIC2 | SIC8 | SIC8_DESCRIPTION | ALT_INDUSTRY_CODE | MICODE | TRADE_DIVISION | GROUP | CLASS | SUB_CLASS_x | EMPLOYEE_HERE | EMPLOYEE_COUNT | YEAR_START | SALES_VOLUME_LOCAL | SALES_VOLUME_US_DOLLARS | CURRENCY_CODE | AGENT_CODE | LEGAL_STATUS_CODE | STATUS_CODE | SUBSIDIARY_INDICATOR | PARENT_BUSINESS_NAME | PARENT_ADDRESS | PARENT_STREET_ADDRESS | PARENT_AREANAME3 | PARENT_AREANAME1 | PARENT_COUNTRY | PARENT_POSTCODE | DOMESTIC_ULTIMATE_BUSINESS_NAME | DOMESTIC_ULTIMATE_ADDRESS | DOMESTIC_ULTIMATE_STREET_ADDRESS | DOMESTIC_ULTIMATE_AREANAME3 | DOMESTIC_ULTIMATE_AREANAME1 | DOMESTIC_ULTIMATE_POSTCODE | GLOBAL_ULTIMATE_INDICATOR | GLOBAL_ULTIMATE_BUSINESS_NAME | GLOBAL_ULTIMATE_ADDRESS | GLOBAL_ULTIMATE_STREET_ADDRESS | GLOBAL_ULTIMATE_AREANAME3 | GLOBAL_ULTIMATE_AREANAME1 | GLOBAL_ULTIMATE_COUNTRY | GLOBAL_ULTIMATE_POSTCODE | FAMILY_MEMBERS | HIERARCHY_CODE | TICKER_SYMBOL | EXCHANGE_NAME | geom | iso_5car | POPCY | HINCYMED65 | HINCYMED75 | INCCYMEDHH | pop_60plus | n_pois_personal_care | health_care_services_exp | personal_care_services_exp | personal_care_products_exp | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1114689327 | 1114689327#-75.186601#39.918301 | 2020-08-01 | WALGREEN EASTERN CO., INC. | WALGREENS | WALGREENS | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19145-4122 | 2310 W OREGON AVE, PHILADELPHIA, PA, 19145-4122 | 2310 W OREGON AVE | PHILADELPHIA, PA, 19145-4122 | -75.186601 | 39.918301 | S8HPNTSCZA | HIGH | 1.0 | (215) 468-2481 | NaN | NaN | WWW.WALLGREENSBOOTSALLIANCE.COM | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59129901 | DRUG STORES | 446110.0 | 10010700 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 40.0 | 0.0 | 0.0 | 0.0 | 0.0 | NaN | G | 0.0 | 2.0 | 0.0 | WALGREEN EASTERN CO., INC. | 200 WILMOT RD, DEERFIELD, ILLINOIS, 600154620,... | 200 WILMOT RD | DEERFIELD | ILLINOIS | USA | 600154620.0 | WALGREENS BOOTS ALLIANCE, INC. | 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145 | 108 WILMOT RD | DEERFIELD | ILLINOIS | 600155145.0 | N | WALGREENS BOOTS ALLIANCE, INC. | 108 WILMOT RD, DEERFIELD, ILLINOIS, 600155145,... | 108 WILMOT RD | DEERFIELD | ILLINOIS | USA | 600155145.0 | 9551.0 | 3.0 | NaN | NaN | POINT (-75.18660 39.91830) | MULTIPOLYGON (((-75.19996 39.93393, -75.19953 ... | 15037.340234 | 50981.227273 | 38971.636364 | 55397.409091 | 3364.675232 | 541.0 | 925.483182 | 349.088636 | 302.686364 |
1 | 1114942469 | 1114942469#-75.160179#39.970478 | 2020-08-01 | TANG PHARMACY IV | NaN | TANG PHARMACY IV | NaN | USA | NaN | PHILADELPHIA | PHILADELPHIA | PENNSYLVANIA | PA | 19130-1604 | 900 N BROAD ST, PHILADELPHIA, PA, 19130-1604 | 900 N BROAD ST | PHILADELPHIA, PA, 19130-1604 | -75.160179 | 39.970478 | S8HPNTSCZA | HIGH | 1.0 | (215) 235-1400 | NaN | NaN | NaN | NaN | DRUG STORES AND PROPRIETARY STORES | 5912 | NaN | 59120000 | DRUG STORES AND PROPRIETARY STORES | 446110.0 | 10230030 | DIVISION G. - RETAIL TRADE | MISCELLANEOUS RETAIL | DRUG STORES AND PROPRIETARY STORES | DRUG STORES AND PROPRIETARY STORES/PHARMACY | 4.0 | 4.0 | 2012.0 | 610000.0 | 610000.0 | 20.0 | G | 3.0 | 0.0 | 0.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | N | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 0.0 | NaN | NaN | POINT (-75.16018 39.97048) | MULTIPOLYGON (((-75.17592 39.96826, -75.17550 ... | 54623.530980 | 46537.368421 | 37788.385965 | 49895.807018 | 10112.725143 | 1442.0 | 833.376667 | 321.834211 | 279.067719 |
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.
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.
cmap = Map(Layer(ph_pharmacies,
geom_col='geom',
style=color_category_style('SIC8_DESCRIPTION', size=4, opacity=0.85, palette='safe', stroke_width=0.15),
widgets=[formula_widget(
'PB_ID',
operation='COUNT',
title='Total number of pharmacies',
description='Keep track of the total amount of pharmacies that meet the ranges selected on the widgets below'),
histogram_widget(
'pop_60plus',
title='Population 60+',
description='Select a range of values to filter',
buckets=15
),
histogram_widget(
'HINCYMED65',
title='Household income 65-74',
buckets=15
),
histogram_widget(
'HINCYMED75',
title='Household income 75+',
buckets=15
),
histogram_widget(
'n_pois_personal_care',
title='Number of personal care POIs',
buckets=15
),
histogram_widget(
'personal_care_products_exp',
title='Expenditure in personal care products ($)',
buckets=15
)],
legends=color_category_legend(
title='Pharmacies',
description='Type of store'),
popup_hover=[popup_element('NAME', title='Name')]
),
viewport={'zoom': 11}
)
cmap
cmap.publish('ph_pharmacies_dashboard', password='MY_PASS', if_exists='replace')
{'id': '6765be0f-02da-4bec-b80c-63ec9bcd5624', 'url': 'https://cartoframes-org.carto.com/u/cartoframes/kuviz/6765be0f-02da-4bec-b80c-63ec9bcd5624', 'name': 'ph_pharmacies_dashboard', 'privacy': 'password'}