04-etl-traces.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our objective is to come up with final production/consumption traces that we will use in our simulations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us first import all the data at hand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv('04-traces-a.csv', na_values=['\\\\N', 'NaN'], dtype={'dataid': np.int64, 'gen': np.float64, 'grid': np.float64, 'use': np.float64})\n",
    "df2 = pd.read_csv('04-traces-b.csv', na_values=['\\\\N', 'NaN'], dtype={'dataid': np.int64, 'gen': np.float64, 'grid': np.float64, 'use': np.float64})\n",
    "df3 = pd.read_csv('04-traces-c.csv', na_values=['\\\\N', 'NaN'], dtype={'dataid': np.int64, 'gen': np.float64, 'grid': np.float64, 'use': np.float64})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge all dataframes into one, see: https://stackoverflow.com/a/21435402/2363529"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df1, df2, df3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7484542"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dataid           int64\n",
       "local_15min     object\n",
       "gen            float64\n",
       "grid           float64\n",
       "use            float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dataid                  int64\n",
       "local_15min    datetime64[ns]\n",
       "gen                   float64\n",
       "grid                  float64\n",
       "use                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['local_15min'] = pd.to_datetime(df['local_15min'])\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "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>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>171</td>\n",
       "      <td>2012-10-24 10:00:00</td>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>171</td>\n",
       "      <td>2012-10-24 10:15:00</td>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>171</td>\n",
       "      <td>2012-10-24 10:30:00</td>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>171</td>\n",
       "      <td>2012-10-24 10:45:00</td>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>171</td>\n",
       "      <td>2012-10-24 11:00:00</td>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dataid         local_15min       gen      grid       use\n",
       "0     171 2012-10-24 10:00:00  0.940400 -0.538867  0.401600\n",
       "1     171 2012-10-24 10:15:00  0.823000 -0.446400  0.376667\n",
       "2     171 2012-10-24 10:30:00  0.704200 -0.335400  0.368800\n",
       "3     171 2012-10-24 10:45:00  1.015467 -0.646200  0.369067\n",
       "4     171 2012-10-24 11:00:00  1.388600 -1.118867  0.269867"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the proper index, see: https://stackoverflow.com/a/35332222/2363529"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index(['dataid', 'local_15min'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use\n",
       "dataid local_15min                                      \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to figure out what to do with the `NaN` values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gen    NaN\n",
       "grid   NaN\n",
       "use    NaN\n",
       "Name: (187, 2014-06-12 00:45:00), dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[187, '2014-06-12 00:45:00']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72028"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[171].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us first identify how many rows we have per `dataid`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(171, Timestamp('2012-10-24 10:00:00'))"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.values[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Time for a set comprehension: https://github.com/jakevdp/WhirlwindTourOfPython/blob/6f1daf714fe52a8dde6a288674ba46a7feed8816/11-List-Comprehensions.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[9729, 5129, 9737, 2072, 545]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataids = {i[0] for i in list(df.index.values)}\n",
    "list(dataids)[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[72028, 72028, 72028, 72028, 72028]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataid_row_count = []\n",
    "for id in dataids:\n",
    "    dataid_row_count.append(df.loc[id].shape[0])\n",
    "dataid_row_count[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the frequency of each row count?\n",
    "\n",
    "See: https://stackoverflow.com/a/45268376/2363529"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "row_counts, freq_row_counts = np.unique(dataid_row_count, return_counts=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72028"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row_counts[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "freq_row_counts[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we have 74 data IDs with 72028 rows.\n",
    "\n",
    "That seems good enough, but we need to check for empty rows within those data IDs first."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which are the data IDs with 72028 rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[171, 370, 545, 585, 744]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataids_first_pass = sorted([id for id in dataids if (df.loc[id].shape[0] == row_counts[-1])])\n",
    "dataids_first_pass[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dataids_first_pass) == freq_row_counts[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many empty elements per data ID trace?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.count_nonzero(df.loc[dataids_first_pass[0]].isnull().values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{171: 0,\n",
       " 370: 0,\n",
       " 545: 0,\n",
       " 585: 0,\n",
       " 744: 0,\n",
       " 861: 0,\n",
       " 890: 0,\n",
       " 1103: 0,\n",
       " 1185: 10713,\n",
       " 1283: 0,\n",
       " 1642: 15,\n",
       " 1718: 0,\n",
       " 1792: 0,\n",
       " 2072: 0,\n",
       " 2233: 0,\n",
       " 2335: 72732,\n",
       " 2337: 0,\n",
       " 2470: 0,\n",
       " 2755: 0,\n",
       " 2818: 0,\n",
       " 2925: 0,\n",
       " 2945: 0,\n",
       " 2980: 0,\n",
       " 2986: 0,\n",
       " 3224: 0,\n",
       " 3456: 0,\n",
       " 3527: 0,\n",
       " 3544: 0,\n",
       " 3635: 0,\n",
       " 3719: 0,\n",
       " 3723: 0,\n",
       " 3918: 0,\n",
       " 3935: 0,\n",
       " 4193: 0,\n",
       " 4302: 0,\n",
       " 4357: 0,\n",
       " 4447: 0,\n",
       " 4526: 0,\n",
       " 4767: 45870,\n",
       " 5129: 0,\n",
       " 5246: 0,\n",
       " 5275: 0,\n",
       " 5357: 0,\n",
       " 5403: 216084,\n",
       " 5439: 0,\n",
       " 5615: 0,\n",
       " 5738: 0,\n",
       " 5785: 0,\n",
       " 5796: 0,\n",
       " 5817: 0,\n",
       " 5892: 0,\n",
       " 5972: 0,\n",
       " 6266: 0,\n",
       " 6423: 0,\n",
       " 6643: 0,\n",
       " 6990: 0,\n",
       " 7108: 0,\n",
       " 7731: 0,\n",
       " 7739: 0,\n",
       " 7767: 0,\n",
       " 7800: 159408,\n",
       " 7863: 0,\n",
       " 7989: 0,\n",
       " 8084: 0,\n",
       " 8155: 0,\n",
       " 8626: 0,\n",
       " 8669: 288,\n",
       " 8829: 0,\n",
       " 8890: 130540,\n",
       " 9019: 126,\n",
       " 9121: 0,\n",
       " 9631: 0,\n",
       " 9729: 156900,\n",
       " 9737: 138023}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "empty_elems = {id:np.count_nonzero(df.loc[id].isnull().values) for id in dataids_first_pass}\n",
    "empty_elems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems that we have enough data IDs without nil values in order to proceed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[171, 370, 545, 585, 744, 861, 890, 1103, 1283, 1718]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chosen_dataids = [id for id, count in empty_elems.items() if count == 0]\n",
    "chosen_dataids[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "63"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(chosen_dataids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the start date?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gen     0.940400\n",
       "grid   -0.538867\n",
       "use     0.401600\n",
       "Name: 2012-10-24 10:00:00, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[chosen_dataids[0]].iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the end date?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gen    -0.0050\n",
       "grid    0.5634\n",
       "use     0.5634\n",
       "Name: 2014-11-13 18:45:00, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[chosen_dataids[0]].iloc[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us first create a dataframe that only contains the `chosen_data_ids`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df.loc[chosen_dataids[0]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(72028, 3)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[chosen_dataids[0]].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gen     float64\n",
       "grid    float64\n",
       "use     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[chosen_dataids[0]].dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7484542, 3)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use\n",
       "dataid local_15min                                      \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>1.368733</td>\n",
       "      <td>-0.975733</td>\n",
       "      <td>0.393000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>2.002400</td>\n",
       "      <td>-1.699267</td>\n",
       "      <td>0.303267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>2.409667</td>\n",
       "      <td>-2.090067</td>\n",
       "      <td>0.319533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>2.168467</td>\n",
       "      <td>-1.846667</td>\n",
       "      <td>0.321733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.360133</td>\n",
       "      <td>-0.949867</td>\n",
       "      <td>0.410400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          gen      grid       use\n",
       "local_15min                                      \n",
       "2012-10-24 10:00:00  1.368733 -0.975733  0.393000\n",
       "2012-10-24 10:15:00  2.002400 -1.699267  0.303267\n",
       "2012-10-24 10:30:00  2.409667 -2.090067  0.319533\n",
       "2012-10-24 10:45:00  2.168467 -1.846667  0.321733\n",
       "2012-10-24 11:00:00  1.360133 -0.949867  0.410400"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1185].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We got:\n",
    "* the `dataids` set containing all data IDs in `df`, and\n",
    "* `chosen_data_ids`\n",
    "\n",
    "We wish to create `deleted_dataids`, the supplement of `chosen_data_ids`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "deleted_dataids = [id for id in dataids if id not in chosen_dataids]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(deleted_dataids) == len(dataids) - len(chosen_dataids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kchrist\\AppData\\Local\\Continuum\\miniconda3\\envs\\test\\lib\\site-packages\\pandas\\core\\generic.py:2530: PerformanceWarning: dropping on a non-lexsorted multi-index without a level parameter may impact performance.\n",
      "  obj = obj._drop_axis(labels, axis, level=level, errors=errors)\n"
     ]
    }
   ],
   "source": [
    "df.drop(deleted_dataids, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4537764, 3)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "remaining_dataids = {i[0] for i in list(df.index.values)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "63"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(remaining_dataids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have a dataframe with just the data IDs we are interested in.\n",
    "\n",
    "Let's us add the lower/upper limit market columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use\n",
       "dataid local_15min                                      \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[171, '2012-10-24 10:15:00'].name[1].year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Average annual real-time market prices for the South zone in ERCOT, see: https://www.potomaceconomics.com/wp-content/uploads/2017/06/2016-ERCOT-State-of-the-Market-Report.pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "lower_limit = {2012:2.8, 2013:3.4, 2014:4.2}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.8"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lower_limit[2012]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.8"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lower_limit[df.loc[171, '2012-10-24 10:15:00'].name[1].year]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def yearly_lower_limit(year):\n",
    "    return lower_limit[year]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.8"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yearly_lower_limit(df.loc[171, '2012-10-24 10:15:00'].name[1].year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['low_lim'] = df.apply(lambda x:yearly_lower_limit(x.name[1].year), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use  low_lim\n",
       "dataid local_15min                                               \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600      2.8\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667      2.8\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800      2.8\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067      2.8\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867      2.8"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">9631</th>\n",
       "      <th>2014-11-13 17:45:00</th>\n",
       "      <td>-0.025467</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:00:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:15:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:30:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:45:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use  low_lim\n",
       "dataid local_15min                                               \n",
       "9631   2014-11-13 17:45:00 -0.025467  0.538733  0.538733      4.2\n",
       "       2014-11-13 18:00:00 -0.007000  0.538133  0.538133      4.2\n",
       "       2014-11-13 18:15:00 -0.007000  0.497667  0.497667      4.2\n",
       "       2014-11-13 18:30:00 -0.007000  0.721733  0.721733      4.2\n",
       "       2014-11-13 18:45:00 -0.007000  0.784867  0.784867      4.2"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the upper limit CSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit_df = pd.read_csv('04-market-lim-up.csv', header=None, names= ['date', 'price (c/kWh)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>date</th>\n",
       "      <th>price (c/kWh)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10-Oct</td>\n",
       "      <td>11.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10-Nov</td>\n",
       "      <td>11.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10-Dec</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11-Jan</td>\n",
       "      <td>10.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-Feb</td>\n",
       "      <td>10.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     date  price (c/kWh)\n",
       "0  10-Oct          11.62\n",
       "1  10-Nov          11.32\n",
       "2  10-Dec          11.25\n",
       "3  11-Jan          10.70\n",
       "4  11-Feb          10.65"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date              object\n",
       "price (c/kWh)    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit_df['date'] = pd.to_datetime(upper_limit_df['date'], format='%y-%b')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>date</th>\n",
       "      <th>price (c/kWh)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-10-01</td>\n",
       "      <td>11.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-11-01</td>\n",
       "      <td>11.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-12-01</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>10.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>10.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  price (c/kWh)\n",
       "0 2010-10-01          11.62\n",
       "1 2010-11-01          11.32\n",
       "2 2010-12-01          11.25\n",
       "3 2011-01-01          10.70\n",
       "4 2011-02-01          10.65"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date             datetime64[ns]\n",
       "price (c/kWh)           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit_df.set_index('date', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>price (c/kWh)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-10-01</th>\n",
       "      <td>11.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-11-01</th>\n",
       "      <td>11.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-12-01</th>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-01-01</th>\n",
       "      <td>10.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02-01</th>\n",
       "      <td>10.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            price (c/kWh)\n",
       "date                     \n",
       "2010-10-01          11.62\n",
       "2010-11-01          11.32\n",
       "2010-12-01          11.25\n",
       "2011-01-01          10.70\n",
       "2011-02-01          10.65"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create `year` and `month` columns, see: https://stackoverflow.com/a/13652262/2363529"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011,\n",
       "            2011, 2011, 2011, 2011, 2012, 2012, 2012, 2012, 2012, 2012, 2012,\n",
       "            2012, 2012, 2012, 2012, 2012, 2013, 2013, 2013, 2013, 2013, 2013,\n",
       "            2013, 2013, 2013, 2013, 2013, 2013, 2014, 2014, 2014, 2014, 2014,\n",
       "            2014, 2014, 2014, 2014, 2014, 2014],\n",
       "           dtype='int64', name='date')"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.index.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit_df = upper_limit_df.assign(year=upper_limit_df.index.year, month=upper_limit_df.index.month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>price (c/kWh)</th>\n",
       "      <th>year</th>\n",
       "      <th>month</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>2010-10-01</th>\n",
       "      <td>11.62</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-11-01</th>\n",
       "      <td>11.32</td>\n",
       "      <td>2010</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-12-01</th>\n",
       "      <td>11.25</td>\n",
       "      <td>2010</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-01-01</th>\n",
       "      <td>10.70</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02-01</th>\n",
       "      <td>10.65</td>\n",
       "      <td>2011</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            price (c/kWh)  year  month\n",
       "date                                  \n",
       "2010-10-01          11.62  2010     10\n",
       "2010-11-01          11.32  2010     11\n",
       "2010-12-01          11.25  2010     12\n",
       "2011-01-01          10.70  2011      1\n",
       "2011-02-01          10.65  2011      2"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit_df.set_index(['year', 'month'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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></th>\n",
       "      <th>price (c/kWh)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2010</th>\n",
       "      <th>10</th>\n",
       "      <td>11.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2011</th>\n",
       "      <th>1</th>\n",
       "      <td>10.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            price (c/kWh)\n",
       "year month               \n",
       "2010 10             11.62\n",
       "     11             11.32\n",
       "     12             11.25\n",
       "2011 1              10.70\n",
       "     2              10.65"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11.62"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.loc[2010, 10].data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use  low_lim\n",
       "dataid local_15min                                               \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600      2.8\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667      2.8\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800      2.8\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067      2.8\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867      2.8"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['upper_lim'] = df.apply(lambda x:upper_limit_df.loc[x.name[1].year,x.name[1].month].data[0], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">171</th>\n",
       "      <th>2012-10-24 10:00:00</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:15:00</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:30:00</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 10:45:00</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-24 11:00:00</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use  low_lim  upper_lim\n",
       "dataid local_15min                                                          \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600      2.8      11.16\n",
       "       2012-10-24 10:15:00  0.823000 -0.446400  0.376667      2.8      11.16\n",
       "       2012-10-24 10:30:00  0.704200 -0.335400  0.368800      2.8      11.16\n",
       "       2012-10-24 10:45:00  1.015467 -0.646200  0.369067      2.8      11.16\n",
       "       2012-10-24 11:00:00  1.388600 -1.118867  0.269867      2.8      11.16"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price (c/kWh)    11.16\n",
       "Name: (2012, 10), dtype: float64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.loc[2012, 10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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></th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th>local_15min</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">9631</th>\n",
       "      <th>2014-11-13 17:45:00</th>\n",
       "      <td>-0.025467</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:00:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:15:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:30:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13 18:45:00</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 gen      grid       use  low_lim  upper_lim\n",
       "dataid local_15min                                                          \n",
       "9631   2014-11-13 17:45:00 -0.025467  0.538733  0.538733      4.2      11.98\n",
       "       2014-11-13 18:00:00 -0.007000  0.538133  0.538133      4.2      11.98\n",
       "       2014-11-13 18:15:00 -0.007000  0.497667  0.497667      4.2      11.98\n",
       "       2014-11-13 18:30:00 -0.007000  0.721733  0.721733      4.2      11.98\n",
       "       2014-11-13 18:45:00 -0.007000  0.784867  0.784867      4.2      11.98"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price (c/kWh)    11.98\n",
       "Name: (2014, 11), dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upper_limit_df.loc[2014, 11]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like we're done.\n",
    "\n",
    "Let's save three versions of this dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('04-final-trace-full.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lite = df.reset_index(level='local_15min', drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             gen      grid       use  low_lim  upper_lim\n",
       "dataid                                                  \n",
       "171     0.940400 -0.538867  0.401600      2.8      11.16\n",
       "171     0.823000 -0.446400  0.376667      2.8      11.16\n",
       "171     0.704200 -0.335400  0.368800      2.8      11.16\n",
       "171     1.015467 -0.646200  0.369067      2.8      11.16\n",
       "171     1.388600 -1.118867  0.269867      2.8      11.16"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lite.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>-0.025467</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>0.538733</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>0.538133</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>0.497667</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>0.721733</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>-0.007000</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>0.784867</td>\n",
       "      <td>4.2</td>\n",
       "      <td>11.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             gen      grid       use  low_lim  upper_lim\n",
       "dataid                                                  \n",
       "9631   -0.025467  0.538733  0.538733      4.2      11.98\n",
       "9631   -0.007000  0.538133  0.538133      4.2      11.98\n",
       "9631   -0.007000  0.497667  0.497667      4.2      11.98\n",
       "9631   -0.007000  0.721733  0.721733      4.2      11.98\n",
       "9631   -0.007000  0.784867  0.784867      4.2      11.98"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lite.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lite.to_csv('04-final-trace-lite.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year = df.reset_index(level='local_15min')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>local_15min</th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2012-10-24 10:00:00</td>\n",
       "      <td>0.940400</td>\n",
       "      <td>-0.538867</td>\n",
       "      <td>0.401600</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2012-10-24 10:15:00</td>\n",
       "      <td>0.823000</td>\n",
       "      <td>-0.446400</td>\n",
       "      <td>0.376667</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2012-10-24 10:30:00</td>\n",
       "      <td>0.704200</td>\n",
       "      <td>-0.335400</td>\n",
       "      <td>0.368800</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2012-10-24 10:45:00</td>\n",
       "      <td>1.015467</td>\n",
       "      <td>-0.646200</td>\n",
       "      <td>0.369067</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2012-10-24 11:00:00</td>\n",
       "      <td>1.388600</td>\n",
       "      <td>-1.118867</td>\n",
       "      <td>0.269867</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               local_15min       gen      grid       use  low_lim  upper_lim\n",
       "dataid                                                                      \n",
       "171    2012-10-24 10:00:00  0.940400 -0.538867  0.401600      2.8      11.16\n",
       "171    2012-10-24 10:15:00  0.823000 -0.446400  0.376667      2.8      11.16\n",
       "171    2012-10-24 10:30:00  0.704200 -0.335400  0.368800      2.8      11.16\n",
       "171    2012-10-24 10:45:00  1.015467 -0.646200  0.369067      2.8      11.16\n",
       "171    2012-10-24 11:00:00  1.388600 -1.118867  0.269867      2.8      11.16"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "local_15min    datetime64[ns]\n",
       "gen                   float64\n",
       "grid                  float64\n",
       "use                   float64\n",
       "low_lim               float64\n",
       "upper_lim             float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_year.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year = df_year[df_year['local_15min'].dt.year == int(2013)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>local_15min</th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2013-01-01 00:00:00</td>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.878267</td>\n",
       "      <td>0.878267</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2013-01-01 00:15:00</td>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.607600</td>\n",
       "      <td>0.607600</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2013-01-01 00:30:00</td>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.717600</td>\n",
       "      <td>0.717600</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2013-01-01 00:45:00</td>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.642333</td>\n",
       "      <td>0.642333</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2013-01-01 01:00:00</td>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.683000</td>\n",
       "      <td>0.683000</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               local_15min    gen      grid       use  low_lim  upper_lim\n",
       "dataid                                                                   \n",
       "171    2013-01-01 00:00:00 -0.005  0.878267  0.878267      3.4      10.84\n",
       "171    2013-01-01 00:15:00 -0.005  0.607600  0.607600      3.4      10.84\n",
       "171    2013-01-01 00:30:00 -0.005  0.717600  0.717600      3.4      10.84\n",
       "171    2013-01-01 00:45:00 -0.005  0.642333  0.642333      3.4      10.84\n",
       "171    2013-01-01 01:00:00 -0.005  0.683000  0.683000      3.4      10.84"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>local_15min</th>\n",
       "      <th>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>2013-12-31 22:45:00</td>\n",
       "      <td>-0.0060</td>\n",
       "      <td>0.814000</td>\n",
       "      <td>0.814000</td>\n",
       "      <td>3.4</td>\n",
       "      <td>11.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>2013-12-31 23:00:00</td>\n",
       "      <td>-0.0060</td>\n",
       "      <td>0.641333</td>\n",
       "      <td>0.641333</td>\n",
       "      <td>3.4</td>\n",
       "      <td>11.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>2013-12-31 23:15:00</td>\n",
       "      <td>-0.0060</td>\n",
       "      <td>0.473400</td>\n",
       "      <td>0.473400</td>\n",
       "      <td>3.4</td>\n",
       "      <td>11.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>2013-12-31 23:30:00</td>\n",
       "      <td>-0.0060</td>\n",
       "      <td>0.682800</td>\n",
       "      <td>0.682800</td>\n",
       "      <td>3.4</td>\n",
       "      <td>11.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>2013-12-31 23:45:00</td>\n",
       "      <td>-0.0066</td>\n",
       "      <td>1.283200</td>\n",
       "      <td>1.283200</td>\n",
       "      <td>3.4</td>\n",
       "      <td>11.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               local_15min     gen      grid       use  low_lim  upper_lim\n",
       "dataid                                                                    \n",
       "9631   2013-12-31 22:45:00 -0.0060  0.814000  0.814000      3.4      11.27\n",
       "9631   2013-12-31 23:00:00 -0.0060  0.641333  0.641333      3.4      11.27\n",
       "9631   2013-12-31 23:15:00 -0.0060  0.473400  0.473400      3.4      11.27\n",
       "9631   2013-12-31 23:30:00 -0.0060  0.682800  0.682800      3.4      11.27\n",
       "9631   2013-12-31 23:45:00 -0.0066  1.283200  1.283200      3.4      11.27"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_year.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year.drop(columns=['local_15min'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>gen</th>\n",
       "      <th>grid</th>\n",
       "      <th>use</th>\n",
       "      <th>low_lim</th>\n",
       "      <th>upper_lim</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dataid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.878267</td>\n",
       "      <td>0.878267</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.607600</td>\n",
       "      <td>0.607600</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.717600</td>\n",
       "      <td>0.717600</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.642333</td>\n",
       "      <td>0.642333</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>-0.005</td>\n",
       "      <td>0.683000</td>\n",
       "      <td>0.683000</td>\n",
       "      <td>3.4</td>\n",
       "      <td>10.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          gen      grid       use  low_lim  upper_lim\n",
       "dataid                                               \n",
       "171    -0.005  0.878267  0.878267      3.4      10.84\n",
       "171    -0.005  0.607600  0.607600      3.4      10.84\n",
       "171    -0.005  0.717600  0.717600      3.4      10.84\n",
       "171    -0.005  0.642333  0.642333      3.4      10.84\n",
       "171    -0.005  0.683000  0.683000      3.4      10.84"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_year.to_csv('04-final-trace-2013.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}