test_predict.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/bdebian/.virtualenvs/app_inventory/lib/python3.11/site-packages/statsforecast/utils.py:237: FutureWarning: 'M' is deprecated and will be removed in a future version, please use 'ME' instead.\n",
      "  \"ds\": pd.date_range(start=\"1949-01-01\", periods=len(AirPassengers), freq=\"M\"),\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import re\n",
    "import yaml\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import logging\n",
    "\n",
    "from scipy import stats, signal\n",
    "from pathlib import Path\n",
    "from src.lib.class_load import LoadFiles\n",
    "from src.data.save_models import SAVE_DIR\n",
    "from src.lib.utils import select_best_model\n",
    "from src.lib.factory_data import SQLDataSourceFactory, get_data, create_table, set_data\n",
    "from src.lib.factory_models import ModelContext\n",
    "from src.lib.factory_prepare_data import (\n",
    "    DataCleaner,\n",
    "    DataModel,\n",
    "    MeanImputation,\n",
    "    OutliersToIQRMean,\n",
    "    PrepareDtypeColumns,\n",
    "    base_dtypes\n",
    ")\n",
    "from src.models.DP_model import Modelos\n",
    "from src.features.features_redis import HandleRedis\n",
    "from src.features.features_postgres import HandleDBpsql\n",
    "from src.models.args_data_model import Parameters\n",
    "from src.data.logs import LOGS_DIR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-04-09 03:01:58,708 - train - DEBUG - Inciando secuencia de entrenamiento\n",
      "2024-04-09 03:01:58,708 - train - DEBUG - Inciando secuencia de entrenamiento\n",
      "2024-04-09 03:01:58,710 - train - DEBUG - Archivo de configuraciones cargado\n",
      "2024-04-09 03:01:58,710 - train - DEBUG - Archivo de configuraciones cargado\n",
      "2024-04-09 03:01:58,711 - train - DEBUG - verficando si existe data cache\n",
      "2024-04-09 03:01:58,711 - train - DEBUG - verficando si existe data cache\n",
      "2024-04-09 03:01:58,816 - train - DEBUG - Existe data en cache\n",
      "2024-04-09 03:01:58,816 - train - DEBUG - Existe data en cache\n",
      "2024-04-09 03:01:58,817 - train - DEBUG - Realizando peticion a la fuente de datos\n",
      "2024-04-09 03:01:58,817 - train - DEBUG - Realizando peticion a la fuente de datos\n",
      "2024-04-09 03:01:58,843 - train - DEBUG - Actualizando cache en redis\n",
      "2024-04-09 03:01:58,843 - train - DEBUG - Actualizando cache en redis\n",
      "2024-04-09 03:01:58,886 - datasource - DEBUG - Recuperando data existente\n",
      "2024-04-09 03:01:58,886 - datasource - DEBUG - Recuperando data existente\n",
      "2024-04-09 03:01:58,958 - datasource - DEBUG - Verificacion si existe nueva informacion\n",
      "2024-04-09 03:01:58,958 - datasource - DEBUG - Verificacion si existe nueva informacion\n",
      "2024-04-09 03:01:59,199 - train - DEBUG - Actualizacion completa de datos en redis\n",
      "2024-04-09 03:01:59,199 - train - DEBUG - Actualizacion completa de datos en redis\n"
     ]
    }
   ],
   "source": [
    "path_folder = os.path.dirname(\n",
    "    '/home/bdebian/Documents/Projects/app_prediction/src')\n",
    "folder_model = Path(path_folder).joinpath(\"scr/data/save_models\")\n",
    "\n",
    "handler_load = LoadFiles()\n",
    "handler_redis = HandleRedis()\n",
    "ruta_actual = os.path.dirname(\n",
    "    '/home/bdebian/Documents/Projects/app_prediction/src')\n",
    "\n",
    "# =================================================================\n",
    "#             Configuracion Logger\n",
    "# =================================================================\n",
    "# Configura un logger personalizado en lugar de usar el logger raíz\n",
    "logfile = ruta_actual + \"/src/data/config/logging.conf\"\n",
    "logging.config.fileConfig(os.path.join(LOGS_DIR, logfile))\n",
    "logger = logging.getLogger(\"train\")\n",
    "logger.debug(\"Inciando secuencia de entrenamiento\")\n",
    "\n",
    "# =================================================================\n",
    "#             Cargar los parametros\n",
    "# =================================================================\n",
    "CONFIG_FILE = ruta_actual + \"/src/data/config/config.yaml\"\n",
    "with open(CONFIG_FILE, \"r\", encoding=\"utf-8\") as file:\n",
    "    parameters = yaml.safe_load(file)\n",
    "\n",
    "logger.debug(\"Archivo de configuraciones cargado\")\n",
    "parametros = Parameters(**parameters)\n",
    "# =================================================================\n",
    "#             Cargar datos de la fuente de datos\n",
    "# =================================================================\n",
    "# Interacion para hacer un cache de los datos en redis\n",
    "try:\n",
    "    logger.debug(\"verficando si existe data cache\")\n",
    "    data = handler_redis.get_cache_data(\n",
    "        hash_name=parametros.query_template[\"table\"],\n",
    "        config=parametros.connection_data_source,\n",
    "    )\n",
    "    # Condicional para actualizar datos en caso de existan datos en redis\n",
    "    if data is not None:\n",
    "        logger.debug(\"Existe data en cache\")\n",
    "\n",
    "        # Secuencia de codigo para perdir nuevos datos a la base de datos\n",
    "        date_col_query = parameters[\"query_template\"][\"columns\"][\"0\"]\n",
    "        LAST_DAY = str(data.iloc[-1][0])\n",
    "        parameters[\"query_template\"][\"where\"] = f\" \\\"{date_col_query}\\\" > '{LAST_DAY}'\"\n",
    "        parameters[\"query_template\"][\"order\"] = \"\".join(\n",
    "            ['\"' + columna + '\"' for columna in [date_col_query]])\n",
    "\n",
    "        logger.debug(\"Realizando peticion a la fuente de datos\")\n",
    "\n",
    "        # Extraccion de la nueva data para actualizar\n",
    "        new = get_data(SQLDataSourceFactory(**parameters))\n",
    "        logger.debug(\"Actualizando cache en redis\")\n",
    "        data = handler_redis.set_cache_data(\n",
    "            hash_name=parametros.query_template[\"table\"],\n",
    "            old_dataframe=data,\n",
    "            new_dataframe=new,\n",
    "            exp_time=parametros.exp_time_cache,\n",
    "            config=parametros.connection_data_source,\n",
    "        )\n",
    "        logger.debug(\"Actualizacion completa de datos en redis\")\n",
    "\n",
    "    # Verificar que existieran datos en cache\n",
    "    if data is None:\n",
    "        logger.debug(\"No existe cache de datos\")\n",
    "\n",
    "        data = get_data(SQLDataSourceFactory(**parameters))\n",
    "        logger.debug(\"Insertando datos de cache en redis\")\n",
    "        data = handler_redis.set_cache_data(\n",
    "            hash_name=parametros.query_template[\"table\"],\n",
    "            old_dataframe=data,\n",
    "            new_dataframe=None,\n",
    "            exp_time=parametros.exp_time_cache,\n",
    "            config=parametros.connection_data_source,\n",
    "        )\n",
    "\n",
    "except ValueError as error:\n",
    "    logger.debug(\"[ERROR] No se puede hacer un cache de la fuente de datos\")\n",
    "    logger.debug(error)\n",
    "    exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/bdebian/Documents/Projects/app_prediction/src/features/features_fix_data.py:272: FutureWarning: 'M' is deprecated and will be removed in a future version, please use 'ME' instead.\n",
      "  self.dataframe = self.dataframe.groupby([pd.Grouper(freq=frequency_group)])[col_group].sum()\n"
     ]
    }
   ],
   "source": [
    "# =================================================================\n",
    "#             Limpieza de datos\n",
    "# =================================================================\n",
    "new_types = []\n",
    "\n",
    "for dtypo in parameters[\"type_data\"].values():\n",
    "\n",
    "    new_types.append(base_dtypes[dtypo])\n",
    "\n",
    "# metodo para transformar los tipo de datos\n",
    "strategy = {int: np.mean, float: np.mean, object: stats.mode}\n",
    "\n",
    "# Estrategias para imputar los datos faltantes de NA\n",
    "replace = {\n",
    "    int: lambda x: int(float(x.replace(\",\", \"\"))),\n",
    "    float: lambda x: float(x.replace(\",\", \"\")),\n",
    "    object: lambda x: x.strip(),\n",
    "}\n",
    "# =================================================================\n",
    "\n",
    "update_dtype_columns = PrepareDtypeColumns(\n",
    "    replace_dtypes=new_types,\n",
    "    strategy_imputation=strategy,\n",
    "    preprocess_function=replace,\n",
    "    **parameters,\n",
    ")\n",
    "\n",
    "# Ejecucion de fabrica para aplicar y ordenar los tipos de datos y los valores\n",
    "cleaner = DataCleaner()\n",
    "cleaner.strategy = update_dtype_columns\n",
    "data_ = cleaner.clean(data)\n",
    "\n",
    "# Condicion de filtrado para informacion segun los valores\n",
    "filter_label: str = parameters[\"filter_data\"][\"filter_1_feature\"]\n",
    "filter_col: str = parameters[\"filter_data\"][\"filter_1_column\"]\n",
    "filter_product = data_.dataframe[filter_col] == filter_label\n",
    "filter_data = data_.dataframe[filter_product].sort_values(\n",
    "    by=parameters[\"filter_data\"][\"date_column\"])\n",
    "\n",
    "# Seleccion de agrupacion de tiempo\n",
    "# parameters[\"filter_data\"][\"group_frequecy\"] = \"M\"\n",
    "# parameters[\"filter_data\"][\"filter_1_feature\"] = filter_label\n",
    "\n",
    "# # Datos de validacion\n",
    "# validate_data = filter_data.set_index(time_series_col)[\"2023-12-01\":].reset_index()\n",
    "\n",
    "# # Datos de entrenamiento\n",
    "# filter_data = filter_data.set_index(time_series_col)[:\"2023-11-30\"].reset_index()\n",
    "\n",
    "outliners = OutliersToIQRMean(**parameters)\n",
    "cleaner.strategy = outliners\n",
    "outlines_data = cleaner.clean(filter_data)\n",
    "# validate_outlines = cleaner.clean(validate_data)\n",
    "\n",
    "# Filtrado de datos para eliminar valores negativos\n",
    "filter_values = outlines_data[\"quantity\"] <= 0\n",
    "outlines_data[filter_values] = 0.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =================================================================\n",
    "#             Filtro pasabajos\n",
    "# =================================================================\n",
    "fs = 1 / 24 / 3600  # 1 day in Hz (sampling frequency)\n",
    "\n",
    "nyquist = fs / 0.5  # 2 # 0.5 times the sampling frequency\n",
    "cutoff = 0.5  # 0.1 fraction of nyquist frequency, here  it is 5 days\n",
    "# cutoff=  4.999999999999999  days\n",
    "b, a = signal.butter(5, cutoff, btype=\"lowpass\")  # low pass filter\n",
    "\n",
    "dUfilt = signal.filtfilt(b, a, outlines_data[\"quantity\"])\n",
    "dUfilt = np.array(dUfilt)\n",
    "dUfilt = dUfilt.transpose()\n",
    "outlines_data[\"low_past\"] = dUfilt\n",
    "\n",
    "# =================================================================\n",
    "#             Preparacion de datos para el modelo\n",
    "# =================================================================\n",
    "data_for_model = DataModel(**parameters)\n",
    "cleaner.strategy = data_for_model\n",
    "data_ready, scaler_data = cleaner.clean(outlines_data)\n",
    "\n",
    "# Creacion del dataframe para del filtro pasa bajo para los datos\n",
    "low_pass_data = outlines_data[\"low_past\"]\n",
    "low_pass_data = low_pass_data.to_frame()\n",
    "low_pass_data.rename(columns={\"low_past\": \"quantity\"}, inplace=True)\n",
    "data_ready_lp, scaler_data_lp = cleaner.clean(low_pass_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Modelo importado NBeatsModel\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d081a7e2e2f74013863e8daf3e506c7b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Predicting: |          | 0/? [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_18825/491405056.py:55: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  [\"predict_column\"]].clip(lower=0, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "# =================================================================\n",
    "#            Cargar modelo\n",
    "# =================================================================\n",
    "# Rutas de los parametros para predicciones\n",
    "save_dir = Path(SAVE_DIR).joinpath(\n",
    "    parameters[\"filter_data\"][\"filter_1_feature\"])\n",
    "models_metrics = save_dir.joinpath(\n",
    "    \"train_metrics\").with_suffix(\".json\").as_posix()\n",
    "\n",
    "MODE_USED = select_best_model(models_metrics)\n",
    "\n",
    "scaler_name = save_dir.joinpath(\"scaler\").with_suffix(\".pkl\").as_posix()\n",
    "scaler_lp_name = save_dir.joinpath(\"scaler_lp\").with_suffix(\".pkl\").as_posix()\n",
    "last_pred = save_dir.joinpath(\"previus\").with_suffix(\".json\").as_posix()\n",
    "model_train = save_dir.joinpath(\n",
    "    f\"model_{MODE_USED}\").with_suffix(\".pt\").as_posix()\n",
    "parameters_model = save_dir.joinpath(\n",
    "    f\"parametros_{MODE_USED}\").with_suffix(\".json\").as_posix()\n",
    "\n",
    "modelo = ModelContext(model_name=MODE_USED,\n",
    "                      data=data_ready,\n",
    "                      split=83,\n",
    "                      covarianze=data_ready_lp,\n",
    "                      ** parameters\n",
    "                      )\n",
    "\n",
    "# Cargar escaler\n",
    "scaler = handler_load.load_scaler(scaler_name)\n",
    "scaler_lp = handler_load.load_scaler(scaler_lp_name)\n",
    "\n",
    "# Cargar modelo para hacer las predicciones\n",
    "IntModel = Modelos[MODE_USED]\n",
    "trained_parameters = handler_load.json_to_dict(json_file=parameters_model)[0]\n",
    "model_update_parameters = IntModel(**trained_parameters)\n",
    "model_trained = model_update_parameters.load(model_train)\n",
    "\n",
    "# Se carga el modelo , los datos de predicciones ya se cargaron previamente en ModelContext\n",
    "pred_series = modelo.predict(\n",
    "    model=model_trained,\n",
    "    data=None,  # data_ready,\n",
    "    horizont=parameters[\"forecast_val\"],\n",
    "    past_cov=None  # data_ready_lp\n",
    ")\n",
    "\n",
    "# Invertir predicciones escaler de entrenamietno\n",
    "pred_scale = scaler.inverse_transform(pred_series)\n",
    "\n",
    "# Invertir Predicciones escaler de transformacion de los datos\n",
    "# pred_scale = scaler_data.inverse_transform(pred_series)\n",
    "\n",
    "data_frame_predicciones = pred_scale.pd_dataframe()\n",
    "column_field = list(data_frame_predicciones.columns)\n",
    "data_frame_predicciones.reset_index(inplace=True)\n",
    "data_frame_predicciones[parameters[\"filter_data\"]\n",
    "                        [\"predict_column\"]].clip(lower=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-04-09 03:02:00,081 - train - DEBUG - Enviando valor de las predicciones\n",
      "2024-04-09 03:02:00,081 - train - DEBUG - Enviando valor de las predicciones\n",
      "2024-04-09 03:02:00,090 - datasource - ERROR - relation \"predicciones_modelo\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,090 - datasource - ERROR - relation \"predicciones_modelo\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,091 - datasource - DEBUG - Finalizacion query\n",
      "2024-04-09 03:02:00,091 - datasource - DEBUG - Finalizacion query\n",
      "insertando\n",
      "2024-04-09 03:02:00,128 - datasource - INFO - Query de insercion de archivo csv finalizada\n",
      "2024-04-09 03:02:00,128 - datasource - INFO - Query de insercion de archivo csv finalizada\n"
     ]
    }
   ],
   "source": [
    "# ===============================================================================================\n",
    "#                        PREDICCIONES\n",
    "# ===============================================================================================\n",
    "# TODO:\n",
    "\"\"\"Esta parte tiene un ToDo importante: Tiene que ordenarse y optimizarce para se escalable\n",
    "   De momento funciona de manera estatica para ciertas cosas sobre todo el tema de la escritura\n",
    "   en postgres, ademas de tener codigo copiado de funciones internas ya ordenadas\n",
    "\"\"\"\n",
    "logger.debug(\"Enviando valor de las predicciones\")\n",
    "# Cuantificar metricas de la columan de predicciones\n",
    "# metric_columns_pred = data_.metrics_column(\n",
    "#     data_frame_predicciones[parameters[\"filter_data\"][\"predict_column\"]]\n",
    "# )\n",
    "# Seleccion de columans para generar el dataframe de salida para la base de datos\n",
    "filter_temp = []\n",
    "for filter_list in parameters[\"filter_data\"]:\n",
    "    if \"feature\" in filter_list:\n",
    "        filter_temp.append(parameters[\"filter_data\"][filter_list])\n",
    "\n",
    "for adding_data in filter_temp:\n",
    "    data_frame_predicciones[str(adding_data)] = adding_data\n",
    "\n",
    "new_names = list(parameters[\"query_template_write\"][\"columns\"].values())\n",
    "rename = {x: y for x, y in zip(\n",
    "    list(data_frame_predicciones.columns), new_names)}\n",
    "data_frame_predicciones.rename(columns=rename, inplace=True)\n",
    "\n",
    "# Crear tabla para guardas la informacion\n",
    "create_table(SQLDataSourceFactory(**parameters))\n",
    "\n",
    "# Ingresar los datos a la base de datos\n",
    "set_data(SQLDataSourceFactory(**parameters), data_frame_predicciones)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-04-09 03:02:00,170 - train - DEBUG - Agrupando datos reales por perido de tiempo a las predicciones\n",
      "2024-04-09 03:02:00,170 - train - DEBUG - Agrupando datos reales por perido de tiempo a las predicciones\n",
      "2024-04-09 03:02:00,171 - train - DEBUG - Agrupando datos reales por perido de tiempo a las predicciones para el modelo : 90000S\n",
      "2024-04-09 03:02:00,171 - train - DEBUG - Agrupando datos reales por perido de tiempo a las predicciones para el modelo : 90000S\n",
      "2024-04-09 03:02:00,177 - train - DEBUG - Archivo de configuraciones cargado\n",
      "2024-04-09 03:02:00,177 - train - DEBUG - Archivo de configuraciones cargado\n",
      "2024-04-09 03:02:00,178 - train - DEBUG - Creando tabla agrupacion de datos reales semanales caso de ser necesario\n",
      "2024-04-09 03:02:00,178 - train - DEBUG - Creando tabla agrupacion de datos reales semanales caso de ser necesario\n",
      "2024-04-09 03:02:00,184 - datasource - ERROR - relation \"datos_originales_agrupados\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,184 - datasource - ERROR - relation \"datos_originales_agrupados\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,185 - datasource - DEBUG - Finalizacion query\n",
      "2024-04-09 03:02:00,185 - datasource - DEBUG - Finalizacion query\n",
      "2024-04-09 03:02:00,191 - train - DEBUG - agruando informacion temporal para el modelo : 90000S\n",
      "2024-04-09 03:02:00,191 - train - DEBUG - agruando informacion temporal para el modelo : 90000S\n",
      "2024-04-09 03:02:00,196 - datasource - INFO - Query de insercion de archivo csv finalizada\n",
      "2024-04-09 03:02:00,196 - datasource - INFO - Query de insercion de archivo csv finalizada\n"
     ]
    }
   ],
   "source": [
    "# ===============================================================================================\n",
    "#                             DATOS REALES MESES\n",
    "# ===============================================================================================\n",
    "logger.debug(\"Agrupando datos reales por perido de tiempo a las predicciones\")\n",
    "item = filter_label\n",
    "logger.debug(\n",
    "    \"Agrupando datos reales por perido de tiempo a las predicciones para el modelo : %s\",\n",
    "    item,\n",
    ")\n",
    "\n",
    "date_col = parameters[\"filter_data\"][\"date_column\"]\n",
    "data_col = parameters[\"filter_data\"][\"predict_column\"]\n",
    "\n",
    "outlines_data.reset_index(inplace=True)\n",
    "outlines_data['code'] = filter_label\n",
    "outlines_data.rename({\"low_past\": 'filter_data'}, axis=\"columns\", inplace=True)\n",
    "outlines_data = outlines_data.round(0)\n",
    "\n",
    "with open(CONFIG_FILE, \"r\", encoding=\"utf-8\") as file:\n",
    "    parameters = yaml.safe_load(file)\n",
    "\n",
    "logger.debug(\"Archivo de configuraciones cargado\")\n",
    "parametros = Parameters(**parameters)\n",
    "\n",
    "parameters[\"query_template_write\"][\"table\"] = \"datos_originales_agrupados\"\n",
    "parameters[\"query_template_write\"][\"columns\"][\"0\"] = \"date\"\n",
    "parameters[\"query_template_write\"][\"columns\"][\"1\"] = \"data\"\n",
    "parameters[\"query_template_write\"][\"columns\"][\"2\"] = \"filter_data\"\n",
    "parameters[\"query_template_write\"][\"columns\"][\"3\"] = \"code\"\n",
    "parameters[\"type_data_out\"] = {\n",
    "    \"date\": \"date\", \"data\": \"float\", \"filter_data\":\"float\" ,\"code\": \"string\"}\n",
    "\n",
    "# Crear tabla para guardas la informacion\n",
    "logger.debug(\n",
    "    \"Creando tabla agrupacion de datos reales semanales caso de ser necesario\")\n",
    "create_table(SQLDataSourceFactory(**parameters))\n",
    "\n",
    "#Solicita datos anteriores para verificar la existencia de los mismos\n",
    "parameters[\"query_template\"][\"table\"] = \"datos_originales_agrupados\"\n",
    "parameters[\"query_template\"][\"columns\"][\"0\"] = \"date\"\n",
    "parameters[\"query_template\"][\"columns\"][\"1\"] = \"data\"\n",
    "parameters[\"query_template\"][\"columns\"][\"2\"] = \"filter_data\"\n",
    "parameters[\"query_template\"][\"columns\"][\"3\"] = \"code\"\n",
    "parameters[\"type_data_out\"] = {\n",
    "    \"date\": \"date\", \"data\": \"float\", \"filter_data\": \"float\", \"code\": \"string\"}\n",
    "# del parameters[\"query_template\"][\"columns\"][\"3\"]\n",
    "data_last = get_data(SQLDataSourceFactory(**parameters))\n",
    "\n",
    "\n",
    "# Condicional para verifical la ultima fecha de los datos almacenados\n",
    "# En caso de estar vacio rellena con el historial de los datos por meses\n",
    "if data_last.empty:\n",
    "    # Ingresar los datos a la base de datos\n",
    "    logger.debug(\"agruando informacion temporal para el modelo : %s\", item)\n",
    "    set_data(SQLDataSourceFactory(**parameters), outlines_data)\n",
    "else:\n",
    "    # obtiene el ultimo punto de las predicciones\n",
    "    LAST_DATE = data_last.iloc[-1, 0]\n",
    "    # Filtra los datos a enviar en base a la ultima fecha\n",
    "    outlines_data = outlines_data[outlines_data[date_col]\n",
    "                                  > np.datetime64(LAST_DATE)]\n",
    "\n",
    "    # Ingresar los datos a la base de datos\n",
    "    logger.debug(\"agruando informacion temporal para el modelo : %s\", item)\n",
    "    set_data(SQLDataSourceFactory(**parameters), outlines_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-04-09 03:02:00,224 - train - DEBUG - Calculando metricas de datos reales\n",
      "2024-04-09 03:02:00,224 - train - DEBUG - Calculando metricas de datos reales\n",
      "2024-04-09 03:02:00,229 - datasource - ERROR - relation \"datos_originales_metricas\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,229 - datasource - ERROR - relation \"datos_originales_metricas\" already exists\n",
      "\n",
      "2024-04-09 03:02:00,230 - datasource - DEBUG - Finalizacion query\n",
      "2024-04-09 03:02:00,230 - datasource - DEBUG - Finalizacion query\n",
      "2024-04-09 03:02:00,251 - datasource - INFO - Query de insercion de archivo csv finalizada\n",
      "2024-04-09 03:02:00,251 - datasource - INFO - Query de insercion de archivo csv finalizada\n"
     ]
    }
   ],
   "source": [
    "# ===============================================================================================\n",
    "#                            ORIGINAL  METRICAS DATA\n",
    "# ===============================================================================================\n",
    "logger.debug(\"Calculando metricas de datos reales\")\n",
    "\n",
    "type_data_out = {\n",
    "    \"rango\": \"float\",\n",
    "    \"mean\": \"float\",\n",
    "    # \"varianza\": \"float\",\n",
    "    \"desviacion_estandar\": \"float\",\n",
    "    \"coeficiente_varianza\": \"float\",\n",
    "    \"quantile_q0\": \"float\",\n",
    "    \"quantile_q1\": \"float\",\n",
    "    \"quantile_q3\": \"float\",\n",
    "    \"quantile_q4\": \"float\",\n",
    "    \"interquantile\": \"float\",\n",
    "    \"desviacion_media_absoluta\": \"float\",\n",
    "    \"init_date\": \"date\",\n",
    "    \"end_date\": \"date\",\n",
    "    \"product\": \"string\",\n",
    "}\n",
    "\n",
    "fix_data_dict = {\n",
    "    \"table\": \"datos_originales_metricas\",\n",
    "    \"columns\": {str(index): key for index, key in enumerate(type_data_out.keys())},\n",
    "    \"order\": \"index\",\n",
    "    \"where\": \"posicion > 1\",\n",
    "}\n",
    "\n",
    "parameters[\"query_template_write\"] = fix_data_dict\n",
    "parameters[\"type_data_out\"] = type_data_out\n",
    "\n",
    "create_table(SQLDataSourceFactory(**parameters))\n",
    "\n",
    "fix_data_dict = {\n",
    "    \"table\": \"datos_originales_metricas\",\n",
    "    \"columns\": {str(index): key for index, key in enumerate(type_data_out.keys())},\n",
    "    \"order\": \"index\",\n",
    "    \"where\": \"index > 1\",\n",
    "}\n",
    "\n",
    "parameters[\"query_template\"] = fix_data_dict\n",
    "parameters[\"type_data_out\"] = type_data_out\n",
    "data_original_metricas = get_data(SQLDataSourceFactory(**parameters))\n",
    "\n",
    "#Condicional para validar el tipo de dataframe que se requieres\n",
    "if data_original_metricas.empty:\n",
    "    df = data_.dataframe\n",
    "else:\n",
    "    df = data_.dataframe[data_.dataframe[date_col] > np.datetime64(LAST_DATE)]\n",
    "\n",
    "# Convertir la columna date_col a tipo datetime\n",
    "df[date_col] = pd.to_datetime(df[date_col])\n",
    "\n",
    "# Agregar columnas de mes y año\n",
    "df['month'] = df[date_col].dt.month\n",
    "df['year'] = df[date_col].dt.year\n",
    "\n",
    "# Calcular estadísticas por mes\n",
    "monthly_stats = df.groupby(['year', 'month']).agg(\n",
    "    rango=('quantity', lambda x: x.max() - x.min()),\n",
    "    # varianza=('quantity', 'var'),\n",
    "    desviacion_estandar=('quantity', 'std'),\n",
    "    coeficiente_varianza=('quantity', lambda x: x.std() / x.mean()),\n",
    "    mean=('quantity', 'mean'),\n",
    "    quantile_q0=('quantity', lambda x: x.quantile(0)),\n",
    "    quantile_q1=('quantity', lambda x: x.quantile(0.25)),\n",
    "    quantile_q3=('quantity', lambda x: x.quantile(0.75)),\n",
    "    quantile_q4=('quantity', lambda x: x.quantile(1)),\n",
    "    interquantile=('quantity', lambda x: x.quantile(\n",
    "        0.75) - x.quantile(0.25)),\n",
    "    desviacion_media_absoluta=('quantity', lambda x: (x - x.mean()).abs().mean())\n",
    ").reset_index()\n",
    "\n",
    "# Convertir año y mes a fecha inicial y final del mes\n",
    "monthly_stats['init_date'] = pd.to_datetime(\n",
    "    monthly_stats[['year', 'month']].assign(day=1))\n",
    "monthly_stats['end_date'] = pd.to_datetime(monthly_stats[['year', 'month']].assign(\n",
    "    day=pd.DatetimeIndex(pd.to_datetime(monthly_stats['init_date'])).days_in_month))\n",
    "\n",
    "# Eliminar columnas de año y mes\n",
    "monthly_stats.drop(columns=['year', 'month'], inplace=True)\n",
    "\n",
    "# Agregar la columna 'product' al DataFrame resultante\n",
    "monthly_stats['product'] = filter_label\n",
    "\n",
    "# Reordenar las columnas según el tipo de datos\n",
    "monthly_stats = monthly_stats[['rango',\n",
    "                                'mean',\n",
    "                                # 'varianza',\n",
    "                                'desviacion_estandar',\n",
    "                                'coeficiente_varianza',\n",
    "                                'quantile_q0',\n",
    "                                'quantile_q1',\n",
    "                                'quantile_q3',\n",
    "                                'quantile_q4',\n",
    "                                'interquantile',\n",
    "                                'desviacion_media_absoluta',\n",
    "                                'init_date',\n",
    "                                'end_date',\n",
    "                                'product'\n",
    "                                ]]\n",
    "\n",
    "monthly_stats = monthly_stats.round(2)\n",
    "set_data(SQLDataSourceFactory(**parameters), monthly_stats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "app_inventory",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}