data_processing_bca_batch2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Python libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "import sklearn.neighbors._base\n",
    "sys.modules['sklearn.neighbors.base'] = sklearn.neighbors._base\n",
    "from missingpy import MissForest\n",
    "from datetime import datetime, timedelta\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process survey data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "global_id_counter = 100  # starting with 100 to avoid overlapping with batch 1 users\n",
    "\n",
    "def process_survey_data(survey_data):\n",
    "    \"\"\"\n",
    "    Process the survey data by:\n",
    "    1) Formatting the timestamp information to merge with other datasets.\n",
    "    2) Renaming the column names align with other batches\n",
    "    3) Assigning user ID information to each survey entry\n",
    "    4) Retain relevant features\n",
    "    \n",
    "    Parameters:\n",
    "        survey_data: The unprocessed survey data\n",
    "        \n",
    "    Returns:\n",
    "        survey_data: The processed survey data.\n",
    "    \"\"\"\n",
    "    global global_id_counter\n",
    "    \n",
    "    experiment_date_mapping = {\n",
    "        datetime(2023, 9, 18).strftime(\"%Y-%m-%d\"):1,\n",
    "        datetime(2023, 9, 19).strftime(\"%Y-%m-%d\"):1,\n",
    "        datetime(2023, 9, 20).strftime(\"%Y-%m-%d\"):2,\n",
    "        datetime(2023, 9, 21).strftime(\"%Y-%m-%d\"):2,\n",
    "        datetime(2023, 9, 22).strftime(\"%Y-%m-%d\"):3,\n",
    "        datetime(2023, 9, 25).strftime(\"%Y-%m-%d\"):3,\n",
    "        datetime(2023, 9, 26).strftime(\"%Y-%m-%d\"):4,\n",
    "        datetime(2023, 9, 27).strftime(\"%Y-%m-%d\"):4,\n",
    "        datetime(2023, 9, 28).strftime(\"%Y-%m-%d\"):5,\n",
    "        datetime(2023, 9, 29).strftime(\"%Y-%m-%d\"):5,\n",
    "    }\n",
    "    \n",
    "    # format timestamp information to the nearest minute\n",
    "    survey_data['Timestamp'] = pd.to_datetime(\n",
    "        survey_data['Completion time'], \n",
    "        format=\"%Y-%m-%d %H:%M:%S\"\n",
    "    ).dt.round('T')\n",
    "\n",
    "    # rename column names\n",
    "    name_mapping = {\n",
    "        'Right now, would you prefer overall environment to be?':'Thermal Preference',\n",
    "        'Do you think current thermal environment (i.e., temperature, humidity, & airflow) acceptable?':'Thermal Acceptability',\n",
    "        'How would you prefer air movement now?':'Air Movement Preference'\n",
    "    }\n",
    "    survey_data.rename(columns=name_mapping, inplace=True)\n",
    "    \n",
    "    # assign user id based on 2-day experiments\n",
    "    survey_data['Date'] = survey_data['Timestamp'].apply(lambda x: x.date())\n",
    "    survey_data['Experiment Day'] = survey_data['Date'].apply(lambda x: experiment_date_mapping[x.strftime(\"%Y-%m-%d\")])\n",
    "#     print(survey_data['Date'].unique())\n",
    "#     print(survey_data['Experiment Day'].unique())\n",
    "    unique_days = list(survey_data['Experiment Day'].unique())\n",
    "    day_id_mapping = {}\n",
    "    for day in unique_days:\n",
    "        day_id_mapping[day] = global_id_counter\n",
    "        global_id_counter += 1\n",
    "    survey_data['User Id'] = survey_data['Experiment Day'].apply(lambda x: day_id_mapping[x])\n",
    "    survey_data.drop(columns=['Date', 'Experiment Day'], inplace=True)\n",
    "    \n",
    "    # standardised labels for thermal acceptability and air movement preferece\n",
    "    survey_data['Thermal Acceptability'] = survey_data[\"Thermal Acceptability\"].replace(\"Clearly Acceptable\", \"Acceptable\")\n",
    "    survey_data['Thermal Acceptability'] = survey_data[\"Thermal Acceptability\"].replace(\"Just Acceptable\", \"Acceptable\")\n",
    "    survey_data['Thermal Acceptability'] = survey_data[\"Thermal Acceptability\"].replace(\"Clearly Unacceptable\", \"Unacceptable\")\n",
    "    survey_data['Thermal Acceptability'] = survey_data[\"Thermal Acceptability\"].replace(\"Just Unacceptable\", \"Unacceptable\")\n",
    "    survey_data['Air Movement Preference'] = survey_data[\"Air Movement Preference\"].replace(\"More Air Movement\", \"More\")\n",
    "    survey_data['Air Movement Preference'] = survey_data[\"Air Movement Preference\"].replace(\"Less Air Movement\", \"Less\")\n",
    "\n",
    "    # retain relevant features\n",
    "    survey_data = survey_data[[\n",
    "        'Timestamp', \n",
    "        'Thermal Preference', \n",
    "        'Thermal Acceptability',\n",
    "        'Air Movement Preference',\n",
    "        'User Id'\n",
    "    ]]\n",
    "    \n",
    "    # Drop rows with missing labels\n",
    "    survey_data = survey_data.dropna().reset_index(drop=True)\n",
    "    \n",
    "    # sort survey data based on timestamp information in ascending order\n",
    "    survey_data = survey_data.sort_values(by='Timestamp').reset_index(drop=True)\n",
    "    \n",
    "    return survey_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Timestamp</th>\n",
       "      <th>Thermal Preference</th>\n",
       "      <th>Thermal Acceptability</th>\n",
       "      <th>Air Movement Preference</th>\n",
       "      <th>User Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 09:32:00</td>\n",
       "      <td>Cooler</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>More</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 09:33:00</td>\n",
       "      <td>Cooler</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>More</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 09:33:00</td>\n",
       "      <td>Cooler</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>More</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 09:34:00</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp Thermal Preference Thermal Acceptability  \\\n",
       "0 2023-09-18 09:30:00          No Change            Acceptable   \n",
       "1 2023-09-18 09:32:00             Cooler            Acceptable   \n",
       "2 2023-09-18 09:33:00             Cooler            Acceptable   \n",
       "3 2023-09-18 09:33:00             Cooler            Acceptable   \n",
       "4 2023-09-18 09:34:00          No Change            Acceptable   \n",
       "\n",
       "  Air Movement Preference  User Id  \n",
       "0               No Change      100  \n",
       "1                    More      100  \n",
       "2                    More      100  \n",
       "3                    More      100  \n",
       "4               No Change      100  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "survey_files = [\n",
    "    'Survey#1.xlsx',\n",
    "    'Survey#2.xlsx',\n",
    "    'Survey#3.xlsx',\n",
    "    'Survey#4.xlsx',\n",
    "    'Survey#5.xlsx',\n",
    "    'Survey#6.xlsx'\n",
    "]\n",
    "\n",
    "all_survey_data = pd.DataFrame()\n",
    "\n",
    "for file in survey_files:\n",
    "    survey_data = pd.read_excel(\n",
    "        f'data/yue_data/Survey/{file}', \n",
    "        sheet_name='Form1'\n",
    "    )\n",
    "    processed_survey_data = process_survey_data(survey_data)\n",
    "    all_survey_data = pd.concat(\n",
    "        [all_survey_data, processed_survey_data], \n",
    "        ignore_index=True\n",
    "    )\n",
    "\n",
    "all_survey_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112,\n",
       "       113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125,\n",
       "       126])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_survey_data['User Id'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process outdoor weather data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_weather_data(weather_data):\n",
    "    \"\"\"\n",
    "    Process the weather data by:\n",
    "    1) Formatting the timestamp information to merge with other datasets.\n",
    "    2) Renaming the column names align with other batches\n",
    "    \n",
    "    Parameters:\n",
    "        weather_data: The unprocessed weather data\n",
    "        \n",
    "    Returns:\n",
    "        weather_data: The processed weather data.\n",
    "    \"\"\"\n",
    "    # format timestamp information\n",
    "    weather_data['Timestamp'] = pd.to_datetime(\n",
    "        weather_data['Date'], \n",
    "        format=\"%m/%d/%y %H:%M:%S\"\n",
    "    ).dt.round('T')\n",
    "\n",
    "    # rename column names\n",
    "    name_mapping = {\n",
    "        'Temperature (S-THC 21535782:21555952-1), *C, ideaslab(Aug23)':'Outdoor Temp',\n",
    "        'RH (S-THC 21535782:21555952-2), %, ideaslab(Aug23)':'Outdoor Humidity',\n",
    "    }\n",
    "    weather_data.rename(\n",
    "        columns=name_mapping, \n",
    "        inplace=True\n",
    "    )\n",
    "    \n",
    "    # retain relevant features and rows based on study period\n",
    "    weather_data = weather_data[[\n",
    "        'Timestamp', \n",
    "        'Outdoor Temp', \n",
    "        'Outdoor Humidity',\n",
    "    ]]\n",
    "    \n",
    "    survey_start_date = datetime(2023, 9, 18)\n",
    "    survey_end_date = datetime(2023, 9, 29)\n",
    "    weather_data = weather_data[\n",
    "        (weather_data['Timestamp'] >= survey_start_date) & \n",
    "        (weather_data['Timestamp'] <= survey_end_date)\n",
    "    ].reset_index(drop=True)\n",
    "    \n",
    "    # sort weather data based on timestamp information in ascending order\n",
    "    weather_data = weather_data.sort_values(by='Timestamp').reset_index(drop=True)\n",
    "    \n",
    "    return weather_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Timestamp</th>\n",
       "      <th>Outdoor Temp</th>\n",
       "      <th>Outdoor Humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 00:00:00</td>\n",
       "      <td>28.647</td>\n",
       "      <td>79.135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 00:05:00</td>\n",
       "      <td>28.797</td>\n",
       "      <td>78.471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 00:10:00</td>\n",
       "      <td>28.990</td>\n",
       "      <td>77.998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 00:15:00</td>\n",
       "      <td>29.118</td>\n",
       "      <td>77.398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 00:20:00</td>\n",
       "      <td>28.454</td>\n",
       "      <td>79.117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp  Outdoor Temp  Outdoor Humidity\n",
       "0 2023-09-18 00:00:00        28.647            79.135\n",
       "1 2023-09-18 00:05:00        28.797            78.471\n",
       "2 2023-09-18 00:10:00        28.990            77.998\n",
       "3 2023-09-18 00:15:00        29.118            77.398\n",
       "4 2023-09-18 00:20:00        28.454            79.117"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_data = pd.read_csv(f'data/yue_data/Environment measurement/Weather data in UTC timezone.csv')\n",
    "weather_data = process_weather_data(weather_data)\n",
    "weather_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process globe temperature data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_globetemp_data(globetemp_door, globetemp_window):\n",
    "    \"\"\"\n",
    "    Process the globe temperature data by:\n",
    "    1) Formatting the timestamp information to merge with other datasets.\n",
    "    2) Renaming the column names\n",
    "    3) Merging the globe temperature data collected at the door and window based on \n",
    "    timestamp information rounded to the nearest minute\n",
    "    4) Find the average globe temperature reading between the door and window sensors.\n",
    "    \n",
    "    Parameters:\n",
    "        globetemp_door: The unprocessed globe temperature data collected at the door.\n",
    "        globetemp_window: The unprocessed globe temperature data collected at the window.\n",
    "        \n",
    "    Returns:\n",
    "        globetemp_data: The processed globe temperature data.\n",
    "    \"\"\"\n",
    "    # format timestamp information and round it to nearest minute\n",
    "    globetemp_door['Timestamp'] = pd.to_datetime(\n",
    "        globetemp_door['Time'], \n",
    "        format=\"%Y/%m/%d %H:%M:%S.%f\"\n",
    "    ).dt.round('T')\n",
    "    \n",
    "    globetemp_window['Timestamp'] = pd.to_datetime(\n",
    "        globetemp_door['Time'], \n",
    "        format=\"%Y/%m/%d %H:%M:%S.%f\"\n",
    "    ).dt.round('T')\n",
    "    \n",
    "    # rename column names\n",
    "    globetemp_door.rename(\n",
    "        columns={'CH1-1[C]':'Globe Temperature - Door'}, \n",
    "        inplace=True\n",
    "    )\n",
    "    \n",
    "    globetemp_window.rename(\n",
    "        columns={'CH1-1[C]':'Globe Temperature - Window'}, \n",
    "        inplace=True\n",
    "    )\n",
    "    \n",
    "    # merge globe temperature data at door and windows\n",
    "    globetemp_data = globetemp_door[['Timestamp', 'Globe Temperature - Door']].merge(\n",
    "        globetemp_window[['Timestamp', 'Globe Temperature - Window']], \n",
    "        how='outer'\n",
    "    )\n",
    "\n",
    "    # find average globe temperature\n",
    "    globetemp_data['Globe Temperature'] = globetemp_data['Globe Temperature - Door'].fillna(globetemp_data['Globe Temperature - Window']).mean()\n",
    "    \n",
    "    # retain relevant features and rows based on study period\n",
    "    globetemp_data = globetemp_data[[\n",
    "        'Timestamp', \n",
    "        'Globe Temperature', \n",
    "    ]]\n",
    "    \n",
    "    survey_start_date = datetime(2023, 9, 18)\n",
    "    survey_end_date = datetime(2023, 9, 29)\n",
    "    globetemp_data = globetemp_data[\n",
    "        (globetemp_data['Timestamp'] >= survey_start_date) & \n",
    "        (globetemp_data['Timestamp'] <= survey_end_date)\n",
    "    ].reset_index(drop=True)\n",
    "    \n",
    "    # sort weather data based on timestamp information in ascending order\n",
    "    globetemp_data = globetemp_data.sort_values(by='Timestamp').reset_index(drop=True)\n",
    "    \n",
    "    return globetemp_data\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Timestamp</th>\n",
       "      <th>Globe Temperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 00:00:00</td>\n",
       "      <td>28.119314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 00:01:00</td>\n",
       "      <td>28.119314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 00:02:00</td>\n",
       "      <td>28.119314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 00:03:00</td>\n",
       "      <td>28.119314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 00:04:00</td>\n",
       "      <td>28.119314</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp  Globe Temperature\n",
       "0 2023-09-18 00:00:00          28.119314\n",
       "1 2023-09-18 00:01:00          28.119314\n",
       "2 2023-09-18 00:02:00          28.119314\n",
       "3 2023-09-18 00:03:00          28.119314\n",
       "4 2023-09-18 00:04:00          28.119314"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "globetemp_door1 = pd.read_csv(\n",
    "    f'data/yue_data/Environment measurement/Globe temp/LR8515_180345475_180345475_2023-09-13_115253000_Door.csv', \n",
    "    skiprows=10\n",
    ")\n",
    "globetemp_door2 = pd.read_csv(\n",
    "    f'data/yue_data/Environment measurement/Globe temp/LR8515_180345475_180345475_2023-09-28_191022000_Door.csv', \n",
    "    skiprows=10\n",
    ")\n",
    "globetemp_door = pd.concat(\n",
    "    [globetemp_door1, globetemp_door2], \n",
    "    ignore_index=True\n",
    ")\n",
    "globetemp_window = pd.read_csv(\n",
    "    f'data/yue_data/Environment measurement/Globe temp/LR8515_180349862_180349862_2023-09-13_120259000_Window.csv', \n",
    "    skiprows=10\n",
    ")\n",
    "\n",
    "globetemp_data = process_globetemp_data(globetemp_door, globetemp_window)\n",
    "globetemp_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process air temperature, RH and air speed data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def correct_timestamp(time_interval, start_time, delay):\n",
    "    \"\"\"\n",
    "    Corrects the timestamp information by adding the time_interval to start_time and \n",
    "    accounting for delays.\n",
    "    \n",
    "    Parameters:\n",
    "        time_interval: Time that have passed since start_time\n",
    "        start_time: Time where data collection begins.\n",
    "        delay: The amount of time delay in seconds that needs to be accounted for in the timestamp.\n",
    "        \n",
    "    Returns:\n",
    "        final_timestamp\n",
    "    \"\"\"\n",
    "    hour, minutes, sec = time_interval.split('.')[1].split(':')\n",
    "    final_timestamp = start_time + timedelta(hours=int(hour), \n",
    "                                             minutes=int(minutes), \n",
    "                                             seconds=int(sec)) + timedelta(seconds=int(delay))\n",
    "    return final_timestamp\n",
    "    \n",
    "def process_indoor_env_data(filename, start_time, delay):\n",
    "    \"\"\"\n",
    "    Process the indoor environmental data by:\n",
    "    1) Formatting the timestamp information and accounting for time delays.\n",
    "    2) Find the average air temperature, humidity and air velocity data across 6 tables\n",
    "\n",
    "    Parameters:\n",
    "       filename: File name of the sensor data\n",
    "       start_time: A datetime object containing the start date and time\n",
    "       delay: The amount of time delay in seconds that needs to be accounted for in the timestamp.\n",
    "\n",
    "    Returns:\n",
    "        env_data: Dataframe containing the processed indoor environmental data with timestamp information.\n",
    "    \"\"\"\n",
    "    # Load the raw indoor environmental data file\n",
    "    env_data = pd.read_csv(\n",
    "        f'data/yue_data/Environment measurement/Air temp, RH, air speed/{filename}', \n",
    "        skiprows=9\n",
    "    )\n",
    "    \n",
    "    # format timestamp information and account for time delays. rounded to nearest minute\n",
    "    env_data['Timestamp'] = env_data['Data header Date&Time '].apply(lambda x: correct_timestamp(x, start_time, delay))\n",
    "    env_data['Timestamp'] = env_data['Timestamp'].dt.round('T')\n",
    "    \n",
    "    # find average air temp, humidity and air velocity across 6 tables\n",
    "    env_data['Indoor Temp'] = env_data[['HUB01-01CH-T',\n",
    "                                        'HUB01-02CH-T',\n",
    "                                        'HUB01-03CH-T',\n",
    "                                        'HUB01-04CH-T',\n",
    "                                        'HUB01-05CH-T',\n",
    "                                        'HUB01-06CH-T']].mean(skipna=True, axis=1)\n",
    "    env_data['Indoor Humidity'] = env_data[['HUB01-01CH-H',\n",
    "                                            'HUB01-02CH-H',\n",
    "                                            'HUB01-03CH-H',\n",
    "                                            'HUB01-04CH-H',\n",
    "                                            'HUB01-05CH-H',\n",
    "                                            'HUB01-06CH-H']].mean(skipna=True, axis=1)\n",
    "    env_data['Air Velocity'] = env_data[['HUB01-01CH-V',\n",
    "                                         'HUB01-02CH-V',\n",
    "                                         'HUB01-03CH-V',\n",
    "                                         'HUB01-04CH-V',\n",
    "                                         'HUB01-05CH-V',\n",
    "                                         'HUB01-06CH-V']].mean(skipna=True, axis=1)\n",
    "    \n",
    "    # retain relevant features and rows based on study period\n",
    "    env_data = env_data[[\n",
    "        'Timestamp', \n",
    "        'Indoor Temp',\n",
    "        'Indoor Humidity',\n",
    "        'Air Velocity',\n",
    "    ]]\n",
    "    \n",
    "    # sort environmental data based on timestamp information in ascending order\n",
    "    env_data = env_data.sort_values(by='Timestamp').reset_index(drop=True)\n",
    "    \n",
    "    return env_data\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Timestamp</th>\n",
       "      <th>Indoor Temp</th>\n",
       "      <th>Indoor Humidity</th>\n",
       "      <th>Air Velocity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 09:01:00</td>\n",
       "      <td>26.750000</td>\n",
       "      <td>69.266667</td>\n",
       "      <td>0.505000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 09:01:00</td>\n",
       "      <td>26.683333</td>\n",
       "      <td>69.183333</td>\n",
       "      <td>0.576667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 09:02:00</td>\n",
       "      <td>26.750000</td>\n",
       "      <td>69.100000</td>\n",
       "      <td>0.473333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 09:02:00</td>\n",
       "      <td>26.750000</td>\n",
       "      <td>68.983333</td>\n",
       "      <td>0.585000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 09:02:00</td>\n",
       "      <td>26.733333</td>\n",
       "      <td>68.916667</td>\n",
       "      <td>0.628333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp  Indoor Temp  Indoor Humidity  Air Velocity\n",
       "0 2023-09-18 09:01:00    26.750000        69.266667      0.505000\n",
       "1 2023-09-18 09:01:00    26.683333        69.183333      0.576667\n",
       "2 2023-09-18 09:02:00    26.750000        69.100000      0.473333\n",
       "3 2023-09-18 09:02:00    26.750000        68.983333      0.585000\n",
       "4 2023-09-18 09:02:00    26.733333        68.916667      0.628333"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indoor_env_files = {\n",
    "    '20230918_30 sec slower.CSV':{'Start Time': datetime(2023, 9, 18, 9, 0, 43, 0), 'Delay (s)': 30},\n",
    "    '20230919_2 sec slower.CSV':{'Start Time': datetime(2023, 9, 19, 8, 50, 48, 0), 'Delay (s)': 2},\n",
    "    '20230920_7 sec slower.CSV':{'Start Time': datetime(2023, 9, 20, 8, 42, 41, 0), 'Delay (s)': 7},\n",
    "    '20230921_5 sec faster.CSV':{'Start Time': datetime(2023, 9, 21, 8, 54, 11, 0), 'Delay (s)': -5},\n",
    "    '20230922.CSV':{'Start Time': datetime(2023, 9, 22, 8, 45, 47, 0), 'Delay (s)': 0},\n",
    "    '20230925.CSV':{'Start Time': datetime(2023, 9, 25, 8, 35, 4, 0), 'Delay (s)': 0},\n",
    "    '20230926_5 sec slower.CSV':{'Start Time': datetime(2023, 9, 26, 8, 50, 49, 0), 'Delay (s)': 5},\n",
    "    '20230927.CSV':{'Start Time': datetime(2023, 9, 27, 8, 46, 57, 0), 'Delay (s)': 0},\n",
    "    '20230928.CSV':{'Start Time': datetime(2023, 9, 28, 8, 43, 57, 0), 'Delay (s)': 0},\n",
    "    '20230929.CSV':{'Start Time': datetime(2023, 9, 29, 8, 46, 23, 0), 'Delay (s)': 0},\n",
    "}\n",
    "\n",
    "indoor_env_data = pd.DataFrame()\n",
    "\n",
    "for file in indoor_env_files.keys():\n",
    "    processed_env_data = process_indoor_env_data(\n",
    "        filename=file, \n",
    "        start_time=indoor_env_files[file]['Start Time'], \n",
    "        delay=indoor_env_files[file]['Delay (s)']\n",
    "    )\n",
    "    indoor_env_data = pd.concat([indoor_env_data, processed_env_data], ignore_index=True)\n",
    "    \n",
    "indoor_env_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and process mode data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_minute_interval(data):\n",
    "    \"\"\"\n",
    "    Generate a DataFrame with one-minute intervals with the corresponding \"Mode\" values based on\n",
    "    the start time and end time defined in \"data\".\n",
    "\n",
    "    Args:\n",
    "        data: The input DataFrame containing \"Start Time,\" \"End Time,\" and \"Mode\" columns.\n",
    "\n",
    "    Returns:\n",
    "        minute_df: A new DataFrame with one-minute intervals and corresponding \"Mode\" values.\n",
    "\n",
    "    \n",
    "    \"\"\"\n",
    "    # Ensure the \"Start Time\" and \"End Time\" columns are in datetime format\n",
    "    data['Start Time'] = pd.to_datetime(\n",
    "        data['Start Time'], \n",
    "        format='%d/%m/%y %H:%M'\n",
    "    )\n",
    "    data['End Time'] = pd.to_datetime(\n",
    "        data['End Time'],\n",
    "        format='%d/%m/%y %H:%M'\n",
    "    )\n",
    "\n",
    "    # Create a list of minute-level timestamps between \"Start Time\" and \"End Time\"\n",
    "    minute_intervals = []\n",
    "    for index, row in data.iterrows():\n",
    "        start_time = row['Start Time']\n",
    "        end_time = row['End Time']\n",
    "        mode = row['Mode']\n",
    "        \n",
    "        current_time = start_time\n",
    "        while current_time <= end_time:\n",
    "            minute_intervals.append((current_time, mode))\n",
    "            current_time += pd.Timedelta(minutes=1)\n",
    "\n",
    "    # Create a new DataFrame with minute intervals and corresponding \"Mode\" values\n",
    "    minute_df = pd.DataFrame(minute_intervals, columns=['Timestamp', 'Mode'])\n",
    "    \n",
    "    return minute_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Timestamp</th>\n",
       "      <th>Mode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 09:00:00</td>\n",
       "      <td>AC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 09:01:00</td>\n",
       "      <td>AC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 09:02:00</td>\n",
       "      <td>AC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 09:03:00</td>\n",
       "      <td>AC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 09:04:00</td>\n",
       "      <td>AC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp Mode\n",
       "0 2023-09-18 09:00:00   AC\n",
       "1 2023-09-18 09:01:00   AC\n",
       "2 2023-09-18 09:02:00   AC\n",
       "3 2023-09-18 09:03:00   AC\n",
       "4 2023-09-18 09:04:00   AC"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mode_data = pd.read_csv('data/yue_data/experimental_mode.csv')\n",
    "mode_data = generate_minute_interval(mode_data)\n",
    "mode_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge survey, environmental and outdoor weather data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Timestamp</th>\n",
       "      <th>Mode</th>\n",
       "      <th>Indoor Temp</th>\n",
       "      <th>Indoor Humidity</th>\n",
       "      <th>Air Velocity</th>\n",
       "      <th>Globe Temperature</th>\n",
       "      <th>Outdoor Temp</th>\n",
       "      <th>Outdoor Humidity</th>\n",
       "      <th>Thermal Preference</th>\n",
       "      <th>Thermal Acceptability</th>\n",
       "      <th>Air Movement Preference</th>\n",
       "      <th>User Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>NV</td>\n",
       "      <td>26.316667</td>\n",
       "      <td>68.683333</td>\n",
       "      <td>0.718333</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.019</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>NV</td>\n",
       "      <td>26.333333</td>\n",
       "      <td>68.716667</td>\n",
       "      <td>0.721667</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.019</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>NV</td>\n",
       "      <td>26.350000</td>\n",
       "      <td>68.733333</td>\n",
       "      <td>0.615000</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.019</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>NV</td>\n",
       "      <td>26.333333</td>\n",
       "      <td>68.766667</td>\n",
       "      <td>0.658333</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.019</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-09-18 09:30:00</td>\n",
       "      <td>NV</td>\n",
       "      <td>26.300000</td>\n",
       "      <td>68.800000</td>\n",
       "      <td>0.721667</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.019</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Timestamp Mode  Indoor Temp  Indoor Humidity  Air Velocity  \\\n",
       "0 2023-09-18 09:30:00   NV    26.316667        68.683333      0.718333   \n",
       "1 2023-09-18 09:30:00   NV    26.333333        68.716667      0.721667   \n",
       "2 2023-09-18 09:30:00   NV    26.350000        68.733333      0.615000   \n",
       "3 2023-09-18 09:30:00   NV    26.333333        68.766667      0.658333   \n",
       "4 2023-09-18 09:30:00   NV    26.300000        68.800000      0.721667   \n",
       "\n",
       "   Globe Temperature  Outdoor Temp  Outdoor Humidity Thermal Preference  \\\n",
       "0          28.119314        29.805            70.019          No Change   \n",
       "1          28.119314        29.805            70.019          No Change   \n",
       "2          28.119314        29.805            70.019          No Change   \n",
       "3          28.119314        29.805            70.019          No Change   \n",
       "4          28.119314        29.805            70.019          No Change   \n",
       "\n",
       "  Thermal Acceptability Air Movement Preference  User Id  \n",
       "0            Acceptable               No Change      100  \n",
       "1            Acceptable               No Change      100  \n",
       "2            Acceptable               No Change      100  \n",
       "3            Acceptable               No Change      100  \n",
       "4            Acceptable               No Change      100  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_survey_data['Timestamp_5min'] = all_survey_data['Timestamp'].dt.round('5min')\n",
    "merged_data = pd.merge(\n",
    "    all_survey_data,\n",
    "    weather_data, \n",
    "    left_on='Timestamp_5min', \n",
    "    right_on='Timestamp', \n",
    "    how='left', suffixes=('', '_delete')\n",
    ")\n",
    "merged_data.drop(columns=['Timestamp_5min', 'Timestamp_delete'], inplace=True)\n",
    "merged_data = pd.merge(\n",
    "    merged_data,\n",
    "    globetemp_data, \n",
    "    on='Timestamp', \n",
    "    how='left', \n",
    ")\n",
    "merged_data = pd.merge(\n",
    "    merged_data,\n",
    "    indoor_env_data, \n",
    "    on='Timestamp', \n",
    "    how='left', \n",
    ")\n",
    "merged_data = pd.merge(\n",
    "    merged_data,\n",
    "    mode_data, \n",
    "    on='Timestamp', \n",
    "    how='left', \n",
    ")\n",
    "\n",
    "# Reorder columns\n",
    "merged_data = merged_data[[\n",
    "    'Timestamp',\n",
    "    'Mode',\n",
    "    'Indoor Temp',\n",
    "    'Indoor Humidity',\n",
    "    'Air Velocity',\n",
    "    'Globe Temperature',\n",
    "    'Outdoor Temp',\n",
    "    'Outdoor Humidity',\n",
    "    'Thermal Preference',\n",
    "    'Thermal Acceptability',\n",
    "    'Air Movement Preference',\n",
    "    'User Id'\n",
    "]]\n",
    "\n",
    "merged_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 58839 entries, 0 to 58838\n",
      "Data columns (total 12 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   Timestamp                58839 non-null  datetime64[ns]\n",
      " 1   Mode                     58785 non-null  object        \n",
      " 2   Indoor Temp              58824 non-null  float64       \n",
      " 3   Indoor Humidity          58824 non-null  float64       \n",
      " 4   Air Velocity             58824 non-null  float64       \n",
      " 5   Globe Temperature        53349 non-null  float64       \n",
      " 6   Outdoor Temp             53349 non-null  float64       \n",
      " 7   Outdoor Humidity         53349 non-null  float64       \n",
      " 8   Thermal Preference       58839 non-null  object        \n",
      " 9   Thermal Acceptability    58839 non-null  object        \n",
      " 10  Air Movement Preference  58839 non-null  object        \n",
      " 11  User Id                  58839 non-null  int64         \n",
      "dtypes: datetime64[ns](1), float64(6), int64(1), object(4)\n",
      "memory usage: 5.8+ MB\n"
     ]
    }
   ],
   "source": [
    "merged_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Perform imputation for missing data using MissForest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration: 0\n",
      "Iteration: 1\n",
      "Iteration: 2\n",
      "Iteration: 3\n",
      "Iteration: 4\n"
     ]
    }
   ],
   "source": [
    "def impute_missing_data(data):    \n",
    "    original_col_sequence = list(data.columns)\n",
    "    imputer = MissForest(criterion='squared_error')\n",
    "    numerical_data = data.drop(columns=['Timestamp',\n",
    "                                        'Mode',\n",
    "                                        'Thermal Preference',\n",
    "                                        'Thermal Acceptability',\n",
    "                                        'Air Movement Preference', \n",
    "                                        'User Id'])\n",
    "    numerical_data_cols = list(numerical_data.columns)\n",
    "    imputed_numerical_data = imputer.fit_transform(numerical_data)\n",
    "    imputed_numerical_data = pd.DataFrame(imputed_numerical_data, \n",
    "                                          columns=numerical_data_cols)\n",
    "    imputed_data = pd.concat([data[['Timestamp',\n",
    "                                    'Mode',\n",
    "                                    'Thermal Preference',\n",
    "                                    'Thermal Acceptability',\n",
    "                                    'Air Movement Preference', \n",
    "                                    'User Id']], \n",
    "                              imputed_numerical_data], \n",
    "                             axis=1)\n",
    "    imputed_data = imputed_data[original_col_sequence]\n",
    "\n",
    "    assert imputed_data.shape == data.shape\n",
    "    assert imputed_data[\"Timestamp\"].tolist() == data[\"Timestamp\"].tolist()\n",
    "\n",
    "    return imputed_data\n",
    "\n",
    "merged_data = impute_missing_data(merged_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop rows with missing mode labels and duplicated rows, and sort entries based on timestamp\n",
    "merged_data = merged_data.dropna(subset=['Mode']).reset_index(drop=True)\n",
    "merged_data = merged_data.drop_duplicates().reset_index(drop=True)\n",
    "merged_data = merged_data.sort_values(by='Timestamp').reset_index(drop=True)\n",
    "merged_data.drop(columns=['Timestamp'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['No Change' 'Cooler' 'Warmer']\n",
      "['No Change' 'More' 'Less']\n",
      "['Acceptable' 'Unacceptable']\n",
      "['AC' 'NV']\n",
      "(58419, 11)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Mode</th>\n",
       "      <th>Indoor Temp</th>\n",
       "      <th>Indoor Humidity</th>\n",
       "      <th>Air Velocity</th>\n",
       "      <th>Globe Temperature</th>\n",
       "      <th>Outdoor Temp</th>\n",
       "      <th>Outdoor Humidity</th>\n",
       "      <th>Thermal Preference</th>\n",
       "      <th>Thermal Acceptability</th>\n",
       "      <th>Air Movement Preference</th>\n",
       "      <th>User Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AC</td>\n",
       "      <td>25.866667</td>\n",
       "      <td>64.083333</td>\n",
       "      <td>1.066667</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.051</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AC</td>\n",
       "      <td>25.850000</td>\n",
       "      <td>64.133333</td>\n",
       "      <td>0.856667</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.051</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AC</td>\n",
       "      <td>25.883333</td>\n",
       "      <td>64.283333</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.051</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AC</td>\n",
       "      <td>25.850000</td>\n",
       "      <td>64.250000</td>\n",
       "      <td>0.763333</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.051</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AC</td>\n",
       "      <td>25.933333</td>\n",
       "      <td>64.200000</td>\n",
       "      <td>1.106667</td>\n",
       "      <td>28.119314</td>\n",
       "      <td>29.805</td>\n",
       "      <td>70.051</td>\n",
       "      <td>No Change</td>\n",
       "      <td>Acceptable</td>\n",
       "      <td>No Change</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Mode  Indoor Temp  Indoor Humidity  Air Velocity  Globe Temperature  \\\n",
       "0   AC    25.866667        64.083333      1.066667          28.119314   \n",
       "1   AC    25.850000        64.133333      0.856667          28.119314   \n",
       "2   AC    25.883333        64.283333      0.833333          28.119314   \n",
       "3   AC    25.850000        64.250000      0.763333          28.119314   \n",
       "4   AC    25.933333        64.200000      1.106667          28.119314   \n",
       "\n",
       "   Outdoor Temp  Outdoor Humidity Thermal Preference Thermal Acceptability  \\\n",
       "0        29.805            70.051          No Change            Acceptable   \n",
       "1        29.805            70.051          No Change            Acceptable   \n",
       "2        29.805            70.051          No Change            Acceptable   \n",
       "3        29.805            70.051          No Change            Acceptable   \n",
       "4        29.805            70.051          No Change            Acceptable   \n",
       "\n",
       "  Air Movement Preference  User Id  \n",
       "0               No Change      110  \n",
       "1               No Change      110  \n",
       "2               No Change      110  \n",
       "3               No Change      110  \n",
       "4               No Change      110  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(merged_data['Thermal Preference'].unique())\n",
    "print(merged_data['Air Movement Preference'].unique())\n",
    "print(merged_data['Thermal Acceptability'].unique())\n",
    "print(merged_data['Mode'].unique())\n",
    "print(merged_data.shape)\n",
    "merged_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save processed data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# thermal preference data\n",
    "thermal_pref_data = merged_data.drop(columns=['Thermal Acceptability','Air Movement Preference'])\n",
    "thermal_pref_data = thermal_pref_data.dropna(subset=['Thermal Preference']).reset_index(drop=True)\n",
    "assert thermal_pref_data.isnull().values.any() == False\n",
    "thermal_pref_data.to_csv('data/bca_thermalpref_unsampled_batch2_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# thermal acceptability data\n",
    "thermal_acc_data = merged_data.drop(columns=['Thermal Preference','Air Movement Preference'])\n",
    "thermal_acc_data = thermal_acc_data.dropna(subset=['Thermal Acceptability']).reset_index(drop=True)\n",
    "assert thermal_acc_data.isnull().values.any() == False\n",
    "thermal_acc_data.to_csv('data/bca_thermalacc_unsampled_batch2_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# air movement preference data\n",
    "air_pref_data = merged_data.drop(columns=['Thermal Acceptability','Thermal Preference'])\n",
    "air_pref_data = air_pref_data.dropna(subset=['Air Movement Preference']).reset_index(drop=True)\n",
    "assert air_pref_data.isnull().values.any() == False\n",
    "air_pref_data.to_csv('data/bca_airpref_unsampled_batch2_data.csv', index=False)"
   ]
  }
 ],
 "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}