A Python package for integrating CARTO maps, analysis, and data services into data science workflows.
CARTOframes provides the Dataset(your_query)
method for performing analysis and returning the results as a pandas dataframe, and the Layer
class for visualizing analysis as a map layer. Both methods run the quries against a PostgreSQL database with PostGIS. CARTO also provides more advanced spatial analysis through the crankshaft extension.
In this guide, we will analyze McDonald’s locations in US Census tracts using spatial analysis functionality in CARTO.
You can download directly the dataset from their sources:
Or follow the following steps to download them directly from the CARTOframes account.
1
2
3
4
5
6
7
from cartoframes.auth import Credentials
from cartoframes.viz import Map, Layer, Legend, Source
from cartoframes.data import Dataset
## Add here your CARTO credentials:
credentials = Credentials(base_url='https://your_user_name.carto.com', api_key='your_api_key')
cf_credentials = Credentials(base_url='https://cartoframes.carto.com', api_key='default_public')
And upload it to your account:
McDonald’s:
1
2
3
4
5
mcdonalds_nyc_data = Dataset('mcdonalds_nyc', credentials=cf_credentials)
mcdonalds_nyc_data_df = mcdonalds_nyc_data.download()
mcdonalds_nyc = Dataset(mcdonalds_nyc_data_df)
mcdonalds_nyc.upload(table_name='mcdonalds_nyc', if_exists='replace', credentials=credentials)
NYC Census Tracts:
1
2
3
4
5
nyc_census_tracts_data = Dataset('nyc_census_tracts', credentials=cf_credentials)
nyc_census_tracts_data_df = nyc_census_tracts_data.download()
nyc_census_tracts = Dataset(nyc_census_tracts_data_df)
nyc_census_tracts.upload(table_name='nyc_census_tracts', if_exists='replace', credentials=credentials)
Find the number of McDonald’s in each census tract in New York City.
1
2
3
4
5
6
7
8
9
10
mcdonalds_per_census_tracts = Dataset('''
SELECT
tracts.geom_refs AS FIPS_code,
tracts.the_geom as the_geom,
COUNT(mcd.*) AS num_mcdonalds
FROM nyc_census_tracts As tracts, mcdonalds_nyc As mcd
WHERE ST_Intersects(tracts.the_geom, mcd.the_geom)
GROUP BY tracts.geom_refs, tracts.the_geom
ORDER BY num_mcdonalds DESC
''', credentials=credentials)
1
2
3
4
5
# Show first five entries of results
# Including FIPS code (unique digital identifier for census tracts) and the number of McDonald's
# Sorted by the number of McDonald's in descending order
mcdonalds_per_census_tracts = mcdonalds_per_census_tracts.download()
mcdonalds_per_census_tracts.head()
fips_code | num_mcdonalds | |
---|---|---|
0 | 36061010100 | 4 |
1 | 36061007100 | 2 |
2 | 36047025500 | 2 |
3 | 36061011300 | 2 |
4 | 36061010900 | 2 |
Build 100 meter buffer area for each McDonald’s by updating the geometry.
nyc_mcdonalds_buffer_100m
.1
2
3
4
5
6
7
8
9
10
11
nyc_mcdonalds_buffer_100m = Dataset(
'''
SELECT name, id, address, city, zip,
ST_Buffer(the_geom::geography, 100)::geometry AS the_geom
FROM mcdonalds_nyc
''',
credentials=credentials)
nyc_mcdonalds_buffer_100m.upload(table_name='nyc_mcdonalds_buffer_100m', if_exists='replace', credentials=credentials)
nyc_mcdonalds_buffer_100m_df = nyc_mcdonalds_buffer_100m.download()
nyc_mcdonalds_buffer_100m_df.iloc[0, :]
1
2
3
4
5
6
7
8
from cartoframes.viz import Map, Layer
Map([
Layer('nyc_mcdonalds_buffer_100m', 'color: lightgray width: 20', credentials=credentials),
Layer('mcdonalds_nyc', 'color: red width: 3', credentials=credentials),
],
viewport={'zoom': 13.00, 'lat': 40.74, 'lng': -73.98}
)
Note: for more complicated queries, it is best to create a temporary table from the query and then visualize it.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
k_means_dataset = Dataset('''
SELECT
row_number() OVER () AS cartodb_id,
con.cluster_no,
con.the_geom,
ST_Transform(c.the_geom, 3857) AS the_geom_webmercator
FROM
((SELECT *
FROM cdb_crankshaft.cdb_kmeans(
'SELECT the_geom, cartodb_id, longitude, latitude FROM mcdonalds_nyc', 5)
) AS a
JOIN
mcdonalds_nyc AS b
ON a.cartodb_id = b.cartodb_id
) AS c
''',
credentials=credentials)
k_means_dataset.upload(table_name='mcdonalds_clusters', if_exists='replace', credentials=credentials)
1
2
3
from cartoframes.viz import Map, Layer
Map(Layer('mcdonalds_clusters', 'color: ramp($cluster_no, Prism)', credentials=credentials))