CARTO frames workshop¶
Full details at the documentation page. I'm using the master branch installed using
pip install https://github.com/CartoDB/cartoframes/archive/master.zip
In [1]:
import cartoframes
from cartoframes.credentials import set_credentials
from cartoframes.credentials import credentials
import pandas as pd
import os
Load the credentials¶
In [2]:
try:
credentials()
print('Getting the credentials from a previous session')
except Exception as e:
print('Getting the credentials from your environment or here')
BASEURL = os.environ.get('CARTO_API_URL','https://jsanz.carto.com') # <-- replace with your username or set up the envvar
APIKEY = os.environ.get('CARTO_API_KEY',False) # <-- replace False with your CARTO API key or set up the envvar
if BASEURL and APIKEY:
set_credentials(base_url=BASEURL, api_key=APIKEY, overwrite=True)
else:
print('Set up your environment!')
if credentials():
cc = cartoframes.CartoContext()
Load the typical Populated Places dataset from CARTO¶
You can import this dataset from the Data Library
In [3]:
df = cc.read('populated_places')
df.head()
Out[3]:
It's a Pandas data frame¶
You can get the featurecla field counts
In [4]:
df.groupby('featurecla').featurecla.count()
Out[4]:
Run SQL queries¶
In [5]:
cc.query('''
SELECT featurecla,count(*) as counts
FROM populated_places
GROUP BY featurecla
''')
Out[5]:
Draw graphics using Seaborn¶
In [6]:
import seaborn as sns
import matplotlib.pyplot as plt
import warnings
warnings.filterwarnings('ignore')
%matplotlib inline
In [7]:
f, ax = plt.subplots(figsize=(16, 6))
sns.boxplot(x="featurecla", y="pop_min", data=df);
Render your tables from CARTO¶
In [8]:
from cartoframes import Layer, styling
l = Layer(
'populated_places',
color={'column': 'featurecla','scheme': styling.prism(9)},
size ={'column': 'pop_max','bin_method':'quantiles','bins' : 4, 'min': 3, 'max':10}
)
cc.map(layers=l, interactive=False)
Out[8]:
Render queries from CARTO¶
In [13]:
from cartoframes import QueryLayer
l = QueryLayer(
'''
WITH capitals as (
select *
from populated_places
where featurecla like 'Admin-0 capital'
)
select pp.cartodb_id,
pp.pop_max,
ST_MakeLine(c.the_geom,pp.the_geom) as the_geom,
ST_MakeLine(c.the_geom_webmercator,pp.the_geom_webmercator) as the_geom_webmercator
from populated_places pp join capitals c on pp.adm0_a3 = c.adm0_a3
where c.adm0_a3 = 'ESP'
''',
color = {'column':'pop_max','scheme':styling.sunset(bins=3)},
size ={'column': 'pop_max','bin_method':'quantiles','bins' : 4, 'min': 3, 'max':10}
)
cc.map(layers=l, interactive=False,zoom=4, lat=36.19, lng=-6.79)
Out[13]:
In [12]:
l2 = QueryLayer(
'''
select *
from populated_places
where adm0_a3 = 'ESP'
''',
color = {'column':'pop_max','scheme':styling.sunset(bins=3)},
size = {'column':'pop_max','bin_method':'quantiles','bins' : 4, 'min': 3, 'max':7}
)
cc.map(layers=[l,l2], interactive=False,zoom=4, lat=36.19, lng=-6.79)
Out[12]:
In [16]:
query = '''
with -- first data
data as (
SELECT *
FROM jsanz.ne_10m_populated_places_simple_7
WHERE
(megacity >= 0.5 AND megacity <= 1) AND featurecla IN ('Admin-0 capital','Admin-1 region capital','Admin-0 region capital','Admin-0 capital alt')
), -- from dubai
origin as (
select *
from jsanz.ne_10m_populated_places_simple_7
where cartodb_id = 7263
), -- cities closer to 14000 Km
dests as (
select d.*,
ST_Distance(
o.the_geom::geography,
d.the_geom::geography
)::int distance
from data d, origin o
where
ST_DWithin( o.the_geom::geography, d.the_geom::geography, 14000000 )
),
geoms as (
select
dests.cartodb_id, dests.name, dests.adm0name, dests.distance,
st_transform(
st_segmentize(
st_makeline(
origin.the_geom,
dests.the_geom
)::geography,
10000
)::geometry,
3857) the_geom_webmercator
from origin,dests
)
select *, st_transform(the_geom_webmercator,4326) as the_geom from geoms
'''
cc.map(QueryLayer(query), interactive=False, zoom=2, lat=19.9, lng=29.5)
Out[16]:
Create a new table on CARTO¶
In [17]:
df_spain = df[(df['adm0_a3'] == 'ESP')]
df_spain['name'].head()
Out[17]:
In [18]:
cc.write(df_spain, 'places_spain', overwrite=True)
In [19]:
cc.query('SELECT DISTINCT adm0_a3 FROM places_spain')
Out[19]:
Modify schema and data¶
Drop a column
In [20]:
df_spain = df[(df['adm0_a3'] == 'ESP')]
df_spain = df_spain.drop('adm0_a3', 1)
cc.write(df_spain, 'places_spain', overwrite=True)
In [21]:
try:
cc.query('SELECT DISTINCT adm0_a3 FROM places_spain')
except Exception as e:
print(e)
Add València as an alternate name for the city of Valencia
In [22]:
vlc_id = df_spain[df.apply(lambda x: x['name'] == 'Valencia', axis=1)].index.values[0]
df_spain = df_spain.set_value(vlc_id,'cityalt','València')
In [23]:
# THE FUTURE
# cc.sync(df_spain,'places_spain')
# THE PRESENT
cc.write(df_spain, 'places_spain', overwrite=True)
In [24]:
cc.query('''SELECT name,cityalt from places_spain WHERE cartodb_id = {}'''.format(vlc_id))
Out[24]:
Delete the table¶
In [25]:
cc.delete('places_spain')
But there's more¶
Take a look into the documentation for other functions related with CARTO Data Observatory. All these features are is still under active development, things may change quickly.