May 2012 to April 2013.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2d5a0011-d521-4733-a1ed-d39c7b465c00",
   "metadata": {},
   "source": [
    "## Import Statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e5e8fbae-5f53-4fbd-8a25-97b0b91b359a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.dates as mdates\n",
    "import seaborn as sns\n",
    "from scipy import stats\n",
    "\n",
    "pd.options.display.max_columns=25"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32cf7843-eb07-4d7c-873d-fe8aaf9f9540",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Data FY 2013-2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4a0ceca7-39b6-462a-8f53-957457fc55d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65806, 24)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2 = pd.read_excel('houston-houston-electricity-bills/coh-ee-bills-may2012-apr2013.xlsx')\n",
    "orig_shape_2013_2 = data_2013_2.shape[0]\n",
    "\n",
    "data_2013_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2ef78f9b-3b74-4c50-97b5-c9236f4f2893",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Reliant Contract No</th>\n",
       "      <th>Service Address</th>\n",
       "      <th>Meter No</th>\n",
       "      <th>ESID</th>\n",
       "      <th>Business Area</th>\n",
       "      <th>Cost Center</th>\n",
       "      <th>Fund</th>\n",
       "      <th>Bill Type</th>\n",
       "      <th>Bill Date</th>\n",
       "      <th>Read Date</th>\n",
       "      <th>Due Date</th>\n",
       "      <th>Meter Read</th>\n",
       "      <th>Base Cost ($)</th>\n",
       "      <th>T&amp;D Discretionary ($)</th>\n",
       "      <th>T&amp;D Charges ($)</th>\n",
       "      <th>Current Due ($)</th>\n",
       "      <th>Adjustment ($)</th>\n",
       "      <th>Total Due ($)</th>\n",
       "      <th>Franchise Fee ($)</th>\n",
       "      <th>Voucher Date</th>\n",
       "      <th>Billed Demand (KVA)</th>\n",
       "      <th>kWh Usage</th>\n",
       "      <th>Nodal Cu Charge ($)</th>\n",
       "      <th>Reliability Unit Charge ($)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2059605</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>303261</td>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000040005</td>\n",
       "      <td>8300</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-04-23</td>\n",
       "      <td>2013-05-29</td>\n",
       "      <td>55598.0</td>\n",
       "      <td>67005.80</td>\n",
       "      <td>1357.92</td>\n",
       "      <td>10853.62</td>\n",
       "      <td>79309.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79309.40</td>\n",
       "      <td>-1036.92</td>\n",
       "      <td>2013-04-30</td>\n",
       "      <td>1502.083333</td>\n",
       "      <td>997407.0</td>\n",
       "      <td>92.06</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2059605</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>303261</td>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000040005</td>\n",
       "      <td>8300</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-03-28</td>\n",
       "      <td>2013-03-21</td>\n",
       "      <td>2013-04-27</td>\n",
       "      <td>54767.0</td>\n",
       "      <td>57008.44</td>\n",
       "      <td>1300.83</td>\n",
       "      <td>10473.16</td>\n",
       "      <td>68778.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68778.30</td>\n",
       "      <td>-995.50</td>\n",
       "      <td>2013-03-29</td>\n",
       "      <td>1432.989691</td>\n",
       "      <td>849351.0</td>\n",
       "      <td>-4.13</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2059605</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>303261</td>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000040005</td>\n",
       "      <td>8300</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-02-26</td>\n",
       "      <td>2013-02-21</td>\n",
       "      <td>2013-03-28</td>\n",
       "      <td>54059.0</td>\n",
       "      <td>61287.57</td>\n",
       "      <td>1313.49</td>\n",
       "      <td>10534.33</td>\n",
       "      <td>73135.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73135.18</td>\n",
       "      <td>-1000.33</td>\n",
       "      <td>2013-02-27</td>\n",
       "      <td>1452.577320</td>\n",
       "      <td>911746.0</td>\n",
       "      <td>-0.21</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2059605</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>303261</td>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000040005</td>\n",
       "      <td>8300</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-01-28</td>\n",
       "      <td>2013-01-22</td>\n",
       "      <td>2013-02-27</td>\n",
       "      <td>53299.0</td>\n",
       "      <td>64657.23</td>\n",
       "      <td>1369.20</td>\n",
       "      <td>10878.65</td>\n",
       "      <td>77043.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77043.90</td>\n",
       "      <td>-1048.66</td>\n",
       "      <td>2013-01-29</td>\n",
       "      <td>1498.969072</td>\n",
       "      <td>969810.0</td>\n",
       "      <td>138.82</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2059605</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>303261</td>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000040005</td>\n",
       "      <td>8300</td>\n",
       "      <td>T</td>\n",
       "      <td>2012-12-28</td>\n",
       "      <td>2012-12-20</td>\n",
       "      <td>2013-01-27</td>\n",
       "      <td>52491.0</td>\n",
       "      <td>60872.54</td>\n",
       "      <td>1612.61</td>\n",
       "      <td>10928.60</td>\n",
       "      <td>73740.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73740.34</td>\n",
       "      <td>-1088.01</td>\n",
       "      <td>2012-12-31</td>\n",
       "      <td>1572.916667</td>\n",
       "      <td>927935.0</td>\n",
       "      <td>326.59</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Reliant Contract No Service Address  Meter No                    ESID  \\\n",
       "0              2059605   10518 BELLAIRE   303261  1008901000140050014100   \n",
       "1              2059605   10518 BELLAIRE   303261  1008901000140050014100   \n",
       "2              2059605   10518 BELLAIRE   303261  1008901000140050014100   \n",
       "3              2059605   10518 BELLAIRE   303261  1008901000140050014100   \n",
       "4              2059605   10518 BELLAIRE   303261  1008901000140050014100   \n",
       "\n",
       "   Business Area  Cost Center  Fund Bill Type  Bill Date  Read Date  \\\n",
       "0           2000   2000040005  8300         T 2013-04-29 2013-04-23   \n",
       "1           2000   2000040005  8300         T 2013-03-28 2013-03-21   \n",
       "2           2000   2000040005  8300         T 2013-02-26 2013-02-21   \n",
       "3           2000   2000040005  8300         T 2013-01-28 2013-01-22   \n",
       "4           2000   2000040005  8300         T 2012-12-28 2012-12-20   \n",
       "\n",
       "    Due Date  Meter Read  Base Cost ($)  T&D Discretionary ($)  \\\n",
       "0 2013-05-29     55598.0       67005.80                1357.92   \n",
       "1 2013-04-27     54767.0       57008.44                1300.83   \n",
       "2 2013-03-28     54059.0       61287.57                1313.49   \n",
       "3 2013-02-27     53299.0       64657.23                1369.20   \n",
       "4 2013-01-27     52491.0       60872.54                1612.61   \n",
       "\n",
       "   T&D Charges ($)  Current Due ($)  Adjustment ($)  Total Due ($)  \\\n",
       "0         10853.62         79309.40             NaN       79309.40   \n",
       "1         10473.16         68778.30             NaN       68778.30   \n",
       "2         10534.33         73135.18             NaN       73135.18   \n",
       "3         10878.65         77043.90             NaN       77043.90   \n",
       "4         10928.60         73740.34             NaN       73740.34   \n",
       "\n",
       "   Franchise Fee ($) Voucher Date  Billed Demand (KVA)  kWh Usage  \\\n",
       "0           -1036.92   2013-04-30          1502.083333   997407.0   \n",
       "1            -995.50   2013-03-29          1432.989691   849351.0   \n",
       "2           -1000.33   2013-02-27          1452.577320   911746.0   \n",
       "3           -1048.66   2013-01-29          1498.969072   969810.0   \n",
       "4           -1088.01   2012-12-31          1572.916667   927935.0   \n",
       "\n",
       "   Nodal Cu Charge ($)  Reliability Unit Charge ($)  \n",
       "0                92.06                          0.0  \n",
       "1                -4.13                          0.0  \n",
       "2                -0.21                          0.0  \n",
       "3               138.82                          0.0  \n",
       "4               326.59                          0.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9741435a-face-43a5-affa-17e61e573cd6",
   "metadata": {},
   "source": [
    "### Checking Nulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "67b172ad-5625-42aa-830a-0f55bd66c201",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Reliant Contract No                0\n",
       "Service Address                    0\n",
       "Meter No                       14049\n",
       "ESID                               0\n",
       "Business Area                      0\n",
       "Cost Center                        0\n",
       "Fund                               0\n",
       "Bill Type                          0\n",
       "Bill Date                          0\n",
       "Read Date                          0\n",
       "Due Date                           0\n",
       "Meter Read                        16\n",
       "Base Cost ($)                      0\n",
       "T&D Discretionary ($)              0\n",
       "T&D Charges ($)                    0\n",
       "Current Due ($)                    0\n",
       "Adjustment ($)                 64229\n",
       "Total Due ($)                      0\n",
       "Franchise Fee ($)                  0\n",
       "Voucher Date                       0\n",
       "Billed Demand (KVA)               16\n",
       "kWh Usage                          1\n",
       "Nodal Cu Charge ($)                4\n",
       "Reliability Unit Charge ($)       16\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b42d726-fdab-45ab-a35b-d33adb19cbce",
   "metadata": {},
   "source": [
    "### Checking Adjustment ($) column "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d1632b8-9dfd-4799-84a2-030371be3cc9",
   "metadata": {},
   "source": [
    "This column was named Index Charge in the other FY 2013 electricity usage data file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f68a0aff-69ca-4084-b0ac-973029ed4bec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NaN       64229\n",
       "0.0        1576\n",
       "9425.9        1\n",
       "Name: Adjustment ($), dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2['Adjustment ($)'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91a86e2f-5c39-45f1-a5c7-93eac227d8c7",
   "metadata": {},
   "source": [
    "The column does not have any relevant information based on the above reported values. Electing to drop the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "70c232d7-b775-4f47-bf02-082bd0bbf832",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2013_2.drop(columns=['Adjustment ($)'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6a0c1ba-4724-4594-acac-2cb825207eeb",
   "metadata": {},
   "source": [
    "### Checking Unique Number of Customers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4732ae10-e832-4780-b792-24ab483e7b27",
   "metadata": {},
   "source": [
    "There are quite a few columns in the dataset that signify relating to a unique person/house/business. Checking the unique counts of such columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7fcda4c3-5de1-4c17-bcd8-c36301aef571",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Unique Values in Reliant Contract No: 5786\n",
      "Number of Unique Values in Service Address : 5725\n",
      "Number of Unique Values in Meter No: 4035\n",
      "Number of Unique Values in ESID: 5783\n",
      "Number of Unique Values in Business Area: 9\n",
      "Number of Unique Values in Cost Center: 39\n"
     ]
    }
   ],
   "source": [
    "check_unique_columns = ['Reliant Contract No', 'Service Address ', 'Meter No', \n",
    "                        'ESID', 'Business Area', 'Cost Center',]\n",
    "\n",
    "for col in check_unique_columns:\n",
    "    print(f'Number of Unique Values in {col}: {data_2013_2[col].nunique()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cea9e1f-d8c5-4111-aa7f-366b1b9466a4",
   "metadata": {},
   "source": [
    "Based on the above reported values and further research online:\n",
    "\n",
    "ESID signifies a unique ID provided to each customer subscribed to the electricity board. It would be best to choose ESID and Service Address columns going forward as these would provide number of unique customers and the areas (streets) where higher usage of electricity occurs.\n",
    "\n",
    "Business Area signifies a grouping a number of buildings which covers a certain area. This would be useful usage patterns grouped by certain zones in the city."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a8da456-7de1-4b77-9f3d-b7131f770700",
   "metadata": {},
   "source": [
    "### Checking Bill Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "853e3f73-6d43-4201-ac83-75a442d340ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "T    65252\n",
       "P      552\n",
       "C        2\n",
       "Name: Bill Type, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2['Bill Type'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5fc078d-24c1-4c40-99ae-27d7e11604cd",
   "metadata": {},
   "source": [
    "Bill Type could signify the type of the connection given. Since commercial, residential and government spaces would have different type of pricing and needs this column could be capturing that information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "12b7dc33-f90d-4e86-95b6-f796588c34e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5725, 4035, 5783)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2['Service Address '].nunique(), data_2013_2['Meter No'].nunique(), data_2013_2['ESID'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8c7c8ba-f72e-4e12-b559-2b5531393b7d",
   "metadata": {},
   "source": [
    "The next 3 columns are: Bill Date, Read Date and Due Date. Of these it would be best to choose the Bill date across all the data files to keep the data consistent. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bbbe51d-ff44-42b2-ad29-032a3ef5bc92",
   "metadata": {},
   "source": [
    "### Electricity Usage Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "66b77726-75dc-4d8f-97ed-cf562290f79c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Meter Read</th>\n",
       "      <th>Billed Demand (KVA)</th>\n",
       "      <th>kWh Usage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>65790.000000</td>\n",
       "      <td>65790.000000</td>\n",
       "      <td>6.580500e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>9743.299217</td>\n",
       "      <td>45.011893</td>\n",
       "      <td>1.926132e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>17901.894291</td>\n",
       "      <td>382.634210</td>\n",
       "      <td>2.047392e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3004.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.410000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>8669.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>1.789000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>239800.000000</td>\n",
       "      <td>16775.903614</td>\n",
       "      <td>9.689658e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Meter Read  Billed Demand (KVA)     kWh Usage\n",
       "count   65790.000000         65790.000000  6.580500e+04\n",
       "mean     9743.299217            45.011893  1.926132e+04\n",
       "std     17901.894291           382.634210  2.047392e+05\n",
       "min         0.000000             0.000000  0.000000e+00\n",
       "25%         0.000000             0.000000  1.000000e+00\n",
       "50%      3004.500000             0.000000  2.410000e+02\n",
       "75%      8669.000000             9.000000  1.789000e+03\n",
       "max    239800.000000         16775.903614  9.689658e+06"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2[['Meter Read', 'Billed Demand (KVA)', 'kWh Usage']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52acf392-6f07-4fad-b9f3-edc9d39a40f4",
   "metadata": {},
   "source": [
    "There are 3 columns that denote the amount of electricity: Meter Read, Billed Demand, kWh Usage.\n",
    "\n",
    "Using kWh Usage as a standard unit of measurement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5cbba29e-5273-407f-8b65-ff612f618cba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Base Cost ($)</th>\n",
       "      <th>T&amp;D Discretionary ($)</th>\n",
       "      <th>T&amp;D Charges ($)</th>\n",
       "      <th>Current Due ($)</th>\n",
       "      <th>Total Due ($)</th>\n",
       "      <th>Franchise Fee ($)</th>\n",
       "      <th>Nodal Cu Charge ($)</th>\n",
       "      <th>Reliability Unit Charge ($)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65806.000000</td>\n",
       "      <td>65802.000000</td>\n",
       "      <td>65790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1286.967789</td>\n",
       "      <td>374.537243</td>\n",
       "      <td>283.605427</td>\n",
       "      <td>1950.938646</td>\n",
       "      <td>1951.890851</td>\n",
       "      <td>-34.636836</td>\n",
       "      <td>5.695807</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>13665.877497</td>\n",
       "      <td>11888.489071</td>\n",
       "      <td>1995.337024</td>\n",
       "      <td>21571.815431</td>\n",
       "      <td>21572.522026</td>\n",
       "      <td>241.014300</td>\n",
       "      <td>132.109391</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>-7091.410000</td>\n",
       "      <td>-37666.730000</td>\n",
       "      <td>-44264.860000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-7017.800000</td>\n",
       "      <td>-323.080000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.070000</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>6.020000</td>\n",
       "      <td>10.830000</td>\n",
       "      <td>11.020000</td>\n",
       "      <td>-5.520000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>16.070000</td>\n",
       "      <td>5.800000</td>\n",
       "      <td>11.140000</td>\n",
       "      <td>32.770000</td>\n",
       "      <td>32.650000</td>\n",
       "      <td>-0.460000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>119.257500</td>\n",
       "      <td>21.527500</td>\n",
       "      <td>81.792500</td>\n",
       "      <td>250.297500</td>\n",
       "      <td>247.592500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.140000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>650951.220000</td>\n",
       "      <td>756478.120000</td>\n",
       "      <td>69826.360000</td>\n",
       "      <td>907001.560000</td>\n",
       "      <td>907001.560000</td>\n",
       "      <td>84.910000</td>\n",
       "      <td>20461.930000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Base Cost ($)  T&D Discretionary ($)  T&D Charges ($)  Current Due ($)  \\\n",
       "count   65806.000000           65806.000000     65806.000000     65806.000000   \n",
       "mean     1286.967789             374.537243       283.605427      1950.938646   \n",
       "std     13665.877497           11888.489071      1995.337024     21571.815431   \n",
       "min         0.000000           -7091.410000    -37666.730000    -44264.860000   \n",
       "25%         0.070000               3.100000         6.020000        10.830000   \n",
       "50%        16.070000               5.800000        11.140000        32.770000   \n",
       "75%       119.257500              21.527500        81.792500       250.297500   \n",
       "max    650951.220000          756478.120000     69826.360000    907001.560000   \n",
       "\n",
       "       Total Due ($)  Franchise Fee ($)  Nodal Cu Charge ($)  \\\n",
       "count   65806.000000       65806.000000         65802.000000   \n",
       "mean     1951.890851         -34.636836             5.695807   \n",
       "std     21572.522026         241.014300           132.109391   \n",
       "min         0.000000       -7017.800000          -323.080000   \n",
       "25%        11.020000          -5.520000             0.000000   \n",
       "50%        32.650000          -0.460000             0.000000   \n",
       "75%       247.592500           0.000000             0.140000   \n",
       "max    907001.560000          84.910000         20461.930000   \n",
       "\n",
       "       Reliability Unit Charge ($)  \n",
       "count                      65790.0  \n",
       "mean                           0.0  \n",
       "std                            0.0  \n",
       "min                            0.0  \n",
       "25%                            0.0  \n",
       "50%                            0.0  \n",
       "75%                            0.0  \n",
       "max                            0.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2[[\n",
    "    'Base Cost ($)', 'T&D Discretionary ($)', 'T&D Charges ($)', \n",
    "    'Current Due ($)', 'Total Due ($)', 'Franchise Fee ($)', \n",
    "    'Nodal Cu Charge ($)', 'Reliability Unit Charge ($)'\n",
    "     ]].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5036fca5-a539-4ae5-9beb-399b9c27f31d",
   "metadata": {},
   "source": [
    "Reliability Unit Charge ($) does not contain any useful information. Electing to drop that column.\n",
    "\n",
    "The columns other than Current Due or Total Due are adding up the value present in these two columns. Going forward choosing the column Total Due ($). \n",
    "Based on the above statistics the columns Current Due and Total Due represent the same value. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7096d2b4-7176-443a-9fb5-6411301f91aa",
   "metadata": {},
   "source": [
    "Based on the above analysis of the dataset choosing the following columns:\n",
    "\n",
    "1. ESID\n",
    "2. Business Area\n",
    "3. Service Address \n",
    "3. Bill Type\n",
    "4. Bill Date\n",
    "5. Total Due ($)\n",
    "6. kWh Usage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a4bf337-206f-421d-8182-33551eaef746",
   "metadata": {},
   "source": [
    "### Selecting and Filtering Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "14736651-827e-4ba9-81f8-d27d151470bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2013_2 = data_2013_2[[\n",
    "    'ESID', 'Business Area', 'Service Address ', 'Bill Type',\n",
    "    'Bill Date', 'Total Due ($)', 'kWh Usage'\n",
    "]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1aadaecf-e3ed-411e-9381-cb74bab60b09",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "rename_cols = {\n",
    "    'ESID': 'esid',\n",
    "    'Business Area': 'business_area',\n",
    "    'Service Address ': 'service_address',\n",
    "    'Bill Type': 'bill_type',\n",
    "    'Bill Date': 'bill_date',\n",
    "    'Total Due ($)': 'total_due',\n",
    "    'kWh Usage': 'kwh_usage'\n",
    "}\n",
    "\n",
    "data_2013_2_main = data_2013_2.rename(columns=rename_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28d2033f-5e88-4d0c-83fb-eb91766472fa",
   "metadata": {},
   "source": [
    "Checking for Nulls again and dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "06384f99-c5fd-49a2-9405-ab67a971e848",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "esid               0\n",
       "business_area      0\n",
       "service_address    0\n",
       "bill_type          0\n",
       "bill_date          0\n",
       "total_due          0\n",
       "kwh_usage          1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2_main.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "89478527-c99e-497c-861a-37301aa9cad3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2013_2_main.dropna(subset=['kwh_usage'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c7f1ed63-160f-4338-9869-2abd15150fa5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "esid               0\n",
       "business_area      0\n",
       "service_address    0\n",
       "bill_type          0\n",
       "bill_date          0\n",
       "total_due          0\n",
       "kwh_usage          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2_main.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "84c75208-6ed3-4f6e-81af-1e78ddbb2cae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "esid                       object\n",
       "business_area               int64\n",
       "service_address            object\n",
       "bill_type                  object\n",
       "bill_date          datetime64[ns]\n",
       "total_due                 float64\n",
       "kwh_usage                 float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2_main.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1e628721-47f6-40d9-8989-e67d740a3e40",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65805, 7)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2_main.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0e750806-4634-440c-b71a-a243d4d32039",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_due</th>\n",
       "      <th>kwh_usage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.585927</td>\n",
       "      <td>4.777556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.097755</td>\n",
       "      <td>4.054406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.299719</td>\n",
       "      <td>4.359162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.480909</td>\n",
       "      <td>4.642764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.327772</td>\n",
       "      <td>4.438234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65801</th>\n",
       "      <td>-0.058835</td>\n",
       "      <td>-0.081652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65802</th>\n",
       "      <td>-0.059092</td>\n",
       "      <td>-0.081652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65803</th>\n",
       "      <td>-0.058462</td>\n",
       "      <td>-0.081652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65804</th>\n",
       "      <td>-0.058694</td>\n",
       "      <td>-0.081652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65805</th>\n",
       "      <td>-0.058593</td>\n",
       "      <td>-0.081652</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65805 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       total_due  kwh_usage\n",
       "0       3.585927   4.777556\n",
       "1       3.097755   4.054406\n",
       "2       3.299719   4.359162\n",
       "3       3.480909   4.642764\n",
       "4       3.327772   4.438234\n",
       "...          ...        ...\n",
       "65801  -0.058835  -0.081652\n",
       "65802  -0.059092  -0.081652\n",
       "65803  -0.058462  -0.081652\n",
       "65804  -0.058694  -0.081652\n",
       "65805  -0.058593  -0.081652\n",
       "\n",
       "[65805 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zscore_2013_2 = stats.zscore(data_2013_2_main[['total_due', 'kwh_usage']])\n",
    "\n",
    "zscore_2013_2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee73d0bb-2a05-41ea-896c-afff06e15b85",
   "metadata": {},
   "source": [
    "Each zscore value signifies how many standard deviations away an individual value is from the mean. This is a good indicator to finding outliers in the dataframe.\n",
    "\n",
    "Usually z-score=3 is considered as a cut-off value to set the limit. Therefore, any z-score greater than +3 or less than -3 is considered as outlier which is pretty much similar to standard deviation method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "371492bd-5d7e-4d86-874f-75b907b5234c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(65805, 7)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data_2013_2_main = data_2013_2_main[(np.abs(zscore_2013_2) < 3).all(axis=1)]\n",
    "\n",
    "data_2013_2_main.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f72f66e1-25cd-4b5d-b0b2-a5d6a0ee7c39",
   "metadata": {},
   "source": [
    "The number of rows has decreased from 65,805 to 65,388. So 417 rows were outliers based on the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ad477930-9a4e-4384-9e29-b8fc4d7be8b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>esid</th>\n",
       "      <th>business_area</th>\n",
       "      <th>service_address</th>\n",
       "      <th>bill_type</th>\n",
       "      <th>bill_date</th>\n",
       "      <th>total_due</th>\n",
       "      <th>kwh_usage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>79309.40</td>\n",
       "      <td>997407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-03-28</td>\n",
       "      <td>68778.30</td>\n",
       "      <td>849351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-02-26</td>\n",
       "      <td>73135.18</td>\n",
       "      <td>911746.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>T</td>\n",
       "      <td>2013-01-28</td>\n",
       "      <td>77043.90</td>\n",
       "      <td>969810.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1008901000140050014100</td>\n",
       "      <td>2000</td>\n",
       "      <td>10518 BELLAIRE</td>\n",
       "      <td>T</td>\n",
       "      <td>2012-12-28</td>\n",
       "      <td>73740.34</td>\n",
       "      <td>927935.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     esid  business_area service_address bill_type  bill_date  \\\n",
       "0  1008901000140050014100           2000  10518 BELLAIRE         T 2013-04-29   \n",
       "1  1008901000140050014100           2000  10518 BELLAIRE         T 2013-03-28   \n",
       "2  1008901000140050014100           2000  10518 BELLAIRE         T 2013-02-26   \n",
       "3  1008901000140050014100           2000  10518 BELLAIRE         T 2013-01-28   \n",
       "4  1008901000140050014100           2000  10518 BELLAIRE         T 2012-12-28   \n",
       "\n",
       "   total_due  kwh_usage  \n",
       "0   79309.40   997407.0  \n",
       "1   68778.30   849351.0  \n",
       "2   73135.18   911746.0  \n",
       "3   77043.90   969810.0  \n",
       "4   73740.34   927935.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_2_main.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ed455890-1830-474c-a7f4-6e73a489f4d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orig_shape_2013_2 - data_2013_2_main.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "dd1784df-e0fb-49b6-a94b-a66862708f8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2013_2_main.to_csv('electricity_usage_data_2013_2.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}