ceptolatitudelongitude.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data manipulation of the code, after running the converter from CEP to latitude and logitude, was done by me. For the converter from CEP to latitude and longitude, I made some adaptations from the code on this [github page]( https://github.com/mstuttgart/pycep-correios/blob/develop/pycep_correios/client.py) CEP stands for Código de Endereçamento Postal, it's the Brazilian postal code. I needed to make this conversion as part of the doctorate thesis project. This converter found approximately 80% of the latitude and longitude, for every 5 thousand CEPs, it took approximately 3 to 4 hours of processing time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CEP to latitude and longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "#base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import string\n",
    "from abc import ABC, abstractmethod\n",
    "\n",
    "class CEPConverter(ABC):\n",
    "    @abstractmethod\n",
    "    def __call__(self, cep):\n",
    "        pass\n",
    "class Coordinates(ABC):\n",
    "    def __init__(self, cep):\n",
    "        self.cep = cep\n",
    "        self.clean_cep = self._clean_CEP()\n",
    "    def _clean_CEP(self):\n",
    "        # Regex to avoid CEPs with dash ('-')\n",
    "        regex = re.compile(\"[%s]\" % re.escape(string.punctuation))\n",
    "        return regex.sub(\"\", self.cep)\n",
    "    @abstractmethod\n",
    "    def __call__(self):\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "#viacep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "\n",
    "\n",
    "URL_GET_ADDRESS_FROM_CEP = \"http://www.viacep.com.br/ws/{}/json\"\n",
    "\n",
    "\n",
    "class BaseException(Exception):\n",
    "    \"\"\" base exception\"\"\"\n",
    "\n",
    "    def __init__(self, message=\"\"):\n",
    "        super(BaseException, self).__init__(message)\n",
    "        self.message = message\n",
    "\n",
    "    def __repr__(self):\n",
    "        return \"%s(%r)\" % (self.__class__.__name__, self.message)\n",
    "\n",
    "\n",
    "def get_address_from_cep(cep):\n",
    "    \"\"\" Source: https://github.com/mstuttgart/pycep-correios/blob/develop/pycep_correios/client.py \"\"\"\n",
    "    try:\n",
    "        response = requests.get(URL_GET_ADDRESS_FROM_CEP.format(cep))\n",
    "\n",
    "        if response.status_code == 200:\n",
    "            address = json.loads(response.text)\n",
    "\n",
    "            if address.get(\"erro\"):\n",
    "                raise BaseException(message=\"Other error\")\n",
    "\n",
    "            return {\n",
    "                \"bairro\": address.get(\"bairro\", \"\"),\n",
    "                \"cep\": address.get(\"cep\", \"\"),\n",
    "                \"cidade\": address.get(\"localidade\", \"\"),\n",
    "                \"logradouro\": address.get(\"logradouro\", \"\"),\n",
    "                \"uf\": address.get(\"uf\", \"\"),\n",
    "                \"complemento\": address.get(\"complemento\", \"\"),\n",
    "            }\n",
    "\n",
    "        elif response.status_code == 400:\n",
    "            raise BaseException(message=\"Invalid CEP: %s\" % cep)  # noqa\n",
    "        else:\n",
    "            raise BaseException(message=\"Other error\")\n",
    "\n",
    "    except requests.exceptions.RequestException as e:\n",
    "        raise BaseException(message=e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "#strategies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import requests\n",
    "\n",
    "#from .viacep import get_address_from_cep\n",
    "#from .base import CEPConverter, Coordinates\n",
    "\n",
    "\n",
    "class CorreiosPhotonConverter(CEPConverter):\n",
    "    def __call__(self, cep):\n",
    "        return CorreiosPhotonCoordinates(cep)()\n",
    "\n",
    "\n",
    "class CEPAbertoConverter(CEPConverter):\n",
    "    def __call__(self, cep):\n",
    "        return CEPAbertoCoordinates(cep)()\n",
    "\n",
    "\n",
    "class CorreiosPhotonCoordinates(Coordinates):\n",
    "    def fetch_address(self):\n",
    "        try:\n",
    "            search_result = get_address_from_cep(self.clean_cep)\n",
    "            address = \" \".join(\n",
    "                [\n",
    "                    search_result[\"logradouro\"],\n",
    "                    search_result[\"bairro\"],\n",
    "                    search_result[\"cidade\"],\n",
    "                    search_result[\"uf\"],\n",
    "                    \"Brasil\",\n",
    "                ]\n",
    "            )\n",
    "            # Treating the case when Correios API return an empty json\n",
    "            if address == \" Brasil\":\n",
    "                address = \"-\"\n",
    "        except:\n",
    "            address = \"-\"\n",
    "\n",
    "        return address\n",
    "\n",
    "    def fetch_coordinates(self, address):\n",
    "        try:\n",
    "            if address == \"-\":\n",
    "                print(\"NaN\")\n",
    "                return {\"latitude\": float(\"nan\"), \"longitude\": float(\"nan\")}\n",
    "\n",
    "            r = requests.get(\n",
    "                \"\".join([\"http://photon.komoot.io/api?q=\", address, \"&limit=1\"])\n",
    "            )\n",
    "\n",
    "            result = r.json()\n",
    "            # list with lon, lat\n",
    "            coordinates = result[\"features\"][0][\"geometry\"][\"coordinates\"]\n",
    "            return {\"latitude\": coordinates[1], \"longitude\": coordinates[0]}\n",
    "        except IndexError:\n",
    "            return {\"latitude\": float(\"nan\"), \"longitude\": float(\"nan\")}\n",
    "        except requests.exceptions.RequestException as e:\n",
    "            raise SystemExit(e)\n",
    "\n",
    "    def __call__(self):\n",
    "        address = self.fetch_address()\n",
    "        coordinates = self.fetch_coordinates(address)\n",
    "        return coordinates\n",
    "\n",
    "\n",
    "class CEPAbertoCoordinates(Coordinates):\n",
    "    def fetch_coordinates(self):\n",
    "        try:\n",
    "            url = f\"https://www.cepaberto.com/api/v3/cep?cep={self.clean_cep}\"\n",
    "            # Sign up for your free token on: https://cepaberto.com/\n",
    "            # export CEP_ABERTO_TOKEN='your-token'\n",
    "            headers = {\"Authorization\": f'Token token={os.getenv(\"CEP_ABERTO_TOKEN\")}'}\n",
    "            response = requests.get(url, headers=headers)\n",
    "            json_response = response.json()\n",
    "            return {\n",
    "                \"latitude\": float(json_response[\"latitude\"]),\n",
    "                \"longitude\": float(json_response[\"longitude\"]),\n",
    "            }\n",
    "        \n",
    "        except requests.exceptions.RequestException as e:\n",
    "            raise SystemExit(e)\n",
    "        \n",
    "\n",
    "    def __call__(self):\n",
    "        coordinates = self.fetch_coordinates()\n",
    "        return coordinates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "#from .base import CEPConverter\n",
    "#from .strategies import CorreiosPhotonConverter\n",
    "\n",
    "\n",
    "def cep_to_coords(cep: str, factory: CEPConverter = CorreiosPhotonConverter) -> dict:\n",
    "    coordinates = factory()(cep)\n",
    "    return coordinates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "#testing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "id1ate1000 = pd.read_excel(\"C:/Users/andre/Documents/Thesis/Thesis Project/Novas planilhas até 22-12-2020/cepnupitsus/id1ate1000.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "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>id</th>\n",
       "      <th>cep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>56903140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>56912285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>56400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>56903000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>56912440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>56912190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>56840000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>56220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>22010201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>55008020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>55660000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>55120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>55120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>56304920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>55160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>53670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>55002970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>55101000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>55490000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>55002970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>55490000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>55695000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>56700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>55030070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>55030450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>55435000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>56520000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>55020450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>39</td>\n",
       "      <td>55014580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>55028070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>55018640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>55018560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>57000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>79822340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>55024590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>46</td>\n",
       "      <td>56300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>47</td>\n",
       "      <td>55200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>48</td>\n",
       "      <td>55008000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>49</td>\n",
       "      <td>55014635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>56180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>51</td>\n",
       "      <td>50740050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>52</td>\n",
       "      <td>55190000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>53</td>\n",
       "      <td>63155000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>55670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>55</td>\n",
       "      <td>55670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>56</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>57</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>58</td>\n",
       "      <td>55819901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>59</td>\n",
       "      <td>52131180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>60</td>\n",
       "      <td>52061050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>61</td>\n",
       "      <td>55865000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>62</td>\n",
       "      <td>50620300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>63</td>\n",
       "      <td>55940000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>64</td>\n",
       "      <td>53620280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>65</td>\n",
       "      <td>52280050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>66</td>\n",
       "      <td>54300022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>67</td>\n",
       "      <td>55730000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>68</td>\n",
       "      <td>56332720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>69</td>\n",
       "      <td>51240200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>70</td>\n",
       "      <td>56250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>71</td>\n",
       "      <td>56512380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>72</td>\n",
       "      <td>55745000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>73</td>\n",
       "      <td>56850000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>74</td>\n",
       "      <td>55720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>75</td>\n",
       "      <td>55578000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>76</td>\n",
       "      <td>54340080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>77</td>\n",
       "      <td>55014265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>78</td>\n",
       "      <td>55860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>79</td>\n",
       "      <td>63180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>80</td>\n",
       "      <td>56512050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>81</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>82</td>\n",
       "      <td>55270000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>83</td>\n",
       "      <td>53600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>84</td>\n",
       "      <td>55665000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>85</td>\n",
       "      <td>55495000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>86</td>\n",
       "      <td>56163000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>87</td>\n",
       "      <td>50731450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>88</td>\n",
       "      <td>55578000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>89</td>\n",
       "      <td>56304620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>90</td>\n",
       "      <td>55720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>91</td>\n",
       "      <td>55630000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>92</td>\n",
       "      <td>48905361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>93</td>\n",
       "      <td>54410280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>94</td>\n",
       "      <td>8062600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>95</td>\n",
       "      <td>55345000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>56400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>50860260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>56395000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>55028400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       cep\n",
       "0     1  56900000\n",
       "1     2  55000000\n",
       "2     3  56903140\n",
       "3     4  56912285\n",
       "4     5  56900000\n",
       "5     6  56400000\n",
       "6     7  56900000\n",
       "7     8  56903000\n",
       "8     9  56900000\n",
       "9    10  56912440\n",
       "10   11  56912190\n",
       "11   12  56840000\n",
       "12   13  56220000\n",
       "13   14  22010201\n",
       "14   15  55008020\n",
       "15   16  55660000\n",
       "16   17  55120000\n",
       "17   18  55120000\n",
       "18   19  56304920\n",
       "19   20  55160000\n",
       "20   21  53670000\n",
       "21   22  55002970\n",
       "22   23  55101000\n",
       "23   24  55000000\n",
       "24   25  55490000\n",
       "25   26  55002970\n",
       "26   27  55000000\n",
       "27   28  55490000\n",
       "28   29  55695000\n",
       "29   30  56700000\n",
       "30   31  56280000\n",
       "31   32  55030070\n",
       "32   33  55000000\n",
       "33   34  55000000\n",
       "34   35  55030450\n",
       "35   36  55435000\n",
       "36   37  56520000\n",
       "37   38  55020450\n",
       "38   39  55014580\n",
       "39   40  55028070\n",
       "40   41  55018640\n",
       "41   42  55018560\n",
       "42   43  57000000\n",
       "43   44  79822340\n",
       "44   45  55024590\n",
       "45   46  56300000\n",
       "46   47  55200000\n",
       "47   48  55008000\n",
       "48   49  55014635\n",
       "49   50  56180000\n",
       "50   51  50740050\n",
       "51   52  55190000\n",
       "52   53  63155000\n",
       "53   54  55670000\n",
       "54   55  55670000\n",
       "55   56  56280000\n",
       "56   57  56280000\n",
       "57   58  55819901\n",
       "58   59  52131180\n",
       "59   60  52061050\n",
       "60   61  55865000\n",
       "61   62  50620300\n",
       "62   63  55940000\n",
       "63   64  53620280\n",
       "64   65  52280050\n",
       "65   66  54300022\n",
       "66   67  55730000\n",
       "67   68  56332720\n",
       "68   69  51240200\n",
       "69   70  56250000\n",
       "70   71  56512380\n",
       "71   72  55745000\n",
       "72   73  56850000\n",
       "73   74  55720000\n",
       "74   75  55578000\n",
       "75   76  54340080\n",
       "76   77  55014265\n",
       "77   78  55860000\n",
       "78   79  63180000\n",
       "79   80  56512050\n",
       "80   81  56280000\n",
       "81   82  55270000\n",
       "82   83  53600000\n",
       "83   84  55665000\n",
       "84   85  55495000\n",
       "85   86  56163000\n",
       "86   87  50731450\n",
       "87   88  55578000\n",
       "88   89  56304620\n",
       "89   90  55720000\n",
       "90   91  55630000\n",
       "91   92  48905361\n",
       "92   93  54410280\n",
       "93   94   8062600\n",
       "94   95  55345000\n",
       "95   96  56400000\n",
       "96   97  50860260\n",
       "97   98  56280000\n",
       "98   99  56395000\n",
       "99  100  55028400"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id1ate1000[0:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "id1ate100 =id1ate1000.loc[0:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "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>id</th>\n",
       "      <th>cep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>56903140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>56912285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>56900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>50860260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>56280000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>56395000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>55028400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>101</td>\n",
       "      <td>55178000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id       cep\n",
       "0      1  56900000\n",
       "1      2  55000000\n",
       "2      3  56903140\n",
       "3      4  56912285\n",
       "4      5  56900000\n",
       "..   ...       ...\n",
       "96    97  50860260\n",
       "97    98  56280000\n",
       "98    99  56395000\n",
       "99   100  55028400\n",
       "100  101  55178000\n",
       "\n",
       "[101 rows x 2 columns]"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      id                         cep\n",
      "0      1  cep_to_coords('56900000'),\n",
      "1      2  cep_to_coords('55000000'),\n",
      "2      3  cep_to_coords('56903140'),\n",
      "3      4  cep_to_coords('56912285'),\n",
      "4      5  cep_to_coords('56900000'),\n",
      "..   ...                         ...\n",
      "96    97  cep_to_coords('50860260'),\n",
      "97    98  cep_to_coords('56280000'),\n",
      "98    99  cep_to_coords('56395000'),\n",
      "99   100  cep_to_coords('55028400'),\n",
      "100  101  cep_to_coords('55178000'),\n",
      "\n",
      "[101 rows x 2 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\andre\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:6596: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self[col] = expressions.where(mask, this, that)\n"
     ]
    }
   ],
   "source": [
    "id1ate100.update(\"cep_to_coords('\" + id1ate100[['cep']].astype(str) + \"'),\")\n",
    "print(id1ate100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "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>id</th>\n",
       "      <th>cep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>cep_to_coords('56900000'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>cep_to_coords('55000000'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>cep_to_coords('56903140'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>cep_to_coords('56912285'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>cep_to_coords('56900000'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>cep_to_coords('50860260'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>cep_to_coords('56280000'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>cep_to_coords('56395000'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>cep_to_coords('55028400'),</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>101</td>\n",
       "      <td>cep_to_coords('55178000'),</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                         cep\n",
       "0      1  cep_to_coords('56900000'),\n",
       "1      2  cep_to_coords('55000000'),\n",
       "2      3  cep_to_coords('56903140'),\n",
       "3      4  cep_to_coords('56912285'),\n",
       "4      5  cep_to_coords('56900000'),\n",
       "..   ...                         ...\n",
       "96    97  cep_to_coords('50860260'),\n",
       "97    98  cep_to_coords('56280000'),\n",
       "98    99  cep_to_coords('56395000'),\n",
       "99   100  cep_to_coords('55028400'),\n",
       "100  101  cep_to_coords('55178000'),\n",
       "\n",
       "[101 rows x 2 columns]"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [],
   "source": [
    "id1ate100=id1ate100.loc[:,'cep']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      cep_to_coords('56900000'),\n",
       "1      cep_to_coords('55000000'),\n",
       "2      cep_to_coords('56903140'),\n",
       "3      cep_to_coords('56912285'),\n",
       "4      cep_to_coords('56900000'),\n",
       "                  ...            \n",
       "96     cep_to_coords('50860260'),\n",
       "97     cep_to_coords('56280000'),\n",
       "98     cep_to_coords('56395000'),\n",
       "99     cep_to_coords('55028400'),\n",
       "100    cep_to_coords('55178000'),\n",
       "Name: cep, Length: 101, dtype: object"
      ]
     },
     "execution_count": 243,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cep_to_coords('56900000'), cep_to_coords('55000000'), cep_to_coords('56903140'), cep_to_coords('56912285'), cep_to_coords('56900000'), cep_to_coords('56400000'), cep_to_coords('56900000'), cep_to_coords('56903000'), cep_to_coords('56900000'), cep_to_coords('56912440'), cep_to_coords('56912190'), cep_to_coords('56840000'), cep_to_coords('56220000'), cep_to_coords('22010201'), cep_to_coords('55008020'), cep_to_coords('55660000'), cep_to_coords('55120000'), cep_to_coords('55120000'), cep_to_coords('56304920'), cep_to_coords('55160000'), cep_to_coords('53670000'), cep_to_coords('55002970'), cep_to_coords('55101000'), cep_to_coords('55000000'), cep_to_coords('55490000'), cep_to_coords('55002970'), cep_to_coords('55000000'), cep_to_coords('55490000'), cep_to_coords('55695000'), cep_to_coords('56700000'), cep_to_coords('56280000'), cep_to_coords('55030070'), cep_to_coords('55000000'), cep_to_coords('55000000'), cep_to_coords('55030450'), cep_to_coords('55435000'), cep_to_coords('56520000'), cep_to_coords('55020450'), cep_to_coords('55014580'), cep_to_coords('55028070'), cep_to_coords('55018640'), cep_to_coords('55018560'), cep_to_coords('57000000'), cep_to_coords('79822340'), cep_to_coords('55024590'), cep_to_coords('56300000'), cep_to_coords('55200000'), cep_to_coords('55008000'), cep_to_coords('55014635'), cep_to_coords('56180000'), cep_to_coords('50740050'), cep_to_coords('55190000'), cep_to_coords('63155000'), cep_to_coords('55670000'), cep_to_coords('55670000'), cep_to_coords('56280000'), cep_to_coords('56280000'), cep_to_coords('55819901'), cep_to_coords('52131180'), cep_to_coords('52061050'), cep_to_coords('55865000'), cep_to_coords('50620300'), cep_to_coords('55940000'), cep_to_coords('53620280'), cep_to_coords('52280050'), cep_to_coords('54300022'), cep_to_coords('55730000'), cep_to_coords('56332720'), cep_to_coords('51240200'), cep_to_coords('56250000'), cep_to_coords('56512380'), cep_to_coords('55745000'), cep_to_coords('56850000'), cep_to_coords('55720000'), cep_to_coords('55578000'), cep_to_coords('54340080'), cep_to_coords('55014265'), cep_to_coords('55860000'), cep_to_coords('63180000'), cep_to_coords('56512050'), cep_to_coords('56280000'), cep_to_coords('55270000'), cep_to_coords('53600000'), cep_to_coords('55665000'), cep_to_coords('55495000'), cep_to_coords('56163000'), cep_to_coords('50731450'), cep_to_coords('55578000'), cep_to_coords('56304620'), cep_to_coords('55720000'), cep_to_coords('55630000'), cep_to_coords('48905361'), cep_to_coords('54410280'), cep_to_coords('8062600'), cep_to_coords('55345000'), cep_to_coords('56400000'), cep_to_coords('50860260'), cep_to_coords('56280000'), cep_to_coords('56395000'), cep_to_coords('55028400'), cep_to_coords('55178000'),\n"
     ]
    }
   ],
   "source": [
    "print(\" \".join(id1ate100))#.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.set_option('display.max_rows', None)    #to display max number of rows \n",
    "#pd.set_option('display.max_columns', None) #to display max number of columns \n",
    "#pd.set_option('display.max_rows', 100) #returns number of rows to default\n",
    "#pd.set_option('display.max_columns', 100) #returns number of columns to default"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n",
      "NaN\n"
     ]
    }
   ],
   "source": [
    "cordenadasid1ate100 = cep_to_coords('56900000'), cep_to_coords('55000000'), cep_to_coords('56903140'), cep_to_coords('56912285'), cep_to_coords('56900000'), cep_to_coords('56400000'), cep_to_coords('56900000'), cep_to_coords('56903000'), cep_to_coords('56900000'), cep_to_coords('56912440'), cep_to_coords('56912190'), cep_to_coords('56840000'), cep_to_coords('56220000'), cep_to_coords('22010201'), cep_to_coords('55008020'), cep_to_coords('55660000'), cep_to_coords('55120000'), cep_to_coords('55120000'), cep_to_coords('56304920'), cep_to_coords('55160000'), cep_to_coords('53670000'), cep_to_coords('55002970'), cep_to_coords('55101000'), cep_to_coords('55000000'), cep_to_coords('55490000'), cep_to_coords('55002970'), cep_to_coords('55000000'), cep_to_coords('55490000'), cep_to_coords('55695000'), cep_to_coords('56700000'), cep_to_coords('56280000'), cep_to_coords('55030070'), cep_to_coords('55000000'), cep_to_coords('55000000'), cep_to_coords('55030450'), cep_to_coords('55435000'), cep_to_coords('56520000'), cep_to_coords('55020450'), cep_to_coords('55014580'), cep_to_coords('55028070'), cep_to_coords('55018640'), cep_to_coords('55018560'), cep_to_coords('57000000'), cep_to_coords('79822340'), cep_to_coords('55024590'), cep_to_coords('56300000'), cep_to_coords('55200000'), cep_to_coords('55008000'), cep_to_coords('55014635'), cep_to_coords('56180000'), cep_to_coords('50740050'), cep_to_coords('55190000'), cep_to_coords('63155000'), cep_to_coords('55670000'), cep_to_coords('55670000'), cep_to_coords('56280000'), cep_to_coords('56280000'), cep_to_coords('55819901'), cep_to_coords('52131180'), cep_to_coords('52061050'), cep_to_coords('55865000'), cep_to_coords('50620300'), cep_to_coords('55940000'), cep_to_coords('53620280'), cep_to_coords('52280050'), cep_to_coords('54300022'), cep_to_coords('55730000'), cep_to_coords('56332720'), cep_to_coords('51240200'), cep_to_coords('56250000'), cep_to_coords('56512380'), cep_to_coords('55745000'), cep_to_coords('56850000'), cep_to_coords('55720000'), cep_to_coords('55578000'), cep_to_coords('54340080'), cep_to_coords('55014265'), cep_to_coords('55860000'), cep_to_coords('63180000'), cep_to_coords('56512050'), cep_to_coords('56280000'), cep_to_coords('55270000'), cep_to_coords('53600000'), cep_to_coords('55665000'), cep_to_coords('55495000'), cep_to_coords('56163000'), cep_to_coords('50731450'), cep_to_coords('55578000'), cep_to_coords('56304620'), cep_to_coords('55720000'), cep_to_coords('55630000'), cep_to_coords('48905361'), cep_to_coords('54410280'), cep_to_coords('8062600'), cep_to_coords('55345000'), cep_to_coords('56400000'), cep_to_coords('50860260'), cep_to_coords('56280000'), cep_to_coords('56395000'), cep_to_coords('55028400'), cep_to_coords('55178000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "({'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.981733, 'longitude': -38.2935601}, {'latitude': -7.9941181, 'longitude': -38.2850475}, {'latitude': nan, 'longitude': nan}, {'latitude': -22.3086683, 'longitude': -42.6201671}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.981733, 'longitude': -38.2935601}, {'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.9941321, 'longitude': -38.2876137}, {'latitude': -8.0586442, 'longitude': -34.934496}, {'latitude': -7.777592, 'longitude': -39.933797}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.2872591, 'longitude': -35.9765889}, {'latitude': -8.2372132, 'longitude': -35.7558262}, {'latitude': -8.1356327, 'longitude': -35.8566421}, {'latitude': -8.1356327, 'longitude': -35.8566421}, {'latitude': -9.3938235, 'longitude': -40.4928845}, {'latitude': -8.22489615, 'longitude': -36.286024852340965}, {'latitude': -7.75353355, 'longitude': -34.9931035125}, {'latitude': -8.2858789, 'longitude': -35.9678183}, {'latitude': -8.2829702, 'longitude': -35.9722852}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.4904095, 'longitude': -36.0607494}, {'latitude': -8.2858789, 'longitude': -35.9678183}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.4904095, 'longitude': -36.0607494}, {'latitude': -8.2985061, 'longitude': -35.69229117454354}, {'latitude': -7.47543895, 'longitude': -37.26233782586722}, {'latitude': -7.5768582, 'longitude': -40.5038509}, {'latitude': -8.2963052, 'longitude': -35.9707443}, {'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.2977862, 'longitude': -35.9776993}, {'latitude': -22.4635638, 'longitude': -43.0203366}, {'latitude': -8.6239732, 'longitude': -37.157296}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.2581939, 'longitude': -35.9816831}, {'latitude': -8.2950398, 'longitude': -35.9640663}, {'latitude': -8.2744298, 'longitude': -35.9605144}, {'latitude': -8.2739189, 'longitude': -35.9569766}, {'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.3574471, 'longitude': -36.6968238}, {'latitude': -8.288389, 'longitude': -35.9796857}, {'latitude': -8.2696397, 'longitude': -35.978872}, {'latitude': -8.3847921, 'longitude': -39.1566635}, {'latitude': -8.0373588, 'longitude': -34.9456277}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.285445, 'longitude': -40.457306}, {'latitude': -8.4323653, 'longitude': -35.8076179}, {'latitude': -8.4323653, 'longitude': -35.8076179}, {'latitude': -7.5768582, 'longitude': -40.5038509}, {'latitude': -7.5768582, 'longitude': -40.5038509}, {'latitude': -7.8454716, 'longitude': -35.2563355}, {'latitude': -8.0088371, 'longitude': -34.9024406}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.555638, 'longitude': -35.44246}, {'latitude': -8.0447568, 'longitude': -34.92279285206094}, {'latitude': -7.587868, 'longitude': -35.09994}, {'latitude': -7.8631354, 'longitude': -34.9034894}, {'latitude': -8.0210286, 'longitude': -34.9130052}, {'latitude': -8.1266407, 'longitude': -34.9619789}, {'latitude': -7.7961572, 'longitude': -35.5871696}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.1267111, 'longitude': -34.9334496}, {'latitude': -7.759, 'longitude': -40.26472}, {'latitude': -8.4126288, 'longitude': -37.071716}, {'latitude': -7.74562, 'longitude': -35.6019195}, {'latitude': -22.8909821, 'longitude': -46.3245928}, {'latitude': -7.8608755, 'longitude': -35.591575}, {'latitude': -8.7500009, 'longitude': -35.1045787}, {'latitude': -8.194987, 'longitude': -34.954112}, {'latitude': -8.2687337, 'longitude': -35.972174}, {'latitude': -7.589691, 'longitude': -35.480844}, {'latitude': -7.3121067, 'longitude': -39.3036427}, {'latitude': nan, 'longitude': nan}, {'latitude': -7.5768582, 'longitude': -40.5038509}, {'latitude': -8.5765199, 'longitude': -36.876569}, {'latitude': nan, 'longitude': nan}, {'latitude': -8.3616757, 'longitude': -35.7638903}, {'latitude': -8.4558021, 'longitude': -35.9442663}, {'latitude': -8.0913471, 'longitude': -39.5778505}, {'latitude': -8.0400227, 'longitude': -34.9303212}, {'latitude': -8.7500009, 'longitude': -35.1045787}, {'latitude': -9.3877852, 'longitude': -40.5010262}, {'latitude': -7.8608755, 'longitude': -35.591575}, {'latitude': -8.1372746, 'longitude': -35.4026893606909}, {'latitude': -9.4325523, 'longitude': -40.4906525}, {'latitude': -8.1765501, 'longitude': -34.9176112}, {'latitude': nan, 'longitude': nan}, {'latitude': -9.1351947, 'longitude': -36.87780369473684}, {'latitude': -22.3086683, 'longitude': -42.6201671}, {'latitude': -8.0958185, 'longitude': -34.9251651}, {'latitude': -7.5768582, 'longitude': -40.5038509}, {'latitude': -8.873429649999999, 'longitude': -40.19159683712913}, {'latitude': -8.3035818, 'longitude': -35.9665182}, {'latitude': -8.01658205, 'longitude': -36.20603954721368})\n"
     ]
    }
   ],
   "source": [
    "print(cordenadasid1ate100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      cep_to_coords('56900000'),\n",
       "1      cep_to_coords('55000000'),\n",
       "2      cep_to_coords('56903140'),\n",
       "3      cep_to_coords('56912285'),\n",
       "4      cep_to_coords('56900000'),\n",
       "                  ...            \n",
       "96     cep_to_coords('50860260'),\n",
       "97     cep_to_coords('56280000'),\n",
       "98     cep_to_coords('56395000'),\n",
       "99     cep_to_coords('55028400'),\n",
       "100    cep_to_coords('55178000'),\n",
       "Name: cep, Length: 101, dtype: object"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "#id1ate100\n",
    "#id1ate100.tolist()\n",
    "#print((id1ate100).tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "pattern = re.compile(r\"(\\d+)\")\n",
    "result = []\n",
    "for item in id1ate100.tolist():\n",
    "    result.append(''.join(pattern.findall(item)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['56900000', '55000000', '56903140', '56912285', '56900000', '56400000', '56900000', '56903000', '56900000', '56912440', '56912190', '56840000', '56220000', '22010201', '55008020', '55660000', '55120000', '55120000', '56304920', '55160000', '53670000', '55002970', '55101000', '55000000', '55490000', '55002970', '55000000', '55490000', '55695000', '56700000', '56280000', '55030070', '55000000', '55000000', '55030450', '55435000', '56520000', '55020450', '55014580', '55028070', '55018640', '55018560', '57000000', '79822340', '55024590', '56300000', '55200000', '55008000', '55014635', '56180000', '50740050', '55190000', '63155000', '55670000', '55670000', '56280000', '56280000', '55819901', '52131180', '52061050', '55865000', '50620300', '55940000', '53620280', '52280050', '54300022', '55730000', '56332720', '51240200', '56250000', '56512380', '55745000', '56850000', '55720000', '55578000', '54340080', '55014265', '55860000', '63180000', '56512050', '56280000', '55270000', '53600000', '55665000', '55495000', '56163000', '50731450', '55578000', '56304620', '55720000', '55630000', '48905361', '54410280', '8062600', '55345000', '56400000', '50860260', '56280000', '56395000', '55028400', '55178000']\n"
     ]
    }
   ],
   "source": [
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfid1ate100 = pd.DataFrame(cordenadasid1ate100, result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>56900000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55000000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56903140</th>\n",
       "      <td>-7.981733</td>\n",
       "      <td>-38.293560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56912285</th>\n",
       "      <td>-7.994118</td>\n",
       "      <td>-38.285047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56900000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50860260</th>\n",
       "      <td>-8.095819</td>\n",
       "      <td>-34.925165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56280000</th>\n",
       "      <td>-7.576858</td>\n",
       "      <td>-40.503851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56395000</th>\n",
       "      <td>-8.873430</td>\n",
       "      <td>-40.191597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55028400</th>\n",
       "      <td>-8.303582</td>\n",
       "      <td>-35.966518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55178000</th>\n",
       "      <td>-8.016582</td>\n",
       "      <td>-36.206040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          latitude  longitude\n",
       "56900000       NaN        NaN\n",
       "55000000       NaN        NaN\n",
       "56903140 -7.981733 -38.293560\n",
       "56912285 -7.994118 -38.285047\n",
       "56900000       NaN        NaN\n",
       "...            ...        ...\n",
       "50860260 -8.095819 -34.925165\n",
       "56280000 -7.576858 -40.503851\n",
       "56395000 -8.873430 -40.191597\n",
       "55028400 -8.303582 -35.966518\n",
       "55178000 -8.016582 -36.206040\n",
       "\n",
       "[101 rows x 2 columns]"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfid1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfid1ate100.reset_index(level=0, inplace=True) #make the index a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "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>index</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>56900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56903140</td>\n",
       "      <td>-7.981733</td>\n",
       "      <td>-38.293560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>56912285</td>\n",
       "      <td>-7.994118</td>\n",
       "      <td>-38.285047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>50860260</td>\n",
       "      <td>-8.095819</td>\n",
       "      <td>-34.925165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>56280000</td>\n",
       "      <td>-7.576858</td>\n",
       "      <td>-40.503851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>56395000</td>\n",
       "      <td>-8.873430</td>\n",
       "      <td>-40.191597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>55028400</td>\n",
       "      <td>-8.303582</td>\n",
       "      <td>-35.966518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>55178000</td>\n",
       "      <td>-8.016582</td>\n",
       "      <td>-36.206040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        index  latitude  longitude\n",
       "0    56900000       NaN        NaN\n",
       "1    55000000       NaN        NaN\n",
       "2    56903140 -7.981733 -38.293560\n",
       "3    56912285 -7.994118 -38.285047\n",
       "4    56900000       NaN        NaN\n",
       "..        ...       ...        ...\n",
       "96   50860260 -8.095819 -34.925165\n",
       "97   56280000 -7.576858 -40.503851\n",
       "98   56395000 -8.873430 -40.191597\n",
       "99   55028400 -8.303582 -35.966518\n",
       "100  55178000 -8.016582 -36.206040\n",
       "\n",
       "[101 rows x 3 columns]"
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfid1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfid1ate100 = dfid1ate100.rename(columns={'index':'cep'}) #change a column's name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "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>cep</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>56900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56903140</td>\n",
       "      <td>-7.981733</td>\n",
       "      <td>-38.293560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>56912285</td>\n",
       "      <td>-7.994118</td>\n",
       "      <td>-38.285047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>50860260</td>\n",
       "      <td>-8.095819</td>\n",
       "      <td>-34.925165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>56280000</td>\n",
       "      <td>-7.576858</td>\n",
       "      <td>-40.503851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>56395000</td>\n",
       "      <td>-8.873430</td>\n",
       "      <td>-40.191597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>55028400</td>\n",
       "      <td>-8.303582</td>\n",
       "      <td>-35.966518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>55178000</td>\n",
       "      <td>-8.016582</td>\n",
       "      <td>-36.206040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          cep  latitude  longitude\n",
       "0    56900000       NaN        NaN\n",
       "1    55000000       NaN        NaN\n",
       "2    56903140 -7.981733 -38.293560\n",
       "3    56912285 -7.994118 -38.285047\n",
       "4    56900000       NaN        NaN\n",
       "..        ...       ...        ...\n",
       "96   50860260 -8.095819 -34.925165\n",
       "97   56280000 -7.576858 -40.503851\n",
       "98   56395000 -8.873430 -40.191597\n",
       "99   55028400 -8.303582 -35.966518\n",
       "100  55178000 -8.016582 -36.206040\n",
       "\n",
       "[101 rows x 3 columns]"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfid1ate100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfid1ate100.to_excel('id1ate100latlong.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}