Gorilla_test_rough.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "g7Q2VlxlOhH5"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "kvsEnQJtOhH8"
   },
   "outputs": [],
   "source": [
    "filename = 'data.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "Vlsw_hEuOhH9"
   },
   "outputs": [],
   "source": [
    "#df1 = pd.read_excel(filename, sheet_name = 'Forecasted Consumption')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "5Y9BQsdPOhH-"
   },
   "outputs": [],
   "source": [
    "df_dummy = pd.ExcelFile(filename)\n",
    "sheet1 = df_dummy.parse(0)\n",
    "sheet2 = df_dummy.parse(1)\n",
    "sheet3 = df_dummy.parse(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "sHSBtqr9OhH-",
    "outputId": "479f8ceb-f557-4e77-ec6f-3aedc5da2bfc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date        datetime64[ns]\n",
      "Meter ID             int64\n",
      "kWh                float64\n",
      "dtype: object\n",
      "------\n",
      "Meter ID                  int64\n",
      "Exit Zone                object\n",
      "Annual Quantity (kWh)     int64\n",
      "dtype: object\n",
      "------\n",
      "Date                     datetime64[ns]\n",
      "Exit Zone                        object\n",
      "Annual Quantity (Min)             int64\n",
      "Annual Quantity (Max)           float64\n",
      "Rate (p/kWh)                    float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(sheet1.dtypes)\n",
    "print(\"------\")\n",
    "print(sheet2.dtypes)\n",
    "print(\"------\")\n",
    "print(sheet3.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "C7svxn_MOhIA",
    "outputId": "bc3da9b7-06b9-4f5c-914b-c4f7c3dc4097"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2190 entries, 0 to 2189\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Non-Null Count  Dtype         \n",
      "---  ------    --------------  -----         \n",
      " 0   Date      2190 non-null   datetime64[ns]\n",
      " 1   Meter ID  2190 non-null   int64         \n",
      " 2   kWh       2190 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(1), int64(1)\n",
      "memory usage: 51.5 KB\n",
      "\n",
      "---------------------\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 3 columns):\n",
      " #   Column                 Non-Null Count  Dtype \n",
      "---  ------                 --------------  ----- \n",
      " 0   Meter ID               3 non-null      int64 \n",
      " 1   Exit Zone              3 non-null      object\n",
      " 2   Annual Quantity (kWh)  3 non-null      int64 \n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 200.0+ bytes\n",
      "\n",
      "---------------------\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1026 entries, 0 to 1025\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   1026 non-null   datetime64[ns]\n",
      " 1   Exit Zone              1026 non-null   object        \n",
      " 2   Annual Quantity (Min)  1026 non-null   int64         \n",
      " 3   Annual Quantity (Max)  684 non-null    float64       \n",
      " 4   Rate (p/kWh)           1026 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 40.2+ KB\n"
     ]
    }
   ],
   "source": [
    "sheet1.info()\n",
    "print()\n",
    "print(\"---------------------\")\n",
    "print()\n",
    "sheet2.info()\n",
    "print()\n",
    "print(\"---------------------\")\n",
    "print()\n",
    "sheet3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "NWLQ6tl8OhIA",
    "outputId": "c365cee5-241e-479d-8370-996953a6e4d2"
   },
   "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>Date</th>\n",
       "      <th>Exit Zone</th>\n",
       "      <th>Annual Quantity (Min)</th>\n",
       "      <th>Annual Quantity (Max)</th>\n",
       "      <th>Rate (p/kWh)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>EA1</td>\n",
       "      <td>0</td>\n",
       "      <td>73200.0</td>\n",
       "      <td>0.028700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>EA2</td>\n",
       "      <td>0</td>\n",
       "      <td>73200.0</td>\n",
       "      <td>0.028700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>EA3</td>\n",
       "      <td>0</td>\n",
       "      <td>73200.0</td>\n",
       "      <td>0.028700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>EA4</td>\n",
       "      <td>0</td>\n",
       "      <td>73200.0</td>\n",
       "      <td>0.028700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>EM1</td>\n",
       "      <td>0</td>\n",
       "      <td>73200.0</td>\n",
       "      <td>0.028700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1021</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>WA1</td>\n",
       "      <td>732000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.384755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1022</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>WA2</td>\n",
       "      <td>732000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.384755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1023</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>WM1</td>\n",
       "      <td>732000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.445482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1024</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>WM2</td>\n",
       "      <td>732000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.445482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1025</th>\n",
       "      <td>2024-04-01</td>\n",
       "      <td>WM3</td>\n",
       "      <td>732000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.445482</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1026 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date Exit Zone  ...  Annual Quantity (Max)  Rate (p/kWh)\n",
       "0    2020-04-01       EA1  ...                73200.0      0.028700\n",
       "1    2020-04-01       EA2  ...                73200.0      0.028700\n",
       "2    2020-04-01       EA3  ...                73200.0      0.028700\n",
       "3    2020-04-01       EA4  ...                73200.0      0.028700\n",
       "4    2020-04-01       EM1  ...                73200.0      0.028700\n",
       "...         ...       ...  ...                    ...           ...\n",
       "1021 2024-04-01       WA1  ...                    NaN      0.384755\n",
       "1022 2024-04-01       WA2  ...                    NaN      0.384755\n",
       "1023 2024-04-01       WM1  ...                    NaN      0.445482\n",
       "1024 2024-04-01       WM2  ...                    NaN      0.445482\n",
       "1025 2024-04-01       WM3  ...                    NaN      0.445482\n",
       "\n",
       "[1026 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NJ2D1iv3OhIC",
    "outputId": "f22bb76d-d6a9-485c-bcfa-170dc07b9e89"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Date    Meter ID        kWh\n",
      "0    2020-10-01  1000000603  28.782474\n",
      "3    2020-10-02  1000000603  26.690797\n",
      "6    2020-10-03  1000000603  21.511312\n",
      "9    2020-10-04  1000000603  22.801972\n",
      "12   2020-10-05  1000000603  30.805517\n",
      "...         ...         ...        ...\n",
      "2175 2022-09-26  1000000603  18.553369\n",
      "2178 2022-09-27  1000000603  18.801933\n",
      "2181 2022-09-28  1000000603  19.015036\n",
      "2184 2022-09-29  1000000603  19.263599\n",
      "2187 2022-09-30  1000000603  17.927143\n",
      "\n",
      "[730 rows x 3 columns]\n",
      "           Date  Meter ID         kWh\n",
      "1    2020-10-01  10588707  126.367711\n",
      "4    2020-10-02  10588707  118.322449\n",
      "7    2020-10-03  10588707   94.272125\n",
      "10   2020-10-04  10588707   97.641038\n",
      "13   2020-10-05  10588707  138.761245\n",
      "...         ...       ...         ...\n",
      "2176 2022-09-26  10588707   73.614794\n",
      "2179 2022-09-27  10588707   75.116336\n",
      "2182 2022-09-28  10588707   76.430689\n",
      "2185 2022-09-29  10588707   77.744203\n",
      "2188 2022-09-30  10588707   73.335098\n",
      "\n",
      "[730 rows x 3 columns]\n",
      "           Date  Meter ID         kWh\n",
      "2    2020-10-01  10626610  326.240595\n",
      "5    2020-10-02  10626610  316.860290\n",
      "8    2020-10-03  10626610  278.227839\n",
      "11   2020-10-04  10626610  277.159075\n",
      "14   2020-10-05  10626610  349.200484\n",
      "...         ...       ...         ...\n",
      "2177 2022-09-26  10626610  259.770107\n",
      "2180 2022-09-27  10626610  263.740473\n",
      "2183 2022-09-28  10626610  267.269087\n",
      "2186 2022-09-29  10626610  270.357196\n",
      "2189 2022-09-30  10626610  263.375674\n",
      "\n",
      "[730 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "for i in range (len(sheet2['Meter ID'])):\n",
    "    print (sheet1[sheet1['Meter ID'] == sheet2['Meter ID'][i]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "-uBh-GBDOhID",
    "outputId": "ec2304af-889c-412f-d992-efe41f35aee0"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1026 entries, 0 to 1025\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   1026 non-null   datetime64[ns]\n",
      " 1   Exit Zone              1026 non-null   object        \n",
      " 2   Annual Quantity (Min)  1026 non-null   int64         \n",
      " 3   Annual Quantity (Max)  684 non-null    float64       \n",
      " 4   Rate (p/kWh)           1026 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 40.2+ KB\n",
      "None\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 570 entries, 0 to 569\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   570 non-null    datetime64[ns]\n",
      " 1   Exit Zone              570 non-null    object        \n",
      " 2   Annual Quantity (Min)  570 non-null    int64         \n",
      " 3   Annual Quantity (Max)  380 non-null    float64       \n",
      " 4   Rate (p/kWh)           570 non-null    float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 26.7+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "newSheet3 = sheet3[(sheet3['Date'] >= '1-10-2020') & (sheet3['Date'] <= '30-09-2022')]\n",
    "\n",
    "print(sheet3.info())\n",
    "print()\n",
    "print(newSheet3.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "LsLNwTKLOhID"
   },
   "outputs": [],
   "source": [
    "## find the metreID and exitZone mapping from sheet2\n",
    " \n",
    "sheet2Reduced = sheet2.drop('Annual Quantity (kWh)', 1)\n",
    "exitZones = sheet2Reduced['Exit Zone'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "gkgVY_hGOhIE"
   },
   "outputs": [],
   "source": [
    "ezone = sheet3['Exit Zone']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5pttYR4jOhIE",
    "outputId": "fc052a88-809c-4581-a630-bb59de792194"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SC1 1000000603\n",
      "EM2 10588707\n",
      "NW1 10626610\n"
     ]
    }
   ],
   "source": [
    "zoneToMeter = {}\n",
    "\n",
    "for zone in exitZones:\n",
    "    tempDf = sheet2Reduced[sheet2Reduced['Exit Zone'] == zone]\n",
    "    mID = tempDf.iloc[0]['Meter ID']\n",
    "    zoneToMeter[zone] = mID\n",
    "\n",
    "for key in zoneToMeter.keys():\n",
    "    print(key, zoneToMeter[key])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "uEzK5oBqOhIE"
   },
   "outputs": [],
   "source": [
    "sheet3 = newSheet3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "8CcurHUZOhIF",
    "outputId": "bc1be642-0542-47e4-cb0b-f35f99f17b2b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 45 entries, 5 to 555\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   45 non-null     datetime64[ns]\n",
      " 1   Exit Zone              45 non-null     object        \n",
      " 2   Annual Quantity (Min)  45 non-null     int64         \n",
      " 3   Annual Quantity (Max)  30 non-null     float64       \n",
      " 4   Rate (p/kWh)           45 non-null     float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 2.1+ KB\n"
     ]
    }
   ],
   "source": [
    "newSheet3 = sheet3.loc[sheet3['Exit Zone'].isin(exitZones)]\n",
    "newSheet3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0473Cj8_OhIF",
    "outputId": "2c28fa89-5aeb-41e1-df81-7f23723b96c6"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LS\n"
     ]
    }
   ],
   "source": [
    "print(random.choice(ezone))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UpZOJxkHOhIG",
    "outputId": "24a9ca72-6310-4adc-db72-711fd53dda32"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "91326.36065278183\n",
      "0.2770949504051441\n"
     ]
    }
   ],
   "source": [
    "#print(random.randint(0,1000))\n",
    "print(random.uniform(0,100000))\n",
    "print(random.random())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "HCYXvwFtOhIG"
   },
   "outputs": [],
   "source": [
    "newSheet3 = newSheet3[newSheet3['Date'] != '2020-04-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "AFtsJojbOhIG",
    "outputId": "b8ad263b-50c6-4acd-ae38-547ed655826e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 36 entries, 119 to 555\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   36 non-null     datetime64[ns]\n",
      " 1   Exit Zone              36 non-null     object        \n",
      " 2   Annual Quantity (Min)  36 non-null     int64         \n",
      " 3   Annual Quantity (Max)  24 non-null     float64       \n",
      " 4   Rate (p/kWh)           36 non-null     float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 1.7+ KB\n"
     ]
    }
   ],
   "source": [
    "newSheet3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "id": "pg0ZKXoNOhIH"
   },
   "outputs": [],
   "source": [
    "sheet3 = newSheet3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "id": "TDMPBhasXuEX"
   },
   "outputs": [],
   "source": [
    "def relevant_dates(sheet3):\n",
    "    rateDates = sheet3['Date'].unique()\n",
    "    sortedRateDates = sorted(rateDates)\n",
    "\n",
    "    return sortedRateDates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "pxZchAUNOhIH",
    "outputId": "fdf68d6f-a9d6-402a-dfd9-347854168075"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[numpy.datetime64('2020-10-01T00:00:00.000000000'),\n",
       " numpy.datetime64('2021-04-01T00:00:00.000000000'),\n",
       " numpy.datetime64('2021-10-01T00:00:00.000000000'),\n",
       " numpy.datetime64('2022-04-01T00:00:00.000000000')]"
      ]
     },
     "execution_count": 21,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sortedRateDates = relevant_dates(sheet3)\n",
    "sortedRateDates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VCmgNkJJOhII",
    "outputId": "1c677c54-09dc-41db-9d0a-a3444cb02436"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1000000603   10588707   10626610] <class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "metreIDs = sheet2['Meter ID'].unique()\n",
    "print(metreIDs, type(metreIDs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "id": "Cm7M2i_BOhII"
   },
   "outputs": [],
   "source": [
    "def Consumption (metreIDs, sheet1, sortedRateDates):\n",
    "    consumptionData = []\n",
    "\n",
    "    for metreID in metreIDs:\n",
    "        sheet1Reduced = sheet1[sheet1['Meter ID'] == metreID]\n",
    "        d = {}\n",
    "        for idx, date in enumerate(sortedRateDates):\n",
    "            if(idx != len(sortedRateDates)-1):\n",
    "                nxtDate = sortedRateDates[idx+1]\n",
    "                tempDf = sheet1Reduced[sheet1Reduced['Date'] >= date]\n",
    "                tempDf = tempDf[tempDf['Date'] < nxtDate]\n",
    "                val = tempDf['kWh'].sum()\n",
    "                d[date] = [metreID , val]\n",
    "            else:\n",
    "                tempDf = sheet1Reduced[sheet1Reduced['Date'] >= date]\n",
    "                val = tempDf['kWh'].sum()\n",
    "                d[date] = [metreID , val]\n",
    "        consumptionData.append(d)\n",
    "        \n",
    "    return (consumptionData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "id": "eDW7JKkmOhII"
   },
   "outputs": [],
   "source": [
    "CD = Consumption(metreIDs, sheet1, sortedRateDates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "yragGMVZOhII",
    "outputId": "91d53e95-5979-447a-85be-bfb213f347e7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-10-01T00:00:00.000000000 [1000000603, 8908.61620600899]\n",
      "2021-04-01T00:00:00.000000000 [1000000603, 3428.383793990991]\n",
      "2021-10-01T00:00:00.000000000 [1000000603, 8790.85792188759]\n",
      "2022-04-01T00:00:00.000000000 [1000000603, 3546.1420782475916]\n",
      "------------------------------\n",
      "2020-10-01T00:00:00.000000000 [10588707, 46488.11297780813]\n",
      "2021-04-01T00:00:00.000000000 [10588707, 14736.887021688537]\n",
      "2021-10-01T00:00:00.000000000 [10588707, 45783.81639460087]\n",
      "2022-04-01T00:00:00.000000000 [10588707, 15441.183605399012]\n",
      "------------------------------\n",
      "2020-10-01T00:00:00.000000000 [10626610, 105024.35742833954]\n",
      "2021-04-01T00:00:00.000000000 [10626610, 46754.64257166028]\n",
      "2021-10-01T00:00:00.000000000 [10626610, 103681.9437768501]\n",
      "2022-04-01T00:00:00.000000000 [10626610, 48097.056224813074]\n",
      "------------------------------\n"
     ]
    }
   ],
   "source": [
    "for data in CD:\n",
    "    for key in data.keys():\n",
    "        print(key , data[key])\n",
    "    print('------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "id": "0YcMQWIbOhIJ"
   },
   "outputs": [],
   "source": [
    "def Rate(sheet3, exitZones, sortedRateDates):\n",
    "    rateData = []\n",
    "\n",
    "    for zone in exitZones:\n",
    "        rateDataDict = {}\n",
    "        for date in sortedRateDates:\n",
    "            tempDf = sheet3[sheet3['Date'] == date]\n",
    "            tempDf = tempDf[tempDf['Exit Zone'] == zone]\n",
    "            rates = tempDf['Rate (p/kWh)'].tolist()\n",
    "            rateDataDict[date] = [zone , rates]\n",
    "        rateData.append(rateDataDict)\n",
    "    return (rateData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NEFLfAOVOhIJ",
    "outputId": "98c8031f-56fa-459a-9094-fb17b643f2ca"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-10-01T00:00:00.000000000 [1000000603, [0.0333, 0.0297, 0.2431]]\n",
      "2021-04-01T00:00:00.000000000 [1000000603, [0.033312536189378, 0.0297111809256614, 0.243191517947081]]\n",
      "2021-10-01T00:00:00.000000000 [1000000603, [0.033312536189378, 0.0297111809256614, 0.243191517947081]]\n",
      "2022-04-01T00:00:00.000000000 [1000000603, [0.03470596778416, 0.0309539712669535, 0.253363987036916]]\n",
      "--------------------------\n",
      "2020-10-01T00:00:00.000000000 [10588707, [0.0287, 0.0228, 0.182]]\n",
      "2021-04-01T00:00:00.000000000 [10588707, [0.0294050827624451, 0.0233601354349738, 0.186471256542335]]\n",
      "2021-10-01T00:00:00.000000000 [10588707, [0.0294050827624451, 0.0233601354349738, 0.186471256542335]]\n",
      "2022-04-01T00:00:00.000000000 [10588707, [0.0306609794113917, 0.0243578512397119, 0.194435479194191]]\n",
      "--------------------------\n",
      "2020-10-01T00:00:00.000000000 [10626610, [0.0339, 0.0285, 0.2474]]\n",
      "2021-04-01T00:00:00.000000000 [10626610, [0.0345836043372242, 0.0290747116109407, 0.252388900089359]]\n",
      "2021-10-01T00:00:00.000000000 [10626610, [0.0345836043372242, 0.0290747116109407, 0.252388900089359]]\n",
      "2022-04-01T00:00:00.000000000 [10626610, [0.036021859631598, 0.0302838642920515, 0.262885193889598]]\n",
      "--------------------------\n"
     ]
    }
   ],
   "source": [
    "for data in Rate(sheet3, exitZones, sortedRateDates):\n",
    "    for key in data.keys():\n",
    "        data[key][0] = zoneToMeter[data[key][0]]\n",
    "        print(key, data[key])\n",
    "    print(\"--------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "zWsk_jZ8OhIK"
   },
   "source": [
    "# Task 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "id": "4GecyjE8OhIK"
   },
   "outputs": [],
   "source": [
    "def TDC(metreIDs, sheet1, sheet3, exitZones, sortedRateDates):\n",
    "    '''\n",
    "    metreIDs: list with the metreIDs of the customer of whom we wish to find the cost\n",
    "    sheet1: dataframe which provides data related to forecasted consumption\n",
    "    sheet3: dataframe which gives the rate information in different reading date at different exitZone\n",
    "    exitZones: list of exitZones \n",
    "    sortedRateDates: dates on which reading date and rate mapping is provided\n",
    "    '''\n",
    "    \n",
    "    costDict = {}\n",
    "    consumeDict = {}\n",
    "\n",
    "    rateData = Rate(sheet3, exitZones, sortedRateDates)\n",
    "\n",
    "    for idx, data in enumerate(Consumption(metreIDs, sheet1, sortedRateDates)):\n",
    "        for date in sortedRateDates:\n",
    "            data[date][0] == rateData[idx][date][0]\n",
    "\n",
    "            consumed = data[date][1]\n",
    "            charges = None\n",
    "            if(consumed >= 0 and consumed < 73200):\n",
    "                charges = rateData[idx][date][1][0]\n",
    "            elif(consumed >= 73200 and consumed < 732000):\n",
    "                charges = rateData[idx][date][1][1]\n",
    "            elif(consumed >= 732000):\n",
    "                charges = rateData[idx][date][1][2] \n",
    "\n",
    "            charges = charges * consumed\n",
    "\n",
    "            if(str(data[date][0]) in costDict.keys()):\n",
    "                costDict[str(data[date][0])] += float(\"{:.2f}\".format((0.1 * charges)))\n",
    "                consumeDict[str(data[date][0])] += float(\"{:.2f}\".format((consumed)))\n",
    "            else:\n",
    "                costDict[str(data[date][0])] = float(\"{:.2f}\".format((0.1 * charges)))\n",
    "                consumeDict[str(data[date][0])] = float(\"{:.2f}\".format((consumed)))\n",
    "                \n",
    "    costDataFrame = pd.DataFrame(costDict.items(), columns=['Meter ID', 'Total Cost (£)'])\n",
    "    consumptionDataFrame = pd.DataFrame(consumeDict.items(), columns=['Meter ID', 'Total Estimated Consumption(kWh)'])\n",
    "    mergedDf = pd.merge(consumptionDataFrame, costDataFrame, on=\"Meter ID\")\n",
    "\n",
    "    return (mergedDf)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 143
    },
    "id": "MbYc2i-6OhIK",
    "outputId": "a293e66b-654a-4ac7-8aaf-220d1527b08e"
   },
   "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>Meter ID</th>\n",
       "      <th>Total Estimated Consumption(kWh)</th>\n",
       "      <th>Total Cost (£)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000603</td>\n",
       "      <td>24674.0</td>\n",
       "      <td>82.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10588707</td>\n",
       "      <td>122450.0</td>\n",
       "      <td>358.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10626610</td>\n",
       "      <td>303558.0</td>\n",
       "      <td>935.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Meter ID  Total Estimated Consumption(kWh)  Total Cost (£)\n",
       "0  1000000603                           24674.0           82.68\n",
       "1    10588707                          122450.0          358.72\n",
       "2    10626610                          303558.0          935.71"
      ]
     },
     "execution_count": 29,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TDC(metreIDs, sheet1, sheet3, exitZones, sortedRateDates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "id": "yGm6u53HOhIL"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Uo6QBTHfOhIL"
   },
   "source": [
    "# Task 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "id": "4iws5M37OhIL"
   },
   "outputs": [],
   "source": [
    "def Random_meter(N=random.randint(0,100)):\n",
    "    ez = []\n",
    "    aq = []\n",
    "    mid = []\n",
    "    for i in range(N):\n",
    "        EZ = random.choice(ezone)\n",
    "        AQ = random.uniform(0,100000)\n",
    "        ez.append(EZ)\n",
    "        aq.append(AQ)\n",
    "        mid.append(random.randint(0,10000))\n",
    "        \n",
    "    D_tuples = list(zip(mid, ez, aq))\n",
    "    \n",
    "    DF = pd.DataFrame(D_tuples, columns = ['Meter ID', 'Exit Zone', 'Annual Quantity (kWh)'])\n",
    "    \n",
    "    return (DF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "huobyNwQOhIL",
    "outputId": "0121af26-965d-4df5-b00f-80f406f97e23"
   },
   "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>Meter ID</th>\n",
       "      <th>Exit Zone</th>\n",
       "      <th>Annual Quantity (kWh)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3995</td>\n",
       "      <td>WA1</td>\n",
       "      <td>31057.583220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57</td>\n",
       "      <td>LC</td>\n",
       "      <td>67429.426556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3050</td>\n",
       "      <td>NT3</td>\n",
       "      <td>83587.387058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3241</td>\n",
       "      <td>EM1</td>\n",
       "      <td>30962.143275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2072</td>\n",
       "      <td>WA2</td>\n",
       "      <td>71362.543028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>5606</td>\n",
       "      <td>WM2</td>\n",
       "      <td>52846.453425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>4630</td>\n",
       "      <td>EM4</td>\n",
       "      <td>79144.532645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>9191</td>\n",
       "      <td>SO1</td>\n",
       "      <td>39870.463842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>8684</td>\n",
       "      <td>WM1</td>\n",
       "      <td>94065.001469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>4770</td>\n",
       "      <td>NW1</td>\n",
       "      <td>50165.316254</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Meter ID Exit Zone  Annual Quantity (kWh)\n",
       "0       3995       WA1           31057.583220\n",
       "1         57        LC           67429.426556\n",
       "2       3050       NT3           83587.387058\n",
       "3       3241       EM1           30962.143275\n",
       "4       2072       WA2           71362.543028\n",
       "..       ...       ...                    ...\n",
       "88      5606       WM2           52846.453425\n",
       "89      4630       EM4           79144.532645\n",
       "90      9191       SO1           39870.463842\n",
       "91      8684       WM1           94065.001469\n",
       "92      4770       NW1           50165.316254\n",
       "\n",
       "[93 rows x 3 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Random_meter()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "id": "si_LxUGBOhIL"
   },
   "outputs": [],
   "source": [
    "my_date = '2020-10-31'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "A1Hu-nGCOhIL",
    "outputId": "22578ae5-f6fd-45d4-d611-1c8ecdf68c76"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-10-31 00:00:00\n"
     ]
    }
   ],
   "source": [
    "my_date = datetime.strptime(my_date, \"%Y-%m-%d\")\n",
    "print (my_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "FqBm9ttvOhIL",
    "outputId": "2822b7c0-b405-4e41-f5ef-be248fbd5a83"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Month 10\n",
      "Year 2020\n"
     ]
    }
   ],
   "source": [
    "print ('Month', my_date.month)\n",
    "print ('Year', my_date.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "id": "pKKnnX_8OhIM"
   },
   "outputs": [],
   "source": [
    "t = np.arange(datetime(1985,7,1), datetime(2015,7,1), timedelta(days=1)).astype(datetime)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "id": "6qvpob4dOhIM"
   },
   "outputs": [],
   "source": [
    "A= pd.date_range(start=\"2018-09-09\",end=\"2020-02-02\").to_pydatetime().tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "kOvrKGiXOhIM",
    "outputId": "a82a7636-4ae6-4abd-af71-aad367ef7293"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "512"
      ]
     },
     "execution_count": 37,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(A)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "id": "WYkMFLfROhIN"
   },
   "outputs": [],
   "source": [
    "sheet1['Date'] = pd.to_datetime(sheet1['Date'])\n",
    "data = sheet1.set_index('Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "MD7mS4yEOhIO",
    "outputId": "adca11df-eb02-407e-b049-08aeb03406bd"
   },
   "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>Meter ID</th>\n",
       "      <th>kWh</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>1000000603</td>\n",
       "      <td>28.782474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>10588707</td>\n",
       "      <td>126.367711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>10626610</td>\n",
       "      <td>326.240595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>1000000603</td>\n",
       "      <td>26.690797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>10588707</td>\n",
       "      <td>118.322449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>10588707</td>\n",
       "      <td>77.744203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>10626610</td>\n",
       "      <td>270.357196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>1000000603</td>\n",
       "      <td>17.927143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>10588707</td>\n",
       "      <td>73.335098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>10626610</td>\n",
       "      <td>263.375674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2190 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Meter ID         kWh\n",
       "Date                              \n",
       "2020-10-01  1000000603   28.782474\n",
       "2020-10-01    10588707  126.367711\n",
       "2020-10-01    10626610  326.240595\n",
       "2020-10-02  1000000603   26.690797\n",
       "2020-10-02    10588707  118.322449\n",
       "...                ...         ...\n",
       "2022-09-29    10588707   77.744203\n",
       "2022-09-29    10626610  270.357196\n",
       "2022-09-30  1000000603   17.927143\n",
       "2022-09-30    10588707   73.335098\n",
       "2022-09-30    10626610  263.375674\n",
       "\n",
       "[2190 rows x 2 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "dfCQnFrrOhIO",
    "outputId": "655960a5-09a1-40f1-a237-047d24e07c19"
   },
   "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>kWh</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>28.782474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>126.367711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>326.240595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>26.690797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>118.322449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>77.744203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>270.357196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>17.927143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>73.335098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>263.375674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2190 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   kWh\n",
       "Date                  \n",
       "2020-10-01   28.782474\n",
       "2020-10-01  126.367711\n",
       "2020-10-01  326.240595\n",
       "2020-10-02   26.690797\n",
       "2020-10-02  118.322449\n",
       "...                ...\n",
       "2022-09-29   77.744203\n",
       "2022-09-29  270.357196\n",
       "2022-09-30   17.927143\n",
       "2022-09-30   73.335098\n",
       "2022-09-30  263.375674\n",
       "\n",
       "[2190 rows x 1 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['kWh']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "id": "n35YBHBFOhIP"
   },
   "outputs": [],
   "source": [
    "data_consumption = data[['kWh']]\n",
    "data_consumption.loc[:,'Yesterday'] = data_consumption.loc[:,'kWh'].shift()\n",
    "data_consumption.loc[:,'Yesterday_Diff'] = data_consumption.loc[:,'Yesterday'].diff()\n",
    "data_consumption = data_consumption.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "QMMpdACROhIP",
    "outputId": "d76bc792-a4bb-4c63-85ea-02fa138885b5"
   },
   "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>kWh</th>\n",
       "      <th>Yesterday</th>\n",
       "      <th>Yesterday_Diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-10-01</th>\n",
       "      <td>326.240595</td>\n",
       "      <td>126.367711</td>\n",
       "      <td>97.585236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>26.690797</td>\n",
       "      <td>326.240595</td>\n",
       "      <td>199.872884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>118.322449</td>\n",
       "      <td>26.690797</td>\n",
       "      <td>-299.549798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-02</th>\n",
       "      <td>316.860290</td>\n",
       "      <td>118.322449</td>\n",
       "      <td>91.631652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-03</th>\n",
       "      <td>21.511312</td>\n",
       "      <td>316.860290</td>\n",
       "      <td>198.537841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>77.744203</td>\n",
       "      <td>19.263599</td>\n",
       "      <td>-248.005488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>270.357196</td>\n",
       "      <td>77.744203</td>\n",
       "      <td>58.480604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>17.927143</td>\n",
       "      <td>270.357196</td>\n",
       "      <td>192.612993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>73.335098</td>\n",
       "      <td>17.927143</td>\n",
       "      <td>-252.430053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>263.375674</td>\n",
       "      <td>73.335098</td>\n",
       "      <td>55.407955</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2188 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   kWh   Yesterday  Yesterday_Diff\n",
       "Date                                              \n",
       "2020-10-01  326.240595  126.367711       97.585236\n",
       "2020-10-02   26.690797  326.240595      199.872884\n",
       "2020-10-02  118.322449   26.690797     -299.549798\n",
       "2020-10-02  316.860290  118.322449       91.631652\n",
       "2020-10-03   21.511312  316.860290      198.537841\n",
       "...                ...         ...             ...\n",
       "2022-09-29   77.744203   19.263599     -248.005488\n",
       "2022-09-29  270.357196   77.744203       58.480604\n",
       "2022-09-30   17.927143  270.357196      192.612993\n",
       "2022-09-30   73.335098   17.927143     -252.430053\n",
       "2022-09-30  263.375674   73.335098       55.407955\n",
       "\n",
       "[2188 rows x 3 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "id": "UvUQ5NfEOhIP"
   },
   "outputs": [],
   "source": [
    "X_train = data_consumption[:'2020'].drop(['kWh'], axis = 1)\n",
    "y_train = data_consumption.loc[:'2020', 'kWh']\n",
    "X_test = data_consumption['2022'].drop(['kWh'], axis = 1)\n",
    "y_test = data_consumption.loc['2022', 'kWh']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "qIsEsF9YOhIQ",
    "outputId": "bdc24979-4e1f-4036-88ab-00bdaa90fbe5"
   },
   "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>Yesterday</th>\n",
       "      <th>Yesterday_Diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>614.239586</td>\n",
       "      <td>324.646178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>46.929989</td>\n",
       "      <td>-567.309597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01</th>\n",
       "      <td>239.867550</td>\n",
       "      <td>192.937561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-02</th>\n",
       "      <td>580.948407</td>\n",
       "      <td>341.080857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-02</th>\n",
       "      <td>47.061218</td>\n",
       "      <td>-533.887190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>19.263599</td>\n",
       "      <td>-248.005488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-29</th>\n",
       "      <td>77.744203</td>\n",
       "      <td>58.480604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>270.357196</td>\n",
       "      <td>192.612993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>17.927143</td>\n",
       "      <td>-252.430053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>73.335098</td>\n",
       "      <td>55.407955</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>819 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Yesterday  Yesterday_Diff\n",
       "Date                                  \n",
       "2022-01-01  614.239586      324.646178\n",
       "2022-01-01   46.929989     -567.309597\n",
       "2022-01-01  239.867550      192.937561\n",
       "2022-01-02  580.948407      341.080857\n",
       "2022-01-02   47.061218     -533.887190\n",
       "...                ...             ...\n",
       "2022-09-29   19.263599     -248.005488\n",
       "2022-09-29   77.744203       58.480604\n",
       "2022-09-30  270.357196      192.612993\n",
       "2022-09-30   17.927143     -252.430053\n",
       "2022-09-30   73.335098       55.407955\n",
       "\n",
       "[819 rows x 2 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "id": "vry226lBOhIQ"
   },
   "outputs": [],
   "source": [
    "from sklearn.model_selection import TimeSeriesSplit\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn import linear_model \n",
    "from sklearn.ensemble import RandomForestRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "id": "qj4h0Us6OhIQ"
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "H5RH-OLFOhIR",
    "outputId": "69c99e4c-ef1b-471f-f8a7-d13db154ac68"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LR: 0.345770 (0.187792)\n",
      "LASSO: 0.345807 (0.187780)\n",
      "RF: 0.538238 (0.495982)\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXwAAAEICAYAAABcVE8dAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAVHUlEQVR4nO3df7RdZX3n8fcHMFCXCKRJUUhKqISOsSraO3SmtcoUHAOdEl3tIBmt6EKZmSW1qzjtYGE0xdGpnWW11tg2RYtgESNr4cQxDlpK6+iAk5uClMAgAUXCD7lCBJVfBr7zx9mXOdy5N9ybe+49N3ner7XOyt7P85z9PHvvlc/d59nnR6oKSdK+b79hD0CSND8MfElqhIEvSY0w8CWpEQa+JDXCwJekRhj4GpgkFyX5z3O07Tck+dJu6k9IsmMu+t7bJfn9JBcOexwaPgNfM5bk75LsTHLgfPVZVX9dVf+ybwyV5Jj56j8970hyY5IfJdmR5LNJXjxfY9hTVfX+qnrrsMeh4TPwNSNJVgC/DBRw6jz1ecB89PMM/gT4beAdwGLgWOBzwK8Oc1DPZIEcOy0QBr5m6k3AtcBFwBm7a5jk95Lck+TuJG/tvypPckiSi5OMJbkjyflJ9uvq3pzka0k+lOR+YF1X9tWu/itdF99I8sMkr+/r851J7uv6fUtf+UVJPpbki91zvpbkeUk+3L1a+T9JXjbFfqwE3g6sraq/rarHqurh7lXHH85wf76f5PYkv9iV39mN94wJY/3zJF9O8oMkf5/kqL76P+me91CSrUl+ua9uXZLLk3wqyUPAm7uyT3X1B3V193dj2ZLk8K7uiCSbkjyQZHuSt03Y7sZuH3+QZFuSkd2dfy08Br5m6k3AX3eP14yHxURJVgPnACcBxwAnTGjyp8AhwM8Ar+q2+5a++l8AbgcOB97X/8SqemW3+NKqek5VfaZbf163zSOBM4H1SQ7re+ppwPnAEuAx4BrgH7r1y4E/nmKfTwR2VNX/nqJ+uvtzA/CTwKXAZcA/pXds3gh8NMlz+tq/AXhvN7br6R3vcVuA4+i90rgU+GySg/rq13T7c+iE50Hvj/QhwPJuLP8OeKSruwzYARwB/Abw/iS/0vfcU7s2hwKbgI/u5nhoATLwNW1JXgEcBWysqq3AbcC/maL5acBfVdW2qnoYWNe3nf2B04F3VdUPqurbwAeB3+x7/t1V9adVtauqHmF6fgxcUFU/rqrNwA+Bn+2rv6KqtlbVo8AVwKNVdXFVPQF8Bpj0Cp9eMN4zVafT3J9vVdVf9fW1vBvrY1X1JeBxeuE/7gtV9ZWqegw4D/jnSZYDVNWnqur+7th8EDhwwn5eU1Wfq6onJzl2P+7255iqeqI7Hg912/4l4D9W1aNVdT1wIb0/XOO+WlWbu324BHjpVMdEC5OBr5k4A/hSVX2vW7+Uqad1jgDu7FvvX14CPAu4o6/sDnpX5pO1n677q2pX3/rDQP9V83f7lh+ZZL2/7dO2Czx/N/1OZ38m9kVV7a7/p/a/qn4IPEDvmJLkPyS5OcmDSb5P74p9yWTPncQlwJXAZd1U2x8leVa37Qeq6ge72Yd7+5YfBg7yHsHexcDXtCT5CXpX7a9Kcm+Se4HfAV6aZLIrvXuAZX3ry/uWv0fvSvOovrKfBu7qW19IX+N6FbBsN3PW09mfmXrqeHVTPYuBu7v5+t+jdy4Oq6pDgQeB9D13ymPXvfr5g6paBfwi8K/oXcXfDSxOcvAA90ELjIGv6Xot8ASwit788XHAC4H/ydNf9o/bCLwlyQuTPBv4T+MV3ZTARuB9SQ7ubkieA3xqBuP5Lr358jlXVbcCHwM+nd77/Rd1Nz9PT3LugPZnolOSvCLJInpz+ddW1Z3AwcAuYAw4IMm7gedOd6NJ/kWSF3fTUA/R+0P1ZLft/wX8l27fXkLvPshs9kELjIGv6TqD3pz8d6rq3vEHvRt3b5j40r6qvgh8BLga2E7vnT3Qu1kK8FvAj+jdmP0qvemhT8xgPOuAT3bvNDltD/dpJt5Bb1/XA9+nd//idcDnu/rZ7s9ElwLvoTeV8/P0buxCbzrmfwDfpDfl8igzm/56Hr0bug8BNwN/T2+aB2AtsILe1f4VwHuq6m9msQ9aYOIPoGg+JHkhcCNw4IR5dk2Q5CJ67wo6f9hj0b7FK3zNmSSvS3Jg99bIDwCfN+yl4THwNZf+LXAfvemPJ4B/P9zhSG1zSkeSGuEVviQ1YsF+aGLJkiW1YsWKYQ9DkvYqW7du/V5VLZ2sbsEG/ooVKxgdHR32MCRpr5LkjqnqnNKRpEYY+JLUCANfkhph4EtSIwYS+Ek+0f1qz41T1CfJR7pf0bkhycsH0a8kafoGdYV/EbB6N/UnAyu7x1nAnw2oX0nSNA0k8KvqK/S+1W8qa4CLq+da4NAku/tBCUnSgM3XHP6RPP0rXHfw9F/SASDJWUlGk4yOjY3N09AkqQ0L6qZtVW2oqpGqGlm6dNIPiknSbiUZyGNfNF+BfxdP/4m7ZfjTaZL2wOLFi+clqHfXx+LFiwfWz3yar69W2AScneQy4BeAB6vqnnnqW9I+ZOfOnQz7W3731lcAAwn8JJ8GTgCWJNlB76fZngVQVX8ObAZOofdTdw8DbxlEv5Kk6RtI4FfV2meoL+Dtg+hLkrRnFtRNW0nS3DHwJakRBr4kNcLAl6RGGPiS1AgDX5IaYeBLUiMMfElqhIEvSY0w8CWpEQa+JDXCwJekRhj4ktQIA1+SGmHgS1IjDHxJaoSBL0mNMPAlqREGviQ1wsCXpEYY+JLUiIEEfpLVSW5Jsj3JuZPU/3SSq5Ncl+SGJKcMol9J0vTNOvCT7A+sB04GVgFrk6ya0Ox8YGNVvQw4HfjYbPuVJM3MIK7wjwe2V9XtVfU4cBmwZkKbAp7bLR8C3D2AfiVJMzCIwD8SuLNvfUdX1m8d8MYkO4DNwG9NtqEkZyUZTTI6NjY2gKFJksbN103btcBFVbUMOAW4JMn/13dVbaiqkaoaWbp06TwNTZLaMIjAvwtY3re+rCvrdyawEaCqrgEOApYMoG9J0jQNIvC3ACuTHJ1kEb2bspsmtPkOcCJAkhfSC3znbCRpHs068KtqF3A2cCVwM71342xLckGSU7tm7wTeluQbwKeBN1dVzbZvSdL0ZaHm7sjISI2Ojg57GJIWmnWHDHsEPeseHPYIJpVka1WNTFZ3wHwPRpJmI3/wEMO+UE1CrRvqEPaIX60gSY0w8CWpEQa+JDXCwJekRhj4ktQIA1+SGmHgS1IjDHxJaoSBL0mNMPAlqREGviQ1wsCXpEYY+JLUCANfkhph4EtSIwx8SWqEP4AyR5IMZDvD/qGHFnnutK8y8OfIM/1nT2IgLFDTOS+eP+2NnNKRpEYY+JLUiIEEfpLVSW5Jsj3JuVO0OS3JTUm2Jbl0EP1KkqZv1nP4SfYH1gOvBnYAW5Jsqqqb+tqsBN4F/FJV7UzyU7PtV5I0M4O4wj8e2F5Vt1fV48BlwJoJbd4GrK+qnQBVdd8A+pUkzcAgAv9I4M6+9R1dWb9jgWOTfC3JtUlWT7ahJGclGU0yOjY2NoChSZLGzddN2wOAlcAJwFrgL5McOrFRVW2oqpGqGlm6dOk8DU2S2jCIwL8LWN63vqwr67cD2FRVP66qbwHfpPcHQJI0TwYR+FuAlUmOTrIIOB3YNKHN5+hd3ZNkCb0pntsH0LckaZpmHfhVtQs4G7gSuBnYWFXbklyQ5NSu2ZXA/UluAq4Gfreq7p9t35Kk6ctC/Xj4yMhIjY6ODnsYc8aP5u/dPH/DsxCO/UIYw1SSbK2qkcnq/KStJDXCwJekRhj4ktQIA38PLV68mCR7/ABm9fwkLF68eMhHYe8023Pn+dPeyu/D30M7d+4c+k2bQf1QR2sWwrkDz5/mn1f4ktQIA1+SGmHgS1IjDHxJaoSBL0mNMPAlqREGviQ1wsCXpEYY+JLUCANfkhph4EtSIwx8SWqEgS9JjTDwJakRBr4kNcLAl6RGDCTwk6xOckuS7UnO3U27X09SSSb9RXVJ0tyZdeAn2R9YD5wMrALWJlk1SbuDgd8Gvj7bPiVJMzeIK/zjge1VdXtVPQ5cBqyZpN17gQ8Ajw6gT0nSDA0i8I8E7uxb39GVPSXJy4HlVfWF3W0oyVlJRpOMjo2NDWBokqRxc37TNsl+wB8D73ymtlW1oapGqmpk6dKlcz00SWrKIAL/LmB53/qyrmzcwcDPAX+X5NvAPwM2eeNWkubXIAJ/C7AyydFJFgGnA5vGK6vqwapaUlUrqmoFcC1walWNDqBvSdI0zTrwq2oXcDZwJXAzsLGqtiW5IMmps92+JGkwDhjERqpqM7B5Qtm7p2h7wiD6lCTNjJ+0laRGGPiS1AgDX5IaYeBLUiMMfElqhIEvSY0w8CWpEQa+JDXCwJekRhj4ktQIA1+SGmHgS1IjDHxJaoSBL0mNMPAlqREGviQ1wsCXpEYY+JLUiIH8xGGL6j3PhXWHDH8MmrGFcO6eGoc0j1JVwx7DpEZGRmp0dHTYw5hSEoZ97BbCGPZGC+W4LZRx7G0WwnFbCGOYSpKtVTUyWd1ApnSSrE5yS5LtSc6dpP6cJDcluSHJVUmOGkS/kqTpm3XgJ9kfWA+cDKwC1iZZNaHZdcBIVb0EuBz4o9n2K0mamUFc4R8PbK+q26vqceAyYE1/g6q6uqoe7lavBZYNoF9J0gwMIvCPBO7sW9/RlU3lTOCLk1UkOSvJaJLRsbGxAQxNkjRuXt+lk+SNwAjwqsnqq2oDsAF6N23ncWh7JMlQ+z/ssMOG2v/ebNjnDjx/mn+DCPy7gOV968u6sqdJchJwHvCqqnpsAP0O1Wzv0C/ku/z7ukEcd8+f9kaDmNLZAqxMcnSSRcDpwKb+BkleBvwFcGpV3TeAPiVJMzTrwK+qXcDZwJXAzcDGqtqW5IIkp3bN/ivwHOCzSa5PsmmKzUmS5shA5vCrajOweULZu/uWTxpEP5IEw78Hs7fef/GrFSTtVbx/tuf88jRJaoSBL0mNcEpnjkxnjnE6bVp96TlM050ffqZ2njstNAb+HPE/+97Lc6d9lVM6ktQIA1+SGmHgS1IjDHxJaoSBL0mNMPAlqREGviQ1wsCXpEYY+JLUCANfkhph4EtSIwx8SWqEgS9JjTDwJakRBr4kNcLAl6RGDCTwk6xOckuS7UnOnaT+wCSf6eq/nmTFIPqVJE3frAM/yf7AeuBkYBWwNsmqCc3OBHZW1THAh4APzLZfSdLMDOIK/3hge1XdXlWPA5cBaya0WQN8slu+HDgx0/3hUEnSQAwi8I8E7uxb39GVTdqmqnYBDwI/OXFDSc5KMppkdGxsbABDkySNW1A3batqQ1WNVNXI0qVLhz0cSdqnDCLw7wKW960v68ombZPkAOAQ4P4B9C1JmqZBBP4WYGWSo5MsAk4HNk1oswk4o1v+DeBvq6oG0LckaZoOmO0GqmpXkrOBK4H9gU9U1bYkFwCjVbUJ+DhwSZLtwAP0/ihIkubRrAMfoKo2A5snlL27b/lR4F8Poi9J0p5ZUDdtJUlzx8CXpEYY+JLUCANfkhph4EtSIwx8SWqEgS9JjTDwJakRBr4kNcLAl6RGGPiS1AgDX5IaYeBLUiMMfElqhIEvSY0w8CWpEQa+JDXCwJekRhj4ktQIA1+SGmHgS1IjZhX4SRYn+XKSW7t/D5ukzXFJrkmyLckNSV4/mz4lSXtmtlf45wJXVdVK4KpufaKHgTdV1YuA1cCHkxw6y34lSTM028BfA3yyW/4k8NqJDarqm1V1a7d8N3AfsHSW/UqSZmi2gX94Vd3TLd8LHL67xkmOBxYBt01Rf1aS0SSjY2NjsxyaJKnfAc/UIMnfAM+bpOq8/pWqqiS1m+08H7gEOKOqnpysTVVtADYAjIyMTLktSdLMPWPgV9VJU9Ul+W6S51fVPV2g3zdFu+cCXwDOq6pr93i0kqQ9NtspnU3AGd3yGcB/m9ggySLgCuDiqrp8lv1JkvbQbAP/D4FXJ7kVOKlbJ8lIkgu7NqcBrwTenOT67nHcLPuVJM1QqhbmVPnIyEiNjo4OexiS9jFJWKi5NwhJtlbVyGR1ftJWkhph4EtSIwx8SWqEgS9JjTDwJakRBr4kNcLAl6RGGPiS1AgDX5IaYeBLUiMMfElqhIEvSY0w8CWpEQa+JDXiGX/xSpL2JkkG0mZf/AplA1/SPmVfDOpBcUpHkhph4EtSIwx8SWqEgS9JjTDwJakRBr4kNcLAl6RGGPiS1Igs1A8pJBkD7hj2OObQEuB7wx6E9pjnb++1r5+7o6pq6WQVCzbw93VJRqtqZNjj0J7x/O29Wj53TulIUiMMfElqhIE/PBuGPQDNiudv79XsuXMOX5Ia4RW+JDXCwJekRhj48yDJDycpW5fkriTXJ7kpydphjK1Vk52TvroPd+dmv76yw5P89yTf6M7X5q58vyQfSXJjkn9MsiXJ0V3dIUkuTrI9yW3d8iFzv3cal+SJ7v/YjUk+n+TQrnxFkke6uvHHomGPd64Z+MP1oao6DlgD/EWSZw17QK3rQv51wJ3Aq/qqLgC+XFUvrapVwLld+euBI4CXVNWLu+d+v6v7OHB7VR1TVS8AvgVcOA+7of/nkao6rqp+DngAeHtf3W1d3fjj8SGNcd4Y+AtAVd0KPAwcNuyxiBOAbcCfAf2vup4P7Bhfqaob+srvqaonu/IdVbUzyTHAzwPv7dvGBcBIkhfM3fC1G9cARw57EMNk4C8ASV4O3FpV9w17LGIt8GngCuBX+151rQc+nuTqJOclOaIr3wj8Wjcl8MEkL+vKVwHXV9UT4xvulq8HXjQve6KnJNkfOBHY1Ff8gr7pnPVDGtq8MvCH63eSbAO+Drxv2INpXTeHewrwuap6iN55eQ1AVV0J/Azwl8A/Aa5LsrSqdgA/C7wLeBK4KsmJwxi/JvUTSa4H7gUOB77cV9c/pfP2yZ++bzHwh+tDVfUi4NfpXT0eNOwBNe41wKHAPyb5NvAK+qZ1quqBqrq0qn4T2AK8sit/rKq+WFW/C7wfeC1wE3DchBu/+wHHdXWaH49098mOAsLT5/CbY+AvAFW1CRgFzhj2WBq3FnhrVa2oqhXA0cCrkzw7ya8keTZAkoOBFwDfSfLy8emdLtBfAtxRVduB64Dz+7Z/PvAPXZ3mUVU9DLwDeGeSA4Y9nmEx8OfHs5Ps6HucM0mbC4Bz+q8INacmnpPfB1YDXxhvUFU/Ar4K/Bq9G7CjSW6gd/PvwqraAvwU8PkkNwI3ALuAj3abOBM4tntL5m3AsV2ZhqCqrqN3jpp9C7RfrSBJjfBqUpIaYeBLUiMMfElqhIEvSY0w8CWpEQa+JDXCwJekRvxfpUY2v9/AoqAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light",
      "tags": []
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Testing some time series Algorithms\n",
    "models = []\n",
    "models.append(('LR', linear_model.LinearRegression()))\n",
    "models.append(('LASSO', linear_model.Lasso()))\n",
    "models.append(('RF', RandomForestRegressor()))\n",
    "\n",
    "\n",
    "# Evaluate each model in turn\n",
    "results = []\n",
    "names = []\n",
    "for name, model in models:\n",
    "    # TimeSeries Cross validation\n",
    "    tscv = TimeSeriesSplit(n_splits=12)\n",
    "    \n",
    "    cv_results = cross_val_score(model, X_train, y_train, cv=tscv, scoring='r2')\n",
    "    results.append(cv_results)\n",
    "    names.append(name)\n",
    "    print('%s: %f (%f)' % (name, cv_results.mean(), cv_results.std()))\n",
    "    \n",
    "# Compare Algorithms\n",
    "plt.boxplot(results, labels=names)\n",
    "plt.title('Algorithm Comparison')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "id": "OTiCXAvoOhIR"
   },
   "outputs": [],
   "source": [
    "y_true = y_test.values\n",
    "RF = RandomForestRegressor()\n",
    "pmodel = RF.fit(X_train, y_train)\n",
    "y_pred = pmodel.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SOdSdJLeOhIR",
    "outputId": "e2639eba-f3df-47e0-ed50-ef1b715b2d61"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "819"
      ]
     },
     "execution_count": 49,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(y_pred)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "1_bEcRbjOhIS",
    "outputId": "92d7474f-07f2-4d1f-d011-2fe0ca0782e5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[164.77633797630187, 211.5054528548243, 449.0528290918708, 97.70330308061781, 455.55657456234206, 437.61476955559345, 62.156778551080485, 250.36044614711966, 227.84377407000844, 16.20745364657672]\n"
     ]
    }
   ],
   "source": [
    "K=[random.uniform(0,500) for i in range(10)]\n",
    "print (K)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "id": "XoH7FchmOhIS"
   },
   "outputs": [],
   "source": [
    "A= pd.date_range(start=\"2018-09-09\",end=\"2020-02-02\").to_pydatetime().tolist()\n",
    "    \n",
    "kWh = [random.uniform(0,500) for i in range(len(A))]\n",
    "    \n",
    "mock_data = list(zip(A, kWh))\n",
    "    \n",
    "    \n",
    "data_mock = pd.DataFrame(mock_data, columns = ['Date', 'kWh'])\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3wkHxkT1OhIS"
   },
   "source": [
    "# Task 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "id": "aWjT-KjxOhIS"
   },
   "outputs": [],
   "source": [
    "def mock_consumption(mlist=[8900], start = '2020-10-31', end = '2022-10-31'):\n",
    "    \n",
    "    if start>end:\n",
    "        return(print ('CAUTION: Your start date should not be in future than end date'))\n",
    "        \n",
    "    \n",
    "    mock_meter_consumption_list = np.array(len(mlist))\n",
    "    frames = []\n",
    "    \n",
    "    for k in range (len(mlist)):\n",
    "        \n",
    "        #Range of the date\n",
    "        A = pd.date_range(start = start, end = end).to_pydatetime().tolist()\n",
    "        \n",
    "        #Random kWh charges of the same length as dates\n",
    "        kWh = [random.uniform(0,500) for i in range(len(A))]\n",
    "    \n",
    "        mock_data = list(zip(A, kWh))\n",
    "        \n",
    "        #Our raw_test_data_set for prediction\n",
    "        data_mock = pd.DataFrame(mock_data, columns = ['Date', 'kWh'])\n",
    "    \n",
    "        # creating new dataframe from consumption column\n",
    "        data_mock_consumption = data_mock[['kWh']]\n",
    "\n",
    "        # inserting new column with yesterday's consumption values\n",
    "        data_mock_consumption.loc[:,'Yesterday'] = data_mock_consumption.loc[:,'kWh'].shift()\n",
    "\n",
    "        # inserting another column with difference between yesterday and day before yesterday's consumption values.\n",
    "        data_mock_consumption.loc[:,'Yesterday_Diff'] = data_mock_consumption.loc[:,'Yesterday'].diff()\n",
    "\n",
    "        # dropping NAs and kWh\n",
    "        data_mock_consumption = data_mock_consumption.dropna()\n",
    "        data_mock_consumption = data_mock_consumption.drop(['kWh'], axis = 1)\n",
    "    \n",
    "        data_mock_pred_consumption = pmodel.predict(data_mock_consumption)\n",
    "    \n",
    "        meter_ID = [mlist[k] for i in range(len(A))]\n",
    "        \n",
    "        meter_data = list(zip(A, meter_ID, data_mock_pred_consumption))\n",
    "    \n",
    "        mock_meter_consumption_list = pd.DataFrame( meter_data, columns = ['Date', 'Meter ID', 'kWh'])\n",
    "        \n",
    "        frames.append(mock_meter_consumption_list)    \n",
    "    \n",
    "    mock_meter_consumption = pd.concat(frames)\n",
    "    \n",
    "    return (mock_meter_consumption)\n",
    "            \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 0
    },
    "id": "lTpv8naCOhIT",
    "outputId": "62f26e15-fb60-4d7a-96ce-3eb2e471dbe8"
   },
   "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>Date</th>\n",
       "      <th>Meter ID</th>\n",
       "      <th>kWh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-31</td>\n",
       "      <td>10626610</td>\n",
       "      <td>399.779638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-01</td>\n",
       "      <td>10626610</td>\n",
       "      <td>374.338190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>10626610</td>\n",
       "      <td>284.346300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>10626610</td>\n",
       "      <td>495.565419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-04</td>\n",
       "      <td>10626610</td>\n",
       "      <td>329.891832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>105.798167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>725</th>\n",
       "      <td>2022-10-26</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>33.965692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>726</th>\n",
       "      <td>2022-10-27</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>162.015477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>727</th>\n",
       "      <td>2022-10-28</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>297.284003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>728</th>\n",
       "      <td>2022-10-29</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>38.369216</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2187 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date    Meter ID         kWh\n",
       "0   2020-10-31    10626610  399.779638\n",
       "1   2020-11-01    10626610  374.338190\n",
       "2   2020-11-02    10626610  284.346300\n",
       "3   2020-11-03    10626610  495.565419\n",
       "4   2020-11-04    10626610  329.891832\n",
       "..         ...         ...         ...\n",
       "724 2022-10-25  1000000603  105.798167\n",
       "725 2022-10-26  1000000603   33.965692\n",
       "726 2022-10-27  1000000603  162.015477\n",
       "727 2022-10-28  1000000603  297.284003\n",
       "728 2022-10-29  1000000603   38.369216\n",
       "\n",
       "[2187 rows x 3 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mock_consumption(mlist =[10626610, 10588707, 1000000603])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "I8UTL7BxOhIT",
    "outputId": "4551df28-953e-43d0-f113-7ff26225c229"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CAUTION: Your start date should not be in future than end date\n"
     ]
    }
   ],
   "source": [
    "mock_consumption(start = '2023-01-01') # Test for Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1taQdeH-OhIT"
   },
   "source": [
    "# Task 4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XvBPWC3ROhIT"
   },
   "source": [
    "## Assumptions:\n",
    " ## 1. meter is a list\n",
    " ## 2. Consumption table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "id": "HkkuXR6gStYu"
   },
   "outputs": [],
   "source": [
    "def Random_meter_datagen_benchmark(metreIDs, N=random.randint(0,100)):\n",
    "    ez = []\n",
    "    aq = []\n",
    "    mid = []\n",
    "    idx = 0\n",
    "    for i in range(N):\n",
    "        EZ = random.choice(ezone)\n",
    "        AQ = random.uniform(0,100000)\n",
    "        ez.append(EZ)\n",
    "        aq.append(AQ)\n",
    "        mid.append(metreIDs[idx])\n",
    "        idx += 1\n",
    "        idx = idx % 3\n",
    "        \n",
    "    D_tuples = list(zip(mid, ez, aq))\n",
    "    \n",
    "    DF = pd.DataFrame(D_tuples, columns = ['Meter ID', 'Exit Zone', 'Annual Quantity (kWh)'])\n",
    "    \n",
    "    return (DF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "rM6mrLWzOhIT",
    "outputId": "490f13fb-fb9e-474d-c15d-7d2713300198"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1000000603\n",
       "1       10588707\n",
       "2       10626610\n",
       "3     1000000603\n",
       "4       10588707\n",
       "5       10626610\n",
       "6     1000000603\n",
       "7       10588707\n",
       "8       10626610\n",
       "9     1000000603\n",
       "10      10588707\n",
       "11      10626610\n",
       "12    1000000603\n",
       "13      10588707\n",
       "14      10626610\n",
       "15    1000000603\n",
       "16      10588707\n",
       "17      10626610\n",
       "18    1000000603\n",
       "19      10588707\n",
       "20      10626610\n",
       "21    1000000603\n",
       "22      10588707\n",
       "23      10626610\n",
       "24    1000000603\n",
       "25      10588707\n",
       "26      10626610\n",
       "27    1000000603\n",
       "28      10588707\n",
       "29      10626610\n",
       "30    1000000603\n",
       "31      10588707\n",
       "Name: Meter ID, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mlist = Random_meter_datagen_benchmark(metreIDs)['Meter ID']\n",
    "mlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "id": "iZbf0K-dbHhl"
   },
   "outputs": [],
   "source": [
    "benchmark_data = mock_consumption(metreIDs, start = '2020-10-31', end = '2022-09-30')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "6QIIKjf5TXcr",
    "outputId": "d5aa2f10-597c-4c6e-90af-5a6036383c91"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10588707      698\n",
       "10626610      698\n",
       "1000000603    698\n",
       "Name: Meter ID, dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "benchmark_data['Meter ID'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "KGUYbchrOhIU",
    "outputId": "d9c5d897-385c-4c42-ab01-3f418cdba405"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2094 entries, 0 to 697\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Non-Null Count  Dtype         \n",
      "---  ------    --------------  -----         \n",
      " 0   Date      2094 non-null   datetime64[ns]\n",
      " 1   Meter ID  2094 non-null   int64         \n",
      " 2   kWh       2094 non-null   float64       \n",
      "dtypes: datetime64[ns](1), float64(1), int64(1)\n",
      "memory usage: 65.4 KB\n"
     ]
    }
   ],
   "source": [
    "benchmark_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "Vco30uCmb-Kq",
    "outputId": "206a8d2f-c11f-4256-e1bf-63eb96347900"
   },
   "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>Date</th>\n",
       "      <th>Meter ID</th>\n",
       "      <th>kWh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-31</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>396.492734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-01</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>31.419092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-02</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>36.160565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>310.394855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-04</td>\n",
       "      <td>1000000603</td>\n",
       "      <td>443.680060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>2022-09-24</td>\n",
       "      <td>10626610</td>\n",
       "      <td>398.808828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>2022-09-25</td>\n",
       "      <td>10626610</td>\n",
       "      <td>35.648421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>695</th>\n",
       "      <td>2022-09-26</td>\n",
       "      <td>10626610</td>\n",
       "      <td>152.312242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696</th>\n",
       "      <td>2022-09-27</td>\n",
       "      <td>10626610</td>\n",
       "      <td>42.633156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>2022-09-28</td>\n",
       "      <td>10626610</td>\n",
       "      <td>396.381472</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2094 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date    Meter ID         kWh\n",
       "0   2020-10-31  1000000603  396.492734\n",
       "1   2020-11-01  1000000603   31.419092\n",
       "2   2020-11-02  1000000603   36.160565\n",
       "3   2020-11-03  1000000603  310.394855\n",
       "4   2020-11-04  1000000603  443.680060\n",
       "..         ...         ...         ...\n",
       "693 2022-09-24    10626610  398.808828\n",
       "694 2022-09-25    10626610   35.648421\n",
       "695 2022-09-26    10626610  152.312242\n",
       "696 2022-09-27    10626610   42.633156\n",
       "697 2022-09-28    10626610  396.381472\n",
       "\n",
       "[2094 rows x 3 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "benchmark_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "id": "RiQOfqxwUks7"
   },
   "outputs": [],
   "source": [
    "sheet3 = df_dummy.parse(2)\n",
    "newSheet3 = sheet3[(sheet3['Date'] >= '2020-10-31') & (sheet3['Date'] <= '2022-09-30')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "id": "SxLL8gorUkqe"
   },
   "outputs": [],
   "source": [
    "newSheet3 = newSheet3.loc[sheet3['Exit Zone'].isin(exitZones)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "oFuEpwoJUkod",
    "outputId": "5537ba05-6c73-493b-f009-8c8a86cf405c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 27 entries, 233 to 555\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   Date                   27 non-null     datetime64[ns]\n",
      " 1   Exit Zone              27 non-null     object        \n",
      " 2   Annual Quantity (Min)  27 non-null     int64         \n",
      " 3   Annual Quantity (Max)  18 non-null     float64       \n",
      " 4   Rate (p/kWh)           27 non-null     float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(1), object(1)\n",
      "memory usage: 1.3+ KB\n"
     ]
    }
   ],
   "source": [
    "newSheet3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "uF9NPlNDUkmH",
    "outputId": "b8790fd6-7a14-4459-c7d7-38c3f49a1ac9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[numpy.datetime64('2021-04-01T00:00:00.000000000'),\n",
       " numpy.datetime64('2021-10-01T00:00:00.000000000'),\n",
       " numpy.datetime64('2022-04-01T00:00:00.000000000')]"
      ]
     },
     "execution_count": 64,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sortedRateDates = relevant_dates(newSheet3)\n",
    "sortedRateDates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 143
    },
    "id": "WrZTOz0Wa2jI",
    "outputId": "b7ffecf9-0c63-4fce-d53f-41053a370159"
   },
   "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>Meter ID</th>\n",
       "      <th>Total Estimated Consumption(kWh)</th>\n",
       "      <th>Total Cost (£)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000603</td>\n",
       "      <td>112238.35</td>\n",
       "      <td>379.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10588707</td>\n",
       "      <td>114672.14</td>\n",
       "      <td>342.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10626610</td>\n",
       "      <td>119628.01</td>\n",
       "      <td>419.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Meter ID  Total Estimated Consumption(kWh)  Total Cost (£)\n",
       "0  1000000603                         112238.35          379.36\n",
       "1    10588707                         114672.14          342.05\n",
       "2    10626610                         119628.01          419.54"
      ]
     },
     "execution_count": 65,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TDC(metreIDs, benchmark_data, newSheet3, exitZones, sortedRateDates)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7FXMaQjpgc1e"
   },
   "source": [
    "## Complexity analysis of the approach\n",
    "\n",
    "> Task : Compute and return a dataframe describing Transportation cost\n",
    "\n",
    "Let \n",
    "\n",
    "* $z$ denote number of unique zones\n",
    "* $c$ denote number of rows in the forecasted consumption table\n",
    "* $r$ denote number of unique reading dates\n",
    "\n",
    "then time required is -----\n",
    "\n",
    "\n",
    "For calculating \n",
    "* sortedRateDates : $O(r * logr)$ \n",
    "\n",
    "The functions\n",
    "* Consumption() takes $O(z (c + rc))$ which is equivalent to $O(zrc)$\n",
    "\n",
    "* Rate() takes $O(zr)$\n",
    "* TDC() takes $O(zr + zr^2c)$  i.e. $O(zr^2c)$\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Kf-FSy_1q7FX"
   },
   "source": [
    "## Task 5\n",
    "\n",
    "The dominant term in my approach is the $r^2$ in the $O(zr^2c)$. That comes up as in this approach I take, $O(zr^2c)$ as $O(zrc) * O(r)$ where the $O(r)$ comes from the iteration over `sortedRateDates`. Now, in the output of `Consumption()` we already have the date related information. \n",
    "\n",
    "To improve the complexity, we should try and exlpore the means on how we can use that information.\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "Gorilla_test_rough.ipynb",
   "provenance": []
  },
  "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}