{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nlcStationBoroughNoteWeekdaySaturdaySundayWeekday.1Saturday.1Sunday.1million
0500.0Acton TownEaling9531.06716.04744.09382.06617.04785.06.040516
1502.0AldgateCity of London15080.04397.03261.016023.05909.04230.08.846940
2503.0Aldgate EastTower Hamlets22327.016166.013323.021071.013893.011347.013.998292
3505.0AlpertonBrent4495.03279.02345.05081.03392.02445.03.052230
4506.0AmershamChiltern3848.01876.01232.04025.01797.01121.02.321692
\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": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "file_path = 'https://libs.cartocdn.com/cartoframes/samples/london_stations.xls'\n", "df = pandas.read_excel(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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nlcStationBoroughNoteweekday_entrysaturday_entrysunday_entryweekday_exitsaturday_exitsunday_exitmillion
0500.0Acton TownEaling9531.06716.04744.09382.06617.04785.06.040516
1502.0AldgateCity of London15080.04397.03261.016023.05909.04230.08.846940
2503.0Aldgate EastTower Hamlets22327.016166.013323.021071.013893.011347.013.998292
3505.0AlpertonBrent4495.03279.02345.05081.03392.02445.03.052230
4506.0AmershamChiltern3848.01876.01232.04025.01797.01121.02.321692
\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": 1, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
the_geomnlcstationboroughnoteweekday_entrysaturday_entrysunday_entryweekday_exitsaturday_exitsunday_exitmilliongc_status_relcarto_geocode_hash
0POINT (-0.30421 51.51335)500.0Acton TownEaling9531.06716.04744.09382.06617.04785.06.0405160.55985f9d318280297b6108bc30e92d92e1
1POINT (-0.08895 51.51333)502.0AldgateCity of London15080.04397.03261.016023.05909.04230.08.8469400.887abfcd5c0283e877a275f0d059836a09
2POINT (-0.03228 51.51740)503.0Aldgate EastTower Hamlets22327.016166.013323.021071.013893.011347.013.9982920.75c1ba0efb09cc8e5caf79092547a7a3e7
3POINT (-0.24564 51.55493)505.0AlpertonBrent4495.03279.02345.05081.03392.02445.03.0522300.55c9f07eb29a060527ca81a613cca80007
4POINT (-0.15566 51.52048)506.0AmershamChiltern3848.01876.01232.04025.01797.01121.02.3216920.54518e37ccd63a56df5d5758fb8e52a5dd
\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.03228 51.51740) 503.0 Aldgate East Tower Hamlets \n", "3 POINT (-0.24564 51.55493) 505.0 Alperton Brent \n", "4 POINT (-0.15566 51.52048) 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.75 c1ba0efb09cc8e5caf79092547a7a3e7 \n", "3 2445.0 3.052230 0.55 c9f07eb29a060527ca81a613cca80007 \n", "4 1121.0 2.321692 0.54 518e37ccd63a56df5d5758fb8e52a5dd " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "london_stations_gdf.head()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "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", " StackTrace\n", "
    \n", "
    \n", "
    \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 1, "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.8.5" } }, "nbformat": 4, "nbformat_minor": 2 }