estudos_tempo_votacao.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4de72873",
   "metadata": {},
   "source": [
    "<h1 style=\"font-size:3rem;color:orange;\">Eleições 2022 - Análise do Tempo de Votação</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d489ea1e",
   "metadata": {},
   "source": [
    "AVISO de Licença de Uso e Reserva de Direitos Autorais BSD 3 Clause. Copyright (c) 2022 por John Robson <john.robson@msn.com> (PIX)\n",
    "\n",
    "Todo o Código fonte e demais arquivos estão sobre a Licença BSD 3 Clause. Modificações, Redistribuição, uso Comercial são permitidos, sempre informando\n",
    "esse aviso de direitos autorais e o repositório oficial: https://github.com/JohnRobson/Eleicoes2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7542de9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.OutputArea.auto_scroll_threshold = 9999;\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "IPython.OutputArea.auto_scroll_threshold = 9999;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8ba82b44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>div.output_scroll { height: 100%; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import timeit\n",
    "import ast\n",
    "from sqlalchemy import select, func\n",
    "\n",
    "import matplotlib.pylab as plt\n",
    "%matplotlib inline\n",
    "\n",
    "from IPython.display import Markdown, display, HTML\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "display(HTML(\"<style>div.output_scroll { height: 100%; }</style>\"))\n",
    "\n",
    "from bd_urnas import engine, Base, Session, Urnas\n",
    "from src_config import federacao, estados_federacao\n",
    "session = Session()\n",
    "\n",
    "def printmd(string): # convert markdown para HTML\n",
    "    display(Markdown(string))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "646afe72",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) no such column: vConfPres10min\n[SQL: SELECT id, turno, regiao, estado, municipio, zona, local, secao, vPresA22, vPresB13, vPresNulo, vPresBranco,vGovA, vGovB, vGovNulo, vGovBranco, qEleitAptos, qEleitAptos, qComparecimento, qEleitLibCodigo, qEleitBiometrico, vPresTotal, modelUrna,vConfPres10min, arquivoBU FROM urnas]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1900\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1899\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[0;32m-> 1900\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1901\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1902\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1904\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    735\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m--> 736\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mOperationalError\u001b[0m: no such column: vConfPres10min",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [3], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# Importando Banco de Dados para Python Pandas Dataframe\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m bd \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_sql_query\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mSELECT id, turno, regiao, estado, municipio, zona, local, secao, vPresA22, vPresB13, vPresNulo, vPresBranco,\u001b[39;49m\u001b[38;5;130;43;01m\\\u001b[39;49;00m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124;43mvGovA, vGovB, vGovNulo, vGovBranco, qEleitAptos, qEleitAptos, qComparecimento, qEleitLibCodigo, qEleitBiometrico, vPresTotal, modelUrna,\u001b[39;49m\u001b[38;5;130;43;01m\\\u001b[39;49;00m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;124;43mvConfPres10min, arquivoBU FROM urnas\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43msession\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mbind\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      5\u001b[0m bd\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/pandas/io/sql.py:397\u001b[0m, in \u001b[0;36mread_sql_query\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)\u001b[0m\n\u001b[1;32m    339\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    340\u001b[0m \u001b[38;5;124;03mRead SQL query into a DataFrame.\u001b[39;00m\n\u001b[1;32m    341\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    394\u001b[0m \u001b[38;5;124;03mparameter will be converted to UTC.\u001b[39;00m\n\u001b[1;32m    395\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    396\u001b[0m pandas_sql \u001b[38;5;241m=\u001b[39m pandasSQL_builder(con)\n\u001b[0;32m--> 397\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    398\u001b[0m \u001b[43m    \u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    399\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    400\u001b[0m \u001b[43m    \u001b[49m\u001b[43mparams\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    401\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcoerce_float\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcoerce_float\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    402\u001b[0m \u001b[43m    \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    403\u001b[0m \u001b[43m    \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    404\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    405\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/pandas/io/sql.py:1560\u001b[0m, in \u001b[0;36mSQLDatabase.read_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1512\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   1513\u001b[0m \u001b[38;5;124;03mRead SQL query into a DataFrame.\u001b[39;00m\n\u001b[1;32m   1514\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1556\u001b[0m \n\u001b[1;32m   1557\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   1558\u001b[0m args \u001b[38;5;241m=\u001b[39m _convert_params(sql, params)\n\u001b[0;32m-> 1560\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1561\u001b[0m columns \u001b[38;5;241m=\u001b[39m result\u001b[38;5;241m.\u001b[39mkeys()\n\u001b[1;32m   1563\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m chunksize \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/pandas/io/sql.py:1405\u001b[0m, in \u001b[0;36mSQLDatabase.execute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1403\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mexecute\u001b[39m(\u001b[38;5;28mself\u001b[39m, \u001b[38;5;241m*\u001b[39margs, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs):\n\u001b[1;32m   1404\u001b[0m     \u001b[38;5;124;03m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[39;00m\n\u001b[0;32m-> 1405\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnectable\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecution_options\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m<string>:2\u001b[0m, in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py:402\u001b[0m, in \u001b[0;36m_decorate_with_warning.<locals>.warned\u001b[0;34m(fn, *args, **kwargs)\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m skip_warning:\n\u001b[1;32m    401\u001b[0m     _warn_with_version(message, version, wtype, stacklevel\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m3\u001b[39m)\n\u001b[0;32m--> 402\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfn\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3257\u001b[0m, in \u001b[0;36mEngine.execute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   3239\u001b[0m \u001b[38;5;124;03m\"\"\"Executes the given construct and returns a\u001b[39;00m\n\u001b[1;32m   3240\u001b[0m \u001b[38;5;124;03m:class:`_engine.CursorResult`.\u001b[39;00m\n\u001b[1;32m   3241\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   3254\u001b[0m \n\u001b[1;32m   3255\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   3256\u001b[0m connection \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mconnect(close_with_result\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m)\n\u001b[0;32m-> 3257\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mmultiparams\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1365\u001b[0m, in \u001b[0;36mConnection.execute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1356\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(statement, util\u001b[38;5;241m.\u001b[39mstring_types):\n\u001b[1;32m   1357\u001b[0m     util\u001b[38;5;241m.\u001b[39mwarn_deprecated_20(\n\u001b[1;32m   1358\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mPassing a string to Connection.execute() is \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   1359\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdeprecated and will be removed in version 2.0.  Use the \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1362\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdriver-level SQL string.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   1363\u001b[0m     )\n\u001b[0;32m-> 1365\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_exec_driver_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1366\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1367\u001b[0m \u001b[43m        \u001b[49m\u001b[43mmultiparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1368\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1369\u001b[0m \u001b[43m        \u001b[49m\u001b[43m_EMPTY_EXECUTION_OPTS\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1370\u001b[0m \u001b[43m        \u001b[49m\u001b[43mfuture\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m   1371\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1373\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m   1374\u001b[0m     meth \u001b[38;5;241m=\u001b[39m statement\u001b[38;5;241m.\u001b[39m_execute_on_connection\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1669\u001b[0m, in \u001b[0;36mConnection._exec_driver_sql\u001b[0;34m(self, statement, multiparams, params, execution_options, future)\u001b[0m\n\u001b[1;32m   1659\u001b[0m         (\n\u001b[1;32m   1660\u001b[0m             statement,\n\u001b[1;32m   1661\u001b[0m             distilled_params,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1665\u001b[0m             statement, distilled_parameters, execution_options\n\u001b[1;32m   1666\u001b[0m         )\n\u001b[1;32m   1668\u001b[0m dialect \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect\n\u001b[0;32m-> 1669\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_execute_context\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1670\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1671\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecution_ctx_cls\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_init_statement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1672\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1673\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1674\u001b[0m \u001b[43m    \u001b[49m\u001b[43mexecution_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1675\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1676\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1677\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1679\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m future:\n\u001b[1;32m   1680\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1943\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1940\u001b[0m             branched\u001b[38;5;241m.\u001b[39mclose()\n\u001b[1;32m   1942\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[0;32m-> 1943\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_dbapi_exception\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1944\u001b[0m \u001b[43m        \u001b[49m\u001b[43me\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1945\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1947\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2124\u001b[0m, in \u001b[0;36mConnection._handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   2122\u001b[0m     util\u001b[38;5;241m.\u001b[39mraise_(newraise, with_traceback\u001b[38;5;241m=\u001b[39mexc_info[\u001b[38;5;241m2\u001b[39m], from_\u001b[38;5;241m=\u001b[39me)\n\u001b[1;32m   2123\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m should_wrap:\n\u001b[0;32m-> 2124\u001b[0m     \u001b[43mutil\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   2125\u001b[0m \u001b[43m        \u001b[49m\u001b[43msqlalchemy_exception\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mwith_traceback\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mexc_info\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m2\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfrom_\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43me\u001b[49m\n\u001b[1;32m   2126\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2127\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m   2128\u001b[0m     util\u001b[38;5;241m.\u001b[39mraise_(exc_info[\u001b[38;5;241m1\u001b[39m], with_traceback\u001b[38;5;241m=\u001b[39mexc_info[\u001b[38;5;241m2\u001b[39m])\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208\u001b[0m, in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m     exception\u001b[38;5;241m.\u001b[39m__cause__ \u001b[38;5;241m=\u001b[39m replace_context\n\u001b[1;32m    207\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 208\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exception\n\u001b[1;32m    209\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m    210\u001b[0m     \u001b[38;5;66;03m# credit to\u001b[39;00m\n\u001b[1;32m    211\u001b[0m     \u001b[38;5;66;03m# https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/\u001b[39;00m\n\u001b[1;32m    212\u001b[0m     \u001b[38;5;66;03m# as the __traceback__ object creates a cycle\u001b[39;00m\n\u001b[1;32m    213\u001b[0m     \u001b[38;5;28;01mdel\u001b[39;00m exception, replace_context, from_, with_traceback\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1900\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1898\u001b[0m                 \u001b[38;5;28;01mbreak\u001b[39;00m\n\u001b[1;32m   1899\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[0;32m-> 1900\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1901\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1902\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1904\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n\u001b[1;32m   1905\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdispatch\u001b[38;5;241m.\u001b[39mafter_cursor_execute(\n\u001b[1;32m   1906\u001b[0m         \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m   1907\u001b[0m         cursor,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1911\u001b[0m         context\u001b[38;5;241m.\u001b[39mexecutemany,\n\u001b[1;32m   1912\u001b[0m     )\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    735\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m--> 736\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) no such column: vConfPres10min\n[SQL: SELECT id, turno, regiao, estado, municipio, zona, local, secao, vPresA22, vPresB13, vPresNulo, vPresBranco,vGovA, vGovB, vGovNulo, vGovBranco, qEleitAptos, qEleitAptos, qComparecimento, qEleitLibCodigo, qEleitBiometrico, vPresTotal, modelUrna,vConfPres10min, arquivoBU FROM urnas]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
     ]
    }
   ],
   "source": [
    "# Importando Banco de Dados para Python Pandas Dataframe\n",
    "bd = pd.read_sql_query('SELECT id, turno, regiao, estado, municipio, zona, local, secao, vPresA22, vPresB13, vPresNulo, vPresBranco,\\\n",
    "vGovA, vGovB, vGovNulo, vGovBranco, qEleitAptos, qEleitAptos, qComparecimento, qEleitLibCodigo, qEleitBiometrico, vPresTotal, modelUrna,\\\n",
    "vConfPres10min, arquivoBU FROM urnas', session.bind)\n",
    "bd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20d23526",
   "metadata": {},
   "source": [
    "# Calculando Votos Intervalo de Tempo Entre os Votos Registrados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d04bc93",
   "metadata": {},
   "outputs": [],
   "source": [
    "alto_comparecimento = bd.sort_values(by=['qComparecimento'], ascending=False) # ordena por quantidade de comparecimento\n",
    "cols = ['vPresIntervalo', 'vConfPres10min'] # se não existir registro de votos remove a urna \n",
    "alto_comparecimento[cols] = alto_comparecimento[cols].replace({'0':np.nan, 0:np.nan})\n",
    "alto_comparecimento.dropna(subset=alto_comparecimento.loc[:0, cols].columns, inplace=True)\n",
    "\n",
    "m1 = 0; m2 = 0\n",
    "\n",
    "for i in range(2):\n",
    "    urna = alto_comparecimento.iloc[i]\n",
    "    printmd(f'{i} - {urna[\"regiao\"]}, {urna[\"estado\"]}, {urna[\"municipio\"]}, {urna[\"zona\"]}, {urna[\"secao\"]}, {urna[\"local\"]} - Comparecimento: {urna[\"qComparecimento\"]}')\n",
    "\n",
    "    fig, ax = plt.subplots(1, 3, gridspec_kw={'width_ratios': [10, 1, 2]}, figsize=(12, 5))\n",
    "    \n",
    "    votos10min = dict(ast.literal_eval(urna['vConfPres10min']).items()) # lê o string_dict e converte para dict\n",
    "    x, y = zip(*votos10min.items())\n",
    "    x = [str(i) for i in x] # converter x de int para str\n",
    "    ax[0].bar(x, y);\n",
    "    ax[0].title.set_text('Quantidade de Votos a Cada 10 Minutos')\n",
    "    ax[0].tick_params(axis='x', labelrotation=90, labelsize=8)\n",
    "    ax[0].set_ylim([0, 25])\n",
    "    ax[0].margins(0.01, None)\n",
    "    m1 = max(y) if max(y) >= m1 else m1\n",
    "    \n",
    "    menores_intervalos_voto = list(map(int, urna['vPresIntervalo'].split(','))) # converte string de int para lista de int\n",
    "    ax[1].boxplot(menores_intervalos_voto, notch=True, patch_artist=True, boxprops=dict(facecolor='lightblue'));\n",
    "    ax[1].title.set_text('Votos Rápidos')\n",
    "    ax[1].get_xaxis().set_ticks([])\n",
    "    \n",
    "    votos60min = pd.DataFrame(votos10min.values(), index=votos10min.keys()) # converte o dicionário para um dataframe\n",
    "    votos60min = votos60min.groupby(votos60min.index // 6).sum().to_dict()[0] # soma a cada 6 linhas e converte para dict\n",
    "    x, y = zip(*votos60min.items())\n",
    "    x = [str(i) for i in x] # converter x de int para str\n",
    "    ax[2].bar(x, y);\n",
    "    ax[2].title.set_text('Qtd de Votos Por Hora')\n",
    "    ax[2].tick_params(axis='x', labelsize=8)\n",
    "    ax[2].set_ylim([0, 125])\n",
    "    m2 = max(y) if max(y) >= m2 else m2\n",
    "    \n",
    "    fig.tight_layout();\n",
    "    plt.show();\n",
    "\n",
    "printmd(f'Máximo a cada 10 min {m1} e 1 hora {m2}. Analisados {i} urnas em ordem de quantidade de comparecimento.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4111b4ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [] # lista de dicionários\n",
    "for index, row in alto_comparecimento.iterrows():\n",
    "    data.append(ast.literal_eval(row['vConfPres10min'])) # converte os items da columa em dicionários e adiciona na lista\n",
    "\n",
    "votosPres10min = pd.DataFrame(data, index=alto_comparecimento.index, columns=list(range(0, 61)))\n",
    "votosPres10min"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45a68ebd",
   "metadata": {},
   "source": [
    "## REVISAR ABAIXO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a79413f",
   "metadata": {},
   "outputs": [],
   "source": [
    "votosPres10min.iloc[:, 0].max()\n",
    "votosPres10min.max()\n",
    "votosPres10min_max = votosPres10min.max().max()\n",
    "printmd(f'Máximo e Votos a cada 1 Hora: {votosPres10min_max}, ou 1 voto a cada {round((60 * 60) / votosPres10min_max, 1)} segundos.')\n",
    "votosPres10min_mean = votosPres10min.mean().mean()\n",
    "printmd(f'Média de Votos a cada 1 Hora: {round(votosPres10min_mean, 1)}, ou 1 voto a cada {round((60 * 60) / votosPres10min_mean, 1)} segundos.')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}