google_sheets_to_pandas_to_sheets.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "__authors__ = \"Adriano Henrique Cantão, Rafael Biagioni de Fázio\"\n",
    "__date__ = \"12 Nov 2020\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Google sheets to pandas to sheets\n",
    "#### Importing from and updating to sheets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing libs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "import pandas as pd\n",
    "# import connectmongo\n",
    "# import my_logger\n",
    "# from datetime import datetime\n",
    "# import time\n",
    "# from send_mail import send_warning\n",
    "# import settings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting the access scope"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "scope = ['https://www.googleapis.com/auth/spreadsheets']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Authorizing the access to the spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'full_path_to/credentials.json'\n",
    "creds = ServiceAccountCredentials.from_json_keyfile_name(filename=file_path, scopes=scope)\n",
    "gc = gspread.authorize(creds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Spreadsheet information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Worksheet 'Catanduva' id:0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spread_id = 'change_by_spreadsheet_id'\n",
    "PICOVID = gc.open_by_key(spread_id)\n",
    "CITIES = PICOVID.worksheets()\n",
    "CITIES[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the gspread with pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>date</th>\n",
       "      <th>totalConfirmed</th>\n",
       "      <th>totalSuspect</th>\n",
       "      <th>totalDiscarded</th>\n",
       "      <th>totalNotified</th>\n",
       "      <th>totalDeath</th>\n",
       "      <th>totalCured</th>\n",
       "      <th>totalHospitalized</th>\n",
       "      <th>totalActive</th>\n",
       "      <th>epidemiologicalWeek</th>\n",
       "      <th>estimatedPopulation2019</th>\n",
       "      <th>estimatedPopulation2020</th>\n",
       "      <th>IBGECode</th>\n",
       "      <th>processed</th>\n",
       "      <th>cityName</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>2020-11-07</td>\n",
       "      <td>5929</td>\n",
       "      <td>3204</td>\n",
       "      <td>12534</td>\n",
       "      <td>21667</td>\n",
       "      <td>165</td>\n",
       "      <td>5713</td>\n",
       "      <td>15</td>\n",
       "      <td>51</td>\n",
       "      <td>45</td>\n",
       "      <td>121862</td>\n",
       "      <td>122497</td>\n",
       "      <td>3511102</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>catanduva</td>\n",
       "      <td>sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>2020-11-09</td>\n",
       "      <td>5934</td>\n",
       "      <td>3165</td>\n",
       "      <td>12583</td>\n",
       "      <td>21682</td>\n",
       "      <td>166</td>\n",
       "      <td>5725</td>\n",
       "      <td>12</td>\n",
       "      <td>43</td>\n",
       "      <td>46</td>\n",
       "      <td>121862</td>\n",
       "      <td>122497</td>\n",
       "      <td>3511102</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>catanduva</td>\n",
       "      <td>sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>2020-11-10</td>\n",
       "      <td>5942</td>\n",
       "      <td>3160</td>\n",
       "      <td>12648</td>\n",
       "      <td>21750</td>\n",
       "      <td>167</td>\n",
       "      <td>5735</td>\n",
       "      <td>14</td>\n",
       "      <td>40</td>\n",
       "      <td>46</td>\n",
       "      <td>121862</td>\n",
       "      <td>122497</td>\n",
       "      <td>3511102</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>catanduva</td>\n",
       "      <td>sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>2020-11-11</td>\n",
       "      <td>5947</td>\n",
       "      <td>3174</td>\n",
       "      <td>12694</td>\n",
       "      <td>21815</td>\n",
       "      <td>167</td>\n",
       "      <td>5747</td>\n",
       "      <td>12</td>\n",
       "      <td>33</td>\n",
       "      <td>46</td>\n",
       "      <td>121862</td>\n",
       "      <td>122497</td>\n",
       "      <td>3511102</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>catanduva</td>\n",
       "      <td>sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>5954</td>\n",
       "      <td>3187</td>\n",
       "      <td>12739</td>\n",
       "      <td>21880</td>\n",
       "      <td>168</td>\n",
       "      <td>5755</td>\n",
       "      <td>11</td>\n",
       "      <td>31</td>\n",
       "      <td>46</td>\n",
       "      <td>121862</td>\n",
       "      <td>122497</td>\n",
       "      <td>3511102</td>\n",
       "      <td></td>\n",
       "      <td>catanduva</td>\n",
       "      <td>sp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  totalConfirmed  totalSuspect  totalDiscarded  totalNotified  \\\n",
       "201  2020-11-07            5929          3204           12534          21667   \n",
       "202  2020-11-09            5934          3165           12583          21682   \n",
       "203  2020-11-10            5942          3160           12648          21750   \n",
       "204  2020-11-11            5947          3174           12694          21815   \n",
       "205  2020-11-12            5954          3187           12739          21880   \n",
       "\n",
       "     totalDeath  totalCured  totalHospitalized  totalActive  \\\n",
       "201         165        5713                 15           51   \n",
       "202         166        5725                 12           43   \n",
       "203         167        5735                 14           40   \n",
       "204         167        5747                 12           33   \n",
       "205         168        5755                 11           31   \n",
       "\n",
       "     epidemiologicalWeek  estimatedPopulation2019  estimatedPopulation2020  \\\n",
       "201                   45                   121862                   122497   \n",
       "202                   46                   121862                   122497   \n",
       "203                   46                   121862                   122497   \n",
       "204                   46                   121862                   122497   \n",
       "205                   46                   121862                   122497   \n",
       "\n",
       "     IBGECode processed   cityName state  \n",
       "201   3511102      TRUE  catanduva    sp  \n",
       "202   3511102      TRUE  catanduva    sp  \n",
       "203   3511102      TRUE  catanduva    sp  \n",
       "204   3511102      TRUE  catanduva    sp  \n",
       "205   3511102            catanduva    sp  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = pd.DataFrame(CITIES[0].get_all_records())\n",
    "dataset.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Updating the spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spreadsheetId': '1Vn9orIaXtw-tahQnyyej_8oCpZy9TGww4iXKtaFkNCg',\n",
       " 'updatedRange': 'Catanduva!N2:N207',\n",
       " 'updatedRows': 206,\n",
       " 'updatedColumns': 1,\n",
       " 'updatedCells': 206}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#INSERT DF to database\n",
    "#.......\n",
    "\n",
    "#if success use the update bellow\n",
    "\n",
    "#Total number of rows [including header] of the current worksheet\n",
    "n_rows = len(CITIES[0].get_all_values())\n",
    "\n",
    "#update all rows of column N (processed) to True\n",
    "CITIES[0].update(f'N2:N{n_rows}', [[True]] * (n_rows-1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking the updated column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "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>processed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    processed\n",
       "201      TRUE\n",
       "202      TRUE\n",
       "203      TRUE\n",
       "204      TRUE\n",
       "205      TRUE"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = pd.DataFrame(CITIES[0].get_all_records())\n",
    "pd.DataFrame(dataset['processed'].tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# this is an end, not 'the' end."
   ]
  }
 ],
 "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
}