2_Data_Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4a2fba98-fbcf-4e71-8215-7fc782f1e037",
   "metadata": {},
   "source": [
    "# House Keeping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8d4a231f-0738-43ab-8332-0f3cc77889ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import normalize, StandardScaler\n",
    "from sklearn.model_selection import GridSearchCV, RandomizedSearchCV\n",
    "from sklearn.metrics import confusion_matrix, classification_report, roc_auc_score, accuracy_score, roc_curve, auc, recall_score, precision_score, f1_score, precision_recall_curve\n",
    "from sklearn.model_selection import StratifiedKFold,train_test_split,KFold\n",
    "from sklearn.model_selection import cross_val_score, KFold\n",
    "from sklearn.model_selection import cross_validate\n",
    "from sklearn.preprocessing import label_binarize\n",
    "from sklearn import preprocessing\n",
    "\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.experimental import enable_iterative_imputer\n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from sklearn.metrics import confusion_matrix\n",
    "from sklearn.linear_model import Lasso, LinearRegression, Ridge\n",
    "\n",
    "from sklearn.linear_model import LogisticRegression \n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.naive_bayes import MultinomialNB\n",
    "from xgboost import XGBClassifier\n",
    "from sklearn.ensemble import BaggingClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier, VotingClassifier\n",
    "from sklearn.ensemble import AdaBoostClassifier\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "from sklearn.multiclass import OneVsRestClassifier\n",
    "\n",
    "from sklearn.multioutput import MultiOutputClassifier\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "\n",
    "from catboost import Pool,CatBoostClassifier\n",
    "import warnings\n",
    "\n",
    "import shap\n",
    "from pingouin import partial_corr\n",
    "\n",
    "from sklearn.feature_selection import SelectKBest, chi2, f_classif\n",
    "from sklearn.preprocessing import LabelEncoder\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d85a0264-105b-4c37-ab91-9914f557c418",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the data\n",
    "df_data = pd.read_csv(\"C:/Users/Meyke/WiDS/data/train_smogn.csv\")\n",
    "df_test = pd.read_csv(\"C:/Users/Meyke/WiDS/data/test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b69c11cb-ebc9-4d91-9697-a8e09b4bc9ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df_data, df_test])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2b6429ea-66dc-4ddf-a3fd-d29e27a8d38f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set energy star rating to np.nan if 1 (since these values seem to be missing)\n",
    "df['energy_star_rating'] = df.apply(lambda row: np.nan if row.energy_star_rating < 2 else row.energy_star_rating, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "615ecbd1-0746-48f8-b4d5-bd95f56cd181",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>days_above_100F</th>\n",
       "      <th>days_above_110F</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>id</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>118.453650</td>\n",
       "      <td>41927</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>119.167517</td>\n",
       "      <td>41949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>3</td>\n",
       "      <td>29.466343</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116.384798</td>\n",
       "      <td>66798</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>3</td>\n",
       "      <td>29.258065</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>119.313113</td>\n",
       "      <td>66819</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>118.472678</td>\n",
       "      <td>40661</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 65 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "0         0.0            4      State_6    Residential   \n",
       "1         1.0            4      State_6    Residential   \n",
       "2         2.0            4      State_6    Residential   \n",
       "3         3.0            4      State_8     Commercial   \n",
       "4         4.0            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  ELEVATION  \\\n",
       "0              Multifamily_Uncategorized   56543.693554  42.700000   \n",
       "1              Multifamily_Uncategorized  102367.741694  34.755029   \n",
       "2              Multifamily_Uncategorized  159995.763193  12.556233   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280  61.744429   \n",
       "4              Multifamily_Uncategorized  407084.764995  31.235663   \n",
       "\n",
       "   january_min_temp  january_avg_temp  january_max_temp  ...  days_above_100F  \\\n",
       "0                 4         28.596774                58  ...                0   \n",
       "1                 4         28.596774                58  ...                0   \n",
       "2                 3         29.466343                62  ...                0   \n",
       "3                 3         29.258065                62  ...                0   \n",
       "4                 4         28.596774                58  ...                0   \n",
       "\n",
       "   days_above_110F    site_eui     id  year_built  energy_star_rating  \\\n",
       "0                0  118.453650  41927         NaN                 NaN   \n",
       "1                0  119.167517  41949         NaN                 NaN   \n",
       "2                0  116.384798  66798         NaN                 NaN   \n",
       "3                0  119.313113  66819         NaN                 NaN   \n",
       "4                0  118.472678  40661         NaN                 NaN   \n",
       "\n",
       "   direction_max_wind_speed  direction_peak_wind_speed  max_wind_speed  \\\n",
       "0                       NaN                        NaN             NaN   \n",
       "1                       NaN                        NaN             NaN   \n",
       "2                       NaN                        NaN             NaN   \n",
       "3                       NaN                        NaN             NaN   \n",
       "4                       NaN                        NaN             NaN   \n",
       "\n",
       "   days_with_fog  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "\n",
       "[5 rows x 65 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7ca6523d-054f-49ce-bdb9-f72aad8a53cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "original_df_columns = df.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "db864b34-563e-4deb-8540-56bed5a24792",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_buildings = df[['Year_Factor',\n",
    " 'State_Factor',\n",
    " 'building_class',\n",
    " 'facility_type',\n",
    " 'floor_area',\n",
    " 'year_built',\n",
    " 'energy_star_rating',\n",
    " 'ELEVATION','id','site_eui']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9b3b3193-1651-4658-9afd-2019df0a6891",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>id</th>\n",
       "      <th>site_eui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>41927</td>\n",
       "      <td>118.453650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>41949</td>\n",
       "      <td>119.167517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>66798</td>\n",
       "      <td>116.384798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>66819</td>\n",
       "      <td>119.313113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>40661</td>\n",
       "      <td>118.472678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Factor State_Factor building_class  \\\n",
       "0            4      State_6    Residential   \n",
       "1            4      State_6    Residential   \n",
       "2            4      State_6    Residential   \n",
       "3            4      State_8     Commercial   \n",
       "4            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  year_built  \\\n",
       "0              Multifamily_Uncategorized   56543.693554         NaN   \n",
       "1              Multifamily_Uncategorized  102367.741694         NaN   \n",
       "2              Multifamily_Uncategorized  159995.763193         NaN   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280         NaN   \n",
       "4              Multifamily_Uncategorized  407084.764995         NaN   \n",
       "\n",
       "   energy_star_rating  ELEVATION     id    site_eui  \n",
       "0                 NaN  42.700000  41927  118.453650  \n",
       "1                 NaN  34.755029  41949  119.167517  \n",
       "2                 NaN  12.556233  66798  116.384798  \n",
       "3                 NaN  61.744429  66819  119.313113  \n",
       "4                 NaN  31.235663  40661  118.472678  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_buildings.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "736377f3-dc3a-4dab-88ff-d4160e029d10",
   "metadata": {},
   "source": [
    "# 1 Quest for Finding Unique Buildings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a3a3e042-c751-4c6b-b25b-0b726fa554f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">State_4</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Residential</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Multifamily_Uncategorized</th>\n",
       "      <th>59310.0</th>\n",
       "      <th>1950.0</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289267.0</th>\n",
       "      <th>1960.0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107500.0</th>\n",
       "      <th>1952.0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67133.0</th>\n",
       "      <th>1964.0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54096.0</th>\n",
       "      <th>1995.0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                             id\n",
       "State_Factor building_class facility_type             floor_area year_built    \n",
       "State_4      Residential    Multifamily_Uncategorized 59310.0    1950.0       6\n",
       "                                                      289267.0   1960.0       5\n",
       "                                                      107500.0   1952.0       5\n",
       "                                                      67133.0    1964.0       4\n",
       "                                                      54096.0    1995.0       4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Find_Unique_building = df.groupby(['State_Factor',\n",
    " 'building_class',\n",
    " 'facility_type',\n",
    " 'floor_area',\n",
    " 'year_built'])['id'].count().sort_values(ascending=False)\n",
    "\n",
    "# Create frame\n",
    "Find_Unique_building = Find_Unique_building.to_frame()\n",
    "Find_Unique_building.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9e0d67ee-77e2-4b47-8f3b-96fc0557e1a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">State_4</th>\n",
       "      <th rowspan=\"7\" valign=\"top\">Residential</th>\n",
       "      <th rowspan=\"7\" valign=\"top\">Multifamily_Uncategorized</th>\n",
       "      <th>59310.0</th>\n",
       "      <th>1950.0</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289267.0</th>\n",
       "      <th>1960.0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107500.0</th>\n",
       "      <th>1952.0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67133.0</th>\n",
       "      <th>1964.0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54096.0</th>\n",
       "      <th>1995.0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303979.0</th>\n",
       "      <th>1956.0</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169000.0</th>\n",
       "      <th>1966.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_11</th>\n",
       "      <th>Residential</th>\n",
       "      <th>2to4_Unit_Building</th>\n",
       "      <th>35796.0</th>\n",
       "      <th>1968.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">State_4</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Residential</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Multifamily_Uncategorized</th>\n",
       "      <th>64000.0</th>\n",
       "      <th>2007.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63803.0</th>\n",
       "      <th>2000.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">State_11</th>\n",
       "      <th>Residential</th>\n",
       "      <th>2to4_Unit_Building</th>\n",
       "      <th>43228.0</th>\n",
       "      <th>1990.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Commercial</th>\n",
       "      <th>Office_Uncategorized</th>\n",
       "      <th>25872.0</th>\n",
       "      <th>1969.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_4</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Nursing_Home</th>\n",
       "      <th>72844.0</th>\n",
       "      <th>1977.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_8</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Retail_Uncategorized</th>\n",
       "      <th>115928.0</th>\n",
       "      <th>2004.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_2</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Multifamily_Uncategorized</th>\n",
       "      <th>90652.0</th>\n",
       "      <th>1932.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_11</th>\n",
       "      <th>Residential</th>\n",
       "      <th>2to4_Unit_Building</th>\n",
       "      <th>21200.0</th>\n",
       "      <th>1960.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_2</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Multifamily_Uncategorized</th>\n",
       "      <th>84000.0</th>\n",
       "      <th>1912.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">State_4</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Residential</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Multifamily_Uncategorized</th>\n",
       "      <th>75600.0</th>\n",
       "      <th>1926.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63803.0</th>\n",
       "      <th>1999.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Commercial</th>\n",
       "      <th>Nursing_Home</th>\n",
       "      <th>71586.0</th>\n",
       "      <th>1998.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Office_Uncategorized</th>\n",
       "      <th>200150.0</th>\n",
       "      <th>1981.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nursing_Home</th>\n",
       "      <th>60000.0</th>\n",
       "      <th>1996.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Multifamily_Uncategorized</th>\n",
       "      <th>55000.0</th>\n",
       "      <th>1976.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_11</th>\n",
       "      <th>Residential</th>\n",
       "      <th>5plus_Unit_Building</th>\n",
       "      <th>21294.0</th>\n",
       "      <th>1991.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_8</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Office_Uncategorized</th>\n",
       "      <th>248000.0</th>\n",
       "      <th>1983.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State_11</th>\n",
       "      <th>Residential</th>\n",
       "      <th>2to4_Unit_Building</th>\n",
       "      <th>22625.0</th>\n",
       "      <th>2004.0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                             id\n",
       "State_Factor building_class facility_type             floor_area year_built    \n",
       "State_4      Residential    Multifamily_Uncategorized 59310.0    1950.0       6\n",
       "                                                      289267.0   1960.0       5\n",
       "                                                      107500.0   1952.0       5\n",
       "                                                      67133.0    1964.0       4\n",
       "                                                      54096.0    1995.0       4\n",
       "                                                      303979.0   1956.0       3\n",
       "                                                      169000.0   1966.0       2\n",
       "State_11     Residential    2to4_Unit_Building        35796.0    1968.0       2\n",
       "State_4      Residential    Multifamily_Uncategorized 64000.0    2007.0       2\n",
       "                                                      63803.0    2000.0       2\n",
       "State_11     Residential    2to4_Unit_Building        43228.0    1990.0       2\n",
       "             Commercial     Office_Uncategorized      25872.0    1969.0       2\n",
       "State_4      Commercial     Nursing_Home              72844.0    1977.0       2\n",
       "State_8      Commercial     Retail_Uncategorized      115928.0   2004.0       2\n",
       "State_2      Residential    Multifamily_Uncategorized 90652.0    1932.0       2\n",
       "State_11     Residential    2to4_Unit_Building        21200.0    1960.0       2\n",
       "State_2      Residential    Multifamily_Uncategorized 84000.0    1912.0       2\n",
       "State_4      Residential    Multifamily_Uncategorized 75600.0    1926.0       2\n",
       "                                                      63803.0    1999.0       2\n",
       "             Commercial     Nursing_Home              71586.0    1998.0       2\n",
       "                            Office_Uncategorized      200150.0   1981.0       2\n",
       "                            Nursing_Home              60000.0    1996.0       2\n",
       "             Residential    Multifamily_Uncategorized 55000.0    1976.0       2\n",
       "State_11     Residential    5plus_Unit_Building       21294.0    1991.0       2\n",
       "State_8      Commercial     Office_Uncategorized      248000.0   1983.0       2\n",
       "State_11     Residential    2to4_Unit_Building        22625.0    2004.0       2"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Find_Unique_building[Find_Unique_building['id']>1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "96e63ce6-c8f5-49cd-8e94-8666342c3030",
   "metadata": {},
   "outputs": [],
   "source": [
    "Find_Unique_building = Find_Unique_building.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "de307fff-11ca-45c7-b059-88e5bccf3c89",
   "metadata": {},
   "outputs": [],
   "source": [
    "Find_Unique_building = Find_Unique_building[Find_Unique_building['id']>1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bfd34b78-6cf5-43f5-a5b4-0839d33cd4ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>289267.0</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>107500.0</td>\n",
       "      <td>1952.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>67133.0</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>54096.0</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Factor building_class              facility_type  floor_area  \\\n",
       "0      State_4    Residential  Multifamily_Uncategorized     59310.0   \n",
       "1      State_4    Residential  Multifamily_Uncategorized    289267.0   \n",
       "2      State_4    Residential  Multifamily_Uncategorized    107500.0   \n",
       "3      State_4    Residential  Multifamily_Uncategorized     67133.0   \n",
       "4      State_4    Residential  Multifamily_Uncategorized     54096.0   \n",
       "\n",
       "   year_built  id  \n",
       "0      1950.0   6  \n",
       "1      1960.0   5  \n",
       "2      1952.0   5  \n",
       "3      1964.0   4  \n",
       "4      1995.0   4  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Find_Unique_building.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e1b57888-059d-433d-a0dd-3cb288f51a89",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "# Lets use the new index to Find_Unique_building to assign to rows that seem to apply to the same building, in a new column named \"building_id\"\n",
    "df_buildings['building_id'] = df_buildings.apply(lambda row: np.nan if Find_Unique_building[(Find_Unique_building['State_Factor'] == row.State_Factor) & \n",
    "                     (Find_Unique_building['building_class'] == row.building_class) & \n",
    "                     (Find_Unique_building['facility_type'] == row.facility_type) & \n",
    "                     (Find_Unique_building['floor_area'] == row.floor_area) & \n",
    "                     (Find_Unique_building['year_built'] == row.year_built)].empty else Find_Unique_building[(Find_Unique_building['State_Factor'] == row.State_Factor) & \n",
    "                     (Find_Unique_building['building_class'] == row.building_class) & \n",
    "                     (Find_Unique_building['facility_type'] == row.facility_type) & \n",
    "                     (Find_Unique_building['floor_area'] == row.floor_area) & \n",
    "                     (Find_Unique_building['year_built'] == row.year_built)].index.values[0], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ab62858b-47b1-4157-b8dc-38f843b49700",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>id</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>building_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Office_Uncategorized</td>\n",
       "      <td>200150.0</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>77350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1748</th>\n",
       "      <td>7</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Office_Uncategorized</td>\n",
       "      <td>248000.0</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>130.8</td>\n",
       "      <td>77505</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1763</th>\n",
       "      <td>7</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Office_Uncategorized</td>\n",
       "      <td>248000.0</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>130.8</td>\n",
       "      <td>77520</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2627</th>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Nursing_Home</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>241.4</td>\n",
       "      <td>78384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2628</th>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Nursing_Home</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>229.8</td>\n",
       "      <td>78385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8932</th>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>2to4_Unit_Building</td>\n",
       "      <td>43228.0</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>57.3</td>\n",
       "      <td>84689</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9272</th>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>2to4_Unit_Building</td>\n",
       "      <td>43228.0</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>57.3</td>\n",
       "      <td>85029</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9281</th>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>2to4_Unit_Building</td>\n",
       "      <td>21200.0</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>33.5</td>\n",
       "      <td>85038</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9492</th>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>21294.0</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>33.5</td>\n",
       "      <td>85249</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9601</th>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>21294.0</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>33.5</td>\n",
       "      <td>85358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year_Factor State_Factor building_class         facility_type  \\\n",
       "1593            7      State_4     Commercial  Office_Uncategorized   \n",
       "1748            7      State_8     Commercial  Office_Uncategorized   \n",
       "1763            7      State_8     Commercial  Office_Uncategorized   \n",
       "2627            7      State_4     Commercial          Nursing_Home   \n",
       "2628            7      State_4     Commercial          Nursing_Home   \n",
       "...           ...          ...            ...                   ...   \n",
       "8932            7     State_11    Residential    2to4_Unit_Building   \n",
       "9272            7     State_11    Residential    2to4_Unit_Building   \n",
       "9281            7     State_11    Residential    2to4_Unit_Building   \n",
       "9492            7     State_11    Residential   5plus_Unit_Building   \n",
       "9601            7     State_11    Residential   5plus_Unit_Building   \n",
       "\n",
       "      floor_area  year_built  energy_star_rating  ELEVATION     id  site_eui  \\\n",
       "1593    200150.0      1981.0                70.0      260.0  77350       NaN   \n",
       "1748    248000.0      1983.0                87.0      130.8  77505       NaN   \n",
       "1763    248000.0      1983.0                89.0      130.8  77520       NaN   \n",
       "2627     60000.0      1996.0                42.0      241.4  78384       NaN   \n",
       "2628     60000.0      1996.0                38.0      229.8  78385       NaN   \n",
       "...          ...         ...                 ...        ...    ...       ...   \n",
       "8932     43228.0      1990.0               100.0       57.3  84689       NaN   \n",
       "9272     43228.0      1990.0               100.0       57.3  85029       NaN   \n",
       "9281     21200.0      1960.0                82.0       33.5  85038       NaN   \n",
       "9492     21294.0      1991.0                89.0       33.5  85249       NaN   \n",
       "9601     21294.0      1991.0                86.0       33.5  85358       NaN   \n",
       "\n",
       "      building_id  \n",
       "1593         20.0  \n",
       "1748         24.0  \n",
       "1763         24.0  \n",
       "2627         21.0  \n",
       "2628         21.0  \n",
       "...           ...  \n",
       "8932         10.0  \n",
       "9272         10.0  \n",
       "9281         15.0  \n",
       "9492         23.0  \n",
       "9601         23.0  \n",
       "\n",
       "[67 rows x 11 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check correct execution\n",
    "df_buildings[df_buildings['building_id'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "89f95fba-8d43-424c-a09f-694c1447ca61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>id</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>building_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>41927</td>\n",
       "      <td>118.453650</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>41949</td>\n",
       "      <td>119.167517</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>66798</td>\n",
       "      <td>116.384798</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>66819</td>\n",
       "      <td>119.313113</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>40661</td>\n",
       "      <td>118.472678</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Factor State_Factor building_class  \\\n",
       "0            4      State_6    Residential   \n",
       "1            4      State_6    Residential   \n",
       "2            4      State_6    Residential   \n",
       "3            4      State_8     Commercial   \n",
       "4            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  year_built  \\\n",
       "0              Multifamily_Uncategorized   56543.693554         NaN   \n",
       "1              Multifamily_Uncategorized  102367.741694         NaN   \n",
       "2              Multifamily_Uncategorized  159995.763193         NaN   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280         NaN   \n",
       "4              Multifamily_Uncategorized  407084.764995         NaN   \n",
       "\n",
       "   energy_star_rating  ELEVATION     id    site_eui  building_id  \n",
       "0                 NaN  42.700000  41927  118.453650          NaN  \n",
       "1                 NaN  34.755029  41949  119.167517          NaN  \n",
       "2                 NaN  12.556233  66798  116.384798          NaN  \n",
       "3                 NaN  61.744429  66819  119.313113          NaN  \n",
       "4                 NaN  31.235663  40661  118.472678          NaN  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_buildings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ed3dab92-b48a-4ff9-9538-911b81b420b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['building_id'] = df_buildings['building_id'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "17648eb1-5f2e-43c6-80c2-cf961eb0c6e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>days_above_110F</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>id</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "      <th>building_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>118.453650</td>\n",
       "      <td>41927</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>119.167517</td>\n",
       "      <td>41949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>3</td>\n",
       "      <td>29.466343</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>116.384798</td>\n",
       "      <td>66798</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>3</td>\n",
       "      <td>29.258065</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>119.313113</td>\n",
       "      <td>66819</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>118.472678</td>\n",
       "      <td>40661</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "0         0.0            4      State_6    Residential   \n",
       "1         1.0            4      State_6    Residential   \n",
       "2         2.0            4      State_6    Residential   \n",
       "3         3.0            4      State_8     Commercial   \n",
       "4         4.0            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  ELEVATION  \\\n",
       "0              Multifamily_Uncategorized   56543.693554  42.700000   \n",
       "1              Multifamily_Uncategorized  102367.741694  34.755029   \n",
       "2              Multifamily_Uncategorized  159995.763193  12.556233   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280  61.744429   \n",
       "4              Multifamily_Uncategorized  407084.764995  31.235663   \n",
       "\n",
       "   january_min_temp  january_avg_temp  january_max_temp  ...  days_above_110F  \\\n",
       "0                 4         28.596774                58  ...                0   \n",
       "1                 4         28.596774                58  ...                0   \n",
       "2                 3         29.466343                62  ...                0   \n",
       "3                 3         29.258065                62  ...                0   \n",
       "4                 4         28.596774                58  ...                0   \n",
       "\n",
       "     site_eui     id  year_built  energy_star_rating  \\\n",
       "0  118.453650  41927         NaN                 NaN   \n",
       "1  119.167517  41949         NaN                 NaN   \n",
       "2  116.384798  66798         NaN                 NaN   \n",
       "3  119.313113  66819         NaN                 NaN   \n",
       "4  118.472678  40661         NaN                 NaN   \n",
       "\n",
       "   direction_max_wind_speed  direction_peak_wind_speed  max_wind_speed  \\\n",
       "0                       NaN                        NaN             NaN   \n",
       "1                       NaN                        NaN             NaN   \n",
       "2                       NaN                        NaN             NaN   \n",
       "3                       NaN                        NaN             NaN   \n",
       "4                       NaN                        NaN             NaN   \n",
       "\n",
       "   days_with_fog  building_id  \n",
       "0            NaN          NaN  \n",
       "1            NaN          NaN  \n",
       "2            NaN          NaN  \n",
       "3            NaN          NaN  \n",
       "4            NaN          NaN  \n",
       "\n",
       "[5 rows x 66 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c67c48d9-28ec-4a73-b3d9-e18711d256d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "building_id    75620\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['building_id']].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2d72bbc9-1945-449b-bf9a-5a2fffdcf6c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['building_id'] = df.apply(lambda row:  row.building_id if row.building_id == row.building_id else\n",
    "                             df['building_id'].max() + row.id, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "906a533f-e91b-4e27-9faa-b9c554f64933",
   "metadata": {},
   "outputs": [],
   "source": [
    "le = LabelEncoder()\n",
    "df['building_id'] = le.fit_transform(df['building_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "a5af0156-05f3-4f61-b450-55c7ae0e59af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>days_above_110F</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>id</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "      <th>building_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3112</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78869</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3221</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78978</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3240</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78997</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8017</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>83774</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8294</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84051</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8440</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>59310.0</td>\n",
       "      <td>186.5</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84197</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "3112         NaN            7      State_4    Residential   \n",
       "3221         NaN            7      State_4    Residential   \n",
       "3240         NaN            7      State_4    Residential   \n",
       "8017         NaN            7      State_4    Residential   \n",
       "8294         NaN            7      State_4    Residential   \n",
       "8440         NaN            7      State_4    Residential   \n",
       "\n",
       "                  facility_type  floor_area  ELEVATION  january_min_temp  \\\n",
       "3112  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "3221  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "3240  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "8017  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "8294  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "8440  Multifamily_Uncategorized     59310.0      186.5                -1   \n",
       "\n",
       "      january_avg_temp  january_max_temp  ...  days_above_110F  site_eui  \\\n",
       "3112         27.548387                59  ...                0       NaN   \n",
       "3221         27.548387                59  ...                0       NaN   \n",
       "3240         27.548387                59  ...                0       NaN   \n",
       "8017         27.548387                59  ...                0       NaN   \n",
       "8294         27.548387                59  ...                0       NaN   \n",
       "8440         27.548387                59  ...                0       NaN   \n",
       "\n",
       "         id  year_built  energy_star_rating  direction_max_wind_speed  \\\n",
       "3112  78869      1950.0                59.0                     340.0   \n",
       "3221  78978      1950.0                 4.0                     340.0   \n",
       "3240  78997      1950.0                18.0                     340.0   \n",
       "8017  83774      1950.0                26.0                     340.0   \n",
       "8294  84051      1950.0                31.0                     340.0   \n",
       "8440  84197      1950.0                58.0                     340.0   \n",
       "\n",
       "      direction_peak_wind_speed  max_wind_speed  days_with_fog  building_id  \n",
       "3112                      320.0            19.2            NaN            0  \n",
       "3221                      320.0            19.2            NaN            0  \n",
       "3240                      320.0            19.2            NaN            0  \n",
       "8017                      320.0            19.2            NaN            0  \n",
       "8294                      320.0            19.2            NaN            0  \n",
       "8440                      320.0            19.2            NaN            0  \n",
       "\n",
       "[6 rows x 66 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['building_id']==0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b75f9b1f-f1bb-45eb-8836-4c43c2eb7705",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>days_above_110F</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>id</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "      <th>building_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>118.453650</td>\n",
       "      <td>41927</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>119.167517</td>\n",
       "      <td>41949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>3</td>\n",
       "      <td>29.466343</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>116.384798</td>\n",
       "      <td>66798</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>3</td>\n",
       "      <td>29.258065</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>119.313113</td>\n",
       "      <td>66819</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>118.472678</td>\n",
       "      <td>40661</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "0         0.0            4      State_6    Residential   \n",
       "1         1.0            4      State_6    Residential   \n",
       "2         2.0            4      State_6    Residential   \n",
       "3         3.0            4      State_8     Commercial   \n",
       "4         4.0            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  ELEVATION  \\\n",
       "0              Multifamily_Uncategorized   56543.693554  42.700000   \n",
       "1              Multifamily_Uncategorized  102367.741694  34.755029   \n",
       "2              Multifamily_Uncategorized  159995.763193  12.556233   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280  61.744429   \n",
       "4              Multifamily_Uncategorized  407084.764995  31.235663   \n",
       "\n",
       "   january_min_temp  january_avg_temp  january_max_temp  ...  days_above_110F  \\\n",
       "0                 4         28.596774                58  ...                0   \n",
       "1                 4         28.596774                58  ...                0   \n",
       "2                 3         29.466343                62  ...                0   \n",
       "3                 3         29.258065                62  ...                0   \n",
       "4                 4         28.596774                58  ...                0   \n",
       "\n",
       "     site_eui     id  year_built  energy_star_rating  \\\n",
       "0  118.453650  41927         NaN                 NaN   \n",
       "1  119.167517  41949         NaN                 NaN   \n",
       "2  116.384798  66798         NaN                 NaN   \n",
       "3  119.313113  66819         NaN                 NaN   \n",
       "4  118.472678  40661         NaN                 NaN   \n",
       "\n",
       "   direction_max_wind_speed  direction_peak_wind_speed  max_wind_speed  \\\n",
       "0                       NaN                        NaN             NaN   \n",
       "1                       NaN                        NaN             NaN   \n",
       "2                       NaN                        NaN             NaN   \n",
       "3                       NaN                        NaN             NaN   \n",
       "4                       NaN                        NaN             NaN   \n",
       "\n",
       "   days_with_fog  building_id  \n",
       "0            NaN        27109  \n",
       "1            NaN        27125  \n",
       "2            NaN        43278  \n",
       "3            NaN        43293  \n",
       "4            NaN        26176  \n",
       "\n",
       "[5 rows x 66 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "03e8d408-4742-4577-95ff-c0565012daa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_B_id_count = df.groupby(['building_id'])['building_id'].count().sort_values(ascending=False).to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9c9addef-c1e0-4525-aafd-6a9c3f97b21d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_B_id_count = df_B_id_count.rename(columns={\"building_id\": \"count\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "af345412-70d6-4cb4-a1d9-9ca4e59b8f23",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count\n",
       "1    44497\n",
       "2    10932\n",
       "3     2275\n",
       "4      459\n",
       "5      101\n",
       "6       22\n",
       "7        4\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_B_id_count.groupby(['count'])['count'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee943184-a760-4945-90e5-0f150f4b07d9",
   "metadata": {},
   "source": [
    "# 3 impute energy_star_rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "10902b48-9746-4cc8-b1d6-bd4692f69ced",
   "metadata": {},
   "outputs": [],
   "source": [
    "building_id_list = df['building_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9cbc6b7a-b561-46ba-86c0-cb8913dbbba9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "\n",
    "def impute_star(row):\n",
    "    if row.energy_star_rating == row.energy_star_rating:\n",
    "        return row.energy_star_rating\n",
    "    else:\n",
    "        B_id = row.building_id\n",
    "        B_year = row.Year_Factor\n",
    "        \n",
    "        temp_df = df[df['building_id'] == B_id] \n",
    "        \n",
    "        # list_star is list of energy_star_ratings\n",
    "        list_star = temp_df['energy_star_rating'].to_list()\n",
    "        \n",
    "        # list_year is list of year_factor\n",
    "        list_year = temp_df['Year_Factor'].to_list()\n",
    "        \n",
    "        # list of the indexes of the nan values in list_star\n",
    "        nan_list = [list_star.index(x) for x in list_star if math.isnan(x) == True]\n",
    "        len_nan_list = len(nan_list)\n",
    "        #print(\"nan_list\", nan_list)\n",
    "        \n",
    "        if len_nan_list > 0:\n",
    "            # neem index 0 van len_nan_list\n",
    "            smallest_nan_value = nan_list[0]\n",
    "            #print(\"smallest:\", smallest_nan_value)\n",
    "            # neem laatste index van len_nan_list\n",
    "            largest_nan_value = nan_list[-1]\n",
    "            #print(\"largest:\", largest_nan_value)\n",
    "        \n",
    "            # check of er kleinere waarde in de list_star zitten, neem die value, het gemiddelde van die values of np.nan als er geen zijn\n",
    "            if smallest_nan_value > 0:\n",
    "                #print(\"smaller than 0\")\n",
    "                get_year = list_year[smallest_nan_value - 1]\n",
    "                # check if the previous year is a duplicate year\n",
    "                duplicate_year = list_year.count(get_year) > 1\n",
    "                if duplicate_year:\n",
    "                    # get indexes of the duplicate year\n",
    "                    get_indices_years = [i for i,val in enumerate(list_year) if val==get_year]\n",
    "                    # calc the average of the star ratings\n",
    "                    temp_previous_year = 0\n",
    "                    for z in get_indices_years:\n",
    "                        temp_previous_year += list_star[z]\n",
    "                    temp_previous_year = temp_previous_year/z\n",
    "                else:\n",
    "                    temp_previous_year = list_star[list_year.index(get_year)]\n",
    "            else:\n",
    "                #print(\"larger than 0\")\n",
    "                temp_previous_year = np.nan\n",
    "\n",
    "            # check of er grotere waarde in de list_star zitten, neem die value, het gemiddelde van die values of np.nan als er geen zijn\n",
    "            if largest_nan_value+1 < len(list_star):\n",
    "                #print(\"smaller than length of the list\", largest_nan_value)\n",
    "                get_year = list_year[largest_nan_value + 1]\n",
    "                #print(\"list_year\", list_year)\n",
    "                #print(\"get_year\", get_year)\n",
    "                #print(\"star_list\", list_star)\n",
    "                # check if the next year is a duplicate year\n",
    "                duplicate_year = list_year.count(get_year) > 1\n",
    "                if duplicate_year:\n",
    "                    # get indexes of the duplicate year\n",
    "                    get_indices_years = [i for i,val in enumerate(list_year) if val==get_year]\n",
    "                    # calc the average of the star ratings\n",
    "                    temp_next_year = 0\n",
    "                    for z in get_indices_years:\n",
    "                        temp_next_year += list_star[z]\n",
    "                    temp_next_year = temp_next_year/z\n",
    "                else:\n",
    "                    temp_next_year = list_star[list_year.index(get_year)]\n",
    "\n",
    "            else:\n",
    "                temp_next_year = np.nan\n",
    "\n",
    "\n",
    "            if (temp_next_year == temp_next_year) & (temp_previous_year ==  temp_previous_year):\n",
    "                return (temp_next_year+temp_previous_year)/2\n",
    "            elif (temp_next_year == temp_next_year) & (temp_previous_year != temp_previous_year):\n",
    "                return temp_next_year\n",
    "            elif (temp_next_year != temp_next_year) & (temp_previous_year == temp_previous_year):\n",
    "                return temp_previous_year\n",
    "            else:\n",
    "                return np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f67b1613-d73d-4cad-aae4-ca6593cb3194",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['star'] = df.apply(impute_star, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6b57fa59-9420-4286-90f5-af8e407b40eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>site_eui</th>\n",
       "      <th>id</th>\n",
       "      <th>year_built</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>direction_max_wind_speed</th>\n",
       "      <th>direction_peak_wind_speed</th>\n",
       "      <th>max_wind_speed</th>\n",
       "      <th>days_with_fog</th>\n",
       "      <th>building_id</th>\n",
       "      <th>star</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28103</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>61242.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>36</td>\n",
       "      <td>50.500000</td>\n",
       "      <td>68</td>\n",
       "      <td>...</td>\n",
       "      <td>248.682615</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "      <td>90.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>28484.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75757</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48652</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>21906.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75758</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48653</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>97422.0</td>\n",
       "      <td>35.4</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75760</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48655</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Grocery_store_or_food_market</td>\n",
       "      <td>61242.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75761</td>\n",
       "      <td>1942.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>126.0</td>\n",
       "      <td>48656</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9700</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>305925.0</td>\n",
       "      <td>812.0</td>\n",
       "      <td>21</td>\n",
       "      <td>38.661290</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85457</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58285</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9701</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>34909.0</td>\n",
       "      <td>26.5</td>\n",
       "      <td>21</td>\n",
       "      <td>38.661290</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85458</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58286</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9702</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>42393.0</td>\n",
       "      <td>812.0</td>\n",
       "      <td>21</td>\n",
       "      <td>38.661290</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85459</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58287</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9703</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>25368.0</td>\n",
       "      <td>812.0</td>\n",
       "      <td>21</td>\n",
       "      <td>38.661290</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85460</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58288</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9704</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_11</td>\n",
       "      <td>Residential</td>\n",
       "      <td>5plus_Unit_Building</td>\n",
       "      <td>45640.0</td>\n",
       "      <td>26.5</td>\n",
       "      <td>21</td>\n",
       "      <td>38.661290</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85461</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58289</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7340 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "28103         0.0            1      State_1     Commercial   \n",
       "0             NaN            7      State_1     Commercial   \n",
       "1             NaN            7      State_1     Commercial   \n",
       "3             NaN            7      State_1     Commercial   \n",
       "4             NaN            7      State_1     Commercial   \n",
       "...           ...          ...          ...            ...   \n",
       "9700          NaN            7     State_11    Residential   \n",
       "9701          NaN            7     State_11    Residential   \n",
       "9702          NaN            7     State_11    Residential   \n",
       "9703          NaN            7     State_11    Residential   \n",
       "9704          NaN            7     State_11    Residential   \n",
       "\n",
       "                      facility_type  floor_area  ELEVATION  january_min_temp  \\\n",
       "28103  Grocery_store_or_food_market     61242.0        2.4                36   \n",
       "0      Grocery_store_or_food_market     28484.0        2.4                38   \n",
       "1      Grocery_store_or_food_market     21906.0       45.7                38   \n",
       "3      Grocery_store_or_food_market     97422.0       35.4                38   \n",
       "4      Grocery_store_or_food_market     61242.0        1.8                38   \n",
       "...                             ...         ...        ...               ...   \n",
       "9700            5plus_Unit_Building    305925.0      812.0                21   \n",
       "9701            5plus_Unit_Building     34909.0       26.5                21   \n",
       "9702            5plus_Unit_Building     42393.0      812.0                21   \n",
       "9703            5plus_Unit_Building     25368.0      812.0                21   \n",
       "9704            5plus_Unit_Building     45640.0       26.5                21   \n",
       "\n",
       "       january_avg_temp  january_max_temp  ...    site_eui     id  year_built  \\\n",
       "28103         50.500000                68  ...  248.682615      0         NaN   \n",
       "0             50.596774                64  ...         NaN  75757      1994.0   \n",
       "1             50.596774                64  ...         NaN  75758      1961.0   \n",
       "3             50.596774                64  ...         NaN  75760      1971.0   \n",
       "4             50.596774                64  ...         NaN  75761      1942.0   \n",
       "...                 ...               ...  ...         ...    ...         ...   \n",
       "9700          38.661290                54  ...         NaN  85457      2013.0   \n",
       "9701          38.661290                54  ...         NaN  85458      1910.0   \n",
       "9702          38.661290                54  ...         NaN  85459      2014.0   \n",
       "9703          38.661290                54  ...         NaN  85460      1992.0   \n",
       "9704          38.661290                54  ...         NaN  85461      1994.0   \n",
       "\n",
       "       energy_star_rating  direction_max_wind_speed  \\\n",
       "28103                 NaN                       NaN   \n",
       "0                    37.0                       NaN   \n",
       "1                    55.0                       NaN   \n",
       "3                    34.0                       NaN   \n",
       "4                    35.0                     340.0   \n",
       "...                   ...                       ...   \n",
       "9700                 99.0                       NaN   \n",
       "9701                 94.0                       NaN   \n",
       "9702                 94.0                       NaN   \n",
       "9703                 79.0                       NaN   \n",
       "9704                 87.0                       NaN   \n",
       "\n",
       "       direction_peak_wind_speed  max_wind_speed  days_with_fog  building_id  \\\n",
       "28103                        NaN             NaN            NaN           25   \n",
       "0                            NaN             NaN            NaN        48652   \n",
       "1                            NaN             NaN            NaN        48653   \n",
       "3                            NaN             NaN            NaN        48655   \n",
       "4                          330.0            22.8          126.0        48656   \n",
       "...                          ...             ...            ...          ...   \n",
       "9700                         NaN             NaN            NaN        58285   \n",
       "9701                         NaN             NaN            NaN        58286   \n",
       "9702                         NaN             NaN            NaN        58287   \n",
       "9703                         NaN             NaN            NaN        58288   \n",
       "9704                         NaN             NaN            NaN        58289   \n",
       "\n",
       "       star  \n",
       "28103  90.5  \n",
       "0      37.0  \n",
       "1      55.0  \n",
       "3      34.0  \n",
       "4      35.0  \n",
       "...     ...  \n",
       "9700   99.0  \n",
       "9701   94.0  \n",
       "9702   94.0  \n",
       "9703   79.0  \n",
       "9704   87.0  \n",
       "\n",
       "[7340 rows x 67 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# almost 30061K rows imputed\n",
    "df[df['star'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "f5160854-4669-461b-af90-1abea706096f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "building_class  facility_type                            \n",
       "Commercial      Commercial_Other                             316\n",
       "                Commercial_Unknown                            21\n",
       "                Data_Center                                    3\n",
       "                Education_College_or_university              201\n",
       "                Education_Other_classroom                     31\n",
       "                Education_Preschool_or_daycare                 3\n",
       "                Education_Uncategorized                       11\n",
       "                Food_Sales                                     2\n",
       "                Food_Service_Other                             3\n",
       "                Food_Service_Restaurant_or_cafeteria          17\n",
       "                Food_Service_Uncategorized                     4\n",
       "                Grocery_store_or_food_market                  10\n",
       "                Health_Care_Inpatient                         15\n",
       "                Health_Care_Outpatient_Clinic                  5\n",
       "                Health_Care_Outpatient_Uncategorized           2\n",
       "                Health_Care_Uncategorized                      2\n",
       "                Industrial                                    43\n",
       "                Laboratory                                    44\n",
       "                Lodging_Dormitory_or_fraternity_sorority      22\n",
       "                Lodging_Hotel                                 14\n",
       "                Lodging_Other                                  8\n",
       "                Lodging_Uncategorized                          5\n",
       "                Nursing_Home                                  32\n",
       "                Office_Medical_non_diagnostic                  9\n",
       "                Office_Mixed_use                               2\n",
       "                Office_Uncategorized                         210\n",
       "                Parking_Garage                                37\n",
       "                Public_Assembly_Drama_theater                 14\n",
       "                Public_Assembly_Entertainment_culture         20\n",
       "                Public_Assembly_Library                       17\n",
       "                Public_Assembly_Movie_Theater                 13\n",
       "                Public_Assembly_Other                          7\n",
       "                Public_Assembly_Recreation                    26\n",
       "                Public_Assembly_Social_meeting                14\n",
       "                Public_Assembly_Stadium                        3\n",
       "                Public_Assembly_Uncategorized                  8\n",
       "                Public_Safety_Fire_or_police_station           8\n",
       "                Public_Safety_Penitentiary                     8\n",
       "                Public_Safety_Uncategorized                   16\n",
       "                Religious_worship                             23\n",
       "                Retail_Enclosed_mall                          33\n",
       "                Retail_Strip_shopping_mall                    37\n",
       "                Retail_Uncategorized                          44\n",
       "                Retail_Vehicle_dealership_showroom            12\n",
       "                Service_Drycleaning_or_Laundry                 2\n",
       "                Service_Uncategorized                         15\n",
       "                Service_Vehicle_service_repair_shop            9\n",
       "                Warehouse_Distribution_or_Shipping_center     22\n",
       "                Warehouse_Nonrefrigerated                     25\n",
       "                Warehouse_Refrigerated                         7\n",
       "                Warehouse_Selfstorage                         68\n",
       "                Warehouse_Uncategorized                       43\n",
       "Residential     2to4_Unit_Building                           209\n",
       "                5plus_Unit_Building                          170\n",
       "                Mixed_Use_Commercial_and_Residential         150\n",
       "                Mixed_Use_Predominantly_Commercial            44\n",
       "                Mixed_Use_Predominantly_Residential            1\n",
       "                Multifamily_Uncategorized                    226\n",
       "Name: facility_type, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_site_and_star = df[(df['Year_Factor']==7)&(df['star'].isna())].groupby(['building_class','facility_type'])['facility_type'].count()\n",
    "missing_site_and_star"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1af043af-635a-4187-bd7a-47c19d0575f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def impute_missing_star(row):\n",
    "    if row.star == row.star:\n",
    "        return row.star\n",
    "    else:\n",
    "        return df[df['facility_type']== row.facility_type]['energy_star_rating'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "896680af-3dac-41fc-a3b7-efaab481850f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['star'] = df.apply(impute_missing_star, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "0a50c52e-c6fc-4cdf-9478-0d1fa2ee3b1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['energy_star_rating']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "762b8edc-4503-404a-b39e-43ae9220bd28",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename(columns={\"star\": \"energy_star_rating\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61d77c20-7913-462d-9ef4-67f6cd1c7530",
   "metadata": {},
   "source": [
    "# 4 Create other wind columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "0d840e5e-4135-4cc8-bc2d-511772a3f447",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['peak_NE'] = df.apply(lambda row: 1 if (row.direction_peak_wind_speed < 91) & (row.direction_peak_wind_speed > 0) else 0, axis=1)\n",
    "df['peak_SE'] = df.apply(lambda row: 1 if (row.direction_peak_wind_speed < 181) & (row.direction_peak_wind_speed > 90) else 0, axis=1)\n",
    "df['peak_SW'] = df.apply(lambda row: 1 if (row.direction_peak_wind_speed < 271) & (row.direction_peak_wind_speed > 180) else 0, axis=1)\n",
    "df['peak_NW'] = df.apply(lambda row: 1 if (row.direction_peak_wind_speed < 361) & (row.direction_peak_wind_speed > 270) else 0, axis=1)\n",
    "\n",
    "df['max_NE'] = df.apply(lambda row: 1 if (row.direction_max_wind_speed < 91) & (row.direction_max_wind_speed > 0) else 0, axis=1)\n",
    "df['max_SE'] = df.apply(lambda row: 1 if (row.direction_max_wind_speed < 181) & (row.direction_max_wind_speed > 90) else 0, axis=1)\n",
    "df['max_SW'] = df.apply(lambda row: 1 if (row.direction_max_wind_speed < 271) & (row.direction_max_wind_speed > 180) else 0, axis=1)\n",
    "df['max_NW'] = df.apply(lambda row: 1 if (row.direction_max_wind_speed < 361) & (row.direction_max_wind_speed > 270) else 0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "457e8c2e-1780-470e-99c2-d63d38be6fdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['direction_max_wind_speed']\n",
    "del df['direction_peak_wind_speed']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cbbb439-186b-4643-957b-9e38022bae8c",
   "metadata": {},
   "source": [
    "# 5 fill na for days with fog and max wind speed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "90d3fa50-f6e0-405e-8014-7b84afb3baf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['days_with_fog'] = df['days_with_fog'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "61f39dfd-da11-4605-b25d-3969bb720ea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['max_wind_speed'] = df['max_wind_speed'].fillna(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8e60ddd4-7a18-4bdf-9a13-27e26d93d9ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>building_id</th>\n",
       "      <th>site_eui</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>State_8</td>\n",
       "      <td>4</td>\n",
       "      <td>1.315443e+05</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>43293</td>\n",
       "      <td>119.313113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>State_6</td>\n",
       "      <td>6</td>\n",
       "      <td>4.741135e+04</td>\n",
       "      <td>Public_Safety_Fire_or_police_station</td>\n",
       "      <td>41299</td>\n",
       "      <td>128.122115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>State_6</td>\n",
       "      <td>6</td>\n",
       "      <td>2.228793e+04</td>\n",
       "      <td>Public_Safety_Fire_or_police_station</td>\n",
       "      <td>41290</td>\n",
       "      <td>118.971034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>State_4</td>\n",
       "      <td>6</td>\n",
       "      <td>1.468219e+05</td>\n",
       "      <td>Retail_Enclosed_mall</td>\n",
       "      <td>8588</td>\n",
       "      <td>123.175888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>State_4</td>\n",
       "      <td>6</td>\n",
       "      <td>1.857208e+05</td>\n",
       "      <td>Retail_Enclosed_mall</td>\n",
       "      <td>8787</td>\n",
       "      <td>158.335108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8792</th>\n",
       "      <td>State_2</td>\n",
       "      <td>7</td>\n",
       "      <td>6.811600e+04</td>\n",
       "      <td>Lodging_Other</td>\n",
       "      <td>57384</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8796</th>\n",
       "      <td>State_4</td>\n",
       "      <td>7</td>\n",
       "      <td>4.570176e+06</td>\n",
       "      <td>Public_Safety_Penitentiary</td>\n",
       "      <td>57388</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8797</th>\n",
       "      <td>State_8</td>\n",
       "      <td>7</td>\n",
       "      <td>1.600000e+05</td>\n",
       "      <td>Public_Safety_Penitentiary</td>\n",
       "      <td>57389</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8798</th>\n",
       "      <td>State_8</td>\n",
       "      <td>7</td>\n",
       "      <td>4.362500e+05</td>\n",
       "      <td>Public_Safety_Penitentiary</td>\n",
       "      <td>57390</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8799</th>\n",
       "      <td>State_8</td>\n",
       "      <td>7</td>\n",
       "      <td>1.089600e+05</td>\n",
       "      <td>Public_Safety_Penitentiary</td>\n",
       "      <td>57391</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3717 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     State_Factor  Year_Factor    floor_area  \\\n",
       "3         State_8            4  1.315443e+05   \n",
       "26        State_6            6  4.741135e+04   \n",
       "27        State_6            6  2.228793e+04   \n",
       "96        State_4            6  1.468219e+05   \n",
       "97        State_4            6  1.857208e+05   \n",
       "...           ...          ...           ...   \n",
       "8792      State_2            7  6.811600e+04   \n",
       "8796      State_4            7  4.570176e+06   \n",
       "8797      State_8            7  1.600000e+05   \n",
       "8798      State_8            7  4.362500e+05   \n",
       "8799      State_8            7  1.089600e+05   \n",
       "\n",
       "                              facility_type  building_id    site_eui  \n",
       "3     Public_Assembly_Entertainment_culture        43293  119.313113  \n",
       "26     Public_Safety_Fire_or_police_station        41299  128.122115  \n",
       "27     Public_Safety_Fire_or_police_station        41290  118.971034  \n",
       "96                     Retail_Enclosed_mall         8588  123.175888  \n",
       "97                     Retail_Enclosed_mall         8787  158.335108  \n",
       "...                                     ...          ...         ...  \n",
       "8792                          Lodging_Other        57384         NaN  \n",
       "8796             Public_Safety_Penitentiary        57388         NaN  \n",
       "8797             Public_Safety_Penitentiary        57389         NaN  \n",
       "8798             Public_Safety_Penitentiary        57390         NaN  \n",
       "8799             Public_Safety_Penitentiary        57391         NaN  \n",
       "\n",
       "[3717 rows x 6 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['energy_star_rating'].isna()][['State_Factor','Year_Factor','floor_area','facility_type','building_id','site_eui']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68374b97-fa85-4fa1-af8f-4d6cb6d453e3",
   "metadata": {},
   "source": [
    "# 6 Create facility_group column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5cee83f5-763f-4a06-83ac-db21cb105f91",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create facility groups\n",
    "food = ['Grocery_store_or_food_market', 'Food_Service_Uncategorized', 'Food_Service_Other', 'Food_Service_Restaurant_or_cafeteria',\n",
    "        'Food_Sales']\n",
    "\n",
    "distribution_shipping = ['Warehouse_Distribution_or_Shipping_center', 'Warehouse_Nonrefrigerated',  'Warehouse_Selfstorage',\n",
    "                         'Warehouse_Refrigerated',  'Warehouse_Uncategorized']\n",
    "\n",
    "retail = ['Retail_Enclosed_mall', 'Retail_Vehicle_dealership_showroom' , 'Retail_Uncategorized',  'Retail_Strip_shopping_mall']\n",
    "\n",
    "eduation = ['Education_Other_classroom', 'Education_College_or_university', 'Education_Uncategorized', 'Education_Preschool_or_daycare']\n",
    "\n",
    "office = ['Office_Uncategorized', 'Office_Mixed_use']\n",
    "\n",
    "technology = ['Data_Center', 'Laboratory']\n",
    "\n",
    "other = ['Commercial_Other', 'Mixed_Use_Predominantly_Commercial', 'Mixed_Use_Commercial_and_Residential', 'Commercial_Unknown']\n",
    "\n",
    "other_res = ['Mixed_Use_Predominantly_Residential',  '2to4_Unit_Building', '5plus_Unit_Building']\n",
    "\n",
    "health_care = ['Office_Medical_non_diagnostic', 'Health_Care_Inpatient', 'Health_Care_Uncategorized', 'Health_Care_Outpatient_Clinic',\n",
    "               'Nursing_Home', 'Health_Care_Outpatient_Uncategorized']\n",
    "\n",
    "industrial = ['Industrial']\n",
    "\n",
    "public_assembly = ['Public_Assembly_Entertainment_culture', 'Public_Assembly_Drama_theater', 'Public_Assembly_Social_meeting',\n",
    "                   'Public_Assembly_Recreation', 'Public_Assembly_Movie_Theater', 'Public_Assembly_Uncategorized',\n",
    "                   'Public_Assembly_Other', 'Public_Assembly_Stadium']\n",
    "\n",
    "public_service = ['Public_Assembly_Library', 'Public_Safety_Uncategorized', 'Public_Safety_Fire_or_police_station',\n",
    "                  'Public_Safety_Penitentiary', 'Public_Safety_Courthouse']\n",
    "\n",
    "lodging_residential = ['Lodging_Hotel',  'Multifamily_Uncategorized', 'Lodging_Dormitory_or_fraternity_sorority', 'Lodging_Other', 'Lodging_Uncategorized']\n",
    "\n",
    "worship = ['Religious_worship']\n",
    "\n",
    "banking = ['Office_Bank_or_other_financial']\n",
    "\n",
    "parking = ['Parking_Garage']\n",
    "\n",
    "services = [ 'Service_Vehicle_service_repair_shop', 'Service_Drycleaning_or_Laundry', 'Service_Uncategorized']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "2920c067-bc94-424d-bc60-ba87c8c89fcf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>peak_NE</th>\n",
       "      <th>peak_SE</th>\n",
       "      <th>peak_SW</th>\n",
       "      <th>peak_NW</th>\n",
       "      <th>max_NE</th>\n",
       "      <th>max_SE</th>\n",
       "      <th>max_SW</th>\n",
       "      <th>max_NW</th>\n",
       "      <th>facility_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>56543.693554</td>\n",
       "      <td>42.700000</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>lodging_residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>102367.741694</td>\n",
       "      <td>34.755029</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>lodging_residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>159995.763193</td>\n",
       "      <td>12.556233</td>\n",
       "      <td>3</td>\n",
       "      <td>29.466343</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>lodging_residential</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>131544.271280</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>3</td>\n",
       "      <td>29.258065</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Residential</td>\n",
       "      <td>Multifamily_Uncategorized</td>\n",
       "      <td>407084.764995</td>\n",
       "      <td>31.235663</td>\n",
       "      <td>4</td>\n",
       "      <td>28.596774</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>lodging_residential</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "0         0.0            4      State_6    Residential   \n",
       "1         1.0            4      State_6    Residential   \n",
       "2         2.0            4      State_6    Residential   \n",
       "3         3.0            4      State_8     Commercial   \n",
       "4         4.0            4      State_6    Residential   \n",
       "\n",
       "                           facility_type     floor_area  ELEVATION  \\\n",
       "0              Multifamily_Uncategorized   56543.693554  42.700000   \n",
       "1              Multifamily_Uncategorized  102367.741694  34.755029   \n",
       "2              Multifamily_Uncategorized  159995.763193  12.556233   \n",
       "3  Public_Assembly_Entertainment_culture  131544.271280  61.744429   \n",
       "4              Multifamily_Uncategorized  407084.764995  31.235663   \n",
       "\n",
       "   january_min_temp  january_avg_temp  january_max_temp  ...  \\\n",
       "0                 4         28.596774                58  ...   \n",
       "1                 4         28.596774                58  ...   \n",
       "2                 3         29.466343                62  ...   \n",
       "3                 3         29.258065                62  ...   \n",
       "4                 4         28.596774                58  ...   \n",
       "\n",
       "   energy_star_rating  peak_NE  peak_SE  peak_SW  peak_NW  max_NE  max_SE  \\\n",
       "0                63.0        0        0        0        0       0       0   \n",
       "1                63.0        0        0        0        0       0       0   \n",
       "2                63.0        0        0        0        0       0       0   \n",
       "3                 NaN        0        0        0        0       0       0   \n",
       "4                63.0        0        0        0        0       0       0   \n",
       "\n",
       "   max_SW  max_NW       facility_group  \n",
       "0       0       0  lodging_residential  \n",
       "1       0       0  lodging_residential  \n",
       "2       0       0  lodging_residential  \n",
       "3       0       0      public_assembly  \n",
       "4       0       0  lodging_residential  \n",
       "\n",
       "[5 rows x 73 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#apply grouping\n",
    "df['facility_group'] = 1\n",
    "\n",
    "df.loc[df['facility_type'].isin(food), \"facility_group\"] = 'food_sales_service'  \n",
    "df.loc[df['facility_type'].isin(distribution_shipping), \"facility_group\"] = 'distribution_shipping'  \n",
    "df.loc[df['facility_type'].isin(retail), \"facility_group\"] = 'retail'  \n",
    "df.loc[df['facility_type'].isin(eduation), \"facility_group\"] = 'eduation'  \n",
    "df.loc[df['facility_type'].isin(office), \"facility_group\"] = 'office'  \n",
    "df.loc[df['facility_type'].isin(technology), \"facility_group\"] = 'technology'  \n",
    "df.loc[df['facility_type'].isin(other), \"facility_group\"] = 'other' \n",
    "df.loc[df['facility_type'].isin(health_care), \"facility_group\"] = 'health_care'\n",
    "df.loc[df['facility_type'].isin(industrial), \"facility_group\"] = 'industrial'    \n",
    "df.loc[df['facility_type'].isin(public_assembly ), \"facility_group\"] = 'public_assembly'  \n",
    "df.loc[df['facility_type'].isin(public_service ), \"facility_group\"] = 'public_service'  \n",
    "df.loc[df['facility_type'].isin(lodging_residential), \"facility_group\"] = 'lodging_residential'  \n",
    "df.loc[df['facility_type'].isin(worship), \"facility_group\"] = 'worship'  \n",
    "df.loc[df['facility_type'].isin(banking), \"facility_group\"] = 'banking'  \n",
    "df.loc[df['facility_type'].isin(parking), \"facility_group\"] = 'parking'  \n",
    "df.loc[df['facility_type'].isin(services), \"facility_group\"] = 'services'  \n",
    "df.loc[df['facility_type'].isin(other_res), \"facility_group\"] = 'other_res' \n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56ab59ce-c250-4b74-8e27-e353dd552c93",
   "metadata": {},
   "source": [
    "# 7 Impute last missing Energy star ratings based on facility group column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "8cee0970-1744-4191-98dd-39aecbff175b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def impute_missing_star_2(row):\n",
    "    if row.energy_star_rating == row.energy_star_rating:\n",
    "        return row.energy_star_rating\n",
    "    else:\n",
    "        return df[df['facility_group']== row.facility_group]['energy_star_rating'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "d74429b0-c727-4e84-9f1c-120ae927bdb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['energy_star_rating'] = df.apply(impute_missing_star_2, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "e15728fe-41f5-4112-bf5c-90dbbcf10bdb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1573"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['energy_star_rating'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b8d761fc-a449-4956-b944-9f974d005571",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>peak_NE</th>\n",
       "      <th>peak_SE</th>\n",
       "      <th>peak_SW</th>\n",
       "      <th>peak_NW</th>\n",
       "      <th>max_NE</th>\n",
       "      <th>max_SE</th>\n",
       "      <th>max_SW</th>\n",
       "      <th>max_NW</th>\n",
       "      <th>facility_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>1.315443e+05</td>\n",
       "      <td>61.744429</td>\n",
       "      <td>3</td>\n",
       "      <td>29.258065</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>228.0</td>\n",
       "      <td>1</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Other</td>\n",
       "      <td>6.414919e+04</td>\n",
       "      <td>20.115558</td>\n",
       "      <td>6</td>\n",
       "      <td>29.677419</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>229.0</td>\n",
       "      <td>1</td>\n",
       "      <td>State_6</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Other</td>\n",
       "      <td>6.483766e+04</td>\n",
       "      <td>17.803195</td>\n",
       "      <td>6</td>\n",
       "      <td>29.677419</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>361.0</td>\n",
       "      <td>5</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Recreation</td>\n",
       "      <td>6.800931e+04</td>\n",
       "      <td>214.264754</td>\n",
       "      <td>0</td>\n",
       "      <td>21.935484</td>\n",
       "      <td>46</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>408.0</td>\n",
       "      <td>4</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Entertainment_culture</td>\n",
       "      <td>1.213598e+05</td>\n",
       "      <td>9.100000</td>\n",
       "      <td>41</td>\n",
       "      <td>55.096774</td>\n",
       "      <td>73</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7520</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_4</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Recreation</td>\n",
       "      <td>5.127300e+04</td>\n",
       "      <td>214.000000</td>\n",
       "      <td>-1</td>\n",
       "      <td>27.548387</td>\n",
       "      <td>59</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7617</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_1</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Service_Drycleaning_or_Laundry</td>\n",
       "      <td>1.360800e+04</td>\n",
       "      <td>43.900000</td>\n",
       "      <td>38</td>\n",
       "      <td>50.596774</td>\n",
       "      <td>64</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8738</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_2</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Other</td>\n",
       "      <td>2.000000e+05</td>\n",
       "      <td>56.100000</td>\n",
       "      <td>15</td>\n",
       "      <td>41.177419</td>\n",
       "      <td>71</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8739</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_2</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Other</td>\n",
       "      <td>2.300000e+06</td>\n",
       "      <td>15.200000</td>\n",
       "      <td>15</td>\n",
       "      <td>41.177419</td>\n",
       "      <td>71</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8740</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>State_8</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Public_Assembly_Other</td>\n",
       "      <td>6.600000e+04</td>\n",
       "      <td>130.800000</td>\n",
       "      <td>2</td>\n",
       "      <td>33.435484</td>\n",
       "      <td>61</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>public_assembly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1573 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0  Year_Factor State_Factor building_class  \\\n",
       "3            3.0            4      State_8     Commercial   \n",
       "228        228.0            1      State_6     Commercial   \n",
       "229        229.0            1      State_6     Commercial   \n",
       "361        361.0            5      State_4     Commercial   \n",
       "408        408.0            4      State_1     Commercial   \n",
       "...          ...          ...          ...            ...   \n",
       "7520         NaN            7      State_4     Commercial   \n",
       "7617         NaN            7      State_1     Commercial   \n",
       "8738         NaN            7      State_2     Commercial   \n",
       "8739         NaN            7      State_2     Commercial   \n",
       "8740         NaN            7      State_8     Commercial   \n",
       "\n",
       "                              facility_type    floor_area   ELEVATION  \\\n",
       "3     Public_Assembly_Entertainment_culture  1.315443e+05   61.744429   \n",
       "228                   Public_Assembly_Other  6.414919e+04   20.115558   \n",
       "229                   Public_Assembly_Other  6.483766e+04   17.803195   \n",
       "361              Public_Assembly_Recreation  6.800931e+04  214.264754   \n",
       "408   Public_Assembly_Entertainment_culture  1.213598e+05    9.100000   \n",
       "...                                     ...           ...         ...   \n",
       "7520             Public_Assembly_Recreation  5.127300e+04  214.000000   \n",
       "7617         Service_Drycleaning_or_Laundry  1.360800e+04   43.900000   \n",
       "8738                  Public_Assembly_Other  2.000000e+05   56.100000   \n",
       "8739                  Public_Assembly_Other  2.300000e+06   15.200000   \n",
       "8740                  Public_Assembly_Other  6.600000e+04  130.800000   \n",
       "\n",
       "      january_min_temp  january_avg_temp  january_max_temp  ...  \\\n",
       "3                    3         29.258065                62  ...   \n",
       "228                  6         29.677419                53  ...   \n",
       "229                  6         29.677419                53  ...   \n",
       "361                  0         21.935484                46  ...   \n",
       "408                 41         55.096774                73  ...   \n",
       "...                ...               ...               ...  ...   \n",
       "7520                -1         27.548387                59  ...   \n",
       "7617                38         50.596774                64  ...   \n",
       "8738                15         41.177419                71  ...   \n",
       "8739                15         41.177419                71  ...   \n",
       "8740                 2         33.435484                61  ...   \n",
       "\n",
       "      energy_star_rating  peak_NE  peak_SE  peak_SW  peak_NW  max_NE  max_SE  \\\n",
       "3                    NaN        0        0        0        0       0       0   \n",
       "228                  NaN        0        0        0        0       0       0   \n",
       "229                  NaN        0        0        0        0       0       0   \n",
       "361                  NaN        0        0        0        0       0       0   \n",
       "408                  NaN        0        0        0        0       0       0   \n",
       "...                  ...      ...      ...      ...      ...     ...     ...   \n",
       "7520                 NaN        0        0        0        0       0       0   \n",
       "7617                 NaN        0        0        0        0       0       0   \n",
       "8738                 NaN        0        0        0        0       0       0   \n",
       "8739                 NaN        0        0        0        0       0       0   \n",
       "8740                 NaN        0        0        0        0       0       0   \n",
       "\n",
       "      max_SW  max_NW   facility_group  \n",
       "3          0       0  public_assembly  \n",
       "228        0       0  public_assembly  \n",
       "229        0       0  public_assembly  \n",
       "361        0       0  public_assembly  \n",
       "408        0       0  public_assembly  \n",
       "...      ...     ...              ...  \n",
       "7520       0       0  public_assembly  \n",
       "7617       0       0         services  \n",
       "8738       0       0  public_assembly  \n",
       "8739       0       0  public_assembly  \n",
       "8740       0       0  public_assembly  \n",
       "\n",
       "[1573 rows x 73 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['energy_star_rating'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "1dc1c800-c96b-4002-b20b-b59c632e7d42",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['energy_star_rating'] = df['energy_star_rating'].fillna(-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "868ea7f4-dbdd-48ab-aaec-745685d7e8e4",
   "metadata": {},
   "source": [
    "# 8 Impute Year Built"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ea85ede2-913a-448d-81a3-900fcbabea6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Year_Factor</th>\n",
       "      <th>State_Factor</th>\n",
       "      <th>building_class</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>floor_area</th>\n",
       "      <th>ELEVATION</th>\n",
       "      <th>january_min_temp</th>\n",
       "      <th>january_avg_temp</th>\n",
       "      <th>january_max_temp</th>\n",
       "      <th>...</th>\n",
       "      <th>energy_star_rating</th>\n",
       "      <th>peak_NE</th>\n",
       "      <th>peak_SE</th>\n",
       "      <th>peak_SW</th>\n",
       "      <th>peak_NW</th>\n",
       "      <th>max_NE</th>\n",
       "      <th>max_SE</th>\n",
       "      <th>max_SW</th>\n",
       "      <th>max_NW</th>\n",
       "      <th>facility_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, Year_Factor, State_Factor, building_class, facility_type, floor_area, ELEVATION, january_min_temp, january_avg_temp, january_max_temp, february_min_temp, february_avg_temp, february_max_temp, march_min_temp, march_avg_temp, march_max_temp, april_min_temp, april_avg_temp, april_max_temp, may_min_temp, may_avg_temp, may_max_temp, june_min_temp, june_avg_temp, june_max_temp, july_min_temp, july_avg_temp, july_max_temp, august_min_temp, august_avg_temp, august_max_temp, september_min_temp, september_avg_temp, september_max_temp, october_min_temp, october_avg_temp, october_max_temp, november_min_temp, november_avg_temp, november_max_temp, december_min_temp, december_avg_temp, december_max_temp, cooling_degree_days, heating_degree_days, precipitation_inches, snowfall_inches, snowdepth_inches, avg_temp, days_below_30F, days_below_20F, days_below_10F, days_below_0F, days_above_80F, days_above_90F, days_above_100F, days_above_110F, site_eui, id, year_built, max_wind_speed, days_with_fog, building_id, energy_star_rating, peak_NE, peak_SE, peak_SW, peak_NW, max_NE, max_SE, max_SW, max_NW, facility_group]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 73 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['year_built']==0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "30801e8a-f0bc-438b-a376-af467f8fa8bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set year 0 to np.nan\n",
    "df['year_built'] = np.where(df['year_built']==0, np.nan, df['year_built'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4b2e23ac-5e77-4601-b371-a9bc472f7f51",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create train and test\n",
    "df_train= df[df[\"id\"].isin(df_data[\"id\"])]\n",
    "df_test = df[df[\"id\"].isin(df_test[\"id\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "54c78175-c104-4559-930d-82fe26ecc072",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df_train['Unnamed: 0']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "2464b9bd-fffd-4541-85e5-4d59e0ff7753",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df_test['Unnamed: 0']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e49fb6ac-5f56-4f95-8a13-dca4166f4d8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0          9705\n",
       "site_eui            9705\n",
       "year_built            93\n",
       "snowdepth_inches       0\n",
       "days_below_0F          0\n",
       "                    ... \n",
       "june_max_temp          0\n",
       "june_avg_temp          0\n",
       "june_min_temp          0\n",
       "may_max_temp           0\n",
       "facility_group         0\n",
       "Length: 73, dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "b6bd0e89-f106-466a-bcfe-74047252cac9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop columns that we won't include in mice imputing\n",
    "mice_df_train = df_train .drop(['site_eui', 'id','peak_NE', 'peak_SE', 'peak_SW', 'peak_NW',\n",
    "       'max_NE', 'max_SE', 'max_SW', 'max_NW' ],axis=1) \n",
    "mice_df_test = df_test.drop(['site_eui' , 'id','peak_NE', 'peak_SE', 'peak_SW', 'peak_NW',\n",
    "       'max_NE', 'max_SE', 'max_SW', 'max_NW' ],axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "7b4c6fa4-1092-4402-ba7f-993f964bca56",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a list of all categorical features\n",
    "categorical_cols = [cname for cname in mice_df_train.columns if\n",
    "                    mice_df_train [cname].dtype == \"object\"]\n",
    "\n",
    "# Encode Categorical Columns \n",
    "for col in categorical_cols:\n",
    "    le = LabelEncoder()\n",
    "    mice_df_train [col] = le.fit_transform(mice_df_train[col])\n",
    "    mice_df_test[col] = le.transform(mice_df_test[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "2928ff27-55e4-4358-bc44-364da16a73b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a list of the maximum values of each column\n",
    "max_list = []\n",
    "for col in mice_df_train.columns:\n",
    "    max_value = mice_df_train[col].max()\n",
    "    max_list.append(max_value)\n",
    "\n",
    "#create a list of the minimum values of each column\n",
    "min_list = []\n",
    "for col in mice_df_train.columns:\n",
    "    min_value = mice_df_train[col].min()\n",
    "    min_list.append(min_value)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f41c1d9f-0b46-406a-a6b6-49e90db0ca08",
   "metadata": {},
   "source": [
    "#create mice imputer\n",
    "mice_imputer = IterativeImputer( max_value =  max_list,  min_value = min_list,  random_state = 11)\n",
    "\n",
    "#fit and transform on train and transform test\n",
    "mice_df_train.iloc[:, :] = (mice_imputer.fit_transform(mice_df_train))\n",
    "mice_df_test.iloc[:, :] = mice_imputer.transform(mice_df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a64fe3b-eacd-48ed-8486-3fa954356326",
   "metadata": {},
   "outputs": [],
   "source": [
    "mice_df_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a22d18e7-5591-4529-b5cd-5b3d5d25c981",
   "metadata": {},
   "outputs": [],
   "source": [
    "mice_df_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6f9d568-a9e4-497c-a59f-a180621f1c43",
   "metadata": {},
   "outputs": [],
   "source": [
    "mice = mice_df_train.append(mice_df_test)\n",
    "mice['id'] = df['id']\n",
    "mice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "673c49eb-dfb7-4d3e-98b9-738fc39c6e63",
   "metadata": {},
   "outputs": [],
   "source": [
    "mice= mice[['id', 'year_built']]\n",
    "mice = mice.sort_values('id')\n",
    "mice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75e52608-a102-43dd-a3cb-03794f2a26ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imputed = df.merge(mice[['id','year_built']], on = 'id', how = 'left' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8237813-858f-4af2-9193-fd27cda4d417",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imputed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "491e2573-7955-4ed0-a36f-e86549f3fe2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imputed.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "faa69397-cc00-455c-97ee-e01bd22e642f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imputed[df_imputed['year_built_x'] != df_imputed['year_built_y']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df50b526-a4ce-4fb0-b139-672580a9ba61",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df_imputed['year_built_x']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7186327b-5137-41b0-979c-84cb3f10eb36",
   "metadata": {},
   "outputs": [],
   "source": [
    "#del df_imputed['energy_star_rating_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e32fcc24-48fc-42f9-9c2a-c4788e8640f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_imputed.rename(columns={\"year_built_y\": \"year_built\", \"Year_Factor\": \"year_factor\", \"State_Factor\": \"state_factor\", \"ELEVATION\": \"elevation\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6874b27f-4859-49c0-a552-e9f3bdba2fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d917e0d-f616-4444-ad11-e3a0310982c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b25d35a-e1bd-4c8c-8bf7-83aa64a8d6c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"C:/Users/Meyke/WiDS/data/clean_data_3001_imputed_star_rating_and_generous_building_id.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ccd28ab-7cd1-4d17-a254-3fdc393edc2a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}