data_cleaning_energy_pre_2015.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 8216\r\n",
      "-rw-r--r--@ 1 ns  staff  276509 29 Aug 14:55 SYSTEM_VERTICAL_LOAD_201301010000-201401010000_ES.xlsx\r\n",
      "-rw-r--r--@ 1 ns  staff  273110 29 Aug 14:54 SYSTEM_VERTICAL_LOAD_201401010000-201501010000_ES.xlsx\r\n",
      "-rw-r--r--@ 1 ns  staff  180613 29 Aug 14:54 SYSTEM_VERTICAL_LOAD_201501010000-201601010000_ES.xlsx\r\n",
      "-rw-r--r--@ 1 ns  staff  473123 18 Aug 20:03 Total Load - Day Ahead _ Actual_2015.csv\r\n",
      "-rw-r--r--@ 1 ns  staff  474463 18 Aug 20:03 Total Load - Day Ahead _ Actual_2016.csv\r\n",
      "-rw-r--r--@ 1 ns  staff  473173 18 Aug 20:03 Total Load - Day Ahead _ Actual_2017.csv\r\n",
      "-rw-r--r--@ 1 ns  staff  473173 18 Aug 20:00 Total Load - Day Ahead _ Actual_2018.csv\r\n",
      "-rw-r--r--@ 1 ns  staff  447309 18 Aug 20:00 Total Load - Day Ahead _ Actual_2019.csv\r\n",
      "-rw-r--r--  1 ns  staff  946953 20 Aug 19:42 load_forecast_2016_2018.csv\r\n"
     ]
    }
   ],
   "source": [
    "!ls -l ./data/raw_data_ES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_excel('./data/raw_data_ES/SYSTEM_VERTICAL_LOAD_201501010000-201601010000_ES.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.drop(['Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.columns = ['time', 'day_ahead_load', 'actual_load']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>time</th>\n",
       "      <th>day_ahead_load</th>\n",
       "      <th>actual_load</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01.01.2015 00:00 - 01.01.2016 00:00 - CET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>ES</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Time Interval</td>\n",
       "      <td>Day-ahead Vertical Load</td>\n",
       "      <td>Realised Vertical Load</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>[MW]</td>\n",
       "      <td>[MW]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>00:00 - 01:00</td>\n",
       "      <td>26118</td>\n",
       "      <td>25393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>01:00 - 02:00</td>\n",
       "      <td>24934</td>\n",
       "      <td>24406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>02:00 - 03:00</td>\n",
       "      <td>23515</td>\n",
       "      <td>22763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>03:00 - 04:00</td>\n",
       "      <td>22642</td>\n",
       "      <td>21301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>04:00 - 05:00</td>\n",
       "      <td>21785</td>\n",
       "      <td>20274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>05:00 - 06:00</td>\n",
       "      <td>21441</td>\n",
       "      <td>19912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>06:00 - 07:00</td>\n",
       "      <td>21285</td>\n",
       "      <td>20010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>07:00 - 08:00</td>\n",
       "      <td>21545</td>\n",
       "      <td>20372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>08:00 - 09:00</td>\n",
       "      <td>21443</td>\n",
       "      <td>20100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>09:00 - 10:00</td>\n",
       "      <td>21560</td>\n",
       "      <td>20616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10:00 - 11:00</td>\n",
       "      <td>22824</td>\n",
       "      <td>22226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11:00 - 12:00</td>\n",
       "      <td>23720</td>\n",
       "      <td>23529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>12:00 - 13:00</td>\n",
       "      <td>24180</td>\n",
       "      <td>24124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>13:00 - 14:00</td>\n",
       "      <td>24797</td>\n",
       "      <td>24701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>14:00 - 15:00</td>\n",
       "      <td>25222</td>\n",
       "      <td>24682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>15:00 - 16:00</td>\n",
       "      <td>24173</td>\n",
       "      <td>23542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>16:00 - 17:00</td>\n",
       "      <td>23659</td>\n",
       "      <td>23121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>17:00 - 18:00</td>\n",
       "      <td>23982</td>\n",
       "      <td>23580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>18:00 - 19:00</td>\n",
       "      <td>26981</td>\n",
       "      <td>26401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>19:00 - 20:00</td>\n",
       "      <td>28515</td>\n",
       "      <td>28009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20:00 - 21:00</td>\n",
       "      <td>30482</td>\n",
       "      <td>28998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>21:00 - 22:00</td>\n",
       "      <td>30739</td>\n",
       "      <td>29572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>22:00 - 23:00</td>\n",
       "      <td>29756</td>\n",
       "      <td>29053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>23:00 - 00:00</td>\n",
       "      <td>27589</td>\n",
       "      <td>26838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>02.01.2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>00:00 - 01:00</td>\n",
       "      <td>23959</td>\n",
       "      <td>24336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9798</th>\n",
       "      <td>19:00 - 20:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9799</th>\n",
       "      <td>20:00 - 21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9800</th>\n",
       "      <td>21:00 - 22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9801</th>\n",
       "      <td>22:00 - 23:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9802</th>\n",
       "      <td>23:00 - 00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9804</th>\n",
       "      <td>30.12.2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9806</th>\n",
       "      <td>00:00 - 01:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9807</th>\n",
       "      <td>01:00 - 02:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9808</th>\n",
       "      <td>02:00 - 03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9809</th>\n",
       "      <td>03:00 - 04:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9810</th>\n",
       "      <td>04:00 - 05:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9811</th>\n",
       "      <td>05:00 - 06:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9812</th>\n",
       "      <td>06:00 - 07:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9813</th>\n",
       "      <td>07:00 - 08:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9814</th>\n",
       "      <td>08:00 - 09:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9815</th>\n",
       "      <td>09:00 - 10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9816</th>\n",
       "      <td>10:00 - 11:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9817</th>\n",
       "      <td>11:00 - 12:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9818</th>\n",
       "      <td>12:00 - 13:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9819</th>\n",
       "      <td>13:00 - 14:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9820</th>\n",
       "      <td>14:00 - 15:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9821</th>\n",
       "      <td>15:00 - 16:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9822</th>\n",
       "      <td>16:00 - 17:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9823</th>\n",
       "      <td>17:00 - 18:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9824</th>\n",
       "      <td>18:00 - 19:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9825</th>\n",
       "      <td>19:00 - 20:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9826</th>\n",
       "      <td>20:00 - 21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9827</th>\n",
       "      <td>21:00 - 22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9828</th>\n",
       "      <td>22:00 - 23:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9829</th>\n",
       "      <td>23:00 - 00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9103 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           time           day_ahead_load  \\\n",
       "0     01.01.2015 00:00 - 01.01.2016 00:00 - CET                      NaN   \n",
       "2                                           NaN                       ES   \n",
       "3                                 Time Interval  Day-ahead Vertical Load   \n",
       "4                                           NaN                     [MW]   \n",
       "5                                 00:00 - 01:00                    26118   \n",
       "6                                 01:00 - 02:00                    24934   \n",
       "7                                 02:00 - 03:00                    23515   \n",
       "8                                 03:00 - 04:00                    22642   \n",
       "9                                 04:00 - 05:00                    21785   \n",
       "10                                05:00 - 06:00                    21441   \n",
       "11                                06:00 - 07:00                    21285   \n",
       "12                                07:00 - 08:00                    21545   \n",
       "13                                08:00 - 09:00                    21443   \n",
       "14                                09:00 - 10:00                    21560   \n",
       "15                                10:00 - 11:00                    22824   \n",
       "16                                11:00 - 12:00                    23720   \n",
       "17                                12:00 - 13:00                    24180   \n",
       "18                                13:00 - 14:00                    24797   \n",
       "19                                14:00 - 15:00                    25222   \n",
       "20                                15:00 - 16:00                    24173   \n",
       "21                                16:00 - 17:00                    23659   \n",
       "22                                17:00 - 18:00                    23982   \n",
       "23                                18:00 - 19:00                    26981   \n",
       "24                                19:00 - 20:00                    28515   \n",
       "25                                20:00 - 21:00                    30482   \n",
       "26                                21:00 - 22:00                    30739   \n",
       "27                                22:00 - 23:00                    29756   \n",
       "28                                23:00 - 00:00                    27589   \n",
       "30                                   02.01.2015                      NaN   \n",
       "32                                00:00 - 01:00                    23959   \n",
       "...                                         ...                      ...   \n",
       "9798                              19:00 - 20:00                      NaN   \n",
       "9799                              20:00 - 21:00                      NaN   \n",
       "9800                              21:00 - 22:00                      NaN   \n",
       "9801                              22:00 - 23:00                      NaN   \n",
       "9802                              23:00 - 00:00                      NaN   \n",
       "9804                                 30.12.2015                      NaN   \n",
       "9806                              00:00 - 01:00                      NaN   \n",
       "9807                              01:00 - 02:00                      NaN   \n",
       "9808                              02:00 - 03:00                      NaN   \n",
       "9809                              03:00 - 04:00                      NaN   \n",
       "9810                              04:00 - 05:00                      NaN   \n",
       "9811                              05:00 - 06:00                      NaN   \n",
       "9812                              06:00 - 07:00                      NaN   \n",
       "9813                              07:00 - 08:00                      NaN   \n",
       "9814                              08:00 - 09:00                      NaN   \n",
       "9815                              09:00 - 10:00                      NaN   \n",
       "9816                              10:00 - 11:00                      NaN   \n",
       "9817                              11:00 - 12:00                      NaN   \n",
       "9818                              12:00 - 13:00                      NaN   \n",
       "9819                              13:00 - 14:00                      NaN   \n",
       "9820                              14:00 - 15:00                      NaN   \n",
       "9821                              15:00 - 16:00                      NaN   \n",
       "9822                              16:00 - 17:00                      NaN   \n",
       "9823                              17:00 - 18:00                      NaN   \n",
       "9824                              18:00 - 19:00                      NaN   \n",
       "9825                              19:00 - 20:00                      NaN   \n",
       "9826                              20:00 - 21:00                      NaN   \n",
       "9827                              21:00 - 22:00                      NaN   \n",
       "9828                              22:00 - 23:00                      NaN   \n",
       "9829                              23:00 - 00:00                      NaN   \n",
       "\n",
       "                 actual_load  \n",
       "0                        NaN  \n",
       "2                        NaN  \n",
       "3     Realised Vertical Load  \n",
       "4                       [MW]  \n",
       "5                      25393  \n",
       "6                      24406  \n",
       "7                      22763  \n",
       "8                      21301  \n",
       "9                      20274  \n",
       "10                     19912  \n",
       "11                     20010  \n",
       "12                     20372  \n",
       "13                     20100  \n",
       "14                     20616  \n",
       "15                     22226  \n",
       "16                     23529  \n",
       "17                     24124  \n",
       "18                     24701  \n",
       "19                     24682  \n",
       "20                     23542  \n",
       "21                     23121  \n",
       "22                     23580  \n",
       "23                     26401  \n",
       "24                     28009  \n",
       "25                     28998  \n",
       "26                     29572  \n",
       "27                     29053  \n",
       "28                     26838  \n",
       "30                       NaN  \n",
       "32                     24336  \n",
       "...                      ...  \n",
       "9798                     NaN  \n",
       "9799                     NaN  \n",
       "9800                     NaN  \n",
       "9801                     NaN  \n",
       "9802                     NaN  \n",
       "9804                     NaN  \n",
       "9806                     NaN  \n",
       "9807                     NaN  \n",
       "9808                     NaN  \n",
       "9809                     NaN  \n",
       "9810                     NaN  \n",
       "9811                     NaN  \n",
       "9812                     NaN  \n",
       "9813                     NaN  \n",
       "9814                     NaN  \n",
       "9815                     NaN  \n",
       "9816                     NaN  \n",
       "9817                     NaN  \n",
       "9818                     NaN  \n",
       "9819                     NaN  \n",
       "9820                     NaN  \n",
       "9821                     NaN  \n",
       "9822                     NaN  \n",
       "9823                     NaN  \n",
       "9824                     NaN  \n",
       "9825                     NaN  \n",
       "9826                     NaN  \n",
       "9827                     NaN  \n",
       "9828                     NaN  \n",
       "9829                     NaN  \n",
       "\n",
       "[9103 rows x 3 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dropna(axis=0, how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}