notebooks/Network_diagrams.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# NOTE:  This is preserved, but needs updating!\n",
    "## It needs updating for:\n",
    "- location (the imports need to be aligned to this file location)\n",
    "- V3 database naming.  Opening alongside a version 3 database should help ID erroneous names."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify path to database in the cell below and run all cells"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hide_input": false,
    "init_cell": true
   },
   "outputs": [],
   "source": [
    "db_path = '../data_files/temoa_utopia.sqlite'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hide_input": true,
    "init_cell": true,
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "import nbformat as nbf\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sqlite3\n",
    "import tabulate\n",
    "import shutil\n",
    "import IPython\n",
    "from IPython.display import HTML, display, Markdown, Image\n",
    "import ipywidgets as widgets\n",
    "from ipywidgets import HBox, VBox, Layout\n",
    "import graphviz\n",
    "from GraphVizUtil import *\n",
    "from GraphVizFormats import *\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "def filter_descriptions(tech_comm_desc):\n",
    "    try:\n",
    "        tech_comm_desc = tech_comm_desc.values[0][0].replace('#', '').replace('\"','').replace(\"\\n\",'').strip()\n",
    "    except:\n",
    "        tech_comm_desc = 'No description provided'\n",
    "    return tech_comm_desc\n",
    "    \n",
    "\n",
    "def create_args_flowd(df_graph):\n",
    "    nodes, tech, ltech, to_tech, from_tech = set(), set(), set(), set(), set()\n",
    "    for ind,row in df_graph.iterrows():\n",
    "        #descriptions:\n",
    "        input_comm_des = filter_descriptions(pd.read_sql(\"SELECT comm_desc FROM commodities WHERE comm_name='\" + row['input_comm'] + \"'\", con))\n",
    "        output_comm_des = filter_descriptions(pd.read_sql(\"SELECT comm_desc FROM commodities WHERE comm_name='\" + row['output_comm'] + \"'\", con))\n",
    "        tech_des = filter_descriptions(pd.read_sql(\"SELECT tech_desc FROM technologies WHERE tech='\" + row['tech'] + \"'\", con))\n",
    "\n",
    "        if 'ethos' in row['input_comm']:\n",
    "            ltech.add('\"' + row['tech'] + '\"' +  ' [tooltip = \"' + tech_des + '\"]')\n",
    "        else :\n",
    "            nodes.add('\"' + row['input_comm'] + '\"' +  ' [tooltip = \"' + input_comm_des + '\"]')\n",
    "        nodes.add('\"' + row['output_comm'] + '\"' +  ' [tooltip = \"' + output_comm_des + '\"]')\n",
    "        tech.add('\"' + row['tech'] + '\"' +  ' [tooltip = \"' + tech_des + '\"]')\n",
    "\n",
    "        if row['input_comm'] != 'ethos':\n",
    "            to_tech.add('\"%s\"' % row['input_comm'] + '\\t->\\t\"%s\"' % row['tech']) \n",
    "        from_tech.add('\"%s\"' % row['tech'] + '\\t->\\t\"%s\"' % row['output_comm'])\n",
    "    args = dict(\n",
    "    enodes = \"\".join('%s;\\n\\t\\t' % x for x in nodes),\n",
    "    tnodes = \"\".join('%s;\\n\\t\\t' % x for x in tech),\n",
    "    iedges = \"\".join('%s;\\n\\t\\t' % x for x in to_tech),\n",
    "    oedges = \"\".join('%s;\\n\\t\\t' % x for x in from_tech),\n",
    "    snodes = \";\".join('%s' %x for x in ltech),\n",
    "    )\n",
    "    return args\n",
    "\n",
    "def return_format_colors():\n",
    "    colors = {}\n",
    "    colors.update(getColorConfig(False))\n",
    "    return colors, quick_run_dot_fmt\n",
    "\n",
    "def return_flowd_table(final_dem, level):\n",
    "    df = pd.read_sql(\"SELECT * FROM Efficiency\", con)\n",
    "    df_sel = df[df['output_comm']==final_dem]\n",
    "    if len(df_sel)==0:\n",
    "        df_sel = df[df['tech']==final_dem]\n",
    "    inputs = df_sel['input_comm'].unique()\n",
    "    iterval=0\n",
    "    while len(inputs)>0:\n",
    "        df_append = df[df['output_comm'].isin(inputs)]\n",
    "        df_sel = pd.concat([df_sel, df_append])\n",
    "        inputs = df_append['input_comm'].unique()\n",
    "        iterval+=1\n",
    "        if iterval>level:\n",
    "            break\n",
    "    df_graph = df_sel[['input_comm', 'tech', 'output_comm']].drop_duplicates()\n",
    "    return df_graph\n",
    "\n",
    "\n",
    "con = sqlite3.connect(db_path) #change path to database\n",
    "cur = con.cursor()   \n",
    "con.text_factory = str \n",
    "\n",
    "def controls_rows(w):\n",
    "    controls = HBox(w.children[:-1], layout = Layout(flex_flow='row wrap', width='max-content'))\n",
    "    output = w.children[-1]\n",
    "    display(VBox([controls, output],  layout = Layout(flex_flow='columns wrap', width='max-content', height='max-content')))\n",
    "\n",
    "    display(HTML(\"<style>.container { width:90% !important; }</style>\"))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": false
   },
   "source": [
    "### Network diagrams for commodities and technologies\n",
    "Select a table, followed by a sector to view the available commodities and technologies. Then select a specific technology/commodity to generate the network diagram. Finally, select the level (upstream connections) of the network to be displayed. Hovering the cursor over each node in the diagram will display the associated description from the commodities or technologies tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "hide_input": true,
    "init_cell": true,
    "jupyter": {
     "source_hidden": true
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "16320f95465b4393b8734889efd08d68",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(Select(description='Table', layout=Layout(height='50px', width='200px'), options…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def network_sector(sector, table_name):\n",
    "    if table_name=='commodities':\n",
    "        query = \"SELECT * FROM commodities WHERE flag='p' AND comm_name IN \\\n",
    "    (SELECT DISTINCT(output_comm) FROM efficiency \\\n",
    "    WHERE tech IN (SELECT DISTINCT(tech) FROM technologies WHERE  sector=\"\n",
    "    elif table_name=='technologies':\n",
    "        query = \"SELECT * FROM technologies WHERE  sector=\"\n",
    "\n",
    "    if sector=='Industry':\n",
    "        query += \"'industrial'\"\n",
    "    elif sector=='Transport':\n",
    "        query += \"'transport'\"\n",
    "    elif sector=='Residential':\n",
    "        query += \"'residential'\"\n",
    "    elif sector=='Commercial':\n",
    "        query += \"'commercial'\"\n",
    "    elif sector=='Supply':\n",
    "        query += \"'supply'\"\n",
    "    elif sector=='Electric':\n",
    "        query += \"'electric'\"\n",
    "    elif sector=='Electric Misc':\n",
    "        query += \"'electric_misc'\"\n",
    "        \n",
    "    if (table_name=='commodities'):\n",
    "        query +=\"))\"\n",
    "\n",
    "    if (sector=='Final demands') & (table_name=='commodities'):\n",
    "        query = \"SELECT * FROM commodities WHERE flag='d'\"\n",
    "    \n",
    "    if (sector=='Final demands') & (table_name=='technologies'):\n",
    "        df_demands = pd.DataFrame(columns = pd.read_sql(\"SELECT * FROM technologies\", con).columns)\n",
    "    else:\n",
    "        df_demands = pd.read_sql(query, con)\n",
    "        \n",
    "    if table_name=='commodities':\n",
    "        col_filter = 'comm_name'\n",
    "    elif table_name=='technologies':\n",
    "        col_filter = 'tech'\n",
    "\n",
    "    df_demands[col_filter] = df_demands[col_filter].str.replace('#', '').str.strip()\n",
    "\n",
    "    def show_desc(desc, level):\n",
    "        if desc!='':\n",
    "            col = col_filter\n",
    "            final_dem = df_demands.loc[df_demands[col_filter]==desc, col].values[0]\n",
    "            df_graph = return_flowd_table(final_dem, level)\n",
    "            args = create_args_flowd(df_graph)\n",
    "            colors, quick_run_dot_fmt = return_format_colors()\n",
    "            args.update(colors)\n",
    "            o_str = 'rankdir = \"LR\" ;'\n",
    "            r_str = 'rankdir = \"LR\" ; \\n\\t size=\"12,12\";'\n",
    "            quick_run_dot_fmt = quick_run_dot_fmt.replace(o_str, r_str)\n",
    "            dot_graph = quick_run_dot_fmt % args\n",
    "            graph = graphviz.Source(dot_graph)\n",
    "            display(Markdown('Network diagram for ' + final_dem))\n",
    "            display(graph)\n",
    "            \n",
    "    layout = widgets.Layout(width='500px', height='150px')\n",
    "    df_demands = df_demands.dropna(subset=[col_filter])\n",
    "    select_options = df_demands[col_filter].unique()\n",
    "    if len(select_options)==1:\n",
    "        select_options = list(select_options) + ['']        \n",
    "\n",
    "    w1 = widgets.Select(options=np.sort(select_options), description=table_name.replace('ies','y').capitalize() , layout=layout)\n",
    "    w2 = widgets.IntSlider(\n",
    "    value=2,\n",
    "    min=0,\n",
    "    max=10,\n",
    "    step=1,\n",
    "    description='Level:',\n",
    "    disabled=False,\n",
    "    continuous_update=False,\n",
    "    orientation='horizontal',\n",
    "    readout=True,\n",
    "    readout_format='d')\n",
    "    w = widgets.interactive(show_desc, desc = w1, level=w2)\n",
    "    controls_rows(w)\n",
    "\n",
    "#selects particular table\n",
    "def select_tech_comm():\n",
    "    def single_table(table_val, sector):\n",
    "        if table_val=='Technologies':\n",
    "            network_sector(sector, 'technologies')\n",
    "        elif table_val=='Commodities':\n",
    "            network_sector(sector, 'commodities')\n",
    "            \n",
    "    layout = widgets.Layout(width='200px', height = '50px')\n",
    "    w1 = widgets.Select(options=['Technologies', 'Commodities'], \\\n",
    "                        description='Table', layout=layout)\n",
    "    layout = widgets.Layout(width='200px', height = '150px')\n",
    "    w2 = widgets.Select(options=['Final demands','Supply','Transport','Residential','Commercial','Industry', 'Electric', 'Electric Misc',], \\\n",
    "                        description='Sector', layout=layout)\n",
    "    \n",
    "\n",
    "    w = widgets.interactive(single_table, table_val = w1, sector = w2)\n",
    "    controls_rows(w)\n",
    "\n",
    "#main function\n",
    "select_tech_comm()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Technology data look-up\n",
    "Use the tool below to select any technology within the database. Data tables specific to the selected technology will be displayed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "hide_input": true,
    "init_cell": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f2f5bde2f6d944d2be7cce49dc2f2c99",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Text(value='E21')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cf9961ed99dd4a6780256239662828bf",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "w = widgets.Text(value='E21')\n",
    "display(w)\n",
    "def f(w):\n",
    "    df = pd.read_sql(\"SELECT * FROM technologies WHERE tech='\" + w + \"'\", con)\n",
    "\n",
    "    if len(df)>0:\n",
    "        \n",
    "        df_efficiency= pd.read_sql(\"SELECT regions, input_comm, tech, vintage, output_comm, efficiency FROM Efficiency WHERE tech='\" + w + \"'\", con)\n",
    "        df_existing_capacity = pd.read_sql(\"SELECT regions, tech, vintage, exist_cap FROM ExistingCapacity WHERE tech='\" + w + \"'\", con)\n",
    "        df_lifetime = pd.read_sql(\"SELECT regions, tech, life FROM LifetimeTech WHERE tech='\" + w + \"'\", con)\n",
    "        df_cost_invest = pd.read_sql(\"SELECT regions, tech, vintage, cost_invest FROM CostInvest WHERE tech='\" + w + \"'\", con)\n",
    "\n",
    "        df_all = df_efficiency.merge(df_existing_capacity, on = ['regions','tech','vintage'], how='left')\n",
    "        df_all = df_all.merge(df_lifetime, on = ['regions','tech'], how='left')\n",
    "        df_all = df_all.merge(df_cost_invest, on = ['regions','tech','vintage'], how='left')\n",
    "\n",
    "        df_all.fillna(0, inplace=True)\n",
    "        display(\n",
    "                HTML(\n",
    "                    tabulate.tabulate(df_all.set_index('regions'),['region'] + list(df_all.set_index('regions').columns.values),tablefmt='html')))\n",
    "       \n",
    "        df_costfixed= pd.read_sql(\"SELECT regions, periods, tech, vintage, cost_fixed FROM CostFixed WHERE tech='\" + w + \"'\", con)\n",
    "        df_costvariable= pd.read_sql(\"SELECT regions, periods, tech, vintage, cost_variable FROM CostVariable WHERE tech='\" + w + \"'\", con)\n",
    "        df_costsannual = df_costfixed.merge(df_costvariable, on = ['regions','periods','tech','vintage'], how='outer')\n",
    "        df_costsannual.fillna(0, inplace=True)\n",
    "        df_costsannual = df_costsannual[['regions', 'periods', 'tech', 'vintage', 'cost_fixed', 'cost_variable']]\n",
    "        if len(df_costsannual)>0:\n",
    "            display(\n",
    "                    HTML(\n",
    "                        tabulate.tabulate(df_costsannual.set_index('regions'),['region'] + list(df_costsannual.set_index('regions').columns.values),tablefmt='html')))\n",
    "\n",
    "    else:\n",
    "        print('')\n",
    "\n",
    "\n",
    "out = widgets.interactive_output(f, {'w': w})\n",
    "display(out)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hide_input": true
   },
   "source": [
    "### Technology/commodity look-up tool\n",
    "Use the tool below to provide a description for any technology or commodity within the database. Type the commodity or technology name in the box below to view its description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "hide_input": true,
    "init_cell": true,
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ae698f893de748259577009b514828d7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Text(value='ELC')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d85d4ca4cf98497ba36a02ad651456e3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Output()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "w = widgets.Text(value='ELC')\n",
    "display(w)\n",
    "def f(w):\n",
    "    df = pd.read_sql(\"SELECT * FROM commodities WHERE comm_name='\" + w + \"'\", con)\n",
    "    if len(df)==0:\n",
    "        df = pd.read_sql(\"SELECT * FROM technologies WHERE tech='\" + w + \"'\", con)\n",
    "\n",
    "    if len(df)>0:\n",
    "        try:\n",
    "            display(Markdown(df['comm_desc'].values[0].replace('#', '').strip()))\n",
    "        except:\n",
    "            display(Markdown(df['tech_desc'].values[0].replace('#', '').strip()))\n",
    "    else:\n",
    "        print('')\n",
    "\n",
    "\n",
    "out = widgets.interactive_output(f, {'w': w})\n",
    "display(out)\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Initialization Cell",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}