04 - Analisando Votos.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Relatório de Análise 4\n",
    "\n",
    "### Analisando dados de votação na cidade de São Paulo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/10/2012</td>\n",
       "      <td>19:39:52</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>ELEIÇÃO MUNICIPAL 2012</td>\n",
       "      <td>SP</td>\n",
       "      <td>71072</td>\n",
       "      <td>71072</td>\n",
       "      <td>SÃO PAULO</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>PREFEITO</td>\n",
       "      <td>10</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08/10/2012</td>\n",
       "      <td>19:39:52</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>ELEIÇÃO MUNICIPAL 2012</td>\n",
       "      <td>SP</td>\n",
       "      <td>71072</td>\n",
       "      <td>71072</td>\n",
       "      <td>SÃO PAULO</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>PREFEITO</td>\n",
       "      <td>13</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08/10/2012</td>\n",
       "      <td>19:39:52</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>ELEIÇÃO MUNICIPAL 2012</td>\n",
       "      <td>SP</td>\n",
       "      <td>71072</td>\n",
       "      <td>71072</td>\n",
       "      <td>SÃO PAULO</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>PREFEITO</td>\n",
       "      <td>15</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>08/10/2012</td>\n",
       "      <td>19:39:52</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>ELEIÇÃO MUNICIPAL 2012</td>\n",
       "      <td>SP</td>\n",
       "      <td>71072</td>\n",
       "      <td>71072</td>\n",
       "      <td>SÃO PAULO</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>PREFEITO</td>\n",
       "      <td>23</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>08/10/2012</td>\n",
       "      <td>19:39:52</td>\n",
       "      <td>2012</td>\n",
       "      <td>1</td>\n",
       "      <td>ELEIÇÃO MUNICIPAL 2012</td>\n",
       "      <td>SP</td>\n",
       "      <td>71072</td>\n",
       "      <td>71072</td>\n",
       "      <td>SÃO PAULO</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>PREFEITO</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0         1     2   3                       4   5      6      7   \\\n",
       "0  08/10/2012  19:39:52  2012   1  ELEIÇÃO MUNICIPAL 2012  SP  71072  71072   \n",
       "1  08/10/2012  19:39:52  2012   1  ELEIÇÃO MUNICIPAL 2012  SP  71072  71072   \n",
       "2  08/10/2012  19:39:52  2012   1  ELEIÇÃO MUNICIPAL 2012  SP  71072  71072   \n",
       "3  08/10/2012  19:39:52  2012   1  ELEIÇÃO MUNICIPAL 2012  SP  71072  71072   \n",
       "4  08/10/2012  19:39:52  2012   1  ELEIÇÃO MUNICIPAL 2012  SP  71072  71072   \n",
       "\n",
       "          8    9   10  11        12  13  14  \n",
       "0  SÃO PAULO  417  78  11  PREFEITO  10  47  \n",
       "1  SÃO PAULO  417  78  11  PREFEITO  13  82  \n",
       "2  SÃO PAULO  417  78  11  PREFEITO  15  61  \n",
       "3  SÃO PAULO  417  78  11  PREFEITO  23  13  \n",
       "4  SÃO PAULO  417  78  11  PREFEITO  29   1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados = pd.read_csv('dados/votos.csv', sep = ';', encoding = 'latin-1', header = None)\n",
    "dados.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2381878, 15)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtrando apenas para candidatos a vereador\n",
    "- Observar que não há dados com quantidade de votos na seção nulo ou zero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Candidato</th>\n",
       "      <th>Zona</th>\n",
       "      <th>Seção</th>\n",
       "      <th>QtdVotos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>237252</th>\n",
       "      <td>10000</td>\n",
       "      <td>417</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491716</th>\n",
       "      <td>33140</td>\n",
       "      <td>371</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491715</th>\n",
       "      <td>31192</td>\n",
       "      <td>371</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491714</th>\n",
       "      <td>29329</td>\n",
       "      <td>371</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491710</th>\n",
       "      <td>25100</td>\n",
       "      <td>371</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Candidato  Zona  Seção  QtdVotos\n",
       "237252       10000   417     78         1\n",
       "1491716      33140   371    263         1\n",
       "1491715      31192   371    263         1\n",
       "1491714      29329   371    263         1\n",
       "1491710      25100   371    263         1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.rename(columns = {8 : 'Cidade', 9 : 'Zona' , 10 : 'Seção', 12 : 'Cargo', 13 : 'Candidato', 14: 'QtdVotos' }, inplace = True)\n",
    "selection = (dados.Cargo == 'VEREADOR') & (dados.Candidato > 999)\n",
    "dados = dados[selection][['Candidato', 'Zona', 'Seção', 'QtdVotos']]\n",
    "dados.sort_values(by = 'QtdVotos', inplace = True)\n",
    "dados.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1860152, 4)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gerando tabela de frequência por seção eleitoral "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Candidato</th>\n",
       "      <th>Frequência</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43666</td>\n",
       "      <td>21551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45190</td>\n",
       "      <td>20693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11333</td>\n",
       "      <td>18834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40096</td>\n",
       "      <td>18281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65000</td>\n",
       "      <td>18107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Candidato  Frequência\n",
       "0      43666       21551\n",
       "1      45190       20693\n",
       "2      11333       18834\n",
       "3      40096       18281\n",
       "4      65000       18107"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "freq_secao = pd.Series(dados.Candidato.value_counts().sort_values(ascending = False)).rename_axis('Candidato').reset_index(name='Frequência')\n",
    "freq_secao.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modificando a tabela de dados para obter observações únicas por zona eleitoral"
   ]
  },
  {
   "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>Candidato</th>\n",
       "      <th>Zona</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>237252</th>\n",
       "      <td>10000</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491716</th>\n",
       "      <td>33140</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491715</th>\n",
       "      <td>31192</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491714</th>\n",
       "      <td>29329</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491710</th>\n",
       "      <td>25100</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Candidato  Zona\n",
       "237252       10000   417\n",
       "1491716      33140   371\n",
       "1491715      31192   371\n",
       "1491714      29329   371\n",
       "1491710      25100   371"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.drop(['Seção','QtdVotos'], axis = 1, inplace = True)\n",
    "dados.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1860152, 2)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Candidato</th>\n",
       "      <th>Zona</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>237252</th>\n",
       "      <td>10000</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491716</th>\n",
       "      <td>33140</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491715</th>\n",
       "      <td>31192</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491714</th>\n",
       "      <td>29329</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491710</th>\n",
       "      <td>25100</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Candidato  Zona\n",
       "237252       10000   417\n",
       "1491716      33140   371\n",
       "1491715      31192   371\n",
       "1491714      29329   371\n",
       "1491710      25100   371"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.drop_duplicates(inplace = True)\n",
    "dados.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50496, 2)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dados.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gerando tabela de frequência por zona eleitoral "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Candidato</th>\n",
       "      <th>Frequência</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13130</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50077</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22122</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13100</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13112</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Candidato  Frequência\n",
       "0      13130          58\n",
       "1      50077          58\n",
       "2      22122          58\n",
       "3      13100          58\n",
       "4      13112          58"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "freq_zona = pd.Series(dados.Candidato.value_counts().sort_values(ascending = False)).rename_axis('Candidato').reset_index(name='Frequência')\n",
    "freq_zona.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exportando tabelas de frequência"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "freq_secao.to_csv('dados/freq_secao.csv', sep = ';', index = False, encoding = 'latin-1', header = False)\n",
    "freq_zona.to_csv('dados/freq_zona.csv', sep = ';', index = False, encoding = 'latin-1', header = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}