TEMBA_binder_Results Viewer 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": false
   },
   "source": [
    "# JRC-KTH-TEMBA result viewer 2\n",
    "\n",
    "## Users can open another version of the result viewer from the following link and use it to compare results\n",
    "[![Binder](https://mybinder.org/badge_logo.svg)](https://mybinder.org/v2/gh/vignesh1987/ipywidgets/master?filepath=TEMBA_binder_Results%20Viewer%201.ipynb)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import csv\n",
    "import pickle\n",
    "import os, sys\n",
    "import numpy as np\n",
    "import psutil\n",
    "from IPython.display import HTML\n",
    "import ipywidgets as widgets\n",
    "from IPython.display import display\n",
    "#import matplotlib as plt\n",
    "from ipywidgets import interact, interactive, fixed, interact_manual\n",
    "#importing plotly and cufflinks in offline mode\n",
    "import plotly as py\n",
    "import plotly.io as pio\n",
    "import plotly.graph_objs as go\n",
    "import cufflinks\n",
    "import plotly.offline as pyo\n",
    "from plotly.offline import plot, iplot, init_notebook_mode\n",
    "pyo.init_notebook_mode()\n",
    "cufflinks.go_offline()\n",
    "cufflinks.set_config_file(world_readable=True, theme='white')\n",
    "print('TEMBA visualization: Base code written by \\033[95mAbhishek Shivakumar\\033[0m.\\n\\t\\t     Adapted and modified by\\033[95m Vignesh Sridharan and Will Usher\\033[0m.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Interactive widget for choosing the results to visualise\n",
    "from glob import glob\n",
    "\n",
    "# Extract the result files from those stored in the directory\n",
    "options = [(x.split(\".pickle\")[0], x) for x in glob('*.pickle')]\n",
    "\n",
    "# Build a dropdown widget to select the results you wish to view\n",
    "result_dropdown = widgets.Dropdown(\n",
    "    options=options,\n",
    "    description='Select TEMBA results to view:',\n",
    ")\n",
    "\n",
    "def update_results(change):\n",
    "    \"\"\"Handles the change in the dropdown menu to select the pickle file and scenario\n",
    "    \"\"\"\n",
    "    global all_params\n",
    "    # The pickle file for the corressponding powerpool/TEMBA is unbundled in the following steps\n",
    "    picklefile=change['new']\n",
    "    pkl_file = open(picklefile, 'rb')\n",
    "    # The pickle file is loaded onto the all_params dictionary\n",
    "    all_params = pickle.load(pkl_file)\n",
    "    \n",
    "    #Automatic naming of scenarios based on pickle file selection\n",
    "    global scenario\n",
    "    scen_name=change['new']\n",
    "    scenario=scen_name.split('pickle')[0]\n",
    "\n",
    "result_dropdown.observe(update_results, names='value')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Please select the power-pool or TEMBA results that you want to visualize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(result_dropdown)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Fundamental dictionaries that govern naming and colour coding\n",
    "url1='./agg_col.csv'\n",
    "url2='./agg_pow_col.csv'\n",
    "url3='./countrycode.csv'\n",
    "url4='./power_tech.csv'\n",
    "url5='./techcodes.csv'\n",
    "colorcode=pd.read_csv(url5,sep=',',encoding = \"ISO-8859-1\")\n",
    "colorcode1=colorcode.drop('colour',axis=1)\n",
    "colorcode2=colorcode.drop('tech_code',axis=1)\n",
    "det_col=dict([(a,b) for a,b in zip(colorcode1.tech_code,colorcode1.tech_name)])\n",
    "color_dict=dict([(a,b) for a,b in zip(colorcode2.tech_name,colorcode2.colour)])\n",
    "agg1=pd.read_csv(url1,sep=',',encoding = \"ISO-8859-1\")\n",
    "agg2=pd.read_csv(url2,sep=',',encoding = \"ISO-8859-1\")\n",
    "agg_col=agg1.to_dict('list')\n",
    "agg_pow_col=agg2.to_dict('list')\n",
    "power_tech=pd.read_csv(url4,sep=',',encoding = \"ISO-8859-1\")\n",
    "t_include = list(power_tech['power_tech'])\n",
    "#Country code list\n",
    "country_code=pd.read_csv(url3,sep=',',encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ###### TO be used only on local jupyter notebooks . Not ont he BINDER###################\n",
    "# #Fundamental dictionaries that govern naming and colour coding\n",
    "# colorcode=pd.read_excel(r'C:\\Users\\vsri\\Box Sync\\dESA\\06 Projects\\2018-12_JRC_TEMBA\\03. Work\\02. Modelling\\Python_script_vignesh\\temba_dict.xlsx',sheet_name='techcodes')\n",
    "# colorcode1=colorcode.drop('colour',axis=1)\n",
    "# colorcode2=colorcode.drop('tech_code',axis=1)\n",
    "# det_col=dict([(a,b) for a,b in zip(colorcode1.tech_code,colorcode1.tech_name)])\n",
    "# color_dict=dict([(a,b) for a,b in zip(colorcode2.tech_name,colorcode2.colour)])\n",
    "# agg1=pd.read_excel(r'C:\\Users\\vsri\\Box Sync\\dESA\\06 Projects\\2018-12_JRC_TEMBA\\03. Work\\02. Modelling\\Python_script_vignesh\\temba_dict.xlsx',sheet_name='agg_col')\n",
    "# agg2=pd.read_excel(r'C:\\Users\\vsri\\Box Sync\\dESA\\06 Projects\\2018-12_JRC_TEMBA\\03. Work\\02. Modelling\\Python_script_vignesh\\temba_dict.xlsx',sheet_name='agg_pow_col')\n",
    "# agg_col=agg1.to_dict('list')\n",
    "# agg_pow_col=agg2.to_dict('list')\n",
    "# power_tech=pd.read_excel(r'C:\\Users\\vsri\\Box Sync\\dESA\\06 Projects\\2018-12_JRC_TEMBA\\03. Work\\02. Modelling\\Python_script_vignesh\\temba_dict.xlsx',sheet_name='power_tech')\n",
    "# t_include = list(power_tech['power_tech'])\n",
    "# #Country code list\n",
    "# country_code=pd.read_excel(r'C:/Users/vsri/Box Sync/dESA/06 Projects/2018-12_JRC_TEMBA/03. Work/02. Modelling/Python_script_vignesh/temba_dict.xlsx',sheet_name='countrycode')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Please sepcify the name of the scenario you are visualizing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# time period definition\n",
    "years = pd.Series(range(2015,2071))\n",
    "#home directory for any image/CSV creation\n",
    "homedir=os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#base function used for many different variables (mainly cost)\n",
    "def df_filter(df,lb,ub,t_exclude):\n",
    "    df['t'] = df['t'].str[lb:ub]\n",
    "    df['value'] = df['value'].astype('float64')\n",
    "    df = df[~df['t'].isin(t_exclude)].pivot_table(index='y', \n",
    "                                          columns='t',\n",
    "                                          values='value', \n",
    "                                          aggfunc='sum').reset_index().fillna(0)\n",
    "    df = df.reindex(sorted(df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    df['y'] = years\n",
    "    df=df[df['y']>2018]\n",
    "    return df\n",
    "#### PLotting function for all graphs except Gas (as it needs relative charts)\n",
    "def df_plot(df,y_title,p_title):\n",
    "    if len(df.columns)==1:\n",
    "        print('There are no values for the result variable that you want to plot')\n",
    "    else:\n",
    "         fig = df.iplot(x='y',\n",
    "             kind='bar', \n",
    "             barmode='stack',\n",
    "             xTitle='Year',\n",
    "             yTitle=y_title,\n",
    "             color=[color_dict[x] for x in df.columns if x != 'y'],\n",
    "             title=(p_title+\"-\"+scenario),\n",
    "             showlegend=True,\n",
    "             asFigure=True)\n",
    "         fig.update_xaxes(range=[2015,2065])  \n",
    "         #pio.write_image(fig, '{}.png'.format(p_title+\"_\"+scenario))\n",
    "         return iplot(fig)\n",
    "#### Emissions#####\n",
    "def df_filter_emission_tech(df,lb,ub):\n",
    "    df['t'] = df['t'].str[lb:ub]\n",
    "    df['e'] = df['e'].str[2:5]\n",
    "    df['value'] = df['value'].astype('float64')\n",
    "    df = df.pivot_table(index='y',columns='t',\n",
    "                        values='value',\n",
    "                        aggfunc='sum').reset_index().fillna(0)\n",
    "    df = df.reindex(sorted(df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    df['y'] = years\n",
    "    #df=df[df['y']>2018]\n",
    "    return df\n",
    "### Annual Emissions\n",
    "def df_filter_emission_tot(df):\n",
    "    df['e'] = df['e'].str[2:5]\n",
    "    df['value'] = df['value'].astype('float64')\n",
    "    df = df.pivot_table(index='y',columns='e',\n",
    "                        values='value',\n",
    "                        aggfunc='sum').reset_index().fillna(0)\n",
    "    df = df.reindex(sorted(df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    df['y'] = years\n",
    "    #df=df[df['y']>2018]\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def power_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #print('The country code is:'+cc)\n",
    "    # Power capacity (detailed)\n",
    "    #cap_df = all_params['TotalCapacityAnnual'][all_params['TotalCapacityAnnual'].t.str.startswith('PWR')].drop('r', axis=1)\n",
    "    cap_df = all_params['TotalCapacityAnnual']\n",
    "    cap_df=cap_df[cap_df['t'].str[:2]==cc].copy()\n",
    "    cap_df['t'] = cap_df['t'].str[2:10]\n",
    "    cap_df['value'] = cap_df['value'].astype('float64')\n",
    "    cap_df = cap_df[cap_df['t'].isin(t_include)].pivot_table(index='y', \n",
    "                                               columns='t',\n",
    "                                               values='value', \n",
    "                                               aggfunc='sum').reset_index().fillna(0)\n",
    "    cap_df = cap_df.reindex(sorted(cap_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #cap_df['y'] = years\n",
    "    #cap_df=cap_df[cap_df['y']>2018]\n",
    "    # The following code can be unhashed to get a detailed power capcity graph.\n",
    "    #df_plot(cap_df,'Gigawatts (GW)',cc+\"-\"+'Power Generation Capacity (Detail)')\n",
    "    #***********************************************\n",
    "    # Power capacity (Aggregated)\n",
    "    cap_agg_df = pd.DataFrame(columns=agg_pow_col)\n",
    "    cap_agg_df.insert(0,'y',cap_df['y'])\n",
    "    cap_agg_df  = cap_agg_df.fillna(0.00)\n",
    "    #\n",
    "    for each in agg_pow_col:\n",
    "        for tech_exists in agg_pow_col[each]:\n",
    "            if tech_exists in cap_df.columns:\n",
    "                try:\n",
    "                    cap_agg_df[each] = cap_agg_df[each] + cap_df[tech_exists]\n",
    "                except TypeError as ex:\n",
    "                    print(cap_agg_df[each].dtypes,cap_df[tech_exists].dtypes)\n",
    "                    raise TypeError(ex)\n",
    "                cap_agg_df[each] = cap_agg_df[each].round(3)\n",
    "    #\n",
    "    cap_agg_df=cap_agg_df.drop('gas_trade',axis=1)\n",
    "    cap_agg_df=cap_agg_df.drop('power_trade',axis=1)\n",
    "    df_plot(cap_agg_df,'Gigawatts (GW)',cc+\"-\"+'Power Generation Capacity (Aggregate)')\n",
    "    # New capacity (detailed)\n",
    "    cap_new_df = all_params['NewCapacity']\n",
    "    cap_new_df=cap_new_df[cap_new_df['t'].str[:2]==cc].copy()\n",
    "    cap_new_df['t'] = cap_new_df['t'].str[2:10]\n",
    "    cap_new_df['value'] = cap_new_df['value'].astype('float64')\n",
    "    cap_new_df = cap_new_df[cap_new_df['t'].isin(t_include)].pivot_table(index='y', \n",
    "                                               columns='t',\n",
    "                                               values='value', \n",
    "                                               aggfunc='sum').reset_index().fillna(0)\n",
    "    cap_new_df = cap_new_df.reindex(sorted(cap_new_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #cap_new_df['y'] = years\n",
    "    #cap_new_df=cap_new_df[cap_new_df['y']>2018]\n",
    "    # The following code can be unhashed to get a detailed power capacity graph.\n",
    "    #df_plot(cap_new_df,'Gigawatts (GW)','New Power Generation Capacity (Detail)')\n",
    "    #***********************************************\n",
    "    # Power capacity (Aggregated)\n",
    "    cap_new_agg_df = pd.DataFrame(columns=agg_pow_col)\n",
    "    cap_new_agg_df.insert(0,'y',cap_new_df['y'])\n",
    "    cap_new_agg_df  = cap_new_agg_df.fillna(0.00)\n",
    "    #\n",
    "    for each in agg_pow_col:\n",
    "        for tech_exists in agg_pow_col[each]:\n",
    "            if tech_exists in cap_new_df.columns:\n",
    "                cap_new_agg_df[each] = cap_new_agg_df[each] + cap_new_df[tech_exists]\n",
    "                cap_new_agg_df[each] = cap_new_agg_df[each].round(3)\n",
    "                ##\n",
    "    cap_new_agg_df=cap_new_agg_df.drop('gas_trade',axis=1)\n",
    "    cap_new_agg_df=cap_new_agg_df.drop('power_trade',axis=1)\n",
    "    df_plot(cap_new_agg_df,'Gigawatts (GW)',cc+\"-\"+ 'New power generation capacity (Aggregate)')\n",
    "    ## Power generation (Detailed)\n",
    "    gen_df = all_params['ProductionByTechnologyAnnual'].copy()\n",
    "    #gen_df=gen_df[gen_df['t'].str[:2]==cc].copy()\n",
    "    #gen_df['t'] = gen_df['t'].str[2:10]\n",
    "    gen_df_export=gen_df[(gen_df['f'].str[2:6]=='EL01')&(gen_df['f'].str[0:2]!=cc)].copy()\n",
    "    gen_df_export=gen_df_export[gen_df_export['t'].str[6:10]=='BP00'].copy()\n",
    "    gen_df_export=gen_df_export[(gen_df_export['t'].str[0:2]==cc)|(gen_df_export['t'].str[4:6]==cc)]\n",
    "    gen_df_export['value'] = gen_df_export['value'].astype(float)*-1\n",
    "    gen_df=gen_df[(gen_df['f'].str[:2]==cc)].copy()\n",
    "    gen_df=gen_df[(gen_df['f'].str[2:6]=='EL01')|(gen_df['f'].str[2:6]=='EL03')].copy()\n",
    "    gen_df=gen_df[(gen_df['t'].str[2:10]!='EL00T00X')&(gen_df['t'].str[2:10]!='EL00TDTX')].copy()\n",
    "    gen_df=pd.concat([gen_df,gen_df_export])\n",
    "    gen_df['value'] = gen_df['value'].astype('float64')\n",
    "    gen_df = gen_df.pivot_table(index='y', \n",
    "                                           columns='t',\n",
    "                                           values='value', \n",
    "                                           aggfunc='sum').reset_index().fillna(0)\n",
    "    for each in gen_df.columns:\n",
    "        if len(each)!=1:\n",
    "            if (each[2:4]=='EL') & (each[6:10]=='BP00'):\n",
    "                pass\n",
    "            else:\n",
    "                gen_df.rename(columns={each:each[2:10]},inplace=True)\n",
    "        else:\n",
    "            pass\n",
    "    gen_df = gen_df.reindex(sorted(gen_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #gen_df['y'] = years\n",
    "    #gen_df=gen_df[gen_df['y']>2018]\n",
    "    #df_plot(gen_df,'Petajoules (PJ)',cc+\"-\"+'Power Generation (Detail)')\n",
    "    #####\n",
    "    # Power generation (Aggregated)\n",
    "    gen_agg_df = pd.DataFrame(columns=agg_pow_col)\n",
    "    gen_agg_df.insert(0,'y',gen_df['y'])\n",
    "    gen_agg_df  = gen_agg_df.fillna(0.00)\n",
    "    for each in agg_pow_col:\n",
    "        for tech_exists in agg_pow_col[each]:\n",
    "            if tech_exists in gen_df.columns:\n",
    "                gen_agg_df[each] = gen_agg_df[each] + gen_df[tech_exists]\n",
    "                gen_agg_df[each] = gen_agg_df[each].round(2)\n",
    "    fig = gen_agg_df.iplot(x='y',\n",
    "                 kind='bar', \n",
    "                 barmode='relative',\n",
    "                 xTitle='Year',\n",
    "                 yTitle=\"Petajoules (PJ)\",\n",
    "                 color=[color_dict[x] for x in gen_agg_df.columns if x != 'y'],\n",
    "                 title=cc+\"-\"+\"Power Generation (Aggregate)\"+\"-\"+scenario,\n",
    "                 showlegend=True,\n",
    "                 asFigure=True)\n",
    "    fig.update_xaxes(range=[2015,2065])  \n",
    "    title=(cc+\"-\"+\"Power Generation (Aggregate)\")\n",
    "    #pio.write_image(fig, '{}.png'.format(title+\"_\"+scenario))\n",
    "    return iplot(fig)\n",
    "    #df_plot(gen_agg_df,'Petajoules (PJ)',cc+\"-\"+'Power Generation (Aggregate)')  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def water_chart (Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #print('The country code is:'+cc)\n",
    "    #water withdrawal detailed\n",
    "    wat_w_df = all_params['UseByTechnologyAnnual']\n",
    "    wat_w_df=wat_w_df[wat_w_df['f'].str[:6]==cc+'WAT1'].copy()\n",
    "\n",
    "    wat_w_df['t'] = wat_w_df['t'].str[2:10]\n",
    "    wat_w_df['value'] = wat_w_df['value'].astype('float64')\n",
    "    wat_w_df = wat_w_df.pivot_table(index='y', \n",
    "                                  columns='t',\n",
    "                                  values='value', \n",
    "                                  aggfunc='sum').reset_index().fillna(0)\n",
    "    wat_w_df = wat_w_df.reindex(sorted(wat_w_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #wat_w_df['y'] = years\n",
    "    #wat_w_df=wat_w_df[wat_w_df['y']>2018]\n",
    "    #df_plot(wat_w_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water Withdrawal')\n",
    "    ###\n",
    "    #Water Withdrawal (Aggregated)\n",
    "    watw_agg_df = pd.DataFrame(columns=agg_col)\n",
    "    watw_agg_df.insert(0,'y',wat_w_df['y'])\n",
    "    watw_agg_df  = watw_agg_df.fillna(0.00)\n",
    "    for each in agg_col:\n",
    "        for tech_exists in agg_col[each]:\n",
    "            if tech_exists in wat_w_df.columns:\n",
    "                watw_agg_df[each] = watw_agg_df[each] + wat_w_df[tech_exists]\n",
    "                watw_agg_df[each] = watw_agg_df[each].round(2)\n",
    "\n",
    "    df_plot(watw_agg_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water Withdrawal')\n",
    "    ##\n",
    "    #water output detailed\n",
    "    wat_o_df = all_params['ProductionByTechnologyAnnual']\n",
    "    wat_o_df=wat_o_df[wat_o_df['f'].str[:6]==cc+'WAT2'].copy()\n",
    "    wat_o_df['t'] = wat_o_df['t'].str[2:10].copy()\n",
    "    wat_o_df['value'] = wat_o_df['value'].astype('float64')\n",
    "    wat_o_df = wat_o_df.pivot_table(index='y', \n",
    "                                 columns='t',\n",
    "                                 values='value', \n",
    "                                 aggfunc='sum').reset_index().fillna(0)\n",
    "    wat_o_df = wat_o_df.reindex(sorted(wat_o_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #wat_o_df['y'] = years\n",
    "    #wat_o_df=wat_o_df[wat_o_df['y']>2018]\n",
    "    #df_plot(wat_o_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water output')\n",
    "    ###\n",
    "    #Water consumption missing row additions\n",
    "    for wd in wat_w_df.columns:\n",
    "        for wc in wat_o_df.columns:\n",
    "            if wd in wat_o_df.columns:\n",
    "                pass\n",
    "            else:\n",
    "                wat_o_df[wd]=0\n",
    "    #####\n",
    "    ####Water consumption (Detailed)\n",
    "    wat_c_df=wat_w_df.set_index('y')-wat_o_df.set_index('y')\n",
    "    wat_c_df=wat_c_df.fillna(0.00)\n",
    "    wat_c_df.reset_index(inplace=True)\n",
    "    #wat_c_df['y']=years\n",
    "    #df_plot(wat_c_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water consumption')\n",
    "    #Water consumption (Aggregate)\n",
    "    watc_agg_df = pd.DataFrame(columns=agg_col)\n",
    "    watc_agg_df.insert(0,'y',wat_c_df['y'])\n",
    "    watc_agg_df  = watc_agg_df.fillna(0.00)\n",
    "    for each in agg_col:\n",
    "        for tech_exists in agg_col[each]:\n",
    "            if tech_exists in wat_c_df.columns:\n",
    "                watc_agg_df[each] = watc_agg_df[each] + wat_c_df[tech_exists]\n",
    "                watc_agg_df[each] = watc_agg_df[each].round(2)\n",
    "    df_plot(watc_agg_df,'Million cubic metres (Mm^3)',cc+'-'+'Water consumption aggregated')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def emissions_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "#     #CO2-Emission detailed\n",
    "#     co2_df = all_params['AnnualTechnologyEmission']\n",
    "#     co2_df=co2_df[co2_df['e'].str[:6]==cc+'CO2'].copy()\n",
    "\n",
    "#     co2_df['value'] = co2_df['value'].astype('float64')\n",
    "#     co2_df = co2_df.pivot_table(index='y',columns='t',\n",
    "#                             values='value',\n",
    "#                             aggfunc='sum').reset_index().fillna(0)\n",
    "#     for each in co2_df.columns:\n",
    "#         if len(each)!=1:\n",
    "#             if (each[2:4]=='NG') & (each[6:10]=='BP00'):\n",
    "#                 pass\n",
    "#             else:\n",
    "#                 co2_df.rename(columns={each:each[2:10]},inplace=True)\n",
    "#         else:\n",
    "#             pass\n",
    "#     co2_df = co2_df.reindex(sorted(co2_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #co2_df['y'] = years\n",
    "    #co2_df=co2_df[co2_df['y']>2018]\n",
    "    #df_plot(co2_df,'Million Tonnes (Mt)',cc+'-''Emissions (CO2)-by technology')\n",
    "#Total emissions by type- This graph shows the total emissions in the country by emissiontype\n",
    "    emis_df = all_params['AnnualEmissions']\n",
    "    emis_df=emis_df[emis_df['e'].str[:5]==cc+'CO2'].copy()\n",
    "    emis_df = df_filter_emission_tot(emis_df)\n",
    "    df_plot(emis_df,'Million Tonnes of CO2 (Mt)',cc+'-''Annual Emissions')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gas_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #GAS Production (Detailed)\n",
    "    gas_df = all_params['ProductionByTechnologyAnnual']\n",
    "    gas_df_export1=gas_df[(gas_df['t'].str[0:4]==cc+'NG')&(gas_df['t'].str[6:10]=='BP00')].copy()\n",
    "    gas_df_export1['value'] = gas_df_export1['value'].astype(float)*-1\n",
    "    gas_df_import1=gas_df[(gas_df['t'].str[2:10]=='NG'+cc+'BP00')].copy()\n",
    "    gas_df=gas_df[(gas_df['t'].str[:2]==cc)&(gas_df['t'].str[2:4]=='NG')&(gas_df['t'].str[6:7]!='P')].copy()\n",
    "    gas_df= gas_df[(gas_df['t'].str[6:10]=='ELGX')|(gas_df['t'].str[6:10]=='ILGX')|(gas_df['t'].str[6:10]=='X00X')].copy()\n",
    "    #gas_df = df_filter_gas(gas_df,2,10,gas_df_export1,gas_df_import1)\n",
    "    gas_df['t'] = gas_df['t'].str[2:10]\n",
    "    gas_df['value'] = gas_df['value'].astype('float64')\n",
    "    gas_df['t'] = gas_df['t'].astype(str)\n",
    "    gas_df=pd.concat([gas_df,gas_df_export1,gas_df_import1])\n",
    "    gas_df = gas_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    gas_df = gas_df.reindex(sorted(gas_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #gas_df['y'] = years\n",
    "    #gas_df=gas_df[gas_df['y']>2018]\n",
    "    for each in gas_df.columns:\n",
    "        if each=='Natural gas exports (Liquification terminal)':\n",
    "            gas_df[each] =gas_df[each].astype(float)*-1\n",
    "        else:\n",
    "            pass\n",
    "    if len(gas_df.columns)==1:\n",
    "            print('There are no values for the result variable that you want to plot')\n",
    "    else:\n",
    "        fig=gas_df.iplot(x='y',\n",
    "                 kind='bar', \n",
    "                 barmode='relative',\n",
    "                 xTitle='Year',\n",
    "                 yTitle=\"Petajoules (PJ)\",\n",
    "                 color=[color_dict[x] for x in gas_df.columns if x != 'y'],\n",
    "                 title=cc+\"-\"+\"Gas extraction, imports and exports\"+\"-\"+scenario,\n",
    "                 showlegend=True,\n",
    "                 asFigure=True)\n",
    "        fig.update_xaxes(range=[2015,2065])  \n",
    "        title=(cc+\"-\"+\"Gas extraction, imports and exports\")\n",
    "        #pio.write_image(fig, '{}.png'.format(title+\"_\"+scenario))\n",
    "        return iplot(fig)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def crude_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #Crude oil refined in the country\n",
    "    cru_r_df = all_params['ProductionByTechnologyAnnual']\n",
    "    cru_r_df=cru_r_df[cru_r_df['f'].str[:6]==cc+'CRU2'].copy()\n",
    "    cru_r_df['t'] = cru_r_df['t'].str[2:10]\n",
    "    cru_r_df['value'] = cru_r_df['value'].astype('float64')\n",
    "    cru_r_df = cru_r_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    cru_r_df = cru_r_df.reindex(sorted(cru_r_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #cru_r_df['y'] = years\n",
    "    #cru_r_df=cru_r_df[cru_r_df['y']>2018]\n",
    "    df_plot(cru_r_df,'Petajoules (PJ)',cc+'-'+'Crude oil refined in the country')\n",
    "    #Crude oil production/imports/exports (Detailed)\n",
    "    cru_df = all_params['ProductionByTechnologyAnnual']\n",
    "    cru_df=cru_df[(cru_df['f'].str[:6]==cc+'CRU1')].copy()\n",
    "    cru_df['t'] = cru_df['t'].str[2:10]\n",
    "    cru_df['value'] = cru_df['value'].astype('float64')\n",
    "    cru_df['t'] = cru_df['t'].astype(str)\n",
    "    cru_df = cru_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    cru_df = cru_df.reindex(sorted(cru_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #cru_df['y'] = years\n",
    "    #cru_df=cru_df[cru_df['y']>2018]\n",
    "    fig=cru_df.iplot(x='y',\n",
    "                  kind='bar', \n",
    "                  barmode='relative',\n",
    "                  xTitle='Year',\n",
    "                  yTitle=\"Petajoules (PJ)\",\n",
    "                  color=[color_dict[x] for x in cru_df.columns if x != 'y'],\n",
    "                  title=cc+\"-\"+\"Crude oil extraction, imports and exports\"+\"-\"+scenario,\n",
    "                  showlegend=True,\n",
    "                  asFigure=True)\n",
    "    fig.update_xaxes(range=[2015,2065])  \n",
    "    title=(cc+\"-\"+\"Crude oil extraction, imports\")\n",
    "    #pio.write_image(fig, '{}.png'.format(title+\"_\"+scenario))\n",
    "    return iplot(fig)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def coal_biomass_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #Coal overview\n",
    "    coal_df = all_params['ProductionByTechnologyAnnual']\n",
    "    coal_df=coal_df[coal_df['f'].str[:6]==cc+'COAL'].copy()\n",
    "    coal_df['t'] = coal_df['t'].str[2:10]\n",
    "    coal_df['value'] = coal_df['value'].astype('float64')\n",
    "    coal_df = coal_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    coal_df = coal_df.reindex(sorted(coal_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #coal_df['y'] = years\n",
    "    #coal_df=coal_df[coal_df['y']>2018]\n",
    "    df_plot(coal_df,'Petajoules (PJ)',cc+'-'+'Coal production by technology')\n",
    "    #Biomass overview\n",
    "    biom_df = all_params['ProductionByTechnologyAnnual']\n",
    "    biom_df=biom_df[biom_df['f'].str[:6]==cc+'BIOM'].copy()\n",
    "    biom_df['t'] = biom_df['t'].str[2:10]\n",
    "    biom_df['value'] = biom_df['value'].astype('float64')\n",
    "    biom_df = biom_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    biom_df = biom_df.reindex(sorted(biom_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #biom_df['y'] = years\n",
    "    #biom_df=biom_df[biom_df['y']>2018]\n",
    "    df_plot(biom_df,'Petajoules (PJ)',cc+'-'+'Biomass production by technology')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def hfo_lfo_chart(Country):\n",
    "    cc=country_code[country_code['Country Name']==Country]['Country code'].tolist()[0]\n",
    "    #Heavy Fuel Oil overview\n",
    "    hfo_df = all_params['ProductionByTechnologyAnnual']\n",
    "    hfo_df=hfo_df[hfo_df['f'].str[:6]==cc+'HFOI'].copy()\n",
    "    hfo_df['t'] = hfo_df['t'].str[2:10]\n",
    "    hfo_df['value'] = hfo_df['value'].astype('float64')\n",
    "    hfo_df = hfo_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    hfo_df = hfo_df.reindex(sorted(hfo_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #hfo_df['y'] = years\n",
    "    #hfo_df=hfo_df[hfo_df['y']>2018]\n",
    "    df_plot(hfo_df,'Petajoules (PJ)',cc+'-'+'HFO production by technology')\n",
    "    #Light Fuel Oil overview\n",
    "    lfo_df = all_params['ProductionByTechnologyAnnual']\n",
    "    lfo_df=lfo_df[lfo_df['f'].str[:6]==cc+'LFOI'].copy()\n",
    "    lfo_df['t'] = lfo_df['t'].str[2:10]\n",
    "    lfo_df['value'] = lfo_df['value'].astype('float64')\n",
    "    lfo_df = lfo_df.pivot_table(index='y',columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "    lfo_df = lfo_df.reindex(sorted(lfo_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "    #lfo_df['y'] = years\n",
    "    #lfo_df=lfo_df[lfo_df['y']>2018]\n",
    "    df_plot(lfo_df,'Petajoules (PJ)',cc+'-'+'LFO production by technology')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CHOOSE THE COUNTRY TO VISUALIZE\n",
    "\n",
    "## The following section will help visualising results on a country level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {},
       "report_default": {}
      }
     }
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "country = widgets.Dropdown(options=country_code['Country Name'])\n",
    "display(country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "power_chart(country.value)\n",
    "water_chart(country.value)\n",
    "emissions_chart(country.value)\n",
    "gas_chart(country.value)\n",
    "crude_chart(country.value)\n",
    "coal_biomass_chart(country.value)\n",
    "hfo_lfo_chart(country.value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# In the follwoing block, the capacity and generation graphs for all the powerpools and TEMBA will be plotted for the chosen scenario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dictionary for the powerpool classifications and countries\n",
    "pp_def={'CAPP':['CM','CF','TD','CG','CD','GQ','GA'],\n",
    "'EAPP':['BI','DJ','ER','ET','KE','RW','SO','SD','TZ','UG','EG','SS'],\n",
    "'NAPP':['DZ','LY','MR','MA','TN'],\n",
    "'SAPP':['AO','BW','LS','MW','MZ','NM','ZA','SZ','ZM','ZW'],\n",
    "'WAPP':['BJ','BF','CI','GM','GH','GN','GW','LR','ML','NE','NG','SN','SL','TG'],     \n",
    "'TEMBA':['DZ','EG','LY','MR','MA','TN','BI','DJ','ER','ET','KE','RW','SO','SD','TZ','UG',\n",
    "                    'AO','BW','LS','MW','MZ','NM','ZA','SZ','ZM','ZW',\n",
    "                    'BJ','BF','CI','GM','GH','GN','GW','LR','ML','NE','NG','SN','SL','TG','CM','CF','TD','CG','CD','GQ','GA','SS']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# first for loop to loop over the major dictionary keys \n",
    "for tk in pp_def.keys():\n",
    "    # The following lines are used for creating dummy \n",
    "    #(empty) dataframes to print aggregated (powerpool/TEMBA) results as csv files\n",
    "    total_gen_df=pd.DataFrame(np.zeros(shape=(56,16)),columns=['y','Coal','Coal with ccs','Gas with ccs','Biomass with ccs','Oil','Gas','Hydro','Nuclear','Solar CSP','Solar PV',\n",
    "                'Wind','Biomass','Geothermal','power_trade','gas_trade'],dtype='float64')\n",
    "    total_gen_df['y']=years\n",
    "    total_cap_df=pd.DataFrame(np.zeros(shape=(56,16)),columns=['y','Coal','Coal with ccs','Gas with ccs','Biomass with ccs','Oil','Gas','Hydro','Nuclear','Solar CSP','Solar PV',\n",
    "                'Wind','Biomass','Geothermal','power_trade','gas_trade'],dtype='float64')\n",
    "    #total_cap_df['y'] = total_cap_df['y'].astype('float64')\n",
    "\n",
    "    total_cap_df['y']=years\n",
    "    #for loop for each country inside a powerpool/TEMBA starts here\n",
    "    for cc in pp_def[tk]:\n",
    "        cap_df = all_params['TotalCapacityAnnual']\n",
    "        cap_df=cap_df[cap_df['t'].str[:2]==cc].copy()\n",
    "        cap_df['t'] = cap_df['t'].str[2:10]\n",
    "        cap_df['value'] = cap_df['value'].astype('float64')\n",
    "        cap_df = cap_df[cap_df['t'].isin(t_include)].pivot_table(index='y', \n",
    "                                                   columns='t',\n",
    "                                                   values='value', \n",
    "                                                   aggfunc='sum').reset_index().fillna(0)\n",
    "        cap_df = cap_df.reindex(sorted(cap_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        #***********************************************\n",
    "        # Power capacity (Aggregated)\n",
    "        cap_agg_df = pd.DataFrame(columns=agg_pow_col)\n",
    "        cap_agg_df.insert(0,'y',cap_df['y'])\n",
    "        cap_agg_df  = cap_agg_df.fillna(0.00)\n",
    "        #\n",
    "        for each in agg_pow_col:\n",
    "            for tech_exists in agg_pow_col[each]:\n",
    "                if tech_exists in cap_df.columns:\n",
    "                    cap_agg_df[each] = cap_agg_df[each] + cap_df[tech_exists]\n",
    "                    cap_agg_df[each] = cap_agg_df[each].round(3)\n",
    "        #df_plot(cap_agg_df,'Gigawatts (GW)',cc+\"-\"+'Power Generation Capacity (Aggregate)')\n",
    "        #total_cap_df=cap_agg_df+total_cap_df\n",
    "        total_cap_df= total_cap_df.set_index('y').add(cap_agg_df.set_index('y'), fill_value=0).reset_index()\n",
    "        #Power generation\n",
    "        gen_df = all_params['ProductionByTechnologyAnnual'].copy()\n",
    "        gen_df_export=gen_df[(gen_df['f'].str[2:6]=='EL01')&(gen_df['f'].str[0:2]!=cc)].copy()\n",
    "        gen_df_export=gen_df_export[gen_df_export['t'].str[6:10]=='BP00'].copy()\n",
    "        gen_df_export=gen_df_export[(gen_df_export['t'].str[0:2]==cc)|(gen_df_export['t'].str[4:6]==cc)]\n",
    "        gen_df_export['value'] = gen_df_export['value'].astype(float)*-1\n",
    "        gen_df=gen_df[(gen_df['f'].str[:2]==cc)].copy()\n",
    "        gen_df=gen_df[(gen_df['f'].str[2:6]=='EL01')|(gen_df['f'].str[2:6]=='EL03')].copy()\n",
    "        gen_df=gen_df[(gen_df['t'].str[2:10]!='EL00T00X')&(gen_df['t'].str[2:10]!='EL00TDTX')].copy()\n",
    "        gen_df=pd.concat([gen_df,gen_df_export])\n",
    "        gen_df['value'] = gen_df['value'].astype('float64')\n",
    "        gen_df = gen_df.pivot_table(index='y', \n",
    "                                               columns='t',\n",
    "                                               values='value', \n",
    "                                               aggfunc='sum').reset_index().fillna(0)\n",
    "        for each in gen_df.columns:\n",
    "            if len(each)!=1:\n",
    "                if (each[2:4]=='EL') & (each[6:10]=='BP00'):\n",
    "                    pass\n",
    "                else:\n",
    "                    gen_df.rename(columns={each:each[2:10]},inplace=True)\n",
    "            else:\n",
    "                pass\n",
    "        gen_df = gen_df.reindex(sorted(gen_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        #df_plot(gen_df,'Petajoules (PJ)',cc+\"-\"+'Power Generation (Detail)')\n",
    "        #####\n",
    "        # Power generation (Aggregated)\n",
    "        gen_agg_df = pd.DataFrame(columns=agg_pow_col)\n",
    "        gen_agg_df.insert(0,'y',gen_df['y'])\n",
    "        gen_agg_df  = gen_agg_df.fillna(0.00)\n",
    "        for each in agg_pow_col:\n",
    "            for tech_exists in agg_pow_col[each]:\n",
    "                if tech_exists in gen_df.columns:\n",
    "                    gen_agg_df[each] = gen_agg_df[each] + gen_df[tech_exists]\n",
    "                    gen_agg_df[each] = gen_agg_df[each].round(2)\n",
    "        total_gen_df=gen_agg_df+total_gen_df\n",
    "    total_gen_df['y']=years\n",
    "    total_gen_df['y']=total_gen_df['y'].astype('float64')\n",
    "    total_cap_df['y']=total_cap_df['y'].astype('float64')\n",
    "    total_gen_df=total_gen_df.drop('gas_trade',axis=1)\n",
    "    total_gen_df=total_gen_df[total_gen_df['y']<=2065]\n",
    "    total_cap_df=total_cap_df[total_cap_df['y']<=2065]\n",
    "    fig=total_gen_df.iplot(x='y',\n",
    "                          kind='bar', \n",
    "                          barmode='relative',\n",
    "                          xTitle='Year',\n",
    "                          yTitle=\"Petajoules (PJ)\",\n",
    "                          color=[color_dict[x] for x in total_gen_df.columns if x != 'y'],\n",
    "                          title=tk +\"-\"+\"Power Generation (Aggregate)\"+\"-\"+ scenario,\n",
    "                          showlegend=True,\n",
    "                          asFigure=True)\n",
    "    fig.update_xaxes(range=[2015,2065])  \n",
    "    title=(tk +\"-\"+\"Power Generation (Aggregate)\"+\"-\"+ scenario)\n",
    "    #pio.write_image(fig, '{}.png'.format(title))\n",
    "    fig.show()\n",
    "    #total_cap_df['y']=years\n",
    "    total_cap_df=total_cap_df[51:]\n",
    "    total_cap_df=total_cap_df.drop('power_trade',axis=1)\n",
    "    total_cap_df=total_cap_df.drop('gas_trade',axis=1)\n",
    "    df_plot(total_cap_df,'Gigawatts (GW)',tk +\"-\"+'Power Generation Capacity (Aggregate)')\n",
    "    #total_gen_df.to_csv(os.path.join(homedir,tk +\"-\"+ scenario +\"-\"+\"-generation.csv\"))\n",
    "    #total_cap_df.to_csv(os.path.join(homedir,tk +\"-\"+ scenario +\"-\"+\"-capacity.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# In the follwoing block, the water consumption and withdrawal graphs are plotted for all the powerpools and TEMBA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for tk in pp_def.keys():\n",
    "    # The following lines are used for creating dummy \n",
    "    #(empty) dataframes to print aggregated (powerpool/TEMBA) results as csv files\n",
    "    total_watc_df=pd.DataFrame(np.zeros(shape=(56,19)),columns=['y','Coal','Oil','Gas','Hydro','Nuclear','Solar CSP','Solar PV',\n",
    "            'Wind','Geothermal','Biomass','Coal Production','Crude Oil production','Crude oil Refinery',\n",
    "            'Natural gas extraction','Uranium extraction','Transmission & Distribution','Backstop',\n",
    "                                                            'Biofuel and Biomass production'],dtype='float64')\n",
    "    total_watc_df['y']=years\n",
    "    total_watw_df=pd.DataFrame(np.zeros(shape=(56,19)),columns=['y','Coal','Oil','Gas','Hydro','Nuclear','Solar CSP','Solar PV',\n",
    "            'Wind','Geothermal','Biomass','Coal Production','Crude Oil production','Crude oil Refinery',\n",
    "            'Natural gas extraction','Uranium extraction','Transmission & Distribution','Backstop',\n",
    "                                                            'Biofuel and Biomass production'],dtype='float64')\n",
    "    total_watw_df['y']=years\n",
    "    ######\n",
    "    for cc in pp_def[tk]:\n",
    "        wat_w_df = all_params['UseByTechnologyAnnual']\n",
    "        wat_w_df=wat_w_df[wat_w_df['f'].str[:6]==cc+'WAT1'].copy()\n",
    "\n",
    "        wat_w_df['t'] = wat_w_df['t'].str[2:10]\n",
    "        wat_w_df['value'] = wat_w_df['value'].astype('float64')\n",
    "        wat_w_df = wat_w_df.pivot_table(index='y', \n",
    "                                      columns='t',\n",
    "                                      values='value', \n",
    "                                      aggfunc='sum').reset_index().fillna(0)\n",
    "        wat_w_df = wat_w_df.reindex(sorted(wat_w_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        #wat_w_df['y'] = years\n",
    "        #wat_w_df=wat_w_df[wat_w_df['y']>2018]\n",
    "        #df_plot(wat_w_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water Withdrawal')\n",
    "        ###\n",
    "        #Water Withdrawal (Aggregated)\n",
    "        watw_agg_df = pd.DataFrame(columns=agg_col)\n",
    "        watw_agg_df.insert(0,'y',wat_w_df['y'])\n",
    "        watw_agg_df  = watw_agg_df.fillna(0.00)\n",
    "        for each in agg_col:\n",
    "            for tech_exists in agg_col[each]:\n",
    "                if tech_exists in wat_w_df.columns:\n",
    "                    watw_agg_df[each] = watw_agg_df[each] + wat_w_df[tech_exists]\n",
    "                    watw_agg_df[each] = watw_agg_df[each].round(2)\n",
    "        total_watw_df= total_watw_df.set_index('y').add(watw_agg_df.set_index('y'), fill_value=0).reset_index()\n",
    "        ##\n",
    "        #water output detailed\n",
    "        wat_o_df = all_params['ProductionByTechnologyAnnual']\n",
    "        wat_o_df=wat_o_df[wat_o_df['f'].str[:6]==cc+'WAT2'].copy()\n",
    "        wat_o_df['t'] = wat_o_df['t'].str[2:10].copy()\n",
    "        wat_o_df['value'] = wat_o_df['value'].astype('float64')\n",
    "        wat_o_df = wat_o_df.pivot_table(index='y', \n",
    "                                     columns='t',\n",
    "                                     values='value', \n",
    "                                     aggfunc='sum').reset_index().fillna(0)\n",
    "        wat_o_df = wat_o_df.reindex(sorted(wat_o_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        #wat_o_df['y'] = years\n",
    "        #wat_o_df=wat_o_df[wat_o_df['y']>2018]\n",
    "        #df_plot(wat_o_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water output')\n",
    "        ###\n",
    "        #Water consumption missing row additions\n",
    "        for wd in wat_w_df.columns:\n",
    "            for wc in wat_o_df.columns:\n",
    "                if wd in wat_o_df.columns:\n",
    "                    pass\n",
    "                else:\n",
    "                    wat_o_df[wd]=0\n",
    "        #####\n",
    "        ####Water consumption (Detailed)\n",
    "        wat_c_df=wat_w_df.set_index('y')-wat_o_df.set_index('y')\n",
    "        wat_c_df=wat_c_df.fillna(0.00)\n",
    "        wat_c_df.reset_index(inplace=True)\n",
    "        #wat_c_df['y']=years\n",
    "        #df_plot(wat_c_df,'Million cubic metres (Mm^3)',cc+\"-\"+'Water consumption')\n",
    "        #Water consumption (Aggregate)\n",
    "        watc_agg_df = pd.DataFrame(columns=agg_col)\n",
    "        watc_agg_df.insert(0,'y',wat_c_df['y'])\n",
    "        watc_agg_df  = watc_agg_df.fillna(0.00)\n",
    "        for each in agg_col:\n",
    "            for tech_exists in agg_col[each]:\n",
    "                if tech_exists in wat_c_df.columns:\n",
    "                    watc_agg_df[each] = watc_agg_df[each] + wat_c_df[tech_exists]\n",
    "                    watc_agg_df[each] = watc_agg_df[each].round(2)\n",
    "        total_watc_df= total_watc_df.set_index('y').add(watc_agg_df.set_index('y'), fill_value=0).reset_index()\n",
    "    total_watw_df['y']=years\n",
    "    total_watc_df['y']=years\n",
    "    total_watc_df['y']=total_watc_df['y'].astype('float64')\n",
    "    total_watw_df['y']=total_watw_df['y'].astype('float64')\n",
    "    total_watw_df=total_watw_df.drop('Backstop',axis=1)\n",
    "    total_watc_df=total_watc_df.drop('Backstop',axis=1)\n",
    "    total_watw_df=total_watw_df[total_watw_df['y']<=2065]\n",
    "    total_watc_df=total_watc_df[total_watc_df['y']<=2065]\n",
    "    df_plot(total_watw_df,'Million cubic metres (Mm^3)',tk+\"-\"+'Water Withdrawal')\n",
    "    df_plot(total_watc_df,'Million cubic metres (Mm^3)',tk+\"-\"+'Water Consumption')\n",
    "    #df_plot(watw_agg_df,'Million cubic metres (Mm^3)',cc+'Water Withdrawal')\n",
    "    #df_plot(watc_agg_df,'Million cubic metres (Mm^3)',cc+'Water consumption aggregated')\n",
    "    #total_watc_df.to_csv(os.path.join(homedir,tk +\"-\"+ scenario + \"-wat consumption.csv\"))\n",
    "    #total_watw_df.to_csv(os.path.join(homedir,tk +\"-\"+ scenario + \"-wat withdrawal.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In the following blocks, total emissions, HFO & LFO use and Coal and Biomass use are plotted for all the powerpools and TEMBA "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Consolidated Emissions for all power pools and TEMBA\n",
    "for tk in pp_def.keys():\n",
    "    total_emis_df=pd.DataFrame(np.zeros(shape=(56,2)),columns=['y','CO2'],dtype='float64')\n",
    "    total_emis_df['y'] = total_emis_df['y'].astype('float64')\n",
    "    total_emis_df['y']=years\n",
    "    for cc in pp_def[tk]:\n",
    "        emis_df = all_params['AnnualEmissions']\n",
    "        emis_df=emis_df[emis_df['e'].str[:5]==cc+'CO2'].copy()\n",
    "        emis_df = df_filter_emission_tot(emis_df)\n",
    "        total_emis_df= total_emis_df.set_index('y').add(emis_df.set_index('y'), fill_value=0).reset_index()\n",
    "    total_emis_df['y']=years\n",
    "    total_emis_df=total_emis_df[total_emis_df['y']<=2065]\n",
    "    df_plot(total_emis_df,'Million Tonnes of CO2 (Mt)',tk+\"-\"+'Annual Emissions')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Consolidated HFO and LFO use\n",
    "for tk in pp_def.keys():\n",
    "    total_lfo_df=pd.DataFrame(np.zeros(shape=(56,4)),columns=['y','Crude oil refinery 1','Crude oil refinery 2','Light Fuel Oil imports'],dtype='float64')\n",
    "    total_lfo_df['y'] = total_lfo_df['y'].astype('float64')\n",
    "    total_lfo_df['y']=years\n",
    "    total_hfo_df=pd.DataFrame(np.zeros(shape=(56,4)),columns=['y','Crude oil refinery 1','Crude oil refinery 2','Heavy Fuel Oil imports'],dtype='float64')\n",
    "    total_hfo_df['y'] = total_hfo_df['y'].astype('float64')\n",
    "    total_hfo_df['y']=years\n",
    "    for cc in pp_def[tk]:\n",
    "        #Heavy Fuel Oil overview\n",
    "        hfo_df = all_params['ProductionByTechnologyAnnual']\n",
    "        hfo_df=hfo_df[hfo_df['f'].str[:6]==cc+'HFOI'].copy()\n",
    "        hfo_df['t'] = hfo_df['t'].str[2:10]\n",
    "        hfo_df['value'] = hfo_df['value'].astype('float64')\n",
    "        hfo_df = hfo_df.pivot_table(index='y',columns='t',\n",
    "                                  values='value', \n",
    "                                  aggfunc='sum').reset_index().fillna(0)\n",
    "        hfo_df = hfo_df.reindex(sorted(hfo_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        total_hfo_df= total_hfo_df.set_index('y').add(hfo_df.set_index('y'), fill_value=0).reset_index()\n",
    "        #hfo_df['y'] = years\n",
    "        #hfo_df=hfo_df[hfo_df['y']>2018]\n",
    "        #Light Fuel Oil overview\n",
    "        lfo_df = all_params['ProductionByTechnologyAnnual']\n",
    "        lfo_df=lfo_df[lfo_df['f'].str[:6]==cc+'LFOI'].copy()\n",
    "        lfo_df['t'] = lfo_df['t'].str[2:10]\n",
    "        lfo_df['value'] = lfo_df['value'].astype('float64')\n",
    "        lfo_df = lfo_df.pivot_table(index='y',columns='t',\n",
    "                                  values='value', \n",
    "                                  aggfunc='sum').reset_index().fillna(0)\n",
    "        lfo_df = lfo_df.reindex(sorted(lfo_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        #df_plot(lfo_df,'Petajoules (PJ)',cc+\"-\"+'LFO production by technology')\n",
    "        total_lfo_df= total_lfo_df.set_index('y').add(lfo_df.set_index('y'), fill_value=0).reset_index()\n",
    "        #lfo_df['y'] = years\n",
    "        #lfo_df=lfo_df[lfo_df['y']>2018]\n",
    "    total_hfo_df['y']=years\n",
    "    total_lfo_df['y']=years\n",
    "    total_hfo_df=total_hfo_df[total_hfo_df['y']<=2065]\n",
    "    total_lfo_df=total_lfo_df[total_lfo_df['y']<=2065]\n",
    "    df_plot(total_hfo_df,'Petajoules (PJ)',tk+\"-\"+'HFO production by technology')\n",
    "    df_plot(total_lfo_df,'Petajoules (PJ)',tk+\"-\"+'LFO production by technology')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Cosnsolidated coal and bioamss usage\n",
    "for tk in pp_def.keys():\n",
    "    total_coal_df=pd.DataFrame(np.zeros(shape=(56,3)),columns=['y','Coal imports (inland transport, maritime freight)','Coal extraction (mining)'],dtype='float64')\n",
    "    total_coal_df['y'] = total_coal_df['y'].astype('float64')\n",
    "    total_coal_df['y']=years\n",
    "    total_biom_df=pd.DataFrame(np.zeros(shape=(56,2)),columns=['y','Biomass extraction/production/refining'],dtype='float64')\n",
    "    total_biom_df['y'] = total_biom_df['y'].astype('float64')\n",
    "    total_biom_df['y']=years\n",
    "    for cc in pp_def[tk]:\n",
    "        #Coal overview\n",
    "        coal_df = all_params['ProductionByTechnologyAnnual']\n",
    "        coal_df=coal_df[coal_df['f'].str[:6]==cc+'COAL'].copy()\n",
    "        coal_df['t'] = coal_df['t'].str[2:10]\n",
    "        coal_df['value'] = coal_df['value'].astype('float64')\n",
    "        coal_df = coal_df.pivot_table(index='y',columns='t',\n",
    "                                  values='value', \n",
    "                                  aggfunc='sum').reset_index().fillna(0)\n",
    "        coal_df = coal_df.reindex(sorted(coal_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        if len(coal_df.columns)==1:\n",
    "            coal_df=pd.DataFrame(np.zeros(shape=(56,3)),columns=['y','Coal imports (inland transport, maritime freight)','Coal extraction (mining)'],dtype='float64')\n",
    "            coal_df['y']=years\n",
    "        total_coal_df= total_coal_df.set_index('y').add(coal_df.set_index('y'), fill_value=0).reset_index()\n",
    "        #total_coal_df=coal_df+total_coal_df\n",
    "        #coal_df['y'] = years\n",
    "        #coal_df=coal_df[coal_df['y']>2018]\n",
    "        \n",
    "        #Biomass overview\n",
    "        biom_df = all_params['ProductionByTechnologyAnnual']\n",
    "        biom_df=biom_df[biom_df['f'].str[:6]==cc+'BIOM'].copy()\n",
    "        biom_df['t'] = biom_df['t'].str[2:10]\n",
    "        biom_df['value'] = biom_df['value'].astype('float64')\n",
    "        biom_df = biom_df.pivot_table(index='y',columns='t',\n",
    "                                          values='value', \n",
    "                                          aggfunc='sum').reset_index().fillna(0)\n",
    "        biom_df = biom_df.reindex(sorted(biom_df.columns), axis=1).set_index('y').reset_index().rename(columns=det_col)\n",
    "        total_biom_df= total_biom_df.set_index('y').add(biom_df.set_index('y'), fill_value=0).reset_index()\n",
    "        #biom_df['y'] = years\n",
    "        #biom_df=biom_df[biom_df['y']>2018]\n",
    "    total_coal_df['y']=years\n",
    "    total_biom_df['y']=years\n",
    "    total_coal_df=total_coal_df[total_coal_df['y']<=2065]\n",
    "    total_biom_df=total_biom_df[total_biom_df['y']<=2065]\n",
    "    df_plot(total_biom_df,'Petajoules (PJ)',tk+'-'+'Biomass production')\n",
    "    df_plot(total_coal_df,'Petajoules (PJ)',tk+'-'+'Coal production')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "extensions": {
   "jupyter_dashboards": {
    "activeView": "grid_default",
    "version": 1,
    "views": {
     "grid_default": {
      "cellMargin": 10,
      "defaultCellHeight": 20,
      "maxColumns": 12,
      "name": "grid",
      "type": "grid"
     },
     "report_default": {
      "name": "report",
      "type": "report"
     }
    }
   }
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}