{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import os\nfrom datetime import datetime\nimport gc\nfrom itertools import product\nimport matplotlib\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport scipy\nimport warnings\n\n\nimport lightgbm as lgb\nimport optuna\nfrom optuna.integration import LightGBMPruningCallback\nfrom optuna.visualization.matplotlib import plot_optimization_history, plot_param_importances\nimport pandas as pd\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\nimport seaborn as sns\nfrom sklearn.preprocessing import MinMaxScaler, LabelEncoder, StandardScaler\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.metrics import mean_squared_error\nimport tensorflow as tf\n\nmatplotlib.rcParams['figure.dpi'] = 100\nsns.set()\nsns.set_style(\"whitegrid\")\n%matplotlib inline\nwarnings.simplefilter('ignore')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T17:12:22.856808Z","iopub.execute_input":"2023-02-15T17:12:22.858355Z","iopub.status.idle":"2023-02-15T17:12:22.877124Z","shell.execute_reply.started":"2023-02-15T17:12:22.858283Z","shell.execute_reply":"2023-02-15T17:12:22.875778Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Usefull functions**","metadata":{}},{"cell_type":"code","source":"## Memory optimization\n\n# Original code from https://www.kaggle.com/code/rohanrao/ashrae-half-and-half/notebook\ndef reduce_mem_usage(df, use_float16=False):\n    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n    \"\"\"\n    \n    start_mem = df.memory_usage().sum() / 1024**2\n    print(\"Memory usage of dataframe is {:.2f} MB\".format(start_mem))\n    \n    for col in df.columns:\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            continue\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == \"int\":\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n#        else:\n#            df[col] = df[col].astype(\"category\")\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    print(\"Memory usage after optimization is: {:.2f} MB\".format(end_mem))\n    print(\"Decreased by {:.1f}%\".format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:47:10.598918Z","iopub.execute_input":"2023-02-15T15:47:10.600225Z","iopub.status.idle":"2023-02-15T15:47:10.615479Z","shell.execute_reply.started":"2023-02-15T15:47:10.600186Z","shell.execute_reply":"2023-02-15T15:47:10.613880Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Perform a one hot encoding\ndef one_hot(train, feature, test=None):\n    train2 = train.copy()\n    train2[f'{feature}s'] = train2[feature]\n    train2 = pd.get_dummies(train2, columns=[feature], prefix=[f'{feature}_'])\n    if test is None:\n        return(train2)\n    else:\n        test2 = test.copy()\n        test2[f'{feature}s'] = test2[feature]\n        test2 = pd.get_dummies(test2, columns=[feature], prefix=[f'{feature}_'])\n        return(train2, test2)\n\n# Perform a label encoding\ndef label_encode(train, feature, test=None):\n    le = LabelEncoder()\n    train2 = train.copy()\n    train2[feature] = le.fit_transform(train2[feature])\n    if test is None:\n        return(train2)\n    else:\n        test2 = test.copy()\n        test2[feature] = le.transform(test2[feature])\n        return(train2, test2)\n\n# Extract time features\ndef extract_time_features(train, time_feature, test=None):\n    train['month'] = train[time_feature].dt.month\n    train['weekday'] = train[time_feature].dt.weekday\n    train['hour'] = train[time_feature].dt.hour\n    train['day'] = train[time_feature].dt.day\n    train.sort_values(time_feature, inplace=True)\n    train.reset_index(drop=True, inplace=True)\n    \n    if test is None:\n        return(train)\n    \n    else:\n        test['month'] = test[time_feature].dt.month\n        test['weekday'] = test[time_feature].dt.weekday\n        test['hour'] = test[time_feature].dt.hour\n        test['day'] = test[time_feature].dt.day\n        test.sort_values(time_feature, inplace=True)\n        test.reset_index(drop=True, inplace=True)\n        return(train, test)\n        \n# Add holidays based on a list of public holidays dates\ndef add_holidays(train, holidays, test=None):\n    train[\"is_holiday\"] = (train['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int)\n    if test is None:\n        return(train)\n    else:\n        test[\"is_holiday\"] = (test['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int)\n        return(train, test)\n\n# Scale data using minmax scaler\ndef scale_numerical_feature(train, num_features, test=None):\n    scaler = MinMaxScaler()\n    train2 = train.copy()\n    train2[num_features] = scaler.fit_transform(train2[num_features])\n    if test is None:\n        return(train2)\n    else:\n        test2 = test.copy()\n        test2[num_features] = scaler.transform(test2[num_features])\n    return(train2, test2)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:47:10.617699Z","iopub.execute_input":"2023-02-15T15:47:10.618395Z","iopub.status.idle":"2023-02-15T15:47:10.640811Z","shell.execute_reply.started":"2023-02-15T15:47:10.618356Z","shell.execute_reply":"2023-02-15T15:47:10.639535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Data Loading & Exploration","metadata":{}},{"cell_type":"code","source":"# Load all the data from csv\ntrain_raw = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/train.csv\", sep = ',')\ntest_raw = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/test.csv\", sep = ',')\nb_meta = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/building_metadata.csv\",sep = ',')\nw_train = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/weather_train.csv\",sep = ',')\nw_test = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/weather_test.csv\",sep = ',')\nsample = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/sample_submission.csv\",sep = ',')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:47:10.644167Z","iopub.execute_input":"2023-02-15T15:47:10.644950Z","iopub.status.idle":"2023-02-15T15:48:32.982027Z","shell.execute_reply.started":"2023-02-15T15:47:10.644894Z","shell.execute_reply":"2023-02-15T15:48:32.980663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Reduce memory usage to preserve RAM\ntrain_raw = reduce_mem_usage(train_raw, use_float16=True)\ntest_raw = reduce_mem_usage(test_raw, use_float16=True)\nb_meta = reduce_mem_usage(b_meta, use_float16=True)\nw_train = reduce_mem_usage(w_train, use_float16=True)\nw_test = reduce_mem_usage(w_test, use_float16=True)\nsample = reduce_mem_usage(sample, use_float16=True)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:48:32.983655Z","iopub.execute_input":"2023-02-15T15:48:32.984149Z","iopub.status.idle":"2023-02-15T15:48:34.954932Z","shell.execute_reply.started":"2023-02-15T15:48:32.984106Z","shell.execute_reply":"2023-02-15T15:48:34.953853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Explore the number of rows and columns per dataframe","metadata":{}},{"cell_type":"code","source":"tables = [train_raw, test_raw, b_meta, w_train, w_test, sample]\ntable_names = ['train_raw', 'test_raw', 'b_meta', 'w_train', 'w_test', 'sample']\nfor name, i in zip(table_names,tables):\n    print(f\"table en cours d'étude: {name}\")\n    print(i.columns)\n    print(i.shape,'\\n')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:48:34.956401Z","iopub.execute_input":"2023-02-15T15:48:34.956715Z","iopub.status.idle":"2023-02-15T15:48:34.964543Z","shell.execute_reply.started":"2023-02-15T15:48:34.956686Z","shell.execute_reply":"2023-02-15T15:48:34.963287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## General Analysis","metadata":{}},{"cell_type":"markdown","source":"We will explore at high level the different dataframes by:\n- giving the **meaning of each column**\n- see if there are any **NaN values**\n- see if there are any **duplicate values**","metadata":{}},{"cell_type":"markdown","source":"### Train_raw","metadata":{}},{"cell_type":"markdown","source":"- `building_id` - Foreign key for the building metadata.\n\n- `meter` - The meter id code. Read as {0: electricity, 1: chilledwater, 2: steam, 3: hotwater}. Not every building has all meter types.\n\n- `timestamp` - When the measurement was taken\n\n- `meter_reading` - The target variable. Energy consumption in kWh (or equivalent). Note that this is real data with measurement error, which we expect will impose a baseline level of modeling error. UPDATE: as discussed here, the site 0 electric meter readings are in kBTU.\n","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"train_raw.isna().any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:48:34.966117Z","iopub.execute_input":"2023-02-15T15:48:34.966469Z","iopub.status.idle":"2023-02-15T15:48:35.927932Z","shell.execute_reply.started":"2023-02-15T15:48:34.966437Z","shell.execute_reply":"2023-02-15T15:48:35.926774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"train_raw.duplicated(subset=['building_id','meter','timestamp'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:48:35.931113Z","iopub.execute_input":"2023-02-15T15:48:35.931768Z","iopub.status.idle":"2023-02-15T15:48:43.804355Z","shell.execute_reply.started":"2023-02-15T15:48:35.931733Z","shell.execute_reply":"2023-02-15T15:48:43.803140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Test_raw","metadata":{}},{"cell_type":"markdown","source":"- `row_id` - Row id for your submission file\n- `building_id` - Building id code\n- `meter` - The meter id code\n- `timestamp` - Timestamps for the test data period\n","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"test_raw.isna().any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:48:43.805816Z","iopub.execute_input":"2023-02-15T15:48:43.806220Z","iopub.status.idle":"2023-02-15T15:48:45.746397Z","shell.execute_reply.started":"2023-02-15T15:48:43.806185Z","shell.execute_reply":"2023-02-15T15:48:45.745062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"test_raw.duplicated(subset=['building_id','meter','timestamp'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:48:45.751897Z","iopub.execute_input":"2023-02-15T15:48:45.752306Z","iopub.status.idle":"2023-02-15T15:49:00.479186Z","shell.execute_reply.started":"2023-02-15T15:48:45.752271Z","shell.execute_reply":"2023-02-15T15:49:00.477908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### sample","metadata":{}},{"cell_type":"markdown","source":"- `row_id` - Row id for your submission file\n\n- `meter_reading` - The target variable. Energy consumption in kWh (or equivalent). Note that this is real data with measurement error, which we expect will impose a baseline level of modeling error. UPDATE: as discussed here, the site 0 electric meter readings are in kBTU.\n","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"sample.isna().any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:00.480687Z","iopub.execute_input":"2023-02-15T15:49:00.481054Z","iopub.status.idle":"2023-02-15T15:49:00.523014Z","shell.execute_reply.started":"2023-02-15T15:49:00.481020Z","shell.execute_reply":"2023-02-15T15:49:00.521808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"sample.duplicated(subset=['row_id'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:00.524324Z","iopub.execute_input":"2023-02-15T15:49:00.524687Z","iopub.status.idle":"2023-02-15T15:49:11.290512Z","shell.execute_reply.started":"2023-02-15T15:49:00.524654Z","shell.execute_reply":"2023-02-15T15:49:11.289235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### b_meta","metadata":{}},{"cell_type":"markdown","source":"- `site_id` - Foreign key for the weather files. \n- `building_id` - Foreign key for training.csv\n- `primary_use` - Indicator of the primary category of activities for the building based on EnergyStar property type definitions\n- `square_feet` - Gross floor area of the building\n- `year_built` - Year building was opened\n- `floor_count` - Number of floors of the building\n","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"b_meta.isna().any()","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:49:11.291822Z","iopub.execute_input":"2023-02-15T15:49:11.292680Z","iopub.status.idle":"2023-02-15T15:49:11.303557Z","shell.execute_reply.started":"2023-02-15T15:49:11.292632Z","shell.execute_reply":"2023-02-15T15:49:11.302159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"b_meta.duplicated(subset=['building_id'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:11.305565Z","iopub.execute_input":"2023-02-15T15:49:11.306338Z","iopub.status.idle":"2023-02-15T15:49:11.319696Z","shell.execute_reply.started":"2023-02-15T15:49:11.306299Z","shell.execute_reply":"2023-02-15T15:49:11.318302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### w_train","metadata":{}},{"cell_type":"markdown","source":"- `site_id`\n- `air_temperature` - Degrees Celsius\n- `cloud_coverage` - Portion of the sky covered in clouds, in oktas\n- `dew_temperature` - Degrees Celsius\n- `precip_depth_1_hr` - Millimeters\n- `sea_level_pressure` - Millibar/hectopascals\n- `wind_direction` - Compass direction (0-360)\n- `wind_speed` - Meters per second\n","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"w_train.isna().any(axis=0)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:49:11.320943Z","iopub.execute_input":"2023-02-15T15:49:11.322216Z","iopub.status.idle":"2023-02-15T15:49:11.348177Z","shell.execute_reply.started":"2023-02-15T15:49:11.322166Z","shell.execute_reply":"2023-02-15T15:49:11.347037Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"w_train.duplicated(subset=['site_id','timestamp'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:11.349470Z","iopub.execute_input":"2023-02-15T15:49:11.350192Z","iopub.status.idle":"2023-02-15T15:49:11.382135Z","shell.execute_reply.started":"2023-02-15T15:49:11.350155Z","shell.execute_reply":"2023-02-15T15:49:11.381075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### w_test","metadata":{}},{"cell_type":"markdown","source":"- `site_id`\n- `air_temperature` - Degrees Celsius\n- `cloud_coverage` - Portion of the sky covered in clouds, in oktas\n- `dew_temperature` - Degrees Celsius\n- `precip_depth_1_hr` - Millimeters\n- `sea_level_pressure` - Millibar/hectopascals\n- `wind_direction` - Compass direction (0-360)\n- `wind_speed` - Meters per second","metadata":{}},{"cell_type":"markdown","source":"__NaN values__","metadata":{}},{"cell_type":"code","source":"w_test.isna().any(axis=0)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:49:11.383524Z","iopub.execute_input":"2023-02-15T15:49:11.383877Z","iopub.status.idle":"2023-02-15T15:49:11.412610Z","shell.execute_reply.started":"2023-02-15T15:49:11.383846Z","shell.execute_reply":"2023-02-15T15:49:11.410989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Duplicate__","metadata":{}},{"cell_type":"code","source":"w_test.duplicated(subset=['site_id','timestamp'], keep=False).any()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:11.413795Z","iopub.execute_input":"2023-02-15T15:49:11.414144Z","iopub.status.idle":"2023-02-15T15:49:11.476358Z","shell.execute_reply.started":"2023-02-15T15:49:11.414113Z","shell.execute_reply":"2023-02-15T15:49:11.475174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION** \n\nWith this high-level analysis, we have seen that \n- there are **some missing values for the weather data and building metadata.**\n- we **do not have any duplicated values** so joining table will be safe.","metadata":{}},{"cell_type":"markdown","source":"## Joining","metadata":{}},{"cell_type":"markdown","source":"In order to analyse, in a more precise way, we will first **join all the data** in order to get the global picture of our dataset","metadata":{}},{"cell_type":"code","source":"train_full_raw = train_raw.merge(b_meta, how='left', on='building_id')\ntrain_full_raw = train_full_raw.merge(w_train, how='left', on=['site_id','timestamp'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:11.477870Z","iopub.execute_input":"2023-02-15T15:49:11.478386Z","iopub.status.idle":"2023-02-15T15:49:21.708469Z","shell.execute_reply.started":"2023-02-15T15:49:11.478343Z","shell.execute_reply":"2023-02-15T15:49:21.707311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_full_raw = test_raw.merge(sample, how='inner', on='row_id')\ntest_full_raw = test_full_raw.merge(b_meta, how='left', on='building_id')\ntest_full_raw = test_full_raw.merge(w_test, how='left', on=['site_id','timestamp'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:49:21.712289Z","iopub.execute_input":"2023-02-15T15:49:21.712670Z","iopub.status.idle":"2023-02-15T15:50:12.283143Z","shell.execute_reply.started":"2023-02-15T15:49:21.712636Z","shell.execute_reply":"2023-02-15T15:50:12.282069Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del b_meta, w_train, w_test, sample, train_raw, test_raw\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:12.284606Z","iopub.execute_input":"2023-02-15T15:50:12.284999Z","iopub.status.idle":"2023-02-15T15:50:12.524383Z","shell.execute_reply.started":"2023-02-15T15:50:12.284962Z","shell.execute_reply":"2023-02-15T15:50:12.523112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_full_raw.drop('row_id',axis=1,inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:12.526171Z","iopub.execute_input":"2023-02-15T15:50:12.526507Z","iopub.status.idle":"2023-02-15T15:50:19.463753Z","shell.execute_reply.started":"2023-02-15T15:50:12.526477Z","shell.execute_reply":"2023-02-15T15:50:19.462472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We convert `timetamp` data into a a **datetime** one in order to perform proper analysis ","metadata":{}},{"cell_type":"code","source":"train_full_raw['timestamp'] = pd.to_datetime(train_full_raw['timestamp'])\ntest_full_raw['timestamp'] = pd.to_datetime(test_full_raw['timestamp'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:19.465078Z","iopub.execute_input":"2023-02-15T15:50:19.465441Z","iopub.status.idle":"2023-02-15T15:50:30.293972Z","shell.execute_reply.started":"2023-02-15T15:50:19.465409Z","shell.execute_reply":"2023-02-15T15:50:30.292677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Analysis train_full_raw & test_full_raw","metadata":{}},{"cell_type":"markdown","source":"### Global observations","metadata":{}},{"cell_type":"code","source":"train_full_raw.columns","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:30.295537Z","iopub.execute_input":"2023-02-15T15:50:30.295919Z","iopub.status.idle":"2023-02-15T15:50:30.304103Z","shell.execute_reply.started":"2023-02-15T15:50:30.295885Z","shell.execute_reply":"2023-02-15T15:50:30.302768Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_full_raw.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:30.305607Z","iopub.execute_input":"2023-02-15T15:50:30.306047Z","iopub.status.idle":"2023-02-15T15:50:30.340300Z","shell.execute_reply.started":"2023-02-15T15:50:30.306004Z","shell.execute_reply":"2023-02-15T15:50:30.339407Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Missing Values","metadata":{}},{"cell_type":"markdown","source":"We will count for each column the **percentage of missing values** in both **train and test sets**","metadata":{}},{"cell_type":"code","source":"# Count missing values for train set\ntotal_train = train_full_raw.isnull().sum().sort_values(ascending = False)\npercent_train = (train_full_raw.isnull().sum()/train_full_raw.isnull().count()*100).sort_values(ascending = False)\nmissing_val_train = pd.concat([total_train, percent_train], axis=1, keys=['Total', 'Percent'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:30.342360Z","iopub.execute_input":"2023-02-15T15:50:30.343218Z","iopub.status.idle":"2023-02-15T15:50:35.866062Z","shell.execute_reply.started":"2023-02-15T15:50:30.343169Z","shell.execute_reply":"2023-02-15T15:50:35.864845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Count missing values for test set\ntotal_test = test_full_raw.isnull().sum().sort_values(ascending = False)\npercent_test = (test_full_raw.isnull().sum()/test_full_raw.isnull().count()*100).sort_values(ascending = False)\nmissing_val_test = pd.concat([total_test, percent_test], axis=1, keys=['Total', 'Percent'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:35.867376Z","iopub.execute_input":"2023-02-15T15:50:35.867789Z","iopub.status.idle":"2023-02-15T15:50:46.956359Z","shell.execute_reply.started":"2023-02-15T15:50:35.867759Z","shell.execute_reply":"2023-02-15T15:50:46.955228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plot missing values\nfig, axes = plt.subplots(2,figsize=(12,12))\nsns.barplot(data=missing_val_train.reset_index(), y='Percent', x='index', ax=axes[0]).set(title='Missing values Percentage per Feature in Train Set');\naxes[0].tick_params(labelrotation=90)\nsns.barplot(data=missing_val_test.reset_index(), y='Percent', x='index', ax=axes[1]).set(title='Missing values Percentage per Feature in Test Set');\naxes[1].tick_params(labelrotation=90)\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:46.964672Z","iopub.execute_input":"2023-02-15T15:50:46.965052Z","iopub.status.idle":"2023-02-15T15:50:47.945482Z","shell.execute_reply.started":"2023-02-15T15:50:46.965021Z","shell.execute_reply":"2023-02-15T15:50:47.944414Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe can see that we have the **same proportion of missing values for the train and test sets**.\n\n`floor_count`, `year_built` and `cloud_coverage` are columns having the most NaN values with **more than 40% of NaN values**. \n\nThis will be complicated to impute properly those missing data.","metadata":{}},{"cell_type":"markdown","source":"### Outliers","metadata":{}},{"cell_type":"code","source":"train_full_raw.describe()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:50:47.946625Z","iopub.execute_input":"2023-02-15T15:50:47.947422Z","iopub.status.idle":"2023-02-15T15:51:13.656281Z","shell.execute_reply.started":"2023-02-15T15:50:47.947388Z","shell.execute_reply":"2023-02-15T15:51:13.655039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_full_raw.describe()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:51:13.658013Z","iopub.execute_input":"2023-02-15T15:51:13.658992Z","iopub.status.idle":"2023-02-15T15:52:03.541760Z","shell.execute_reply.started":"2023-02-15T15:51:13.658954Z","shell.execute_reply":"2023-02-15T15:52:03.540542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"For the train set:\n- There is a **10^4 difference between max and mean for `meter_reading`** suggesting some **strong outliers**","metadata":{}},{"cell_type":"markdown","source":"### Meter","metadata":{}},{"cell_type":"code","source":"sns.countplot(data=train_full_raw, x='meter').set(title='Occurences per meter type')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:03.543426Z","iopub.execute_input":"2023-02-15T15:52:03.544524Z","iopub.status.idle":"2023-02-15T15:52:05.280428Z","shell.execute_reply.started":"2023-02-15T15:52:03.544473Z","shell.execute_reply":"2023-02-15T15:52:05.279154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have a **majority of meter 0 data**","metadata":{}},{"cell_type":"markdown","source":"**Create variables**","metadata":{}},{"cell_type":"code","source":"# One hot encoding of meter\ntrain_full_raw = one_hot(train_full_raw, 'meter')\ntest_full_raw = one_hot(test_full_raw, 'meter')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:05.282134Z","iopub.execute_input":"2023-02-15T15:52:05.282492Z","iopub.status.idle":"2023-02-15T15:52:16.053718Z","shell.execute_reply.started":"2023-02-15T15:52:05.282460Z","shell.execute_reply":"2023-02-15T15:52:16.052161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i in range(0,4):\n    train_full_raw[f'meter__{i}'] = train_full_raw[f'meter__{i}']*train_full_raw['meter_reading']\n    test_full_raw[f'meter__{i}'] = test_full_raw[f'meter__{i}']*test_full_raw['meter_reading']","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:16.055652Z","iopub.execute_input":"2023-02-15T15:52:16.056137Z","iopub.status.idle":"2023-02-15T15:52:16.732569Z","shell.execute_reply.started":"2023-02-15T15:52:16.056079Z","shell.execute_reply":"2023-02-15T15:52:16.731131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Analysis**","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(1,2,figsize=(10,6))\nsns.boxplot(x='meters', y='meter_reading', data=train_full_raw, ax=axes[0]).set(title='Boxplot with outliers');\nsns.boxplot(x='meters', y='meter_reading', data=train_full_raw, ax=axes[1], showfliers=False).set(title='Boxplot without outliers');","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:16.734453Z","iopub.execute_input":"2023-02-15T15:52:16.735263Z","iopub.status.idle":"2023-02-15T15:52:31.499939Z","shell.execute_reply.started":"2023-02-15T15:52:16.735209Z","shell.execute_reply":"2023-02-15T15:52:31.498423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train0 = train_full_raw[train_full_raw['meters']==0].groupby('timestamp').agg({'meter_reading':'mean'})\ntrain1 = train_full_raw[train_full_raw['meters']==1].groupby('timestamp').agg({'meter_reading':'mean'})\ntrain2 = train_full_raw[train_full_raw['meters']==2].groupby('timestamp').agg({'meter_reading':'mean'})\ntrain3 = train_full_raw[train_full_raw['meters']==3].groupby('timestamp').agg({'meter_reading':'mean'})\ntrain01 = train0.merge(train1, how='outer', left_index=True, right_index=True, suffixes=('_0', '_1'))\ntrain012 = train01.merge(train2, how='outer', left_index=True, right_index=True, suffixes=('','_2'))\ntrain0123 = train012.merge(train3, how='outer', left_index=True, right_index=True, suffixes=('_2','_3'))","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:31.501713Z","iopub.execute_input":"2023-02-15T15:52:31.502293Z","iopub.status.idle":"2023-02-15T15:52:35.778871Z","shell.execute_reply.started":"2023-02-15T15:52:31.502255Z","shell.execute_reply":"2023-02-15T15:52:35.777792Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some **significant differences in terms of scale** between the different meter\n- **meter 2 has peaks above 80000** meanwhile **the other meters are between 0 and 2500**\n- **meter 2 has a huge difference in terms of min and max**, let see what is driving this","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(2,2,figsize=(14, 10))\nfor i in range(0,4):\n    sns.lineplot(data=train0123.reset_index(),y=f'meter_reading_{i}',x='timestamp',ax=axes[i%2][i//2], label='hour')\n    sns.lineplot(data=train0123.resample('D').mean().reset_index(),y=f'meter_reading_{i}',x='timestamp',ax=axes[i%2][i//2], label='day')\nfig.suptitle(\"Hourly & Daily Mean for the Different Meters on Train Set\");","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:52:35.780414Z","iopub.execute_input":"2023-02-15T15:52:35.780917Z","iopub.status.idle":"2023-02-15T15:53:01.629887Z","shell.execute_reply.started":"2023-02-15T15:52:35.780884Z","shell.execute_reply":"2023-02-15T15:53:01.628788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can see that the **site 2 has the same shape as the global plot**, this is due to the fact that the scale (10^4) is way more important than the other site (10^2)","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(8,2,figsize=(14, 30))\nfor i in range(0, 16):\n    sns.lineplot(data=train_full_raw[train_full_raw['site_id']==i].set_index('timestamp').resample('D').mean().reset_index(),y=f'meter__2',x='timestamp',ax=axes[i%8][i//8]);\n    axes[i%8][i//8].set_title(f'site_id_{i}', fontsize=13);\nplt.tight_layout()    ","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:53:01.631744Z","iopub.execute_input":"2023-02-15T15:53:01.632474Z","iopub.status.idle":"2023-02-15T15:53:17.656037Z","shell.execute_reply.started":"2023-02-15T15:53:01.632414Z","shell.execute_reply":"2023-02-15T15:53:17.654918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Similarly, we can see that the **education buildings in site 13 has the same shape as the global plot**, this is due to the fact that the scale (10^4) is way more important than the other types of buildings (10^2)","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(8,2,figsize=(14, 30))\nfor idx, i in enumerate(train_full_raw['primary_use'].unique()):\n    sns.lineplot(data=train_full_raw[(train_full_raw['primary_use']==i) & (train_full_raw['site_id']==13)].set_index('timestamp').resample('D').mean().reset_index(),y='meter__2',x='timestamp',ax=axes[idx%8][idx//8]);\n    axes[idx%8][idx//8].set_title(f'{i}', fontsize=13);\nplt.tight_layout()    ","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:53:17.657985Z","iopub.execute_input":"2023-02-15T15:53:17.658426Z","iopub.status.idle":"2023-02-15T15:53:50.242583Z","shell.execute_reply.started":"2023-02-15T15:53:17.658387Z","shell.execute_reply":"2023-02-15T15:53:50.241395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- We can see that the **building 1099 has the same trend as the global one**. On top of that, **its scale (10^7) is much greater than the other buildings (up to 10^4).**\n\n- **This is probably an outlier**, hence we will delete the entries of meter 2 with building_id=1099 (at least for the analysis).\n\nLet's see if building 1099 is still an outlier with the other meters","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(13,2,figsize=(14, 30))\nbuildings = train_full_raw[(train_full_raw['site_id']==13) & (train_full_raw['primary_use']=='Education')]['building_id'].unique()\nfor idx, i in enumerate(buildings):\n    sns.lineplot(data=train_full_raw[train_full_raw['building_id']==i].set_index('timestamp').resample('D').mean().reset_index(),y='meter__2',x='timestamp',ax=axes[idx%13][idx//13]);\n    axes[idx%13][idx//13].set_title(f'building_{i}', fontsize=13);\nplt.tight_layout()    ","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:53:50.244235Z","iopub.execute_input":"2023-02-15T15:53:50.244656Z","iopub.status.idle":"2023-02-15T15:54:07.305196Z","shell.execute_reply.started":"2023-02-15T15:53:50.244623Z","shell.execute_reply":"2023-02-15T15:54:07.304246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**No outlier for the other meters** so we will keep them in the data set","metadata":{}},{"cell_type":"code","source":"fig, axes = plt.subplots(1,3,figsize=(14, 6))\nfor idx,i in enumerate([0,1,3]):\n    sns.lineplot(data=train_full_raw[train_full_raw['building_id']!=1099][['timestamp',f'meter__{i}']].set_index('timestamp').resample('D').mean(),y=f'meter__{i}',x='timestamp',ax=axes[idx], label='full')\n    sns.lineplot(data=train_full_raw[train_full_raw['building_id']==1099][['timestamp',f'meter__{i}']].set_index('timestamp').resample('D').mean(),y=f'meter__{i}',x='timestamp',ax=axes[idx], label='1099')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:54:07.306694Z","iopub.execute_input":"2023-02-15T15:54:07.307446Z","iopub.status.idle":"2023-02-15T15:54:20.703604Z","shell.execute_reply.started":"2023-02-15T15:54:07.307405Z","shell.execute_reply":"2023-02-15T15:54:20.702485Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Taking off outliers**","metadata":{}},{"cell_type":"code","source":"train_full = train_full_raw[~((train_full_raw['building_id']==1099)&(train_full_raw['meters']==2))]\ntrain_full_w_outliers = train_full_raw.copy()\ntest_full = test_full_raw.copy()\ndel test_full_raw, train_full_raw\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:54:20.705041Z","iopub.execute_input":"2023-02-15T15:54:20.705415Z","iopub.status.idle":"2023-02-15T15:54:26.452763Z","shell.execute_reply.started":"2023-02-15T15:54:20.705381Z","shell.execute_reply":"2023-02-15T15:54:26.451591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### building_id","metadata":{}},{"cell_type":"code","source":"# Counting unique building ids\ntrain_full['building_id'].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:54:26.454342Z","iopub.execute_input":"2023-02-15T15:54:26.454797Z","iopub.status.idle":"2023-02-15T15:54:26.602833Z","shell.execute_reply.started":"2023-02-15T15:54:26.454752Z","shell.execute_reply":"2023-02-15T15:54:26.601552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__Historic for the buildings__","metadata":{}},{"cell_type":"code","source":"sns.histplot(train_full[['building_id']].value_counts()).set(xlabel='# Measurements', ylabel='Count');","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:54:26.604150Z","iopub.execute_input":"2023-02-15T15:54:26.604473Z","iopub.status.idle":"2023-02-15T15:54:27.554522Z","shell.execute_reply.started":"2023-02-15T15:54:26.604444Z","shell.execute_reply":"2023-02-15T15:54:27.553173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Count measurements for each building ids\nfig, axes = plt.subplots(1,1,figsize=(14, 6))\nsns.countplot(data = train_full[['building_id']], x='building_id',color='r', label='train')\nplt.setp(axes.patches, linewidth=0);","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:54:27.556055Z","iopub.execute_input":"2023-02-15T15:54:27.556438Z","iopub.status.idle":"2023-02-15T15:55:08.397437Z","shell.execute_reply.started":"2023-02-15T15:54:27.556406Z","shell.execute_reply":"2023-02-15T15:55:08.395886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Conclusion**\n\nThere is mainly the **same number of measurements for each building id**","metadata":{}},{"cell_type":"markdown","source":"### timestamp","metadata":{}},{"cell_type":"code","source":"train__timestamp = train_full.groupby('building_id').agg({'timestamp':['max', 'min']})\ntrain__timestamp.columns = train__timestamp.columns.get_level_values(0)\ntrain__timestamp.reset_index(inplace=True)\ntrain__timestamp.columns = ['building_id','max_time','min_time']\ntrain__timestamp['delta'] = train__timestamp['max_time'] - train__timestamp['min_time']","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:08.399411Z","iopub.execute_input":"2023-02-15T15:55:08.400532Z","iopub.status.idle":"2023-02-15T15:55:09.159722Z","shell.execute_reply.started":"2023-02-15T15:55:08.400483Z","shell.execute_reply":"2023-02-15T15:55:09.158390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train__timestamp[['delta','max_time','min_time']].value_counts().head(3)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:09.161425Z","iopub.execute_input":"2023-02-15T15:55:09.161809Z","iopub.status.idle":"2023-02-15T15:55:09.175500Z","shell.execute_reply.started":"2023-02-15T15:55:09.161765Z","shell.execute_reply":"2023-02-15T15:55:09.174380Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test__timestamp = test_full.groupby('building_id').agg({'timestamp':['max', 'min']})\ntest__timestamp.columns = test__timestamp.columns.get_level_values(0)\ntest__timestamp.reset_index(inplace=True)\ntest__timestamp.columns = ['building_id','max_time','min_time']\ntest__timestamp['delta'] = test__timestamp['max_time'] - test__timestamp['min_time']","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:09.177246Z","iopub.execute_input":"2023-02-15T15:55:09.177701Z","iopub.status.idle":"2023-02-15T15:55:10.667309Z","shell.execute_reply.started":"2023-02-15T15:55:09.177659Z","shell.execute_reply":"2023-02-15T15:55:10.666150Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test__timestamp[['delta','max_time','min_time']].value_counts().head(3)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:55:10.669176Z","iopub.execute_input":"2023-02-15T15:55:10.669901Z","iopub.status.idle":"2023-02-15T15:55:10.682452Z","shell.execute_reply.started":"2023-02-15T15:55:10.669862Z","shell.execute_reply":"2023-02-15T15:55:10.681231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have mainly: \n- **the 2016 historic for the train set**\n- **the 2017 & 2018 historic for the test set** (two years following the train set)","metadata":{}},{"cell_type":"markdown","source":"### Site id","metadata":{}},{"cell_type":"code","source":"# Plotting the number of building ids per site id\nfig, axes = plt.subplots(1,2,figsize=(12,6))\nsns.countplot(data=train_full, x='site_id', ax=axes[0]).set(title='train set')\nsns.countplot(data=test_full, x='site_id', ax=axes[1]).set(title='test set')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:10.684658Z","iopub.execute_input":"2023-02-15T15:55:10.685424Z","iopub.status.idle":"2023-02-15T15:55:15.655302Z","shell.execute_reply.started":"2023-02-15T15:55:10.685387Z","shell.execute_reply":"2023-02-15T15:55:15.653663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have the **same distribution of buildings per site id** which is logical as the test set corresponds to the two following years of the train set","metadata":{}},{"cell_type":"markdown","source":"### primary_use","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['primary_use'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['primary_use'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['primary_use'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['primary_use'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:15.657549Z","iopub.execute_input":"2023-02-15T15:55:15.658442Z","iopub.status.idle":"2023-02-15T15:55:21.253258Z","shell.execute_reply.started":"2023-02-15T15:55:15.658401Z","shell.execute_reply":"2023-02-15T15:55:21.252073Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,2,figsize=(12,6))\nsns.countplot(data=train_full, x='primary_use', ax=axes[0]).set(title='train set')\naxes[0].tick_params(labelrotation=90);\nsns.countplot(data=test_full, x='primary_use', ax=axes[1]).set(title='test set')\naxes[1].tick_params(labelrotation=90);","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:55:21.255250Z","iopub.execute_input":"2023-02-15T15:55:21.255734Z","iopub.status.idle":"2023-02-15T15:56:00.839644Z","shell.execute_reply.started":"2023-02-15T15:55:21.255688Z","shell.execute_reply":"2023-02-15T15:56:00.838328Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have a **majority of Education buildings**","metadata":{}},{"cell_type":"markdown","source":"### Square feet","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['square_feet'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['square_feet'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['square_feet'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['square_feet'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:00.841197Z","iopub.execute_input":"2023-02-15T15:56:00.841632Z","iopub.status.idle":"2023-02-15T15:56:01.010838Z","shell.execute_reply.started":"2023-02-15T15:56:00.841598Z","shell.execute_reply":"2023-02-15T15:56:01.009451Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,2,figsize=(14, 6))\nsns.histplot(data=train_full, x='square_feet',ax=axes[0]).set(title='train set');\nsns.histplot(data=test_full, x='square_feet',ax=axes[1]).set(title='test set');","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:01.012491Z","iopub.execute_input":"2023-02-15T15:56:01.012880Z","iopub.status.idle":"2023-02-15T15:56:28.056678Z","shell.execute_reply.started":"2023-02-15T15:56:01.012846Z","shell.execute_reply":"2023-02-15T15:56:28.055507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have most of the **buildings having a surface lower than 200000 square feet**","metadata":{}},{"cell_type":"markdown","source":"### year_built","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['year_built'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['year_built'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['year_built'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['year_built'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:28.058269Z","iopub.execute_input":"2023-02-15T15:56:28.058605Z","iopub.status.idle":"2023-02-15T15:56:28.542332Z","shell.execute_reply.started":"2023-02-15T15:56:28.058575Z","shell.execute_reply":"2023-02-15T15:56:28.541140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(2,1,figsize=(12,12))\nsns.countplot(data=train_full.groupby(['year_built','building_id']).size().reset_index(), x='year_built', ax=axes[0]).set(title='train set');\naxes[0].tick_params(labelrotation=90)\nsns.countplot(data=test_full.groupby(['year_built','building_id']).size().reset_index(), x='year_built', ax=axes[1]).set(title='test set');\naxes[1].tick_params(labelrotation=90)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:28.543543Z","iopub.execute_input":"2023-02-15T15:56:28.543861Z","iopub.status.idle":"2023-02-15T15:56:39.586063Z","shell.execute_reply.started":"2023-02-15T15:56:28.543831Z","shell.execute_reply":"2023-02-15T15:56:39.584682Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- Approx **60% of missing values** for that feature\n- There is no **year built significantly more frequent unless 1976**","metadata":{}},{"cell_type":"markdown","source":"### floor_count","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['floor_count'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['floor_count'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['floor_count'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['floor_count'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:39.587455Z","iopub.execute_input":"2023-02-15T15:56:39.587798Z","iopub.status.idle":"2023-02-15T15:56:40.070769Z","shell.execute_reply.started":"2023-02-15T15:56:39.587767Z","shell.execute_reply":"2023-02-15T15:56:40.069293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- Approx. **82% of missing values** for that feature\n- This feature won't be used as there are too many missing values","metadata":{}},{"cell_type":"markdown","source":"### air_temperature","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['air_temperature'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['air_temperature'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['air_temperature'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['air_temperature'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:40.072186Z","iopub.execute_input":"2023-02-15T15:56:40.072522Z","iopub.status.idle":"2023-02-15T15:56:40.472662Z","shell.execute_reply.started":"2023-02-15T15:56:40.072491Z","shell.execute_reply":"2023-02-15T15:56:40.470765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='air_temperature',x='timestamp', label='train').set(title='Air Temperature');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='air_temperature',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:14:27.798910Z","iopub.execute_input":"2023-02-15T16:14:27.801535Z","iopub.status.idle":"2023-02-15T16:14:58.048741Z","shell.execute_reply.started":"2023-02-15T16:14:27.801471Z","shell.execute_reply":"2023-02-15T16:14:58.043371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- **less than 1% of missing values for that feature**\n- the **same seasonality for both train and test sets**","metadata":{}},{"cell_type":"markdown","source":"### cloud_coverage","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['cloud_coverage'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['cloud_coverage'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['cloud_coverage'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['cloud_coverage'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:14:58.056143Z","iopub.execute_input":"2023-02-15T16:14:58.058066Z","iopub.status.idle":"2023-02-15T16:14:58.949352Z","shell.execute_reply.started":"2023-02-15T16:14:58.057787Z","shell.execute_reply":"2023-02-15T16:14:58.947765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,2,figsize=(14, 6))\nsns.countplot(data=train_full, x='cloud_coverage',ax=axes[0]).set(title='train set');\nsns.countplot(data=test_full, x='cloud_coverage',ax=axes[1]).set(title='test set');","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:14:58.950871Z","iopub.execute_input":"2023-02-15T16:14:58.952026Z","iopub.status.idle":"2023-02-15T16:15:09.491504Z","shell.execute_reply.started":"2023-02-15T16:14:58.951959Z","shell.execute_reply":"2023-02-15T16:15:09.490264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='cloud_coverage',x='timestamp', label='train').set(title='Cloud Coverage');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='cloud_coverage',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:15:30.747265Z","iopub.execute_input":"2023-02-15T16:15:30.748580Z","iopub.status.idle":"2023-02-15T16:16:00.339826Z","shell.execute_reply.started":"2023-02-15T16:15:30.748520Z","shell.execute_reply":"2023-02-15T16:16:00.338671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- appox. **46% of missing values**\n- the **same distribution and trend between train and test set**","metadata":{}},{"cell_type":"markdown","source":"### dew_temperature","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['dew_temperature'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['dew_temperature'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['dew_temperature'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['dew_temperature'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:17:21.022107Z","iopub.execute_input":"2023-02-15T16:17:21.022623Z","iopub.status.idle":"2023-02-15T16:17:21.421645Z","shell.execute_reply.started":"2023-02-15T16:17:21.022586Z","shell.execute_reply":"2023-02-15T16:17:21.420796Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='dew_temperature',x='timestamp', label='train').set(title='Dew Temperature');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='dew_temperature',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:17:21.422944Z","iopub.execute_input":"2023-02-15T16:17:21.423322Z","iopub.status.idle":"2023-02-15T16:17:48.305798Z","shell.execute_reply.started":"2023-02-15T16:17:21.423289Z","shell.execute_reply":"2023-02-15T16:17:48.304517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- **less than 1% of missing values for that feature**\n- the **same seasonality for both train and test sets**","metadata":{}},{"cell_type":"markdown","source":"### precip_depth_1_hr","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['precip_depth_1_hr'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['precip_depth_1_hr'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['precip_depth_1_hr'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['precip_depth_1_hr'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:17:20.608826Z","iopub.execute_input":"2023-02-15T16:17:20.609175Z","iopub.status.idle":"2023-02-15T16:17:21.020421Z","shell.execute_reply.started":"2023-02-15T16:17:20.609144Z","shell.execute_reply":"2023-02-15T16:17:21.018907Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='train').set(title='Precipitation Depth');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:16:00.341628Z","iopub.execute_input":"2023-02-15T16:16:00.341949Z","iopub.status.idle":"2023-02-15T16:16:27.610490Z","shell.execute_reply.started":"2023-02-15T16:16:00.341921Z","shell.execute_reply":"2023-02-15T16:16:27.609421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('W').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='train').set(title='Precipitation Depth');\nsns.lineplot(data=test_full.set_index('timestamp').resample('W').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:16:27.611884Z","iopub.execute_input":"2023-02-15T16:16:27.612320Z","iopub.status.idle":"2023-02-15T16:16:54.145657Z","shell.execute_reply.started":"2023-02-15T16:16:27.612288Z","shell.execute_reply":"2023-02-15T16:16:54.144389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('M').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='train').set(title='Precipitation Depth');\nsns.lineplot(data=test_full.set_index('timestamp').resample('M').mean().reset_index(),y='precip_depth_1_hr',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:16:54.148702Z","iopub.execute_input":"2023-02-15T16:16:54.149059Z","iopub.status.idle":"2023-02-15T16:17:20.607350Z","shell.execute_reply.started":"2023-02-15T16:16:54.149028Z","shell.execute_reply":"2023-02-15T16:17:20.605775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- Appox. **20% of missing values for that feature**\n- the **same seasonality between 2016 and 2017 but a greater precipitation depth for late 2018**","metadata":{}},{"cell_type":"markdown","source":"### Sea_level_pressure","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['sea_level_pressure'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['sea_level_pressure'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['sea_level_pressure'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['sea_level_pressure'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:17:48.307454Z","iopub.execute_input":"2023-02-15T16:17:48.307919Z","iopub.status.idle":"2023-02-15T16:17:48.708142Z","shell.execute_reply.started":"2023-02-15T16:17:48.307874Z","shell.execute_reply":"2023-02-15T16:17:48.706786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='sea_level_pressure',x='timestamp', label='train').set(title='Sea Level Pressure');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='sea_level_pressure',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:17:48.710034Z","iopub.execute_input":"2023-02-15T16:17:48.710424Z","iopub.status.idle":"2023-02-15T16:18:15.641284Z","shell.execute_reply.started":"2023-02-15T16:17:48.710392Z","shell.execute_reply":"2023-02-15T16:18:15.640053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 8))\ntr = sns.lineplot(data=train_full.set_index('timestamp').resample('W').mean().reset_index(),y='sea_level_pressure',x='timestamp', label='train').set(title='Sea Level Pressure');\ntt = sns.lineplot(data=test_full.set_index('timestamp').resample('W').mean().reset_index(),y='sea_level_pressure',x='timestamp', label='test');\nax2 = axes.twinx()\nax2.grid(False);\nmr = sns.lineplot(data=train_full.set_index('timestamp').resample('W').mean().reset_index(),y='meter_reading',x='timestamp', color='r', label='meter reading on train set');\naxes.legend(loc=0);\nax2.legend(loc=2);","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:18:15.645603Z","iopub.execute_input":"2023-02-15T16:18:15.645986Z","iopub.status.idle":"2023-02-15T16:18:46.882134Z","shell.execute_reply.started":"2023-02-15T16:18:15.645951Z","shell.execute_reply":"2023-02-15T16:18:46.880684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- Approx. **5% of missing values for that feature**\n- the **same seasonality for both train and test sets**\n- There is **positive correlation between sea level pressure and meter reading**","metadata":{}},{"cell_type":"markdown","source":"### wind_direction","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['wind_direction'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['wind_direction'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['wind_direction'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['wind_direction'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:18:46.883783Z","iopub.execute_input":"2023-02-15T16:18:46.884313Z","iopub.status.idle":"2023-02-15T16:18:47.293663Z","shell.execute_reply.started":"2023-02-15T16:18:46.884267Z","shell.execute_reply":"2023-02-15T16:18:47.292275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(2,1,figsize=(14, 12))\nsns.countplot(data=train_full.sort_values(by='wind_direction'), x='wind_direction',ax=axes[0]).set(title='train set');\naxes[0].tick_params(labelrotation=90)\nsns.countplot(data=test_full.sort_values(by='wind_direction'), x='wind_direction',ax=axes[1]).set(title='test set');\naxes[1].tick_params(labelrotation=90)\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:18:47.295508Z","iopub.execute_input":"2023-02-15T16:18:47.295956Z","iopub.status.idle":"2023-02-15T16:19:27.749807Z","shell.execute_reply.started":"2023-02-15T16:18:47.295919Z","shell.execute_reply":"2023-02-15T16:19:27.748526Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(2,1,figsize=(14, 12))\nsns.countplot(data=train_full[train_full['wind_direction']%10==0], x='wind_direction',ax=axes[0]).set(title='train set');\naxes[0].tick_params(labelrotation=90)\nsns.countplot(data=test_full[test_full['wind_direction']%10==0], x='wind_direction',ax=axes[1]).set(title='test set');\naxes[1].tick_params(labelrotation=90)\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:19:27.751292Z","iopub.execute_input":"2023-02-15T16:19:27.751656Z","iopub.status.idle":"2023-02-15T16:19:46.219319Z","shell.execute_reply.started":"2023-02-15T16:19:27.751620Z","shell.execute_reply":"2023-02-15T16:19:46.218161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- approx. **7% of missing values for that feature**\n- the **same distribution for both train and test set**\n- wind direction = 0 is significantly more frequent, this is most probably due to lack of values than actual value = 0","metadata":{}},{"cell_type":"markdown","source":"### wind_speed","metadata":{}},{"cell_type":"code","source":"print('In train set:','\\n','Null values:',round(train_full['wind_speed'].isnull().sum()*100/len(train_full),2),'%', 'Nan values:',round(train_full['wind_speed'].isna().sum()*100/len(train_full),2),'%')\nprint('In test set:','\\n','Null values:',round(test_full['wind_speed'].isnull().sum()*100/len(test_full),2),'%', 'Nan values:',round(test_full['wind_speed'].isna().sum()*100/len(test_full),2),'%')\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:19:46.220896Z","iopub.execute_input":"2023-02-15T16:19:46.221632Z","iopub.status.idle":"2023-02-15T16:19:46.621742Z","shell.execute_reply.started":"2023-02-15T16:19:46.221593Z","shell.execute_reply":"2023-02-15T16:19:46.620282Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('D').mean().reset_index(),y='wind_speed',x='timestamp', label='train').set(title='Wind Speed');\nsns.lineplot(data=test_full.set_index('timestamp').resample('D').mean().reset_index(),y='wind_speed',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:19:46.623601Z","iopub.execute_input":"2023-02-15T16:19:46.623995Z","iopub.status.idle":"2023-02-15T16:20:13.777150Z","shell.execute_reply.started":"2023-02-15T16:19:46.623960Z","shell.execute_reply":"2023-02-15T16:20:13.775933Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,axes = plt.subplots(1,1,figsize=(14, 6))\nsns.lineplot(data=train_full.set_index('timestamp').resample('W').mean().reset_index(),y='wind_speed',x='timestamp', label='train').set(title='Wind Speed');\nsns.lineplot(data=test_full.set_index('timestamp').resample('W').mean().reset_index(),y='wind_speed',x='timestamp', label='test');\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:20:13.778678Z","iopub.execute_input":"2023-02-15T16:20:13.779125Z","iopub.status.idle":"2023-02-15T16:20:40.396123Z","shell.execute_reply.started":"2023-02-15T16:20:13.779058Z","shell.execute_reply":"2023-02-15T16:20:40.394830Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**CONCLUSION**\n\nWe have:\n- **Less than 1% of missing values for that feature**\n- the **same seasonality for both train and test sets**","metadata":{}},{"cell_type":"markdown","source":"## Save modified train and test sets","metadata":{}},{"cell_type":"code","source":"train_full.to_csv('train_full_wo_outliers.csv',index=False)\ntrain_full_w_outliers.to_csv('train_full_w_outliers.csv',index=False) # if we want to keep outliers\ntest_full.to_csv('test_full.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:20:41.354129Z","iopub.execute_input":"2023-02-15T16:20:41.354562Z","iopub.status.idle":"2023-02-15T16:33:57.605844Z","shell.execute_reply.started":"2023-02-15T16:20:41.354521Z","shell.execute_reply":"2023-02-15T16:33:57.603909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_full, train_full_w_outliers, test_full\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:33:57.608013Z","iopub.execute_input":"2023-02-15T16:33:57.608468Z","iopub.status.idle":"2023-02-15T16:33:58.702675Z","shell.execute_reply.started":"2023-02-15T16:33:57.608434Z","shell.execute_reply":"2023-02-15T16:33:58.701400Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## General Conclusion about EDA","metadata":{}},{"cell_type":"markdown","source":"This EDA brought us some insights:\n- train set is the **2016 historic of approx. 1400 buildings** and **test set is the 2017 & 2018 historic of those same buildings**\n\nConsequently, we have for both train and test sets: \n- **the same trends, seasonality and distribution**\n- the **same proportion of missing values** with, as **features with most missing values**:\n    - `floor count` (approx. 80% of missing values)\n    - `year built` (approx. 60% of missing values)\n    - `cloud coverage` (approx. 50% of missing values)\n    - `precip depth 1hr` (approx. 20% of missing values)\n    - `wind direction` (approx. 8% of missing values)\n    - `see level pressure` (approx. 5% of missing values)\n\n \nOn top of that, we saw that there are **some outliers linked to building id 1099 for meter 2**. This might be due to measurement errors in terms of sensors or unit of measure. \nOn a regular basis, we should delete these outliers but as test set is the measurements for the 2 following years, it might be difficult to delete those. \nWe will do the two cases, **train with those outliers** and **train without those outliers**. If training with those outliers, **it is mandatory to scale** `meter reading`.\n","metadata":{}},{"cell_type":"markdown","source":"**Improvements for EDA**\n- For meteorolical data, make a split by site id\n- Add meter reading to plot to visualize potential correlation\n- Compute correlation","metadata":{}},{"cell_type":"markdown","source":"# Predictions","metadata":{}},{"cell_type":"markdown","source":"We will now test **LightGBM** model:\n- **with outliers** \n- **optimization using Optuna**.","metadata":{}},{"cell_type":"markdown","source":"## LightGBM with Outliers","metadata":{}},{"cell_type":"markdown","source":"As we have seen in the EDA, the test set consists of the following two years historic of the same building as train set. Hence **it makes sense to keep all the data** even if there are some outliers.","metadata":{}},{"cell_type":"markdown","source":"### LightGBM: Feature Engineering with Label Encoding and Log Scaling","metadata":{}},{"cell_type":"code","source":"train_full = pd.read_csv('/kaggle/working/train_full_w_outliers.csv')\ntest_full = pd.read_csv('/kaggle/working/test_full.csv')\ntrain_full['timestamp'] = pd.to_datetime(train_full['timestamp'])\ntest_full['timestamp'] = pd.to_datetime(test_full['timestamp'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:33:58.704593Z","iopub.execute_input":"2023-02-15T16:33:58.705019Z","iopub.status.idle":"2023-02-15T16:36:42.494339Z","shell.execute_reply.started":"2023-02-15T16:33:58.704982Z","shell.execute_reply":"2023-02-15T16:36:42.492835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_full = reduce_mem_usage(train_full, use_float16=True)\ntest_full = reduce_mem_usage(test_full, use_float16=True)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:36:42.496563Z","iopub.execute_input":"2023-02-15T16:36:42.496971Z","iopub.status.idle":"2023-02-15T16:37:12.616393Z","shell.execute_reply.started":"2023-02-15T16:36:42.496933Z","shell.execute_reply":"2023-02-15T16:37:12.614996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Some variables to variate feature engineering\ntest = \"ok\" # Apply feature engineering to test set\nlogscale = True # Apply log scale\n\n# Some variables for features engineering\nholidays = [\"2016-01-01\", \"2016-01-18\", \"2016-02-15\", \"2016-05-30\", \"2016-07-04\",\n            \"2016-09-05\", \"2016-10-10\", \"2016-11-11\", \"2016-11-24\", \"2016-12-26\",\n            \"2017-01-01\", \"2017-01-16\", \"2017-02-20\", \"2017-05-29\", \"2017-07-04\",\n            \"2017-09-04\", \"2017-10-09\", \"2017-11-10\", \"2017-11-23\", \"2017-12-25\",\n            \"2018-01-01\", \"2018-01-15\", \"2018-02-19\", \"2018-05-28\", \"2018-07-04\",\n            \"2018-09-03\", \"2018-10-08\", \"2018-11-12\", \"2018-11-22\", \"2018-12-25\",\n            \"2019-01-01\"]\n\nfeatures_label = ['building_id', 'site_id', 'primary_use',\n       'square_feet', 'year_built', 'floor_count', 'air_temperature',\n       'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr',\n       'sea_level_pressure', 'wind_direction', 'wind_speed', 'meters', 'month', 'weekday',\n       'hour', 'day', 'is_holiday']\n\nnumerical_features = ['square_feet',\n       'year_built', 'floor_count', 'air_temperature',\n       'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure',\n       'wind_direction', 'wind_speed']\n\ncategorical_features = [\"building_id\", \"site_id\", \"meters\", \"primary_use\", \"hour\", \"weekday\", \"month\", \"cloud_coverage\", \"is_holiday\"]\n\n# Sort Data by Timestamp\ntrain_full.sort_values('timestamp', inplace=True)\ntrain_full.reset_index(drop=True, inplace=True)\n\n\n# Label Encode Primary Use\nle = LabelEncoder()\ntrain_full['primary_use'] = le.fit_transform(train_full['primary_use'])\n\nif test is not None:\n    test_full['primary_use'] = le.transform(test_full['primary_use'])\n\n\n# Extract information from timestamp\ntrain_full['month'] = train_full['timestamp'].dt.month\ntrain_full['weekday'] = train_full['timestamp'].dt.weekday\ntrain_full['hour'] = train_full['timestamp'].dt.hour\ntrain_full['day'] = train_full['timestamp'].dt.day\n\nif test is not None:\n    test_full['month'] = test_full['timestamp'].dt.month\n    test_full['weekday'] = test_full['timestamp'].dt.weekday\n    test_full['hour'] = test_full['timestamp'].dt.hour\n    test_full['day'] = test_full['timestamp'].dt.day\n\n    \n# Add holidays feature\ntrain_full[\"is_holiday\"] = (train_full['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int)\n\nif test is not None:\n    test_full[\"is_holiday\"] = (test_full['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int) \n    \n\n# Split train data for the scaling (in particular for minmaxscale)\nif logscale:\n    train_full['square_feet'] = np.log1p(train_full['square_feet'])\n    train_full['meter_reading'] = np.log1p(train_full['meter_reading'])\n    data_half_1 = train_full[:int(train_full.shape[0]/2)]\n    data_half_2 = train_full[int(train_full.shape[0]/2):]\n    if test is not None:\n        test_full['square_feet'] = np.log1p(test_full['square_feet'])\n        test_full['meter_reading'] = np.log1p(test_full['meter_reading'])\n\nelse:\n    data_half_1 = train_full[:int(train_full.shape[0]/2)]\n    data_half_2 = train_full[int(train_full.shape[0]/2):]\n    scaler = MinMaxScaler()\n    data_half_1[numerical_features] = scaler.fit_transform(data_half_1[numerical_features])\n    data_half_2[numerical_features] = scaler.transform(data_half_2[numerical_features])\n    y_scaler = MinMaxScaler()\n    data_half_1['meter_reading'] = y_scaler.fit_transform(data_half_1[['meter_reading']])\n    data_half_2['meter_reading'] = y_scaler.transform(data_half_2[['meter_reading']])\n    if test is not None:\n        test_full[numerical_features] = scaler.transform(test_full[numerical_features])\n\n\n# Split into two halves for validation\nX_half_1 = data_half_1[features_label]\nX_half_2 = data_half_2[features_label]\ny_half_1 = data_half_1[['meter_reading']]\ny_half_2 = data_half_2[['meter_reading']]\nif test is not None:\n    X_test = test_full[features_label]\n    \ndel data_half_1, data_half_2, test_full, train_full\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:37:12.618652Z","iopub.execute_input":"2023-02-15T16:37:12.619044Z","iopub.status.idle":"2023-02-15T16:39:25.845743Z","shell.execute_reply.started":"2023-02-15T16:37:12.619009Z","shell.execute_reply":"2023-02-15T16:39:25.844295Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#study2_full = optuna.create_study(direction=\"minimize\", study_name=\"LGBM Regressor log scaled Full Data\")\n#func = lambda trial: objective(trial, X_half_1, X_half_2, y_half_1, y_half_2, scale=False)\n#study2_full.optimize(func, n_trials=40)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.163220Z","iopub.status.idle":"2023-02-15T15:56:46.163764Z","shell.execute_reply.started":"2023-02-15T15:56:46.163486Z","shell.execute_reply":"2023-02-15T15:56:46.163513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#study2_full.optimize(func, n_trials=200)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:56:46.165198Z","iopub.status.idle":"2023-02-15T15:56:46.165620Z","shell.execute_reply.started":"2023-02-15T15:56:46.165419Z","shell.execute_reply":"2023-02-15T15:56:46.165438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#plot_optimization_history(study2_full);","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.166766Z","iopub.status.idle":"2023-02-15T15:56:46.167196Z","shell.execute_reply.started":"2023-02-15T15:56:46.166970Z","shell.execute_reply":"2023-02-15T15:56:46.166989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#plot_param_importances(study2_full);","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.173714Z","iopub.status.idle":"2023-02-15T15:56:46.174219Z","shell.execute_reply.started":"2023-02-15T15:56:46.173975Z","shell.execute_reply":"2023-02-15T15:56:46.173997Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#study2_full.best_trial.params","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.175812Z","iopub.status.idle":"2023-02-15T15:56:46.176272Z","shell.execute_reply.started":"2023-02-15T15:56:46.176028Z","shell.execute_reply":"2023-02-15T15:56:46.176046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"params = {\n    \"objective\": \"regression\",\n    \"boosting\": \"gbdt\",\n    \"metric\": \"rmse\",\n    'n_estimators': 10000,\n    'learning_rate': 0.2763138252184725,\n    'num_leaves': 620,\n    'max_depth': 10,\n    'min_data_in_leaf': 4000,\n    'lambda_l1': 35,\n    'lambda_l2': 70,\n    'min_gain_to_split': 4.147157097212114,\n    'bagging_fraction': 0.5,\n    'bagging_freq': 1,\n    'feature_fraction': 0.6}\n\ntrain_lgb = lgb.Dataset(X_half_1, label=y_half_1, categorical_feature=categorical_features, free_raw_data=False)\nvalidation_lgb = lgb.Dataset(X_half_2, label=y_half_2, categorical_feature=categorical_features, free_raw_data=False)\n\nwatchlist = [train_lgb, validation_lgb]\nlgb_model = lgb.train(params, train_set=train_lgb, num_boost_round=1000, valid_sets=watchlist, verbose_eval=200, early_stopping_rounds=200)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:51:17.126837Z","iopub.execute_input":"2023-02-15T16:51:17.128979Z","iopub.status.idle":"2023-02-15T16:56:25.829164Z","shell.execute_reply.started":"2023-02-15T16:51:17.128902Z","shell.execute_reply":"2023-02-15T16:56:25.826685Z"},"collapsed":true,"jupyter":{"outputs_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_fimp = pd.DataFrame()\ndf_fimp[\"feature\"] = train_lgb.feature_name\ndf_fimp[\"importance\"] = lgb_model.feature_importance()\n\nplt.figure(figsize=(14, 7))\nsns.barplot(x=\"importance\", y=\"feature\", data=df_fimp.sort_values(by=\"importance\", ascending=False))\nplt.title(\"LightGBM Feature Importance\")\nplt.tight_layout()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T16:56:25.832535Z","iopub.execute_input":"2023-02-15T16:56:25.834461Z","iopub.status.idle":"2023-02-15T16:56:26.477177Z","shell.execute_reply.started":"2023-02-15T16:56:25.834375Z","shell.execute_reply":"2023-02-15T16:56:26.475853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**With this scaling**, we can see that **our model is able to provide non constant outputs**","metadata":{}},{"cell_type":"code","source":"y_pred = lgb_model.predict(X_test)\ny_pred = y_pred.reshape(len(y_pred),1)\ny_pred = np.expm1(y_pred)\nrow_ids = X_test.reset_index()['index'].values","metadata":{"execution":{"iopub.status.busy":"2023-02-15T17:03:00.322452Z","iopub.execute_input":"2023-02-15T17:03:00.323395Z","iopub.status.idle":"2023-02-15T17:03:46.444651Z","shell.execute_reply.started":"2023-02-15T17:03:00.323343Z","shell.execute_reply":"2023-02-15T17:03:46.443370Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.DataFrame({\"row_id\": row_ids, \"meter_reading\": np.clip(np.clip(y_pred, 0, a_max=None).reshape(len(y_pred)), 0, a_max=None)})\nsubmission.to_csv(\"submission.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T17:03:46.449253Z","iopub.execute_input":"2023-02-15T17:03:46.449649Z","iopub.status.idle":"2023-02-15T17:05:26.942231Z","shell.execute_reply.started":"2023-02-15T17:03:46.449613Z","shell.execute_reply":"2023-02-15T17:05:26.940611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"os.remove(\"/kaggle/working/train_full_w_outliers.csv\") \nos.remove(\"/kaggle/working/test_full.csv\") \nos.remove(\"/kaggle/working/train_full_wo_outliers.csv\") ","metadata":{"execution":{"iopub.status.busy":"2023-02-15T17:12:29.380659Z","iopub.execute_input":"2023-02-15T17:12:29.382614Z","iopub.status.idle":"2023-02-15T17:12:30.029936Z","shell.execute_reply.started":"2023-02-15T17:12:29.382551Z","shell.execute_reply":"2023-02-15T17:12:30.028843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Score on Kaggle: **1.547**","metadata":{}},{"cell_type":"markdown","source":"## RNN methods","metadata":{}},{"cell_type":"markdown","source":"We will test another method: **RNNs**. \n\nIn order to use this kind of model, we need to transform our data to create inputs with correct shapes.\n\nThe **shape for our training set** will be as followed: **(# building<>Meter, defined timestamp, features).**","metadata":{}},{"cell_type":"code","source":"## Download all the different data\n#train_raw = pd.read_csv(\"train.csv\", sep = ',')\n#test_raw = pd.read_csv(\"test.csv\", sep = ',')\n#b_meta = pd.read_csv(\"building_metadata.csv\",sep = ',')\n#w_train = pd.read_csv(\"weather_train.csv\",sep = ',')\n#w_test = pd.read_csv(\"weather_test.csv\",sep = ',')\n#sample = pd.read_csv(\"sample_submission.csv\",sep = ',')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.185957Z","iopub.status.idle":"2023-02-15T15:56:46.186520Z","shell.execute_reply.started":"2023-02-15T15:56:46.186264Z","shell.execute_reply":"2023-02-15T15:56:46.186299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## We reduce memory usage to preserve RAM\n#train_raw = reduce_mem_usage(train_raw, use_float16=True)\n#test_raw = reduce_mem_usage(test_raw, use_float16=True)\n#b_meta = reduce_mem_usage(b_meta, use_float16=True)\n#w_train = reduce_mem_usage(w_train, use_float16=True)\n#w_test = reduce_mem_usage(w_test, use_float16=True)\n#sample = reduce_mem_usage(sample, use_float16=True)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.187546Z","iopub.status.idle":"2023-02-15T15:56:46.187925Z","shell.execute_reply.started":"2023-02-15T15:56:46.187739Z","shell.execute_reply":"2023-02-15T15:56:46.187756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Prepare Building Metadata**","metadata":{}},{"cell_type":"code","source":"#year_built_gp = b_meta.groupby('site_id')['year_built']","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.191541Z","iopub.status.idle":"2023-02-15T15:56:46.192271Z","shell.execute_reply.started":"2023-02-15T15:56:46.192002Z","shell.execute_reply":"2023-02-15T15:56:46.192034Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Fill in missing year_built with the mean of other buildings in the same site; \n## if none of the buildings in a site has year_built, then fill with the mean of the entire dataset\n#year_built_gp = b_meta.groupby('site_id')['year_built']\n#b_meta['year_built'] = year_built_gp.transform(lambda x: x.fillna(np.floor(x.astype(float).mean())))\n#b_meta['year_built'].fillna(np.floor(np.nanmean(b_meta['year_built'].astype(float))), inplace=True)\n#assert pd.isnull(b_meta['year_built']).sum() == 0\n\n\n## Fill in missing floor_count with the mean of other buildings of the same primary_use; \n## if none of the buildings of a primary use has year_built, then fill with the mean of the entire dataset\n#floor_count_gp = b_meta.groupby('primary_use')['floor_count']\n#b_meta['floor_count'] = floor_count_gp.transform(lambda x: x.fillna(np.floor(x.astype(float).mean())))\n#b_meta['floor_count'].fillna(np.floor(np.nanmean(b_meta['floor_count'].astype(float))), inplace=True)\n#assert pd.isnull(b_meta['floor_count']).sum() == 0\n\n## To conserve RAM:\n#del year_built_gp, floor_count_gp\n\n#gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.194475Z","iopub.status.idle":"2023-02-15T15:56:46.195051Z","shell.execute_reply.started":"2023-02-15T15:56:46.194760Z","shell.execute_reply":"2023-02-15T15:56:46.194789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Weather data**","metadata":{}},{"cell_type":"code","source":"## The following function is inspired by https://www.kaggle.com/aitude/ashrae-missing-weather-data-handling\n#def add_missing_hours(weather_df):\n#    import datetime\n#    time_format = \"%Y-%m-%d %H:%M:%S\"\n#    start_date = datetime.datetime.strptime(weather_df['timestamp'].cat.as_ordered().min(),time_format)\n#    end_date = datetime.datetime.strptime(weather_df['timestamp'].cat.as_ordered().max(),time_format)\n#    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n#    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n\n#    for site_id in range(16):\n#        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n#        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n#        new_rows['site_id'] = site_id\n#        weather_df = pd.concat([weather_df,new_rows]).reset_index(drop=True)\n#    return weather_df\n\n#w_train = add_missing_hours(w_train)\n#w_test = add_missing_hours(w_test)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.197312Z","iopub.status.idle":"2023-02-15T15:56:46.197893Z","shell.execute_reply.started":"2023-02-15T15:56:46.197595Z","shell.execute_reply":"2023-02-15T15:56:46.197624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#weath_col = ['air_temperature', 'cloud_coverage',\n#       'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure',\n#       'wind_direction', 'wind_speed']\n\n## Train set\n## Interpolate the missing data\n#for i in range(w_train['site_id'].nunique()):\n#    w_train[w_train['site_id']==i] = w_train[w_train['site_id']==i].sort_values(by='timestamp').interpolate(method='linear', limit_direction='forward', axis=0)\n\n## for the remaining missing values, we impute with the mean values of the site id x month\n#w_train['timestamp'] = pd.to_datetime(w_train['timestamp'])\n#w_train['month'] = w_train['timestamp'].dt.month\n#gp = w_train.groupby(['site_id', 'month'])\n#for col in weath_col:\n#    w_train[col] = gp[col].transform(lambda x: x.fillna(np.floor(x.astype(float).mean())))\n#    w_train[col].fillna(np.nanmean(w_train[col].astype(float)), inplace=True)\n\n    \n## We do the same with the test set\n#for i in range(w_test['site_id'].nunique()):\n#    w_test[w_test['site_id']==i] = w_test[w_test['site_id']==i].sort_values(by='timestamp').interpolate(method='linear', limit_direction='forward', axis=0)\n\n#w_test['timestamp'] = pd.to_datetime(w_test['timestamp'])\n#w_test['month'] = w_test['timestamp'].dt.month\n#gp = w_test.groupby(['site_id', 'month'])\n#for col in weath_col:\n#    w_test[col] = gp[col].transform(lambda x: x.fillna(np.floor(x.astype(float).mean())))\n#    w_test[col].fillna(np.nanmean(w_test[col].astype(float)), inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.199595Z","iopub.status.idle":"2023-02-15T15:56:46.200169Z","shell.execute_reply.started":"2023-02-15T15:56:46.199866Z","shell.execute_reply":"2023-02-15T15:56:46.199893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Preparing all data**","metadata":{}},{"cell_type":"code","source":"## Combination if all dates possible in train and test sets\n#all_dates_train = pd.DataFrame(np.sort(train_raw['timestamp'].unique()),columns=['timestamp'])\n#all_dates_test = pd.DataFrame(np.sort(test_raw['timestamp'].unique()),columns=['timestamp'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.202233Z","iopub.status.idle":"2023-02-15T15:56:46.202829Z","shell.execute_reply.started":"2023-02-15T15:56:46.202518Z","shell.execute_reply":"2023-02-15T15:56:46.202547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Combination if all building possible in train and test sets\n#all_building_train = pd.DataFrame(train_raw['building_id'].unique(),columns=['building_id'])\n#all_building_test = pd.DataFrame(test_raw['building_id'].unique(),columns=['building_id'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.204402Z","iopub.status.idle":"2023-02-15T15:56:46.205328Z","shell.execute_reply.started":"2023-02-15T15:56:46.204988Z","shell.execute_reply":"2023-02-15T15:56:46.205018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Combination if all building and meter combinations in train and test sets\n#all_building_meter_train = train_raw.groupby(['building_id','meter']).size().reset_index().drop(0,axis=1)\n#all_building_meter_test = test_raw.groupby(['building_id','meter']).size().reset_index().drop(0,axis=1)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.206991Z","iopub.status.idle":"2023-02-15T15:56:46.207577Z","shell.execute_reply.started":"2023-02-15T15:56:46.207292Z","shell.execute_reply":"2023-02-15T15:56:46.207320Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Preparing keys to proceed a cross join\n#all_dates_train['key'] = 0\n#all_building_train['key'] = 0\n#all_dates_test['key'] = 0\n#all_building_test['key'] = 0\n#all_building_meter_train['key'] = 0\n#all_building_meter_test['key'] = 0","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.209211Z","iopub.status.idle":"2023-02-15T15:56:46.209761Z","shell.execute_reply.started":"2023-02-15T15:56:46.209483Z","shell.execute_reply":"2023-02-15T15:56:46.209510Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Join meter, building ids and dates altogether\n#building_time_train = all_building_meter_train.merge(all_dates_train, on='key', how='outer').drop('key',axis=1)\n#building_time_test = all_building_meter_train.merge(all_dates_test, on='key', how='outer').drop('key',axis=1)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.211559Z","iopub.status.idle":"2023-02-15T15:56:46.212126Z","shell.execute_reply.started":"2023-02-15T15:56:46.211824Z","shell.execute_reply":"2023-02-15T15:56:46.211850Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Joining building metadata\n#building_full_train = building_time_train.merge(b_meta, on='building_id')\n#building_full_test = building_time_test.merge(b_meta, on='building_id')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.214012Z","iopub.status.idle":"2023-02-15T15:56:46.214590Z","shell.execute_reply.started":"2023-02-15T15:56:46.214305Z","shell.execute_reply":"2023-02-15T15:56:46.214333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Joining data with train and test data\n#train_full = building_full_train.merge(train_raw,on=['building_id','timestamp','meter'], how='left')\n#test_full = building_full_test.merge(test_raw,on=['building_id','timestamp','meter'], how='left')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.216052Z","iopub.status.idle":"2023-02-15T15:56:46.216629Z","shell.execute_reply.started":"2023-02-15T15:56:46.216349Z","shell.execute_reply":"2023-02-15T15:56:46.216376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Converting to datetime\n#train_full['timestamp'] = pd.to_datetime(train_full['timestamp'])\n#test_full['timestamp'] = pd.to_datetime(test_full['timestamp'])\n## Joining weather data\n#train_full = train_full.merge(w_train, on=['site_id','timestamp'], how='left')\n#test_full = test_full.merge(w_test, on=['site_id','timestamp'], how='left')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.218174Z","iopub.status.idle":"2023-02-15T15:56:46.218729Z","shell.execute_reply.started":"2023-02-15T15:56:46.218451Z","shell.execute_reply":"2023-02-15T15:56:46.218478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## clearing memory\n#del all_dates_train, all_building_train, all_dates_test, all_building_test\n#gc.collect()\n#all_building_meter_train.drop('key',axis=1, inplace=True)\n#all_building_meter_test.drop('key',axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.220646Z","iopub.status.idle":"2023-02-15T15:56:46.221480Z","shell.execute_reply.started":"2023-02-15T15:56:46.221169Z","shell.execute_reply":"2023-02-15T15:56:46.221198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Some variables to variate feature engineering\n#test = \"ok\" # Apply feature engineering to test set\n\n## Some variables for features engineering\n#holidays = [\"2016-01-01\", \"2016-01-18\", \"2016-02-15\", \"2016-05-30\", \"2016-07-04\",\n#            \"2016-09-05\", \"2016-10-10\", \"2016-11-11\", \"2016-11-24\", \"2016-12-26\",\n#            \"2017-01-01\", \"2017-01-16\", \"2017-02-20\", \"2017-05-29\", \"2017-07-04\",\n#            \"2017-09-04\", \"2017-10-09\", \"2017-11-10\", \"2017-11-23\", \"2017-12-25\",\n#            \"2018-01-01\", \"2018-01-15\", \"2018-02-19\", \"2018-05-28\", \"2018-07-04\",\n#            \"2018-09-03\", \"2018-10-08\", \"2018-11-12\", \"2018-11-22\", \"2018-12-25\",\n#            \"2019-01-01\"]\n\n#features_label = ['building_id', 'site_id', 'primary_use',\n#       'square_feet', 'year_built', 'floor_count', 'air_temperature',\n#       'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr',\n#       'sea_level_pressure', 'wind_direction', 'wind_speed', 'meter', 'month', 'weekday',\n#       'hour', 'day', 'is_holiday']\n\n#numerical_features = ['square_feet',\n#       'year_built', 'floor_count', 'air_temperature',\n#       'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure',\n#       'wind_direction', 'wind_speed']\n\n#categorical_features = [\"building_id\", \"site_id\", \"meters\", \"primary_use\", \"hour\", \"weekday\", \"month\", \"cloud_coverage\", \"is_holiday\"]\n\n#train_full['timestamp'] = pd.to_datetime(train_full['timestamp'])\n#test_full['timestamp'] = pd.to_datetime(test_full['timestamp'])\n\n## Label Encode Primary Use\n#le = LabelEncoder()\n#train_full['primary_use'] = le.fit_transform(train_full['primary_use'])\n\n#if test is not None:\n#    test_full['primary_use'] = le.transform(test_full['primary_use'])\n\n\n## Extract information from timestamp\n#train_full['month'] = train_full['timestamp'].dt.month\n#train_full['weekday'] = train_full['timestamp'].dt.weekday\n#train_full['hour'] = train_full['timestamp'].dt.hour\n#train_full['day'] = train_full['timestamp'].dt.day\n\n#if test is not None:\n#    test_full['month'] = test_full['timestamp'].dt.month\n#    test_full['weekday'] = test_full['timestamp'].dt.weekday\n#    test_full['hour'] = test_full['timestamp'].dt.hour\n#    test_full['day'] = test_full['timestamp'].dt.day\n\n    \n## Add holidays feature\n#train_full[\"is_holiday\"] = (train_full['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int)\n\n#if test is not None:\n#    test_full[\"is_holiday\"] = (test_full['timestamp'].dt.date.astype(\"str\").isin(holidays)).astype(int) \n    \n\n## Split train data for the scaling (in particular for minmaxscale)\n\n#train_full['square_feet'] = np.log1p(train_full['square_feet'])\n#train_full['meter_reading'] = np.log1p(train_full['meter_reading'])\n#if test is not None:\n#    test_full['square_feet'] = np.log1p(test_full['square_feet'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.222907Z","iopub.status.idle":"2023-02-15T15:56:46.223954Z","shell.execute_reply.started":"2023-02-15T15:56:46.223643Z","shell.execute_reply":"2023-02-15T15:56:46.223674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Padding the data**\n\nAfter filling all the missing values, we will now prepare the data in order to have the wanted shape.\n\nAs all the building x meter does not have the same length historic, we will keep all the data having at least 8000 records in the train set.","metadata":{}},{"cell_type":"code","source":"#threshold = 8000\n#train_pad = []\n#target_pad = []\n#for i, j in all_building_meter_train.values:\n#    # We want at least 8000 metric reading entries\n#    if train_full[(train_full['building_id']==i)&(train_full['meter']==j)].isna().sum()['meter_reading']<(8784-threshold): \n#        train_pad.append(np.float32(train_full[(train_full['building_id']==i)&(train_full['meter']==j)][features_label].dropna()[-8000:]))\n#        target_pad.append(train_full[(train_full['building_id']==i)&(train_full['meter']==j)]['meter_reading'].dropna()[-8000:])\n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.225303Z","iopub.status.idle":"2023-02-15T15:56:46.225859Z","shell.execute_reply.started":"2023-02-15T15:56:46.225570Z","shell.execute_reply":"2023-02-15T15:56:46.225595Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Stack the data to get final train set\n#train_final = np.stack(train_pad)\n#target_final = np.array(target_pad)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-02-15T15:56:46.227747Z","iopub.status.idle":"2023-02-15T15:56:46.228143Z","shell.execute_reply.started":"2023-02-15T15:56:46.227947Z","shell.execute_reply":"2023-02-15T15:56:46.227965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#print(train_final.shape, target_final.shape)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.229514Z","iopub.status.idle":"2023-02-15T15:56:46.229896Z","shell.execute_reply.started":"2023-02-15T15:56:46.229701Z","shell.execute_reply":"2023-02-15T15:56:46.229726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Model Creation**","metadata":{}},{"cell_type":"code","source":"#train_ds = tf.data.Dataset.from_tensor_slices((train_final, target_final))","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.231371Z","iopub.status.idle":"2023-02-15T15:56:46.231733Z","shell.execute_reply.started":"2023-02-15T15:56:46.231555Z","shell.execute_reply":"2023-02-15T15:56:46.231572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Create a simple RNN model\n#num_features = len(features_label)\n\n#model = tf.keras.Sequential()\n\n## Add RNN: LSTM layer\n#model.add(tf.keras.layers.LSTM(units=32,\n#                               input_shape = (None, num_features),\n#                               dropout = 0.2, \n#                               return_sequences = True)) \n\n## Add output layer\n#model.add(tf.keras.layers.Dense(1)) \n","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.233234Z","iopub.status.idle":"2023-02-15T15:56:46.233629Z","shell.execute_reply.started":"2023-02-15T15:56:46.233445Z","shell.execute_reply":"2023-02-15T15:56:46.233464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Compile model\n#model.compile(optimizer=tf.keras.optimizers.Adam(learning_rate=5e-4),\n#              loss='mse',\n#              metrics=[tf.keras.metrics.RootMeanSquaredError()])#, \n#                       #tf.keras.metrics.MeanSquaredLogarithmicError()])\n#model.summary()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.235203Z","iopub.status.idle":"2023-02-15T15:56:46.235576Z","shell.execute_reply.started":"2023-02-15T15:56:46.235390Z","shell.execute_reply":"2023-02-15T15:56:46.235407Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Model Training**","metadata":{}},{"cell_type":"code","source":"## Train our RNN model\n#model.fit(train_ds.shuffle(50).batch(10), \n#          epochs=30, \n#          callbacks=tf.keras.callbacks.EarlyStopping(monitor='loss', patience=3))","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.236719Z","iopub.status.idle":"2023-02-15T15:56:46.237109Z","shell.execute_reply.started":"2023-02-15T15:56:46.236905Z","shell.execute_reply":"2023-02-15T15:56:46.236922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Save our RNN model\n#model.save('rnn_model')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.238463Z","iopub.status.idle":"2023-02-15T15:56:46.238825Z","shell.execute_reply.started":"2023-02-15T15:56:46.238647Z","shell.execute_reply":"2023-02-15T15:56:46.238664Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Load model if necessary\n#model = tf.keras.models.load_model('rnn_model')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.240135Z","iopub.status.idle":"2023-02-15T15:56:46.240512Z","shell.execute_reply.started":"2023-02-15T15:56:46.240316Z","shell.execute_reply":"2023-02-15T15:56:46.240332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Make Prediction**","metadata":{}},{"cell_type":"code","source":"#test_full['meter_reading'] = np.zeros(test_full.shape[0], dtype=np.float32)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.241931Z","iopub.status.idle":"2023-02-15T15:56:46.242307Z","shell.execute_reply.started":"2023-02-15T15:56:46.242125Z","shell.execute_reply":"2023-02-15T15:56:46.242142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## make prediction on the test set\n#for i, j in all_building_meter_test.values:\n#    ts = np.expand_dims(test_full[(test_full['building_id']==i)&(test_full['meter']==j)][features_label].values, axis=0) \n#    res = np.float32(np.expm1(model.predict(ts).squeeze()))\n#    del ts\n#    test_full.loc[(test_full['building_id']==i)&(test_full['meter']==j), 'meter_reading'] = res\n#    del res\n#gc.collect()","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.243959Z","iopub.status.idle":"2023-02-15T15:56:46.244365Z","shell.execute_reply.started":"2023-02-15T15:56:46.244174Z","shell.execute_reply":"2023-02-15T15:56:46.244192Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#sample_sub = pd.read_csv('sample_submission.csv',\n#                         usecols=['row_id'])","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.245454Z","iopub.status.idle":"2023-02-15T15:56:46.245826Z","shell.execute_reply.started":"2023-02-15T15:56:46.245642Z","shell.execute_reply":"2023-02-15T15:56:46.245659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#sample_sub = sample_sub.merge(test_full[['row_id', 'meter_reading']], on='row_id')","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.246901Z","iopub.status.idle":"2023-02-15T15:56:46.247272Z","shell.execute_reply.started":"2023-02-15T15:56:46.247079Z","shell.execute_reply":"2023-02-15T15:56:46.247110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# save for submissions on Kaggle\n#sample_sub.to_csv('submission_14_02_2023.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2023-02-15T15:56:46.248225Z","iopub.status.idle":"2023-02-15T15:56:46.248582Z","shell.execute_reply.started":"2023-02-15T15:56:46.248404Z","shell.execute_reply":"2023-02-15T15:56:46.248421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Results on Kaggle: **1.653**","metadata":{}},{"cell_type":"markdown","source":"# Conclusion & Further Improvements","metadata":{}},{"cell_type":"markdown","source":"**CONCLUSION**\n\n<ins> EDA <ins>\n- In this Kernel, we have done first a deep EDA enabling us to understand what is our train and test set and what are their caracteristics. \n\n<ins>Feature Engineering<ins>\n- After that, we have tried several technics with the data engineering with a various combination between label encoding vs min-max scaling vs standard scaling vs log scaling, label encoding vs one-hot encoding. We have chosen the most promising ones using a simple LightGBM model and a sample of our train set. \n\n\n<ins> LightGBM Optimization <ins>\n- Using these best feature engineerings, we have optimized our LightGBM model using Optuna during its training. We have been able to see the improvements that we have had using Optuna.\n\n<ins> RNN Model <ins>\n- Finally, we have tried another approach with a RNN model. To do so, we have used a simple one layer LSTM model. Some specific feature engineerings have been done: 1) make train and test sets' shape adapted for our model and 2) make some imputations on missing data, scale data or encode features.\n\n**POSSIBLE IMPROVEMENTS**\n\nSome improvements can be done in this Kernel. \n\n- For the EDA part: when possible display meter_reading to vizualize potential correlation with the feature\n- For the feature engineering part: 1) See the correlation between the different features, 2) Do a better imputation for the missing values\n- For the modelling part, 1) make a better cross validation using for example  a blocked cross validation instead of a half and half validation, 3) improve the RNN model using a more complex one and try some more regularization.\n","metadata":{}}]}