Data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Only select winter season data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total records: 41977\n",
      "total heating records: 15992\n"
     ]
    }
   ],
   "source": [
    "df_rawdata1 = pd.read_csv('Chinese Thermal Comfort Dataset Class I.csv', encoding='GBK')\n",
    "df_rawdata2 = pd.read_csv('Chinese Thermal Comfort Dataset Class II.csv', encoding='GBK')\n",
    "df_rawdata3 = pd.read_csv('Chinese Thermal Comfort Dataset Class III.csv', encoding='GBK')\n",
    "# concatenate the three datasets\n",
    "df_rawdata = pd.concat([df_rawdata1, df_rawdata2, df_rawdata3], ignore_index=True)\n",
    "print(\"total records:\", len(df_rawdata))\n",
    "# only select A4.Season = heating season\n",
    "df_heating = df_rawdata[df_rawdata['A4.Season'] == 'Winter Season']\n",
    "print(\"total heating records:\", len(df_heating))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean city names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Hebei Province,Handan City', 'Henan Province,Zhengzhou City',\n",
       "       'Henan Province,Luoyang City', 'Tianjin City',\n",
       "       'Liaoning Province,Dalian City',\n",
       "       'Heilongjiang Province, Harbin City', 'Beijing',\n",
       "       'Jiangsu Province,Changzhou City', 'Shanghai City',\n",
       "       \"Shaanxi Province,Xi'an City\", 'Guangdong Province,Guangzhou City',\n",
       "       'Beijing City', 'Baotou City', 'Turpan',\n",
       "       'Hubei Province, Wuhan City', \"Shaanxi Province, Xi'an City\",\n",
       "       'Hunan Province, Changsha City', 'Yunnan Province,Kunming City',\n",
       "       'Lhasa City', 'Henan Province,Jiaozuo City',\n",
       "       'Sichuan Province, Zigong City', 'Liaoning Province, Benxi City',\n",
       "       'Henan Province, Nanyang City', 'Liaoning Province, Dalian City',\n",
       "       'Hainan Province, Sanya City', 'Hunan Province, Hengyang City',\n",
       "       'Shanxi Province, Lvliangfenyang City',\n",
       "       'Henan Province, Pingdingshan City',\n",
       "       'Yunnan Province, Kunming City', 'Shandong Province, Qingdao City',\n",
       "       'Fujian Province, Zhenjiang City',\n",
       "       'Shandong Province, Yantai City', 'Hohhot City',\n",
       "       'Fujian Province, Fuzhou City', 'Shandong Province, Xintai City',\n",
       "       'Henan Province, Kaifeng City', 'Fujian Province, Nanping City',\n",
       "       'Henan Province, Luoyang City'], dtype=object)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_heating['A5.City'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Organizing city names in the same format (without comma and space)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_heating = df_heating.copy()\n",
    "# remove comma and replace space by _ in the A5.City strings\n",
    "df_heating['A5.City'] = df_heating['A5.City'].str.replace(' ', '')\n",
    "df_heating['A5.City'] = df_heating['A5.City'].str.replace(',', '')\n",
    "# replace BeijingCity by Beijing\n",
    "df_heating['A5.City'] = df_heating['A5.City'].str.replace('BeijingCity', 'Beijing')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['HebeiProvinceHandanCity', 'HenanProvinceZhengzhouCity',\n",
       "       'HenanProvinceLuoyangCity', 'TianjinCity',\n",
       "       'LiaoningProvinceDalianCity', 'HeilongjiangProvinceHarbinCity',\n",
       "       'Beijing', 'JiangsuProvinceChangzhouCity', 'ShanghaiCity',\n",
       "       \"ShaanxiProvinceXi'anCity\", 'GuangdongProvinceGuangzhouCity',\n",
       "       'BaotouCity', 'Turpan', 'HubeiProvinceWuhanCity',\n",
       "       'HunanProvinceChangshaCity', 'YunnanProvinceKunmingCity',\n",
       "       'LhasaCity', 'HenanProvinceJiaozuoCity',\n",
       "       'SichuanProvinceZigongCity', 'LiaoningProvinceBenxiCity',\n",
       "       'HenanProvinceNanyangCity', 'HainanProvinceSanyaCity',\n",
       "       'HunanProvinceHengyangCity', 'ShanxiProvinceLvliangfenyangCity',\n",
       "       'HenanProvincePingdingshanCity', 'ShandongProvinceQingdaoCity',\n",
       "       'FujianProvinceZhenjiangCity', 'ShandongProvinceYantaiCity',\n",
       "       'HohhotCity', 'FujianProvinceFuzhouCity',\n",
       "       'ShandongProvinceXintaiCity', 'HenanProvinceKaifengCity',\n",
       "       'FujianProvinceNanpingCity'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_heating['A5.City'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add latitude and outdoor temperature (January) data for each city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "city_dict_lat = {\n",
    "    \"Beijing\": 39.90,\n",
    "    \"TianjinCity\": 39.12, \n",
    "    \"BaotouCity\": 40.65,\n",
    "    \"HohhotCity\": 40.82,\n",
    "    \"LiaoningProvinceDalianCity\": 38.92,\n",
    "    \"ShandongProvinceYantaiCity\": 37.53,\n",
    "    \"ShanxiProvinceLvliangfenyangCity\": 37.51, \n",
    "    \"HebeiProvinceHandanCity\": 36.62,\n",
    "    \"HeilongjiangProvinceHarbinCity\": 45.75,\n",
    "    \"LiaoningProvinceBenxiCity\": 41.29,\n",
    "\n",
    "    \"ShandongProvinceQingdaoCity\": 36.07,\n",
    "    \"ShandongProvinceXintaiCity\": 35.90, \n",
    "\n",
    "    \"HenanProvinceJiaozuoCity\": 35.24,\n",
    "    \"HenanProvinceKaifengCity\": 34.79,\n",
    "    \"HenanProvinceZhengzhouCity\": 34.75,\n",
    "    \"HenanProvinceLuoyangCity\": 34.62,\n",
    "    \"HenanProvincePingdingshanCity\": 33.74,\n",
    "    \"HenanProvinceNanyangCity\": 32.99,\n",
    "\n",
    "    \"ShaanxiProvinceXi'anCity\": 34.26,\n",
    "\n",
    "    \"JiangsuProvinceChangzhouCity\": 31.78,  \n",
    "    \"ShanghaiCity\": 31.23,\n",
    "    \"Turpan\": 42.94,\n",
    "    \"LhasaCity\": 29.65, \n",
    "    \"HubeiProvinceWuhanCity\": 30.58,  \n",
    "    \"SichuanProvinceZigongCity\": 29.34,\n",
    "    \"HunanProvinceChangshaCity\": 28.23,\n",
    "    \"HunanProvinceHengyangCity\": 26.89,\n",
    "\n",
    "    \"FujianProvinceNanpingCity\": 26.64,  \n",
    "    \"FujianProvinceFuzhouCity\": 26.08,\n",
    "    \"FujianProvinceZhenjiangCity\": 26.10,\n",
    "\n",
    "    \"YunnanProvinceKunmingCity\": 24.87, \n",
    "    \"GuangdongProvinceGuangzhouCity\": 23.13,  \n",
    "    \"HainanProvinceSanyaCity\": 18.25,\n",
    "}\n",
    "# create a new column 'latitude' and in df_heating and assign the latitude value to corresponding city\n",
    "df_heating['latitude'] = df_heating['A5.City'].map(city_dict_lat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mannual find the minimum monthly outdoor temperature for each city\n",
    "city_t_out_min = {\n",
    "    \"HebeiProvinceHandanCity\": -0.8,  \n",
    "    \"HenanProvinceZhengzhouCity\": 0.8,\n",
    "    \"HenanProvinceKaifengCity\": 0.6,\n",
    "    \"HenanProvinceJiaozuoCity\": 1.5, \n",
    "    \"HenanProvinceNanyangCity\": 1.8, \n",
    "    \"HenanProvincePingdingshanCity\": 0.7,  # Assume same as Xuchang\n",
    "    \"HenanProvinceLuoyangCity\": 0.8,  # Assume same as Zhengzhou\n",
    "\n",
    "    \"ShaanxiProvinceXi'anCity\": 3.1,\n",
    "    \"ShanxiProvinceLvliangfenyangCity\": -4.8,  # Assume same as Taiyuan\n",
    "\n",
    "    \"Beijing\": -2.8,  \n",
    "    \"TianjinCity\": -3.3,  \n",
    "    \"LiaoningProvinceDalianCity\": -3.4, \n",
    "    \"LiaoningProvinceBenxiCity\": -10.9,\n",
    "    \"HeilongjiangProvinceHarbinCity\": -17.4,  \n",
    "\n",
    "    \"JiangsuProvinceChangzhouCity\": 3.9,  # Assume same as Wuxi\n",
    "    \"ShanghaiCity\": 5.2,  \n",
    "\n",
    "    \"BaotouCity\": -10.4,  # Estimated\n",
    "    \"HohhotCity\": -10.7, \n",
    "    \"LhasaCity\": -0.2,  \n",
    "    \"Turpan\": -6.5,  \n",
    "\n",
    "    \"HubeiProvinceWuhanCity\": 4.1,  \n",
    "    \"HunanProvinceChangshaCity\": 5.2,  \n",
    "    \"HunanProvinceHengyangCity\": 6.3,  \n",
    " \n",
    "    \"SichuanProvinceZigongCity\": 7.6,  \n",
    "    \"YunnanProvinceKunmingCity\": 9.2,\n",
    "    \"GuangdongProvinceGuangzhouCity\": 13.7,  \n",
    "    \"HainanProvinceSanyaCity\": 21,  \n",
    "\n",
    "    \"ShandongProvinceQingdaoCity\": 0.1,  \n",
    "    \"ShandongProvinceYantaiCity\": -1.3,  # Assume same as Longkou\n",
    "    \"ShandongProvinceXintaiCity\": -7.4,  # Assume same as Taishan\n",
    "\n",
    "    \"FujianProvinceZhenjiangCity\": 10,  # Typo, but should be a Fujian city, assum same as Sanming\n",
    "    \"FujianProvinceNanpingCity\": 10.1,  # Assume same as Sanming\n",
    "    \"FujianProvinceFuzhouCity\": 11.3\n",
    "\n",
    "}\n",
    "# create a new column 't_out_min' and in df_heating and assign the value to corresponding city\n",
    "df_heating['t_out_min'] = df_heating['A5.City'].map(city_t_out_min)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['HebeiHandan', 'HenanZhengzhou', 'HenanLuoyang', 'Tianjin',\n",
       "       'LiaoningDalian', 'HeilongjiangHarbin', 'Beijing',\n",
       "       'JiangsuChangzhou', 'Shanghai', \"ShaanxiXi'an\",\n",
       "       'GuangdongGuangzhou', 'Baotou', 'Turpan', 'HubeiWuhan',\n",
       "       'HunanChangsha', 'YunnanKunming', 'Lhasa', 'HenanJiaozuo',\n",
       "       'SichuanZigong', 'LiaoningBenxi', 'HenanNanyang', 'HainanSanya',\n",
       "       'HunanHengyang', 'ShanxiLvliangfenyang', 'HenanPingdingshan',\n",
       "       'ShandongQingdao', 'FujianZhenjiang', 'ShandongYantai', 'Hohhot',\n",
       "       'FujianFuzhou', 'ShandongXintai', 'HenanKaifeng', 'FujianNanping'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove \"City\" from the city names\n",
    "df_heating['A5.City'] = df_heating['A5.City'].str.replace('City', '')\n",
    "# remove \"Province\" from the city names\n",
    "df_heating['A5.City'] = df_heating['A5.City'].str.replace('Province', '')\n",
    "# check the unique city names\n",
    "df_heating['A5.City'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use ASHRAE Thermal Comfort Database2 naming conventions\n",
    "https://github.com/ruijis/ashrae-db-II\n",
    "\n",
    "cooling_type -> heating_type\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'A1.Code', 'A2.Date', 'A3.Data Contributor', 'A4.Season',\n",
       "       'A5.City', 'A6.Climate Zone', 'B1.Building Type',\n",
       "       'B2.Building Function', 'B3.Floors', 'B4.Building Operation Mode',\n",
       "       'B5.Room (Length×Width)', 'B5.Room Height (m)', 'C1.Sex', 'C2.Age',\n",
       "       'C3.Height（cm）', 'C4.Weight（kg）', 'C5.Living Years', 'D1.TSV', 'D2.TCV',\n",
       "       'D3.TAV', 'D5.Clothing Insulation (clo)', 'D6.Metabolic Rate (met)',\n",
       "       'Measured Height (m)', 'E1.Indoor Air Temperature (℃)',\n",
       "       'E2.Indoor Relative Humidity (%)', 'E3.Indoor Air Velocity (m/s)',\n",
       "       'E4.Globe Temperature (℃)', 'E5.Roof Temperature (℃)',\n",
       "       'E5.Wall Temperature (℃)', 'E5.Floor Temperature (℃)',\n",
       "       'F1.Operative Temperature (℃)', 'F2.Mean Radiant Temperature (℃)',\n",
       "       'F3.Radiant Temperature Asymmetry (℃)', 'F4.PMV', 'F5.PPD',\n",
       "       'G1.Real-Time Outdoor Temperature (℃)',\n",
       "       'G2.Mean Daily Outdoor Temperature (℃)',\n",
       "       'G3.Monthly Mean Outdoor Temperature (℃)',\n",
       "       'G4.7-Day Running Mean Outdoor Temperature (℃)',\n",
       "       'G4.15-Day Running Mean Outdoor Temperature (℃)',\n",
       "       'G5.Mean Daily Outdoor Relative Humidity (%)',\n",
       "       'G6.Mean Daily Outdoor Air Velocity (m/s)', 'latitude', 't_out_min'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_heating.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename general information\n",
    "df_heating = df_heating.rename(columns={'ID': 'suject_id'})\n",
    "df_heating = df_heating.rename(columns={'A2.Date': 'timestamp'})\n",
    "df_heating = df_heating.rename(columns={'A3.Data Contributor': 'contributor'})\n",
    "df_heating = df_heating.rename(columns={'A4.Season': 'season'})\n",
    "df_heating = df_heating.rename(columns={'A5.City': 'city'})\n",
    "df_heating = df_heating.rename(columns={'A6.Climate Zone': 'climate'})\n",
    "df_heating = df_heating.rename(columns={'B1.Building Type': 'building_type'})\n",
    "df_heating = df_heating.rename(columns={'B4.Building Operation Mode': 'heating_type'})\n",
    "# rename personal conditions\n",
    "df_heating = df_heating.rename(columns={'C1.Sex': 'gender'})\n",
    "df_heating = df_heating.rename(columns={'C2.Age': 'age'})\n",
    "df_heating = df_heating.rename(columns={'C3.Height（cm）': 'ht'})\n",
    "df_heating = df_heating.rename(columns={'C4.Weight（kg）': 'wt'})\n",
    "df_heating = df_heating.rename(columns={'D1.TSV': 'thermal_sensation'})\n",
    "df_heating = df_heating.rename(columns={'D2.TCV': 'thermal_comfort'})\n",
    "df_heating = df_heating.rename(columns={'D3.TAV': 'thermal_acceptability'})\n",
    "df_heating = df_heating.rename(columns={'D5.Clothing Insulation (clo)': 'clo'})\n",
    "df_heating = df_heating.rename(columns={'D6.Metabolic Rate (met)': 'met'})\n",
    "# rename  indoor environment parameters\n",
    "df_heating = df_heating.rename(columns={'E1.Indoor Air Temperature (℃)': 'ta'})\n",
    "df_heating = df_heating.rename(columns={'E2.Indoor Relative Humidity (%)': 'rh'})\n",
    "df_heating = df_heating.rename(columns={'E3.Indoor Air Velocity (m/s)': 'vel'})\n",
    "df_heating = df_heating.rename(columns={'E4.Globe Temperature (℃)': 'tg'})\n",
    "df_heating = df_heating.rename(columns={'F1.Operative Temperature (℃)': 'top'})\n",
    "df_heating = df_heating.rename(columns={'F2.Mean Radiant Temperature (℃)': 'tr'})\n",
    "# rename outdoor temperature parameters\n",
    "df_heating = df_heating.rename(columns={'G1.Real-Time Outdoor Temperature (℃)': 't_out_realtime'})\n",
    "df_heating = df_heating.rename(columns={'G2.Mean Daily Outdoor Temperature (℃)': 't_out_daily'})\n",
    "df_heating = df_heating.rename(columns={'G3.Monthly Mean Outdoor Temperature (℃)': 't_out_monthly'})\n",
    "df_heating = df_heating.rename(columns={'G4.7-Day Running Mean Outdoor Temperature (℃)': 't_out_7'})\n",
    "df_heating = df_heating.rename(columns={'G4.15-Day Running Mean Outdoor Temperature (℃)': 't_out_15'})\n",
    "\n",
    "# rename\n",
    "df_heating = df_heating.rename(columns={'G5.Mean Daily Outdoor Relative Humidity (%)': 'rh_out'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the datatypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(15992, 25)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "suject_id                  int64\n",
       "timestamp                 object\n",
       "season                    object\n",
       "city                      object\n",
       "climate                   object\n",
       "building_type             object\n",
       "heating_type              object\n",
       "gender                    object\n",
       "age                       object\n",
       "ht                       float64\n",
       "wt                       float64\n",
       "thermal_sensation        float64\n",
       "thermal_comfort          float64\n",
       "thermal_acceptability    float64\n",
       "clo                      float64\n",
       "met                      float64\n",
       "ta                       float64\n",
       "rh                         int64\n",
       "vel                      float64\n",
       "tg                       float64\n",
       "top                      float64\n",
       "tr                       float64\n",
       "t_out_min                float64\n",
       "t_out_monthly            float64\n",
       "latitude                 float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select a sbuset of columns\n",
    "df_heating_export = df_heating[['suject_id', \n",
    "                            'timestamp', \n",
    "                            'season', \n",
    "                            'city', \n",
    "                            'climate', \n",
    "                            'building_type', \n",
    "                            'heating_type', \n",
    "                            'gender', \n",
    "                            'age', \n",
    "                            'ht', \n",
    "                            'wt', \n",
    "                            'thermal_sensation', \n",
    "                            'thermal_comfort', \n",
    "                            'thermal_acceptability', \n",
    "                            'clo', \n",
    "                            'met', \n",
    "                            'ta', \n",
    "                            'rh', \n",
    "                            'vel', \n",
    "                            'tg', \n",
    "                            'top', \n",
    "                            'tr', \n",
    "                            't_out_min', \n",
    "                            't_out_monthly', \n",
    "                            'latitude']]\n",
    "print(df_heating_export.shape)\n",
    "df_heating_export.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_heating_export.to_csv('CN_DB.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}