-
-
Save leighajarett/d12de74f6a3b52ef582b70a0ba28a492 to your computer and use it in GitHub Desktop.
GeoCodingApi.ipynb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"nbformat": 4, | |
"nbformat_minor": 0, | |
"metadata": { | |
"colab": { | |
"name": "GeoCodingApi.ipynb", | |
"provenance": [], | |
"include_colab_link": true | |
}, | |
"kernelspec": { | |
"name": "python3", | |
"display_name": "Python 3" | |
}, | |
"language_info": { | |
"name": "python" | |
} | |
}, | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "view-in-github", | |
"colab_type": "text" | |
}, | |
"source": [ | |
"<a href=\"https://colab.research.google.com/gist/leighajarett/d12de74f6a3b52ef582b70a0ba28a492/geocodingapi.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "rpfnAt_RJUCB" | |
}, | |
"source": [ | |
"# Python sample for getting coordinates for new customers in Cloud SQL and appending them to a table in BigQuery" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/" | |
}, | |
"id": "Jh_yaswUQWUO", | |
"outputId": "c9a6fe71-6220-48de-ac68-40704b6bafd0" | |
}, | |
"source": [ | |
"!pip install googlemaps" | |
], | |
"execution_count": null, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"text": [ | |
"Collecting googlemaps\n", | |
" Downloading https://files.pythonhosted.org/packages/bb/e9/bbab9ca9a1d528f6a66469392ea65b2ac7160c2a8b4ccb733038812b1be4/googlemaps-4.4.7.tar.gz\n", | |
"Requirement already satisfied: requests<3.0,>=2.20.0 in /usr/local/lib/python3.7/dist-packages (from googlemaps) (2.23.0)\n", | |
"Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests<3.0,>=2.20.0->googlemaps) (1.24.3)\n", | |
"Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests<3.0,>=2.20.0->googlemaps) (3.0.4)\n", | |
"Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3.0,>=2.20.0->googlemaps) (2.10)\n", | |
"Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests<3.0,>=2.20.0->googlemaps) (2021.5.30)\n", | |
"Building wheels for collected packages: googlemaps\n", | |
" Building wheel for googlemaps (setup.py) ... \u001b[?25l\u001b[?25hdone\n", | |
" Created wheel for googlemaps: filename=googlemaps-4.4.7-cp37-none-any.whl size=38437 sha256=6d6a87c3f1ea7f4efc4361b444817bff1b1a0ab225c36d17326644793f11263d\n", | |
" Stored in directory: /root/.cache/pip/wheels/a8/57/20/17111affcacfda69feea3fb4023db13554bd372112931d093e\n", | |
"Successfully built googlemaps\n", | |
"Installing collected packages: googlemaps\n", | |
"Successfully installed googlemaps-4.4.7\n" | |
], | |
"name": "stdout" | |
} | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/" | |
}, | |
"id": "HWKF4yNpP7sN", | |
"outputId": "9c32bed1-e421-4c4a-96c5-e14eb0a47cf6" | |
}, | |
"source": [ | |
"import googlemaps\n", | |
"import pandas as pd\n", | |
"from google.cloud import bigquery\n", | |
"from google.colab import auth\n", | |
"auth.authenticate_user()\n", | |
"print('Authenticated')" | |
], | |
"execution_count": null, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"text": [ | |
"Authenticated\n" | |
], | |
"name": "stdout" | |
} | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"id": "2v6HFMyMP9dQ" | |
}, | |
"source": [ | |
"%load_ext google.colab.data_table" | |
], | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "VRbtwIzfI4Bn" | |
}, | |
"source": [ | |
"Run an external query in BigQuery to get all new customers added to Cloud SQL yesterday **Note: you might want to connect directly to Cloud SQL" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 194 | |
}, | |
"id": "71qfIoHjJS4J", | |
"outputId": "c5f0543e-df9c-48b9-b38c-be04d48e63b5" | |
}, | |
"source": [ | |
"new_customers.head()" | |
], | |
"execution_count": null, | |
"outputs": [ | |
{ | |
"output_type": "execute_result", | |
"data": { | |
"application/vnd.google.colaboratory.module+javascript": "\n import \"https://ssl.gstatic.com/colaboratory/data_table/a6224c040fa35dcf/data_table.js\";\n\n window.createDataTable({\n data: [[{\n 'v': 0,\n 'f': \"0\",\n },\n{\n 'v': 3364,\n 'f': \"3364\",\n },\n\"SALVADOR\",\n\"HOPPER\",\n\"shopper@gmail.com\",\n{\n 'v': 61,\n 'f': \"61\",\n },\n\"36 Union Rd, Shirley, Solihull B90 3DQ, UK\",\n\"Wythall\",\n\"Solihull\",\n\"UK\",\n\"B90\",\n\"Male\",\n\"Email\",\n\"2015-07-27 19:59:35 UTC\"],\n [{\n 'v': 1,\n 'f': \"1\",\n },\n{\n 'v': 4347,\n 'f': \"4347\",\n },\n\"MOSES\",\n\"KENNEDY\",\n\"mkennedy@yahoo.com\",\n{\n 'v': 75,\n 'f': \"75\",\n },\n\"9 Rebecca Dr, Birmingham B29 6TP, UK\",\n\"Bournbrook\",\n\"Birmingham\",\n\"UK\",\n\"B29\",\n\"Male\",\n\"Organic\",\n\"2015-09-14 20:32:10 UTC\"],\n [{\n 'v': 2,\n 'f': \"2\",\n },\n{\n 'v': 8227,\n 'f': \"8227\",\n },\n\"DANIEL\",\n\"ADKINS\",\n\"danieladkins@yahoo.com\",\n{\n 'v': 67,\n 'f': \"67\",\n },\n\"93 Grand Ave, Cardiff CF5 4LF, UK\",\n\"Ely\",\n\"Cardiff\",\n\"UK\",\n\"CF5\",\n\"Male\",\n\"Display\",\n\"2016-02-15 20:21:16 UTC\"],\n [{\n 'v': 3,\n 'f': \"3\",\n },\n{\n 'v': 8984,\n 'f': \"8984\",\n },\n\"LARRY\",\n\"ANTHONY\",\n\"larryanthony@hotmail.com\",\n{\n 'v': 58,\n 'f': \"58\",\n },\n\"2 Heath Cottages, Broadclyst, Exeter EX5 3GZ, UK\",\n\"Woodbury\",\n\"East Devon\",\n\"UK\",\n\"EX5\",\n\"Male\",\n\"Organic\",\n\"2016-03-21 07:41:11 UTC\"],\n [{\n 'v': 4,\n 'f': \"4\",\n },\n{\n 'v': 10988,\n 'f': \"10988\",\n },\n\"WAYNE\",\n\"SCHOOLEY\",\n\"wayne.schooley@yahoo.com\",\n{\n 'v': 48,\n 'f': \"48\",\n },\n\"1 Straw House Farm, Kirkby Road, Ripon HG4 3JU, UK\",\n\"North Stainley\",\n\"Ripon\",\n\"UK\",\n\"HG4\",\n\"Male\",\n\"Organic\",\n\"2016-06-12 21:08:37 UTC\"]],\n columns: [[\"number\", \"index\"], [\"number\", \"id\"], [\"string\", \"first_name\"], [\"string\", \"last_name\"], [\"string\", \"email\"], [\"number\", \"age\"], [\"string\", \"address\"], [\"string\", \"city\"], [\"string\", \"state\"], [\"string\", \"country\"], [\"string\", \"zip\"], [\"string\", \"gender\"], [\"string\", \"traffic_source\"], [\"string\", \"created_at\"]],\n columnOptions: [{\"width\": \"1px\", \"className\": \"index_column\"}],\n rowsPerPage: 25,\n helpUrl: \"https://colab.research.google.com/notebooks/data_table.ipynb\",\n suppressOutputScrolling: true,\n minimumWidth: undefined,\n });\n ", | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>id</th>\n", | |
" <th>first_name</th>\n", | |
" <th>last_name</th>\n", | |
" <th>email</th>\n", | |
" <th>age</th>\n", | |
" <th>address</th>\n", | |
" <th>city</th>\n", | |
" <th>state</th>\n", | |
" <th>country</th>\n", | |
" <th>zip</th>\n", | |
" <th>gender</th>\n", | |
" <th>traffic_source</th>\n", | |
" <th>created_at</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <th>0</th>\n", | |
" <td>3364</td>\n", | |
" <td>SALVADOR</td>\n", | |
" <td>HOPPER</td>\n", | |
" <td>shopper@gmail.com</td>\n", | |
" <td>61</td>\n", | |
" <td>36 Union Rd, Shirley, Solihull B90 3DQ, UK</td>\n", | |
" <td>Wythall</td>\n", | |
" <td>Solihull</td>\n", | |
" <td>UK</td>\n", | |
" <td>B90</td>\n", | |
" <td>Male</td>\n", | |
" <td>Email</td>\n", | |
" <td>2015-07-27 19:59:35 UTC</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>1</th>\n", | |
" <td>4347</td>\n", | |
" <td>MOSES</td>\n", | |
" <td>KENNEDY</td>\n", | |
" <td>mkennedy@yahoo.com</td>\n", | |
" <td>75</td>\n", | |
" <td>9 Rebecca Dr, Birmingham B29 6TP, UK</td>\n", | |
" <td>Bournbrook</td>\n", | |
" <td>Birmingham</td>\n", | |
" <td>UK</td>\n", | |
" <td>B29</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2015-09-14 20:32:10 UTC</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>2</th>\n", | |
" <td>8227</td>\n", | |
" <td>DANIEL</td>\n", | |
" <td>ADKINS</td>\n", | |
" <td>danieladkins@yahoo.com</td>\n", | |
" <td>67</td>\n", | |
" <td>93 Grand Ave, Cardiff CF5 4LF, UK</td>\n", | |
" <td>Ely</td>\n", | |
" <td>Cardiff</td>\n", | |
" <td>UK</td>\n", | |
" <td>CF5</td>\n", | |
" <td>Male</td>\n", | |
" <td>Display</td>\n", | |
" <td>2016-02-15 20:21:16 UTC</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>3</th>\n", | |
" <td>8984</td>\n", | |
" <td>LARRY</td>\n", | |
" <td>ANTHONY</td>\n", | |
" <td>larryanthony@hotmail.com</td>\n", | |
" <td>58</td>\n", | |
" <td>2 Heath Cottages, Broadclyst, Exeter EX5 3GZ, UK</td>\n", | |
" <td>Woodbury</td>\n", | |
" <td>East Devon</td>\n", | |
" <td>UK</td>\n", | |
" <td>EX5</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-03-21 07:41:11 UTC</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>4</th>\n", | |
" <td>10988</td>\n", | |
" <td>WAYNE</td>\n", | |
" <td>SCHOOLEY</td>\n", | |
" <td>wayne.schooley@yahoo.com</td>\n", | |
" <td>48</td>\n", | |
" <td>1 Straw House Farm, Kirkby Road, Ripon HG4 3JU...</td>\n", | |
" <td>North Stainley</td>\n", | |
" <td>Ripon</td>\n", | |
" <td>UK</td>\n", | |
" <td>HG4</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-06-12 21:08:37 UTC</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" id first_name last_name ... gender traffic_source created_at\n", | |
"0 3364 SALVADOR HOPPER ... Male Email 2015-07-27 19:59:35 UTC\n", | |
"1 4347 MOSES KENNEDY ... Male Organic 2015-09-14 20:32:10 UTC\n", | |
"2 8227 DANIEL ADKINS ... Male Display 2016-02-15 20:21:16 UTC\n", | |
"3 8984 LARRY ANTHONY ... Male Organic 2016-03-21 07:41:11 UTC\n", | |
"4 10988 WAYNE SCHOOLEY ... Male Organic 2016-06-12 21:08:37 UTC\n", | |
"\n", | |
"[5 rows x 13 columns]" | |
] | |
}, | |
"metadata": { | |
"tags": [] | |
}, | |
"execution_count": 30 | |
} | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"id": "fxqXcsYdJBKk" | |
}, | |
"source": [ | |
"%%bigquery new_customers\n", | |
"\n", | |
"SELECT * FROM EXTERNAL_QUERY(\"projects/leigha-bq-dev/locations/us/connections/customer-geos\",\n", | |
" \"SELECT * FROM public.customers WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)\")" | |
], | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "kQOKPP58JDhR" | |
}, | |
"source": [ | |
"Iterate through each customer and send their address to geocoding API" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"id": "rEyKl3pTQusb" | |
}, | |
"source": [ | |
"gmaps = googlemaps.Client(key='Add Your Key here')\n", | |
"new_customers = new_customers.merge(new_customers['address'].apply(lambda x: pd.Series(gmaps.geocode(x)[0]['geometry']['location'])), left_index=True, right_index=True)" | |
], | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 152 | |
}, | |
"id": "HCvi-_dTcqHJ", | |
"outputId": "66352ff6-2bad-4f22-fd21-f337564b04b2" | |
}, | |
"source": [ | |
"new_customers.head()" | |
], | |
"execution_count": null, | |
"outputs": [ | |
{ | |
"output_type": "execute_result", | |
"data": { | |
"application/vnd.google.colaboratory.module+javascript": "\n import \"https://ssl.gstatic.com/colaboratory/data_table/a6224c040fa35dcf/data_table.js\";\n\n window.createDataTable({\n data: [[{\n 'v': 0,\n 'f': \"0\",\n },\n{\n 'v': 3364,\n 'f': \"3364\",\n },\n\"SALVADOR\",\n\"HOPPER\",\n\"shopper@gmail.com\",\n{\n 'v': 61,\n 'f': \"61\",\n },\n\"36 Union Rd, Shirley, Solihull B90 3DQ, UK\",\n\"Wythall\",\n\"Solihull\",\n\"UK\",\n\"B90\",\n\"Male\",\n\"Email\",\n\"2015-07-27 19:59:35 UTC\",\n{\n 'v': 52.4050057,\n 'f': \"52.4050057\",\n },\n{\n 'v': -1.8201428,\n 'f': \"-1.8201428\",\n }],\n [{\n 'v': 1,\n 'f': \"1\",\n },\n{\n 'v': 4347,\n 'f': \"4347\",\n },\n\"MOSES\",\n\"KENNEDY\",\n\"mkennedy@yahoo.com\",\n{\n 'v': 75,\n 'f': \"75\",\n },\n\"9 Rebecca Dr, Birmingham B29 6TP, UK\",\n\"Bournbrook\",\n\"Birmingham\",\n\"UK\",\n\"B29\",\n\"Male\",\n\"Organic\",\n\"2015-09-14 20:32:10 UTC\",\n{\n 'v': 52.4407901,\n 'f': \"52.4407901\",\n },\n{\n 'v': -1.9414256,\n 'f': \"-1.9414256\",\n }],\n [{\n 'v': 2,\n 'f': \"2\",\n },\n{\n 'v': 8227,\n 'f': \"8227\",\n },\n\"DANIEL\",\n\"ADKINS\",\n\"danieladkins@yahoo.com\",\n{\n 'v': 67,\n 'f': \"67\",\n },\n\"93 Grand Ave, Cardiff CF5 4LF, UK\",\n\"Ely\",\n\"Cardiff\",\n\"UK\",\n\"CF5\",\n\"Male\",\n\"Display\",\n\"2016-02-15 20:21:16 UTC\",\n{\n 'v': 51.4799924,\n 'f': \"51.4799924\",\n },\n{\n 'v': -3.2455175,\n 'f': \"-3.2455175\",\n }],\n [{\n 'v': 3,\n 'f': \"3\",\n },\n{\n 'v': 8984,\n 'f': \"8984\",\n },\n\"LARRY\",\n\"ANTHONY\",\n\"larryanthony@hotmail.com\",\n{\n 'v': 58,\n 'f': \"58\",\n },\n\"2 Heath Cottages, Broadclyst, Exeter EX5 3GZ, UK\",\n\"Woodbury\",\n\"East Devon\",\n\"UK\",\n\"EX5\",\n\"Male\",\n\"Organic\",\n\"2016-03-21 07:41:11 UTC\",\n{\n 'v': 50.7615907,\n 'f': \"50.7615907\",\n },\n{\n 'v': -3.4381188,\n 'f': \"-3.4381188\",\n }],\n [{\n 'v': 4,\n 'f': \"4\",\n },\n{\n 'v': 10988,\n 'f': \"10988\",\n },\n\"WAYNE\",\n\"SCHOOLEY\",\n\"wayne.schooley@yahoo.com\",\n{\n 'v': 48,\n 'f': \"48\",\n },\n\"1 Straw House Farm, Kirkby Road, Ripon HG4 3JU, UK\",\n\"North Stainley\",\n\"Ripon\",\n\"UK\",\n\"HG4\",\n\"Male\",\n\"Organic\",\n\"2016-06-12 21:08:37 UTC\",\n{\n 'v': 54.1498151,\n 'f': \"54.1498151\",\n },\n{\n 'v': -1.5494804,\n 'f': \"-1.5494804\",\n }],\n [{\n 'v': 5,\n 'f': \"5\",\n },\n{\n 'v': 12984,\n 'f': \"12984\",\n },\n\"LANCE\",\n\"BOWENS\",\n\"lancebowens@yahoo.com\",\n{\n 'v': 34,\n 'f': \"34\",\n },\n\"1516 Shettleston Rd, Glasgow G32 9AL, UK\",\n\"Tollcross\",\n\"Glasgow\",\n\"UK\",\n\"G32\",\n\"Male\",\n\"Organic\",\n\"2016-08-29 16:34:37 UTC\",\n{\n 'v': 55.8506931,\n 'f': \"55.8506931\",\n },\n{\n 'v': -4.1567424,\n 'f': \"-4.1567424\",\n }],\n [{\n 'v': 6,\n 'f': \"6\",\n },\n{\n 'v': 13911,\n 'f': \"13911\",\n },\n\"LEWIS\",\n\"THOMPSON\",\n\"lthompson@gmail.com\",\n{\n 'v': 32,\n 'f': \"32\",\n },\n\"1 Old Malling Way, Lewes BN7 2JA, UK\",\n\"Offham\",\n\"Lewes\",\n\"UK\",\n\"BN7\",\n\"Male\",\n\"Organic\",\n\"2016-09-27 19:34:47 UTC\",\n{\n 'v': 50.88053859999999,\n 'f': \"50.88053859999999\",\n },\n{\n 'v': 0.007307600000000001,\n 'f': \"0.007307600000000001\",\n }],\n [{\n 'v': 7,\n 'f': \"7\",\n },\n{\n 'v': 17715,\n 'f': \"17715\",\n },\n\"ESTEBAN\",\n\"GREEN\",\n\"estebangreen@gmail.com\",\n{\n 'v': 54,\n 'f': \"54\",\n },\n\"18 Balfour Rd, Brighton BN1 6NA, UK\",\n\"Coldean\",\n\"Brighton and Hove\",\n\"UK\",\n\"BN1\",\n\"Male\",\n\"Facebook\",\n\"2017-01-08 02:00:33 UTC\",\n{\n 'v': 50.8449464,\n 'f': \"50.8449464\",\n },\n{\n 'v': -0.1422194,\n 'f': \"-0.1422194\",\n }],\n [{\n 'v': 8,\n 'f': \"8\",\n },\n{\n 'v': 17896,\n 'f': \"17896\",\n },\n\"ANTOINE\",\n\"CRAIG\",\n\"antoinecraig@gmail.com\",\n{\n 'v': 32,\n 'f': \"32\",\n },\n\"43 Cheshire St, London E2 6EE, UK\",\n\"Portsoken\",\n\"Tower Hamlets\",\n\"UK\",\n\"E1\",\n\"Male\",\n\"Organic\",\n\"2017-01-12 17:14:09 UTC\",\n{\n 'v': 51.523712,\n 'f': \"51.523712\",\n },\n{\n 'v': -0.06867519999999999,\n 'f': \"-0.06867519999999999\",\n }],\n [{\n 'v': 9,\n 'f': \"9\",\n },\n{\n 'v': 25979,\n 'f': \"25979\",\n },\n\"BEN\",\n\"ESPINOZA\",\n\"benespinoza@gmail.com\",\n{\n 'v': 56,\n 'f': \"56\",\n },\n\"Water Ln, Godalming GU8 5AW, UK\",\n\"Thursley\",\n\"Waverley\",\n\"UK\",\n\"GU8\",\n\"Male\",\n\"Organic\",\n\"2017-08-26 10:24:56 UTC\",\n{\n 'v': 51.14487159999999,\n 'f': \"51.14487159999999\",\n },\n{\n 'v': -0.6373238999999999,\n 'f': \"-0.6373238999999999\",\n }]],\n columns: [[\"number\", \"index\"], [\"number\", \"id\"], [\"string\", \"first_name\"], [\"string\", \"last_name\"], [\"string\", \"email\"], [\"number\", \"age\"], [\"string\", \"address\"], [\"string\", \"city\"], [\"string\", \"state\"], [\"string\", \"country\"], [\"string\", \"zip\"], [\"string\", \"gender\"], [\"string\", \"traffic_source\"], [\"string\", \"created_at\"], [\"number\", \"lat\"], [\"number\", \"lng\"]],\n columnOptions: [{\"width\": \"1px\", \"className\": \"index_column\"}],\n rowsPerPage: 25,\n helpUrl: \"https://colab.research.google.com/notebooks/data_table.ipynb\",\n suppressOutputScrolling: true,\n minimumWidth: undefined,\n });\n ", | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>id</th>\n", | |
" <th>first_name</th>\n", | |
" <th>last_name</th>\n", | |
" <th>email</th>\n", | |
" <th>age</th>\n", | |
" <th>address</th>\n", | |
" <th>city</th>\n", | |
" <th>state</th>\n", | |
" <th>country</th>\n", | |
" <th>zip</th>\n", | |
" <th>gender</th>\n", | |
" <th>traffic_source</th>\n", | |
" <th>created_at</th>\n", | |
" <th>lat</th>\n", | |
" <th>lng</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <th>0</th>\n", | |
" <td>3364</td>\n", | |
" <td>SALVADOR</td>\n", | |
" <td>HOPPER</td>\n", | |
" <td>shopper@gmail.com</td>\n", | |
" <td>61</td>\n", | |
" <td>36 Union Rd, Shirley, Solihull B90 3DQ, UK</td>\n", | |
" <td>Wythall</td>\n", | |
" <td>Solihull</td>\n", | |
" <td>UK</td>\n", | |
" <td>B90</td>\n", | |
" <td>Male</td>\n", | |
" <td>Email</td>\n", | |
" <td>2015-07-27 19:59:35 UTC</td>\n", | |
" <td>52.405006</td>\n", | |
" <td>-1.820143</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>1</th>\n", | |
" <td>4347</td>\n", | |
" <td>MOSES</td>\n", | |
" <td>KENNEDY</td>\n", | |
" <td>mkennedy@yahoo.com</td>\n", | |
" <td>75</td>\n", | |
" <td>9 Rebecca Dr, Birmingham B29 6TP, UK</td>\n", | |
" <td>Bournbrook</td>\n", | |
" <td>Birmingham</td>\n", | |
" <td>UK</td>\n", | |
" <td>B29</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2015-09-14 20:32:10 UTC</td>\n", | |
" <td>52.440790</td>\n", | |
" <td>-1.941426</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>2</th>\n", | |
" <td>8227</td>\n", | |
" <td>DANIEL</td>\n", | |
" <td>ADKINS</td>\n", | |
" <td>danieladkins@yahoo.com</td>\n", | |
" <td>67</td>\n", | |
" <td>93 Grand Ave, Cardiff CF5 4LF, UK</td>\n", | |
" <td>Ely</td>\n", | |
" <td>Cardiff</td>\n", | |
" <td>UK</td>\n", | |
" <td>CF5</td>\n", | |
" <td>Male</td>\n", | |
" <td>Display</td>\n", | |
" <td>2016-02-15 20:21:16 UTC</td>\n", | |
" <td>51.479992</td>\n", | |
" <td>-3.245518</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>3</th>\n", | |
" <td>8984</td>\n", | |
" <td>LARRY</td>\n", | |
" <td>ANTHONY</td>\n", | |
" <td>larryanthony@hotmail.com</td>\n", | |
" <td>58</td>\n", | |
" <td>2 Heath Cottages, Broadclyst, Exeter EX5 3GZ, UK</td>\n", | |
" <td>Woodbury</td>\n", | |
" <td>East Devon</td>\n", | |
" <td>UK</td>\n", | |
" <td>EX5</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-03-21 07:41:11 UTC</td>\n", | |
" <td>50.761591</td>\n", | |
" <td>-3.438119</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>4</th>\n", | |
" <td>10988</td>\n", | |
" <td>WAYNE</td>\n", | |
" <td>SCHOOLEY</td>\n", | |
" <td>wayne.schooley@yahoo.com</td>\n", | |
" <td>48</td>\n", | |
" <td>1 Straw House Farm, Kirkby Road, Ripon HG4 3JU...</td>\n", | |
" <td>North Stainley</td>\n", | |
" <td>Ripon</td>\n", | |
" <td>UK</td>\n", | |
" <td>HG4</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-06-12 21:08:37 UTC</td>\n", | |
" <td>54.149815</td>\n", | |
" <td>-1.549480</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>5</th>\n", | |
" <td>12984</td>\n", | |
" <td>LANCE</td>\n", | |
" <td>BOWENS</td>\n", | |
" <td>lancebowens@yahoo.com</td>\n", | |
" <td>34</td>\n", | |
" <td>1516 Shettleston Rd, Glasgow G32 9AL, UK</td>\n", | |
" <td>Tollcross</td>\n", | |
" <td>Glasgow</td>\n", | |
" <td>UK</td>\n", | |
" <td>G32</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-08-29 16:34:37 UTC</td>\n", | |
" <td>55.850693</td>\n", | |
" <td>-4.156742</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>6</th>\n", | |
" <td>13911</td>\n", | |
" <td>LEWIS</td>\n", | |
" <td>THOMPSON</td>\n", | |
" <td>lthompson@gmail.com</td>\n", | |
" <td>32</td>\n", | |
" <td>1 Old Malling Way, Lewes BN7 2JA, UK</td>\n", | |
" <td>Offham</td>\n", | |
" <td>Lewes</td>\n", | |
" <td>UK</td>\n", | |
" <td>BN7</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2016-09-27 19:34:47 UTC</td>\n", | |
" <td>50.880539</td>\n", | |
" <td>0.007308</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>7</th>\n", | |
" <td>17715</td>\n", | |
" <td>ESTEBAN</td>\n", | |
" <td>GREEN</td>\n", | |
" <td>estebangreen@gmail.com</td>\n", | |
" <td>54</td>\n", | |
" <td>18 Balfour Rd, Brighton BN1 6NA, UK</td>\n", | |
" <td>Coldean</td>\n", | |
" <td>Brighton and Hove</td>\n", | |
" <td>UK</td>\n", | |
" <td>BN1</td>\n", | |
" <td>Male</td>\n", | |
" <td>Facebook</td>\n", | |
" <td>2017-01-08 02:00:33 UTC</td>\n", | |
" <td>50.844946</td>\n", | |
" <td>-0.142219</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>8</th>\n", | |
" <td>17896</td>\n", | |
" <td>ANTOINE</td>\n", | |
" <td>CRAIG</td>\n", | |
" <td>antoinecraig@gmail.com</td>\n", | |
" <td>32</td>\n", | |
" <td>43 Cheshire St, London E2 6EE, UK</td>\n", | |
" <td>Portsoken</td>\n", | |
" <td>Tower Hamlets</td>\n", | |
" <td>UK</td>\n", | |
" <td>E1</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2017-01-12 17:14:09 UTC</td>\n", | |
" <td>51.523712</td>\n", | |
" <td>-0.068675</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>9</th>\n", | |
" <td>25979</td>\n", | |
" <td>BEN</td>\n", | |
" <td>ESPINOZA</td>\n", | |
" <td>benespinoza@gmail.com</td>\n", | |
" <td>56</td>\n", | |
" <td>Water Ln, Godalming GU8 5AW, UK</td>\n", | |
" <td>Thursley</td>\n", | |
" <td>Waverley</td>\n", | |
" <td>UK</td>\n", | |
" <td>GU8</td>\n", | |
" <td>Male</td>\n", | |
" <td>Organic</td>\n", | |
" <td>2017-08-26 10:24:56 UTC</td>\n", | |
" <td>51.144872</td>\n", | |
" <td>-0.637324</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" id first_name last_name ... created_at lat lng\n", | |
"0 3364 SALVADOR HOPPER ... 2015-07-27 19:59:35 UTC 52.405006 -1.820143\n", | |
"1 4347 MOSES KENNEDY ... 2015-09-14 20:32:10 UTC 52.440790 -1.941426\n", | |
"2 8227 DANIEL ADKINS ... 2016-02-15 20:21:16 UTC 51.479992 -3.245518\n", | |
"3 8984 LARRY ANTHONY ... 2016-03-21 07:41:11 UTC 50.761591 -3.438119\n", | |
"4 10988 WAYNE SCHOOLEY ... 2016-06-12 21:08:37 UTC 54.149815 -1.549480\n", | |
"5 12984 LANCE BOWENS ... 2016-08-29 16:34:37 UTC 55.850693 -4.156742\n", | |
"6 13911 LEWIS THOMPSON ... 2016-09-27 19:34:47 UTC 50.880539 0.007308\n", | |
"7 17715 ESTEBAN GREEN ... 2017-01-08 02:00:33 UTC 50.844946 -0.142219\n", | |
"8 17896 ANTOINE CRAIG ... 2017-01-12 17:14:09 UTC 51.523712 -0.068675\n", | |
"9 25979 BEN ESPINOZA ... 2017-08-26 10:24:56 UTC 51.144872 -0.637324\n", | |
"\n", | |
"[10 rows x 15 columns]" | |
] | |
}, | |
"metadata": { | |
"tags": [] | |
}, | |
"execution_count": 34 | |
} | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "KKGMDQtyJO30" | |
}, | |
"source": [ | |
"Append the new customers, with their lat/lon, to the table in BigQuery" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"id": "wjbbN6-UMWU4" | |
}, | |
"source": [ | |
"client = bigquery.Client(project='leigha-bq-dev')\n", | |
"\n", | |
"job = client.load_table_from_dataframe(\n", | |
" new_customers, bigquery.table.TableReference.from_string('retail.customers', default_project='leigha-bq-dev')\n", | |
") \n", | |
"\n", | |
"job.result() \n" | |
], | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"metadata": { | |
"id": "JFR5c0t6fAgy" | |
}, | |
"source": [ | |
"" | |
], | |
"execution_count": null, | |
"outputs": [] | |
} | |
] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment