part-2-wids2022-model2-no-lag.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "26885ba6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:16.815162Z",
     "iopub.status.busy": "2022-03-01T14:57:16.814280Z",
     "iopub.status.idle": "2022-03-01T14:57:53.868824Z",
     "shell.execute_reply": "2022-03-01T14:57:53.869560Z",
     "shell.execute_reply.started": "2022-03-01T14:55:54.560293Z"
    },
    "papermill": {
     "duration": 37.088296,
     "end_time": "2022-03-01T14:57:53.870838",
     "exception": false,
     "start_time": "2022-03-01T14:57:16.782542",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting h2o==3.34.0.8\r\n",
      "  Downloading h2o-3.34.0.8.tar.gz (175.8 MB)\r\n",
      "     |████████████████████████████████| 175.8 MB 11 kB/s              \r\n",
      "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l-\b \bdone\r\n",
      "\u001b[?25hRequirement already satisfied: requests in /opt/conda/lib/python3.7/site-packages (from h2o==3.34.0.8) (2.26.0)\r\n",
      "Requirement already satisfied: tabulate in /opt/conda/lib/python3.7/site-packages (from h2o==3.34.0.8) (0.8.9)\r\n",
      "Requirement already satisfied: future in /opt/conda/lib/python3.7/site-packages (from h2o==3.34.0.8) (0.18.2)\r\n",
      "Requirement already satisfied: idna<4,>=2.5 in /opt/conda/lib/python3.7/site-packages (from requests->h2o==3.34.0.8) (3.1)\r\n",
      "Requirement already satisfied: charset-normalizer~=2.0.0 in /opt/conda/lib/python3.7/site-packages (from requests->h2o==3.34.0.8) (2.0.9)\r\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /opt/conda/lib/python3.7/site-packages (from requests->h2o==3.34.0.8) (1.26.7)\r\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /opt/conda/lib/python3.7/site-packages (from requests->h2o==3.34.0.8) (2021.10.8)\r\n",
      "Building wheels for collected packages: h2o\r\n",
      "  Building wheel for h2o (setup.py) ... \u001b[?25l-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \bdone\r\n",
      "\u001b[?25h  Created wheel for h2o: filename=h2o-3.34.0.8-py2.py3-none-any.whl size=175904398 sha256=3719ff08a06dc7fd38951ff57349506e36e6fa57190fb85ff88b720b8f2e95f2\r\n",
      "  Stored in directory: /root/.cache/pip/wheels/f0/2e/33/4366d488c23b0c08ae68accd8b26612a3dc59c84bdb13a9383\r\n",
      "Successfully built h2o\r\n",
      "Installing collected packages: h2o\r\n",
      "  Attempting uninstall: h2o\r\n",
      "    Found existing installation: h2o 3.36.0.2\r\n",
      "    Uninstalling h2o-3.36.0.2:\r\n",
      "      Successfully uninstalled h2o-3.36.0.2\r\n",
      "Successfully installed h2o-3.34.0.8\r\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\r\n"
     ]
    }
   ],
   "source": [
    "!pip install h2o==3.34.0.8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "574655ec",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:54.310371Z",
     "iopub.status.busy": "2022-03-01T14:57:54.307448Z",
     "iopub.status.idle": "2022-03-01T14:57:54.820827Z",
     "shell.execute_reply": "2022-03-01T14:57:54.820044Z",
     "shell.execute_reply.started": "2022-03-01T14:56:28.530560Z"
    },
    "papermill": {
     "duration": 0.745244,
     "end_time": "2022-03-01T14:57:54.820977",
     "exception": false,
     "start_time": "2022-03-01T14:57:54.075733",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "import h2o\n",
    "from h2o.automl import H2OAutoML\n",
    "SEED=6789"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f55e407",
   "metadata": {
    "papermill": {
     "duration": 0.144,
     "end_time": "2022-03-01T14:57:55.096076",
     "exception": false,
     "start_time": "2022-03-01T14:57:54.952076",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Pre Processing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09d6bdfc",
   "metadata": {
    "papermill": {
     "duration": 0.102507,
     "end_time": "2022-03-01T14:57:55.309051",
     "exception": false,
     "start_time": "2022-03-01T14:57:55.206544",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "First we applied some post processing using formula : EUI = previousEUI - previous*(energy_star_rating - previous_energy_star_rating)/150"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "53870299",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:55.508449Z",
     "iopub.status.busy": "2022-03-01T14:57:55.507791Z",
     "iopub.status.idle": "2022-03-01T14:57:56.606150Z",
     "shell.execute_reply": "2022-03-01T14:57:56.606612Z",
     "shell.execute_reply.started": "2022-03-01T14:56:46.695194Z"
    },
    "papermill": {
     "duration": 1.201383,
     "end_time": "2022-03-01T14:57:56.606789",
     "exception": false,
     "start_time": "2022-03-01T14:57:55.405406",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "train=pd.read_csv(\"../input/widsdatathon2022/train.csv\")\n",
    "test=pd.read_csv(\"../input/widsdatathon2022/test.csv\")\n",
    "sub=pd.read_csv('../input/part-1-wids2022-model-with-lag/submission_automl1.csv')\n",
    "\n",
    "df=train\n",
    "cols1=['State_Factor','building_class','facility_type','floor_area','year_built']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c09d46ef",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:56.809644Z",
     "iopub.status.busy": "2022-03-01T14:57:56.808683Z",
     "iopub.status.idle": "2022-03-01T14:57:57.152762Z",
     "shell.execute_reply": "2022-03-01T14:57:57.153257Z",
     "shell.execute_reply.started": "2022-03-01T14:56:47.379385Z"
    },
    "papermill": {
     "duration": 0.445176,
     "end_time": "2022-03-01T14:57:57.153440",
     "exception": false,
     "start_time": "2022-03-01T14:57:56.708264",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:11: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  # This is added back by InteractiveShellApp.init_path()\n"
     ]
    },
    {
     "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>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>days_above_100F</th>\n",
       "      <th>days_above_110F</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "      <th>id</th>\n",
       "      <th>site_eui_cols1</th>\n",
       "      <th>Year_Factor_cols1</th>\n",
       "      <th>energy_star_rating_cols1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>28484.0</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75757</td>\n",
       "      <td>168.039617</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>21906.0</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75758</td>\n",
       "      <td>272.553279</td>\n",
       "      <td>6.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>16138.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>59.1</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75759</td>\n",
       "      <td>151.983607</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>97422.0</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>35.4</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75760</td>\n",
       "      <td>264.375683</td>\n",
       "      <td>6.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>61242.0</td>\n",
       "      <td>1942.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>126.0</td>\n",
       "      <td>75761</td>\n",
       "      <td>242.535519</td>\n",
       "      <td>6.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Factor State_Factor building_class                 facility_type  \\\n",
       "0            7      State_1     Commercial  Grocery_store_or_food_market   \n",
       "1            7      State_1     Commercial  Grocery_store_or_food_market   \n",
       "2            7      State_1     Commercial  Grocery_store_or_food_market   \n",
       "3            7      State_1     Commercial  Grocery_store_or_food_market   \n",
       "4            7      State_1     Commercial  Grocery_store_or_food_market   \n",
       "\n",
       "   floor_area  year_built  energy_star_rating  ELEVATION  january_min_temp  \\\n",
       "0     28484.0      1994.0                37.0        2.4                38   \n",
       "1     21906.0      1961.0                55.0       45.7                38   \n",
       "2     16138.0      1950.0                 1.0       59.1                38   \n",
       "3     97422.0      1971.0                34.0       35.4                38   \n",
       "4     61242.0      1942.0                35.0        1.8                38   \n",
       "\n",
       "   january_avg_temp  ...  days_above_100F  days_above_110F  \\\n",
       "0         50.596774  ...                2                0   \n",
       "1         50.596774  ...                2                0   \n",
       "2         50.596774  ...                2                0   \n",
       "3         50.596774  ...                2                0   \n",
       "4         50.596774  ...                2                0   \n",
       "\n",
       "   direction_max_wind_speed  direction_peak_wind_speed  max_wind_speed  \\\n",
       "0                       NaN                        NaN             NaN   \n",
       "1                       NaN                        NaN             NaN   \n",
       "2                       NaN                        NaN             NaN   \n",
       "3                       NaN                        NaN             NaN   \n",
       "4                     340.0                      330.0            22.8   \n",
       "\n",
       "   days_with_fog     id  site_eui_cols1  Year_Factor_cols1  \\\n",
       "0            NaN  75757      168.039617                6.0   \n",
       "1            NaN  75758      272.553279                6.0   \n",
       "2            NaN  75759      151.983607                6.0   \n",
       "3            NaN  75760      264.375683                6.0   \n",
       "4          126.0  75761      242.535519                6.0   \n",
       "\n",
       "   energy_star_rating_cols1  \n",
       "0                       NaN  \n",
       "1                      48.0  \n",
       "2                       1.0  \n",
       "3                      32.0  \n",
       "4                      34.0  \n",
       "\n",
       "[5 rows x 66 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=df.sort_values(by=cols1+['Year_Factor'])\n",
    "df['site_eui_lag1']=df.groupby(cols1)['site_eui'].shift(1)\n",
    "df['Year_Factor_lag1']=df.groupby(cols1)['Year_Factor'].shift(1)\n",
    "df['energy_star_rating_lag1']=df.groupby(cols1)['energy_star_rating'].shift(1)\n",
    "\n",
    "df['delta_energy'] = (df['energy_star_rating']-df['energy_star_rating_lag1'] ) / df['energy_star_rating']\n",
    "df['delta_energy2'] = (df['energy_star_rating']-df['energy_star_rating_lag1'] ) / 100\n",
    "df['delta_site_eui'] = (df['site_eui']-df['site_eui_lag1'] ) /df['site_eui']\n",
    "df['delta_Year_Factor'] = (df['Year_Factor']-df['Year_Factor_lag1'] ) \n",
    "\n",
    "toto1=df[cols1+['site_eui','Year_Factor','energy_star_rating']].groupby(cols1)['site_eui','Year_Factor','energy_star_rating'].last().reset_index().rename(columns={'site_eui':'site_eui_cols1',\n",
    "                                                                                                                       'Year_Factor':'Year_Factor_cols1',\n",
    "                                                                                                                        'energy_star_rating':'energy_star_rating_cols1'})\n",
    "\n",
    "test=test.merge(toto1,on=cols1,how=\"left\")\n",
    "\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fa081c50",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:57.362699Z",
     "iopub.status.busy": "2022-03-01T14:57:57.361782Z",
     "iopub.status.idle": "2022-03-01T14:57:57.366852Z",
     "shell.execute_reply": "2022-03-01T14:57:57.367329Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.157240Z"
    },
    "papermill": {
     "duration": 0.109329,
     "end_time": "2022-03-01T14:57:57.367494",
     "exception": false,
     "start_time": "2022-03-01T14:57:57.258165",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "test['site_eui_sub']=sub['site_eui']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f0869ed5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:57.561663Z",
     "iopub.status.busy": "2022-03-01T14:57:57.560709Z",
     "iopub.status.idle": "2022-03-01T14:57:57.567529Z",
     "shell.execute_reply": "2022-03-01T14:57:57.568065Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.166046Z"
    },
    "papermill": {
     "duration": 0.106672,
     "end_time": "2022-03-01T14:57:57.568234",
     "exception": false,
     "start_time": "2022-03-01T14:57:57.461562",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "test['site_eui_cols1_updated'] = np.where((test['energy_star_rating'].notnull() ) & (test['energy_star_rating_cols1'].notnull() ) ,\n",
    "                                           test['site_eui_cols1'] - test['site_eui_cols1'] * ( test['energy_star_rating'] - test['energy_star_rating_cols1'] ) / 150 ,\n",
    "                                         test['site_eui_cols1'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2bfd6be3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:57.770767Z",
     "iopub.status.busy": "2022-03-01T14:57:57.769619Z",
     "iopub.status.idle": "2022-03-01T14:57:57.785671Z",
     "shell.execute_reply": "2022-03-01T14:57:57.785105Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.181084Z"
    },
    "papermill": {
     "duration": 0.118505,
     "end_time": "2022-03-01T14:57:57.785844",
     "exception": false,
     "start_time": "2022-03-01T14:57:57.667339",
     "status": "completed"
    },
    "tags": []
   },
   "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>id</th>\n",
       "      <th>site_eui_sub</th>\n",
       "      <th>site_eui_cols1</th>\n",
       "      <th>site_eui_cols1_updated</th>\n",
       "      <th>Year_Factor_cols1</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>energy_star_rating_cols1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>75757</td>\n",
       "      <td>163.202741</td>\n",
       "      <td>168.039617</td>\n",
       "      <td>168.039617</td>\n",
       "      <td>6.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>75758</td>\n",
       "      <td>258.325591</td>\n",
       "      <td>272.553279</td>\n",
       "      <td>259.834126</td>\n",
       "      <td>6.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75759</td>\n",
       "      <td>173.828553</td>\n",
       "      <td>151.983607</td>\n",
       "      <td>151.983607</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>75760</td>\n",
       "      <td>262.820162</td>\n",
       "      <td>264.375683</td>\n",
       "      <td>260.850674</td>\n",
       "      <td>6.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>75761</td>\n",
       "      <td>247.748933</td>\n",
       "      <td>242.535519</td>\n",
       "      <td>240.918616</td>\n",
       "      <td>6.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  site_eui_sub  site_eui_cols1  site_eui_cols1_updated  \\\n",
       "0  75757    163.202741      168.039617              168.039617   \n",
       "1  75758    258.325591      272.553279              259.834126   \n",
       "2  75759    173.828553      151.983607              151.983607   \n",
       "3  75760    262.820162      264.375683              260.850674   \n",
       "4  75761    247.748933      242.535519              240.918616   \n",
       "\n",
       "   Year_Factor_cols1  energy_star_rating  energy_star_rating_cols1  \n",
       "0                6.0                37.0                       NaN  \n",
       "1                6.0                55.0                      48.0  \n",
       "2                6.0                 1.0                       1.0  \n",
       "3                6.0                34.0                      32.0  \n",
       "4                6.0                35.0                      34.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test[['id','site_eui_sub','site_eui_cols1','site_eui_cols1_updated','Year_Factor_cols1','energy_star_rating','energy_star_rating_cols1']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "09c47470",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:57.983313Z",
     "iopub.status.busy": "2022-03-01T14:57:57.982404Z",
     "iopub.status.idle": "2022-03-01T14:57:57.991646Z",
     "shell.execute_reply": "2022-03-01T14:57:57.992243Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.211712Z"
    },
    "papermill": {
     "duration": 0.109909,
     "end_time": "2022-03-01T14:57:57.992407",
     "exception": false,
     "start_time": "2022-03-01T14:57:57.882498",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.0    8488\n",
       "5.0     431\n",
       "4.0      52\n",
       "3.0      13\n",
       "1.0       3\n",
       "2.0       2\n",
       "Name: Year_Factor_cols1, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['Year_Factor_cols1'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bba34b97",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:58.196223Z",
     "iopub.status.busy": "2022-03-01T14:57:58.195211Z",
     "iopub.status.idle": "2022-03-01T14:57:58.203079Z",
     "shell.execute_reply": "2022-03-01T14:57:58.203551Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.223812Z"
    },
    "papermill": {
     "duration": 0.112688,
     "end_time": "2022-03-01T14:57:58.203714",
     "exception": false,
     "start_time": "2022-03-01T14:57:58.091026",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "test['site_eui']=np.where((test['site_eui_cols1'].notnull() ) & \n",
    "                          (test['Year_Factor_cols1']>3 ) & \n",
    "                          (test['Year_Factor_cols1'] <6) , \n",
    "                           test['site_eui_cols1_updated']*0.5 + test['site_eui_sub']*0.5  ,\n",
    "                np.where( (test['site_eui_cols1'].notnull()) &\n",
    "                          (test['Year_Factor_cols1'] >5) ,\n",
    "                           test['site_eui_cols1_updated']*0.7+test['site_eui_sub']*0.3 ,\n",
    "                           test['site_eui_sub'] ))\n",
    "\n",
    "sub_pp = test[['id','site_eui']].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "625da042",
   "metadata": {
    "papermill": {
     "duration": 0.1074,
     "end_time": "2022-03-01T14:57:58.412202",
     "exception": false,
     "start_time": "2022-03-01T14:57:58.304802",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Prediction data with no previous value using some pseudo label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6b4c6dc8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:57:58.647763Z",
     "iopub.status.busy": "2022-03-01T14:57:58.647148Z",
     "iopub.status.idle": "2022-03-01T14:58:00.220586Z",
     "shell.execute_reply": "2022-03-01T14:58:00.220140Z",
     "shell.execute_reply.started": "2022-03-01T14:56:48.241630Z"
    },
    "papermill": {
     "duration": 1.68376,
     "end_time": "2022-03-01T14:58:00.220723",
     "exception": false,
     "start_time": "2022-03-01T14:57:58.536963",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(75757, 64) before dedup\n",
      "(75718, 64) after dedup\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:13: FutureWarning: In a future version of pandas all arguments of concat except for the argument 'objs' will be keyword-only\n",
      "  del sys.path[0]\n",
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:59: FutureWarning: Index.ravel returning ndarray is deprecated; in a future version this will return a view on self.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(85423, 82)\n",
      "(85423, 93)\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_csv('../input/widsdatathon2022/train.csv')\n",
    "print(train.shape ,'before dedup')\n",
    "cols4dup = [ c for c in train.columns if c not in ['Year_Factor', 'id'] ]\n",
    "train = train[~train.duplicated(subset=cols4dup,keep='first')]\n",
    "print(train.shape ,'after dedup')\n",
    "\n",
    "test = pd.read_csv('../input/widsdatathon2022/test.csv')\n",
    "test = test.merge(sub_pp,on='id',how='left')\n",
    "\n",
    "cols1=['State_Factor','building_class','facility_type','floor_area','year_built']\n",
    "train['source'] = 'train'\n",
    "test['source']  = 'test'\n",
    "df = pd.concat([train,test],0,ignore_index=True)\n",
    "df=df.sort_values(by=cols1+['Year_Factor'])\n",
    "\n",
    "# agregate by meteo station so same location \n",
    "min_temp = [col for col in df.columns if 'min_temp' in col]\n",
    "max_temp = [col for col in df.columns if 'max_temp' in col]\n",
    "avg_temp = [col for col in df.columns if 'avg_temp' in col]\n",
    "\n",
    "df['min_temp1'] = df[min_temp].min(axis=1)\n",
    "df['max_temp1'] = df[max_temp].min(axis=1)\n",
    "df['avg_temp1'] = df[avg_temp].min(axis=1)\n",
    "\n",
    "df['min_temp2'] = df[min_temp].max(axis=1)\n",
    "df['max_temp2'] = df[max_temp].max(axis=1)\n",
    "df['avg_temp2'] = df[avg_temp].max(axis=1)\n",
    "\n",
    "df['min_temp3'] = df[min_temp].mean(axis=1)\n",
    "df['max_temp3'] = df[max_temp].mean(axis=1)\n",
    "df['avg_temp3'] = df[avg_temp].mean(axis=1)\n",
    "\n",
    "df['min_temp4'] = df[min_temp].std(axis=1)\n",
    "df['max_temp4'] = df[max_temp].std(axis=1)\n",
    "df['avg_temp4'] = df[avg_temp].std(axis=1)\n",
    "\n",
    "df['min_temp5'] = df[min_temp].skew(axis=1)\n",
    "df['max_temp5'] = df[max_temp].skew(axis=1)\n",
    "df['avg_temp5'] = df[avg_temp].skew(axis=1)\n",
    "\n",
    "\n",
    "cols1=['State_Factor','building_class','facility_type','floor_area','year_built']\n",
    "\n",
    "# lag from current values (just to identify row with no lag)\n",
    "field_liste=['site_eui']\n",
    "for i in [1] :\n",
    "    for field in field_liste : \n",
    "        df[f'{field}_lag{i}'] = df.groupby(cols1)[field].shift(i)\n",
    "        \n",
    "#extra features\n",
    "df['energy_star_rating_bin'] = pd.qcut(df['energy_star_rating'],5)\n",
    "\n",
    "grp1 = df.groupby(min_temp,as_index=True).agg({\n",
    "            \"floor_area\": ['mean', 'max','min','sum']  ,\n",
    "            \"year_built\": ['mean', 'max','min'] ,\n",
    "            \"energy_star_rating\" : ['min','max','mean', ] ,\n",
    "            \"id\" : \"count\"\n",
    "            })\n",
    "grp1.columns = [\"_\".join(x) for x in grp1.columns.ravel()  ]\n",
    "print(df.shape)\n",
    "df = df.merge(grp1,on=min_temp,how='left')\n",
    "print(df.shape)\n",
    "\n",
    "df = df.sort_values(by='id')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a2703196",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:58:00.423953Z",
     "iopub.status.busy": "2022-03-01T14:58:00.421687Z",
     "iopub.status.idle": "2022-03-01T14:58:00.445901Z",
     "shell.execute_reply": "2022-03-01T14:58:00.446307Z",
     "shell.execute_reply.started": "2022-03-01T14:56:49.962961Z"
    },
    "papermill": {
     "duration": 0.125292,
     "end_time": "2022-03-01T14:58:00.446486",
     "exception": false,
     "start_time": "2022-03-01T14:58:00.321194",
     "status": "completed"
    },
    "tags": []
   },
   "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>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>floor_area_max</th>\n",
       "      <th>floor_area_min</th>\n",
       "      <th>floor_area_sum</th>\n",
       "      <th>year_built_mean</th>\n",
       "      <th>year_built_max</th>\n",
       "      <th>year_built_min</th>\n",
       "      <th>energy_star_rating_min</th>\n",
       "      <th>energy_star_rating_max</th>\n",
       "      <th>energy_star_rating_mean</th>\n",
       "      <th>id_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1260</th>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>61242.0</td>\n",
       "      <td>1942.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>36</td>\n",
       "      <td>50.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1978104.0</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>95093036.0</td>\n",
       "      <td>1943.304734</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.44898</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6106</th>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Warehouse_Distribution_or_Shipping_center</td>\n",
       "      <td>274000.0</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>36</td>\n",
       "      <td>50.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1978104.0</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>95093036.0</td>\n",
       "      <td>1943.304734</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.44898</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5532</th>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Retail_Enclosed_mall</td>\n",
       "      <td>280025.0</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>36</td>\n",
       "      <td>50.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1978104.0</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>95093036.0</td>\n",
       "      <td>1943.304734</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.44898</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Education_Other_classroom</td>\n",
       "      <td>55325.0</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>36</td>\n",
       "      <td>50.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1978104.0</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>95093036.0</td>\n",
       "      <td>1943.304734</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.44898</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6218</th>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Warehouse_Nonrefrigerated</td>\n",
       "      <td>66000.0</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>36</td>\n",
       "      <td>50.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1978104.0</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>95093036.0</td>\n",
       "      <td>1943.304734</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.44898</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 93 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year_Factor State_Factor building_class  \\\n",
       "1260            1      State_1     Commercial   \n",
       "6106            1      State_1     Commercial   \n",
       "5532            1      State_1     Commercial   \n",
       "449             1      State_1     Commercial   \n",
       "6218            1      State_1     Commercial   \n",
       "\n",
       "                                  facility_type  floor_area  year_built  \\\n",
       "1260               Grocery_store_or_food_market     61242.0      1942.0   \n",
       "6106  Warehouse_Distribution_or_Shipping_center    274000.0      1955.0   \n",
       "5532                       Retail_Enclosed_mall    280025.0      1951.0   \n",
       "449                   Education_Other_classroom     55325.0      1980.0   \n",
       "6218                  Warehouse_Nonrefrigerated     66000.0      1985.0   \n",
       "\n",
       "      energy_star_rating  ELEVATION  january_min_temp  january_avg_temp  ...  \\\n",
       "1260                11.0        2.4                36              50.5  ...   \n",
       "6106                45.0        1.8                36              50.5  ...   \n",
       "5532                97.0        1.8                36              50.5  ...   \n",
       "449                 46.0        1.8                36              50.5  ...   \n",
       "6218               100.0        2.4                36              50.5  ...   \n",
       "\n",
       "      floor_area_max  floor_area_min  floor_area_sum  year_built_mean  \\\n",
       "1260       1978104.0         41000.0      95093036.0      1943.304734   \n",
       "6106       1978104.0         41000.0      95093036.0      1943.304734   \n",
       "5532       1978104.0         41000.0      95093036.0      1943.304734   \n",
       "449        1978104.0         41000.0      95093036.0      1943.304734   \n",
       "6218       1978104.0         41000.0      95093036.0      1943.304734   \n",
       "\n",
       "      year_built_max  year_built_min  energy_star_rating_min  \\\n",
       "1260          2008.0             0.0                     6.0   \n",
       "6106          2008.0             0.0                     6.0   \n",
       "5532          2008.0             0.0                     6.0   \n",
       "449           2008.0             0.0                     6.0   \n",
       "6218          2008.0             0.0                     6.0   \n",
       "\n",
       "      energy_star_rating_max  energy_star_rating_mean  id_count  \n",
       "1260                   100.0                 76.44898       358  \n",
       "6106                   100.0                 76.44898       358  \n",
       "5532                   100.0                 76.44898       358  \n",
       "449                    100.0                 76.44898       358  \n",
       "6218                   100.0                 76.44898       358  \n",
       "\n",
       "[5 rows x 93 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "331907e0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:58:00.641626Z",
     "iopub.status.busy": "2022-03-01T14:58:00.641060Z",
     "iopub.status.idle": "2022-03-01T14:58:05.019515Z",
     "shell.execute_reply": "2022-03-01T14:58:05.019028Z",
     "shell.execute_reply.started": "2022-03-01T14:56:49.996586Z"
    },
    "papermill": {
     "duration": 4.477214,
     "end_time": "2022-03-01T14:58:05.019648",
     "exception": false,
     "start_time": "2022-03-01T14:58:00.542434",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(77066, 93) (75718, 93) (1348, 93)\n",
      "(715, 93)\n"
     ]
    }
   ],
   "source": [
    "train1 = df.loc[(df['source']=='train')]\n",
    "train2 = df.loc[(df['source']=='test') & ( df['site_eui_lag1'].notnull())]\n",
    "train2 = train2.sample(frac=0.15,random_state=SEED)\n",
    "train = pd.concat([train1,train2])\n",
    "print(train.shape,train1.shape,train2.shape)\n",
    "train.to_csv('train_h2O.csv',index=False)\n",
    "test = df.loc[( df['site_eui_lag1'].isna()) & (df['source']=='test')]\n",
    "print(test.shape)\n",
    "test.to_csv('test_h2O.csv',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2790483a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:58:05.221697Z",
     "iopub.status.busy": "2022-03-01T14:58:05.221133Z",
     "iopub.status.idle": "2022-03-01T14:58:20.242051Z",
     "shell.execute_reply": "2022-03-01T14:58:20.243164Z"
    },
    "papermill": {
     "duration": 15.122303,
     "end_time": "2022-03-01T14:58:20.243422",
     "exception": false,
     "start_time": "2022-03-01T14:58:05.121119",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking whether there is an H2O instance running at http://localhost:54321 ..... not found.\n",
      "Attempting to start a local H2O server...\n",
      "  Java Version: openjdk version \"11.0.13\" 2021-10-19; OpenJDK Runtime Environment (build 11.0.13+8-Ubuntu-0ubuntu1.20.04); OpenJDK 64-Bit Server VM (build 11.0.13+8-Ubuntu-0ubuntu1.20.04, mixed mode, sharing)\n",
      "  Starting server from /opt/conda/lib/python3.7/site-packages/h2o/backend/bin/h2o.jar\n",
      "  Ice root: /tmp/tmpyysnim3k\n",
      "  JVM stdout: /tmp/tmpyysnim3k/h2o_unknownUser_started_from_python.out\n",
      "  JVM stderr: /tmp/tmpyysnim3k/h2o_unknownUser_started_from_python.err\n",
      "  Server is running at http://127.0.0.1:54321\n",
      "Connecting to H2O server at http://127.0.0.1:54321 ... successful.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div style=\"overflow:auto\"><table style=\"width:50%\"><tr><td>H2O_cluster_uptime:</td>\n",
       "<td>02 secs</td></tr>\n",
       "<tr><td>H2O_cluster_timezone:</td>\n",
       "<td>Etc/UTC</td></tr>\n",
       "<tr><td>H2O_data_parsing_timezone:</td>\n",
       "<td>UTC</td></tr>\n",
       "<tr><td>H2O_cluster_version:</td>\n",
       "<td>3.34.0.8</td></tr>\n",
       "<tr><td>H2O_cluster_version_age:</td>\n",
       "<td>1 month and 15 days </td></tr>\n",
       "<tr><td>H2O_cluster_name:</td>\n",
       "<td>H2O_from_python_unknownUser_u48v1m</td></tr>\n",
       "<tr><td>H2O_cluster_total_nodes:</td>\n",
       "<td>1</td></tr>\n",
       "<tr><td>H2O_cluster_free_memory:</td>\n",
       "<td>4 Gb</td></tr>\n",
       "<tr><td>H2O_cluster_total_cores:</td>\n",
       "<td>4</td></tr>\n",
       "<tr><td>H2O_cluster_allowed_cores:</td>\n",
       "<td>4</td></tr>\n",
       "<tr><td>H2O_cluster_status:</td>\n",
       "<td>locked, healthy</td></tr>\n",
       "<tr><td>H2O_connection_url:</td>\n",
       "<td>http://127.0.0.1:54321</td></tr>\n",
       "<tr><td>H2O_connection_proxy:</td>\n",
       "<td>{\"http\": null, \"https\": null}</td></tr>\n",
       "<tr><td>H2O_internal_security:</td>\n",
       "<td>False</td></tr>\n",
       "<tr><td>H2O_API_Extensions:</td>\n",
       "<td>Amazon S3, XGBoost, Algos, AutoML, Core V3, TargetEncoder, Core V4</td></tr>\n",
       "<tr><td>Python_version:</td>\n",
       "<td>3.7.12 final</td></tr></table></div>"
      ],
      "text/plain": [
       "--------------------------  ------------------------------------------------------------------\n",
       "H2O_cluster_uptime:         02 secs\n",
       "H2O_cluster_timezone:       Etc/UTC\n",
       "H2O_data_parsing_timezone:  UTC\n",
       "H2O_cluster_version:        3.34.0.8\n",
       "H2O_cluster_version_age:    1 month and 15 days\n",
       "H2O_cluster_name:           H2O_from_python_unknownUser_u48v1m\n",
       "H2O_cluster_total_nodes:    1\n",
       "H2O_cluster_free_memory:    4 Gb\n",
       "H2O_cluster_total_cores:    4\n",
       "H2O_cluster_allowed_cores:  4\n",
       "H2O_cluster_status:         locked, healthy\n",
       "H2O_connection_url:         http://127.0.0.1:54321\n",
       "H2O_connection_proxy:       {\"http\": null, \"https\": null}\n",
       "H2O_internal_security:      False\n",
       "H2O_API_Extensions:         Amazon S3, XGBoost, Algos, AutoML, Core V3, TargetEncoder, Core V4\n",
       "Python_version:             3.7.12 final\n",
       "--------------------------  ------------------------------------------------------------------"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Parse progress: |████████████████████████████████████████████████████████████████| (done) 100%\n",
      "Parse progress: |████████████████████████████████████████████████████████████████| (done) 100%\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Year_Factor',\n",
       " 'State_Factor',\n",
       " 'building_class',\n",
       " 'facility_type',\n",
       " 'floor_area',\n",
       " 'year_built',\n",
       " 'energy_star_rating',\n",
       " 'ELEVATION',\n",
       " 'january_min_temp',\n",
       " 'january_avg_temp',\n",
       " 'january_max_temp',\n",
       " 'february_min_temp',\n",
       " 'february_avg_temp',\n",
       " 'february_max_temp',\n",
       " 'march_min_temp',\n",
       " 'march_avg_temp',\n",
       " 'march_max_temp',\n",
       " 'april_min_temp',\n",
       " 'april_avg_temp',\n",
       " 'april_max_temp',\n",
       " 'may_min_temp',\n",
       " 'may_avg_temp',\n",
       " 'may_max_temp',\n",
       " 'june_min_temp',\n",
       " 'june_avg_temp',\n",
       " 'june_max_temp',\n",
       " 'july_min_temp',\n",
       " 'july_avg_temp',\n",
       " 'july_max_temp',\n",
       " 'august_min_temp',\n",
       " 'august_avg_temp',\n",
       " 'august_max_temp',\n",
       " 'september_min_temp',\n",
       " 'september_avg_temp',\n",
       " 'september_max_temp',\n",
       " 'october_min_temp',\n",
       " 'october_avg_temp',\n",
       " 'october_max_temp',\n",
       " 'november_min_temp',\n",
       " 'november_avg_temp',\n",
       " 'november_max_temp',\n",
       " 'december_min_temp',\n",
       " 'december_avg_temp',\n",
       " 'december_max_temp',\n",
       " 'cooling_degree_days',\n",
       " 'heating_degree_days',\n",
       " 'precipitation_inches',\n",
       " 'snowfall_inches',\n",
       " 'snowdepth_inches',\n",
       " 'avg_temp',\n",
       " 'days_below_30F',\n",
       " 'days_below_20F',\n",
       " 'days_below_10F',\n",
       " 'days_below_0F',\n",
       " 'days_above_80F',\n",
       " 'days_above_90F',\n",
       " 'days_above_100F',\n",
       " 'days_above_110F',\n",
       " 'direction_max_wind_speed',\n",
       " 'direction_peak_wind_speed',\n",
       " 'max_wind_speed',\n",
       " 'days_with_fog',\n",
       " 'min_temp1',\n",
       " 'max_temp1',\n",
       " 'avg_temp1',\n",
       " 'min_temp2',\n",
       " 'max_temp2',\n",
       " 'avg_temp2',\n",
       " 'min_temp3',\n",
       " 'max_temp3',\n",
       " 'avg_temp3',\n",
       " 'min_temp4',\n",
       " 'max_temp4',\n",
       " 'avg_temp4',\n",
       " 'min_temp5',\n",
       " 'max_temp5',\n",
       " 'avg_temp5',\n",
       " 'energy_star_rating_bin',\n",
       " 'floor_area_mean',\n",
       " 'floor_area_max',\n",
       " 'floor_area_min',\n",
       " 'floor_area_sum',\n",
       " 'year_built_mean',\n",
       " 'year_built_max',\n",
       " 'year_built_min',\n",
       " 'energy_star_rating_min',\n",
       " 'energy_star_rating_max',\n",
       " 'energy_star_rating_mean',\n",
       " 'id_count']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "h2o.init()\n",
    "train =  h2o.import_file('train_h2O.csv')\n",
    "test  =  h2o.import_file('test_h2O.csv')\n",
    "x = train.columns\n",
    "y = 'site_eui'\n",
    "x.remove(y)\n",
    "x.remove(\"id\") \n",
    "x = [ c for c in x if 'Year_Factor__' not in c ]\n",
    "x.remove('site_eui_lag1')\n",
    "x.remove('source')\n",
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c273b7ff",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T14:58:20.578307Z",
     "iopub.status.busy": "2022-03-01T14:58:20.577593Z",
     "iopub.status.idle": "2022-03-01T15:07:38.593935Z",
     "shell.execute_reply": "2022-03-01T15:07:38.593057Z"
    },
    "papermill": {
     "duration": 558.142568,
     "end_time": "2022-03-01T15:07:38.594230",
     "exception": false,
     "start_time": "2022-03-01T14:58:20.451662",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AutoML progress: |███████████████████████████████████████████████████████████████| (done) 100%\n",
      "Model Details\n",
      "=============\n",
      "H2OStackedEnsembleEstimator :  Stacked Ensemble\n",
      "Model Key:  StackedEnsemble_AllModels_1_AutoML_1_20220301_145820\n",
      "\n",
      "No model summary for this model\n",
      "\n",
      "ModelMetricsRegressionGLM: stackedensemble\n",
      "** Reported on train data. **\n",
      "\n",
      "MSE: 1323.148688358683\n",
      "RMSE: 36.375110836376614\n",
      "MAE: 18.975314323922486\n",
      "RMSLE: NaN\n",
      "R^2: 0.6260319264784608\n",
      "Mean Residual Deviance: 1323.148688358683\n",
      "Null degrees of freedom: 9917\n",
      "Residual degrees of freedom: 9911\n",
      "Null deviance: 35091451.88706109\n",
      "Residual deviance: 13122988.69114142\n",
      "AIC: 99450.36309265549\n",
      "\n",
      "ModelMetricsRegressionGLM: stackedensemble\n",
      "** Reported on cross-validation data. **\n",
      "\n",
      "MSE: 1720.5560287851313\n",
      "RMSE: 41.47958568724055\n",
      "MAE: 20.978218153774254\n",
      "RMSLE: NaN\n",
      "R^2: 0.49059182311822935\n",
      "Mean Residual Deviance: 1720.5560287851313\n",
      "Null degrees of freedom: 77065\n",
      "Residual degrees of freedom: 77059\n",
      "Null deviance: 260308871.05975273\n",
      "Residual deviance: 132596370.91435494\n",
      "AIC: 792892.5754229577\n"
     ]
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aml = H2OAutoML(max_runtime_secs= 555, \n",
    "                max_models = 10,\n",
    "                seed=1,\n",
    "                stopping_metric='RMSE',\n",
    "                include_algos = [ \"GLM\", \"DRF\",\"XGBoost\",\"GBM\",\"StackedEnsemble\"])\n",
    "\n",
    "aml.train(x=x, y=y, training_frame=train  ) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6ef8253f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T15:07:38.870395Z",
     "iopub.status.busy": "2022-03-01T15:07:38.869665Z",
     "iopub.status.idle": "2022-03-01T15:07:38.871863Z",
     "shell.execute_reply": "2022-03-01T15:07:38.872411Z"
    },
    "papermill": {
     "duration": 0.140584,
     "end_time": "2022-03-01T15:07:38.872598",
     "exception": false,
     "start_time": "2022-03-01T15:07:38.732014",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get AutoML event log\n",
    "log = aml.event_log\n",
    "\n",
    "# Get training timing info\n",
    "info = aml.training_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "98810399",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T15:07:39.140179Z",
     "iopub.status.busy": "2022-03-01T15:07:39.139533Z",
     "iopub.status.idle": "2022-03-01T15:07:39.153517Z",
     "shell.execute_reply": "2022-03-01T15:07:39.154094Z"
    },
    "papermill": {
     "duration": 0.149578,
     "end_time": "2022-03-01T15:07:39.154266",
     "exception": false,
     "start_time": "2022-03-01T15:07:39.004688",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead>\n",
       "<tr><th>model_id                                               </th><th style=\"text-align: right;\">  mean_residual_deviance</th><th style=\"text-align: right;\">   rmse</th><th style=\"text-align: right;\">    mse</th><th style=\"text-align: right;\">    mae</th><th style=\"text-align: right;\">     rmsle</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr><td>StackedEnsemble_AllModels_1_AutoML_1_20220301_145820   </td><td style=\"text-align: right;\">                 1720.56</td><td style=\"text-align: right;\">41.4796</td><td style=\"text-align: right;\">1720.56</td><td style=\"text-align: right;\">20.9782</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>StackedEnsemble_AllModels_2_AutoML_1_20220301_145820   </td><td style=\"text-align: right;\">                 1720.99</td><td style=\"text-align: right;\">41.4848</td><td style=\"text-align: right;\">1720.99</td><td style=\"text-align: right;\">20.9822</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>StackedEnsemble_BestOfFamily_3_AutoML_1_20220301_145820</td><td style=\"text-align: right;\">                 1733.14</td><td style=\"text-align: right;\">41.631 </td><td style=\"text-align: right;\">1733.14</td><td style=\"text-align: right;\">20.9931</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>StackedEnsemble_AllModels_3_AutoML_1_20220301_145820   </td><td style=\"text-align: right;\">                 1733.43</td><td style=\"text-align: right;\">41.6345</td><td style=\"text-align: right;\">1733.43</td><td style=\"text-align: right;\">20.7912</td><td style=\"text-align: right;\">  0.443665</td></tr>\n",
       "<tr><td>StackedEnsemble_BestOfFamily_2_AutoML_1_20220301_145820</td><td style=\"text-align: right;\">                 1733.45</td><td style=\"text-align: right;\">41.6347</td><td style=\"text-align: right;\">1733.45</td><td style=\"text-align: right;\">20.9945</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>StackedEnsemble_BestOfFamily_1_AutoML_1_20220301_145820</td><td style=\"text-align: right;\">                 1740.41</td><td style=\"text-align: right;\">41.7182</td><td style=\"text-align: right;\">1740.41</td><td style=\"text-align: right;\">21.0264</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>StackedEnsemble_BestOfFamily_4_AutoML_1_20220301_145820</td><td style=\"text-align: right;\">                 1759.72</td><td style=\"text-align: right;\">41.9491</td><td style=\"text-align: right;\">1759.72</td><td style=\"text-align: right;\">20.9498</td><td style=\"text-align: right;\">  0.441932</td></tr>\n",
       "<tr><td>StackedEnsemble_BestOfFamily_5_AutoML_1_20220301_145820</td><td style=\"text-align: right;\">                 1784.83</td><td style=\"text-align: right;\">42.2472</td><td style=\"text-align: right;\">1784.83</td><td style=\"text-align: right;\">21.5697</td><td style=\"text-align: right;\">  0.47171 </td></tr>\n",
       "<tr><td>XGBoost_1_AutoML_1_20220301_145820                     </td><td style=\"text-align: right;\">                 1787.49</td><td style=\"text-align: right;\">42.2787</td><td style=\"text-align: right;\">1787.49</td><td style=\"text-align: right;\">21.667 </td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "<tr><td>GBM_1_AutoML_1_20220301_145820                         </td><td style=\"text-align: right;\">                 1815.35</td><td style=\"text-align: right;\">42.6069</td><td style=\"text-align: right;\">1815.35</td><td style=\"text-align: right;\">21.1645</td><td style=\"text-align: right;\">nan       </td></tr>\n",
       "</tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lb = aml.leaderboard\n",
    "lb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0fe60320",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T15:07:39.426854Z",
     "iopub.status.busy": "2022-03-01T15:07:39.426225Z",
     "iopub.status.idle": "2022-03-01T15:07:39.670159Z",
     "shell.execute_reply": "2022-03-01T15:07:39.671333Z"
    },
    "papermill": {
     "duration": 0.385865,
     "end_time": "2022-03-01T15:07:39.671568",
     "exception": false,
     "start_time": "2022-03-01T15:07:39.285703",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "stackedensemble prediction progress: |███████████████████████████████████████████| (done) 100%\n"
     ]
    }
   ],
   "source": [
    "preds = aml.predict(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "02482cc7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-03-01T15:07:40.115753Z",
     "iopub.status.busy": "2022-03-01T15:07:40.110256Z",
     "iopub.status.idle": "2022-03-01T15:07:40.217376Z",
     "shell.execute_reply": "2022-03-01T15:07:40.218034Z"
    },
    "papermill": {
     "duration": 0.320035,
     "end_time": "2022-03-01T15:07:40.218251",
     "exception": false,
     "start_time": "2022-03-01T15:07:39.898216",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(9705, 3)\n"
     ]
    }
   ],
   "source": [
    "## create submission\n",
    "sample_submission=sub_pp.copy()\n",
    "test = pd.read_csv('test_h2O.csv')\n",
    "test['site_eui_pp'] = h2o.as_list(preds)['predict']\n",
    "sample_submission=sample_submission.merge(test[['id','site_eui_pp']],on='id',how='left')\n",
    "sample_submission['site_eui'] = np.where( sample_submission['site_eui_pp'].isna(),\n",
    "                                          sample_submission['site_eui'],\n",
    "                                          sample_submission['site_eui_pp']*0.75+sample_submission['site_eui']*0.25   )\n",
    "sample_submission[['id','site_eui']].to_csv('sub_final_wids2022.csv',index=False)\n",
    "print(sample_submission.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eb8f980",
   "metadata": {
    "papermill": {
     "duration": 0.13556,
     "end_time": "2022-03-01T15:07:40.490324",
     "exception": false,
     "start_time": "2022-03-01T15:07:40.354764",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 633.884912,
   "end_time": "2022-03-01T15:07:41.939409",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2022-03-01T14:57:08.054497",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}