Cleaning_Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c945b424-e2a8-478d-8244-3e485b76ac52",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "import geojson\n",
    "import numpy as np\n",
    "import folium\n",
    "from shapely.geometry import shape, Point\n",
    "from shapely import geometry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "babbc408",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ray = pd.read_excel('ray.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1bfb82eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#CAMBIAR 1000 por XXXX\n",
    "ray = ray[['FECHA','LATITUD','LONGITUD','CORRIENTE']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "809f84f8",
   "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>FECHA</th>\n",
       "      <th>LATITUD</th>\n",
       "      <th>LONGITUD</th>\n",
       "      <th>CORRIENTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>5.3726</td>\n",
       "      <td>-74.5058</td>\n",
       "      <td>-8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>4.8297</td>\n",
       "      <td>-73.5334</td>\n",
       "      <td>-31.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>4.7931</td>\n",
       "      <td>-73.4938</td>\n",
       "      <td>-50.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>4.9744</td>\n",
       "      <td>-73.9611</td>\n",
       "      <td>-6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>4.7014</td>\n",
       "      <td>-73.3774</td>\n",
       "      <td>-18.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>2018-08-02</td>\n",
       "      <td>5.6766</td>\n",
       "      <td>-74.4469</td>\n",
       "      <td>-6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>2018-08-02</td>\n",
       "      <td>5.6393</td>\n",
       "      <td>-74.3939</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>2018-08-02</td>\n",
       "      <td>5.6534</td>\n",
       "      <td>-74.5210</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>2018-08-02</td>\n",
       "      <td>5.6822</td>\n",
       "      <td>-74.5466</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>2018-08-02</td>\n",
       "      <td>5.6848</td>\n",
       "      <td>-74.5456</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>999 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         FECHA  LATITUD  LONGITUD  CORRIENTE\n",
       "0   2018-08-01   5.3726  -74.5058       -8.9\n",
       "1   2018-08-01   4.8297  -73.5334      -31.3\n",
       "2   2018-08-01   4.7931  -73.4938      -50.9\n",
       "3   2018-08-01   4.9744  -73.9611       -6.7\n",
       "4   2018-08-01   4.7014  -73.3774      -18.1\n",
       "..         ...      ...       ...        ...\n",
       "994 2018-08-02   5.6766  -74.4469       -6.9\n",
       "995 2018-08-02   5.6393  -74.3939        5.5\n",
       "996 2018-08-02   5.6534  -74.5210        5.7\n",
       "997 2018-08-02   5.6822  -74.5466        6.8\n",
       "998 2018-08-02   5.6848  -74.5456        6.0\n",
       "\n",
       "[999 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ray"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "35a0aa2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = gpd.read_file('dataframe.geojson')\n",
    "df = df[['EQUIPO', 'geometry']]\n",
    "\n",
    "ray = gpd.GeoDataFrame(ray, geometry = gpd.points_from_xy(ray.LONGITUD, ray.LATITUD))\n",
    "ray['FECHA'] = ray['FECHA'].dt.strftime('%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "866fb51f-9f0b-4568-9d33-f3e1fca476b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "ray['ubicacion'] = \"otro\"\n",
    "r = 1/(1e2) # 1/1e3 es equivalente a 111 metros\n",
    "\n",
    "l = list()\n",
    "for i in range(0, len(df)):\n",
    "    x = df.loc[i, 'geometry'].x\n",
    "    y = df.loc[i, 'geometry'].y\n",
    "    poly = shape(geometry.Polygon([[x-r, y-r],[x-r, y+r],[x+r, y+r], [x+r, y-r]]))\n",
    "    l.extend([[df.loc[i, 'EQUIPO']]])\n",
    "    for j in range(0, len(ray)):\n",
    "        if poly.contains(ray.loc[j, 'geometry']):\n",
    "            l[i].append(ray.loc[j,'CORRIENTE'])\n",
    "            ray.at[j,'ubicacion'] = df.loc[i, 'EQUIPO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "759b2c90-0f7a-4cd3-b286-949839047d5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "otro       908\n",
       "ERC5278     18\n",
       "ERC5005     11\n",
       "ERC5279      8\n",
       "ESZ5072      6\n",
       "ESZ5070      4\n",
       "ERC5188      3\n",
       "RC1881       3\n",
       "SZ6070       2\n",
       "SZ4443       2\n",
       "SZ2542       1\n",
       "SZ2412       1\n",
       "RC2177       1\n",
       "SZ3374       1\n",
       "SZ6852       1\n",
       "SZ2576       1\n",
       "SZ1719       1\n",
       "SZ4788       1\n",
       "SZ6360       1\n",
       "SZ2524       1\n",
       "ERC5134      1\n",
       "SZ4716       1\n",
       "SZ3863       1\n",
       "SZ7281       1\n",
       "RC2051       1\n",
       "SZ3292       1\n",
       "SZ6451       1\n",
       "RC2049       1\n",
       "RC2201       1\n",
       "SZ7019       1\n",
       "SZ1679       1\n",
       "RC840        1\n",
       "RC509        1\n",
       "SZ1569       1\n",
       "ESZ5076      1\n",
       "SZ6827       1\n",
       "RC1454       1\n",
       "SZ4961       1\n",
       "SZ2210       1\n",
       "SZ2509       1\n",
       "SZ4530       1\n",
       "RC1126       1\n",
       "SZ5035       1\n",
       "RC1337       1\n",
       "Name: ubicacion, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ray['ubicacion'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15d83abb-1fe7-4e8e-9580-d7eb8295daba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<folium.features.GeoJson at 0xc310d70>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map = folium.Map(location=[4.5, -74], tiles=\"Cartodb Positron\", zoom_start=7)\n",
    "\n",
    "ray = ray.set_crs(\"EPSG:4326\")\n",
    "ray = ray.set_crs(epsg=4326)\n",
    "\n",
    "folium.GeoJson(ray\n",
    "              ).add_to(map)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "30f4557a-cf4d-42a1-a962-3e620bdeca78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['ERC2150', 4]]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = list()\n",
    "l.extend([[df.loc[i, 'EQUIPO']]])\n",
    "l[0].append(4)\n",
    "l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1f4e3a2b-cccc-4f59-be61-ec0dab4ad836",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['ERC2150', 4]]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[x for x in l if len(x) > 1]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}