1_pre_process_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook Objectives\n",
    "- Load data\n",
    "- Re-order columns\n",
    "- Add time encoding\n",
    "- Separate weekday from weekend\n",
    "- Explore sensor correlations\n",
    "- Rescale/normalize\n",
    "- Build sliding window representation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pylab as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.preprocessing import MinMaxScaler, StandardScaler\n",
    "from pathlib import Path\n",
    "import time\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = pd.read_csv( index_col = 0,  parse_dates = True, infer_datetime_format = True, \n",
    "                         filepath_or_buffer = './data/2018-01-01__2019-01-01__NConservatory__allMerged.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.index = pd.to_datetime(dataset.index, utc=True).tz_convert('America/Los_Angeles')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Re-order columns [ facilitates cross-correlation analysis ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "orderedSensorList = ( 'co2_1','co2_2', 'co2_3', 'co2_4',                        \n",
    "                      'temp_1', 'temp_2', 'temp_3', 'temp_4',                     \n",
    "                      'dew_1','dew_2', 'dew_3', 'dew_4',\n",
    "                      'relH_1', 'relH_2', 'relH_3', 'relH_4',\n",
    "                      'externTemp_1', \n",
    "                      'externHumid_1', \n",
    "                      'externSunrise_1',                      \n",
    "                      'externCondition_1' )        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "orderedDataset = dataset.reindex( index = dataset.index, columns = orderedSensorList )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot 2018 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams['figure.figsize'] = [ 20, 15 ] \n",
    "plt.rcParams['figure.subplot.left'] = plt.rcParams['figure.subplot.bottom'] = .1\n",
    "plt.rcParams['figure.subplot.right'] = plt.rcParams['figure.subplot.top'] = .9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "orderedDataset.plot( subplots = True );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Separate Week from Weekend"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add time encoding/reference (day and hour)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dayIndexDF = pd.Series(index = orderedDataset.index, \n",
    "                       data = np.round(orderedDataset.index.dayofweek/6, decimals=2), \n",
    "                       name='dayIndex')\n",
    "hourIndexDF = pd.Series(index = orderedDataset.index, \n",
    "                       data = np.round(orderedDataset.index.hour/24, decimals=2), \n",
    "                       name='hourIndex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "orderedDatasetTimeReference = pd.concat([orderedDataset, hourIndexDF, dayIndexDF], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saturdayVal = np.round(5/6,decimals=2)\n",
    "sundayVal = np.round(6/6,decimals=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekdayData = orderedDatasetTimeReference[ ( dayIndexDF != saturdayVal) &( dayIndexDF != sundayVal) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekendData = orderedDatasetTimeReference[ ( dayIndexDF == saturdayVal) | (dayIndexDF == sundayVal) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekdayData.shape, weekendData.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore Weekday and Weekend Sensor Cross-Correlations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xCorrWeekday = weekdayData.corr()\n",
    "xCorrWeekend = weekendData.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(15,15))\n",
    "plt.subplots_adjust(left=0.15, right=0.85, top=0.95, bottom=0.05, wspace=.1)\n",
    "\n",
    "ax1 = plt.subplot(1,2,1)\n",
    "im1 = plt.imshow(xCorrWeekday, cmap = plt.get_cmap('seismic'))\n",
    "plt.xticks(range(0,len(xCorrWeekday)), labels=weekdayData.columns, rotation = 90)\n",
    "plt.yticks(range(0,len(xCorrWeekday)), labels=weekdayData.columns)\n",
    "plt.title('weekday')\n",
    "ax1.xaxis.tick_top()\n",
    "\n",
    "ax2 = plt.subplot(1,2,2)\n",
    "im2 = plt.imshow(xCorrWeekend, cmap = plt.get_cmap('seismic'))\n",
    "plt.xticks(range(0,len(xCorrWeekend)), labels=weekendData.columns, rotation = 90)\n",
    "plt.yticks(range(0,len(xCorrWeekend)), labels=weekendData.columns)\n",
    "ax2.yaxis.tick_right()\n",
    "ax2.xaxis.tick_top()\n",
    "plt.title('weekend')\n",
    "\n",
    "fig.colorbar(im1, ax=ax1, label='pairwise correlation', orientation='horizontal', pad=.025)\n",
    "fig.colorbar(im2, ax=ax2, label='pairwise correlation', orientation='horizontal', pad=.025)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rescale/Normalize [ focus on weekday data ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "continuousData = weekdayData.values[:, 0:17] # first 17 sensors are continuous valued\n",
    "categoricalData = weekdayData.values[:, 17:]\n",
    "\n",
    "standardScaler = StandardScaler()\n",
    "standardScaler.fit( continuousData )\n",
    "\n",
    "minMaxScaler = MinMaxScaler()\n",
    "minMaxScaler.fit( categoricalData )\n",
    "\n",
    "scaledContinuousData = standardScaler.transform(continuousData)\n",
    "scaledCategoricalData = minMaxScaler.transform(categoricalData)\n",
    "\n",
    "weekdayData_scaled =  pd.DataFrame( index = weekdayData.index,\n",
    "                                    data = np.hstack( (scaledContinuousData, scaledCategoricalData)),\n",
    "                                    columns = weekdayData.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekdayData_scaled[['co2_1', 'temp_1', 'dew_1', 'relH_1']].plot(subplots=True);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Convert to Sliding Window Representation\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_train_data ( inputDataframe, outputFilename, advanceTimedeltaStr = '15 min' ):\n",
    "\n",
    "    npFilename = outputFilename + '.npy'\n",
    "    npFilenameSamplebounds = outputFilename + '_sampleBounds' + '.npy'\n",
    "    rebuildFlag = True # flips if file exists in cache and user agrees to restore\n",
    "\n",
    "    windowCounter = correctLenWindows = fixedWindows = 1\n",
    "    trimNeededWindows = extensionNeededWindows = 1\n",
    "    sumExtensionAmount = sumTrimAmount = 0\n",
    "    \n",
    "    def extract_one_day( startTime, endTime, idealIndex, nExtensionAttempts=10 ):        \n",
    "        nonlocal correctLenWindows, trimNeededWindows, extensionNeededWindows, \\\n",
    "                sumExtensionAmount, sumTrimAmount, fixedWindows\n",
    "\n",
    "        oneDay = inputDataframe[ (inputDataframe.index >= startTime) \\\n",
    "                                         & (inputDataframe.index < endTime)]\n",
    "        \n",
    "        if len(oneDay) == len(idealIndex):\n",
    "            correctLenWindows += 1\n",
    "\n",
    "        # available data in day range is too long -- trimming required\n",
    "        if len(oneDay) > len(idealIndex):\n",
    "            trimNeededWindows += 1\n",
    "            sumTrimAmount += len(oneDay) - len(idealIndex)\n",
    "            oneDay = oneDay.iloc[0:len(idealIndex)]\n",
    "        \n",
    "        # available data in day range is of insufficient length -- extension required \n",
    "        if len(oneDay) < len(idealIndex):\n",
    "            extensionNeededWindows += 1\n",
    "            sumExtensionAmount += len(idealIndex) - len(oneDay)\n",
    "            \n",
    "            oneDay = None\n",
    "            # attempt to extend one sample at a time up to nExtensionAttempts\n",
    "            for iExtensionAttempt in range( nExtensionAttempts ):\n",
    "                endTime += pd.Timedelta( advanceTimedeltaStr)\n",
    "                extendedDay = inputDataframe[ (inputDataframe.index >= startTime) \\\n",
    "                                                 & (inputDataframe.index < endTime)]\n",
    "                # sucessfully extened data slice\n",
    "                if len( extendedDay) == len( idealIndex ):\n",
    "                    fixedWindows += 1\n",
    "                    oneDay = extendedDay\n",
    "                    break\n",
    "        \n",
    "        # only happens when available data requires extension beyond nExtensionAttempts\n",
    "        if oneDay is None:\n",
    "            return None, None, None\n",
    "        \n",
    "        assert ( len(oneDay) == len(idealIndex) )\n",
    "        return oneDay.values.reshape(1,-1, order = 'F'), oneDay.index[0], oneDay.index[-1]\n",
    "    \n",
    "    \n",
    "    # check cache\n",
    "    if Path(npFilename).is_file() and Path(npFilenameSamplebounds).is_file():\n",
    "        print('created on: {} \\n\\t size: {} MB'.format( time.ctime(os.path.getctime(npFilename)), \n",
    "                                                        Path(npFilename).stat().st_size / 1e6 ))\n",
    "        if 'y' == input('load from cache? (y/n): '):\n",
    "            npTrainMatrix = np.load(npFilename, allow_pickle=True) # load from cache\n",
    "            sampleIndexBounds = np.load(npFilenameSamplebounds, allow_pickle=True)\n",
    "            rebuildFlag = False\n",
    "    \n",
    "    if rebuildFlag:\n",
    "        npTrainMatrix = None\n",
    "        sampleIndexBounds = None\n",
    "        npTestMatrix = None\n",
    "\n",
    "        startTime = inputDataframe.index[0]\n",
    "        while(1):\n",
    "            endTime = startTime + pd.Timedelta('1 day');\n",
    "            \n",
    "            # terminate window extraction once end of dataset is reached\n",
    "            if startTime > inputDataframe.index[-1] or endTime > inputDataframe.index[-1]:\n",
    "                break\n",
    "            \n",
    "            # generate an ideal index [ 96 x 15 min samples from current startTime ]\n",
    "            idealIndex = pd.date_range(start=startTime, end=endTime, freq='15T', closed='left' );\n",
    "            \n",
    "            # ensure that current window is a weekday\n",
    "            if (idealIndex.dayofweek < 5).all():\n",
    "                \n",
    "                # gather data from sensor streams going one day forward\n",
    "                dataWindow, windowStartIndex, windowEndIndex = \\\n",
    "                    extract_one_day ( startTime, endTime, idealIndex)\n",
    "                \n",
    "                # dataWindow is none only when more than ~10 samples are missing                \n",
    "                if dataWindow is not None:                \n",
    "                    windowCounter += 1\n",
    "                    \n",
    "                    # first iteration is a direct assignment, all others require appending \n",
    "                    if npTrainMatrix is None:\n",
    "                        npTrainMatrix = dataWindow;\n",
    "                        sampleIndexBounds = np.array([windowStartIndex, windowEndIndex]);\n",
    "                    else:\n",
    "                        assert( dataWindow.shape[1] == npTrainMatrix.shape[1] ) # import ipdb; ipdb.set_trace()\n",
    "                        npTrainMatrix = np.append( npTrainMatrix, dataWindow, axis = 0 );\n",
    "                        sampleIndexBounds = np.append( sampleIndexBounds, \n",
    "                                                       np.array([windowStartIndex, windowEndIndex]) , axis = 0);\n",
    "                        \n",
    "            else:\n",
    "                pass # skipping -- day segment includes a piece of the weekend\n",
    "            \n",
    "            avgTrim = np.round(sumTrimAmount/trimNeededWindows, decimals=2);\n",
    "            avgExtend = np.round(sumExtensionAmount/extensionNeededWindows, decimals=2);\n",
    "            \n",
    "            # advance to next sample\n",
    "            startTime = startTime + pd.Timedelta(advanceTimedeltaStr);\n",
    "\n",
    "        print('nWindows {} - nCorrect {}; nTrimNeeded {} | avg.trim {}; nExtendNeeded {} | avg.ext {} | fixedViaExtension {}'.format(windowCounter, correctLenWindows, \n",
    "                       trimNeededWindows, avgTrim, extensionNeededWindows, avgExtend, fixedWindows), end='\\r')\n",
    "\n",
    "        print('\\nwriting to cache')\n",
    "        \n",
    "        # save to cache\n",
    "        sampleIndexBounds = sampleIndexBounds.reshape(npTrainMatrix.shape[0], -1)\n",
    "        np.save(npFilename, npTrainMatrix)\n",
    "        np.save(npFilenameSamplebounds, sampleIndexBounds)\n",
    "\n",
    "    if sampleIndexBounds.shape[0] != npTrainMatrix.shape[0]:\n",
    "        sampleIndexBounds = sampleIndexBounds.reshape(npTrainMatrix.shape[0], -1)\n",
    "        \n",
    "    return npTrainMatrix, sampleIndexBounds"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "expected output if rebuilding without cache:\n",
    "  > nWindows 20008 - nCorrect 17928; nTrimNeeded 439 | avg.trim 3.99; nExtendNeeded 1656 | avg.ext 1.56 | fixedViaExtension 1643        \n",
    "  > Wall time: 35min 53s "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "outputFilename = './data/2018-01-01__2019-01-01__NConservatory_npWeekdayAllOrderedSensorsTimeRef'\n",
    "npTrainMatrix, sampleIndexBounds = build_train_data ( weekdayData_scaled, outputFilename )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "npTrainMatrix.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot Sliding Windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "samplesInADay = 96 # 96 samples 15 minutes apart = 24 hours "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nSlidingWindowsToPlot = 3\n",
    "plt.figure()\n",
    "for iDay in range(nSlidingWindowsToPlot):\n",
    "    startIndex = (iDay) * samplesInADay\n",
    "    plt.plot(npTrainMatrix[startIndex,:])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_pytorch_latest_p36",
   "language": "python",
   "name": "conda_pytorch_latest_p36"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}