Loonshot Questionnaire Script .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Install necessary packages to perform the following task\n",
    "# !pip install gspread oauth2client\n",
    "# !pip install pyhive\n",
    "# load corresponding libraries\n",
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Google APIs credentials\n",
    "scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']\n",
    "creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)\n",
    "client = gspread.authorize(creds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "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>Timestamp</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>Nombre Completo</th>\n",
       "      <th>Sexo</th>\n",
       "      <th>Fecha de Nacimiento</th>\n",
       "      <th>Lugar de Nacimiento (País, estado, ciudad)</th>\n",
       "      <th>Estado Civil</th>\n",
       "      <th>Tienes hijos?</th>\n",
       "      <th>Si tienes, cuántos? ¿De qué edades? [Hijo 1]</th>\n",
       "      <th>Si tienes, cuántos? ¿De qué edades? [Hijo 2]</th>\n",
       "      <th>...</th>\n",
       "      <th>Si tienes, cuántos? ¿De qué edades? [Hijo 4]</th>\n",
       "      <th>Si tienes, cuántos? ¿De qué edades? [Hijo 5]</th>\n",
       "      <th>Cuál es el código postal de su residencia?</th>\n",
       "      <th>¿Cuanto tiempo te haces de tu residencia/casa a tu trabajo?</th>\n",
       "      <th>Su hogar cuenta con otra(s) fuente(s) de ingresos?</th>\n",
       "      <th>Cuando empezaste a trabajar con nosotros?</th>\n",
       "      <th>Cuantos años tienes de experiencia profesional? (En general) - Solo números</th>\n",
       "      <th>Cuantos años tienes de experiencia como conductor?  - Solo números</th>\n",
       "      <th>En cuantas empresas haz trabajado antes de este empleo? (Solamente el número)</th>\n",
       "      <th>Cuál es su nivel de escolaridad?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8/15/2020 13:39:24</td>\n",
       "      <td>dtrejo74@gmail.com</td>\n",
       "      <td>David Alejandro Trejo Atilano</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>1/25/1974</td>\n",
       "      <td>Ciudad de México</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15530</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8/15/2020 13:39:51</td>\n",
       "      <td>josenk11@gmail.com</td>\n",
       "      <td>José Enrique Mariano González</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>2/11/1997</td>\n",
       "      <td>Mexico, distrito federal, Miguel Hidalgo</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>11550</td>\n",
       "      <td>Menos que 30 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8/15/2020 13:39:59</td>\n",
       "      <td>josearmando.svllprz97@gmail.com</td>\n",
       "      <td>José Armando Sevilla Pérez</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>9/22/1997</td>\n",
       "      <td>ciudad de mexico</td>\n",
       "      <td>Soltero(a)</td>\n",
       "      <td>No</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2060</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8/15/2020 13:42:15</td>\n",
       "      <td>dtrejo74@gmail.com</td>\n",
       "      <td>David Alejandro Trejo Atilano</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>1/25/1974</td>\n",
       "      <td>Ciudad de México</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15530</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8/15/2020 13:47:25</td>\n",
       "      <td>ivpalsan67@gmail.com</td>\n",
       "      <td>Iván Palma Sánchezllanes</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>7/9/1968</td>\n",
       "      <td>México DF</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>20-30 años</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4700</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "      <td>6</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>10/28/2020 13:18:20</td>\n",
       "      <td>alanhandytadeo@gmail.com</td>\n",
       "      <td>Alan josue pantoja Molina</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>6/3/1988</td>\n",
       "      <td>Iztacalco</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>8930</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>No</td>\n",
       "      <td>10/20/2020</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>10/28/2020 15:04:06</td>\n",
       "      <td>seravi89@hotmail.com</td>\n",
       "      <td>Sergio Avila Arriaga</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>4/15/1961</td>\n",
       "      <td>Ciudad de Mexico</td>\n",
       "      <td>Divorciado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td></td>\n",
       "      <td>20-30 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9310</td>\n",
       "      <td>De 1 hora hasta 1 hora y media</td>\n",
       "      <td>No</td>\n",
       "      <td>10/28/2020</td>\n",
       "      <td>29</td>\n",
       "      <td>35</td>\n",
       "      <td>7</td>\n",
       "      <td>Licenciatura completa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>10/28/2020 18:21:24</td>\n",
       "      <td>aleex.garcia.perez@gmail.com</td>\n",
       "      <td>Alejandro Garcia Perez</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>10/14/1992</td>\n",
       "      <td>Ciudad de Mxico</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>8500</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, para las dos opciones arriba</td>\n",
       "      <td>10/23/2020</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>10/28/2020 18:39:28</td>\n",
       "      <td>samuel76522@gmail.com</td>\n",
       "      <td>Samuel Huerta Miranda</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>6/13/1997</td>\n",
       "      <td>Nezahualcóyotl</td>\n",
       "      <td>Soltero(a)</td>\n",
       "      <td>No</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9730</td>\n",
       "      <td>Más que 1 hora y media</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>10/27/2020</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>10/29/2020 6:58:22</td>\n",
       "      <td>ehigareda2011@hotmail.com</td>\n",
       "      <td>Jesús Enrique Higareda Lima</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>4/30/1975</td>\n",
       "      <td>Mexico, DF</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2800</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>10/28/2020</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Licenciatura completa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>281 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Timestamp                    Email Address  \\\n",
       "0     8/15/2020 13:39:24               dtrejo74@gmail.com   \n",
       "1     8/15/2020 13:39:51               josenk11@gmail.com   \n",
       "2     8/15/2020 13:39:59  josearmando.svllprz97@gmail.com   \n",
       "3     8/15/2020 13:42:15               dtrejo74@gmail.com   \n",
       "4     8/15/2020 13:47:25             ivpalsan67@gmail.com   \n",
       "..                   ...                              ...   \n",
       "276  10/28/2020 13:18:20         alanhandytadeo@gmail.com   \n",
       "277  10/28/2020 15:04:06             seravi89@hotmail.com   \n",
       "278  10/28/2020 18:21:24     aleex.garcia.perez@gmail.com   \n",
       "279  10/28/2020 18:39:28            samuel76522@gmail.com   \n",
       "280   10/29/2020 6:58:22        ehigareda2011@hotmail.com   \n",
       "\n",
       "                   Nombre Completo    Sexo Fecha de Nacimiento  \\\n",
       "0    David Alejandro Trejo Atilano  Hombre           1/25/1974   \n",
       "1    José Enrique Mariano González  Hombre           2/11/1997   \n",
       "2       José Armando Sevilla Pérez  Hombre           9/22/1997   \n",
       "3    David Alejandro Trejo Atilano  Hombre           1/25/1974   \n",
       "4         Iván Palma Sánchezllanes  Hombre            7/9/1968   \n",
       "..                             ...     ...                 ...   \n",
       "276     Alan josue pantoja Molina   Hombre            6/3/1988   \n",
       "277           Sergio Avila Arriaga  Hombre           4/15/1961   \n",
       "278         Alejandro Garcia Perez  Hombre          10/14/1992   \n",
       "279         Samuel Huerta Miranda   Hombre           6/13/1997   \n",
       "280   Jesús Enrique Higareda Lima   Hombre           4/30/1975   \n",
       "\n",
       "    Lugar de Nacimiento (País, estado, ciudad)   Estado Civil Tienes hijos?  \\\n",
       "0                             Ciudad de México      Casado(a)            Sí   \n",
       "1     Mexico, distrito federal, Miguel Hidalgo    Unión Libre            Sí   \n",
       "2                             ciudad de mexico     Soltero(a)            No   \n",
       "3                             Ciudad de México      Casado(a)            Sí   \n",
       "4                                    México DF      Casado(a)            Sí   \n",
       "..                                         ...            ...           ...   \n",
       "276                                  Iztacalco      Casado(a)            Sí   \n",
       "277                           Ciudad de Mexico  Divorciado(a)            Sí   \n",
       "278                            Ciudad de Mxico    Unión Libre            Sí   \n",
       "279                            Nezahualcóyotl      Soltero(a)            No   \n",
       "280                                Mexico, DF     Unión Libre            Sí   \n",
       "\n",
       "    Si tienes, cuántos? ¿De qué edades? [Hijo 1]  \\\n",
       "0                                     10-20 años   \n",
       "1                                       1-5 años   \n",
       "2                                                  \n",
       "3                                     10-20 años   \n",
       "4                                     20-30 años   \n",
       "..                                           ...   \n",
       "276                                   10-20 años   \n",
       "277                                                \n",
       "278                                    5-10 años   \n",
       "279                                                \n",
       "280                                    5-10 años   \n",
       "\n",
       "    Si tienes, cuántos? ¿De qué edades? [Hijo 2]  ...  \\\n",
       "0                                                 ...   \n",
       "1                                       1-5 años  ...   \n",
       "2                                                 ...   \n",
       "3                                                 ...   \n",
       "4                                     10-20 años  ...   \n",
       "..                                           ...  ...   \n",
       "276                                    5-10 años  ...   \n",
       "277                                   20-30 años  ...   \n",
       "278                                     1-5 años  ...   \n",
       "279                                               ...   \n",
       "280                                    5-10 años  ...   \n",
       "\n",
       "    Si tienes, cuántos? ¿De qué edades? [Hijo 4]  \\\n",
       "0                                                  \n",
       "1                                                  \n",
       "2                                                  \n",
       "3                                                  \n",
       "4                                                  \n",
       "..                                           ...   \n",
       "276                                                \n",
       "277                                                \n",
       "278                                                \n",
       "279                                                \n",
       "280                                                \n",
       "\n",
       "    Si tienes, cuántos? ¿De qué edades? [Hijo 5]  \\\n",
       "0                                                  \n",
       "1                                                  \n",
       "2                                                  \n",
       "3                                                  \n",
       "4                                                  \n",
       "..                                           ...   \n",
       "276                                                \n",
       "277                                                \n",
       "278                                                \n",
       "279                                                \n",
       "280                                                \n",
       "\n",
       "    Cuál es el código postal de su residencia?  \\\n",
       "0                                        15530   \n",
       "1                                        11550   \n",
       "2                                         2060   \n",
       "3                                        15530   \n",
       "4                                         4700   \n",
       "..                                         ...   \n",
       "276                                       8930   \n",
       "277                                       9310   \n",
       "278                                       8500   \n",
       "279                                       9730   \n",
       "280                                       2800   \n",
       "\n",
       "     ¿Cuanto tiempo te haces de tu residencia/casa a tu trabajo?  \\\n",
       "0                                  De 45' hasta 1 hora             \n",
       "1                                 Menos que 30 minutos             \n",
       "2                              De 30' hasta 45 minutos             \n",
       "3                                  De 45' hasta 1 hora             \n",
       "4                                  De 45' hasta 1 hora             \n",
       "..                                                 ...             \n",
       "276                            De 30' hasta 45 minutos             \n",
       "277                     De 1 hora hasta 1 hora y media             \n",
       "278                            De 30' hasta 45 minutos             \n",
       "279                             Más que 1 hora y media             \n",
       "280                            De 30' hasta 45 minutos             \n",
       "\n",
       "    Su hogar cuenta con otra(s) fuente(s) de ingresos?  \\\n",
       "0                                                   No   \n",
       "1       Sí, otras personas de mi hogar tiene ocupación   \n",
       "2       Sí, otras personas de mi hogar tiene ocupación   \n",
       "3                                                   No   \n",
       "4                                                   No   \n",
       "..                                                 ...   \n",
       "276                                                 No   \n",
       "277                                                 No   \n",
       "278                   Sí, para las dos opciones arriba   \n",
       "279     Sí, otras personas de mi hogar tiene ocupación   \n",
       "280     Sí, otras personas de mi hogar tiene ocupación   \n",
       "\n",
       "    Cuando empezaste a trabajar con nosotros?  \\\n",
       "0                                   8/17/2020   \n",
       "1                                   8/17/2020   \n",
       "2                                   8/17/2020   \n",
       "3                                   8/17/2020   \n",
       "4                                   8/17/2020   \n",
       "..                                        ...   \n",
       "276                                10/20/2020   \n",
       "277                                10/28/2020   \n",
       "278                                10/23/2020   \n",
       "279                                10/27/2020   \n",
       "280                                10/28/2020   \n",
       "\n",
       "    Cuantos años tienes de experiencia profesional? (En general) - Solo números  \\\n",
       "0                                                   22                            \n",
       "1                                                    5                            \n",
       "2                                                    5                            \n",
       "3                                                   22                            \n",
       "4                                                   30                            \n",
       "..                                                 ...                            \n",
       "276                                                 14                            \n",
       "277                                                 29                            \n",
       "278                                                  8                            \n",
       "279                                                  0                            \n",
       "280                                                 12                            \n",
       "\n",
       "     Cuantos años tienes de experiencia como conductor?  - Solo números  \\\n",
       "0                                                   22                    \n",
       "1                                                    4                    \n",
       "2                                                    0                    \n",
       "3                                                   22                    \n",
       "4                                                   35                    \n",
       "..                                                 ...                    \n",
       "276                                                  4                    \n",
       "277                                                 35                    \n",
       "278                                                  0                    \n",
       "279                                                  4                    \n",
       "280                                                  2                    \n",
       "\n",
       "     En cuantas empresas haz trabajado antes de este empleo? (Solamente el número)  \\\n",
       "0                                                    4                               \n",
       "1                                                    1                               \n",
       "2                                                    2                               \n",
       "3                                                    4                               \n",
       "4                                                    6                               \n",
       "..                                                 ...                               \n",
       "276                                                  4                               \n",
       "277                                                  7                               \n",
       "278                                                  4                               \n",
       "279                                                  3                               \n",
       "280                                                  2                               \n",
       "\n",
       "     Cuál es su nivel de escolaridad?  \n",
       "0              Preparatoria o técnica  \n",
       "1              Preparatoria o técnica  \n",
       "2             Licenciatura incompleta  \n",
       "3              Preparatoria o técnica  \n",
       "4             Licenciatura incompleta  \n",
       "..                                ...  \n",
       "276            Preparatoria o técnica  \n",
       "277             Licenciatura completa  \n",
       "278           Licenciatura incompleta  \n",
       "279            Preparatoria o técnica  \n",
       "280             Licenciatura completa  \n",
       "\n",
       "[281 rows x 21 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fetch data from google spreadsheet\n",
    "Questionnaire_sheet = client.open(\"Encuesta Viral Code_Updated\").sheet1\n",
    "list_of_hashes = Questionnaire_sheet.get_all_records()\n",
    "Questionnaire_df = pd.DataFrame.from_records(list_of_hashes)\n",
    "Questionnaire_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "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>Timestamp</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>Full Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Birthdate</th>\n",
       "      <th>Place of Birth (Country, State, City)</th>\n",
       "      <th>Civil Status</th>\n",
       "      <th>Children</th>\n",
       "      <th>Child_1 Age</th>\n",
       "      <th>Child_2 Age</th>\n",
       "      <th>...</th>\n",
       "      <th>Child_4 Age</th>\n",
       "      <th>Child_5 Age</th>\n",
       "      <th>Zip Code</th>\n",
       "      <th>Time to commute</th>\n",
       "      <th>Other Source of Income</th>\n",
       "      <th>Hiring Date</th>\n",
       "      <th>Professional Experience</th>\n",
       "      <th>Driving Experience</th>\n",
       "      <th>Previous Companies</th>\n",
       "      <th>Education Level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8/15/2020 13:39:24</td>\n",
       "      <td>dtrejo74@gmail.com</td>\n",
       "      <td>David Alejandro Trejo Atilano</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>1/25/1974</td>\n",
       "      <td>Ciudad de México</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15530</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8/15/2020 13:39:51</td>\n",
       "      <td>josenk11@gmail.com</td>\n",
       "      <td>José Enrique Mariano González</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>2/11/1997</td>\n",
       "      <td>Mexico, distrito federal, Miguel Hidalgo</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>11550</td>\n",
       "      <td>Menos que 30 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8/15/2020 13:39:59</td>\n",
       "      <td>josearmando.svllprz97@gmail.com</td>\n",
       "      <td>José Armando Sevilla Pérez</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>9/22/1997</td>\n",
       "      <td>ciudad de mexico</td>\n",
       "      <td>Soltero(a)</td>\n",
       "      <td>No</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2060</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8/15/2020 13:42:15</td>\n",
       "      <td>dtrejo74@gmail.com</td>\n",
       "      <td>David Alejandro Trejo Atilano</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>1/25/1974</td>\n",
       "      <td>Ciudad de México</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15530</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8/15/2020 13:47:25</td>\n",
       "      <td>ivpalsan67@gmail.com</td>\n",
       "      <td>Iván Palma Sánchezllanes</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>7/9/1968</td>\n",
       "      <td>México DF</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>20-30 años</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4700</td>\n",
       "      <td>De 45' hasta 1 hora</td>\n",
       "      <td>No</td>\n",
       "      <td>8/17/2020</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "      <td>6</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>10/28/2020 13:18:20</td>\n",
       "      <td>alanhandytadeo@gmail.com</td>\n",
       "      <td>Alan josue pantoja Molina</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>6/3/1988</td>\n",
       "      <td>Iztacalco</td>\n",
       "      <td>Casado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td>10-20 años</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>8930</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>No</td>\n",
       "      <td>10/20/2020</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>10/28/2020 15:04:06</td>\n",
       "      <td>seravi89@hotmail.com</td>\n",
       "      <td>Sergio Avila Arriaga</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>4/15/1961</td>\n",
       "      <td>Ciudad de Mexico</td>\n",
       "      <td>Divorciado(a)</td>\n",
       "      <td>Sí</td>\n",
       "      <td></td>\n",
       "      <td>20-30 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9310</td>\n",
       "      <td>De 1 hora hasta 1 hora y media</td>\n",
       "      <td>No</td>\n",
       "      <td>10/28/2020</td>\n",
       "      <td>29</td>\n",
       "      <td>35</td>\n",
       "      <td>7</td>\n",
       "      <td>Licenciatura completa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>10/28/2020 18:21:24</td>\n",
       "      <td>aleex.garcia.perez@gmail.com</td>\n",
       "      <td>Alejandro Garcia Perez</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>10/14/1992</td>\n",
       "      <td>Ciudad de Mxico</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>1-5 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>8500</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, para las dos opciones arriba</td>\n",
       "      <td>10/23/2020</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>Licenciatura incompleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>10/28/2020 18:39:28</td>\n",
       "      <td>samuel76522@gmail.com</td>\n",
       "      <td>Samuel Huerta Miranda</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>6/13/1997</td>\n",
       "      <td>Nezahualcóyotl</td>\n",
       "      <td>Soltero(a)</td>\n",
       "      <td>No</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>9730</td>\n",
       "      <td>Más que 1 hora y media</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>10/27/2020</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Preparatoria o técnica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>10/29/2020 6:58:22</td>\n",
       "      <td>ehigareda2011@hotmail.com</td>\n",
       "      <td>Jesús Enrique Higareda Lima</td>\n",
       "      <td>Hombre</td>\n",
       "      <td>4/30/1975</td>\n",
       "      <td>Mexico, DF</td>\n",
       "      <td>Unión Libre</td>\n",
       "      <td>Sí</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>5-10 años</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2800</td>\n",
       "      <td>De 30' hasta 45 minutos</td>\n",
       "      <td>Sí, otras personas de mi hogar tiene ocupación</td>\n",
       "      <td>10/28/2020</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Licenciatura completa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>281 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Timestamp                    Email Address  \\\n",
       "0     8/15/2020 13:39:24               dtrejo74@gmail.com   \n",
       "1     8/15/2020 13:39:51               josenk11@gmail.com   \n",
       "2     8/15/2020 13:39:59  josearmando.svllprz97@gmail.com   \n",
       "3     8/15/2020 13:42:15               dtrejo74@gmail.com   \n",
       "4     8/15/2020 13:47:25             ivpalsan67@gmail.com   \n",
       "..                   ...                              ...   \n",
       "276  10/28/2020 13:18:20         alanhandytadeo@gmail.com   \n",
       "277  10/28/2020 15:04:06             seravi89@hotmail.com   \n",
       "278  10/28/2020 18:21:24     aleex.garcia.perez@gmail.com   \n",
       "279  10/28/2020 18:39:28            samuel76522@gmail.com   \n",
       "280   10/29/2020 6:58:22        ehigareda2011@hotmail.com   \n",
       "\n",
       "                         Full Name  Gender   Birthdate  \\\n",
       "0    David Alejandro Trejo Atilano  Hombre   1/25/1974   \n",
       "1    José Enrique Mariano González  Hombre   2/11/1997   \n",
       "2       José Armando Sevilla Pérez  Hombre   9/22/1997   \n",
       "3    David Alejandro Trejo Atilano  Hombre   1/25/1974   \n",
       "4         Iván Palma Sánchezllanes  Hombre    7/9/1968   \n",
       "..                             ...     ...         ...   \n",
       "276     Alan josue pantoja Molina   Hombre    6/3/1988   \n",
       "277           Sergio Avila Arriaga  Hombre   4/15/1961   \n",
       "278         Alejandro Garcia Perez  Hombre  10/14/1992   \n",
       "279         Samuel Huerta Miranda   Hombre   6/13/1997   \n",
       "280   Jesús Enrique Higareda Lima   Hombre   4/30/1975   \n",
       "\n",
       "        Place of Birth (Country, State, City)   Civil Status Children  \\\n",
       "0                            Ciudad de México      Casado(a)       Sí   \n",
       "1    Mexico, distrito federal, Miguel Hidalgo    Unión Libre       Sí   \n",
       "2                            ciudad de mexico     Soltero(a)       No   \n",
       "3                            Ciudad de México      Casado(a)       Sí   \n",
       "4                                   México DF      Casado(a)       Sí   \n",
       "..                                        ...            ...      ...   \n",
       "276                                 Iztacalco      Casado(a)       Sí   \n",
       "277                          Ciudad de Mexico  Divorciado(a)       Sí   \n",
       "278                           Ciudad de Mxico    Unión Libre       Sí   \n",
       "279                           Nezahualcóyotl      Soltero(a)       No   \n",
       "280                               Mexico, DF     Unión Libre       Sí   \n",
       "\n",
       "    Child_1 Age Child_2 Age  ... Child_4 Age Child_5 Age Zip Code  \\\n",
       "0    10-20 años              ...                            15530   \n",
       "1      1-5 años    1-5 años  ...                            11550   \n",
       "2                            ...                             2060   \n",
       "3    10-20 años              ...                            15530   \n",
       "4    20-30 años  10-20 años  ...                             4700   \n",
       "..          ...         ...  ...         ...         ...      ...   \n",
       "276  10-20 años   5-10 años  ...                             8930   \n",
       "277              20-30 años  ...                             9310   \n",
       "278   5-10 años    1-5 años  ...                             8500   \n",
       "279                          ...                             9730   \n",
       "280   5-10 años   5-10 años  ...                             2800   \n",
       "\n",
       "                    Time to commute  \\\n",
       "0               De 45' hasta 1 hora   \n",
       "1              Menos que 30 minutos   \n",
       "2           De 30' hasta 45 minutos   \n",
       "3               De 45' hasta 1 hora   \n",
       "4               De 45' hasta 1 hora   \n",
       "..                              ...   \n",
       "276         De 30' hasta 45 minutos   \n",
       "277  De 1 hora hasta 1 hora y media   \n",
       "278         De 30' hasta 45 minutos   \n",
       "279          Más que 1 hora y media   \n",
       "280         De 30' hasta 45 minutos   \n",
       "\n",
       "                             Other Source of Income Hiring Date  \\\n",
       "0                                                No   8/17/2020   \n",
       "1    Sí, otras personas de mi hogar tiene ocupación   8/17/2020   \n",
       "2    Sí, otras personas de mi hogar tiene ocupación   8/17/2020   \n",
       "3                                                No   8/17/2020   \n",
       "4                                                No   8/17/2020   \n",
       "..                                              ...         ...   \n",
       "276                                              No  10/20/2020   \n",
       "277                                              No  10/28/2020   \n",
       "278                Sí, para las dos opciones arriba  10/23/2020   \n",
       "279  Sí, otras personas de mi hogar tiene ocupación  10/27/2020   \n",
       "280  Sí, otras personas de mi hogar tiene ocupación  10/28/2020   \n",
       "\n",
       "    Professional Experience  Driving Experience  Previous Companies  \\\n",
       "0                        22                  22                   4   \n",
       "1                         5                   4                   1   \n",
       "2                         5                   0                   2   \n",
       "3                        22                  22                   4   \n",
       "4                        30                  35                   6   \n",
       "..                      ...                 ...                 ...   \n",
       "276                      14                   4                   4   \n",
       "277                      29                  35                   7   \n",
       "278                       8                   0                   4   \n",
       "279                       0                   4                   3   \n",
       "280                      12                   2                   2   \n",
       "\n",
       "             Education Level  \n",
       "0     Preparatoria o técnica  \n",
       "1     Preparatoria o técnica  \n",
       "2    Licenciatura incompleta  \n",
       "3     Preparatoria o técnica  \n",
       "4    Licenciatura incompleta  \n",
       "..                       ...  \n",
       "276   Preparatoria o técnica  \n",
       "277    Licenciatura completa  \n",
       "278  Licenciatura incompleta  \n",
       "279   Preparatoria o técnica  \n",
       "280    Licenciatura completa  \n",
       "\n",
       "[281 rows x 21 columns]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename columns in English\n",
    "Library_spreadsheet_Name = \"Library Loonshot Questionnaire\"\n",
    "Library_spreadsheet = client.open(Library_spreadsheet_Name)\n",
    "Library_spreadsheet_columns_name = \"Column Names\"  \n",
    "Column_Names_sheet = spreadsheet.worksheet(Library_spreadsheet_columns_name)\n",
    "column_names_df = pd.DataFrame.from_records(Column_Names_sheet.get_all_records())\n",
    "Questionnaire_df.rename(columns=dict(zip(Questionnaire_df.columns.tolist(),column_names_df.New)),inplace = True)\n",
    "# Questionnaire_df.rename(columns=dict(zip(column_names_df.Old,column_names_df.New)),inplace = True)\n",
    "Questionnaire_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "# translate Questionnaire entries in english\n",
    "Library_spreadsheet_translate_name = \"Values Translate\" \n",
    "Translate_sheet = spreadsheet.worksheet(Library_spreadsheet_translate_name)\n",
    "translate_df = pd.DataFrame.from_records(Translate_sheet.get_all_records())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "def translateEntries(df,mapping):\n",
    "    for i in range(mapping.shape[0]):\n",
    "        df.replace(translate_df.iloc[i].MX,translate_df.iloc[i].EN, inplace = True) \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "Questionnaire_df = translateEntries(Questionnaire_df,translate_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert all columns to str\n",
    "all_columns = list(Questionnaire_df) # Creates list of all column headers\n",
    "Questionnaire_df[all_columns] = Questionnaire_df[all_columns].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect with Presto - Here is Argentina !!!\n",
    "cnxn = presto.connect(host=f'presto-tbl-mx.taxibeat.com',port=8080)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0    True"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop existing table\n",
    "# drop_table_if_exists = f'''drop table if exists Loonshot_Questionnaire'''\n",
    "# pd.read_sql_query(drop_table_if_exists,cnxn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0    True"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize Loonshot_Questionnaire table in Presto\n",
    "query_create_table = f'''create table loonshot_questionnaire (\n",
    "                                                         Timestamp                               varchar\n",
    "                                                        ,Email_Address                           varchar\n",
    "                                                        ,Full_Name                               varchar\n",
    "                                                        ,Gender                                  varchar\n",
    "                                                        ,Birthdate                               varchar\n",
    "                                                        ,Place_of_Birth                          varchar\n",
    "                                                        ,Civil_Status                            varchar\n",
    "                                                        ,Children                                varchar\n",
    "                                                        ,Child_1_Age                             varchar \n",
    "                                                        ,Child_2_Age                             varchar\n",
    "                                                        ,Child_3_Age                             varchar\n",
    "                                                        ,Child_4_Age                             varchar\n",
    "                                                        ,Child_5_Age                             varchar\n",
    "                                                        ,Zip_Code                                varchar\n",
    "                                                        ,Time_to_commute                         varchar\n",
    "                                                        ,Other_Source_of_Income                  varchar\n",
    "                                                        ,Hiring_Date                             varchar\n",
    "                                                        ,Professional_Experience                 varchar\n",
    "                                                        ,Driving_Experience                      varchar\n",
    "                                                        ,Previous_Companies                      varchar\n",
    "                                                        ,Education_Level                         varchar\n",
    "                                                        )\n",
    "                                                          '''\n",
    "pd.read_sql_query(query_create_table,cnxn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "def chunks_generator(my_list, chunk_size):\n",
    "    \"\"\"Yield successive n-sized chunks from list my_list.\"\"\"\n",
    "    for i in range(0, len(my_list), chunk_size):\n",
    "        yield my_list[i:i + chunk_size]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import data in Loonshot_Questionnaire table\n",
    "def insert_rows_presto(df,cnxn,presto_table):\n",
    "\n",
    "    df_tmp_control = df.copy()\n",
    "    for col in df_tmp_control.columns:\n",
    "        df[col] = df_tmp_control[col]\n",
    "        \n",
    "    table = presto_table\n",
    "    print('Creating BI table...')\n",
    "    print(' ')\n",
    "    rows = 0\n",
    "    chunk_rows = 500\n",
    "    length_df = len(df)\n",
    "    for data in chunks_generator([str(tuple(row)) for row in df.values], chunk_rows):\n",
    "        rows = rows + chunk_rows\n",
    "        print(f'Inserted {rows} out of {length_df} rows')\n",
    "        data = map(lambda el: el.replace('\"', ''), data)\n",
    "        query = \"INSERT INTO \" + table + \" VALUES \" + \", \".join(data) \n",
    "#         print('')\n",
    "#         print(query)\n",
    "#         print('')\n",
    "        pd.read_sql_query(query,cnxn)\n",
    "    print('')\n",
    "    print(f'Finished creating {presto_table} table')\n",
    "    print('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating BI table...\n",
      " \n",
      "Inserted 500 out of 281 rows\n",
      "\n",
      "Finished creating loonshot_questionnaire table\n",
      "\n"
     ]
    }
   ],
   "source": [
    "insert_rows_presto(Questionnaire_df,cnxn,'loonshot_questionnaire')"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}