{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "# pip3 install simplekml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "import simplekml as kml\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "df = pd.read_excel('res5.xlsx', sheet_name='dados')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "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>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>cod_localidade_tse</th>\n",
       "      <th>nom_localidade</th>\n",
       "      <th>nom_bairro</th>\n",
       "      <th>num_local</th>\n",
       "      <th>nom_local</th>\n",
       "      <th>des_endereco</th>\n",
       "      <th>codigo</th>\n",
       "      <th>lqtd_secoes</th>\n",
       "      <th>...</th>\n",
       "      <th>crimes_eleitorais</th>\n",
       "      <th>boca_de_urna</th>\n",
       "      <th>roubo</th>\n",
       "      <th>furto</th>\n",
       "      <th>desacato</th>\n",
       "      <th>desobediencia</th>\n",
       "      <th>CV</th>\n",
       "      <th>ind_cid</th>\n",
       "      <th>GRUPOS</th>\n",
       "      <th>efetivo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-61.504226</td>\n",
       "      <td>-10.870787</td>\n",
       "      <td>89960</td>\n",
       "      <td>RONDOLÂNDIA</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>1015</td>\n",
       "      <td>ESCOLA ESTADUAL OLAVO BILAC</td>\n",
       "      <td>RUA MATHILDE KLEMS, S/N</td>\n",
       "      <td>61899601015</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>336</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-60.941759</td>\n",
       "      <td>-10.475339</td>\n",
       "      <td>89960</td>\n",
       "      <td>RONDOLÂNDIA</td>\n",
       "      <td>ZONA RURAL</td>\n",
       "      <td>1066</td>\n",
       "      <td>ESCOLA ZAWA KAREY PANGYJY</td>\n",
       "      <td>ALDEIA INDÍGENA ZORÓ</td>\n",
       "      <td>61899601066</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>337</td>\n",
       "      <td>0.077778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-60.180703</td>\n",
       "      <td>-14.107886</td>\n",
       "      <td>90867</td>\n",
       "      <td>NOVA LACERDA</td>\n",
       "      <td>GLEBA SANTA AMÉLIA</td>\n",
       "      <td>1040</td>\n",
       "      <td>ESCOLA MUNICIPAL VALE DO GUAPORÉ</td>\n",
       "      <td>COMUNIDADE SANTA AMÉLIA</td>\n",
       "      <td>61908671040</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>444</td>\n",
       "      <td>0.077778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-59.967301</td>\n",
       "      <td>-15.014093</td>\n",
       "      <td>91090</td>\n",
       "      <td>VILA BELA DA SANTÍSSIMA TRINDADE</td>\n",
       "      <td>JARDIM AEROPORTO</td>\n",
       "      <td>1341</td>\n",
       "      <td>CRECHE AVIAOZINHO</td>\n",
       "      <td>AVENIDA MARTIMIANO RIBEIRO DA FONSECA, S/N</td>\n",
       "      <td>25910901341</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>440</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-59.950224</td>\n",
       "      <td>-15.003089</td>\n",
       "      <td>91090</td>\n",
       "      <td>VILA BELA DA SANTÍSSIMA TRINDADE</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>1171</td>\n",
       "      <td>CAMARA MUNICIPAL DE VILA BELA DA SANTISSIMA TR...</td>\n",
       "      <td>AV. SÃO LUIZ, S/N, VILA BELA DA SS. TRINDADE</td>\n",
       "      <td>25910901171</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>439</td>\n",
       "      <td>0.461111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 4,
     "metadata": {
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "mapa = kml.Kml()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.0'"
      ]
     },
     "execution_count": 6,
     "metadata": {
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(round(df['efetivo'].loc[0],1))"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "def InsereKML(i):\n",
    "    aux = str(df['GRUPOS'].loc[i]) + ' (' +str(round(df['efetivo'].loc[i],1)) + ')'\n",
    "    mapa.newpoint(name=aux, coords=[(df['longitude'].loc[i],df['latitude'].loc[i])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "def InsereKML(i):\n",
    "    aux = str(df['GRUPOS'].loc[i]) + ' (' +str(round(df['efetivo'].loc[i],1)) + ')'\n",
    "\n",
    "    pnt = mapa.newpoint(name=aux, coords=[(df['longitude'].loc[i],df['latitude'].loc[i])])\n",
    "\n",
    "    pnt.style.labelstyle.scale = 0.75\n",
    "\n",
    "    if str(round(df['efetivo'].loc[i],1)) == '2.0':\n",
    "       pnt.style.iconstyle.icon.href = 'http://maps.google.com/mapfiles/kml/shapes/police.png'\n",
    "       pnt.style.labelstyle.color = kml.Color.red\n",
    "    else:\n",
    "       pnt.style.iconstyle.icon.href = 'http://maps.google.com/mapfiles/kml/shapes/track.png'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "for i in range(len(df.index)):\n",
    "    InsereKML(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
   ],
   "source": [
    "mapa.save(\"Mapa 5.kml\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (Ubuntu Linux)",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}