01_Generar_datos_limpiados_semanal_rango_horario.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Juan Pardo Palazón\n",
    "\n",
    "# Importacion de librerias\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Constantes\n",
    "DIRECTORIO_BASE = 'D:/anaconda/data/'\n",
    "FECHA_MINIMA = '2014-02-01'\n",
    "FECHA_MAXIMA = '2015-07-01'\n",
    "NOMBRE_FICHERO_ORIGEN = 'CLEAN_House' \n",
    "TAMANO_VENTANA = 60 # Ventana de rolling\n",
    "CAMPO = 'Aggregate'\n",
    "DECIMALES = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def obtenerDatosFiltrados(fichero, columna):\n",
    "    \"\"\"Filtrar por fechas dadas y filtrar los datos de la columna pasada\n",
    "    por parametro. Se devuelve los datos filtrado de la columna\n",
    "     \n",
    "    Argumentos:\n",
    "    file -- fichero de entrada \n",
    "    column -- columna a seleccionar\n",
    "    \"\"\"\n",
    "    # Leer fichero origen, seleccionar columna consumo total ()\n",
    "    datos_hogar = pd.read_csv(DIRECTORIO_BASE + fichero +'.csv', delimiter = ',', parse_dates=[0], index_col=0)\n",
    "    datos_hogar = datos_hogar[[columna]]\n",
    "    \n",
    "    # Filtrardo de fechas. Rango de fechas con menor rango datos faltantes\n",
    "    df_filtrado_fechas = datos_hogar[(datos_hogar.index > FECHA_MINIMA) & (datos_hogar.index < FECHA_MAXIMA)]\n",
    "    return df_filtrado_fechas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def limpiarDatos(dt_filtrado, rango):\n",
    "    \"\"\"Limpia los valores NA. para un rango dado\n",
    "     \n",
    "    Argumentos:\n",
    "    dt_filtrado -- tabla con todos los datos \n",
    "    rango -- rango a filtrar\n",
    "    \"\"\"\n",
    "    # Se seleccionan aquellos valores del rango hora\n",
    "    # pasado por parámetro (0, 6, 12 ó 18)\n",
    "    dt_filtrado_Rango = dt_filtrado[dt_filtrado_6H.Hora == rango]\n",
    "    \n",
    "    # Limpieza de NA\n",
    "    dt_filtrado_Rango = dt_filtrado_Rango.interpolate(method ='linear', limit_direction ='forward')\n",
    "    \n",
    "    return dt_filtrado_Rango"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dfTransforma6H(fichero, columna):\n",
    "    \"\"\"Agrupa los datos obtenidos del fichero file\n",
    "    por rangos de 6H\n",
    "     \n",
    "    Argumentos:\n",
    "    file -- fichero de entrada \n",
    "    column -- columna a seleccionar\n",
    "    \"\"\"\n",
    "    # Obtener data frame filtrado por fechas y con la columna pasada por parametro    \n",
    "    df_filtrado = obtenerDatosFiltrados(fichero, columna)\n",
    "    \n",
    "    # Se agrupa por rangos de 6 horas en un día\n",
    "    dt_filtrado_muestreo6H = df_filtrado.resample('6H').mean()\n",
    "    \n",
    "    # Se inserta la columna hora\n",
    "    hora = dt_filtrado_muestreo6H.index.hour\n",
    "    dt_filtrado_muestreo6H = pd.concat([dt_filtrado_muestreo6H, pd.DataFrame(hora, index=dt_filtrado_muestreo6H.index)],  axis = 1)\n",
    "\n",
    "    # Se renombran columnas\n",
    "    dt_filtrado_muestreo6H.columns = [columna,'Hora'] \n",
    "    \n",
    "    return dt_filtrado_muestreo6H"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generarFicheroSemanal(dt_filtrado_6H, campo):\n",
    "    \"\"\"Genera para esa semana los valores de consumo\n",
    "    por los 4 rangos horarios (0, 6, 12, 18)\n",
    "     \n",
    "    Argumentos:\n",
    "    dt_filtrado_6H -- datos a transformar \n",
    "    campo -- campo a seleccionar\n",
    "    \"\"\"\n",
    "    # Limpiar datos\n",
    "    datos_filtrar_0 = limpiarDatos(dt_filtrado_6H, 0)\n",
    "    datos_filtrar_6 = limpiarDatos(dt_filtrado_6H, 6)\n",
    "    datos_filtrar_12 = limpiarDatos(dt_filtrado_6H, 12)\n",
    "    datos_filtrar_18 = limpiarDatos(dt_filtrado_6H, 18)\n",
    "\n",
    "    # Filtrar por hora y obtener los datos agrupados por semana\n",
    "    dt_filtrado_6H_Semana = datos_filtrar_0.rolling(window=TAMANO_VENTANA, center=True).mean().resample(\"W\").mean().apply(lambda x: round(x, DECIMALES))\n",
    "    w6 = datos_filtrar_6.rolling(window=TAMANO_VENTANA, center=True).mean().resample(\"W\").mean().apply(lambda x: round(x, DECIMALES))\n",
    "    w12 = datos_filtrar_12.rolling(window=TAMANO_VENTANA, center=True).mean().resample(\"W\").mean().apply(lambda x: round(x, DECIMALES))\n",
    "    w18 = datos_filtrar_18.rolling(window=TAMANO_VENTANA, center=True).mean().resample(\"W\").mean().apply(lambda x: round(x, DECIMALES))\n",
    "    \n",
    "    # Preparar fichero por rangos\n",
    "    dt_filtrado_6H_Semana['Rango 06-12'] = w6[campo]\n",
    "    dt_filtrado_6H_Semana['Rango 12-18'] = w12[campo]\n",
    "    dt_filtrado_6H_Semana['Rango 18-00'] = w18[campo]\n",
    "    dt_filtrado_6H_Semana = dt_filtrado_6H_Semana.drop(columns=['Hora'])\n",
    "    dt_filtrado_6H_Semana = dt_filtrado_6H_Semana.rename(columns={campo: 'Rango 00-06'})\n",
    "\n",
    "    return dt_filtrado_6H_Semana"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                       Aggregate  Hora\n",
      "Time                                  \n",
      "2014-03-06 12:00:00   266.463115    12\n",
      "2014-03-06 18:00:00   859.137496    18\n",
      "2014-03-07 00:00:00  1442.860323     0\n",
      "2014-03-07 06:00:00   199.217453     6\n",
      "2014-03-07 12:00:00   191.393809    12\n",
      "2014-03-07 18:00:00   182.766835    18\n",
      "2014-03-08 00:00:00  1202.994989     0\n",
      "2014-03-08 06:00:00   193.236842     6\n",
      "2014-03-08 12:00:00   182.456944    12\n",
      "2014-03-08 18:00:00   180.091519    18\n"
     ]
    }
   ],
   "source": [
    "# Generar los ficheros con los datos filtrados\n",
    "for num_hogar in range(1, 2):\n",
    "    if num_hogar != 14:\n",
    "    \n",
    "        fichero = NOMBRE_FICHERO_ORIGEN + str(num_hogar)\n",
    "        \n",
    "        # Filtrar por fechas los datos del campo seleccionado\n",
    "        dt_filtrado_6H = dfTransforma6H(fichero, CAMPO)\n",
    "        print(dt_filtrado_6H.head(10))\n",
    "        # Generar fichero con los valores energéticos agrupados por fechas\n",
    "        dt_filtrado_6H_Semana  = generarFicheroSemanal(dt_filtrado_6H, CAMPO)\n",
    "        \n",
    "        # Se ajustan los datos agrupados\n",
    "        if num_hogar in [6]:\n",
    "            dt_filtrado_6H_Semana.iloc[9:-4, :].to_csv('Hogar_' + str(num_hogar) + '_filtro_semanal_rango.csv', sep=',', encoding='utf-8')\n",
    "        \n",
    "        if num_hogar in [3, 11]:\n",
    "            dt_filtrado_6H_Semana.iloc[4:-5, :].to_csv('Hogar_' + str(num_hogar) + '_filtro_semanal_rango.csv', sep=',', encoding='utf-8')\n",
    "        \n",
    "        if num_hogar in [4, 5, 12, 15, 16, 18, 21]:\n",
    "            dt_filtrado_6H_Semana.iloc[5:-4, :].to_csv('Hogar_' + str(num_hogar) + '_filtro_semanal_rango.csv', sep=',', encoding='utf-8')\n",
    "        \n",
    "        if num_hogar in [10]:\n",
    "            dt_filtrado_6H_Semana.iloc[6:-5, :].to_csv('Hogar_' + str(num_hogar) + '_filtro_semanal_rango.csv', sep=',', encoding='utf-8')\n",
    "        \n",
    "        if num_hogar in [1, 2, 7, 8, 9, 13, 14, 17, 19, 20]:\n",
    "            dt_filtrado_6H_Semana.iloc[4:-4, :].to_csv('Hogar_' + str(num_hogar) + '_filtro_semanal_rango.csv', sep=',', encoding='utf-8')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}