{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Geocode London Stations\n",
"\n",
"Source: https://data.london.gov.uk/dataset/london-underground-performance-reports"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas\n",
"from cartoframes.auth import set_default_credentials\n",
"from cartoframes.data.services import Geocoding\n",
"\n",
"set_default_credentials('creds.json')"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" nlc | \n",
" Station | \n",
" Borough | \n",
" Note | \n",
" Weekday | \n",
" Saturday | \n",
" Sunday | \n",
" Weekday.1 | \n",
" Saturday.1 | \n",
" Sunday.1 | \n",
" million | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 500.0 | \n",
" Acton Town | \n",
" Ealing | \n",
" | \n",
" 9531.0 | \n",
" 6716.0 | \n",
" 4744.0 | \n",
" 9382.0 | \n",
" 6617.0 | \n",
" 4785.0 | \n",
" 6.040516 | \n",
"
\n",
" \n",
" 1 | \n",
" 502.0 | \n",
" Aldgate | \n",
" City of London | \n",
" | \n",
" 15080.0 | \n",
" 4397.0 | \n",
" 3261.0 | \n",
" 16023.0 | \n",
" 5909.0 | \n",
" 4230.0 | \n",
" 8.846940 | \n",
"
\n",
" \n",
" 2 | \n",
" 503.0 | \n",
" Aldgate East | \n",
" Tower Hamlets | \n",
" | \n",
" 22327.0 | \n",
" 16166.0 | \n",
" 13323.0 | \n",
" 21071.0 | \n",
" 13893.0 | \n",
" 11347.0 | \n",
" 13.998292 | \n",
"
\n",
" \n",
" 3 | \n",
" 505.0 | \n",
" Alperton | \n",
" Brent | \n",
" | \n",
" 4495.0 | \n",
" 3279.0 | \n",
" 2345.0 | \n",
" 5081.0 | \n",
" 3392.0 | \n",
" 2445.0 | \n",
" 3.052230 | \n",
"
\n",
" \n",
" 4 | \n",
" 506.0 | \n",
" Amersham | \n",
" Chiltern | \n",
" | \n",
" 3848.0 | \n",
" 1876.0 | \n",
" 1232.0 | \n",
" 4025.0 | \n",
" 1797.0 | \n",
" 1121.0 | \n",
" 2.321692 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" nlc Station Borough Note Weekday Saturday Sunday \\\n",
"0 500.0 Acton Town Ealing 9531.0 6716.0 4744.0 \n",
"1 502.0 Aldgate City of London 15080.0 4397.0 3261.0 \n",
"2 503.0 Aldgate East Tower Hamlets 22327.0 16166.0 13323.0 \n",
"3 505.0 Alperton Brent 4495.0 3279.0 2345.0 \n",
"4 506.0 Amersham Chiltern 3848.0 1876.0 1232.0 \n",
"\n",
" Weekday.1 Saturday.1 Sunday.1 million \n",
"0 9382.0 6617.0 4785.0 6.040516 \n",
"1 16023.0 5909.0 4230.0 8.846940 \n",
"2 21071.0 13893.0 11347.0 13.998292 \n",
"3 5081.0 3392.0 2445.0 3.052230 \n",
"4 4025.0 1797.0 1121.0 2.321692 "
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"local_file_path = '../files/london_stations.xls'\n",
"df = pandas.read_excel(local_file_path, header=6, sheet_name=1)\n",
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" nlc | \n",
" Station | \n",
" Borough | \n",
" Note | \n",
" weekday_entry | \n",
" saturday_entry | \n",
" sunday_entry | \n",
" weekday_exit | \n",
" saturday_exit | \n",
" sunday_exit | \n",
" million | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 500.0 | \n",
" Acton Town | \n",
" Ealing | \n",
" | \n",
" 9531.0 | \n",
" 6716.0 | \n",
" 4744.0 | \n",
" 9382.0 | \n",
" 6617.0 | \n",
" 4785.0 | \n",
" 6.040516 | \n",
"
\n",
" \n",
" 1 | \n",
" 502.0 | \n",
" Aldgate | \n",
" City of London | \n",
" | \n",
" 15080.0 | \n",
" 4397.0 | \n",
" 3261.0 | \n",
" 16023.0 | \n",
" 5909.0 | \n",
" 4230.0 | \n",
" 8.846940 | \n",
"
\n",
" \n",
" 2 | \n",
" 503.0 | \n",
" Aldgate East | \n",
" Tower Hamlets | \n",
" | \n",
" 22327.0 | \n",
" 16166.0 | \n",
" 13323.0 | \n",
" 21071.0 | \n",
" 13893.0 | \n",
" 11347.0 | \n",
" 13.998292 | \n",
"
\n",
" \n",
" 3 | \n",
" 505.0 | \n",
" Alperton | \n",
" Brent | \n",
" | \n",
" 4495.0 | \n",
" 3279.0 | \n",
" 2345.0 | \n",
" 5081.0 | \n",
" 3392.0 | \n",
" 2445.0 | \n",
" 3.052230 | \n",
"
\n",
" \n",
" 4 | \n",
" 506.0 | \n",
" Amersham | \n",
" Chiltern | \n",
" | \n",
" 3848.0 | \n",
" 1876.0 | \n",
" 1232.0 | \n",
" 4025.0 | \n",
" 1797.0 | \n",
" 1121.0 | \n",
" 2.321692 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" nlc Station Borough Note weekday_entry saturday_entry \\\n",
"0 500.0 Acton Town Ealing 9531.0 6716.0 \n",
"1 502.0 Aldgate City of London 15080.0 4397.0 \n",
"2 503.0 Aldgate East Tower Hamlets 22327.0 16166.0 \n",
"3 505.0 Alperton Brent 4495.0 3279.0 \n",
"4 506.0 Amersham Chiltern 3848.0 1876.0 \n",
"\n",
" sunday_entry weekday_exit saturday_exit sunday_exit million \n",
"0 4744.0 9382.0 6617.0 4785.0 6.040516 \n",
"1 3261.0 16023.0 5909.0 4230.0 8.846940 \n",
"2 13323.0 21071.0 13893.0 11347.0 13.998292 \n",
"3 2345.0 5081.0 3392.0 2445.0 3.052230 \n",
"4 1232.0 4025.0 1797.0 1121.0 2.321692 "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = df.rename(columns={\n",
" \"Saturday.1\": \"saturday_exit\",\n",
" \"Sunday.1\": \"sunday_exit\",\n",
" \"Weekday.1\": \"weekday_exit\",\n",
" \"Saturday\": \"saturday_entry\",\n",
" \"Sunday\": \"sunday_entry\",\n",
" \"Weekday\": \"weekday_entry\"\n",
"})\n",
"\n",
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Success! Data geocoded correctly\n"
]
}
],
"source": [
"gc = Geocoding()\n",
"\n",
"london_stations_gdf, london_stations_metadata = gc.geocode(\n",
" df,\n",
" street='Borough',\n",
" city={'value': 'London'},\n",
" country={'value': 'United Kingdom'}\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" the_geom | \n",
" nlc | \n",
" station | \n",
" borough | \n",
" note | \n",
" weekday_entry | \n",
" saturday_entry | \n",
" sunday_entry | \n",
" weekday_exit | \n",
" saturday_exit | \n",
" sunday_exit | \n",
" million | \n",
" gc_status_rel | \n",
" carto_geocode_hash | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" POINT (-0.30421 51.51335) | \n",
" 500.0 | \n",
" Acton Town | \n",
" Ealing | \n",
" | \n",
" 9531.0 | \n",
" 6716.0 | \n",
" 4744.0 | \n",
" 9382.0 | \n",
" 6617.0 | \n",
" 4785.0 | \n",
" 6.040516 | \n",
" 0.55 | \n",
" 985f9d318280297b6108bc30e92d92e1 | \n",
"
\n",
" \n",
" 1 | \n",
" POINT (-0.08895 51.51333) | \n",
" 502.0 | \n",
" Aldgate | \n",
" City of London | \n",
" | \n",
" 15080.0 | \n",
" 4397.0 | \n",
" 3261.0 | \n",
" 16023.0 | \n",
" 5909.0 | \n",
" 4230.0 | \n",
" 8.846940 | \n",
" 0.88 | \n",
" 7abfcd5c0283e877a275f0d059836a09 | \n",
"
\n",
" \n",
" 2 | \n",
" POINT (0.01695 51.55042) | \n",
" 503.0 | \n",
" Aldgate East | \n",
" Tower Hamlets | \n",
" | \n",
" 22327.0 | \n",
" 16166.0 | \n",
" 13323.0 | \n",
" 21071.0 | \n",
" 13893.0 | \n",
" 11347.0 | \n",
" 13.998292 | \n",
" 0.74 | \n",
" c1ba0efb09cc8e5caf79092547a7a3e7 | \n",
"
\n",
" \n",
" 3 | \n",
" POINT (-0.30262 51.55225) | \n",
" 505.0 | \n",
" Alperton | \n",
" Brent | \n",
" | \n",
" 4495.0 | \n",
" 3279.0 | \n",
" 2345.0 | \n",
" 5081.0 | \n",
" 3392.0 | \n",
" 2445.0 | \n",
" 3.052230 | \n",
" 0.55 | \n",
" c9f07eb29a060527ca81a613cca80007 | \n",
"
\n",
" \n",
" 4 | \n",
" POINT (-0.21470 51.56339) | \n",
" 506.0 | \n",
" Amersham | \n",
" Chiltern | \n",
" | \n",
" 3848.0 | \n",
" 1876.0 | \n",
" 1232.0 | \n",
" 4025.0 | \n",
" 1797.0 | \n",
" 1121.0 | \n",
" 2.321692 | \n",
" 0.53 | \n",
" 518e37ccd63a56df5d5758fb8e52a5dd | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" the_geom nlc station borough note \\\n",
"0 POINT (-0.30421 51.51335) 500.0 Acton Town Ealing \n",
"1 POINT (-0.08895 51.51333) 502.0 Aldgate City of London \n",
"2 POINT (0.01695 51.55042) 503.0 Aldgate East Tower Hamlets \n",
"3 POINT (-0.30262 51.55225) 505.0 Alperton Brent \n",
"4 POINT (-0.21470 51.56339) 506.0 Amersham Chiltern \n",
"\n",
" weekday_entry saturday_entry sunday_entry weekday_exit saturday_exit \\\n",
"0 9531.0 6716.0 4744.0 9382.0 6617.0 \n",
"1 15080.0 4397.0 3261.0 16023.0 5909.0 \n",
"2 22327.0 16166.0 13323.0 21071.0 13893.0 \n",
"3 4495.0 3279.0 2345.0 5081.0 3392.0 \n",
"4 3848.0 1876.0 1232.0 4025.0 1797.0 \n",
"\n",
" sunday_exit million gc_status_rel carto_geocode_hash \n",
"0 4785.0 6.040516 0.55 985f9d318280297b6108bc30e92d92e1 \n",
"1 4230.0 8.846940 0.88 7abfcd5c0283e877a275f0d059836a09 \n",
"2 11347.0 13.998292 0.74 c1ba0efb09cc8e5caf79092547a7a3e7 \n",
"3 2445.0 3.052230 0.55 c9f07eb29a060527ca81a613cca80007 \n",
"4 1121.0 2.321692 0.53 518e37ccd63a56df5d5758fb8e52a5dd "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"london_stations_gdf.head()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
""
],
"text/plain": [
""
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from cartoframes.viz import Map, Layer\n",
"\n",
"Map(Layer(london_stations_gdf), viewport={'zoom': 10, 'lat': 51.53, 'lng': -0.09})"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}