processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data processing for FlexSUS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "institution: DTU\n",
    "\n",
    "author: [tilseb](mailto:tilseb@dtu.dk)\n",
    "\n",
    "date created: 2020-01-30\n",
    "\n",
    "licensed under: [GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007](https://www.gnu.org/licenses/gpl-3.0.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Installation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Installation via miniconda. Get miniconda with Python 3.7 [here](https://docs.conda.io/en/latest/miniconda.html).\n",
    "2. Open the anaconda promt: press `start button` , type `anaconda promt` and hit enter.\n",
    "3. Navigate to the folder containing this script: `cd <path_to_folder>`\n",
    "3. Use the requirements.yml file in the root directory to set up the environment: `conda env create -f requirements.yml`\n",
    "5. Activate the flexus environment: `conda activate flexsus`\n",
    "5. Open the notebook: `jupyter notebook`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Script set-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make time string current time\n",
    "timestr = time.strftime(\"%Y%m%d%H%M\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make directories\n",
    "dirs = {'output'}\n",
    "for i in dirs:\n",
    "    if not os.path.isdir(i):\n",
    "        os.mkdir(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define output resolution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set of considered countries (if empty, select all)\n",
    "ccc = {'DENMARK','NORWAY'}\n",
    "\n",
    "# set of considered regions (if empty, select all)\n",
    "rrr = {'DK1', 'DK2', 'NO1', 'NO2', 'NO3', 'NO4', 'NO5'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set of years (if empty, select all)\n",
    "yyy = {}  # 2025, 2035, 2045"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load and process functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def readSmallData(f):\n",
    "    return pd.read_csv('data/' + f + '.csv', encoding='utf8', engine='c', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def readData(f):\n",
    "    tmp = []\n",
    "    for chunk in pd.read_csv('data/' + f + '.csv', encoding='utf8', engine='c', low_memory=False,\n",
    "                             chunksize=500000):\n",
    "        tmp.append(chunk)\n",
    "    return pd.concat(tmp, axis=0)\n",
    "    del tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def epsToZeros(df):\n",
    "    df = df.replace('Eps', 0)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filterYearAndCountry(df,y,c):\n",
    "    if y: df = df.loc[~df.Dim3.isin(set(df.Dim3).difference(y)), :]\n",
    "    if c: df = df.loc[~df.Dim4.isin(set(df.Dim4).difference(c)), :]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeValFloat(df):\n",
    "    df.Val = df.Val.astype(float)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def groupFrame(df,i,f,aver):\n",
    "    if aver:\n",
    "        df = df.groupby([i,'Dim2'])['Val'].mean().unstack().T\n",
    "        df.columns = pd.MultiIndex.from_product([[f + ' (mean)'], df.columns])\n",
    "    else:\n",
    "        df = df.groupby([i,'Dim2'])['Val'].sum().unstack().T\n",
    "        df.columns = pd.MultiIndex.from_product([[f + ' (sum)'], df.columns])\n",
    "    df = df.fillna(0)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def groupFrame2(df,f,aver):\n",
    "    if aver:\n",
    "        df = df.groupby(['ctr','Dim2'])['Val'].mean().unstack().T\n",
    "        df.columns = pd.MultiIndex.from_product([[f + ' (mean)'], df.columns])\n",
    "    else:\n",
    "        df = df.groupby(['ctr','Dim2'])['Val'].sum().unstack().T\n",
    "        df.columns = pd.MultiIndex.from_product([[f + ' (sum)'], df.columns])\n",
    "    df = df.fillna(0)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Auxils "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>group_criteria</th>\n",
       "      <th>group_ccc</th>\n",
       "      <th>aver</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>file</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ECONOMY_ELEC_TRANSMISSION</th>\n",
       "      <td>Dim7</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ECONOMY_GENERATION</th>\n",
       "      <td>Dim11</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ECONOMY_HEAT_TRANSMISSION</th>\n",
       "      <td>Dim8</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ELEC_DEMAND</th>\n",
       "      <td>Dim6</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ELEC_PRICE</th>\n",
       "      <td>Dim7</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          group_criteria group_ccc aver\n",
       "file                                                   \n",
       "ECONOMY_ELEC_TRANSMISSION           Dim7       yes   no\n",
       "ECONOMY_GENERATION                 Dim11       yes   no\n",
       "ECONOMY_HEAT_TRANSMISSION           Dim8       yes   no\n",
       "ELEC_DEMAND                         Dim6       yes   no\n",
       "ELEC_PRICE                          Dim7       yes  yes"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load variable criteria to be grouped against\n",
    "ctr = pd.read_csv('auxils/criteria.csv', encoding='utf8', index_col=0)\n",
    "ctr.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ECONOMY_ELEC_TRANSMISSION', 'ECONOMY_GENERATION', 'ECONOMY_HEAT_TRANSMISSION', 'ELEC_DEMAND', 'ELEC_PRICE', 'ELEC_PRICE_HOURLY', 'ELEC_TRANSMISSION_CAPACITY', 'ELEC_TRANSMISSION_FLOW', 'EMISSIONS_CO2', 'ENERGY_PRODUCTION', 'FUEL_CONSUMPTION', 'GENERATION_CAPACITY', 'HEAT_DEMAND', 'HEAT_PRICE', 'HEAT_PRICE_HOURLY', 'HEAT_TRANSMISSION_CAPACITY', 'HEAT_TRANSMISSION_FLOW', 'STORAGE_CAPACITY', 'SYSTEM_COSTS']\n"
     ]
    }
   ],
   "source": [
    "# get list of data files (w/o file extension, i.e. '.csv')\n",
    "lf = [i.split('.')[0] for i in os.listdir('data')]\n",
    "\n",
    "# get list of data files (w/ file extension, i.e. '.csv')\n",
    "#lf = os.listdir('data')\n",
    "\n",
    "# list files\n",
    "print(lf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare ctr and lf for consitency\n",
    "for diff in set(ctr.index).difference(set(lf)):\n",
    "    print(str(diff) + ' is not defined in the criteria.csv file.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implicit processing version"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">ECONOMY_ELEC_TRANSMISSION (sum)</th>\n",
       "      <th colspan=\"6\" halign=\"left\">ECONOMY_GENERATION (sum)</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">SYSTEM_COSTS (sum)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>TRANSMISSION_CAPITAL_COSTS</th>\n",
       "      <th>TRANSMISSION_OPERATIONAL_COSTS</th>\n",
       "      <th>TRANSMISSION_TRADE_COSTS</th>\n",
       "      <th>TRANSMISSION_TRADE_INCOME</th>\n",
       "      <th>ELECTRICITY_SALE</th>\n",
       "      <th>ENERGY_SPECIFIC_REVENUE</th>\n",
       "      <th>GENERATION_CAPITAL_COSTS</th>\n",
       "      <th>GENERATION_CO2_TAX</th>\n",
       "      <th>GENERATION_FIXED_COSTS</th>\n",
       "      <th>GENERATION_FUEL_COSTS</th>\n",
       "      <th>...</th>\n",
       "      <th>GENERATION_OPERATIONAL_COSTS</th>\n",
       "      <th>GENERATION_OTHER_EMI_TAX</th>\n",
       "      <th>GENERATION_UC_COSTS</th>\n",
       "      <th>GRID_TARIFFS</th>\n",
       "      <th>HEAT_TRANSMISSION_CAPITAL_COSTS</th>\n",
       "      <th>HEAT_TRANSMISSION_OPERATIONAL_COSTS</th>\n",
       "      <th>HYDRO_PROFILE</th>\n",
       "      <th>TAXES</th>\n",
       "      <th>TRANSMISSION_CAPITAL_COSTS</th>\n",
       "      <th>TRANSMISSION_OPERATIONAL_COSTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Scenario1</th>\n",
       "      <td>1657.657335</td>\n",
       "      <td>0.065144</td>\n",
       "      <td>8525.846009</td>\n",
       "      <td>33740.028628</td>\n",
       "      <td>44091.194561</td>\n",
       "      <td>44814.327069</td>\n",
       "      <td>12342.189366</td>\n",
       "      <td>231.548661</td>\n",
       "      <td>6357.047931</td>\n",
       "      <td>3984.628893</td>\n",
       "      <td>...</td>\n",
       "      <td>2662.357902</td>\n",
       "      <td>0.020172</td>\n",
       "      <td>144.105256</td>\n",
       "      <td>9975.205058</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000308</td>\n",
       "      <td>-126.858740</td>\n",
       "      <td>12776.638785</td>\n",
       "      <td>1657.657335</td>\n",
       "      <td>0.065144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scenario10</th>\n",
       "      <td>1531.774992</td>\n",
       "      <td>0.054520</td>\n",
       "      <td>8968.415670</td>\n",
       "      <td>29660.490472</td>\n",
       "      <td>37248.493363</td>\n",
       "      <td>42697.123825</td>\n",
       "      <td>9017.136737</td>\n",
       "      <td>355.756872</td>\n",
       "      <td>4834.376680</td>\n",
       "      <td>2311.256359</td>\n",
       "      <td>...</td>\n",
       "      <td>2478.124542</td>\n",
       "      <td>0.012300</td>\n",
       "      <td>159.391437</td>\n",
       "      <td>10162.589376</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000601</td>\n",
       "      <td>-140.707859</td>\n",
       "      <td>14659.872827</td>\n",
       "      <td>1531.774992</td>\n",
       "      <td>0.054520</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 344 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           ECONOMY_ELEC_TRANSMISSION (sum)                                 \\\n",
       "                TRANSMISSION_CAPITAL_COSTS TRANSMISSION_OPERATIONAL_COSTS   \n",
       "Scenario1                      1657.657335                       0.065144   \n",
       "Scenario10                     1531.774992                       0.054520   \n",
       "\n",
       "                                                               \\\n",
       "           TRANSMISSION_TRADE_COSTS TRANSMISSION_TRADE_INCOME   \n",
       "Scenario1               8525.846009              33740.028628   \n",
       "Scenario10              8968.415670              29660.490472   \n",
       "\n",
       "           ECONOMY_GENERATION (sum)                          \\\n",
       "                   ELECTRICITY_SALE ENERGY_SPECIFIC_REVENUE   \n",
       "Scenario1              44091.194561            44814.327069   \n",
       "Scenario10             37248.493363            42697.123825   \n",
       "\n",
       "                                                                               \\\n",
       "           GENERATION_CAPITAL_COSTS GENERATION_CO2_TAX GENERATION_FIXED_COSTS   \n",
       "Scenario1              12342.189366         231.548661            6357.047931   \n",
       "Scenario10              9017.136737         355.756872            4834.376680   \n",
       "\n",
       "                                  ...           SYSTEM_COSTS (sum)  \\\n",
       "           GENERATION_FUEL_COSTS  ... GENERATION_OPERATIONAL_COSTS   \n",
       "Scenario1            3984.628893  ...                  2662.357902   \n",
       "Scenario10           2311.256359  ...                  2478.124542   \n",
       "\n",
       "                                                                       \\\n",
       "           GENERATION_OTHER_EMI_TAX GENERATION_UC_COSTS  GRID_TARIFFS   \n",
       "Scenario1                  0.020172          144.105256   9975.205058   \n",
       "Scenario10                 0.012300          159.391437  10162.589376   \n",
       "\n",
       "                                            \\\n",
       "           HEAT_TRANSMISSION_CAPITAL_COSTS   \n",
       "Scenario1                              0.0   \n",
       "Scenario10                             0.0   \n",
       "\n",
       "                                                                            \\\n",
       "           HEAT_TRANSMISSION_OPERATIONAL_COSTS HYDRO_PROFILE         TAXES   \n",
       "Scenario1                             0.000308   -126.858740  12776.638785   \n",
       "Scenario10                            0.000601   -140.707859  14659.872827   \n",
       "\n",
       "                                                                      \n",
       "           TRANSMISSION_CAPITAL_COSTS TRANSMISSION_OPERATIONAL_COSTS  \n",
       "Scenario1                 1657.657335                       0.065144  \n",
       "Scenario10                1531.774992                       0.054520  \n",
       "\n",
       "[2 rows x 344 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make a data frame for all processed data\n",
    "df_all = pd.DataFrame()\n",
    "\n",
    "for f in lf:\n",
    "    # read the data for each file into a temporary data frame\n",
    "    df = readData(f)\n",
    "    # convert any Eps (GAMS specific notation) to zeros\n",
    "    df = epsToZeros(df)\n",
    "    # convert all data in the 'val' column to floats\n",
    "    df = makeValFloat(df)\n",
    "    \n",
    "    # select the columns (dimensinos) to group the data against as defined in the criteria.csv file\n",
    "    dim = ctr.loc[f, 'group_criteria']\n",
    "    # if more than one dimesion shall be used, they are listed in one cell and seperated by '&'\n",
    "    if '&' in dim:\n",
    "        # select the first dimension and save it in a new column ('ctr')\n",
    "        df['ctr'] = df[dim.split('&')[0]]\n",
    "        # add the other dimensions subsequently to the 'ctr column'\n",
    "        for i in dim.split('&')[1:]:\n",
    "            df['ctr'] += '-' + df[i]\n",
    "    else:\n",
    "        # if only one dimensino is specified, save it in a new column ('ctr')\n",
    "        df['ctr'] = df[dim]\n",
    "    \n",
    "    # group only by the specified countries, if indicated in the criteria.csv file, else keep all\n",
    "    if ctr.loc[f, 'group_ccc'] == 'yes':\n",
    "        df = filterYearAndCountry(df,yyy,ccc)\n",
    "    else:\n",
    "        df = filterYearAndCountry(df,yyy,{})\n",
    "    \n",
    "    # calculate the averages for each group (mean), if indicated in the criteria.csv file, else do the sum (default)\n",
    "    if ctr.loc[f, 'aver'] == 'yes':\n",
    "        aver = True\n",
    "    else:\n",
    "        aver = False\n",
    "    \n",
    "    # call the group function\n",
    "    df = groupFrame2(df,f,aver)\n",
    "    # add the grouped data to the data frame that holds all grouped data\n",
    "    df_all = pd.concat([df_all, df], axis=1, sort=False)\n",
    "\n",
    "# delete naming of index and column\n",
    "df_all.index.set_names([None], inplace=True)\n",
    "df_all.columns.set_names([None, None], inplace=True)\n",
    "\n",
    "# save data to csv\n",
    "df_all.to_csv('output/{}-data_all.csv'.format(timestr), encoding='utf8')\n",
    "\n",
    "# show data frame\n",
    "df_all.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explicit processing version (not uesed)\n",
    "\n",
    "Can be activated by selecting all cells below and pressing \"r\" on the keyboard (on command mode, *blue*)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ECONOMY_ELEC_TRANSMISSION\n",
    "df0 = readData(lf[0])\n",
    "df0 = epsToZeros(df0)\n",
    "df0 = filterYearAndCountry(df0,yyy,ccc)\n",
    "df0 = makeValFloat(df0)\n",
    "df0 = groupFrame(df0,'Dim7',lf[0],aver=False)\n",
    "df0.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ECONOMY_GENERATION\n",
    "df1 = readData(lf[1])\n",
    "df1 = epsToZeros(df1)\n",
    "df1 = filterYearAndCountry(df1,yyy,ccc)\n",
    "df1 = makeValFloat(df1)\n",
    "df1 = groupFrame(df1,'Dim11',lf[1],aver=False)\n",
    "df1.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ECONOMY_HEAT_TRANSMISSION\n",
    "df2 = readData(lf[2])\n",
    "df2 = epsToZeros(df2)\n",
    "df2 = filterYearAndCountry(df2,yyy,ccc)\n",
    "df2 = makeValFloat(df2)\n",
    "df2 = groupFrame(df2,'Dim8',lf[2],aver=False)\n",
    "df2.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ELEC_DEMAND\n",
    "df3 = readData(lf[3])\n",
    "df3 = epsToZeros(df3)\n",
    "df3 = filterYearAndCountry(df3,yyy,ccc)\n",
    "df3 = makeValFloat(df3)\n",
    "df3 = groupFrame(df3,'Dim6',lf[3],aver=False)\n",
    "df3.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ELEC_PRICE\n",
    "df4 = readData(lf[4])\n",
    "df4 = epsToZeros(df4)\n",
    "df4 = filterYearAndCountry(df4,yyy,ccc)\n",
    "df4 = makeValFloat(df4)\n",
    "df4 = groupFrame(df4,'Dim7',lf[4],aver=True)\n",
    "df4.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ELEC_PRICE_HOURLY\n",
    "df5 = readData(lf[5])\n",
    "df5 = epsToZeros(df5)\n",
    "df5 = filterYearAndCountry(df5,yyy,ccc)\n",
    "df5 = makeValFloat(df5)\n",
    "df5 = groupFrame(df5,'Dim6',lf[5],aver=True)\n",
    "df5.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ELEC_TRANSMISSION_CAPACITY\n",
    "df6 = readData(lf[6])\n",
    "df6 = epsToZeros(df6)\n",
    "df6 = filterYearAndCountry(df6,yyy,ccc)\n",
    "df6 = makeValFloat(df6)\n",
    "df6['direction'] = df6.Dim5 + '_' + df6.Dim6\n",
    "df6 = groupFrame(df6,'direction',lf[6],aver=False)\n",
    "df6.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ELEC_TRANSMISSION_FLOW\n",
    "df7 = readData(lf[7])\n",
    "df7 = epsToZeros(df7)\n",
    "df7 = filterYearAndCountry(df7,yyy,{})\n",
    "df7 = makeValFloat(df7)\n",
    "df7['direction'] = df7.Dim5 + '_' + df7.Dim6\n",
    "df7 = df7.loc[(df7.direction.str.contains('|'.join(rrr))==True), :]\n",
    "df7 = groupFrame(df7,'direction',lf[7],aver=False)\n",
    "df7.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# EMISSIONS_CO2\n",
    "df8 = readData(lf[8])\n",
    "df8 = epsToZeros(df8)\n",
    "df8 = filterYearAndCountry(df8,yyy,ccc)\n",
    "df8 = makeValFloat(df8)\n",
    "df8['technology'] = df8.Dim8 + '_' + df8.Dim9\n",
    "df8 = groupFrame(df8,'technology',lf[8],aver=False)\n",
    "df8.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# EMISSIONS_CO2\n",
    "df9 = readData(lf[9])\n",
    "df9 = epsToZeros(df9)\n",
    "df9 = filterYearAndCountry(df9,yyy,ccc)\n",
    "df9 = makeValFloat(df9)\n",
    "df9['technology'] = df9.Dim9 + '_' + df9.Dim8 + '_' + df9.Dim10\n",
    "df9 = groupFrame(df9,'technology',lf[9],aver=False)\n",
    "df9.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# FUEL_CONSUMPTION\n",
    "df10 = readData(lf[10])\n",
    "df10 = epsToZeros(df10)\n",
    "df10 = filterYearAndCountry(df10,yyy,ccc)\n",
    "df10 = makeValFloat(df10)\n",
    "#df10['technology'] = df10.Dim8 + '_' + df10.Dim9\n",
    "df10 = groupFrame(df10,'Dim8',lf[10],aver=False)\n",
    "df10.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# GENERATION_CAPACITY\n",
    "df11 = readData(lf[11])\n",
    "df11 = epsToZeros(df11)\n",
    "df11 = filterYearAndCountry(df11,yyy,ccc)\n",
    "df11 = makeValFloat(df11)\n",
    "df11['technology'] = df11.Dim8 + '_' + df11.Dim9\n",
    "df11 = groupFrame(df11,'Dim10',lf[11],aver=False)\n",
    "df11.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# HEAT_DEMAND\n",
    "df12 = readData(lf[12])\n",
    "df12 = epsToZeros(df12)\n",
    "df12 = filterYearAndCountry(df12,yyy,ccc)\n",
    "df12 = makeValFloat(df12)\n",
    "df12 = groupFrame(df12,'Dim7',lf[12],aver=False)\n",
    "df12.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# HEAT_PRICE\n",
    "df13 = readData(lf[13])\n",
    "df13 = epsToZeros(df13)\n",
    "df13 = filterYearAndCountry(df13,yyy,ccc)\n",
    "df13 = makeValFloat(df13)\n",
    "df13 = groupFrame(df13,'Dim7',lf[13],aver=True)\n",
    "df13.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# HEAT_PRICE_HOURLY\n",
    "df14 = readData(lf[14])\n",
    "df14 = epsToZeros(df14)\n",
    "df14 = filterYearAndCountry(df14,yyy,ccc)\n",
    "df14 = makeValFloat(df14)\n",
    "df14 = groupFrame(df14,'Dim7',lf[14],aver=True)\n",
    "df14.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# HEAT_TRANSMISSION_CAPACITY\n",
    "df15 = readData(lf[15])\n",
    "df15 = epsToZeros(df15)\n",
    "df15 = filterYearAndCountry(df15,yyy,ccc)\n",
    "df15 = makeValFloat(df15)\n",
    "#df15['direction'] = df15.Dim5.str.split('_').str[0] + '-' + df15.Dim6.str.split('_').str[0]\n",
    "df15['direction'] = df15.Dim5 + '-' + df15.Dim6\n",
    "df15 = groupFrame(df15,'direction',lf[15],aver=False)\n",
    "df15.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# HEAT_TRANSMISSION_FLOW\n",
    "df16 = readData(lf[16])\n",
    "df16 = epsToZeros(df16)\n",
    "df16 = filterYearAndCountry(df16,yyy,ccc)\n",
    "df16 = makeValFloat(df16)\n",
    "#df16['direction'] = df16.Dim5.str.split('_').str[0] + '-' + df16.Dim6.str.split('_').str[0]\n",
    "df16['direction'] = df16.Dim5 + '-' + df16.Dim6\n",
    "#or use 'Dim6'\n",
    "df16 = groupFrame(df16,'direction',lf[16],aver=False)\n",
    "df16.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# STORAGE_CAPACITY\n",
    "df17 = readData(lf[17])\n",
    "df17 = epsToZeros(df17)\n",
    "df17 = filterYearAndCountry(df17,yyy,ccc)\n",
    "df17 = makeValFloat(df17)\n",
    "df17 = groupFrame(df17,'Dim10',lf[17],aver=False)\n",
    "df17.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# SYSTEM_COSTS\n",
    "df18 = readData(lf[18])\n",
    "df18 = epsToZeros(df18)\n",
    "df18 = filterYearAndCountry(df18,yyy,ccc)\n",
    "df18 = makeValFloat(df18)\n",
    "df18 = groupFrame(df18,'Dim6',lf[18],aver=False)\n",
    "df18.head(2)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "frames = [df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12, df13, df14, df15, df16, df17, df18]\n",
    "df_all = pd.concat(frames, axis=1, sort=False)\n",
    "df_all.index.set_names([None], inplace=True)\n",
    "df_all.columns.set_names([None, None], inplace=True)\n",
    "df_all.to_csv('output/{}-data_all.csv'.format(timestr), encoding='utf8')\n",
    "df_all.head(2)"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}