{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "Y0IPjMQvr_2h" }, "source": [ "## Filtering a Data Observatory dataset using \"Who's On First\" in CARTOFrames" ] }, { "cell_type": "markdown", "metadata": { "id": "VOVRcqXNrnUu" }, "source": [ "This notebook illustrates how to use the admin. region geometries from Who's on First (public data) to filter a dataset from CARTO's [Data Observatory](https://carto.com/spatial-data-catalog/) using [CARTOFrames](https://carto.com/cartoframes/) methods.\n", "\n", "The notebook is organized as follows:\n", "0. Setup account\n", "1. Access a dataset from a Data Observatory subscription to be filtered\n", "2. Who's on First for filtering data in cities\n", "\n", "**Documentation**\n", "- CARTO Spatial Data Catalogue - [link](https://carto.com/spatial-data-catalog/browser/)\n", "- CARTOFrames technical documentation - [link](https://carto.com/developers/cartoframes/)\n", "- \"Who's on First\" GeoJSON data product - [link](https://carto.com/spatial-data-catalog/browser/geography/wof_geojson_4e78587c/data)" ] }, { "cell_type": "markdown", "metadata": { "id": "nN8F02lPsOBU" }, "source": [ "### 0. Setup" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "bZcjZ3X3sUMr" }, "outputs": [], "source": [ "import geopandas as gpd\n", "import pandas as pd\n", "\n", "from cartoframes.auth import set_default_credentials\n", "from cartoframes.data.observatory import *\n", "from cartoframes.viz import *\n", "\n", "pd.set_option('display.max_columns', None)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "id": "n02GAhWlsXbM" }, "outputs": [], "source": [ "set_default_credentials('creds.json')" ] }, { "cell_type": "markdown", "metadata": { "id": "gdNCauTQtarh" }, "source": [ "### 1. Access a dataset from a Data Observatory subscription to be filtered" ] }, { "cell_type": "markdown", "metadata": { "id": "GRlKWu8xAfGX" }, "source": [ "First, we check our data subscriptions from the Data Observatory to select which dataset we want to filter." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "JI7RCXrnAsNc", "outputId": "9d409e6c-00c3-48cc-afab-f3afc7563fcd" }, "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", "
slugnamedescriptioncategory_idcountry_iddata_source_idprovider_idgeography_namegeography_descriptiontemporal_aggregationtime_coverageupdate_frequencyis_public_datalangversioncategory_nameprovider_namegeography_idid
0lg_parcels_ef7cdae4Parcels - United States of America (Parcel)Nationwide dataset with data from 140 million ...housingusaparcelslandgridParcel - United States of AmericaLand parcels compiled by LandgridmonthlyNonemonthlyFalseengv1HousingLandgridcarto-do.landgrid.geography_usa_parcel_v1carto-do.landgrid.housing_parcels_usa_parcel_v...
1sg_social_dist_667d8e8eSocial Distancing Metrics - United States of A...Due to the COVID-19 pandemic, people are curre...covid19usasocial_distancingsafegraphCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...dailyNonedailyFalseengv1Covid-19SafeGraphcarto-do-public-data.carto.geography_usa_block...carto-do.safegraph.covid19_socialdistancing_us...
2ine_sociodemogr_c8c87afeSociodemographics (Spain, Census Sections)Sociodemographic data from the Instituto Nacio...demographicsespsociodemographicsesp_ineCensus Section (Spain)2020 Census Sections, from the Instituto Nacio...yearlyNoneNoneTrueeng2011DemographicsInstituto Nacional de Estadísticacarto-do-public-data.esp_ine.geography_esp_cen...carto-do-public-data.esp_ine.demographics_soci...
3expn_sociodemogr_25b78bbaSociodemographics - Thailand (Grid 250m)Worldview combines Experian's own datasets wit...demographicsthasociodemographicsexperianGrid 250m - ThailandExperian 250mx250m grid cellsyearly[2019-01-01, 2020-01-01)NoneFalseeng2020DemographicsExperiancarto-do.experian.geography_tha_grid_v1carto-do.experian.demographics_sociodemographi...
4mc_geographic__7980c5c3Geographic Insights - United States of America...Geographic Insights validate, evaluate and ben...financialusageographic_insightsmastercardCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...monthly[2019-01-01, 2020-01-01)monthlyFalseengv1FinancialMastercardcarto-do-public-data.carto.geography_usa_block...carto-do.mastercard.financial_geographicinsigh...
5acs_sociodemogr_b758e778Sociodemographics - United States of America (...The American Community Survey (ACS) is an ongo...demographicsusasociodemographicsusa_acsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...5yrs[2013-01-01, 2018-01-01)NoneTrueeng20132017DemographicsAmerican Community Surveycarto-do-public-data.carto.geography_usa_block...carto-do-public-data.usa_acs.demographics_soci...
6can_sociodemogr_affc7f83Sociodemographics - Canada (Dissemination Area)Sociodemographic data from Statistics Canada. ...demographicscansociodemographicscan_statisticsDissemination Area - Canada (2016)Canada - Dissemination Area5yrsNoneNoneTrueeng2016DemographicsStatistics Canadacarto-do-public-data.carto.geography_can_disse...carto-do-public-data.can_statistics.demographi...
7can_employment_e3bbbb6Employment And Income - Canada (Dissemination ...Employment and income data from Statistics Can...demographicscanemploymentcan_statisticsDissemination Area - Canada (2016)Canada - Dissemination Area5yrsNoneNoneTrueeng2016DemographicsStatistics Canadacarto-do-public-data.carto.geography_can_disse...carto-do-public-data.can_statistics.demographi...
8wp_population_cd347169Population Mosaics - Switzerland (Grid 1km, 2020)Mosaiced 1km resolution global datasets. The m...demographicschepopulationworldpopGrid 1km - SwitzerlandGlobal grid at aprox. 1-kilometer resolution (...yearly[2020-01-01, 2021-01-01)NoneTrueeng2020DemographicsWorldPopcarto-do-public-data.worldpop.geography_che_gr...carto-do-public-data.worldpop.demographics_pop...
9wp_population_35d01fd4Population Mosaics - Belgium (Grid 100m, 2015)Mosaiced 100m resolution global datasets. The ...demographicsbelpopulationworldpopGrid 100m - BelgiumGlobal grid at aprox. 100-meter resolution (0....yearly[2015-01-01, 2016-01-01)NoneTrueeng2015DemographicsWorldPopcarto-do-public-data.worldpop.geography_bel_gr...carto-do-public-data.worldpop.demographics_pop...
10wp_population_6bf077c7Population Mosaics - Italy (Grid 1km, 2020)Mosaiced 1km resolution global datasets. The m...demographicsitapopulationworldpopGrid 1km - ItalyGlobal grid at aprox. 1-kilometer resolution (...yearly[2020-01-01, 2021-01-01)NoneTrueeng2020DemographicsWorldPopcarto-do-public-data.worldpop.geography_ita_gr...carto-do-public-data.worldpop.demographics_pop...
11spa_geosocial_s_d5dc42aeGeosocial Segments - United States of America ...By analysing feeds from Twitter, Instagram, Me...behavioralusageosocial_segmentsspatial_aiCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...quarterly[2020-01-01, 2020-04-01)quarterlyFalseengv1BehavioralSpatial.aicarto-do-public-data.carto.geography_usa_block...carto-do.spatial_ai.behavioral_geosocialsegmen...
12expn_consumer_se_7d6172dConsumer Segments - Russia (Grid 250m)WorldView segments has been developed to segme...demographicsrusconsumer_segmentsexperianGrid 250m - RussiaExperian 250mx250m grid cellsyearly[2019-01-01, 2020-01-01)NoneFalseeng2020DemographicsExperiancarto-do.experian.geography_rus_grid_v2carto-do.experian.demographics_consumersegment...
13expn_sociodemogr_81aa1d1eSociodemographics - Russia (Grid 250m)Worldview combines Experian's own datasets wit...demographicsrussociodemographicsexperianGrid 250m - RussiaExperian 250mx250m grid cellsyearly[2019-01-01, 2020-01-01)NoneFalseeng2020DemographicsExperiancarto-do.experian.geography_rus_grid_v2carto-do.experian.demographics_sociodemographi...
14cdb_spatial_fea_d23a5c97Spatial Features - Spain (Quadgrid 15)Spatial Features is a dataset curated by CARTO...derivedespspatial_featurescartoQuadgrid 15 - SpainGlobal Quadgrid (zoom level 15)yearlyNoneNoneTrueeng2020DerivedCARTOcarto-do-public-data.carto.geography_esp_quadg...carto-do-public-data.carto.derived_spatialfeat...
15cdb_spatial_fea_802d4d44Spatial Features - France (Quadgrid 15)Spatial Features is a dataset curated by CARTO...derivedfraspatial_featurescartoQuadgrid 15 - FranceGlobal Quadgrid (zoom level 15)yearlyNoneNoneFalseeng2020DerivedCARTOcarto-do-public-data.carto.geography_fra_quadg...carto-do.carto.derived_spatialfeatures_fra_qua...
16cdb_spatial_fea_7bd51aecSpatial Features - Guyana (Quadgrid 15)Spatial Features is a dataset curated by CARTO...derivedguyspatial_featurescartoQuadgrid 15 - GuyanaGlobal Quadgrid (zoom level 15)yearlyNoneNoneFalseeng2020DerivedCARTOcarto-do-public-data.carto.geography_guy_quadg...carto-do.carto.derived_spatialfeatures_guy_qua...
17ws_climatology_83bcb297Climatology - Japan (Grid 22km, hourly)Global climatology data providing weather stat...environmentaljpnclimatologyweather_sourceGrid 22km - GlobalCustom grid at 22 kilometer resolutionhourly[2005-01-01, 2020-01-01)yearlyFalseengv1EnvironmentalWeather Sourcecarto-do.weather_source.geography_glo_grid22km_v1carto-do.weather_source.environmental_climatol...
18uc_activity_ae564b62Activity - Philippines (Quadgrid 17)Leveraging a global panel of location signals ...human_mobilityphlactivityunacastQuadgrid 17 - PhilippinesQuad Key Grid - Level 17monthly[2019-01-01, 2020-01-01)monthlyFalseengv1Human MobilityUnacastcarto-do-public-data.carto.geography_phl_quadg...carto-do.unacast.humanmobility_activity_phl_qu...
19uc_home_and_wo_b42b8699Home And Work - Philippines (Quadgrid 17)Paired with the Activity dataset, this data pr...human_mobilityphlhome_and_workunacastQuadgridQuadgrid (multiple zoom levels)monthly[2019-01-01, 2020-01-01)monthlyFalseengv1Human MobilityUnacastcarto-do-public-data.carto.geography_phl_quadg...carto-do.unacast.humanmobility_homeandwork_phl...
20expn_sociodemogr_8d3aa47aSociodemographics - Philippines (Grid 250m)Worldview combines Experian's own datasets wit...demographicsphlsociodemographicsexperianGrid 250m - PhilippinesExperian 250mx250m grid cellsyearly[2019-01-01, 2020-01-01)NoneFalseeng2020DemographicsExperiancarto-do.experian.geography_phl_grid_v1carto-do.experian.demographics_sociodemographi...
21ws_forecast_39e1ab6aForecast - Spain (Municipality, hourly)10 and 15-day weather forecastsenvironmentalespforecastweather_sourceMunicipality (Spain)2020 Municipalities, from the Instituto Geográ...hourlyNonedailyFalseengv1EnvironmentalWeather Sourcecarto-do-public-data.esp_ign.geography_esp_mun...carto-do.weather_source.environmental_forecast...
22ws_historic_43f694ccHistoric - Spain (Municipality, hourly)Past weather data from the year 2000 to presentenvironmentalesphistoricweather_sourceMunicipality (Spain)2020 Municipalities, from the Instituto Geográ...hourlyNonedailyFalseengv1EnvironmentalWeather Sourcecarto-do-public-data.esp_ign.geography_esp_mun...carto-do.weather_source.environmental_historic...
\n", "
" ], "text/plain": [ " slug \\\n", "0 lg_parcels_ef7cdae4 \n", "1 sg_social_dist_667d8e8e \n", "2 ine_sociodemogr_c8c87afe \n", "3 expn_sociodemogr_25b78bba \n", "4 mc_geographic__7980c5c3 \n", "5 acs_sociodemogr_b758e778 \n", "6 can_sociodemogr_affc7f83 \n", "7 can_employment_e3bbbb6 \n", "8 wp_population_cd347169 \n", "9 wp_population_35d01fd4 \n", "10 wp_population_6bf077c7 \n", "11 spa_geosocial_s_d5dc42ae \n", "12 expn_consumer_se_7d6172d \n", "13 expn_sociodemogr_81aa1d1e \n", "14 cdb_spatial_fea_d23a5c97 \n", "15 cdb_spatial_fea_802d4d44 \n", "16 cdb_spatial_fea_7bd51aec \n", "17 ws_climatology_83bcb297 \n", "18 uc_activity_ae564b62 \n", "19 uc_home_and_wo_b42b8699 \n", "20 expn_sociodemogr_8d3aa47a \n", "21 ws_forecast_39e1ab6a \n", "22 ws_historic_43f694cc \n", "\n", " name \\\n", "0 Parcels - United States of America (Parcel) \n", "1 Social Distancing Metrics - United States of A... \n", "2 Sociodemographics (Spain, Census Sections) \n", "3 Sociodemographics - Thailand (Grid 250m) \n", "4 Geographic Insights - United States of America... \n", "5 Sociodemographics - United States of America (... \n", "6 Sociodemographics - Canada (Dissemination Area) \n", "7 Employment And Income - Canada (Dissemination ... \n", "8 Population Mosaics - Switzerland (Grid 1km, 2020) \n", "9 Population Mosaics - Belgium (Grid 100m, 2015) \n", "10 Population Mosaics - Italy (Grid 1km, 2020) \n", "11 Geosocial Segments - United States of America ... \n", "12 Consumer Segments - Russia (Grid 250m) \n", "13 Sociodemographics - Russia (Grid 250m) \n", "14 Spatial Features - Spain (Quadgrid 15) \n", "15 Spatial Features - France (Quadgrid 15) \n", "16 Spatial Features - Guyana (Quadgrid 15) \n", "17 Climatology - Japan (Grid 22km, hourly) \n", "18 Activity - Philippines (Quadgrid 17) \n", "19 Home And Work - Philippines (Quadgrid 17) \n", "20 Sociodemographics - Philippines (Grid 250m) \n", "21 Forecast - Spain (Municipality, hourly) \n", "22 Historic - Spain (Municipality, hourly) \n", "\n", " description category_id \\\n", "0 Nationwide dataset with data from 140 million ... housing \n", "1 Due to the COVID-19 pandemic, people are curre... covid19 \n", "2 Sociodemographic data from the Instituto Nacio... demographics \n", "3 Worldview combines Experian's own datasets wit... demographics \n", "4 Geographic Insights validate, evaluate and ben... financial \n", "5 The American Community Survey (ACS) is an ongo... demographics \n", "6 Sociodemographic data from Statistics Canada. ... demographics \n", "7 Employment and income data from Statistics Can... demographics \n", "8 Mosaiced 1km resolution global datasets. The m... demographics \n", "9 Mosaiced 100m resolution global datasets. The ... demographics \n", "10 Mosaiced 1km resolution global datasets. The m... demographics \n", "11 By analysing feeds from Twitter, Instagram, Me... behavioral \n", "12 WorldView segments has been developed to segme... demographics \n", "13 Worldview combines Experian's own datasets wit... demographics \n", "14 Spatial Features is a dataset curated by CARTO... derived \n", "15 Spatial Features is a dataset curated by CARTO... derived \n", "16 Spatial Features is a dataset curated by CARTO... derived \n", "17 Global climatology data providing weather stat... environmental \n", "18 Leveraging a global panel of location signals ... human_mobility \n", "19 Paired with the Activity dataset, this data pr... human_mobility \n", "20 Worldview combines Experian's own datasets wit... demographics \n", "21 10 and 15-day weather forecasts environmental \n", "22 Past weather data from the year 2000 to present environmental \n", "\n", " country_id data_source_id provider_id \\\n", "0 usa parcels landgrid \n", "1 usa social_distancing safegraph \n", "2 esp sociodemographics esp_ine \n", "3 tha sociodemographics experian \n", "4 usa geographic_insights mastercard \n", "5 usa sociodemographics usa_acs \n", "6 can sociodemographics can_statistics \n", "7 can employment can_statistics \n", "8 che population worldpop \n", "9 bel population worldpop \n", "10 ita population worldpop \n", "11 usa geosocial_segments spatial_ai \n", "12 rus consumer_segments experian \n", "13 rus sociodemographics experian \n", "14 esp spatial_features carto \n", "15 fra spatial_features carto \n", "16 guy spatial_features carto \n", "17 jpn climatology weather_source \n", "18 phl activity unacast \n", "19 phl home_and_work unacast \n", "20 phl sociodemographics experian \n", "21 esp forecast weather_source \n", "22 esp historic weather_source \n", "\n", " geography_name \\\n", "0 Parcel - United States of America \n", "1 Census Block Group - United States of America ... \n", "2 Census Section (Spain) \n", "3 Grid 250m - Thailand \n", "4 Census Block Group - United States of America ... \n", "5 Census Block Group - United States of America ... \n", "6 Dissemination Area - Canada (2016) \n", "7 Dissemination Area - Canada (2016) \n", "8 Grid 1km - Switzerland \n", "9 Grid 100m - Belgium \n", "10 Grid 1km - Italy \n", "11 Census Block Group - United States of America ... \n", "12 Grid 250m - Russia \n", "13 Grid 250m - Russia \n", "14 Quadgrid 15 - Spain \n", "15 Quadgrid 15 - France \n", "16 Quadgrid 15 - Guyana \n", "17 Grid 22km - Global \n", "18 Quadgrid 17 - Philippines \n", "19 Quadgrid \n", "20 Grid 250m - Philippines \n", "21 Municipality (Spain) \n", "22 Municipality (Spain) \n", "\n", " geography_description temporal_aggregation \\\n", "0 Land parcels compiled by Landgrid monthly \n", "1 Shoreline clipped TIGER/Line boundaries. More ... daily \n", "2 2020 Census Sections, from the Instituto Nacio... yearly \n", "3 Experian 250mx250m grid cells yearly \n", "4 Shoreline clipped TIGER/Line boundaries. More ... monthly \n", "5 Shoreline clipped TIGER/Line boundaries. More ... 5yrs \n", "6 Canada - Dissemination Area 5yrs \n", "7 Canada - Dissemination Area 5yrs \n", "8 Global grid at aprox. 1-kilometer resolution (... yearly \n", "9 Global grid at aprox. 100-meter resolution (0.... yearly \n", "10 Global grid at aprox. 1-kilometer resolution (... yearly \n", "11 Shoreline clipped TIGER/Line boundaries. More ... quarterly \n", "12 Experian 250mx250m grid cells yearly \n", "13 Experian 250mx250m grid cells yearly \n", "14 Global Quadgrid (zoom level 15) yearly \n", "15 Global Quadgrid (zoom level 15) yearly \n", "16 Global Quadgrid (zoom level 15) yearly \n", "17 Custom grid at 22 kilometer resolution hourly \n", "18 Quad Key Grid - Level 17 monthly \n", "19 Quadgrid (multiple zoom levels) monthly \n", "20 Experian 250mx250m grid cells yearly \n", "21 2020 Municipalities, from the Instituto Geográ... hourly \n", "22 2020 Municipalities, from the Instituto Geográ... hourly \n", "\n", " time_coverage update_frequency is_public_data lang version \\\n", "0 None monthly False eng v1 \n", "1 None daily False eng v1 \n", "2 None None True eng 2011 \n", "3 [2019-01-01, 2020-01-01) None False eng 2020 \n", "4 [2019-01-01, 2020-01-01) monthly False eng v1 \n", "5 [2013-01-01, 2018-01-01) None True eng 20132017 \n", "6 None None True eng 2016 \n", "7 None None True eng 2016 \n", "8 [2020-01-01, 2021-01-01) None True eng 2020 \n", "9 [2015-01-01, 2016-01-01) None True eng 2015 \n", "10 [2020-01-01, 2021-01-01) None True eng 2020 \n", "11 [2020-01-01, 2020-04-01) quarterly False eng v1 \n", "12 [2019-01-01, 2020-01-01) None False eng 2020 \n", "13 [2019-01-01, 2020-01-01) None False eng 2020 \n", "14 None None True eng 2020 \n", "15 None None False eng 2020 \n", "16 None None False eng 2020 \n", "17 [2005-01-01, 2020-01-01) yearly False eng v1 \n", "18 [2019-01-01, 2020-01-01) monthly False eng v1 \n", "19 [2019-01-01, 2020-01-01) monthly False eng v1 \n", "20 [2019-01-01, 2020-01-01) None False eng 2020 \n", "21 None daily False eng v1 \n", "22 None daily False eng v1 \n", "\n", " category_name provider_name \\\n", "0 Housing Landgrid \n", "1 Covid-19 SafeGraph \n", "2 Demographics Instituto Nacional de Estadística \n", "3 Demographics Experian \n", "4 Financial Mastercard \n", "5 Demographics American Community Survey \n", "6 Demographics Statistics Canada \n", "7 Demographics Statistics Canada \n", "8 Demographics WorldPop \n", "9 Demographics WorldPop \n", "10 Demographics WorldPop \n", "11 Behavioral Spatial.ai \n", "12 Demographics Experian \n", "13 Demographics Experian \n", "14 Derived CARTO \n", "15 Derived CARTO \n", "16 Derived CARTO \n", "17 Environmental Weather Source \n", "18 Human Mobility Unacast \n", "19 Human Mobility Unacast \n", "20 Demographics Experian \n", "21 Environmental Weather Source \n", "22 Environmental Weather Source \n", "\n", " geography_id \\\n", "0 carto-do.landgrid.geography_usa_parcel_v1 \n", "1 carto-do-public-data.carto.geography_usa_block... \n", "2 carto-do-public-data.esp_ine.geography_esp_cen... \n", "3 carto-do.experian.geography_tha_grid_v1 \n", "4 carto-do-public-data.carto.geography_usa_block... \n", "5 carto-do-public-data.carto.geography_usa_block... \n", "6 carto-do-public-data.carto.geography_can_disse... \n", "7 carto-do-public-data.carto.geography_can_disse... \n", "8 carto-do-public-data.worldpop.geography_che_gr... \n", "9 carto-do-public-data.worldpop.geography_bel_gr... \n", "10 carto-do-public-data.worldpop.geography_ita_gr... \n", "11 carto-do-public-data.carto.geography_usa_block... \n", "12 carto-do.experian.geography_rus_grid_v2 \n", "13 carto-do.experian.geography_rus_grid_v2 \n", "14 carto-do-public-data.carto.geography_esp_quadg... \n", "15 carto-do-public-data.carto.geography_fra_quadg... \n", "16 carto-do-public-data.carto.geography_guy_quadg... \n", "17 carto-do.weather_source.geography_glo_grid22km_v1 \n", "18 carto-do-public-data.carto.geography_phl_quadg... \n", "19 carto-do-public-data.carto.geography_phl_quadg... \n", "20 carto-do.experian.geography_phl_grid_v1 \n", "21 carto-do-public-data.esp_ign.geography_esp_mun... \n", "22 carto-do-public-data.esp_ign.geography_esp_mun... \n", "\n", " id \n", "0 carto-do.landgrid.housing_parcels_usa_parcel_v... \n", "1 carto-do.safegraph.covid19_socialdistancing_us... \n", "2 carto-do-public-data.esp_ine.demographics_soci... \n", "3 carto-do.experian.demographics_sociodemographi... \n", "4 carto-do.mastercard.financial_geographicinsigh... \n", "5 carto-do-public-data.usa_acs.demographics_soci... \n", "6 carto-do-public-data.can_statistics.demographi... \n", "7 carto-do-public-data.can_statistics.demographi... \n", "8 carto-do-public-data.worldpop.demographics_pop... \n", "9 carto-do-public-data.worldpop.demographics_pop... \n", "10 carto-do-public-data.worldpop.demographics_pop... \n", "11 carto-do.spatial_ai.behavioral_geosocialsegmen... \n", "12 carto-do.experian.demographics_consumersegment... \n", "13 carto-do.experian.demographics_sociodemographi... \n", "14 carto-do-public-data.carto.derived_spatialfeat... \n", "15 carto-do.carto.derived_spatialfeatures_fra_qua... \n", "16 carto-do.carto.derived_spatialfeatures_guy_qua... \n", "17 carto-do.weather_source.environmental_climatol... \n", "18 carto-do.unacast.humanmobility_activity_phl_qu... \n", "19 carto-do.unacast.humanmobility_homeandwork_phl... \n", "20 carto-do.experian.demographics_sociodemographi... \n", "21 carto-do.weather_source.environmental_forecast... \n", "22 carto-do.weather_source.environmental_historic... " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().subscriptions().datasets.to_dataframe()" ] }, { "cell_type": "markdown", "metadata": { "id": "RZX4JottBHQF" }, "source": [ "We identify the slug_id from the dataset we want to use. For example this one:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "id": "E64jBp4dBNl7" }, "outputs": [], "source": [ "SpatialFeatures_esp_qk15 = Dataset.get('cdb_spatial_fea_d23a5c97')" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "pomGs2ssCxRv", "outputId": "c4254a04-515d-49dc-dceb-79a7b8fe5062" }, "outputs": [ { "data": { "text/plain": [ "{'slug': 'cdb_spatial_fea_d23a5c97',\n", " 'name': 'Spatial Features - Spain (Quadgrid 15)',\n", " 'description': 'Spatial Features is a dataset curated by CARTO providing access to a set of location-based features with global coverage that have been unified in common geographic supports (eg. Quadgrid). This product has been specially designed to facilitate spatial modeling at scale.\\nSpatial Features includes core demographic data and POI aggregations by category that have been generated by processing and unifying globally available sources such as Worldpop and OpenStreetMap.\\nThe current version of this product is available in two different spatial aggregations: Quadgrid level 15 (with cells of approximately 1x1km) and Quadgrid level 18 (with cells of approximately 100x100m).',\n", " 'category_id': 'derived',\n", " 'country_id': 'esp',\n", " 'data_source_id': 'spatial_features',\n", " 'provider_id': 'carto',\n", " 'geography_name': 'Quadgrid 15 - Spain',\n", " 'geography_description': 'Global Quadgrid (zoom level 15)',\n", " 'temporal_aggregation': 'yearly',\n", " 'time_coverage': None,\n", " 'update_frequency': None,\n", " 'is_public_data': True,\n", " 'lang': 'eng',\n", " 'version': '2020',\n", " 'category_name': 'Derived',\n", " 'provider_name': 'CARTO',\n", " 'geography_id': 'carto-do-public-data.carto.geography_esp_quadgrid15_v1',\n", " 'id': 'carto-do-public-data.carto.derived_spatialfeatures_esp_quadgrid15_v1_yearly_2020'}" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15.to_dict()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 379 }, "id": "fvAIhMg9C7Ij", "outputId": "ec7ed779-d922-47df-e386-0513ad3fd846" }, "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", "
malegeoidfemaleretailleisuretourismeducationfinancialfood_drinkhealthcarepopulationcountry_isomale_1_to_4male_5_to_9male_under_1female_1_to_4female_5_to_9male_10_to_14male_15_to_19male_20_to_24male_25_to_29male_30_to_34male_35_to_39male_40_to_44male_45_to_49male_50_to_54male_55_to_59male_60_to_64male_65_to_69male_70_to_74male_75_to_79country_iso_a3female_under_1transportationfemale_10_to_14female_15_to_19female_20_to_24female_25_to_29female_30_to_34female_35_to_39female_40_to_44female_45_to_49female_50_to_54female_55_to_59female_60_to_64female_65_to_69female_70_to_74female_75_to_79male_80_and_overfemale_80_and_over
03157.4843070313321220220313314.603374202230320217176987.135426Spain120.913153163.55626230.859980112.445169152.122509179.977780169.691305162.016885168.189652180.966714220.835550269.261748263.299398248.028995227.292451196.722677161.299894142.868832105.937552ESP28.69391562169.153640158.909087153.972406164.477591177.747316219.014149267.756015268.073208256.767236237.604333208.339251177.525427167.336132139.422190145.765479255.243802
13100.7097440313332121231133112.7076663977181329227416213.417445Spain116.135321156.32984729.640561107.896359146.732161171.439127162.141215156.255203163.785669177.375590216.347498264.268170258.871424245.878540227.791271199.504164161.553366141.963759104.895240ESP27.533142115163.738535153.326000147.104910155.585855167.001699205.922699251.286604251.263774238.742479218.524946189.368642163.730100155.843235130.658199146.533779238.448327
25563.2349940313333303033315634.26342326753310322303911197.498466Spain207.438940282.45438352.943473196.299369263.708241310.187053294.944600277.136621291.315402318.226120392.201611481.832879471.522448446.028405404.935209345.842258282.652461251.291441187.960022ESP50.091947124293.852755273.574052269.563257284.239360302.392749368.791786447.273132447.816753427.330467399.392763354.964127303.556543285.400862236.542165264.321666429.473098
35752.4876740331212300200135897.91927739291922412394613530.062792Spain214.959833292.49945154.862983205.108661275.753969323.571403298.909093289.252283296.326437318.596845400.574748495.122733488.513948459.693972418.807097358.926419296.731579266.983096198.371388ESP52.33991254304.900184292.604586279.560116302.507961327.124385391.198812471.563116468.010031448.989864418.053752370.225668313.187995291.416965243.300764279.784366442.072536
44920.4011400331103312113215101.838268386122411172403810022.239175Spain185.931533251.82867447.454258175.431490237.010205277.479072262.715527251.839640260.796087279.359516345.971107422.165983413.846829389.538337356.188780306.779508250.842350222.981889164.327410ESP44.76685349263.162347246.132949237.480096254.350030276.120897335.150496409.423620409.001029392.155128363.719101320.472125273.839742257.380644215.620176230.354640390.621340
510488.06725312022223300230210980.1024342065399192402921468.169211Spain399.577618543.086551101.981938374.481191504.032327597.000008565.179574533.200418543.255342585.150797735.436253911.480321896.233391834.498850751.953428646.956267529.950073474.194427357.228428ESP95.560624172561.082684524.800891514.948448560.215039604.717769723.562416869.828768866.350490839.930112790.127512696.650138593.946884554.767677456.639502481.703569848.459962
610691.77621512022223300230311193.36820914464413302404021885.144834Spain407.338614553.634872103.962726381.754702513.822090608.595502576.157012543.556738553.806953596.516153749.720600929.183921913.640858850.707247766.558575659.522022540.243248483.404662364.166840ESP97.416694193571.980562534.994066524.950252571.096036616.463167737.616141886.723386883.177559856.244038805.474094710.181137605.483075565.542897465.508790491.059674864.939521
77046.0027740331110121011307718.364580575128138255014814764.367519Spain273.552370370.48271469.817227258.793396349.655445408.169877384.237815361.315362370.977625400.892505496.398761607.745009596.215580555.359332500.855984426.264436351.705464316.607648235.482475ESP66.039259151388.281731365.377672359.531128387.915307417.419276507.002730617.319966615.971377596.200625559.683816497.777880421.235484391.043486324.241345319.922590594.874658
84880.5151670331112303332205060.481569234560417247159940.996620Spain184.424333249.78728347.069582174.009405235.088947275.229765260.585895249.798178258.682006277.094953343.166571418.743808410.492089386.380638353.301415304.292676248.808967221.174350162.995328ESP44.40396379261.029094244.137743235.555022252.288204273.882590332.433680406.104742405.685562388.976229360.770708317.874315271.619926255.294256213.872313228.487330387.454870
96940.9737330313330333103237317.75789432015211362513314258.731450Spain264.518769357.40669767.511636249.595717338.068831394.396529372.588657354.263577365.453562393.743390489.236870600.947986590.391082546.894724499.339633430.833752355.374173313.106841232.581040ESP63.692182110374.778970351.354713341.896086367.449503396.543658479.801048582.162156580.282089565.227739524.879842461.562910391.095832370.309326307.973901312.384817571.083390
\n", "
" ], "text/plain": [ " male geoid female retail leisure tourism \\\n", "0 3157.484307 031332122022031 3314.603374 202 2 30 \n", "1 3100.709744 031333212123113 3112.707666 397 7 18 \n", "2 5563.234994 031333330303331 5634.263423 267 5 33 \n", "3 5752.487674 033121230020013 5897.919277 392 9 19 \n", "4 4920.401140 033110331211321 5101.838268 386 12 24 \n", "5 10488.067253 120222233002302 10980.102434 206 5 39 \n", "6 10691.776215 120222233002303 11193.368209 144 6 44 \n", "7 7046.002774 033111012101130 7718.364580 575 12 81 \n", "8 4880.515167 033111230333220 5060.481569 234 5 60 \n", "9 6940.973733 031333033310323 7317.757894 320 15 21 \n", "\n", " education financial food_drink healthcare population country_iso \\\n", "0 3 20 217 17 6987.135426 Spain \n", "1 13 29 227 41 6213.417445 Spain \n", "2 10 32 230 39 11197.498466 Spain \n", "3 22 41 239 46 13530.062792 Spain \n", "4 11 17 240 38 10022.239175 Spain \n", "5 9 19 240 29 21468.169211 Spain \n", "6 13 30 240 40 21885.144834 Spain \n", "7 38 25 501 48 14764.367519 Spain \n", "8 4 17 247 15 9940.996620 Spain \n", "9 1 36 251 33 14258.731450 Spain \n", "\n", " male_1_to_4 male_5_to_9 male_under_1 female_1_to_4 female_5_to_9 \\\n", "0 120.913153 163.556262 30.859980 112.445169 152.122509 \n", "1 116.135321 156.329847 29.640561 107.896359 146.732161 \n", "2 207.438940 282.454383 52.943473 196.299369 263.708241 \n", "3 214.959833 292.499451 54.862983 205.108661 275.753969 \n", "4 185.931533 251.828674 47.454258 175.431490 237.010205 \n", "5 399.577618 543.086551 101.981938 374.481191 504.032327 \n", "6 407.338614 553.634872 103.962726 381.754702 513.822090 \n", "7 273.552370 370.482714 69.817227 258.793396 349.655445 \n", "8 184.424333 249.787283 47.069582 174.009405 235.088947 \n", "9 264.518769 357.406697 67.511636 249.595717 338.068831 \n", "\n", " male_10_to_14 male_15_to_19 male_20_to_24 male_25_to_29 male_30_to_34 \\\n", "0 179.977780 169.691305 162.016885 168.189652 180.966714 \n", "1 171.439127 162.141215 156.255203 163.785669 177.375590 \n", "2 310.187053 294.944600 277.136621 291.315402 318.226120 \n", "3 323.571403 298.909093 289.252283 296.326437 318.596845 \n", "4 277.479072 262.715527 251.839640 260.796087 279.359516 \n", "5 597.000008 565.179574 533.200418 543.255342 585.150797 \n", "6 608.595502 576.157012 543.556738 553.806953 596.516153 \n", "7 408.169877 384.237815 361.315362 370.977625 400.892505 \n", "8 275.229765 260.585895 249.798178 258.682006 277.094953 \n", "9 394.396529 372.588657 354.263577 365.453562 393.743390 \n", "\n", " male_35_to_39 male_40_to_44 male_45_to_49 male_50_to_54 male_55_to_59 \\\n", "0 220.835550 269.261748 263.299398 248.028995 227.292451 \n", "1 216.347498 264.268170 258.871424 245.878540 227.791271 \n", "2 392.201611 481.832879 471.522448 446.028405 404.935209 \n", "3 400.574748 495.122733 488.513948 459.693972 418.807097 \n", "4 345.971107 422.165983 413.846829 389.538337 356.188780 \n", "5 735.436253 911.480321 896.233391 834.498850 751.953428 \n", "6 749.720600 929.183921 913.640858 850.707247 766.558575 \n", "7 496.398761 607.745009 596.215580 555.359332 500.855984 \n", "8 343.166571 418.743808 410.492089 386.380638 353.301415 \n", "9 489.236870 600.947986 590.391082 546.894724 499.339633 \n", "\n", " male_60_to_64 male_65_to_69 male_70_to_74 male_75_to_79 country_iso_a3 \\\n", "0 196.722677 161.299894 142.868832 105.937552 ESP \n", "1 199.504164 161.553366 141.963759 104.895240 ESP \n", "2 345.842258 282.652461 251.291441 187.960022 ESP \n", "3 358.926419 296.731579 266.983096 198.371388 ESP \n", "4 306.779508 250.842350 222.981889 164.327410 ESP \n", "5 646.956267 529.950073 474.194427 357.228428 ESP \n", "6 659.522022 540.243248 483.404662 364.166840 ESP \n", "7 426.264436 351.705464 316.607648 235.482475 ESP \n", "8 304.292676 248.808967 221.174350 162.995328 ESP \n", "9 430.833752 355.374173 313.106841 232.581040 ESP \n", "\n", " female_under_1 transportation female_10_to_14 female_15_to_19 \\\n", "0 28.693915 62 169.153640 158.909087 \n", "1 27.533142 115 163.738535 153.326000 \n", "2 50.091947 124 293.852755 273.574052 \n", "3 52.339912 54 304.900184 292.604586 \n", "4 44.766853 49 263.162347 246.132949 \n", "5 95.560624 172 561.082684 524.800891 \n", "6 97.416694 193 571.980562 534.994066 \n", "7 66.039259 151 388.281731 365.377672 \n", "8 44.403963 79 261.029094 244.137743 \n", "9 63.692182 110 374.778970 351.354713 \n", "\n", " female_20_to_24 female_25_to_29 female_30_to_34 female_35_to_39 \\\n", "0 153.972406 164.477591 177.747316 219.014149 \n", "1 147.104910 155.585855 167.001699 205.922699 \n", "2 269.563257 284.239360 302.392749 368.791786 \n", "3 279.560116 302.507961 327.124385 391.198812 \n", "4 237.480096 254.350030 276.120897 335.150496 \n", "5 514.948448 560.215039 604.717769 723.562416 \n", "6 524.950252 571.096036 616.463167 737.616141 \n", "7 359.531128 387.915307 417.419276 507.002730 \n", "8 235.555022 252.288204 273.882590 332.433680 \n", "9 341.896086 367.449503 396.543658 479.801048 \n", "\n", " female_40_to_44 female_45_to_49 female_50_to_54 female_55_to_59 \\\n", "0 267.756015 268.073208 256.767236 237.604333 \n", "1 251.286604 251.263774 238.742479 218.524946 \n", "2 447.273132 447.816753 427.330467 399.392763 \n", "3 471.563116 468.010031 448.989864 418.053752 \n", "4 409.423620 409.001029 392.155128 363.719101 \n", "5 869.828768 866.350490 839.930112 790.127512 \n", "6 886.723386 883.177559 856.244038 805.474094 \n", "7 617.319966 615.971377 596.200625 559.683816 \n", "8 406.104742 405.685562 388.976229 360.770708 \n", "9 582.162156 580.282089 565.227739 524.879842 \n", "\n", " female_60_to_64 female_65_to_69 female_70_to_74 female_75_to_79 \\\n", "0 208.339251 177.525427 167.336132 139.422190 \n", "1 189.368642 163.730100 155.843235 130.658199 \n", "2 354.964127 303.556543 285.400862 236.542165 \n", "3 370.225668 313.187995 291.416965 243.300764 \n", "4 320.472125 273.839742 257.380644 215.620176 \n", "5 696.650138 593.946884 554.767677 456.639502 \n", "6 710.181137 605.483075 565.542897 465.508790 \n", "7 497.777880 421.235484 391.043486 324.241345 \n", "8 317.874315 271.619926 255.294256 213.872313 \n", "9 461.562910 391.095832 370.309326 307.973901 \n", "\n", " male_80_and_over female_80_and_over \n", "0 145.765479 255.243802 \n", "1 146.533779 238.448327 \n", "2 264.321666 429.473098 \n", "3 279.784366 442.072536 \n", "4 230.354640 390.621340 \n", "5 481.703569 848.459962 \n", "6 491.059674 864.939521 \n", "7 319.922590 594.874658 \n", "8 228.487330 387.454870 \n", "9 312.384817 571.083390 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15.tail()" ] }, { "cell_type": "markdown", "metadata": { "id": "r8K6I-kDsh3I" }, "source": [ "### 2. Who's On First GeoJSON for filtering data in cities" ] }, { "cell_type": "markdown", "metadata": { "id": "OsKLO9C_tU0f" }, "source": [ "CARTO's Data Observatory also provides direct access to a group of public datasets. You can navigate and explore our Spatial Data Catalog from within your Python notebook with the Data Discovery methods in CARTOFrames or using our [Spatial Data Catalog](https://carto.com/spatial-data-catalog/browser/?license=public).\n", "\n", "[\"Who's on First\"](https://whosonfirst.org/) is a gazetteer (o big list) of places, each with a stable identifier and some number of descriptive properties about that location. \n", "\n", "\n", "We can use the WoF GeoJSON to find the city boundaries to use then for filtering the data from other datasets from the Data Observatory." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 323 }, "id": "WvAK3hmbsk0O", "outputId": "06f9f1e2-c68c-46a1-b24c-864509b9ab60" }, "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", "
slugnamedescriptioncountry_idprovider_idgeom_typegeom_coverageupdate_frequencyis_public_datalangversionprovider_nameid
0wof_ancestors_eaaeac75Ancestors - GlobalA normalized view of the hierarchies in 'geojs...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
1wof_concordance_392f80adConcordances - GlobalRelationship between Who's On First identifier...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
2wof_geojson_4e78587cGeoJSON - GlobalThe main table in Who's On First. Holds all th...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
3wof_names_5a30fa98Names - GlobalWhat things are called in Who's On First. A no...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
4wof_spr_850ad7e9Standard Places Response - GlobalThe \"Standard Places Response\" (or SPR) is an ...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
\n", "
" ], "text/plain": [ " slug name \\\n", "0 wof_ancestors_eaaeac75 Ancestors - Global \n", "1 wof_concordance_392f80ad Concordances - Global \n", "2 wof_geojson_4e78587c GeoJSON - Global \n", "3 wof_names_5a30fa98 Names - Global \n", "4 wof_spr_850ad7e9 Standard Places Response - Global \n", "\n", " description country_id \\\n", "0 A normalized view of the hierarchies in 'geojs... glo \n", "1 Relationship between Who's On First identifier... glo \n", "2 The main table in Who's On First. Holds all th... glo \n", "3 What things are called in Who's On First. A no... glo \n", "4 The \"Standard Places Response\" (or SPR) is an ... glo \n", "\n", " provider_id geom_type geom_coverage update_frequency is_public_data \\\n", "0 whos_on_first MULTIPLE None None True \n", "1 whos_on_first MULTIPLE None None True \n", "2 whos_on_first MULTIPLE None None True \n", "3 whos_on_first MULTIPLE None None True \n", "4 whos_on_first MULTIPLE None None True \n", "\n", " lang version provider_name \\\n", "0 eng 20190520 Who's On First \n", "1 eng 20190520 Who's On First \n", "2 eng 20190520 Who's On First \n", "3 eng 20190520 Who's On First \n", "4 eng 20190520 Who's On First \n", "\n", " id \n", "0 carto-do-public-data.whos_on_first.geography_g... \n", "1 carto-do-public-data.whos_on_first.geography_g... \n", "2 carto-do-public-data.whos_on_first.geography_g... \n", "3 carto-do-public-data.whos_on_first.geography_g... \n", "4 carto-do-public-data.whos_on_first.geography_g... " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().provider('whos_on_first').public().geographies.to_dataframe()" ] }, { "cell_type": "markdown", "metadata": { "id": "8griL1w_D_Xj" }, "source": [ "Note that the ID to access the WoF GeoJSON table is 'wof_geojson_4e78587c'." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "id": "ERTww0ogD8VS" }, "outputs": [], "source": [ "wof_geojson = Geography.get('wof_geojson_4e78587c')" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "ul9SMfO_EBeu", "outputId": "9aac8c58-f448-42f1-a576-6986f60e435f" }, "outputs": [ { "data": { "text/plain": [ "{'slug': 'wof_geojson_4e78587c',\n", " 'name': 'GeoJSON - Global',\n", " 'description': \"The main table in Who's On First. Holds all the relevant information for a place in the 'body' JSON field.\",\n", " 'country_id': 'glo',\n", " 'provider_id': 'whos_on_first',\n", " 'geom_type': 'MULTIPLE',\n", " 'update_frequency': None,\n", " 'is_public_data': True,\n", " 'lang': 'eng',\n", " 'version': '20190520',\n", " 'provider_name': \"Who's On First\",\n", " 'id': 'carto-do-public-data.whos_on_first.geography_glo_geojson_20190520'}" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "wof_geojson.to_dict()" ] }, { "cell_type": "markdown", "metadata": { "id": "ciG_W6ZtED2X" }, "source": [ "Now we are going to perform a query to the table in order to retrieve the different geometries given a city name and a country ISO Alpha-2 code. As we are looking for city boundaries, we can also limit our search to the placetype = 'locality' if we find that this is the specific type of place for our needs." ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 193 }, "id": "4FUNrrG9EC2i", "outputId": "5f20a2ab-19cf-488f-ecfd-02ebcea2a962" }, "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", "
geoididbodynamecountryparent_idis_currentplacetypegeometry_typebboxgeomlastmodifiedlastmodified_timestamp
08568278385682783{\"id\": 85682783, \"type\": \"Feature\", \"propertie...MadridES4042273871regionMultiPolygonPOLYGON((-3.05298331 39.88471951, -3.05298331 ...MULTIPOLYGON(((-4.31950989 40.64764365, -4.318...15538148732019-03-28 23:14:33+00:00
1101748283101748283{\"id\": 101748283, \"type\": \"Feature\", \"properti...MadridES856827831localityMultiPolygonPOLYGON((-3.5180508952556 40.312064309035, -3....POLYGON((-3.77455610356056 40.4003144849762, -...15368811932018-09-13 23:26:33+00:00
2404338863404338863{\"id\": 404338863, \"type\": \"Feature\", \"properti...MadridES856827830localadminPolygonPOLYGON((-3.51823494 40.31206394, -3.51823494 ...POLYGON((-3.88557655 40.57445562, -3.88560442 ...15132675062017-12-14 16:05:06+00:00
\n", "
" ], "text/plain": [ " geoid id body \\\n", "0 85682783 85682783 {\"id\": 85682783, \"type\": \"Feature\", \"propertie... \n", "1 101748283 101748283 {\"id\": 101748283, \"type\": \"Feature\", \"properti... \n", "2 404338863 404338863 {\"id\": 404338863, \"type\": \"Feature\", \"properti... \n", "\n", " name country parent_id is_current placetype geometry_type \\\n", "0 Madrid ES 404227387 1 region MultiPolygon \n", "1 Madrid ES 85682783 1 locality MultiPolygon \n", "2 Madrid ES 85682783 0 localadmin Polygon \n", "\n", " bbox \\\n", "0 POLYGON((-3.05298331 39.88471951, -3.05298331 ... \n", "1 POLYGON((-3.5180508952556 40.312064309035, -3.... \n", "2 POLYGON((-3.51823494 40.31206394, -3.51823494 ... \n", "\n", " geom lastmodified \\\n", "0 MULTIPOLYGON(((-4.31950989 40.64764365, -4.318... 1553814873 \n", "1 POLYGON((-3.77455610356056 40.4003144849762, -... 1536881193 \n", "2 POLYGON((-3.88557655 40.57445562, -3.88560442 ... 1513267506 \n", "\n", " lastmodified_timestamp \n", "0 2019-03-28 23:14:33+00:00 \n", "1 2018-09-13 23:26:33+00:00 \n", "2 2017-12-14 16:05:06+00:00 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "city_name = \"'Madrid'\"\n", "country_code = \"'ES'\"\n", "placetype = \"'locality'\"\n", "\n", "sql_query = f\"SELECT * FROM $geography$ WHERE name = {city_name} AND country = {country_code}\"\n", "\n", "wof_geojson_filtered = wof_geojson.to_dataframe(sql_query=sql_query)\n", "\n", "wof_geojson_filtered" ] }, { "cell_type": "markdown", "metadata": { "id": "1UWpXObbEPXf" }, "source": [ "It may happen that we find that there are more than one locality with the same name. In order to select the right polygon, we can build a map with a category widget that will allow us to decide for the specific geometry that we are looking for." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 692 }, "id": "79ImYc29EJnh", "outputId": "e2e7c9b9-c3aa-4f98-fb15-e1b60be8aed8" }, "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": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "wof_geojson_filtered['geoid_str'] = wof_geojson_filtered['geoid'].astype(str) \n", "Map(\n", " Layer(\n", " wof_geojson_filtered, # where the data comes from\n", " color_category_style('geoid_str',palette='Vivid',opacity=0.6,stroke_width=0.2),\n", " widgets=[category_widget('geoid_str','Select geoid to visualize')],\n", " popup_hover=[popup_element('geoid','geoid'),\n", " popup_element('name','name'),\n", " popup_element('placetype','placetype')],\n", " legends=color_category_legend('Geoid'),\n", " geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data\n", " encode_data = False \n", " )\n", ")" ] }, { "cell_type": "markdown", "metadata": { "id": "yjqihG9JEhlh" }, "source": [ "Once we know which geometry (i.e. polygon of city boundaries) is the right one for our tests, we should copy/note its associated geoid.\n", "\n", "As in this example we want to filter the data for Madrid, we will use geoid = '\t101748283'." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "id": "mw5R2vFtERNG" }, "outputs": [], "source": [ "\"\"\"Helper function for downloading only the data within the area (geometry) of interest\n", " Args:\n", " do_dataset: DO Dataset you'd like to download for a specific area of interest\n", " do_geom_dataset: DO Dataset containing the geometry you'd like to use as filter (your area of interest)\n", " target_geoid: geoid of the geometry you'd like to use as filter (your area of interest)\n", "\"\"\"\n", "def filter_data(do_dataset, do_geom_dataset, target_geoid):\n", " do_geom_dataset_id=do_geom_dataset.id\n", " sql_query = f\"\"\"WITH do_geom AS (\n", " SELECT geom\n", " FROM `{do_geom_dataset_id}`\n", " WHERE geoid = '{target_geoid}')\n", "\n", " SELECT do_d.* FROM $dataset$ do_d, do_geom WHERE ST_Intersects(do_d.geom, do_geom.geom)\"\"\"\n", " filtered_data = do_dataset.to_dataframe(sql_query = sql_query)\n", " return filtered_data" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 564 }, "id": "n3a4jj1mEtET", "outputId": "bd98daba-7eed-4cbc-caaa-a332c09ee303" }, "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", "
    geoiddo_datecountry_isocountry_iso_a3populationfemalemalefemale_under_1female_1_to_4female_5_to_9female_10_to_14female_15_to_19female_20_to_24female_25_to_29female_30_to_34female_35_to_39female_40_to_44female_45_to_49female_50_to_54female_55_to_59female_60_to_64female_65_to_69female_70_to_74female_75_to_79female_80_and_overmale_under_1male_1_to_4male_5_to_9male_10_to_14male_15_to_19male_20_to_24male_25_to_29male_30_to_34male_35_to_39male_40_to_44male_45_to_49male_50_to_54male_55_to_59male_60_to_64male_65_to_69male_70_to_74male_75_to_79male_80_and_overretaileducationfinancialfood_drinkhealthcareleisuretourismtransportationgeom
    0331110121002312020-01-01SpainESP2821.4076761474.9465321346.46107412.61982149.45431466.81766174.19898269.82211668.70486474.12895679.76704496.886058117.967211117.709500113.931398106.95319195.12322880.49630274.72674261.961138113.67800613.34177452.27469170.79766977.99952073.42620769.04582170.89224076.60884894.859687116.137476113.934257106.12680095.71144181.45731767.20941360.50237244.99969661.13584500000000POLYGON((-3.8232421875 40.3883968738836, -3.82...
    1331110121121032020-01-01SpainESP4211.9263482201.8676592010.05867518.83944673.82766299.748466110.767639104.233648102.565767110.663101119.079892144.635941176.106851175.722126170.081996159.664616142.004305120.168563111.55549492.498418169.70372819.91721278.038052105.689998116.441251109.613998103.074766105.831180114.365187141.610877173.375340170.086271158.430937142.882415121.603210100.33328590.32070867.17760591.26638300000300POLYGON((-3.62548828125 40.3632883409158, -3.6...
    2331110103330032020-01-01SpainESP2940.1604261537.0269551403.13352913.15098851.53584469.63001577.32201572.76092371.59665077.24904283.124433100.963990122.932445122.663887118.726763111.45484699.12696183.88438877.87198464.569077118.46270613.90332954.47493173.77753681.28251576.51670971.95195773.87609379.83330798.852327121.025696118.729742110.59366999.73992884.88584770.03825563.04891246.89373063.70904500000000POLYGON((-3.58154296875 40.4970923726957, -3.5...
    3331110103232012020-01-01SpainESP1953.0538711020.997463932.0563758.73577834.23359946.25297551.36252348.33273547.55934651.31404955.21688367.06712481.66005981.48166678.86636074.03586065.84684555.72169651.72784942.89115778.6909599.23553336.18594049.00803853.99335450.82758447.79536649.07350753.03069365.66441280.39347178.86834073.46380966.25402056.38693446.52415341.88136031.14999442.31986500000000POLYGON((-3.75732421875 40.4720243969206, -3.7...
    4331110103022312020-01-01SpainESP26.48458013.84533712.6392430.1184620.4642280.6272180.6965070.6554210.6449330.6958490.7487740.9094701.1073591.1049401.0694751.0039710.8929230.7556200.7014610.5816301.0670970.1252390.4907030.6645780.7321820.6892520.6481340.6654660.7191280.8904491.0901841.0695020.9962130.8984440.7646410.6308950.5679360.4224130.57388300000000POLYGON((-3.8232421875 40.5889281696937, -3.82...
    \n", "
    " ], "text/plain": [ " geoid do_date country_iso country_iso_a3 population \\\n", "0 33111012100231 2020-01-01 Spain ESP 2821.407676 \n", "1 33111012112103 2020-01-01 Spain ESP 4211.926348 \n", "2 33111010333003 2020-01-01 Spain ESP 2940.160426 \n", "3 33111010323201 2020-01-01 Spain ESP 1953.053871 \n", "4 33111010302231 2020-01-01 Spain ESP 26.484580 \n", "\n", " female male female_under_1 female_1_to_4 female_5_to_9 \\\n", "0 1474.946532 1346.461074 12.619821 49.454314 66.817661 \n", "1 2201.867659 2010.058675 18.839446 73.827662 99.748466 \n", "2 1537.026955 1403.133529 13.150988 51.535844 69.630015 \n", "3 1020.997463 932.056375 8.735778 34.233599 46.252975 \n", "4 13.845337 12.639243 0.118462 0.464228 0.627218 \n", "\n", " female_10_to_14 female_15_to_19 female_20_to_24 female_25_to_29 \\\n", "0 74.198982 69.822116 68.704864 74.128956 \n", "1 110.767639 104.233648 102.565767 110.663101 \n", "2 77.322015 72.760923 71.596650 77.249042 \n", "3 51.362523 48.332735 47.559346 51.314049 \n", "4 0.696507 0.655421 0.644933 0.695849 \n", "\n", " female_30_to_34 female_35_to_39 female_40_to_44 female_45_to_49 \\\n", "0 79.767044 96.886058 117.967211 117.709500 \n", "1 119.079892 144.635941 176.106851 175.722126 \n", "2 83.124433 100.963990 122.932445 122.663887 \n", "3 55.216883 67.067124 81.660059 81.481666 \n", "4 0.748774 0.909470 1.107359 1.104940 \n", "\n", " female_50_to_54 female_55_to_59 female_60_to_64 female_65_to_69 \\\n", "0 113.931398 106.953191 95.123228 80.496302 \n", "1 170.081996 159.664616 142.004305 120.168563 \n", "2 118.726763 111.454846 99.126961 83.884388 \n", "3 78.866360 74.035860 65.846845 55.721696 \n", "4 1.069475 1.003971 0.892923 0.755620 \n", "\n", " female_70_to_74 female_75_to_79 female_80_and_over male_under_1 \\\n", "0 74.726742 61.961138 113.678006 13.341774 \n", "1 111.555494 92.498418 169.703728 19.917212 \n", "2 77.871984 64.569077 118.462706 13.903329 \n", "3 51.727849 42.891157 78.690959 9.235533 \n", "4 0.701461 0.581630 1.067097 0.125239 \n", "\n", " male_1_to_4 male_5_to_9 male_10_to_14 male_15_to_19 male_20_to_24 \\\n", "0 52.274691 70.797669 77.999520 73.426207 69.045821 \n", "1 78.038052 105.689998 116.441251 109.613998 103.074766 \n", "2 54.474931 73.777536 81.282515 76.516709 71.951957 \n", "3 36.185940 49.008038 53.993354 50.827584 47.795366 \n", "4 0.490703 0.664578 0.732182 0.689252 0.648134 \n", "\n", " male_25_to_29 male_30_to_34 male_35_to_39 male_40_to_44 male_45_to_49 \\\n", "0 70.892240 76.608848 94.859687 116.137476 113.934257 \n", "1 105.831180 114.365187 141.610877 173.375340 170.086271 \n", "2 73.876093 79.833307 98.852327 121.025696 118.729742 \n", "3 49.073507 53.030693 65.664412 80.393471 78.868340 \n", "4 0.665466 0.719128 0.890449 1.090184 1.069502 \n", "\n", " male_50_to_54 male_55_to_59 male_60_to_64 male_65_to_69 male_70_to_74 \\\n", "0 106.126800 95.711441 81.457317 67.209413 60.502372 \n", "1 158.430937 142.882415 121.603210 100.333285 90.320708 \n", "2 110.593669 99.739928 84.885847 70.038255 63.048912 \n", "3 73.463809 66.254020 56.386934 46.524153 41.881360 \n", "4 0.996213 0.898444 0.764641 0.630895 0.567936 \n", "\n", " male_75_to_79 male_80_and_over retail education financial food_drink \\\n", "0 44.999696 61.135845 0 0 0 0 \n", "1 67.177605 91.266383 0 0 0 0 \n", "2 46.893730 63.709045 0 0 0 0 \n", "3 31.149994 42.319865 0 0 0 0 \n", "4 0.422413 0.573883 0 0 0 0 \n", "\n", " healthcare leisure tourism transportation \\\n", "0 0 0 0 0 \n", "1 0 3 0 0 \n", "2 0 0 0 0 \n", "3 0 0 0 0 \n", "4 0 0 0 0 \n", "\n", " geom \n", "0 POLYGON((-3.8232421875 40.3883968738836, -3.82... \n", "1 POLYGON((-3.62548828125 40.3632883409158, -3.6... \n", "2 POLYGON((-3.58154296875 40.4970923726957, -3.5... \n", "3 POLYGON((-3.75732421875 40.4720243969206, -3.7... \n", "4 POLYGON((-3.8232421875 40.5889281696937, -3.82... " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15_madrid = filter_data(SpatialFeatures_esp_qk15,wof_geojson,'101748283')\n", "SpatialFeatures_esp_qk15_madrid.head()" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 675 }, "id": "NfLXHz7yE3-q", "outputId": "fe81497f-914d-4875-a6c3-c75e16a3c2cf" }, "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": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Map(\n", " Layer(\n", " SpatialFeatures_esp_qk15_madrid, # where the data comes from\n", " color_continuous_style('population', palette=palettes.magenta,opacity=0.75,stroke_width=0.5),\n", " widgets=[formula_widget('population','sum','Total Population'),histogram_widget('population','Population by cell')],\n", " popup_hover=[popup_element('population','Population')],\n", " geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data\n", " encode_data = False \n", " )\n", ")" ] } ], "metadata": { "colab": { "collapsed_sections": [], "name": "CARTO | Using \"Who's on First\" geometries to filter data from Data Observatory", "provenance": [] }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.7" } }, "nbformat": 4, "nbformat_minor": 4 }