data-driven_journalism.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A Simple Example of a [Data-driven Journalism](https://en.wikipedia.org/wiki/Data-driven_journalism) Application\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pkg.add(\"CSV\")\n",
    "#Pkg.add(\"DataFrames\")\n",
    "#Pkg.add(\"Query\")\n",
    "using CSV\n",
    "using DataFrames\n",
    "#using Query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1\n",
    "\n",
    "_Which are the people that are affiliated to a Brazilian political party and also are public servants from a Brazilian federal university?_\n",
    "\n",
    "## Data\n",
    "\n",
    "###  Collecting\n",
    "\n",
    "* List of all public servants from the [Brazilian Official Transparency Web portal](http://transparencia.gov.br). \n",
    "* List of [all people affiliated to all Brazilian political parties](http://dados.gov.br/dataset/filiados-partidos-politicos) from the [Brazilian Offical Open Data Portal](http://dados.gov.br).\n",
    "\n",
    "_Remark - BUGFIX: We faced problems when loading the origianl files so we created copied versions of these files to work on._\n",
    "\n",
    "### Cleaning\n",
    "\n",
    "In order to load the data sets, we have to perform the following cleaning tasks.\n",
    "\n",
    "* Convert files from ISO-8859-1 to UFT-8 enconding with `iconv`.\n",
    "* Remove empty fields because of [`CSV.jl`'s Issue#183](https://github.com/JuliaData/CSV.jl/issues/183) with sed by replacing it with \"Sem informacao\" string.\n",
    "* For the federal public servatns data set: replace `BC[0-9]xxx` fields to `0000xxx` so CSV won't complain about converting `B` to `Int641`.\n",
    "* For the federal public servatns data set: remove the first line of the file with `tail` as `sed` adds an empty line in the begining of the file.\n",
    "\n",
    "The following Bash commands do all this job. Please remark that the last command will take $\\sim20$ secs on an i7 processor with SSD disk.\n",
    "\n",
    "```bash\n",
    "cd data-ddj/filiados-al/aplic-7/sead/lista_filiados/uf\n",
    "iconv -f ISO-8859-1 filiados_pt_al.csv -t UTF-8 | sed 's/\\\"\\\"/\\\"Sem informacao\\\"/g' > filiados_pt_al-clean.csv\n",
    "cd -\n",
    "cd data-ddj/servidores/201806_Servidores/\n",
    "unzip 20180630-full-dataset.zip\n",
    "cd 20180630-full-dataset\n",
    "iconv -f ISO-8859-1 20180630_Cadastro.csv -t UTF-8  | sed 's/\\\"\\\"/\\\"Sem informacao\\\"/g' | sed 's/\\\"BC[0-9]/\\\"0000/g'  | tail -n +2 > 20180630_Cadastro-clean.csv\n",
    "```\n",
    "\n",
    "### Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>DATA DA EXTRACAO</th><th>HORA DA EXTRACAO</th><th>NUMERO DA INSCRICAO</th><th>NOME DO FILIADO</th><th>SIGLA DO PARTIDO</th><th>NOME DO PARTIDO</th><th>UF</th><th>CODIGO DO MUNICIPIO</th><th>NOME DO MUNICIPIO</th><th>ZONA ELEITORAL</th><th>SECAO ELEITORAL</th><th>DATA DA FILIACAO</th><th>SITUACAO DO REGISTRO</th><th>TIPO DO REGISTRO</th><th>DATA DO PROCESSAMENTO</th><th>DATA DA DESFILIACAO</th><th>DATA DO CANCELAMENTO</th><th>DATA DA REGULARIZACAO</th><th>MOTIVO DO CANCELAMENTO</th></tr></thead><tbody><tr><th>1</th><td>29/07/2018</td><td>00:57:37</td><td>24221531783</td><td>CARLOS HENRIQUE GUEDES DE ALMEIDA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>1</td><td>39</td><td>15/09/2004</td><td>REGULAR</td><td>OFICIAL</td><td>14/04/2018</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>2</th><td>29/07/2018</td><td>00:57:37</td><td>26591771740</td><td>DJALBA JOAQUIM DOS SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27197</td><td>BOCA DA MATA</td><td>48</td><td>56</td><td>25/09/2007</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>3</th><td>29/07/2018</td><td>00:57:37</td><td>17819191791</td><td>JOSE MATIAS DOS SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28932</td><td>TEOTÔNIO VILELA</td><td>34</td><td>132</td><td>03/07/2003</td><td>REGULAR</td><td>OFICIAL</td><td>14/04/2018</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>4</th><td>29/07/2018</td><td>00:57:37</td><td>4356561708</td><td>RICARDO JOSE MORONI VALENCA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>2</td><td>199</td><td>24/12/1987</td><td>CANCELADO</td><td>OFICIAL</td><td>14/04/2018</td><td>Sem informacao</td><td>02/03/2016</td><td>Sem informacao</td><td>Judicial</td></tr><tr><th>5</th><td>29/07/2018</td><td>00:57:37</td><td>2965641708</td><td>PEDRO CIRILO DOS SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28436</td><td>POÇO DAS TRINCHEIRAS</td><td>50</td><td>18</td><td>08/07/1999</td><td>REGULAR</td><td>OFICIAL</td><td>16/04/2015</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>6</th><td>29/07/2018</td><td>00:57:37</td><td>24600801767</td><td>REGIVANIO PINTO</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28436</td><td>POÇO DAS TRINCHEIRAS</td><td>50</td><td>32</td><td>29/07/1999</td><td>REGULAR</td><td>OFICIAL</td><td>16/04/2015</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>7</th><td>29/07/2018</td><td>00:57:37</td><td>16963271708</td><td>MARINALVA DE OMENA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>2</td><td>93</td><td>02/04/2007</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>8</th><td>29/07/2018</td><td>00:57:37</td><td>25753411708</td><td>GEDIVAN COSTA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>3</td><td>56</td><td>02/09/2007</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>9</th><td>29/07/2018</td><td>00:57:37</td><td>18522211716</td><td>JOSILENE DA SILVA SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>3</td><td>37</td><td>07/07/1999</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>10</th><td>29/07/2018</td><td>00:57:37</td><td>31821091708</td><td>ASSIS ALEXANDRE DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27278</td><td>CAMPO ALEGRE</td><td>47</td><td>55</td><td>10/05/2004</td><td>REGULAR</td><td>OFICIAL</td><td>16/10/2015</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>11</th><td>29/07/2018</td><td>00:57:37</td><td>16376981767</td><td>MOISES TELES DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>54</td><td>121</td><td>23/01/2003</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>12</th><td>29/07/2018</td><td>00:57:37</td><td>24511871708</td><td>MARIA JOSE DOS SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28550</td><td>ROTEIRO</td><td>18</td><td>175</td><td>19/03/2004</td><td>REGULAR</td><td>OFICIAL</td><td>16/10/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>13</th><td>29/07/2018</td><td>00:57:37</td><td>2973651708</td><td>CICERA TENORIO DE HOLANDA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28193</td><td>OLIVENÇA</td><td>19</td><td>41</td><td>24/09/1999</td><td>REGULAR</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>14</th><td>29/07/2018</td><td>00:57:37</td><td>26267491775</td><td>MARCIANGELA GONCALVES DE LIMA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28851</td><td>UNIÃO DOS PALMARES</td><td>21</td><td>64</td><td>30/09/2003</td><td>CANCELADO</td><td>OFICIAL</td><td>15/04/2016</td><td>15/07/2007</td><td>15/07/2007</td><td>Sem informacao</td><td>A pedido do eleitor</td></tr><tr><th>15</th><td>29/07/2018</td><td>00:57:37</td><td>7096321716</td><td>MARIA HELENA DE LIMA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>3</td><td>162</td><td>05/12/1985</td><td>REGULAR</td><td>OFICIAL</td><td>14/04/2018</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>16</th><td>29/07/2018</td><td>00:57:37</td><td>23734801791</td><td>CLAUDEVAN DE LIMA FERREIRA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28690</td><td>SÃO LUÍS DO QUITUNDE</td><td>17</td><td>42</td><td>10/05/1995</td><td>REGULAR</td><td>OFICIAL</td><td>16/10/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>17</th><td>29/07/2018</td><td>00:57:37</td><td>28491491716</td><td>HELVIO ABREU VANDERLEI DE SOUZA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28916</td><td>SENADOR RUI PALMEIRA</td><td>51</td><td>81</td><td>05/10/2007</td><td>CANCELADO</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>20/07/2011</td><td>Sem informacao</td><td>A pedido do eleitor</td></tr><tr><th>18</th><td>29/07/2018</td><td>00:57:37</td><td>4348561775</td><td>ERNALDO BATISTA DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27472</td><td>DELMIRO GOUVEIA</td><td>40</td><td>3</td><td>27/12/1987</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>19</th><td>29/07/2018</td><td>00:57:37</td><td>21628971716</td><td>HELIO BARBOSA DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27278</td><td>CAMPO ALEGRE</td><td>47</td><td>45</td><td>30/08/1999</td><td>REGULAR</td><td>OFICIAL</td><td>16/10/2015</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>20</th><td>29/07/2018</td><td>00:57:37</td><td>30719581759</td><td>DIEGO CARDOSO RAMOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>54</td><td>159</td><td>23/03/2007</td><td>REGULAR</td><td>OFICIAL</td><td>14/04/2018</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>21</th><td>29/07/2018</td><td>00:57:37</td><td>15655780841</td><td>TIAGO JOAO DE OLIVEIRA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28533</td><td>RIO LARGO</td><td>15</td><td>71</td><td>18/05/1988</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>22</th><td>29/07/2018</td><td>00:57:37</td><td>27930081740</td><td>ALINE DE SANTANA LIMA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27855</td><td>MACEIÓ</td><td>54</td><td>152</td><td>25/08/2001</td><td>REGULAR</td><td>OFICIAL</td><td>15/04/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>23</th><td>29/07/2018</td><td>00:57:37</td><td>5073271783</td><td>ANA LUCIA DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28371</td><td>PILAR</td><td>8</td><td>24</td><td>25/10/1995</td><td>REGULAR</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>24</th><td>29/07/2018</td><td>00:57:37</td><td>5073271783</td><td>ANA LUCIA DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28371</td><td>PILAR</td><td>8</td><td>24</td><td>25/10/1996</td><td>CANCELADO</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>04/12/2009</td><td>Sem informacao</td><td>Judicial</td></tr><tr><th>25</th><td>29/07/2018</td><td>00:57:37</td><td>23805661783</td><td>HUGO ALVES</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28452</td><td>PORTO CALVO</td><td>14</td><td>68</td><td>15/08/1999</td><td>DESFILIADO</td><td>OFICIAL</td><td>15/10/2016</td><td>14/04/2011</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>26</th><td>29/07/2018</td><td>00:57:37</td><td>4700291759</td><td>MARIA DO CARMO PANTALEAO</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28495</td><td>PORTO REAL DO COLÉGIO</td><td>37</td><td>5</td><td>10/05/1995</td><td>REGULAR</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>27</th><td>29/07/2018</td><td>00:57:37</td><td>25328261732</td><td>MARCOS SOARES DA CRUZ</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27634</td><td>IGREJA NOVA</td><td>37</td><td>164</td><td>10/09/2003</td><td>REGULAR</td><td>OFICIAL</td><td>17/04/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>28</th><td>29/07/2018</td><td>00:57:37</td><td>18890861759</td><td>JAIRA SANTOS DA SILVA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>28274</td><td>PÃO DE AÇÚCAR</td><td>11</td><td>73</td><td>01/04/1992</td><td>REGULAR</td><td>OFICIAL</td><td>18/04/2017</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>29</th><td>29/07/2018</td><td>00:57:37</td><td>17904231740</td><td>GILVAN PEREIRA DOS SANTOS</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27022</td><td>PARICONHA</td><td>39</td><td>53</td><td>28/09/2003</td><td>REGULAR</td><td>OFICIAL</td><td>16/10/2012</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>30</th><td>29/07/2018</td><td>00:57:37</td><td>7175031759</td><td>MANOEL MARQUES DA COSTA</td><td>PT</td><td>PARTIDO DOS TRABALHADORES</td><td>AL</td><td>27030</td><td>ANADIA</td><td>48</td><td>109</td><td>14/12/1995</td><td>REGULAR</td><td>OFICIAL</td><td>15/10/2016</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td><td>Sem informacao</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></tbody></table>"
      ],
      "text/plain": [
       "13155×19 DataFrames.DataFrame. Omitted printing of 16 columns\n",
       "│ Row   │ DATA DA EXTRACAO │ HORA DA EXTRACAO │ NUMERO DA INSCRICAO │\n",
       "├───────┼──────────────────┼──────────────────┼─────────────────────┤\n",
       "│ 1     │ 29/07/2018       │ 00:57:37         │ 24221531783         │\n",
       "│ 2     │ 29/07/2018       │ 00:57:37         │ 26591771740         │\n",
       "│ 3     │ 29/07/2018       │ 00:57:37         │ 17819191791         │\n",
       "│ 4     │ 29/07/2018       │ 00:57:37         │ 4356561708          │\n",
       "│ 5     │ 29/07/2018       │ 00:57:37         │ 2965641708          │\n",
       "│ 6     │ 29/07/2018       │ 00:57:37         │ 24600801767         │\n",
       "│ 7     │ 29/07/2018       │ 00:57:37         │ 16963271708         │\n",
       "│ 8     │ 29/07/2018       │ 00:57:37         │ 25753411708         │\n",
       "│ 9     │ 29/07/2018       │ 00:57:37         │ 18522211716         │\n",
       "│ 10    │ 29/07/2018       │ 00:57:37         │ 31821091708         │\n",
       "│ 11    │ 29/07/2018       │ 00:57:37         │ 16376981767         │\n",
       "⋮\n",
       "│ 13144 │ 29/07/2018       │ 00:57:37         │ 27863181724         │\n",
       "│ 13145 │ 29/07/2018       │ 00:57:37         │ 28002491708         │\n",
       "│ 13146 │ 29/07/2018       │ 00:57:37         │ 4265641759          │\n",
       "│ 13147 │ 29/07/2018       │ 00:57:37         │ 31080301783         │\n",
       "│ 13148 │ 29/07/2018       │ 00:57:37         │ 31127061716         │\n",
       "│ 13149 │ 29/07/2018       │ 00:57:37         │ 22265551740         │\n",
       "│ 13150 │ 29/07/2018       │ 00:57:37         │ 25380871775         │\n",
       "│ 13151 │ 29/07/2018       │ 00:57:37         │ 31437931716         │\n",
       "│ 13152 │ 29/07/2018       │ 00:57:37         │ 36678471732         │\n",
       "│ 13153 │ 29/07/2018       │ 00:57:37         │ 36680511767         │\n",
       "│ 13154 │ 29/07/2018       │ 00:57:37         │ 37190481724         │\n",
       "│ 13155 │ 29/07/2018       │ 00:57:37         │ 37703261791         │"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading dataset: all people affiliate to the political party (ex.: PT)\n",
    "path_ptal = \"data-ddj/filiados-al/aplic-7/sead/lista_filiados/uf/filiados_pt_al-clean.csv\"\n",
    "ptal = CSV.read(path_ptal,delim=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "elapsed time: 37.758236406 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "37.758236406"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading dataset: all federal public servants (ex.: only active public servants)\n",
    "tic()\n",
    "path_pservants = \"data-ddj/servidores/201806_Servidores/20180630-full-dataset/20180630_Cadastro-clean.csv\"\n",
    "#TODO path_pservant_afastados = ...\n",
    "pservants = CSV.read(path_pservants,delim=\";\")\n",
    "#TODO pservant_afastados = CSV.read(path_pservant_afastados,delim=\";\")\n",
    "toc()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter \n",
    "\n",
    "* Getting all active (\"regular\" status) people from the party.\n",
    "* Getting the names of all public servants from UFAL and are affiliate to the party."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "regulares_ptal = []\n",
    "for row in eachrow(ptal)\n",
    "    if row[Symbol(\"SITUACAO DO REGISTRO\")] == \"REGULAR\"\n",
    "        push!(regulares_ptal,row[Symbol(\"NOME DO FILIADO\")])\n",
    "    end\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "pservants_ufal_pt = []\n",
    "for row in eachrow(pservants) #TODO include pservant_afastados\n",
    "    if row[Symbol(\"ORG_EXERCICIO\")] == \"UNIVERSIDADE FEDERAL DE ALAGOAS\"\n",
    "        #push!(pservants_ufal_pt,row[Symbol(\"NOME\")])\n",
    "        #TODO Handle homonymous people, e.g., by using Brazilian CPFs (social security numbers)\n",
    "        if findfirst(regulares_ptal,row[Symbol(\"NOME\")]) != 0\n",
    "            push!(pservants_ufal_pt,row[Symbol(\"NOME\")])\n",
    "        end\n",
    "    end\n",
    "end\n",
    "\n",
    "# TODO Example using Query.jl\n",
    "# query = @from i in pservants_ufal begin\n",
    "#     @where i.Symbol(\"NOME\") == \n",
    "# end"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualize\n",
    "\n",
    "**DISCLAIMER**: Owing to homonymous people and to the data sets which might not be updated, the results showed here might not reflect the reality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2164"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pservants_ufal_pt\n",
    "\n",
    "c = \"\"\n",
    "for p in pservants_ufal_pt\n",
    "    c = \"$(c) - $(p)\"\n",
    "end\n",
    "write(\"a\",c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Story\n",
    "\n",
    "TODO were affiliated to TODO party in TODO and also is a public servant at TODO because she/he believes that TODO."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "# Question 2\n",
    "_How are distributed the political party affiliation in a Brazilian federal university?_\n",
    "\n",
    "---\n",
    "# Question 3\n",
    "_How often people change political parties?_\n",
    "\n",
    "---\n",
    "# Question 4\n",
    "_How often people join and/or quit a political party?_\n",
    "\n",
    "---\n",
    "# Question 5\n",
    "_Which are the reasons for people quitting the parties? Which is the party that has more judicial reasons for quitting the party?_\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.6.3",
   "language": "julia",
   "name": "julia-0.6"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}