1_LimpiezaDeDatos.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "prRzPja8Ay5_"
   },
   "source": [
    "# 1. Limpieza de los datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "95c4pG_8C0cH",
    "outputId": "2e4779d3-6271-4fcc-c779-44b923369841",
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandasql in /usr/local/lib/python3.6/dist-packages (0.7.3)\n",
      "Requirement already satisfied: pandas in /usr/local/lib/python3.6/dist-packages (from pandasql) (1.1.5)\n",
      "Requirement already satisfied: numpy in /usr/local/lib/python3.6/dist-packages (from pandasql) (1.19.5)\n",
      "Requirement already satisfied: sqlalchemy in /usr/local/lib/python3.6/dist-packages (from pandasql) (1.3.23)\n",
      "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.6/dist-packages (from pandas->pandasql) (2018.9)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.6/dist-packages (from pandas->pandasql) (2.8.1)\n",
      "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.6/dist-packages (from python-dateutil>=2.7.3->pandas->pandasql) (1.15.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "id": "Tja6D4NXCvgw"
   },
   "outputs": [],
   "source": [
    "import pandasql as ps\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "w0_WPmfBCx3W"
   },
   "outputs": [],
   "source": [
    "#Lectura\n",
    "b_metadata = pd.read_csv('./building_metadata.csv')\n",
    "weather_train = pd.read_csv('./weather_train.csv', parse_dates=True, index_col='timestamp')\n",
    "weather_test = pd.read_csv('./weather_test.csv', parse_dates=True, index_col='timestamp')\n",
    "meters_train = pd.read_csv('./train.csv')\n",
    "meters_test = pd.read_csv('./test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Zh1VUhS3ChKe"
   },
   "source": [
    "## 1.1 Previsualización y Organización de las Tablas "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lQeA_-e3K0-c"
   },
   "source": [
    "### 1.1.1 Metadatos de los edificios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 198
    },
    "id": "ddLsyYKMHaxP",
    "outputId": "ae7d2a1e-ec25-4ed8-ce2b-d0f3cfe1e321"
   },
   "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>site_id</th>\n",
       "      <th>building_id</th>\n",
       "      <th>primary_use</th>\n",
       "      <th>square_feet</th>\n",
       "      <th>year_built</th>\n",
       "      <th>floor_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Education</td>\n",
       "      <td>7432</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Education</td>\n",
       "      <td>2720</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Education</td>\n",
       "      <td>5376</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Education</td>\n",
       "      <td>23685</td>\n",
       "      <td>2002.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>Education</td>\n",
       "      <td>116607</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   site_id  building_id primary_use  square_feet  year_built  floor_count\n",
       "0        0            0   Education         7432      2008.0          NaN\n",
       "1        0            1   Education         2720      2004.0          NaN\n",
       "2        0            2   Education         5376      1991.0          NaN\n",
       "3        0            3   Education        23685      2002.0          NaN\n",
       "4        0            4   Education       116607      1975.0          NaN"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Metadatos de los edificios\n",
    "b_metadata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "HJNOzD8p0DlT"
   },
   "outputs": [],
   "source": [
    "query = ps.sqldf(\"select primary_use, count(primary_use) from b_metadata group by primary_use\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 528
    },
    "id": "4PkHFMB70TsO",
    "outputId": "506b771a-a0c6-4312-b7b1-580d76aceb78"
   },
   "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>primary_use</th>\n",
       "      <th>count(primary_use)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Education</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Entertainment/public assembly</td>\n",
       "      <td>184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Food sales and service</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Healthcare</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Manufacturing/industrial</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Office</td>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Other</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Parking</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Public services</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Religious worship</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Retail</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Services</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Technology/science</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Utility</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Warehouse/storage</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      primary_use  count(primary_use)\n",
       "0                       Education                 549\n",
       "1   Entertainment/public assembly                 184\n",
       "2          Food sales and service                   5\n",
       "3                      Healthcare                  23\n",
       "4             Lodging/residential                 147\n",
       "5        Manufacturing/industrial                  12\n",
       "6                          Office                 279\n",
       "7                           Other                  25\n",
       "8                         Parking                  22\n",
       "9                 Public services                 156\n",
       "10              Religious worship                   3\n",
       "11                         Retail                  11\n",
       "12                       Services                  10\n",
       "13             Technology/science                   6\n",
       "14                        Utility                   4\n",
       "15              Warehouse/storage                  13"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xn28dRE2RcNM"
   },
   "source": [
    "Por fines de memoria RAM disponible, decidimos trabajar únicamente con los edificios residenciales, así que extraemos sus metadatos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "id": "EHDcPe2dQ9ED"
   },
   "outputs": [],
   "source": [
    "residential_meta = ps.sqldf(\"select * from b_metadata where primary_use = 'Lodging/residential'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gi-F703aEyEp",
    "outputId": "2cf4c74c-e588-4e49-e783-507a67c7306f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "site_id  building_id  primary_use  square_feet  year_built  floor_count\n",
       "False    False        False        False        False       True           71\n",
       "                                                True        True           58\n",
       "                                                False       False          14\n",
       "                                                True        False           4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "residential_meta.isna().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "akR7zMeaIDex"
   },
   "source": [
    "Vemos que en la mayor parte de los registros, aunque no toda, se cuenta con la información de número de pisos. Sin embargo todos contienen la información del área en pies cuadrados, la cual corresponde al área bruta del edificio. \n",
    "Resulta intuitivo pensar que el área bruta depende proporcionalmente del número de pisos que tiene el edificio en cuestión. De tal manera, no se considerará la información del número de pisos.\n",
    "\n",
    "Para el caso del año en que se construyó el edificio en cuestión vemos que un 57% de los registros si contienen dicha información y el otro 43% no De tal forma es posible conservar dicha información y los valores nulos mandarlos a 0. \n",
    "Es también razonable pensar que por más antiguo que sea un edificio, es normal que reciban ciertas remodelaciones en las distintas instalaciones, sin querer decir que por ser más antiguos tendrán un consumo distinto a los más modernos. Por lo mismo decidimos dejar atrás también dicha información."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "PIGgMulqeMKM"
   },
   "outputs": [],
   "source": [
    "residential_meta = residential_meta.drop( columns=['floor_count', 'year_built'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Cn48eguTR3RF"
   },
   "source": [
    "Comprobamos como luce ahora el dataframe con los metadatos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 198
    },
    "id": "f9TooNFDMI1B",
    "outputId": "ed65aedd-6850-4117-84b9-c4ba29cf92fd"
   },
   "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>site_id</th>\n",
       "      <th>building_id</th>\n",
       "      <th>primary_use</th>\n",
       "      <th>square_feet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>37100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>59200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>38686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>Lodging/residential</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   site_id  building_id          primary_use  square_feet\n",
       "0        0            6  Lodging/residential        27926\n",
       "1        0           12  Lodging/residential        37100\n",
       "2        0           27  Lodging/residential        59200\n",
       "3        0           33  Lodging/residential        38686\n",
       "4        0           34  Lodging/residential        42731"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "residential_meta.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "W1D6NkcDumx4"
   },
   "outputs": [],
   "source": [
    "residential_meta.to_csv('./residential_metadata.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qtff9BR7SRqB"
   },
   "source": [
    "### 1.1.2 Datos del clima de entrenamiento\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 630
    },
    "id": "FDO4WJrOSpQ-",
    "outputId": "23eee3bb-8bbe-4086-d0cf-12a5d16a7996"
   },
   "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>site_id</th>\n",
       "      <th>air_temperature</th>\n",
       "      <th>cloud_coverage</th>\n",
       "      <th>dew_temperature</th>\n",
       "      <th>precip_depth_1_hr</th>\n",
       "      <th>sea_level_pressure</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-01 00:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 01:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>24.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.1</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1020.2</td>\n",
       "      <td>70.0</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 02:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>21.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1020.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 03:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>21.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1020.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 04:00:00</th>\n",
       "      <td>0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>2.6</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 19:00:00</th>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>180.0</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 20:00:00</th>\n",
       "      <td>15</td>\n",
       "      <td>2.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-8.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1007.4</td>\n",
       "      <td>180.0</td>\n",
       "      <td>7.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 21:00:00</th>\n",
       "      <td>15</td>\n",
       "      <td>2.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-7.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1007.5</td>\n",
       "      <td>180.0</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 22:00:00</th>\n",
       "      <td>15</td>\n",
       "      <td>2.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1008.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>4.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 23:00:00</th>\n",
       "      <td>15</td>\n",
       "      <td>1.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-5.6</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1008.5</td>\n",
       "      <td>180.0</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139773 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     site_id  air_temperature  cloud_coverage  \\\n",
       "timestamp                                                       \n",
       "2016-01-01 00:00:00        0             25.0             6.0   \n",
       "2016-01-01 01:00:00        0             24.4             NaN   \n",
       "2016-01-01 02:00:00        0             22.8             2.0   \n",
       "2016-01-01 03:00:00        0             21.1             2.0   \n",
       "2016-01-01 04:00:00        0             20.0             2.0   \n",
       "...                      ...              ...             ...   \n",
       "2016-12-31 19:00:00       15              3.0             NaN   \n",
       "2016-12-31 20:00:00       15              2.8             2.0   \n",
       "2016-12-31 21:00:00       15              2.8             NaN   \n",
       "2016-12-31 22:00:00       15              2.2             NaN   \n",
       "2016-12-31 23:00:00       15              1.7             NaN   \n",
       "\n",
       "                     dew_temperature  precip_depth_1_hr  sea_level_pressure  \\\n",
       "timestamp                                                                     \n",
       "2016-01-01 00:00:00             20.0                NaN              1019.7   \n",
       "2016-01-01 01:00:00             21.1               -1.0              1020.2   \n",
       "2016-01-01 02:00:00             21.1                0.0              1020.2   \n",
       "2016-01-01 03:00:00             20.6                0.0              1020.1   \n",
       "2016-01-01 04:00:00             20.0               -1.0              1020.0   \n",
       "...                              ...                ...                 ...   \n",
       "2016-12-31 19:00:00             -8.0                NaN                 NaN   \n",
       "2016-12-31 20:00:00             -8.9                NaN              1007.4   \n",
       "2016-12-31 21:00:00             -7.2                NaN              1007.5   \n",
       "2016-12-31 22:00:00             -6.7                NaN              1008.0   \n",
       "2016-12-31 23:00:00             -5.6               -1.0              1008.5   \n",
       "\n",
       "                     wind_direction  wind_speed  \n",
       "timestamp                                        \n",
       "2016-01-01 00:00:00             0.0         0.0  \n",
       "2016-01-01 01:00:00            70.0         1.5  \n",
       "2016-01-01 02:00:00             0.0         0.0  \n",
       "2016-01-01 03:00:00             0.0         0.0  \n",
       "2016-01-01 04:00:00           250.0         2.6  \n",
       "...                             ...         ...  \n",
       "2016-12-31 19:00:00           180.0         5.7  \n",
       "2016-12-31 20:00:00           180.0         7.7  \n",
       "2016-12-31 21:00:00           180.0         5.1  \n",
       "2016-12-31 22:00:00           170.0         4.6  \n",
       "2016-12-31 23:00:00           180.0         8.8  \n",
       "\n",
       "[139773 rows x 8 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_train #previsualización de la tabla"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NdNukmu9U470"
   },
   "source": [
    "Vemos qué tantos valores nulos tenemos por cada uno de los sitios y nos enfrentamos al problema de cómo llenar los valores faltantes.\n",
    "\n",
    "El siguiente análisis pretende resolver dicha interrogante para tomar decisiones al respecto. El análisis se enfoca principalmente en las variables climáticas de nubosidad y precipitación, pues son las más ausentes en los datos como se comprueba a continuación."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "lynSmq-bXK40"
   },
   "outputs": [],
   "source": [
    "sitios = ps.sqldf('select site_id from weather_train group by site_id') #obtenemos un df con los sitios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "OG4ZHWbyUFHB",
    "outputId": "baf4bbab-19fa-404a-81fe-7e442ef955f8"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------\n",
      "Sitio 0\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "El 100.00% de los valores existentes son 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.97% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 56.40% de los datos.\n",
      "El 17.76% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.31.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.97% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.99% de los datos.\n",
      "El 90.49% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.91.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.03% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 97.15% de los datos.\n",
      "El 12.01% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 11.67% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 1\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.99% de los datos.\n",
      "El 0.09% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 19.41% de los datos.\n",
      "El 99.06% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 5.10.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.99% de los datos.\n",
      "El 0.31% de los valores existentes son 0.\n",
      "\n",
      " precip_depth_1_hr esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.41% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 99.97% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 2\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.99% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 73.20% de los datos.\n",
      "El 37.33% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.51.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.99% de los datos.\n",
      "El 2.81% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.25% de los datos.\n",
      "El 97.49% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.98.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.50% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 93.27% de los datos.\n",
      "El 14.62% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.94% de los datos.\n",
      "El 13.65% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 3\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.95% de los datos.\n",
      "El 0.75% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 58.52% de los datos.\n",
      "El 17.87% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.31.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.93% de los datos.\n",
      "El 1.45% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.58% de los datos.\n",
      "El 87.25% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.88.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 98.20% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 98.26% de los datos.\n",
      "El 7.07% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.95% de los datos.\n",
      "El 6.95% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 4\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 51.84% de los datos.\n",
      "El 25.98% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.50.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.98% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 85.01% de los datos.\n",
      "El 92.35% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.09.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.17% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 98.80% de los datos.\n",
      "El 11.79% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 11.65% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 5\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.98% de los datos.\n",
      "El 0.47% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 31.13% de los datos.\n",
      "El 96.77% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 3.11.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.98% de los datos.\n",
      "El 2.97% de los valores existentes son 0.\n",
      "\n",
      " precip_depth_1_hr esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " sea_level_pressure esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " Para wind_direction SE TIENE el 96.63% de los datos.\n",
      "El 0.07% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.97% de los datos.\n",
      "El 0.07% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 6\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.89% de los datos.\n",
      "El 0.84% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 65.93% de los datos.\n",
      "El 81.64% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.24.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.89% de los datos.\n",
      "El 1.46% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 100.00% de los datos.\n",
      "El 88.99% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.89.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 98.01% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 91.13% de los datos.\n",
      "El 30.39% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.56% de los datos.\n",
      "El 27.82% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 7\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 100.00% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " cloud_coverage esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.73% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 8.49% de los datos.\n",
      "No tiene valores en 0.\n",
      "El valor faltante es  probablemente 0.\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.91% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 2.24% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 8\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.97% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 56.40% de los datos.\n",
      "El 17.76% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.31.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.97% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.99% de los datos.\n",
      "El 90.49% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.91.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.03% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 97.15% de los datos.\n",
      "El 12.01% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 11.67% de los valores existentes son 0.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------\n",
      "Sitio 9\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.94% de los datos.\n",
      "El 0.07% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 60.62% de los datos.\n",
      "El 79.26% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.31.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.92% de los datos.\n",
      "El 1.11% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.92% de los datos.\n",
      "El 91.55% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.92.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 97.28% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 70.87% de los datos.\n",
      "El 33.88% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 98.90% de los datos.\n",
      "El 24.28% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 10\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.73% de los datos.\n",
      "El 1.75% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 72.10% de los datos.\n",
      "El 89.73% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.24.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.69% de los datos.\n",
      "El 5.01% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.69% de los datos.\n",
      "El 90.19% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.90.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 98.39% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 93.92% de los datos.\n",
      "El 12.10% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 98.82% de los datos.\n",
      "El 11.50% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 11\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 100.00% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " cloud_coverage esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.73% de los datos.\n",
      "El 0.30% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 8.49% de los datos.\n",
      "No tiene valores en 0.\n",
      "El valor faltante es  probablemente 0.\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.91% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 2.24% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 12\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 100.00% de los datos.\n",
      "El 0.15% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 99.33% de los datos.\n",
      "El 0.54% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.01.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 100.00% de los datos.\n",
      "El 0.46% de los valores existentes son 0.\n",
      "\n",
      " precip_depth_1_hr esta VACIO.\n",
      "No hay medidor.\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 99.36% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 99.99% de los datos.\n",
      "El 0.73% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 100.00% de los datos.\n",
      "El 0.73% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 13\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.99% de los datos.\n",
      "El 1.46% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 51.06% de los datos.\n",
      "El 31.22% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.61.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.99% de los datos.\n",
      "El 2.08% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.86% de los datos.\n",
      "El 85.32% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.85.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 98.63% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 97.53% de los datos.\n",
      "El 6.26% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.99% de los datos.\n",
      "El 6.11% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 14\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 99.99% de los datos.\n",
      "El 1.45% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 62.28% de los datos.\n",
      "El 83.17% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.34.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.99% de los datos.\n",
      "El 1.73% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 99.65% de los datos.\n",
      "El 88.81% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 0.89.\n",
      "\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 98.75% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 96.70% de los datos.\n",
      "El 13.95% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.74% de los datos.\n",
      "El 13.53% de los valores existentes son 0.\n",
      "--------------------------------------------------\n",
      "Sitio 15\n",
      "\n",
      "\n",
      " Para timestamp SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para site_id SE TIENE el 100.00% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para air_temperature SE TIENE el 100.00% de los datos.\n",
      "El 1.70% de los valores existentes son 0.\n",
      "\n",
      " Para cloud_coverage SE TIENE el 47.97% de los datos.\n",
      "El 65.80% de los valores existentes son 0.\n",
      "Probabilidad de que el valor faltante sea una falla y no 0 es: 1.37.\n",
      "\n",
      "\n",
      " Para dew_temperature SE TIENE el 99.96% de los datos.\n",
      "El 2.17% de los valores existentes son 0.\n",
      "\n",
      " Para precip_depth_1_hr SE TIENE el 20.14% de los datos.\n",
      "No tiene valores en 0.\n",
      "El valor faltante es  probablemente 0.\n",
      "\n",
      " Para sea_level_pressure SE TIENE el 93.98% de los datos.\n",
      "No tiene valores en 0.\n",
      "\n",
      " Para wind_direction SE TIENE el 97.14% de los datos.\n",
      "El 11.19% de los valores existentes son 0.\n",
      "\n",
      " Para wind_speed SE TIENE el 99.67% de los datos.\n",
      "El 10.92% de los valores existentes son 0.\n"
     ]
    }
   ],
   "source": [
    "for sitio in sitios.site_id:\n",
    "    local_weather = ps.sqldf(\"select * from weather_train where site_id = {}\".format(sitio))\n",
    "    print('-'*50)\n",
    "    print('Sitio {}\\n'.format(sitio))\n",
    "    for attribute in local_weather.columns:\n",
    "        try:\n",
    "            percentage = int(local_weather[attribute].isna().value_counts().loc[False])*100/local_weather.shape[0]\n",
    "            print('\\n Para {} SE TIENE el {:.2f}% de los datos.'.format(attribute, percentage))\n",
    "            try:\n",
    "                zeros = int(local_weather[attribute].value_counts().loc[0.0])*100/local_weather[attribute].isna().value_counts().loc[False]\n",
    "                print('El {:.2f}% de los valores existentes son 0.'.format(zeros))\n",
    "                if attribute == 'precip_depth_1_hr' or attribute == 'cloud_coverage':\n",
    "                    print('Probabilidad de que el valor faltante sea una falla y no 0 es: {:.2f}.\\n'.format(zeros/percentage))\n",
    "            except:\n",
    "                print('No tiene valores en 0.')\n",
    "                if attribute == 'precip_depth_1_hr' or attribute == 'cloud_coverage':\n",
    "                    print('El valor faltante es  probablemente 0.')\n",
    "        except:\n",
    "            percentage = int(local_weather[attribute].isna().value_counts().loc[True])*100/8784\n",
    "            print('\\n {} esta VACIO.'.format(attribute))\n",
    "            print('No hay medidor.')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XKjD42D-WnEh"
   },
   "source": [
    "Se observa que en la mayor parte de los sitios hacen falta los datos de covertura de nubes y de precipitación. Debido a que no es enteramente claro el motivo del porqué faltan dichas cantidades de datos ni se indican en la [página de la competencia](https://www.kaggle.com/c/ashrae-energy-prediction/overview), hemos decidido dejar atras dicha información, mientras que para el sitio 5, que no tiene medidor para la presión atmosférica, se rellenarán los valores con 0 y se agregará una columna adicional que indique para cada uno de los sitios si se contaba con dicha información con un 1 y si no, con un 0. \n",
    "\n",
    "Los valores faltantes de las demás columnas se completan por medio de interpolaciones.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "id": "C5P8pb1wUlGU"
   },
   "outputs": [],
   "source": [
    "#se ajusta el dataframe con las fechas para cada uno de los sitios\n",
    "weather_train = pd.read_csv('./weather_train.csv', parse_dates=True, index_col='timestamp')\n",
    "weather_train = weather_train.drop( columns=['cloud_coverage', 'precip_depth_1_hr'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "TmHM2jVxKcBh",
    "outputId": "b8b1b09b-241f-41eb-98f4-38f74bfa86a3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Limpiando datos del clima para el sitio 0\n",
      "Limpiando datos del clima para el sitio 1\n",
      "Limpiando datos del clima para el sitio 2\n",
      "Limpiando datos del clima para el sitio 3\n",
      "Limpiando datos del clima para el sitio 4\n",
      "Limpiando datos del clima para el sitio 5\n",
      "Limpiando datos del clima para el sitio 6\n",
      "Limpiando datos del clima para el sitio 7\n",
      "Limpiando datos del clima para el sitio 8\n",
      "Limpiando datos del clima para el sitio 9\n",
      "Limpiando datos del clima para el sitio 10\n",
      "Limpiando datos del clima para el sitio 11\n",
      "Limpiando datos del clima para el sitio 12\n",
      "Limpiando datos del clima para el sitio 13\n",
      "Limpiando datos del clima para el sitio 14\n",
      "Limpiando datos del clima para el sitio 15\n"
     ]
    }
   ],
   "source": [
    "weather_clean_train = pd.DataFrame()\n",
    "for sitio in sitios.site_id:\n",
    "    print('Limpiando datos del clima para el sitio {}'.format(sitio))\n",
    "    local_weather = ps.sqldf(\"select * from weather_train where site_id = {}\".format(sitio)) #read df for each site\n",
    "    local_weather = local_weather.replace(-1,np.nan)\n",
    "    local_weather['timestamp'] = pd.to_datetime(local_weather['timestamp'], infer_datetime_format=True) #parse dates\n",
    "    local_weather = local_weather.set_index('timestamp') #date as index\n",
    "\n",
    "    local_weather = local_weather.asfreq('H') #set hourly frequency to dataframe\n",
    "\n",
    "    #atributo que indica si funciona bien o no el medidor de presión\n",
    "    if sitio == 5 :\n",
    "        local_weather['pressure_meter'] = np.zeros(local_weather.shape[0]).tolist()\n",
    "    else:\n",
    "        local_weather['pressure_meter'] = np.ones(local_weather.shape[0]).tolist()\n",
    "\n",
    "\n",
    "    #se llenan con 0 los datos del medidor de pressión para el sitio 5.\n",
    "    if sitio == 5:\n",
    "        local_weather['sea_level_pressure'] = local_weather['sea_level_pressure'].fillna(0.0)\n",
    "\n",
    "    #se interpolan el resto de los datos\n",
    "    local_weather = local_weather.interpolate(method='time', limit_direction='both')\n",
    "    #se vuelven a juntar los datos del clima limpios para cada uno de los sitios\n",
    "    weather_clean_train = pd.concat([weather_clean_train, local_weather])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TTdhauKrjnvY"
   },
   "source": [
    "Se comprueba que la limpia de los datos ha sido correcta."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "8x7BIL0yTVxy",
    "outputId": "69749197-7a13-4ac4-9c69-67ad42032469"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "site_id  air_temperature  dew_temperature  sea_level_pressure  wind_direction  wind_speed  pressure_meter\n",
       "False    False            False            False               False           False       False             140537\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_clean_train.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ch7Xivi4jwUx"
   },
   "source": [
    "A continuación se transforma la variable de la dirección del viento a categórica. Así mismo se agregan como atributos el día de la semana, hora del día, día del mes y el mes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "id": "7qgd6Z3EhQnv"
   },
   "outputs": [],
   "source": [
    "def weekday_categorical_int(data, initial_day = 1):\n",
    "    '''\n",
    "    Create a categorical value for the number of the day of the week. 1 = monday, \n",
    "    2 = tuesday and so on. \n",
    "    '''\n",
    "    day = [initial_day]\n",
    "    \n",
    "    for i in range(data.shape[0] - 1):\n",
    "        if day[i] == 7:\n",
    "            num_day = 0\n",
    "        else:\n",
    "            num_day = day[i]\n",
    "        day.append(num_day + 1)\n",
    "    return day\n",
    "\n",
    "def wind_dir_categorical_int(data):\n",
    "    wind_dir = data['wind_direction'].astype('float32').tolist()\n",
    "    for i in range(len(wind_dir)):\n",
    "        x = wind_dir[i]\n",
    "        if 22.5 <= x < 67.5:\n",
    "            wind_dir[i] = 1\n",
    "        elif 67.5 <= x < 112.5:\n",
    "            wind_dir[i] = 2\n",
    "        elif 112.5 <= x < 157.5:\n",
    "            wind_dir[i] = 3\n",
    "        elif 157.5 <= x < 202.5:\n",
    "            wind_dir[i] = 4\n",
    "        elif 202.5 <= x < 247.5:\n",
    "            wind_dir[i] = 5\n",
    "        elif 247.5 <= x < 292.5:\n",
    "            wind_dir[i] = 6\n",
    "        elif 292.5 <= x < 337.5:\n",
    "            wind_dir[i] = 7\n",
    "        else:\n",
    "            wind_dir[i] = 0\n",
    "    return wind_dir"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "id": "FPXxF3iikuX3"
   },
   "outputs": [],
   "source": [
    "#adding columns with datetime data\n",
    "weather_clean_train['month'] = [d.month for d in weather_clean_train.index]\n",
    "weather_clean_train['day'] = [d.day for d in weather_clean_train.index]\n",
    "weather_clean_train['weekday'] = weekday_categorical_int(weather_clean_train, 5)\n",
    "weather_clean_train['hour'] = [d.hour for d in weather_clean_train.index]\n",
    "#overwriting wind_dir column\n",
    "weather_clean_train['wind_direction'] = wind_dir_categorical_int(weather_clean_train)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "h6k4FWR0lWzy"
   },
   "source": [
    "Sería posible codificar dichas variables categóricas en One Hot como hicimos anteriormente. Sin embargo, para no incrmenetar excesivamente la dimensionalidad de los datos, decidimos dejarla con la codificación actual (por enteros/labels)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 335
    },
    "id": "zl1vKbiFk9EA",
    "outputId": "9e1d8e96-cc82-4298-8c76-e9c3760917b9"
   },
   "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>site_id</th>\n",
       "      <th>air_temperature</th>\n",
       "      <th>dew_temperature</th>\n",
       "      <th>sea_level_pressure</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>pressure_meter</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-01 00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 01:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>24.4</td>\n",
       "      <td>21.1</td>\n",
       "      <td>1020.2</td>\n",
       "      <td>2</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 02:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>21.1</td>\n",
       "      <td>1020.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 03:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>21.1</td>\n",
       "      <td>20.6</td>\n",
       "      <td>1020.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 04:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     site_id  air_temperature  dew_temperature  \\\n",
       "timestamp                                                        \n",
       "2016-01-01 00:00:00      0.0             25.0             20.0   \n",
       "2016-01-01 01:00:00      0.0             24.4             21.1   \n",
       "2016-01-01 02:00:00      0.0             22.8             21.1   \n",
       "2016-01-01 03:00:00      0.0             21.1             20.6   \n",
       "2016-01-01 04:00:00      0.0             20.0             20.0   \n",
       "\n",
       "                     sea_level_pressure  wind_direction  wind_speed  \\\n",
       "timestamp                                                             \n",
       "2016-01-01 00:00:00              1019.7               0         0.0   \n",
       "2016-01-01 01:00:00              1020.2               2         1.5   \n",
       "2016-01-01 02:00:00              1020.2               0         0.0   \n",
       "2016-01-01 03:00:00              1020.1               0         0.0   \n",
       "2016-01-01 04:00:00              1020.0               6         2.6   \n",
       "\n",
       "                     pressure_meter  month  day  weekday  hour  \n",
       "timestamp                                                       \n",
       "2016-01-01 00:00:00             1.0      1    1        5     0  \n",
       "2016-01-01 01:00:00             1.0      1    1        6     1  \n",
       "2016-01-01 02:00:00             1.0      1    1        7     2  \n",
       "2016-01-01 03:00:00             1.0      1    1        1     3  \n",
       "2016-01-01 04:00:00             1.0      1    1        2     4  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_clean_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VaGWyNbOvBWG",
    "outputId": "226373d1-9875-4e8b-8d74-7524e22a9791"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(140537, 11)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_clean_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "id": "nLxlmfb9u5kY"
   },
   "outputs": [],
   "source": [
    "weather_clean_train.to_csv('./weather_clean_train.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5avPXIq2o_LO"
   },
   "source": [
    "### 1.1.4 Test and Train Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "sb4NJqE2y5MK"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandasql as ps\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LI17yjysvQRD"
   },
   "source": [
    "Partiendo de la suposición de que dichos csv están completos, procedemos a la extracción de los datos con los edificios residenciales."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "id": "xRGlUz6kyIJv"
   },
   "outputs": [],
   "source": [
    "meters_train = pd.read_csv('./train.csv')\n",
    "residential_meta = pd.read_csv('./residential_metadata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "id": "qydOZxCqqAbC"
   },
   "outputs": [],
   "source": [
    "buildings_id_res = ps.sqldf(\"select building_id from residential_meta\") #id con los edificios residenciales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 198
    },
    "id": "tFEgMMN1pGU0",
    "outputId": "96c2ce1e-0e19-4428-bd95-f2e00f3b15b9"
   },
   "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>building_id</th>\n",
       "      <th>meter</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>meter_reading</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   building_id  meter            timestamp  meter_reading\n",
       "0            0      0  2016-01-01 00:00:00            0.0\n",
       "1            1      0  2016-01-01 00:00:00            0.0\n",
       "2            2      0  2016-01-01 00:00:00            0.0\n",
       "3            3      0  2016-01-01 00:00:00            0.0\n",
       "4            4      0  2016-01-01 00:00:00            0.0"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meters_train.head() #tabla con información sobre medidores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "id": "50KAeLA3ySUU"
   },
   "outputs": [],
   "source": [
    "#Filtrado de la información de solo los medidores 0 \n",
    "meter_0 = ps.sqldf(\"select * from meters_train where meter = 0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "id": "neXiPT3qyc0K"
   },
   "outputs": [],
   "source": [
    "#Extrayendo únicamente los edificios residenciales\n",
    "meter_0_res = ps.sqldf(\"select * from meter_0 where building_id in (select * from buildings_id_res)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>building_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>electricity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229077</th>\n",
       "      <td>1422</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>9.575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229078</th>\n",
       "      <td>1423</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>9.400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229079</th>\n",
       "      <td>1424</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>16.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229080</th>\n",
       "      <td>1440</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>154.750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229081</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>159.575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1229082 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         building_id            timestamp  electricity\n",
       "0                  6  2016-01-01 00:00:00        0.000\n",
       "1                 12  2016-01-01 00:00:00        0.000\n",
       "2                 27  2016-01-01 00:00:00        0.000\n",
       "3                 33  2016-01-01 00:00:00        0.000\n",
       "4                 34  2016-01-01 00:00:00        0.000\n",
       "...              ...                  ...          ...\n",
       "1229077         1422  2016-12-31 23:00:00        9.575\n",
       "1229078         1423  2016-12-31 23:00:00        9.400\n",
       "1229079         1424  2016-12-31 23:00:00       16.500\n",
       "1229080         1440  2016-12-31 23:00:00      154.750\n",
       "1229081         1447  2016-12-31 23:00:00      159.575\n",
       "\n",
       "[1229082 rows x 3 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meter_0_res['electricity'] = meter_0_res['meter_reading'].values.tolist()\n",
    "e_load =  meter_0_res.drop(columns=['meter', 'meter_reading'])\n",
    "e_load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "id": "ISoAypy3nal1"
   },
   "outputs": [],
   "source": [
    "e_load.to_csv('./electric_load_residential.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kwijIVR9ve8k"
   },
   "source": [
    "## 1.2 Juntando las tablas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ApaxSg3CjjKq"
   },
   "source": [
    "Nos interesa juntar en una sola tabla la información de los medidores, así como los datos del clima para poder insertarlos al sistema como un solo vector de entrada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "id": "j0tUqvpzvk-H"
   },
   "outputs": [],
   "source": [
    "residential_meta = pd.read_csv('./residential_metadata.csv')\n",
    "weather_clean_train = pd.read_csv('./weather_clean_train.csv')\n",
    "electric_load = pd.read_csv('./electric_load_residential.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 407
    },
    "id": "FufMtCcGybc6",
    "outputId": "1765f1fa-bdb3-4b78-a411-fc9c6dfc2ff7"
   },
   "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>site_id</th>\n",
       "      <th>building_id</th>\n",
       "      <th>square_feet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>37100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>59200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>38686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>15</td>\n",
       "      <td>1422</td>\n",
       "      <td>19876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>15</td>\n",
       "      <td>1423</td>\n",
       "      <td>18277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>15</td>\n",
       "      <td>1424</td>\n",
       "      <td>24103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>15</td>\n",
       "      <td>1440</td>\n",
       "      <td>150294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>15</td>\n",
       "      <td>1447</td>\n",
       "      <td>29775</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>147 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     site_id  building_id  square_feet\n",
       "0          0            6        27926\n",
       "1          0           12        37100\n",
       "2          0           27        59200\n",
       "3          0           33        38686\n",
       "4          0           34        42731\n",
       "..       ...          ...          ...\n",
       "142       15         1422        19876\n",
       "143       15         1423        18277\n",
       "144       15         1424        24103\n",
       "145       15         1440       150294\n",
       "146       15         1447        29775\n",
       "\n",
       "[147 rows x 3 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "residential_meta = residential_meta.drop(columns = ['Unnamed: 0','primary_use'])\n",
    "electric_load = electric_load.drop(columns = ['Unnamed: 0'])\n",
    "residential_meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "id": "kjkRIQTqpqwE"
   },
   "outputs": [],
   "source": [
    "#juntando los datos del medidor con los metadatos del edificio.\n",
    "input_data = electric_load.merge(residential_meta, on='building_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 830
    },
    "id": "bFb3PQckshV_",
    "outputId": "944a804c-471c-4e5f-b4e9-1e1dcb8edefb"
   },
   "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>building_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>electricity</th>\n",
       "      <th>site_id</th>\n",
       "      <th>square_feet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229077</th>\n",
       "      <td>647</td>\n",
       "      <td>2016-12-31 19:00:00</td>\n",
       "      <td>19.296</td>\n",
       "      <td>4</td>\n",
       "      <td>86959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229078</th>\n",
       "      <td>647</td>\n",
       "      <td>2016-12-31 20:00:00</td>\n",
       "      <td>20.078</td>\n",
       "      <td>4</td>\n",
       "      <td>86959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229079</th>\n",
       "      <td>647</td>\n",
       "      <td>2016-12-31 21:00:00</td>\n",
       "      <td>19.654</td>\n",
       "      <td>4</td>\n",
       "      <td>86959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229080</th>\n",
       "      <td>647</td>\n",
       "      <td>2016-12-31 22:00:00</td>\n",
       "      <td>19.664</td>\n",
       "      <td>4</td>\n",
       "      <td>86959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229081</th>\n",
       "      <td>647</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>19.548</td>\n",
       "      <td>4</td>\n",
       "      <td>86959</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1229082 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         building_id            timestamp  electricity  site_id  square_feet\n",
       "0                  6  2016-01-01 00:00:00        0.000        0        27926\n",
       "1                  6  2016-01-01 01:00:00        0.000        0        27926\n",
       "2                  6  2016-01-01 02:00:00        0.000        0        27926\n",
       "3                  6  2016-01-01 03:00:00        0.000        0        27926\n",
       "4                  6  2016-01-01 04:00:00        0.000        0        27926\n",
       "...              ...                  ...          ...      ...          ...\n",
       "1229077          647  2016-12-31 19:00:00       19.296        4        86959\n",
       "1229078          647  2016-12-31 20:00:00       20.078        4        86959\n",
       "1229079          647  2016-12-31 21:00:00       19.654        4        86959\n",
       "1229080          647  2016-12-31 22:00:00       19.664        4        86959\n",
       "1229081          647  2016-12-31 23:00:00       19.548        4        86959\n",
       "\n",
       "[1229082 rows x 5 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "input_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 323
    },
    "id": "js-v-ZfGyG2W",
    "outputId": "d41ae25a-f2b2-41b8-9d56-63990bc2a3e8"
   },
   "outputs": [],
   "source": [
    "#juntando los datos del clima con el resto\n",
    "input_data = input_data.merge(weather_clean_train, on = ['timestamp', 'site_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "id": "4PrE_o9X0LMV",
    "scrolled": true
   },
   "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>building_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>electricity</th>\n",
       "      <th>site_id</th>\n",
       "      <th>square_feet</th>\n",
       "      <th>air_temperature</th>\n",
       "      <th>dew_temperature</th>\n",
       "      <th>sea_level_pressure</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>pressure_meter</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>37100</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>59200</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>38686</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0</td>\n",
       "      <td>42731</td>\n",
       "      <td>25.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1019.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229003</th>\n",
       "      <td>1422</td>\n",
       "      <td>2016-06-26 18:00:00</td>\n",
       "      <td>5.300</td>\n",
       "      <td>15</td>\n",
       "      <td>19876</td>\n",
       "      <td>30.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1020.3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229004</th>\n",
       "      <td>1423</td>\n",
       "      <td>2016-06-26 18:00:00</td>\n",
       "      <td>12.725</td>\n",
       "      <td>15</td>\n",
       "      <td>18277</td>\n",
       "      <td>30.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1020.3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229005</th>\n",
       "      <td>1424</td>\n",
       "      <td>2016-06-26 18:00:00</td>\n",
       "      <td>24.250</td>\n",
       "      <td>15</td>\n",
       "      <td>24103</td>\n",
       "      <td>30.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1020.3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229006</th>\n",
       "      <td>1440</td>\n",
       "      <td>2016-06-26 18:00:00</td>\n",
       "      <td>274.600</td>\n",
       "      <td>15</td>\n",
       "      <td>150294</td>\n",
       "      <td>30.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1020.3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229007</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-06-26 18:00:00</td>\n",
       "      <td>157.225</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>30.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1020.3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1229008 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         building_id            timestamp  electricity  site_id  square_feet  \\\n",
       "0                  6  2016-01-01 00:00:00        0.000        0        27926   \n",
       "1                 12  2016-01-01 00:00:00        0.000        0        37100   \n",
       "2                 27  2016-01-01 00:00:00        0.000        0        59200   \n",
       "3                 33  2016-01-01 00:00:00        0.000        0        38686   \n",
       "4                 34  2016-01-01 00:00:00        0.000        0        42731   \n",
       "...              ...                  ...          ...      ...          ...   \n",
       "1229003         1422  2016-06-26 18:00:00        5.300       15        19876   \n",
       "1229004         1423  2016-06-26 18:00:00       12.725       15        18277   \n",
       "1229005         1424  2016-06-26 18:00:00       24.250       15        24103   \n",
       "1229006         1440  2016-06-26 18:00:00      274.600       15       150294   \n",
       "1229007         1447  2016-06-26 18:00:00      157.225       15        29775   \n",
       "\n",
       "         air_temperature  dew_temperature  sea_level_pressure  wind_direction  \\\n",
       "0                   25.0             20.0              1019.7               0   \n",
       "1                   25.0             20.0              1019.7               0   \n",
       "2                   25.0             20.0              1019.7               0   \n",
       "3                   25.0             20.0              1019.7               0   \n",
       "4                   25.0             20.0              1019.7               0   \n",
       "...                  ...              ...                 ...             ...   \n",
       "1229003             30.6             15.6              1020.3               4   \n",
       "1229004             30.6             15.6              1020.3               4   \n",
       "1229005             30.6             15.6              1020.3               4   \n",
       "1229006             30.6             15.6              1020.3               4   \n",
       "1229007             30.6             15.6              1020.3               4   \n",
       "\n",
       "         wind_speed  pressure_meter  month  day  weekday  hour  \n",
       "0               0.0             1.0      1    1        5     0  \n",
       "1               0.0             1.0      1    1        5     0  \n",
       "2               0.0             1.0      1    1        5     0  \n",
       "3               0.0             1.0      1    1        5     0  \n",
       "4               0.0             1.0      1    1        5     0  \n",
       "...             ...             ...    ...  ...      ...   ...  \n",
       "1229003         4.1             1.0      6   26        7    18  \n",
       "1229004         4.1             1.0      6   26        7    18  \n",
       "1229005         4.1             1.0      6   26        7    18  \n",
       "1229006         4.1             1.0      6   26        7    18  \n",
       "1229007         4.1             1.0      6   26        7    18  \n",
       "\n",
       "[1229008 rows x 15 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "input_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>building_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>electricity</th>\n",
       "      <th>site_id</th>\n",
       "      <th>square_feet</th>\n",
       "      <th>air_temperature</th>\n",
       "      <th>dew_temperature</th>\n",
       "      <th>sea_level_pressure</th>\n",
       "      <th>wind_direction</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>pressure_meter</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-01-01 01:00:00</td>\n",
       "      <td>156.650</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>1019.4</td>\n",
       "      <td>7</td>\n",
       "      <td>4.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-01-01 02:00:00</td>\n",
       "      <td>157.575</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>1019.4</td>\n",
       "      <td>7</td>\n",
       "      <td>2.600000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-01-01 03:00:00</td>\n",
       "      <td>154.925</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>1019.4</td>\n",
       "      <td>0</td>\n",
       "      <td>2.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-01-01 04:00:00</td>\n",
       "      <td>156.075</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.111111</td>\n",
       "      <td>1019.4</td>\n",
       "      <td>7</td>\n",
       "      <td>2.155556</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1447</td>\n",
       "      <td>2016-01-01 05:00:00</td>\n",
       "      <td>154.500</td>\n",
       "      <td>15</td>\n",
       "      <td>29775</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.222222</td>\n",
       "      <td>1019.4</td>\n",
       "      <td>7</td>\n",
       "      <td>2.211111</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229003</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 19:00:00</td>\n",
       "      <td>264.970</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>22.8</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1021.7</td>\n",
       "      <td>3</td>\n",
       "      <td>5.700000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229004</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 20:00:00</td>\n",
       "      <td>265.379</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>23.3</td>\n",
       "      <td>8.900000</td>\n",
       "      <td>1021.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229005</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 21:00:00</td>\n",
       "      <td>263.741</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>23.3</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1021.1</td>\n",
       "      <td>3</td>\n",
       "      <td>4.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229006</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 22:00:00</td>\n",
       "      <td>258.827</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>22.8</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1021.1</td>\n",
       "      <td>4</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229007</th>\n",
       "      <td>6</td>\n",
       "      <td>2016-12-31 23:00:00</td>\n",
       "      <td>259.646</td>\n",
       "      <td>0</td>\n",
       "      <td>27926</td>\n",
       "      <td>20.6</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1021.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1229008 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         building_id            timestamp  electricity  site_id  square_feet  \\\n",
       "0               1447  2016-01-01 01:00:00      156.650       15        29775   \n",
       "1               1447  2016-01-01 02:00:00      157.575       15        29775   \n",
       "2               1447  2016-01-01 03:00:00      154.925       15        29775   \n",
       "3               1447  2016-01-01 04:00:00      156.075       15        29775   \n",
       "4               1447  2016-01-01 05:00:00      154.500       15        29775   \n",
       "...              ...                  ...          ...      ...          ...   \n",
       "1229003            6  2016-12-31 19:00:00      264.970        0        27926   \n",
       "1229004            6  2016-12-31 20:00:00      265.379        0        27926   \n",
       "1229005            6  2016-12-31 21:00:00      263.741        0        27926   \n",
       "1229006            6  2016-12-31 22:00:00      258.827        0        27926   \n",
       "1229007            6  2016-12-31 23:00:00      259.646        0        27926   \n",
       "\n",
       "         air_temperature  dew_temperature  sea_level_pressure  wind_direction  \\\n",
       "0                    0.0        -2.000000              1019.4               7   \n",
       "1                    0.0        -2.000000              1019.4               7   \n",
       "2                    0.0        -2.000000              1019.4               0   \n",
       "3                    0.0        -2.111111              1019.4               7   \n",
       "4                    0.0        -2.222222              1019.4               7   \n",
       "...                  ...              ...                 ...             ...   \n",
       "1229003             22.8        10.000000              1021.7               3   \n",
       "1229004             23.3         8.900000              1021.0               4   \n",
       "1229005             23.3        10.000000              1021.1               3   \n",
       "1229006             22.8        10.000000              1021.1               4   \n",
       "1229007             20.6        10.000000              1021.1               0   \n",
       "\n",
       "         wind_speed  pressure_meter  month  day  weekday  hour  \n",
       "0          4.100000             1.0      1    1        5     1  \n",
       "1          2.600000             1.0      1    1        6     2  \n",
       "2          2.100000             1.0      1    1        7     3  \n",
       "3          2.155556             1.0      1    1        1     4  \n",
       "4          2.211111             1.0      1    1        2     5  \n",
       "...             ...             ...    ...  ...      ...   ...  \n",
       "1229003    5.700000             1.0     12   31        6    19  \n",
       "1229004    4.100000             1.0     12   31        7    20  \n",
       "1229005    4.100000             1.0     12   31        1    21  \n",
       "1229006    3.100000             1.0     12   31        2    22  \n",
       "1229007    0.000000             1.0     12   31        3    23  \n",
       "\n",
       "[1229008 rows x 15 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#se ordenan por número de edificio\n",
    "input_ordered = ps.sqldf(\"select * from input_data order by building_id desc, timestamp asc\")\n",
    "input_ordered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_ordered.to_csv('./data_ordered.csv')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "6nKySfnHAkzo",
    "ZJOPZvkxmBnQ",
    "5avPXIq2o_LO"
   ],
   "name": "IA-Kaggle.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}