bicing_adquisicion.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "M9ilfCr3btnG"
   },
   "source": [
    "# Movilidad Sustentable\n",
    "## Ciencia de Datos e Inteligencia de Negocios\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KZjoFuv8dVMD"
   },
   "source": [
    "### Configuración"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "vWwBPI9fUBG7"
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Bicicletas: Recorridos\n",
    "Clima: PRCP TAVG TEMP[C] HUM[%] PNM[hPa] DD[gr] FF[km/hr]                                                 \n",
    "2020-2021\n",
    "Data acquisition & wrangling\n",
    "\"\"\"\n",
    "\n",
    "import os\n",
    "import sys\n",
    "import urllib.request\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "import datetime\n",
    "from datetime import date, timedelta\n",
    "import re\n",
    "\n",
    "datadir = 'datasets/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ldceDsU_RpC6",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "20e655c7-fe0c-44fe-f308-698c0a424dbb",
    "tags": []
   },
   "outputs": [],
   "source": [
    "if 'google.colab' in str(get_ipython()):\n",
    "    print('Running on CoLab')\n",
    "    from google.colab import drive\n",
    "    drive.mount(\n",
    "      '/content/drive/', \n",
    "    )\n",
    "    if drive:\n",
    "        datadir = '/content/drive/MyDrive/Academicos/UTN/BI/datasets/'\n",
    "else:\n",
    "    print('Not running on CoLab')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0av6wXSuajZN"
   },
   "source": [
    "# Adquisición"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "pk_E7pq5RkJH",
    "tags": []
   },
   "outputs": [],
   "source": [
    "insumos = {\n",
    "  \"recorridos-2020\" : {\n",
    "    \"remote\" : 'https://cdn.buenosaires.gob.ar/datosabiertos/datasets/transporte/bicicletas-publicas/recorridos-realizados-2020.zip',\n",
    "    \"local\" : datadir + 'recorridos-realizados-2020.zip',\n",
    "    \"df\" : pd.DataFrame(),\n",
    "  },\n",
    "  \"recorridos-2021\" : {\n",
    "    \"remote\" : 'https://cdn.buenosaires.gob.ar/datosabiertos/datasets/transporte/bicicletas-publicas/recorridos-realizados-2021.zip',   \n",
    "    \"local\" : datadir + 'recorridos-realizados-2021.zip',\n",
    "    \"df\" : pd.DataFrame(),\n",
    "  },\n",
    "  \"clima\" : {\n",
    "    \"remote\" : '',   \n",
    "    \"local\" : datadir + 'weather-data-ba.csv',\n",
    "    \"df\" : pd.DataFrame(),\n",
    "  },\n",
    "#   \"estaciones\" : {\n",
    "#     \"remote\" : 'https://cdn.buenosaires.gob.ar/datosabiertos/datasets/transporte/estaciones-bicicletas-publicas/nuevas-estaciones-bicicletas-publicas.xlsx',   \n",
    "#     \"local\" : datadir + 'nuevas_estaciones.xlsx',\n",
    "#     \"df\" : pd.DataFrame(),\n",
    "#   },\n",
    "  \"feriados\" : {\n",
    "    \"remote\" : 'https://cdn.buenosaires.gob.ar/datosabiertos/datasets/transporte/estaciones-bicicletas-publicas/nuevas-estaciones-bicicletas-publicas.xlsx',   \n",
    "    \"local\" : datadir + 'dias_festivos.csv',\n",
    "    \"df\" : pd.DataFrame(),\n",
    "  },\n",
    "#   \"rutas\" : {\n",
    "#     \"remote\" : '',   \n",
    "#     \"local\" : datadir + 'rutas.csv',\n",
    "#     \"df\" : pd.DataFrame(),\n",
    "#   },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "OUNJNx4JRkJI",
    "tags": []
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Cargar insumos\n",
    "\"\"\"\n",
    "def cargar_insumos( insumos ):\n",
    "    for i in insumos:\n",
    "        if not os.path.exists( insumos[i][\"local\"] ):\n",
    "            print(\"Descargando:\", insumos[i][\"remote\"])\n",
    "            urllib.request.urlretrieve(\n",
    "              insumos[i][\"remote\"],\n",
    "              insumos[i][\"local\"]\n",
    "            )\n",
    "        if \"zip\" in insumos[i][\"local\"]:\n",
    "            insumos[i][\"df\"] = pd.read_csv(\n",
    "                insumos[i][\"local\"],\n",
    "                compression = 'zip',\n",
    "                encoding = 'utf-8', \n",
    "            )\n",
    "        if \"csv\" in insumos[i][\"local\"]:\n",
    "            insumos[i][\"df\"] = pd.read_csv(\n",
    "                insumos[i][\"local\"],\n",
    "            )\n",
    "        if \"xlsx\" in insumos[i][\"local\"]:\n",
    "            insumos[i][\"df\"] = pd.read_excel(\n",
    "                insumos[i][\"local\"],\n",
    "            )\n",
    "        print( 'Cargado: ', insumos[i][\"local\"] )\n",
    "    return insumos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "mNXoe6RWRkJJ",
    "outputId": "23b325c8-6abe-43f7-900e-7e999b9f19c7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cargado:  datasets/recorridos-realizados-2020.zip\n",
      "Cargado:  datasets/recorridos-realizados-2021.zip\n",
      "Cargado:  datasets/weather-data-ba.csv\n",
      "Cargado:  datasets/dias_festivos.csv\n"
     ]
    }
   ],
   "source": [
    "insumos = cargar_insumos( insumos )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "04Qn4D0IbidY"
   },
   "source": [
    "# Subproductos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "id": "zer0swUV7zTj"
   },
   "outputs": [],
   "source": [
    "derivados = {\n",
    "  \"recorridos\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'recorridos_clima-2020-2021.csv',\n",
    "    \"remote\" : '',   \n",
    "  },\n",
    "#   \"vueltas\" : {\n",
    "#     \"df\"     : pd.DataFrame(),\n",
    "#     \"local\"  : datadir + 'vueltas.csv',\n",
    "#     \"remote\" : '',   \n",
    "#   },\n",
    "  \"estaciones\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'estaciones.csv',\n",
    "    \"remote\" : '',   \n",
    "  },\n",
    "  \"distancias\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'distancias.csv',\n",
    "    \"remote\" : '',   \n",
    "  },\n",
    "  \"rutas\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'rutas.csv',\n",
    "    \"remote\" : '',   \n",
    "  },\n",
    "  \"clima\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'clima_datohorario_2020_2021.csv', \n",
    "    \"remote\" : '',\n",
    "  },\n",
    "  \"pronostico\" : {\n",
    "    \"df\"     : pd.DataFrame(),\n",
    "    \"local\"  : datadir + 'pronostico_2020_2021.csv', \n",
    "    \"remote\" : '',\n",
    "  },\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TCLX76JPdyBL"
   },
   "source": [
    " ## Normalizacion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "id": "LUu7r97bRkJL"
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Renombrar y filtrar columnas 2021\n",
    "\"\"\"\n",
    "encabezados2021 = {\n",
    "    'ID'                                  : 'id',\n",
    "    'Duración'                            : 'duracion_recorrido',\n",
    "    'Estado cerrado'                      : 'estado_cerrado',\n",
    "    'ID de ciclista'                      : 'id_usuario',\n",
    "    'Tipo de ciclista'                    : 'cilicsta_tipo',\n",
    "    'Modelo de bicicleta'                 : 'bicicleta_modelo',   \n",
    "    'Id de estación de inicio'            : 'id_estacion_origen', \n",
    "    'Nombre de estación de inicio'        : 'nombre_estacion_origen',\n",
    "    'Fecha de inicio'                     : 'fecha_origen_recorrido',     \n",
    "    'Origen de viaje'                     : 'direccion_estacion_origen',  \n",
    "#     'lon_estacion_origen'                 : 'lon_estacion_origen',        \n",
    "#     'lat_estacion_origen'                 : 'lat_estacion_origen',\n",
    "    'Id de estación de fin de viaje'      : 'id_estacion_destino',     \n",
    "    'Nombre de estación de fin de viaje'  : 'nombre_estacion_destino',\n",
    "    'Fecha de fin'                        : 'fecha_destino_recorrido',          \n",
    "#     'direccion_estacion_destino'          : 'direccion_estacion_origen',           \n",
    "#     'lon_estacion_destino'                : 'lon_estacion_destino',       \n",
    "#     'lat_estacion_destino'                : 'lat_estacion_destino',\n",
    "#     'periodo'                             : 'periodo',  \n",
    "}\n",
    "insumos[\"recorridos-2021\"][\"df\"] = insumos[\"recorridos-2021\"][\"df\"].rename( \n",
    "    columns = encabezados2021\n",
    ")\n",
    "insumos[\"recorridos-2021\"][\"df\"][\"id\"] = insumos[\"recorridos-2021\"][\"df\"]['id'].astype('Int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "6EMTpai0RkJM"
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Concatenar recorridos 2020:2021\n",
    "\"\"\"\n",
    "recorridos = [\n",
    "    insumos[\"recorridos-2020\"][\"df\"],\n",
    "    insumos[\"recorridos-2021\"][\"df\"]\n",
    "]\n",
    "derivados[\"recorridos\"][\"df\"] = pd.concat( recorridos )\n",
    "del recorridos  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "vi1TnxNDRkJP"
   },
   "outputs": [],
   "source": [
    "encabezados = {\n",
    "#     'id'                         : 'id',\n",
    "    'duracion_recorrido'         : 'duracion',\n",
    "#     'estado_cerrado'             : 'estado_cerrado',\n",
    "    'id_usuario'                 : 'usuario_id',\n",
    "#     'cilicsta_tipo'            : 'cilicsta_tipo',\n",
    "#     'bicicleta_modelo'         : 'bicicleta_modelo',   \n",
    "    'id_estacion_origen'         : 'origen_id', \n",
    "    'nombre_estacion_origen'     : 'origen_nombre',\n",
    "    'fecha_origen_recorrido'     : 'origen_fecha',     \n",
    "    'direccion_estacion_origen'  : 'origen_direccion',\n",
    "#     'lat_estacion_origen'        : 'origen_lat',  \n",
    "#     'long_estacion_origen'       : 'origen_lon',        \n",
    "    'id_estacion_destino'        : 'destino_id',     \n",
    "    'nombre_estacion_destino'    : 'destino_nombre',\n",
    "    'fecha_destino_recorrido'    : 'destino_fecha',          \n",
    "#     'direccion_estacion_destino' : 'destino_direccion', \n",
    "#     'lat_estacion_destino'       : 'destino_lat',          \n",
    "#     'long_estacion_destino'      : 'destino_lon',       \n",
    "#     'periodo'                    : 'periodo',  \n",
    "}\n",
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"].rename( columns = encabezados )\n",
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"][ list( encabezados.values() ) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# len(derivados[\"recorridos\"][\"df\"][\"destino_id\"].unique())\n",
    "# # # hay 3 destinos mas q viajes  \n",
    "# # [nan, 288.0, 427.0, 47.0, 447.0]\n",
    "# list(\n",
    "#     set( derivados['recorridos']['df']['destino_nombre'].unique().tolist() ) -\n",
    "#     set( derivados['recorridos']['df']['origen_nombre'].unique().tolist() )\n",
    "# )\n",
    "# #     3 destinos mas q viajes  [333, 397, 47]\n",
    "# # [nan,\n",
    "# #  'Balboa Definitivo',\n",
    "# #  '333 - MIRO',\n",
    "# #  '047 - Colegio Nacional de Buenos Aires',\n",
    "# #  '397 - PLAZA MATHEU']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "EZZQppOmTfFD"
   },
   "outputs": [],
   "source": [
    "derivados[\"recorridos\"][\"df\"].dropna( subset = [ \"destino_id\" ], inplace = True )\n",
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"][\n",
    "    derivados[\"recorridos\"][\"df\"]['destino_nombre'] != 'Balboa Definitivo'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "4cZgdEwARkJS"
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Separar las columnas nombre de estacion de origen y nombre de estacion de destino \n",
    "Tienen la info correcta del id de las estaciones.\n",
    "Divido la columna Nombre de estacion de fin de viaje porque contiene dos campos \n",
    "el Id y el nombre de la estacion de inicio de viaje.\n",
    "\"\"\"\n",
    "derivados[\"recorridos\"][\"df\"][\n",
    "    ['origen_id','origen_nombre']\n",
    "] = derivados[\"recorridos\"][\"df\"]['origen_nombre'].str.split('-', expand = True )\n",
    "derivados[\"recorridos\"][\"df\"]['origen_id'] = derivados[\"recorridos\"][\"df\"]['origen_id'].str.strip(' ')\n",
    "derivados[\"recorridos\"][\"df\"]['origen_nombre'] = derivados[\"recorridos\"][\"df\"]['origen_nombre'].str.strip(' ')\n",
    "derivados[\"recorridos\"][\"df\"][\n",
    "    ['destino_id','destino_nombre']\n",
    "] = derivados[\"recorridos\"][\"df\"]['destino_nombre'].str.split('-', expand = True )\n",
    "derivados[\"recorridos\"][\"df\"]['destino_id'] = derivados[\"recorridos\"][\"df\"]['destino_id'].str.strip(' ')\n",
    "derivados[\"recorridos\"][\"df\"]['destino_nombre'] = derivados[\"recorridos\"][\"df\"]['destino_nombre'].str.strip(' ')\n",
    "\n",
    "# # hay 3 destinos mas q viajes  [ 333, 397, 47 ]\n",
    "# list(\n",
    "#     set( derivados['recorridos']['df']['destino_id'].unique().tolist() )-\n",
    "#     set( derivados['recorridos']['df']['origen_id'].unique().tolist() )\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "k0nV5WenRkJT"
   },
   "outputs": [],
   "source": [
    "derivados[\"recorridos\"][\"df\"]['origen_id'] = derivados[\"recorridos\"][\"df\"][\"origen_id\"].str.lstrip('0').astype(int)\n",
    "derivados[\"recorridos\"][\"df\"]['destino_id'] = derivados[\"recorridos\"][\"df\"][\"destino_id\"].str.lstrip('0').astype(int)\n",
    "derivados[\"recorridos\"][\"df\"][\"origen_nombre\"]  = derivados[\"recorridos\"][\"df\"][\"origen_nombre\"].str.upper()\n",
    "derivados[\"recorridos\"][\"df\"][\"destino_nombre\"]  = derivados[\"recorridos\"][\"df\"][\"destino_nombre\"].str.upper()\n",
    "derivados[\"recorridos\"][\"df\"][\"origen_direccion\"]  = derivados[\"recorridos\"][\"df\"][\"origen_direccion\"].str.upper()\n",
    "# derivados[\"recorridos\"][\"df\"][\"destino_direccion\"]  = derivados[\"recorridos\"][\"df\"][\"destino_direccion\"].str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion            0\n",
       "usuario_id          0\n",
       "origen_id           0\n",
       "origen_nombre       0\n",
       "origen_fecha        0\n",
       "origen_direccion    0\n",
       "destino_id          0\n",
       "destino_nombre      0\n",
       "destino_fecha       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# LIMPIAR Y PREPARAR ESTACIONES \n",
    "# derivados[\"estaciones\"][\"df\"] = pd.DataFrame()\n",
    "\n",
    "derivados[\"estaciones\"][\"df\"]['id'] = insumos[\"estaciones\"][\"df\"]['codigo']\n",
    "derivados[\"estaciones\"][\"df\"]['nombre'] = insumos[\"estaciones\"][\"df\"]['nombre'].str.replace(\n",
    "    '\\d+', '',\n",
    "    regex=True\n",
    ").str.replace('-', '', regex=True).str.strip(' ').str.upper()\n",
    "derivados[\"estaciones\"][\"df\"]['oid'] = insumos[\"estaciones\"][\"df\"]['id']\n",
    "derivados[\"estaciones\"][\"df\"]['direccion'] = insumos[\"estaciones\"][\"df\"]['ubicacion'].str.upper()\n",
    "derivados[\"estaciones\"][\"df\"][\n",
    "    ['lon','lat']\n",
    "] = insumos[\"estaciones\"][\"df\"]['WKT'].str.replace('POINT ','').str.lstrip('(').str.rstrip(')').str.split(' ', expand = True )\n",
    "derivados[\"estaciones\"][\"df\"]['anclajes'] = insumos[\"estaciones\"][\"df\"]['anclajes_t']\n",
    "derivados[\"estaciones\"][\"df\"] # clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "id": "55ODIY98RkJV",
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# AGREGAR DATA DE VIAJES \n",
    "salidas = derivados[\"recorridos\"][\"df\"].groupby(\n",
    "    'origen_id' \n",
    ").agg(\n",
    "    salidas = pd.NamedAgg( column = \"origen_id\", aggfunc = \"count\"),\n",
    ").reset_index()\n",
    "\n",
    "arribos = derivados[\"recorridos\"][\"df\"].groupby(\n",
    "    'destino_id'\n",
    ").agg(\n",
    "  arribos = pd.NamedAgg( column = \"destino_id\", aggfunc = \"count\"),\n",
    ").reset_index()\n",
    "\n",
    "salidas_arribos = pd.merge(\n",
    "    left     = salidas,\n",
    "    right    = arribos,\n",
    "    how      = 'left',\n",
    "    left_on  = ['origen_id'],\n",
    "    right_on = ['destino_id']\n",
    ")\n",
    "\n",
    "derivados[\"estaciones\"][\"df\"] = pd.merge(\n",
    "    left     = derivados[\"estaciones\"][\"df\"],\n",
    "    right    = salidas_arribos,\n",
    "    how      = 'left',\n",
    "    left_on  = ['id','id'],\n",
    "    right_on = ['origen_id','destino_id']\n",
    ")\n",
    "\n",
    "del [salidas, arribos, salidas_arribos]\n",
    "\n",
    "derivados[\"estaciones\"][\"df\"][\"salidas\"] = derivados[\"estaciones\"][\"df\"][\"salidas\"].fillna(0).astype(int)\n",
    "derivados[\"estaciones\"][\"df\"][\"arribos\"] = derivados[\"estaciones\"][\"df\"][\"arribos\"].fillna(0).astype(int)\n",
    "\n",
    "derivados[\"estaciones\"][\"df\"] = derivados[\"estaciones\"][\"df\"].drop(\n",
    "    columns = ['origen_id','destino_id']\n",
    ")\n",
    "derivados[\"estaciones\"][\"df\"] = derivados[\"estaciones\"][\"df\"].sort_values(\n",
    "   by = ['salidas'], ascending = [False]\n",
    ")\n",
    "\n",
    "# GRABAR CSV ESTACIONES\n",
    "derivados[\"estaciones\"][\"df\"].to_csv(\n",
    "  derivados[\"estaciones\"][\"local\"],\n",
    "  index = False,\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "# CARGAR ESTACIONES\n",
    "# insumos[\"estaciones\"][\"df\"] # raw\n",
    "# derivados[\"estaciones\"][\"df\"] = pd.read_csv(\n",
    "#   derivados[\"estaciones\"][\"local\"]\n",
    "# )\n",
    "# derivados[\"estaciones\"][\"df\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# print(\n",
    "#     derivados[\"estaciones\"][\"df\"][\"salidas\"].sum() -\n",
    "#     derivados[\"estaciones\"][\"df\"][\"arribos\"].sum()\n",
    "# )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ptq8hRiYUBHg"
   },
   "source": [
    "## RESAMPLING (REMUESTREO)\n",
    "\n",
    "### Downsampling frequencies \n",
    "\n",
    "Tamaño de la muestra = Minuto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "qIy_x970UBHi"
   },
   "outputs": [],
   "source": [
    "formato = '%Y%m%d %H:%M:%S'\n",
    "derivados[\"recorridos\"][\"df\"]['origen_datetime'] = pd.to_datetime(\n",
    "    derivados[\"recorridos\"][\"df\"]['origen_fecha'], \n",
    "    format = formato\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"]['fecha'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%Y-%m-%d')\n",
    "# derivados[\"recorridos\"][\"df\"]['anio'] = derivados[\"recorridos\"][\"df\"][\"fecha\"].str.split('-')[0] \n",
    "derivados[\"recorridos\"][\"df\"]['anio'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%Y').astype(str).astype(int)\n",
    "derivados[\"recorridos\"][\"df\"]['ndia'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%j').astype(str).astype(int)\n",
    "# FECHA DELTA Y ESTACIONALIDAD SEMANAL\n",
    "primer_anio = derivados[\"recorridos\"][\"df\"]['anio'].min()\n",
    "derivados[\"recorridos\"][\"df\"].eval( 'ddia = ( ( anio % @primer_anio ) * 365 ) + ndia', inplace = True )\n",
    "derivados[\"recorridos\"][\"df\"]['wdia'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%w').astype(str).astype(int)\n",
    "# DIA HABIL \n",
    "derivados[\"recorridos\"][\"df\"][\"dhabil\"] = 0\n",
    "# Weekday as a decimal number, where 0 is Sunday and 6 is Saturday.\n",
    "derivados[\"recorridos\"][\"df\"][\"dhabil\"][ (derivados[\"recorridos\"][\"df\"]['wdia'] >= 1) | (derivados[\"recorridos\"][\"df\"]['wdia'] <= 5) ] = 1\n",
    "derivados[\"recorridos\"][\"df\"]['hora'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%H').astype(str).astype(int)\n",
    "derivados[\"recorridos\"][\"df\"]['mins'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%M').astype(str).astype(int)\n",
    "\n",
    "# derivados[\"recorridos\"][\"df\"].eval( 'dhabil = wdia > 4', inplace = True )\n",
    "# derivados[\"recorridos\"][\"df\"]['dhabil'] = derivados[\"recorridos\"][\"df\"]['dhabil'].astype(int)\n",
    "\n",
    "# derivados[\"recorridos\"][\"df\"]['mes']  = bicicletas[\"recorridos_df\"]['start_datetime'].dt.strftime('%b').str.lower()\n",
    "# derivados[\"recorridos\"][\"df\"]['nmes'] = derivados[\"recorridos\"][\"df\"]['origen_datetime'].dt.strftime('%m').astype(str).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3, 2, 1, 0])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ESTACIONALIDAD TRIMESTRAL\n",
    "derivados[\"recorridos\"][\"df\"][\"trimestre\"] = pd.cut( \n",
    "    derivados[\"recorridos\"][\"df\"].ndia,\n",
    "    include_lowest = True,\n",
    "    bins = 4,\n",
    "    labels = False,\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"]['trimestre'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3331632 entries, 0 to 1328725\n",
      "Data columns (total 19 columns):\n",
      " #   Column            Dtype  \n",
      "---  ------            -----  \n",
      " 0   duracion          int64  \n",
      " 1   usuario_id        int64  \n",
      " 2   origen_id         int64  \n",
      " 3   origen_nombre     object \n",
      " 4   origen_fecha      object \n",
      " 5   origen_direccion  object \n",
      " 6   origen_lat        float64\n",
      " 7   origen_lon        float64\n",
      " 8   destino_id        int64  \n",
      " 9   destino_nombre    object \n",
      " 10  destino_fecha     object \n",
      " 11  fecha             object \n",
      " 12  ndia              int64  \n",
      " 13  ddia              int64  \n",
      " 14  wdia              int64  \n",
      " 15  dhabil            int64  \n",
      " 16  hora              int64  \n",
      " 17  mins              int64  \n",
      " 18  trimestre         int64  \n",
      "dtypes: float64(2), int64(11), object(6)\n",
      "memory usage: 508.4+ MB\n"
     ]
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"].drop( columns = { 'anio','origen_datetime'} )\n",
    "derivados[\"recorridos\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion            0\n",
       "usuario_id          0\n",
       "origen_id           0\n",
       "origen_nombre       0\n",
       "origen_fecha        0\n",
       "origen_direccion    0\n",
       "destino_id          0\n",
       "destino_nombre      0\n",
       "destino_fecha       0\n",
       "fecha               0\n",
       "ndia                0\n",
       "wdia                0\n",
       "dhabil              0\n",
       "hora                0\n",
       "mins                0\n",
       "ddia                0\n",
       "trimestre           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>duracion</th>\n",
       "      <th>usuario_id</th>\n",
       "      <th>origen_id</th>\n",
       "      <th>destino_id</th>\n",
       "      <th>ndia</th>\n",
       "      <th>wdia</th>\n",
       "      <th>dhabil</th>\n",
       "      <th>hora</th>\n",
       "      <th>mins</th>\n",
       "      <th>ddia</th>\n",
       "      <th>trimestre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "      <td>3.331632e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.360008e+03</td>\n",
       "      <td>4.366418e+05</td>\n",
       "      <td>1.526023e+02</td>\n",
       "      <td>1.537240e+02</td>\n",
       "      <td>1.727415e+02</td>\n",
       "      <td>3.032122e+00</td>\n",
       "      <td>2.768514e-01</td>\n",
       "      <td>1.431416e+01</td>\n",
       "      <td>2.915972e+01</td>\n",
       "      <td>3.183070e+02</td>\n",
       "      <td>1.394215e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.219582e+03</td>\n",
       "      <td>2.613882e+05</td>\n",
       "      <td>1.036271e+02</td>\n",
       "      <td>1.039965e+02</td>\n",
       "      <td>1.189105e+02</td>\n",
       "      <td>1.916750e+00</td>\n",
       "      <td>4.474425e-01</td>\n",
       "      <td>5.293337e+00</td>\n",
       "      <td>1.726815e+01</td>\n",
       "      <td>1.274829e+02</td>\n",
       "      <td>1.247864e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.700000e+01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.830000e+02</td>\n",
       "      <td>1.747990e+05</td>\n",
       "      <td>7.000000e+01</td>\n",
       "      <td>7.100000e+01</td>\n",
       "      <td>5.800000e+01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.100000e+01</td>\n",
       "      <td>1.400000e+01</td>\n",
       "      <td>2.530000e+02</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.095000e+03</td>\n",
       "      <td>4.971600e+05</td>\n",
       "      <td>1.420000e+02</td>\n",
       "      <td>1.420000e+02</td>\n",
       "      <td>1.510000e+02</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.500000e+01</td>\n",
       "      <td>2.900000e+01</td>\n",
       "      <td>3.320000e+02</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.613000e+03</td>\n",
       "      <td>6.840670e+05</td>\n",
       "      <td>2.270000e+02</td>\n",
       "      <td>2.270000e+02</td>\n",
       "      <td>2.870000e+02</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.800000e+01</td>\n",
       "      <td>4.400000e+01</td>\n",
       "      <td>4.170000e+02</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.190094e+06</td>\n",
       "      <td>7.722650e+05</td>\n",
       "      <td>3.990000e+02</td>\n",
       "      <td>3.990000e+02</td>\n",
       "      <td>3.660000e+02</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>2.300000e+01</td>\n",
       "      <td>5.900000e+01</td>\n",
       "      <td>5.180000e+02</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           duracion    usuario_id     origen_id    destino_id          ndia  \\\n",
       "count  3.331632e+06  3.331632e+06  3.331632e+06  3.331632e+06  3.331632e+06   \n",
       "mean   1.360008e+03  4.366418e+05  1.526023e+02  1.537240e+02  1.727415e+02   \n",
       "std    5.219582e+03  2.613882e+05  1.036271e+02  1.039965e+02  1.189105e+02   \n",
       "min    0.000000e+00  3.700000e+01  1.000000e+00  1.000000e+00  1.000000e+00   \n",
       "25%    6.830000e+02  1.747990e+05  7.000000e+01  7.100000e+01  5.800000e+01   \n",
       "50%    1.095000e+03  4.971600e+05  1.420000e+02  1.420000e+02  1.510000e+02   \n",
       "75%    1.613000e+03  6.840670e+05  2.270000e+02  2.270000e+02  2.870000e+02   \n",
       "max    3.190094e+06  7.722650e+05  3.990000e+02  3.990000e+02  3.660000e+02   \n",
       "\n",
       "               wdia        dhabil          hora          mins          ddia  \\\n",
       "count  3.331632e+06  3.331632e+06  3.331632e+06  3.331632e+06  3.331632e+06   \n",
       "mean   3.032122e+00  2.768514e-01  1.431416e+01  2.915972e+01  3.183070e+02   \n",
       "std    1.916750e+00  4.474425e-01  5.293337e+00  1.726815e+01  1.274829e+02   \n",
       "min    0.000000e+00  0.000000e+00  0.000000e+00  0.000000e+00  1.000000e+00   \n",
       "25%    1.000000e+00  0.000000e+00  1.100000e+01  1.400000e+01  2.530000e+02   \n",
       "50%    3.000000e+00  0.000000e+00  1.500000e+01  2.900000e+01  3.320000e+02   \n",
       "75%    5.000000e+00  1.000000e+00  1.800000e+01  4.400000e+01  4.170000e+02   \n",
       "max    6.000000e+00  1.000000e+00  2.300000e+01  5.900000e+01  5.180000e+02   \n",
       "\n",
       "          trimestre  \n",
       "count  3.331632e+06  \n",
       "mean   1.394215e+00  \n",
       "std    1.247864e+00  \n",
       "min    0.000000e+00  \n",
       "25%    0.000000e+00  \n",
       "50%    1.000000e+00  \n",
       "75%    3.000000e+00  \n",
       "max    3.000000e+00  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 360 entries, 46714 to 1327256\n",
      "Data columns (total 17 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   duracion          360 non-null    int64 \n",
      " 1   usuario_id        360 non-null    int64 \n",
      " 2   origen_id         360 non-null    int64 \n",
      " 3   origen_nombre     360 non-null    object\n",
      " 4   origen_fecha      360 non-null    object\n",
      " 5   origen_direccion  360 non-null    object\n",
      " 6   destino_id        360 non-null    int64 \n",
      " 7   destino_nombre    360 non-null    object\n",
      " 8   destino_fecha     360 non-null    object\n",
      " 9   fecha             360 non-null    object\n",
      " 10  ndia              360 non-null    int64 \n",
      " 11  wdia              360 non-null    int64 \n",
      " 12  dhabil            360 non-null    int64 \n",
      " 13  hora              360 non-null    int64 \n",
      " 14  mins              360 non-null    int64 \n",
      " 15  ddia              360 non-null    int64 \n",
      " 16  trimestre         360 non-null    int64 \n",
      "dtypes: int64(11), object(6)\n",
      "memory usage: 50.6+ KB\n"
     ]
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"][\n",
    "    derivados[\"recorridos\"][\"df\"]['duracion'] >= 86400 # 24hs * 60' * 60''\n",
    "].info() # 360 VIAJES DE MAS DE 1 DIA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # FERIADOS\n",
    "# insumos[\"feriados\"][\"df\"] = pd.read_csv(\n",
    "#     datadir + 'dias_festivos.csv'\n",
    "# )\n",
    "# derivados[\"feriados\"][\"df\"][\"nmes\"] = derivados[\"feriados\"][\"df\"][\"nmes\"].astype(str).apply(lambda x: x.zfill(2))\n",
    "# derivados[\"feriados\"][\"df\"][\"fecha\"] = derivados[\"feriados\"][\"df\"][\"anio\"].astype(str) \\\n",
    "#     + '-' + derivados[\"feriados\"][\"df\"][\"nmes\"].astype(str) \\\n",
    "#     + '-' + derivados[\"feriados\"][\"df\"][\"ndia\"].astype(str)\n",
    "# derivados[\"feriados\"][\"df\"][\"feriado\"] = 1\n",
    "# insumos[\"feriados\"][\"df\"] = derivados[\"feriados\"][\"df\"].sort_values('fecha').reset_index()\n",
    "# derivados[\"feriados\"][\"df\"] = derivados[\"feriados\"][\"df\"].drop( \n",
    "#     columns=[ 'Unnamed: 0', 'anio', 'nmes', 'ndia', 'index' ]\n",
    "# )\n",
    "# # derivados[\"feriados\"][\"df\"].to_csv(\n",
    "# #   insumos[\"feriados\"][\"local\"],\n",
    "# #   index = False,\n",
    "# # )\n",
    "# derivados[\"feriados\"][\"df\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 59 entries, 0 to 58\n",
      "Data columns (total 4 columns):\n",
      " #   Column   Non-Null Count  Dtype \n",
      "---  ------   --------------  ----- \n",
      " 0   label    59 non-null     object\n",
      " 1   type     59 non-null     object\n",
      " 2   fecha    59 non-null     object\n",
      " 3   feriado  59 non-null     int64 \n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 2.0+ KB\n"
     ]
    }
   ],
   "source": [
    "insumos[\"feriados\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "derivados[\"recorridos\"][\"df\"] = pd.merge(\n",
    "    left     = derivados[\"recorridos\"][\"df\"],\n",
    "    right    = insumos[\"feriados\"][\"df\"],\n",
    "    how      = 'left',\n",
    "    left_on  = ['fecha'],\n",
    "    right_on = ['fecha']\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"].drop( \n",
    "    columns=[ 'label','type' ]\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"][\"feriado\"] = derivados[\"recorridos\"][\"df\"][\"feriado\"].fillna(0).astype(int)\n",
    "derivados[\"recorridos\"][\"df\"][\"dhabil\"][ derivados[\"recorridos\"][\"df\"][\"feriado\"] == 1 ] = 0\n",
    "del insumos[\"feriados\"][\"df\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# del insumos[\"feriados\"][\"df\"]\n",
    "# derivados[\"recorridos\"][\"df\"][ derivados[\"recorridos\"][\"df\"][\"feriado\"] == 1 ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "J8WAVsJAm2ub"
   },
   "source": [
    "## FEATURE DISCOVERY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "4EE5UvUllIvd",
    "outputId": "f692e955-6e92-407a-cadc-da131063ec79"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22 53168\n"
     ]
    }
   ],
   "source": [
    "# DURACION EN MINUTOS \n",
    "derivados[\"recorridos\"][\"df\"][\"duracion_mins\"] = 0\n",
    "derivados[\"recorridos\"][\"df\"].eval('duracion_mins = duracion / 60', inplace = True) # mins\n",
    "derivados[\"recorridos\"][\"df\"][\"duracion_mins\"] = derivados[\"recorridos\"][\"df\"][\"duracion_mins\"].astype(int)\n",
    "print(\n",
    "    int(derivados[\"recorridos\"][\"df\"][\"duracion_mins\"].mean()),\n",
    "    derivados[\"recorridos\"][\"df\"][\"duracion_mins\"].max()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "4EE5UvUllIvd",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "f692e955-6e92-407a-cadc-da131063ec79",
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1, 2, 3])"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # mean_dur = int(derivados[\"recorridos\"][\"df\"][\"duracion_mins\"].mean())\n",
    "# mean_dur = round( int(derivados[\"recorridos\"][\"df\"][\"duracion_mins\"].mean()), -1 ) # 22 > 20\n",
    "# bins = 20\n",
    "# dur_cortes  = [ int( ( i * 0.25 ) * mean_dur ) for i in range( 0, bins, 1 )  ]\n",
    "# labels = [ dur_cortes[ i ] for i in range( 0, len( dur_cortes ) -1, 1 ) ]\n",
    "# labels= [0, 5, 11, 16, 22, 27, 33, 38, 44]\n",
    "# derivados[\"recorridos\"][\"df\"][\"duracion_cat\"] = pd.cut( \n",
    "#     derivados[\"recorridos\"][\"df\"].duracion_mins,\n",
    "# #     dur_cortes,\n",
    "# #     labels = labels,\n",
    "# #     right = True,\n",
    "#     include_lowest = True,\n",
    "# #     ordered = True,\n",
    "#     bins = 4,\n",
    "#     labels = False,\n",
    "# )\n",
    "# derivados[\"recorridos\"][\"df\"][\"duracion_cat\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "4EE5UvUllIvd",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "f692e955-6e92-407a-cadc-da131063ec79",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # MINUTOS TO CATEGORIES\n",
    "# labels = [ \"{0}-{1}\".format(i, i + top_dur) for i in range(0, top_dur, mean_dur) ]\n",
    "# labels = [ \"{0}-{1}\".format(i, i + 15) for i in range(0, 265, 15) ]\n",
    "# dur_etiquetas = [ 1, 2, 3, 4, 'M', 'ML', 'L', 'XL', 'XXL', 'OS' ]\n",
    "# dur_cortes    = [ 0,    22.240011093532345,   15,  30,    60,     90, 120,  240, 320, 60000 ]\n",
    "# derivados[\"recorridos\"][\"df\"][\"duracion_cat\"] = pd.cut( \n",
    "#     derivados[\"recorridos\"][\"df\"].duracion_mins,\n",
    "# #     dur_cortes,\n",
    "# #     labels = dur_etiquetas\n",
    "#     right = False,\n",
    "#     include_lowest = True,\n",
    "#     bins = 10,\n",
    "#     labels = False\n",
    "# )\n",
    "# derivados[\"recorridos\"][\"df\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "etzsFLhCU2vI"
   },
   "source": [
    "### TIPO DE RECORRIDO: VUELTA / IDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "id": "8vlkg9hmR8kJ"
   },
   "outputs": [],
   "source": [
    "derivados[\"recorridos\"][\"df\"]['vuelta'] = np.where( \n",
    "  derivados[\"recorridos\"][\"df\"]['origen_id'] == derivados[\"recorridos\"][\"df\"]['destino_id'], 1, 0\n",
    ")\n",
    "# derivados[\"recorridos\"][\"df\"][ derivados[\"recorridos\"][\"df\"]['vuelta'] == 1 ] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "T3yUhuXZtjRi"
   },
   "source": [
    "### DISTANCIAS\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EeP-4Q71nDJC",
    "outputId": "e56103b8-9b06-4cc0-9917-1ed45ab8537c"
   },
   "outputs": [],
   "source": [
    "# !pip install openrouteservice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "YFfn3c94YNmU",
    "outputId": "380add7f-1121-4ce7-9ff0-9fa37def2388"
   },
   "outputs": [],
   "source": [
    "derivados[\"estaciones\"][\"df\"] = pd.read_csv(\n",
    " derivados[\"estaciones\"][\"local\"],\n",
    "#      ignore_index = True,\n",
    ")\n",
    "estaciones = [ x for x in derivados[\"estaciones\"][\"df\"].set_index('id').sort_values('id').iterrows() ]\n",
    "# del rutas\n",
    "rutas = {}\n",
    "nid = 0\n",
    "for o in estaciones:\n",
    "    origen_id = int(o[0]) # revisar\n",
    "    print (origen_id)\n",
    "    # https://ask.openrouteservice.org/t/could-not-find-point-within-a-radius-of-350-0-meters/2668/16\n",
    "    origen_coords = ( o[1]['lon'], o[1]['lat'] ) # ORS takes lon,lat \n",
    "    for d in estaciones:\n",
    "        destino_id = int(d[0]) # revisar\n",
    "        destino_coords = ( d[1]['lon'], d[1]['lat'] ) # ibidem\n",
    "        ruta_id = str( origen_id ) + 'to' + str( destino_id )\n",
    "        vuelta_id = str( destino_id) + 'to' + str( origen_id )\n",
    "        ruta = {\n",
    "            \"id\" : ruta_id,\n",
    "            \"nid\" : nid,\n",
    "            \"origen_id\": origen_id,\n",
    "            \"destino_id\": destino_id,\n",
    "            \"coords\" : ( origen_coords, destino_coords ),\n",
    "            \"distancia\" : 0,\n",
    "            \"duracion_estimada\" : 0,\n",
    "            \"consultar\" : ( origen_id != destino_id ),\n",
    "            \"complemetaria\" : vuelta_id,\n",
    "            \"preexiste\" : (ruta_id in rutas or vuelta_id in rutas),\n",
    "        }\n",
    "        nid += 1\n",
    "        rutas[ruta_id] = ruta\n",
    "print( 'rutas:', len( rutas ) )\n",
    "\n",
    "rutas_calcular = {}\n",
    "chunk = 0\n",
    "cuenta_rutas = 0\n",
    "for r in rutas:\n",
    "    ruta = rutas[r]\n",
    "    if ruta['consultar'] and not ruta[\"preexiste\"]:\n",
    "        if( (cuenta_rutas % 2000) == 0 ): # due to ors dayly cuota\n",
    "            chunk = chunk + 1  \n",
    "        ruta[\"chunk\"] = chunk\n",
    "        rutas_calcular[r] = ruta\n",
    "        cuenta_rutas = cuenta_rutas + 1\n",
    "print( 'rutas a calular:', len( rutas_calcular ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2000\n"
     ]
    }
   ],
   "source": [
    "cuenta = 0\n",
    "for r in rutas_calcular:\n",
    "    ruta = rutas_calcular[r]\n",
    "    if ruta[\"chunk\"] == 3:\n",
    "        cuenta += 1\n",
    "print(cuenta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from time import sleep\n",
    "import openrouteservice as ors\n",
    "\n",
    "ors_client = ors.Client(\n",
    "    key = '5b3ce3597851110001cf624827a63bd0239f438caa1f7504352a9fc8'\n",
    ") \n",
    "# del rutas_calculadas\n",
    "# rutas_calculadas = pd.DataFrame()\n",
    "\n",
    "# derivados[\"rutas\"][\"df\"]  = pd.read_csv(\n",
    "#   derivados[\"rutas\"][\"local\"],\n",
    "# )\n",
    "\n",
    "# CARGAR RUTAS YA CALCULDAS\n",
    "derivados[\"rutas\"][\"df\"] = pd.read_csv(\n",
    "  derivados[\"rutas\"][\"local\"],\n",
    ")\n",
    "rutas_calculadas = derivados[\"rutas\"][\"df\"] \n",
    "cuenta_calculadas = 1\n",
    "for rc in rutas_calcular:\n",
    "    ruta = rutas[rc]\n",
    "    if ruta['chunk'] == 4: # ya hice el chunk 2.80\n",
    "        if ( ( cuenta_calculadas % 40 ) == 0 ): # due to ors minute cuota\n",
    "            print( 'durmiendo', cuenta_calculadas )\n",
    "            print( 'rutas calculadas:', len( rutas_calculadas.index ) )\n",
    "            rutas_calculadas\n",
    "            sleep(60)\n",
    "#         if 'directions' not in ruta:\n",
    "        ruta['directions'] = ors_client.directions(\n",
    "          ruta['coords'],\n",
    "          profile = 'cycling-regular',\n",
    "        )\n",
    "        cuenta_calculadas = cuenta_calculadas + 1\n",
    "        rutas_calculadas = rutas_calculadas.append(\n",
    "           ruta,\n",
    "           ignore_index = True\n",
    "        )\n",
    "print( 'rutas calculadas hoy:', len(rutas_calculadas.index))\n",
    "# rutas_df = pd.concat(   derivados[\"rutas\"][\"df\"] , rutas_calculadas ] )\n",
    "\n",
    "rutas_calculadas.to_csv(\n",
    "  derivados[\"rutas\"][\"local\"],\n",
    "  index = False,\n",
    ")\n",
    "# rutas_df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 32
    },
    "id": "3KIdyq0emDyb",
    "outputId": "24af82ae-b0e7-4017-a8de-f7f2919721de"
   },
   "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>chunk</th>\n",
       "      <th>complemetaria</th>\n",
       "      <th>consultar</th>\n",
       "      <th>coords</th>\n",
       "      <th>destino_id</th>\n",
       "      <th>directions</th>\n",
       "      <th>distancia</th>\n",
       "      <th>duracion_estimada</th>\n",
       "      <th>id</th>\n",
       "      <th>nid</th>\n",
       "      <th>origen_id</th>\n",
       "      <th>preexiste</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1to0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>(('-58.3897856456985', '-34.6427424928089'), (...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 7773.0, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0to1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2to0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>(('-58.3897856456985', '-34.6427424928089'), (...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 7162.0, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0to2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3to0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>(('-58.3897856456985', '-34.6427424928089'), (...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 5293.6, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0to3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4to0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>(('-58.3897856456985', '-34.6427424928089'), (...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 6481.4, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0to4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5to0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>(('-58.3897856456985', '-34.6427424928089'), (...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 8829.6, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0to5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7995</th>\n",
       "      <td>4.0</td>\n",
       "      <td>99to50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>((-58.4010767000307, -34.5837350914111), (-58....</td>\n",
       "      <td>99.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 4377.3, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50to99</td>\n",
       "      <td>8775.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7996</th>\n",
       "      <td>4.0</td>\n",
       "      <td>100to50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>((-58.4010767000307, -34.5837350914111), (-58....</td>\n",
       "      <td>100.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 4352.6, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50to100</td>\n",
       "      <td>8776.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7997</th>\n",
       "      <td>4.0</td>\n",
       "      <td>101to50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>((-58.4010767000307, -34.5837350914111), (-58....</td>\n",
       "      <td>101.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 5291.3, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50to101</td>\n",
       "      <td>8777.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7998</th>\n",
       "      <td>4.0</td>\n",
       "      <td>103to50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>((-58.4010767000307, -34.5837350914111), (-58....</td>\n",
       "      <td>103.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 1147.1, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50to103</td>\n",
       "      <td>8778.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7999</th>\n",
       "      <td>4.0</td>\n",
       "      <td>104to50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>((-58.4010767000307, -34.5837350914111), (-58....</td>\n",
       "      <td>104.0</td>\n",
       "      <td>{'routes': [{'summary': {'distance': 6829.0, '...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50to104</td>\n",
       "      <td>8779.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8000 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      chunk complemetaria  consultar  \\\n",
       "0       1.0          1to0        1.0   \n",
       "1       1.0          2to0        1.0   \n",
       "2       1.0          3to0        1.0   \n",
       "3       1.0          4to0        1.0   \n",
       "4       1.0          5to0        1.0   \n",
       "...     ...           ...        ...   \n",
       "7995    4.0        99to50        1.0   \n",
       "7996    4.0       100to50        1.0   \n",
       "7997    4.0       101to50        1.0   \n",
       "7998    4.0       103to50        1.0   \n",
       "7999    4.0       104to50        1.0   \n",
       "\n",
       "                                                 coords  destino_id  \\\n",
       "0     (('-58.3897856456985', '-34.6427424928089'), (...         1.0   \n",
       "1     (('-58.3897856456985', '-34.6427424928089'), (...         2.0   \n",
       "2     (('-58.3897856456985', '-34.6427424928089'), (...         3.0   \n",
       "3     (('-58.3897856456985', '-34.6427424928089'), (...         4.0   \n",
       "4     (('-58.3897856456985', '-34.6427424928089'), (...         5.0   \n",
       "...                                                 ...         ...   \n",
       "7995  ((-58.4010767000307, -34.5837350914111), (-58....        99.0   \n",
       "7996  ((-58.4010767000307, -34.5837350914111), (-58....       100.0   \n",
       "7997  ((-58.4010767000307, -34.5837350914111), (-58....       101.0   \n",
       "7998  ((-58.4010767000307, -34.5837350914111), (-58....       103.0   \n",
       "7999  ((-58.4010767000307, -34.5837350914111), (-58....       104.0   \n",
       "\n",
       "                                             directions  distancia  \\\n",
       "0     {'routes': [{'summary': {'distance': 7773.0, '...        0.0   \n",
       "1     {'routes': [{'summary': {'distance': 7162.0, '...        0.0   \n",
       "2     {'routes': [{'summary': {'distance': 5293.6, '...        0.0   \n",
       "3     {'routes': [{'summary': {'distance': 6481.4, '...        0.0   \n",
       "4     {'routes': [{'summary': {'distance': 8829.6, '...        0.0   \n",
       "...                                                 ...        ...   \n",
       "7995  {'routes': [{'summary': {'distance': 4377.3, '...        0.0   \n",
       "7996  {'routes': [{'summary': {'distance': 4352.6, '...        0.0   \n",
       "7997  {'routes': [{'summary': {'distance': 5291.3, '...        0.0   \n",
       "7998  {'routes': [{'summary': {'distance': 1147.1, '...        0.0   \n",
       "7999  {'routes': [{'summary': {'distance': 6829.0, '...        0.0   \n",
       "\n",
       "      duracion_estimada       id     nid  origen_id  preexiste  \n",
       "0                   0.0     0to1     1.0        0.0        0.0  \n",
       "1                   0.0     0to2     2.0        0.0        0.0  \n",
       "2                   0.0     0to3     3.0        0.0        0.0  \n",
       "3                   0.0     0to4     4.0        0.0        0.0  \n",
       "4                   0.0     0to5     5.0        0.0        0.0  \n",
       "...                 ...      ...     ...        ...        ...  \n",
       "7995                0.0   50to99  8775.0       50.0        0.0  \n",
       "7996                0.0  50to100  8776.0       50.0        0.0  \n",
       "7997                0.0  50to101  8777.0       50.0        0.0  \n",
       "7998                0.0  50to103  8778.0       50.0        0.0  \n",
       "7999                0.0  50to104  8779.0       50.0        0.0  \n",
       "\n",
       "[8000 rows x 12 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(rutas_calculadas.index))\n",
    "rutas_calculadas.to_csv(\n",
    "  derivados[\"rutas\"][\"local\"],\n",
    "  index = False,\n",
    ")\n",
    "rutas_calculadas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "M2NOYVXfmp7t"
   },
   "outputs": [],
   "source": [
    "rutas_df = pd.read_csv(\n",
    "  derivados[\"rutas\"][\"local\"],\n",
    ")\n",
    "# rutas_df\n",
    "print( len( rutas_df.index ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "YIkEOs4vhZdS"
   },
   "outputs": [],
   "source": [
    "# OBTENER DISTANCIAS (DIRECTAS NO CALCULA CAMINO URBANO) GEOPY \n",
    "import geopy\n",
    "distancias = pd.DataFrame()\n",
    "estaciones = [ x for x in derivados[\"estaciones\"][\"df\"].set_index('id').sort_values('id').iterrows() ]\n",
    "for o in estaciones:\n",
    "    print(cuenta_calls)\n",
    "    origen_id = int( o[0] )\n",
    "    origen_geocoords = ( o[1]['lat'], o[1]['lon'] )\n",
    "    # distancias[origen_id] = {}\n",
    "    for d in estaciones:\n",
    "        destino_id = int( d[0] )\n",
    "        destino_geocoords = ( d[1]['lat'], d[1]['lon'] ) \n",
    "        fila = {\n",
    "          \"id\"  : str(origen_id) + 'to' + str(destino_id),\n",
    "          \"origen_id\": origen_id,\n",
    "          \"destino_id\": destino_id,\n",
    "          \"distancia\" : 0,\n",
    "        }\n",
    "        if (\n",
    "            destino_id != origen_id\n",
    "        ): \n",
    "            geocoords = ( origen_coords, destino_coords )\n",
    "            distancia = round( \n",
    "                geopy.distance.vincenty( origen_geocoords, destino_geocoords ).km, \n",
    "                2\n",
    "            ) \n",
    "            fila[\"distancia\"] = distancia\n",
    "        distancias = distancias.append( fila, ignore_index = True )\n",
    "distancias['origen_id'] = distancias['origen_id'].astype(int)\n",
    "distancias.to_csv(\n",
    "  datadir + 'distancias.csv',\n",
    "  index = False,\n",
    ")\n",
    "\n",
    "distancias"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9qGLZBMUEVX7"
   },
   "source": [
    "### Distancias GEOPY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "id": "c3IKg3fML-j2"
   },
   "outputs": [],
   "source": [
    "# esto queda en pararacion\n",
    "# derivados[\"distancias\"][\"df\"] = pd.read_csv(\n",
    "#   derivados[\"distancias\"][\"local\"]\n",
    "# )\n",
    "derivados[\"distancias\"][\"df\"] = derivados[\"distancias\"][\"df\"].rename(\n",
    "    columns = { 'Unnamed: 0': 'id' }\n",
    ").set_index('id')\n",
    "# Hay que reshaping de matriz a lista \n",
    "distasncias_df = pd.DataFrame()\n",
    "for f in derivados[\"distancias\"][\"df\"].iterrows():\n",
    "    oid = int( f[0] )\n",
    "    fila = f[1]\n",
    "    for col in derivados[\"distancias\"][\"df\"].columns:\n",
    "        did = int( col )\n",
    "        distancia = fila[ col ]\n",
    "        trayecto = {\n",
    "            'origen_id'  : oid,\n",
    "            'destino_id' : did,\n",
    "            'distancia'  : distancia,\n",
    "        }\n",
    "        distasncias_df = distasncias_df.append(\n",
    "            trayecto,\n",
    "            ignore_index = True\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "destino_id    0\n",
       "distancia     0\n",
       "origen_id     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "distasncias_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "id": "c3IKg3fML-j2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 40000 entries, 0 to 39999\n",
      "Data columns (total 3 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   destino_id  40000 non-null  float64\n",
      " 1   distancia   40000 non-null  float64\n",
      " 2   origen_id   40000 non-null  float64\n",
      "dtypes: float64(3)\n",
      "memory usage: 937.6 KB\n"
     ]
    }
   ],
   "source": [
    "del derivados[\"distancias\"][\"df\"]\n",
    "distasncias_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# derivados[\"recorridos\"][\"df\"][\"distancia\"] = 0\n",
    "derivados[\"recorridos\"][\"df\"] = pd.merge(\n",
    "    left     = derivados[\"recorridos\"][\"df\"],\n",
    "    right    = distasncias_df,\n",
    "    how      = 'left', \n",
    "    left_on  = ['origen_id','destino_id'], \n",
    "    right_on = ['origen_id','destino_id']\n",
    ")\n",
    "del distasncias_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "rB0uzxlyIQ07",
    "outputId": "1884e070-1d38-4d30-b574-673e63e0c937"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.1956260703548574"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"][\"distancia\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion            0\n",
       "usuario_id          0\n",
       "origen_id           0\n",
       "origen_nombre       0\n",
       "origen_fecha        0\n",
       "origen_direccion    0\n",
       "destino_id          0\n",
       "destino_nombre      0\n",
       "destino_fecha       0\n",
       "fecha               0\n",
       "ndia                0\n",
       "wdia                0\n",
       "dhabil              0\n",
       "hora                0\n",
       "mins                0\n",
       "ddia                0\n",
       "trimestre           0\n",
       "feriado             0\n",
       "duracion_mins       0\n",
       "vuelta              0\n",
       "distancia           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"].drop( columns = { 'duracion' } )\n",
    "derivados[\"recorridos\"][\"df\"][\"distancia\"] = derivados[\"recorridos\"][\"df\"][\"distancia\"].fillna(0)\n",
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "jtq9THlZOR0p",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "3fe3c205-380b-4966-ce96-d15aa6ef0a80",
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1, 2, 3])"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# mean_dis  =  derivados[\"recorridos\"][\"df\"][\"distancia\"].mean()\n",
    "# bins = 4\n",
    "# dis_cortes  = [ int( ( i * 0.5 ) * mean_dis ) for i in range( 0, bins, 1 )  ]\n",
    "# dis_labels = [ dis_cortes[ i ] for i in range( 0, len( dis_cortes ) -1, 1 ) ]\n",
    "# derivados[\"recorridos\"][\"df\"][\"distancia_cat\"] = pd.cut( \n",
    "#     derivados[\"recorridos\"][\"df\"].distancia,\n",
    "# #     dis_cortes,\n",
    "# #     labels = dis_labels,\n",
    "#     right = True,\n",
    "#     include_lowest = True,\n",
    "#     ordered = True,\n",
    "#     bins = 4,\n",
    "#     labels = False,\n",
    "    \n",
    "# )\n",
    "# derivados[\"recorridos\"][\"df\"][\"distancia_cat\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 416
    },
    "id": "KjTSEytElUHU",
    "outputId": "34130ae4-4c6e-4860-98ce-eadb35e3fa2f"
   },
   "outputs": [],
   "source": [
    "# derivados[\"recorridos\"][\"df\"][ derivados[\"recorridos\"][\"df\"][\"vuelta\"] == 0 ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YTtJbfbtTbiy"
   },
   "source": [
    "### VELOCIDAD APROXIMADA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 661
    },
    "id": "EIWWWhMQBZca",
    "outputId": "0bee6228-7eaf-4328-ad22-8a99ffb8e56e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.12328109501919293"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].eval(\n",
    "    'velocidad_kmm = distancia / duracion_mins',\n",
    "    inplace = True\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"][\"velocidad_kmm\"].replace(\n",
    "    np.inf,\n",
    "    0,\n",
    "    inplace = True\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"][\"velocidad_kmm\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion            0\n",
       "usuario_id          0\n",
       "origen_id           0\n",
       "origen_nombre       0\n",
       "origen_fecha        0\n",
       "origen_direccion    0\n",
       "destino_id          0\n",
       "destino_nombre      0\n",
       "destino_fecha       0\n",
       "fecha               0\n",
       "ndia                0\n",
       "wdia                0\n",
       "dhabil              0\n",
       "hora                0\n",
       "mins                0\n",
       "ddia                0\n",
       "trimestre           0\n",
       "feriado             0\n",
       "duracion_mins       0\n",
       "vuelta              0\n",
       "distancia           0\n",
       "velocidad_kmm       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"][\"velocidad_kmm\"] = derivados[\"recorridos\"][\"df\"][\"velocidad_kmm\"].fillna(0)\n",
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "y-k3xiA_ZMVU",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "3ff53a9d-3563-4d42-f071-34786946319d",
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0., nan,  1.,  2.,  3.])"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# derivados[\"recorridos\"][\"df\"][\"velocidad_cat\"] = pd.cut( \n",
    "#     derivados[\"recorridos\"][\"df\"].velocidad_kmm,\n",
    "#     bins = 4,\n",
    "#     labels = False,\n",
    "# #     right = True,\n",
    "#     include_lowest = True,\n",
    "# )\n",
    "# derivados[\"recorridos\"][\"df\"][\"velocidad_cat\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YcPXmUXWfeC_"
   },
   "source": [
    "\n",
    "### CLIMA\n",
    "https://www.smn.gob.ar/descarga-de-datos\n",
    "https://www.smn.gob.ar/datos-abiertos-smn\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QRH8uscyPAEc"
   },
   "source": [
    "### Regular sequences: pd.date_range()\n",
    "\n",
    "Regular date sequences "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "id": "ebG5UuB9ON7t"
   },
   "outputs": [],
   "source": [
    "# Lista de dias a obtener\n",
    "primera_fecha  = derivados[\"recorridos\"][\"df\"][\"fecha\"].min().split('-') \n",
    "sdate = date( int(primera_fecha[0]), int(primera_fecha[1]), int(primera_fecha[2]))\n",
    "ultima_fecha  = derivados[\"recorridos\"][\"df\"][\"fecha\"].max().split('-') \n",
    "fdate = date( int(ultima_fecha[0]), int(ultima_fecha[1]), int(ultima_fecha[2]))\n",
    "dias = pd.date_range( sdate, fdate, freq = 'd' )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bnkpC-VrQXkU"
   },
   "source": [
    "### Observaciones Horarias\n",
    "\n",
    "```\n",
    "$ curl -v --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=observaciones/datohorario20210518.txt 2>&1 | grep AERO\n",
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=observaciones/datohorario20210518.txt | head -n 1\n",
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=observaciones/datohorario20210518.txt | grep AEROPARQUE\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "-MQ3Ya-4PHt_",
    "outputId": "3c21ae5a-256b-4854-acff-5f343d623e3a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FECHA     HORA  TEMP   HUM   PNM    DD    FF     NOMBRE                                             \n",
      "         [HOA]  [�C]   [%]  [hPa]  [gr] [km/hr]                                                     \n",
      "18052021     0  11.2   83  1021.8    0    0     AEROPARQUE AERO                                     \n",
      "18052021     1  10.8   82  1022.1    0    0     AEROPARQUE AERO                                     \n",
      "18052021     2  11.4   77  1021.8  160    6     AEROPARQUE AERO                                     \n",
      "18052021     3  10.8   82  1021.6  160    4     AEROPARQUE AERO                                     \n",
      "18052021     4  10.2   86  1021.2  990    4     AEROPARQUE AERO                                     \n",
      "18052021     5  10.2   86  1021.5  990    4     AEROPARQUE AERO                                     \n",
      "18052021     6  10.2   84  1021.5  250    4     AEROPARQUE AERO                                     \n",
      "18052021     7  10.1   81  1021.7  250    2     AEROPARQUE AERO                                     \n"
     ]
    }
   ],
   "source": [
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=observaciones/datohorario20210518.txt | head -n 10    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## REVISAR FALTAN ESTOS DIAS\n",
    "# ['2020-01-30', '2020-09-04', '2020-01-31', '2020-01-11',\n",
    "#  '2020-10-21', '2020-02-13', '2020-10-14', '2020-09-19',\n",
    "#  '2020-02-11', '2020-12-14', '2020-11-03', '2020-02-15',\n",
    "#  '2020-10-19', '2020-09-02', '2021-02-14', '2021-01-28'],\n",
    "\n",
    "\"\"\" Obtener datos horarios para cada dia  \"\"\"\n",
    "dh_formato = '%d%m%Y%H'\n",
    "prefijo = 'https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=observaciones/datohorario'\n",
    "sufijo = '.txt'\n",
    "for i, d in enumerate(dias):\n",
    "    dstr = d.strftime('%Y%m%d') \n",
    "    dato_horario = prefijo + dstr + sufijo\n",
    "    req = urllib.request.Request( dato_horario )\n",
    "    response = urllib.request.urlopen( req )\n",
    "    lines = response.readlines()\n",
    "    fecha = d.strftime( '%Y-%m-%d' )\n",
    "    print( i, 'procesando', dato_horario )\n",
    "    # encabezados = lines[0].decode(\"utf-8\").split()  \n",
    "    # ['FECHA', 'HORA', 'TEMP', 'HUM', 'PNM', 'DD', 'FF', 'NOMBRE']\n",
    "    # [b'[HOA]', b'[\\xbaC]', b'[%]', b'[hPa]', b'[gr]', b'[km/hr]']\n",
    "    # fstl = lines[2].decode(\"utf-8\").split()  \n",
    "    # print( encabezados )\n",
    "    # print( fstl )\n",
    "    for l in lines:\n",
    "        if b'AEROPARQUE' in l:\n",
    "            row =  l.decode(\"utf-8\").split()\n",
    "            # dh = str(row[0]) + str(row[1])\n",
    "            # dh_dt = pd.to_datetime(\n",
    "            #   dh, \n",
    "            #   format = dh_formato\n",
    "            # ) \n",
    "            dh_dic = {\n",
    "                # 'DH_DT'   : dh_dt,\n",
    "                'FECHA'   : fecha,\n",
    "                'HORA'    : row[1],\n",
    "                'TEMP'    : row[2], \n",
    "                'HUM'     : row[3], \n",
    "                'PNM'     : row[4], \n",
    "                'DD'      : row[5], \n",
    "                'FF'      : row[6], \n",
    "                # 'NOMBRE'  : row[7]\n",
    "            }\n",
    "            derivados[\"clima\"][\"df\"] = derivados[\"clima\"][\"df\"].append(\n",
    "                dh_dic,\n",
    "                ignore_index=True\n",
    "            )\n",
    "            \n",
    "derivados[\"clima\"][\"df\"]['DD'] = derivados[\"clima\"][\"df\"]['DD'].replace('AEROPARQUE',170)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].replace( 'AERO', 13)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].replace( 'AEROPARQUE', 13)\n",
    "derivados[\"clima\"][\"df\"]['HORA'] = derivados[\"clima\"][\"df\"]['HORA'].astype(str).astype(int)\n",
    "derivados[\"clima\"][\"df\"]['HUM'] = derivados[\"clima\"][\"df\"]['HUM'].astype(float).astype(int).astype(int)\n",
    "derivados[\"clima\"][\"df\"]['DD'] = derivados[\"clima\"][\"df\"]['DD'].astype(str).astype(int)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].astype(str).astype(int)\n",
    "derivados[\"clima\"][\"df\"]['TEMP'] = derivados[\"clima\"][\"df\"]['TEMP'].astype(str).astype(float)\n",
    "derivados[\"clima\"][\"df\"]['PNM'] = derivados[\"clima\"][\"df\"]['PNM'].astype(str).astype(float)\n",
    "# derivados[\"clima\"][\"df\"].isnull().sum()\n",
    "derivados[\"clima\"][\"df\"].to_csv(\n",
    "    derivados[\"clima\"][\"local\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "derivados[\"clima\"][\"df\"].isnull().sum()\n",
    "# derivados[\"clima\"][\"df\"].to_csv(\n",
    "#     derivados[\"clima\"][\"local\"],\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# derivados[\"clima\"][\"df\"].isnull().sum()\n",
    "# # derivados[\"clima\"][\"df\"].info()\n",
    "derivados[\"clima\"][\"df\"].to_csv(\n",
    "    derivados[\"clima\"][\"local\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gM8Ne3s5PK3A"
   },
   "source": [
    "#### Pronostico\n",
    "\n",
    "```\n",
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron20210101.txt\n",
    "curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron20210101.txt | head -n 52\n",
    "curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron20210101.txt | head -n 18\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron20210101.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0VBU9y7FyqZc",
    "outputId": "c0736369-bf9b-4a22-9638-f0ffa5a5fad9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "01/ENE/2021 00Hs. 0.0\n",
      "01/ENE/2021 03Hs. 0.0\n",
      "01/ENE/2021 06Hs. 0.0\n",
      "01/ENE/2021 09Hs. 0.0\n",
      "01/ENE/2021 12Hs. 0.0\n",
      "01/ENE/2021 15Hs. 0.0\n",
      "01/ENE/2021 18Hs. 0.0\n",
      "01/ENE/2021 21Hs. 0.0\n"
     ]
    }
   ],
   "source": [
    "# AEROPARQUE 1 DIA 8 OBSERVACIONES c / 3 horas\n",
    "!curl --silent https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron20210101.txt | head -n 18 | tail -n 8 | awk '/1/ -F \":\" {print $1,$2,$7}' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "uWkS7yv0UZSk"
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Obtener Pronostico # AEROPARQUE 1 DIA 8 OBSERVACIONES c / 3 horas\n",
    "upsamplig\n",
    "\"\"\"\n",
    "\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.resample.html\n",
    "\n",
    "# https://machinelearningmastery.com/resample-interpolate-time-series-data-python/\n",
    "# https://towardsdatascience.com/upsample-with-an-average-in-pandas-c029032c57ca\n",
    "\n",
    "# from pandas import datetime\n",
    " \n",
    "# def parser(x):\n",
    "# \treturn datetime.strptime('190'+x, '%Y-%m')\n",
    " \n",
    "# series = read_csv('shampoo-sales.csv', header=0, parse_dates=[0], index_col=0, squeeze=True, date_parser=parser)\n",
    "# upsampled = series.resample('D')\n",
    "# print(upsampled.head(32))\n",
    "\n",
    "dh_formato = '%d%m%Y%H'\n",
    "prefijo = 'https://ssl.smn.gob.ar/dpd/descarga_opendata.php?file=pron5d/pron'\n",
    "sufijo = '.txt'\n",
    "for i, d in enumerate(dias):\n",
    "    dstr = d.strftime('%Y%m%d') \n",
    "    dato_horario = prefijo + dstr + sufijo\n",
    "    req = urllib.request.Request( dato_horario )\n",
    "    response = urllib.request.urlopen( req )\n",
    "    lines = response.readlines()\n",
    "    fecha = d.strftime( '%Y-%m-%d' )\n",
    "    print( i, 'procesando', dato_horario )\n",
    "    # encabezados = lines[0].decode(\"utf-8\").split()  \n",
    "    # ['FECHA', 'HORA', 'TEMP', 'HUM', 'PNM', 'DD', 'FF', 'NOMBRE']\n",
    "    # [b'[HOA]', b'[\\xbaC]', b'[%]', b'[hPa]', b'[gr]', b'[km/hr]']\n",
    "    # fstl = lines[2].decode(\"utf-8\").split()  \n",
    "    # print( encabezados )\n",
    "    # print( fstl )\n",
    "    for l in lines:\n",
    "        if b'AEROPARQUE' in l:\n",
    "            row =  l.decode(\"utf-8\").split()\n",
    "            # dh = str(row[0]) + str(row[1])\n",
    "            # dh_dt = pd.to_datetime(\n",
    "            #   dh, \n",
    "            #   format = dh_formato\n",
    "            # ) \n",
    "            dh_dic = {\n",
    "                # 'DH_DT'   : dh_dt,\n",
    "                'FECHA'   : fecha,\n",
    "                'HORA'    : row[1],\n",
    "#                 'TEMP'    : row[2], \n",
    "#                 'HUM'     : row[3], \n",
    "#                 'PNM'     : row[4], \n",
    "#                 'DD'      : row[5], \n",
    "#                 'FF'      : row[6], \n",
    "                # 'NOMBRE'  : row[7]\n",
    "            }\n",
    "            derivados[\"clima\"][\"df\"] = derivados[\"clima\"][\"df\"].append(\n",
    "                dh_dic,\n",
    "                ignore_index=True\n",
    "            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "TGhOPPcUQu-Y",
    "outputId": "293ca0d7-4e23-46c6-ecf1-1ba1c9ad8c7d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 12411 entries, 0 to 12410\n",
      "Data columns (total 7 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   DD      12411 non-null  int64  \n",
      " 1   FECHA   12411 non-null  object \n",
      " 2   FF      12411 non-null  int64  \n",
      " 3   HORA    12411 non-null  int64  \n",
      " 4   HUM     12411 non-null  int64  \n",
      " 5   PNM     12411 non-null  float64\n",
      " 6   TEMP    12411 non-null  float64\n",
      "dtypes: float64(2), int64(4), object(1)\n",
      "memory usage: 678.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# CARGAR CLIMA\n",
    "derivados[\"clima\"][\"df\"] = pd.read_csv(\n",
    "  derivados[\"clima\"][\"local\"],\n",
    ").drop(columns = ['Unnamed: 0'])\n",
    "# ver fechas y horas q esten todas\n",
    "# ['2021-02-12', '2021-02-11']\n",
    "derivados[\"clima\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "derivados[\"clima\"][\"df\"]['DD'] = derivados[\"clima\"][\"df\"]['DD'].replace('AEROPARQUE',170)\n",
    "derivados[\"clima\"][\"df\"]['DD'] = derivados[\"clima\"][\"df\"]['DD'].astype(str).astype(int)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].replace( 'AERO', 13)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].replace( 'AEROPARQUE', 13)\n",
    "derivados[\"clima\"][\"df\"]['FF'] = derivados[\"clima\"][\"df\"]['FF'].astype(str).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "id": "VlVO1Z9jgU_W",
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DD       0\n",
       "FECHA    0\n",
       "FF       0\n",
       "HORA     0\n",
       "HUM      0\n",
       "PNM      0\n",
       "TEMP     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# formato = '%Y-%m-%d'\n",
    "# derivados[\"clima\"][\"df\"]['DATE'] = pd.to_datetime(\n",
    "#   derivados[\"clima\"][\"df\"]['DATE'], \n",
    "#   format = formato\n",
    "# )\n",
    "# # derivados[\"clima\"][\"df\"]['TAVG'].unique()\n",
    "# derivados[\"clima\"][\"df\"]['TEMP'].unique()\n",
    "# derivados[\"clima\"][\"df\"] = derivados[\"clima\"][\"df\"].rename(columns=[])\n",
    "# derivados[\"clima\"][\"df\"].info()\n",
    "\n",
    "# derivados[\"clima\"][\"df\"].info()\n",
    "derivados[\"clima\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 609
    },
    "id": "maHFL4gLUBIF",
    "outputId": "c01401ea-7354-4427-b38e-1cd9870cf65e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3331632 entries, 0 to 3331631\n",
      "Data columns (total 29 columns):\n",
      " #   Column            Dtype  \n",
      "---  ------            -----  \n",
      " 0   duracion          int64  \n",
      " 1   usuario_id        int64  \n",
      " 2   origen_id         int64  \n",
      " 3   origen_nombre     object \n",
      " 4   origen_fecha      object \n",
      " 5   origen_direccion  object \n",
      " 6   destino_id        int64  \n",
      " 7   destino_nombre    object \n",
      " 8   destino_fecha     object \n",
      " 9   fecha             object \n",
      " 10  ndia              int64  \n",
      " 11  wdia              int64  \n",
      " 12  dhabil            int64  \n",
      " 13  hora              int64  \n",
      " 14  mins              int64  \n",
      " 15  ddia              int64  \n",
      " 16  trimestre         int64  \n",
      " 17  feriado           int64  \n",
      " 18  duracion_mins     int64  \n",
      " 19  vuelta            int64  \n",
      " 20  distancia         float64\n",
      " 21  velocidad_kmm     float64\n",
      " 22  DD                float64\n",
      " 23  FECHA             object \n",
      " 24  FF                float64\n",
      " 25  HORA              float64\n",
      " 26  HUM               float64\n",
      " 27  PNM               float64\n",
      " 28  TEMP              float64\n",
      "dtypes: float64(8), int64(14), object(7)\n",
      "memory usage: 762.6+ MB\n"
     ]
    }
   ],
   "source": [
    "# https://stackoverflow.com/a/56842359\n",
    "# df3 = df1.drop('b', 1).merge(df2, 'left', on='a').set_index(df1.index)\n",
    "# df1.combine_first(df3)\n",
    "# df1.update( df1[ ['a', 'e'] ].merge( df2, 'left' ) )\n",
    "\n",
    "derivados[\"recorridos\"][\"df\"] = pd.merge(\n",
    "    left     = derivados[\"recorridos\"][\"df\"],\n",
    "    right    = derivados[\"clima\"][\"df\"],\n",
    "    how      = 'left', \n",
    "    left_on  = ['fecha','hora'], \n",
    "    right_on = ['FECHA','HORA']\n",
    ")\n",
    "del derivados[\"clima\"][\"df\"]\n",
    "derivados[\"recorridos\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion                0\n",
       "usuario_id              0\n",
       "origen_id               0\n",
       "origen_nombre           0\n",
       "origen_fecha            0\n",
       "origen_direccion        0\n",
       "destino_id              0\n",
       "destino_nombre          0\n",
       "destino_fecha           0\n",
       "fecha                   0\n",
       "ndia                    0\n",
       "wdia                    0\n",
       "dhabil                  0\n",
       "hora                    0\n",
       "mins                    0\n",
       "ddia                    0\n",
       "trimestre               0\n",
       "feriado                 0\n",
       "duracion_mins           0\n",
       "vuelta                  0\n",
       "distancia               0\n",
       "velocidad_kmm           0\n",
       "DD                  14350\n",
       "FECHA               14350\n",
       "FF                  14350\n",
       "HORA                14350\n",
       "HUM                 14350\n",
       "PNM                 14350\n",
       "TEMP                14350\n",
       "dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1613 entries, 0 to 1612\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   DATE    1613 non-null   object \n",
      " 1   PRCP    356 non-null    float64\n",
      " 2   TAVG    1613 non-null   float64\n",
      "dtypes: float64(2), object(1)\n",
      "memory usage: 37.9+ KB\n"
     ]
    }
   ],
   "source": [
    "insumos[\"clima\"][\"df\"] = pd.read_csv(\n",
    "    insumos[\"clima\"][\"local\"]\n",
    ")\n",
    "insumos[\"clima\"][\"df\"] = insumos[\"clima\"][\"df\"].drop( columns = { \n",
    "    'STATION','NAME','LATITUDE','LONGITUDE','ELEVATION','TMAX','TMIN',\n",
    "    'PRCP_ATTRIBUTES','TAVG_ATTRIBUTES','TMAX_ATTRIBUTES','TMIN_ATTRIBUTES'\n",
    "})\n",
    "insumos[\"clima\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>PRCP</th>\n",
       "      <th>TAVG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>356.000000</td>\n",
       "      <td>1613.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.920225</td>\n",
       "      <td>18.632424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>16.342953</td>\n",
       "      <td>5.202943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>14.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.300000</td>\n",
       "      <td>18.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.900000</td>\n",
       "      <td>22.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>112.000000</td>\n",
       "      <td>30.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             PRCP         TAVG\n",
       "count  356.000000  1613.000000\n",
       "mean     7.920225    18.632424\n",
       "std     16.342953     5.202943\n",
       "min      0.000000     5.900000\n",
       "25%      0.000000    14.400000\n",
       "50%      0.300000    18.900000\n",
       "75%      7.900000    22.700000\n",
       "max    112.000000    30.400000"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insumos[\"clima\"][\"df\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# REVISAR\n",
    "# array(['2021-02-12', '2021-02-11'], dtype=object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DATE    0\n",
       "PRCP    0\n",
       "TAVG    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# REVISAR\n",
    "insumos[\"clima\"][\"df\"]['PRCP'] = insumos[\"clima\"][\"df\"]['PRCP'].fillna(0)\n",
    "insumos[\"clima\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "id": "K8XFs-IY6kBG"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3331632 entries, 0 to 3331631\n",
      "Data columns (total 32 columns):\n",
      " #   Column            Dtype  \n",
      "---  ------            -----  \n",
      " 0   duracion          int64  \n",
      " 1   usuario_id        int64  \n",
      " 2   origen_id         int64  \n",
      " 3   origen_nombre     object \n",
      " 4   origen_fecha      object \n",
      " 5   origen_direccion  object \n",
      " 6   destino_id        int64  \n",
      " 7   destino_nombre    object \n",
      " 8   destino_fecha     object \n",
      " 9   fecha             object \n",
      " 10  ndia              int64  \n",
      " 11  wdia              int64  \n",
      " 12  dhabil            int64  \n",
      " 13  hora              int64  \n",
      " 14  mins              int64  \n",
      " 15  ddia              int64  \n",
      " 16  trimestre         int64  \n",
      " 17  feriado           int64  \n",
      " 18  duracion_mins     int64  \n",
      " 19  vuelta            int64  \n",
      " 20  distancia         float64\n",
      " 21  velocidad_kmm     float64\n",
      " 22  DD                float64\n",
      " 23  FECHA             object \n",
      " 24  FF                float64\n",
      " 25  HORA              float64\n",
      " 26  HUM               float64\n",
      " 27  PNM               float64\n",
      " 28  TEMP              float64\n",
      " 29  DATE              object \n",
      " 30  PRCP              float64\n",
      " 31  TAVG              float64\n",
      "dtypes: float64(10), int64(14), object(8)\n",
      "memory usage: 838.8+ MB\n"
     ]
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"] = pd.merge(\n",
    "    left     = derivados[\"recorridos\"][\"df\"],\n",
    "    right    = insumos[\"clima\"][\"df\"],\n",
    "    how      = 'left', \n",
    "    left_on  = 'fecha', \n",
    "    right_on = 'DATE'\n",
    ")\n",
    "del insumos[\"clima\"][\"df\"]\n",
    "derivados[\"recorridos\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3331632 entries, 0 to 3331631\n",
      "Data columns (total 29 columns):\n",
      " #   Column            Dtype  \n",
      "---  ------            -----  \n",
      " 0   duracion          int64  \n",
      " 1   usuario_id        int64  \n",
      " 2   origen_id         int64  \n",
      " 3   origen_nombre     object \n",
      " 4   origen_fecha      object \n",
      " 5   origen_direccion  object \n",
      " 6   destino_id        int64  \n",
      " 7   destino_nombre    object \n",
      " 8   destino_fecha     object \n",
      " 9   fecha             object \n",
      " 10  ndia              int64  \n",
      " 11  wdia              int64  \n",
      " 12  dhabil            int64  \n",
      " 13  hora              int64  \n",
      " 14  mins              int64  \n",
      " 15  ddia              int64  \n",
      " 16  trimestre         int64  \n",
      " 17  feriado           int64  \n",
      " 18  duracion_mins     int64  \n",
      " 19  vuelta            int64  \n",
      " 20  distancia         float64\n",
      " 21  velocidad_kmm     float64\n",
      " 22  DD                float64\n",
      " 23  FF                float64\n",
      " 24  HUM               float64\n",
      " 25  PNM               float64\n",
      " 26  TEMP              float64\n",
      " 27  PRCP              float64\n",
      " 28  TAVG              float64\n",
      "dtypes: float64(9), int64(14), object(6)\n",
      "memory usage: 762.6+ MB\n"
     ]
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"] = derivados[\"recorridos\"][\"df\"].drop( columns = { 'FECHA','HORA', 'DATE'} )\n",
    "derivados[\"recorridos\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion                0\n",
       "usuario_id              0\n",
       "origen_id               0\n",
       "origen_nombre           0\n",
       "origen_fecha            0\n",
       "origen_direccion        0\n",
       "destino_id              0\n",
       "destino_nombre          0\n",
       "destino_fecha           0\n",
       "fecha                   0\n",
       "ndia                    0\n",
       "wdia                    0\n",
       "dhabil                  0\n",
       "hora                    0\n",
       "mins                    0\n",
       "ddia                    0\n",
       "trimestre               0\n",
       "feriado                 0\n",
       "duracion_mins           0\n",
       "vuelta                  0\n",
       "distancia               0\n",
       "velocidad_kmm           0\n",
       "DD                  14350\n",
       "FF                  14350\n",
       "HUM                 14350\n",
       "PNM                 14350\n",
       "TEMP                14350\n",
       "PRCP                21005\n",
       "TAVG                21005\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 416
    },
    "id": "1ncamsX_60Yy",
    "jupyter": {
     "source_hidden": true
    },
    "outputId": "04fa455a-45dd-42ba-d3f6-a81c0283d3f9",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DD 14350 float64 156.64104709819665\n",
      "FF 14350 float64 14.931178295966397\n",
      "HUM 14350 float64 64.6412668564204\n",
      "PNM 14350 float64 1015.1488488165911\n",
      "TEMP 14350 float64 20.561729029970913\n",
      "PRCP 2885453 float64 6.71009191378348\n",
      "TAVG 21005 float64 19.582630359747565\n",
      "TMAX 1623205 float64 24.54931805690263\n",
      "TMIN 459725 float64 15.440816467942744\n"
     ]
    }
   ],
   "source": [
    "# FILL NA CON PROMEDIO\n",
    "# for c in derivados[\"recorridos\"][\"df\"].columns:\n",
    "#     nulls = derivados[\"recorridos\"][\"df\"][c].isnull().sum()\n",
    "#     if nulls:\n",
    "#         mean = derivados[\"recorridos\"][\"df\"][c].mean()\n",
    "#         fill = mean # or 0\n",
    "#         types = derivados[\"recorridos\"][\"df\"][c].dtypes\n",
    "#         print( c, nulls, types, fill)\n",
    "#         derivados[\"recorridos\"][\"df\"][c].fillna( mean, inplace = True)\n",
    "# derivados[\"recorridos\"][\"df\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duracion            0\n",
       "usuario_id          0\n",
       "origen_id           0\n",
       "origen_nombre       0\n",
       "origen_fecha        0\n",
       "origen_direccion    0\n",
       "destino_id          0\n",
       "destino_nombre      0\n",
       "destino_fecha       0\n",
       "fecha               0\n",
       "ndia                0\n",
       "wdia                0\n",
       "dhabil              0\n",
       "hora                0\n",
       "mins                0\n",
       "ddia                0\n",
       "trimestre           0\n",
       "feriado             0\n",
       "duracion_mins       0\n",
       "vuelta              0\n",
       "distancia           0\n",
       "velocidad_kmm       0\n",
       "DD                  0\n",
       "FF                  0\n",
       "HUM                 0\n",
       "PNM                 0\n",
       "TEMP                0\n",
       "PRCP                0\n",
       "TAVG                0\n",
       "TMAX                0\n",
       "TMIN                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "id": "OJZmd9NIu_bp"
   },
   "outputs": [],
   "source": [
    "# recorridos_clima \n",
    "# derivados[\"recorridos\"][\"df\"].to_csv(\n",
    "#   derivados[\"recorridos\"][\"local\"],\n",
    "#   index = False,\n",
    "# )\n",
    "\n",
    "compression_options = dict(\n",
    "    method ='zip',\n",
    "    archive_name = 'recorridos_clima_2020-2021.csv'\n",
    ")\n",
    "derivados[\"recorridos\"][\"df\"].to_csv(\n",
    "    datadir + 'recorridos.zip', \n",
    "    compression = compression_options,\n",
    "    index = False,\n",
    "\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Bs_UQJzcvRMY",
    "outputId": "496def1c-788c-49b2-c667-589adbc5fa5d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3331632 entries, 0 to 3331631\n",
      "Data columns (total 34 columns):\n",
      " #   Column            Dtype  \n",
      "---  ------            -----  \n",
      " 0   duracion          int64  \n",
      " 1   usuario_id        int64  \n",
      " 2   origen_id         int64  \n",
      " 3   origen_nombre     object \n",
      " 4   origen_fecha      object \n",
      " 5   origen_direccion  object \n",
      " 6   destino_id        int64  \n",
      " 7   destino_nombre    object \n",
      " 8   destino_fecha     object \n",
      " 9   fecha             object \n",
      " 10  ndia              int64  \n",
      " 11  wdia              int64  \n",
      " 12  dhabil            int64  \n",
      " 13  hora              int64  \n",
      " 14  mins              int64  \n",
      " 15  ddia              int64  \n",
      " 16  trimestre         int64  \n",
      " 17  feriado           int64  \n",
      " 18  duracion_mins     int64  \n",
      " 19  duracion_cat      int64  \n",
      " 20  vuelta            int64  \n",
      " 21  distancia         float64\n",
      " 22  distancia_cat     int64  \n",
      " 23  velocidad_kmm     float64\n",
      " 24  velocidad_cat     float64\n",
      " 25  DD                float64\n",
      " 26  FF                float64\n",
      " 27  HUM               float64\n",
      " 28  PNM               float64\n",
      " 29  TEMP              float64\n",
      " 30  PRCP              float64\n",
      " 31  TAVG              float64\n",
      " 32  TMAX              float64\n",
      " 33  TMIN              float64\n",
      "dtypes: float64(12), int64(16), object(6)\n",
      "memory usage: 889.6+ MB\n"
     ]
    }
   ],
   "source": [
    "derivados[\"recorridos\"][\"df\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lH-MAl2s_2vH",
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# GRUPING USERS\n",
    "usurios = derivados[\"recorridos\"][\"df\"].groupby(\n",
    "    ['usuario_id']\n",
    ").agg(\n",
    "  salidas = ( 'destino_id',  'count'),\n",
    "  vueltas = ( 'vuelta',  'sum'),\n",
    "  velocidad = ( 'velocidad_kmm',  'max'),\n",
    "  distancia = ( 'distancia',  'max')\n",
    ")\n",
    "# usurios = usurios.pivot_table(\n",
    "#     index   = [ 'usuario_id'],\n",
    "#     columns = \"salidas\",\n",
    "#     values  = \"salidas\",\n",
    "#     # aggfunc = 'count',\n",
    "# ).sort_values('origen_id')\n",
    "# demanda_diaria # comprobar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "id": "TfufCc7Mhp-u",
    "outputId": "2a108e37-78b3-4692-9ac5-37f4da7b8b13"
   },
   "outputs": [],
   "source": [
    "usurios = usurios[ ~usurios.isin([np.nan, np.inf, -np.inf]).any(1) ]\n",
    "# usurios\n",
    "usurios.sort_values( 'velocidad' , ascending = False).head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "oBIIBMQ0kIW-"
   },
   "source": [
    "# Analisis Exploratorio de Datos Espaciales\n",
    "(Exploratory Spatial Data Analysis ESDA)\n",
    "\n",
    "https://alcidanalytics.com/p/geographic-heatmap-in-python\n",
    "\n",
    "https://alysivji.github.io/getting-started-with-folium.html\n",
    "\n",
    "https://medium.com/analytics-vidhya/measure-driving-distance-time-and-plot-routes-between-two-geographical-locations-using-python-39995dfea7e"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KPYYFuVL485g"
   },
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 450
    },
    "id": "V-oVPAu_3EhD",
    "outputId": "63688bb5-7a62-45e6-9143-95ffab465b6c"
   },
   "outputs": [],
   "source": [
    "demanda_viz = derivados[\"recorridos\"][\"df\"]\n",
    "# Fitro x dia\n",
    "# DIA DE MAS DEMANDA 2021-03-05 14061\n",
    "fecha_viz   = '2020-03-05'\n",
    "demanda_viz = demanda_viz[ demanda_viz[\"fecha\"] == fecha_viz ]\n",
    "demanda_viz = demanda_viz.groupby(\n",
    "    ['origen_nombre', 'origen_id', 'hora', 'origen_lon','origen_lat']\n",
    ").agg(\n",
    "  salidas = ( 'hora',  'count' ),\n",
    "  nombre  = ( 'origen_nombre',  'first' ),\n",
    "  id      = ( 'origen_id',  'first' ),\n",
    "  lon     = ( 'origen_lon',  'first' ),\n",
    "  lat     = ( 'origen_lat',  'first' ),\n",
    ")\n",
    "# ).reset_index()\n",
    "# df.set_index(['id', 'date', 'location'], append=True)\n",
    "demanda_viz = demanda_viz.pivot_table(\n",
    "    index = ['nombre',  'id', 'lon','lat'],\n",
    "    columns = \"hora\",\n",
    "    values = \"salidas\",\n",
    "    fill_value = 0,\n",
    "    # aggfunc = 'sum',\n",
    ").sort_values('id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 428
    },
    "id": "XLNcQNqGkVju",
    "outputId": "cc3189be-d4b9-47bc-a0df-5a4d14537519"
   },
   "outputs": [],
   "source": [
    "#... where m is my map object. And 5 is the time (seconds) to render the map.\n",
    "# https://stackoverflow.com/questions/53565979/export-a-folium-map-as-a-png\n",
    "# https://blog.ouseful.info/2015/12/15/grabbing-screenshots-of-folium-produced-choropleth-leaflet-maps-using-selenium/\n",
    "# from time import sleep\n",
    "\n",
    "import io\n",
    "from PIL import Image\n",
    "import folium \n",
    "from folium.features import DivIcon\n",
    "import selenium\n",
    "\n",
    "import math\n",
    "def mapear(x): \n",
    "    return int( (x / 10 ) * 255)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 428
    },
    "id": "VyR8wcwi9GJU",
    "outputId": "00852d89-349e-45fd-8972-54389d4b9eb9"
   },
   "outputs": [],
   "source": [
    "# del m\n",
    "m = ''\n",
    "for h in demanda_viz.columns.to_list():\n",
    "    del m\n",
    "    m = folium.Map(\n",
    "#         [ -34.606499,-58.381094 ], # obelisco\n",
    "        [-34.606739, -58.435539 ], # parque centenario\n",
    "        zoom_start = 13,\n",
    "        zoom_control=False,\n",
    "        scrollWheelZoom=False,\n",
    "        dragging=False\n",
    "#         tiles='stamentoner'\n",
    "    )\n",
    "    folium.map.Marker(\n",
    "        [ -34.559920, -58.372211 ],\n",
    "        icon = DivIcon(\n",
    "            icon_size=(600,400),\n",
    "            icon_anchor=(0,0),\n",
    "            html='<div style=\"font-size: 20pt;font-family: monospace;\">' \\\n",
    "            + fecha_viz + ' HORA:' + \"{:02d}\".format( h ) \\\n",
    "            + '</div>',\n",
    "        )\n",
    "    ).add_to(m)\n",
    "    demandas = demanda_viz[h]\n",
    "    estaciones = demanda_viz[h].reset_index()\n",
    "    for e in estaciones.iterrows():\n",
    "        estacion = e[1]\n",
    "        folium.CircleMarker(\n",
    "            [ estacion['lat'], estacion['lon'] ],\n",
    "            radius = estacion[h] * 5, \n",
    "            popup = \"{0}\\:{1}\\demanda:{2}\".format(\n",
    "                estacion['id'],\n",
    "                estacion['nombre'],\n",
    "                str(estacion[h]),\n",
    "            ),\n",
    "            fill_color = \"#{0:02x}{1:02x}{2:02x}\".format(\n",
    "                mapear( int(estacion[h]) ),\n",
    "                255 - mapear( int(estacion[h]) ) ,\n",
    "                255 - mapear( int(estacion[h]) )\n",
    "            )\n",
    "        ).add_to(m)\n",
    "#     m.save( 'ESDA/' + fecha_viz + '_' + \"{:02d}\".format( h ) + '_' + 'map.html')\n",
    "    img_data = m._to_png(5)\n",
    "    img = Image.open(io.BytesIO(img_data))\n",
    "    img.save( 'ESDA/' + fecha_viz + '_' + \"{:02d}\".format( h ) + '_' + 'map.png' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !convert -density 72 'ESDA/2020-03-01_00_map.html' '2020-03-01_00_map.html'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ffmpeg version n4.4 Copyright (c) 2000-2021 the FFmpeg developers\n",
      "  built with gcc 11.1.0 (GCC)\n",
      "  configuration: --prefix=/usr --disable-debug --disable-static --disable-stripping --enable-amf --enable-avisynth --enable-cuda-llvm --enable-lto --enable-fontconfig --enable-gmp --enable-gnutls --enable-gpl --enable-ladspa --enable-libaom --enable-libass --enable-libbluray --enable-libdav1d --enable-libdrm --enable-libfreetype --enable-libfribidi --enable-libgsm --enable-libiec61883 --enable-libjack --enable-libmfx --enable-libmodplug --enable-libmp3lame --enable-libopencore_amrnb --enable-libopencore_amrwb --enable-libopenjpeg --enable-libopus --enable-libpulse --enable-librav1e --enable-librsvg --enable-libsoxr --enable-libspeex --enable-libsrt --enable-libssh --enable-libsvtav1 --enable-libtheora --enable-libv4l2 --enable-libvidstab --enable-libvmaf --enable-libvorbis --enable-libvpx --enable-libwebp --enable-libx264 --enable-libx265 --enable-libxcb --enable-libxml2 --enable-libxvid --enable-libzimg --enable-nvdec --enable-nvenc --enable-shared --enable-version3\n",
      "  libavutil      56. 70.100 / 56. 70.100\n",
      "  libavcodec     58.134.100 / 58.134.100\n",
      "  libavformat    58. 76.100 / 58. 76.100\n",
      "  libavdevice    58. 13.100 / 58. 13.100\n",
      "  libavfilter     7.110.100 /  7.110.100\n",
      "  libswscale      5.  9.100 /  5.  9.100\n",
      "  libswresample   3.  9.100 /  3.  9.100\n",
      "  libpostproc    55.  9.100 / 55.  9.100\n",
      "Input #0, image2, from './ESDA/*.png':\n",
      "  Duration: 00:00:00.96, start: 0.000000, bitrate: N/A\n",
      "  Stream #0:0: Video: png, rgba(pc), 1366x683, 25 fps, 25 tbr, 25 tbn, 25 tbc\n",
      "Stream mapping:\n",
      "  Stream #0:0 -> #0:0 (png (native) -> h264 (libx264))\n",
      "Press [q] to stop, [?] for help\n",
      "\u001b[1;32m[Parsed_framerate_1 @ 0x55cd874c1cc0] \u001b[0mtime base:1/5 -> 1/25 exact:1\n",
      "\u001b[1;32m[Parsed_framerate_1 @ 0x55cd874c1cc0] \u001b[0mfps -> fps:25/1 scene score:8.200000 interpolate start:15 end:240\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0musing cpu capabilities: MMX2 SSE2Fast SSSE3 SSE4.2 AVX\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mprofile High 4:4:4 Predictive, level 3.2, 4:4:4, 8-bit\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0m264 - core 161 r3039 544c61f - H.264/MPEG-4 AVC codec - Copyleft 2003-2021 - http://www.videolan.org/x264.html - options: cabac=1 ref=3 deblock=1:0:0 analyse=0x3:0x113 me=hex subme=7 psy=1 psy_rd=1.00:0.00 mixed_ref=1 me_range=16 chroma_me=1 trellis=1 8x8dct=1 cqm=0 deadzone=21,11 fast_pskip=1 chroma_qp_offset=4 threads=6 lookahead_threads=1 sliced_threads=0 nr=0 decimate=1 interlaced=0 bluray_compat=0 constrained_intra=0 bframes=3 b_pyramid=2 b_adapt=1 b_bias=0 direct=1 weightb=1 open_gop=0 weightp=2 keyint=250 keyint_min=25 scenecut=40 intra_refresh=0 rc_lookahead=40 rc=crf mbtree=1 crf=23.0 qcomp=0.60 qpmin=0 qpmax=69 qpstep=4 ip_ratio=1.40 aq=1:1.00\n",
      "Output #0, mp4, to 'ESDA.mp4':\n",
      "  Metadata:\n",
      "    encoder         : Lavf58.76.100\n",
      "  Stream #0:0: Video: h264 (avc1 / 0x31637661), yuv444p(progressive), 1366x683, q=2-31, 25 fps, 12800 tbn\n",
      "    Metadata:\n",
      "      encoder         : Lavc58.134.100 libx264\n",
      "    Side data:\n",
      "      cpb: bitrate max/min/avg: 0/0/0 buffer size: 0 vbv_delay: N/A\n",
      "frame=  480 fps= 14 q=-1.0 Lsize=    3401kB time=00:00:19.08 bitrate=1460.2kbits/s speed=0.554x    \n",
      "video:3395kB audio:0kB subtitle:0kB other streams:0kB global headers:0kB muxing overhead: 0.180939%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mframe I:2     Avg QP:19.35  size:452526\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mframe P:170   Avg QP:23.33  size: 15009\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mframe B:308   Avg QP:32.15  size:    62\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mconsecutive B-frames: 14.2%  0.8%  0.0% 85.0%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mmb I  I16..4:  0.4% 20.0% 79.7%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mmb P  I16..4:  0.0%  0.0%  0.1%  P16..4:  9.7%  0.9%  1.4%  0.0%  0.0%    skip:87.9%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mmb B  I16..4:  0.0%  0.0%  0.0%  B16..8:  1.1%  0.0%  0.0%  direct: 0.0%  skip:98.9%  L0:11.3% L1:88.7% BI: 0.0%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0m8x8 transform intra:19.6% inter:46.9%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mcoded y,u,v intra: 86.2% 67.4% 60.5% inter: 2.4% 2.2% 2.1%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mi16 v,h,dc,p: 19% 45% 36%  0%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mi8 v,h,dc,ddl,ddr,vr,hd,vl,hu: 48%  5% 21%  5%  8%  4%  2%  2%  4%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mi4 v,h,dc,ddl,ddr,vr,hd,vl,hu: 14% 12% 14% 10% 13% 10%  8%  7% 11%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mWeighted P-Frames: Y:0.0% UV:0.0%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mref P L0: 65.9% 16.5% 14.4%  3.2%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mref B L0: 83.8% 14.8%  1.4%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mref B L1: 89.3% 10.7%\n",
      "\u001b[1;36m[libx264 @ 0x55cd87474900] \u001b[0mkb/s:1448.18\n"
     ]
    }
   ],
   "source": [
    "# !ffmpeg -pattern_type glob -i './ESDA/*.png' -vf zoompan=z=1:d=4:s=1366x683:fps=5,framerate=25 -vcodec libx264 -acodec aac ESDA.mp4\n",
    "!ffmpeg -pattern_type glob -i './ESDA/*.png' -vf zoompan=z=1:d=4:s=1366x683:fps=5,framerate=25 -vcodec libx264 -an ESDA.mp4"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "T3yUhuXZtjRi",
    "9qGLZBMUEVX7",
    "YTtJbfbtTbiy",
    "YcPXmUXWfeC_"
   ],
   "include_colab_link": true,
   "name": "bicing_2020-2021_clima_rutas",
   "provenance": []
  },
  "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}