Skip to content

Instantly share code, notes, and snippets.

@leighajarett

leighajarett/geocodingapi.ipynb Secret

Created August 18, 2021 02:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save leighajarett/d12de74f6a3b52ef582b70a0ba28a492 to your computer and use it in GitHub Desktop.
Save leighajarett/d12de74f6a3b52ef582b70a0ba28a492 to your computer and use it in GitHub Desktop.
Download ZIP
GeoCodingApi.ipynb
Display the source blob
Display the rendered blob
Raw
geocodingapi.ipynb
{
"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