Leontive inverse tutorial - investments are NOT integrated - iLUC, electricty markets and social extensions ARE included.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Calculate terminated results Exiobase v.3.3.11b1 exc. iLUC, electricity markets and social extensions\n",
    "\n",
    "Investments are not integrated in the MR_HIOT table. They are accounted for in the Final Demand activities.\n",
    "\n",
    "This tutorial is divided in 4 sections.\n",
    "1. Extract numbers from Excel files\n",
    "2. Replace 0s with 1s in norm0 file\n",
    "3. Read all the csv files as matrices\n",
    "4. Run operations\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 1. Extract numbers from Excel files\n",
    "\n",
    "#### Give the name and location of the excel file containing the HIOT and the FD tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/marie/Desktop\n"
     ]
    }
   ],
   "source": [
    "HIOT_FD = \"/Users/marie/Desktop/MR_HIOT_2011_v3.3.11.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import csv\n",
    "### MR-HIOT.csv is created because the excel is too heavy\n",
    "data_xls = pd.read_excel(HIOT_FD, 'HIOT', index_col=None)\n",
    "data_xls.to_csv('MR_HIOT.csv', encoding='utf-8')\n",
    "### FD.csv is created because the excel is too heavy\n",
    "data_xls = pd.read_excel(HIOT_FD, 'FD', index_col=None, header = None)\n",
    "data_xls.to_csv('FD.csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From MR_HIOT_2011_v3.3.11.xlsx (MR_HIOT.csv & FD.csv), we create:\n",
    "- Zn_tonorm.csv (7872 columns & rows)\n",
    "- norm.csv (7872 columns)\n",
    "- FD.csv (288 columns & 7872 rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "outfile1 =\"/Users/marie/Desktop/Zn_tonorm.csv\"\n",
    "source = pd.read_csv('MR_HIOT.csv', index_col = None, header = None, low_memory = False)\n",
    "Zn_tonorm = source.iloc[7:7879, 5:7877]\n",
    "Zn_tonorm.to_csv(outfile1, header = None, index = None)\n",
    "\n",
    "outfile2 =\"/Users/marie/Desktop/norm.csv\"\n",
    "norm = source.iloc[1:2, 5:7877]\n",
    "norm.to_csv(outfile2, header = None, index = None)\n",
    "\n",
    "outfile3 =\"/Users/marie/Desktop/FD.csv\"\n",
    "source1 = pd.read_csv('FD.csv', index_col = None, header = None, low_memory = False)\n",
    "FD = source1.iloc[8:7880, 6:294]\n",
    "FD.to_csv(outfile3, header = None, index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Give the name of the excel file from which the extensions should be extracted\n",
    "The file from Stefano cannot be used as it is. \n",
    "Water extensions should be corrected, biogenic carbon relocated, biogenic methane recalculated and land occupation flows summed up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "extensions = \"/Users/marie/Desktop/MR_HIOT_2011_v3.3.11_extensions_MS.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We create Bn_tonorm.csv including the extensions for the 7872 producing activities:\n",
    "- 30 resource flows (green water was excluded)\n",
    "- 240 land occupation flows\n",
    "- 62 direct emissions to Air, Water and Soil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "data_xls = pd.read_excel(extensions, 'resource_act', index_col=None, header = None, encoding='utf-8')\n",
    "##outfile4 =\"/Users/marie/Desktop/Bn_tonorm_resource.csv\"\n",
    "Bn_tonorm_resource = data_xls.iloc[7:37, 5:7877]\n",
    "##Bn_tonorm_resource.to_csv(outfile4, header = None, index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data_xls = pd.read_excel(extensions, 'Land_act', index_col=None, header = None, encoding='utf-8')\n",
    "##outfile5 =\"/Users/marie/Desktop/Bn_tonorm_land.csv\"\n",
    "Bn_tonorm_land = data_xls.iloc[247:251, 5:7877]\n",
    "##Bn_tonorm_land.to_csv(outfile5, header = None, Index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data_xls = pd.read_excel(extensions, 'Emiss_act', index_col=None, header = None, encoding='utf-8')\n",
    "##outfile6 =\"/Users/marie/Desktop/Bn_tonorm_emiss.csv\"\n",
    "Bn_tonorm_emiss = data_xls.iloc[7:70, 5:7877]\n",
    "##Bn_tonorm_emiss.to_csv(outfile6, header = None, index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "outfile =\"/Users/marie/Desktop/Bn_tonorm.csv\"\n",
    "frame = [Bn_tonorm_resource, Bn_tonorm_land, Bn_tonorm_emiss]\n",
    "Bn_tonorm = pd.concat(frame)\n",
    "Bn_tonorm.to_csv(outfile, header = None, index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We create FD_ext.csv including the extensions for the 288 Final Demand activities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data_xls = pd.read_excel(extensions, 'resource_FD', index_col=None, header = None, encoding='utf-8')\n",
    "##outfile8 =\"/Users/marie/Desktop/FD_resource.csv\"\n",
    "FD_resource = data_xls.iloc[7:37, 5:293]\n",
    "##FD_resource.to_csv(outfile8, header = None, index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data_xls = pd.read_excel(extensions, 'Land_FD', index_col=None, header = None, encoding='utf-8')\n",
    "##outfile9 =\"/Users/marie/Desktop/FD_land.csv\"\n",
    "FD_land = data_xls.iloc[247:251, 5:293]\n",
    "##FD_land.to_csv(outfile9, header = None, index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data_xls = pd.read_excel(extensions, 'Emiss_FD', index_col=None, header = None,encoding='utf-8')\n",
    "##outfile10 =\"/Users/marie/Desktop/FD_emiss.csv\"\n",
    "FD_emiss = data_xls.iloc[7:70, 5:293]\n",
    "##FD_emiss.to_csv(outfile10, header = None, Index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "outfile =\"/Users/marie/Desktop/FD_ext.csv\"\n",
    "frame = [FD_resource, FD_land, FD_emiss]\n",
    "FD_ext = pd.concat(frame)\n",
    "FD_ext.to_csv(outfile, header = None, index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 2. Replace 0s with 1s in norm0 file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Replace 0s with 1s in norm.csv (matrices can't be divided by 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def replace_0with1(source, result):\n",
    "    with open(source,\"r\") as source:\n",
    "        rdr = csv.reader(source)\n",
    "        with open (result, \"w\") as result:\n",
    "            wtr = csv.writer(result)\n",
    "            for row in rdr:\n",
    "                row = [x.replace('0', '1') if x == '0' else x for x in row]\n",
    "                wtr.writerow(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "replace_0with1(\"norm0.csv\", \"norm1.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 3. Read CSV files as matrices \n",
    "To make operations with the numpy package, read the following files extracted previously:\n",
    "- Zn_tonorm.csv as a matrice\n",
    "- norm1.csv as a vector\n",
    "- Bn_tonorm.csv as a matrice\n",
    "- FD.csv as a matrice\n",
    "- FD_ext.csv as a matrice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "import numpy as np\n",
    "\n",
    "with open('norm1.csv','r') as dest_f:\n",
    "    data_iter = csv.reader(dest_f, \n",
    "                           delimiter = ',', \n",
    "                           quotechar = '\"')\n",
    "    data = [data for data in data_iter]\n",
    "nor = np.asarray(data, dtype='float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "with open(\"Zn_tonorm.csv\",'r') as dest_f:\n",
    "    data_iter = csv.reader(dest_f, \n",
    "                           delimiter = ',', \n",
    "                           quotechar = '\"')\n",
    "    data = [data for data in data_iter]\n",
    "Zn_tonorm = np.array(list(data)).astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "with open(\"Bn_tonorm.csv\",'r') as dest_f:\n",
    "    data_iter = csv.reader(dest_f, \n",
    "                           delimiter = ',', \n",
    "                           quotechar = '\"')\n",
    "    data = [data for data in data_iter]\n",
    "Bn_tonorm = np.array(list(data)).astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "with open(\"FD.csv\",'r') as dest_f:\n",
    "    data_iter = csv.reader(dest_f, \n",
    "                           delimiter = ',', \n",
    "                           quotechar = '\"')\n",
    "    data = [data for data in data_iter]\n",
    "f_cons = np.array(list(data)).astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "with open(\"FD_ext.csv\",'r') as dest_f:\n",
    "    data_iter = csv.reader(dest_f, \n",
    "                           delimiter = ',', \n",
    "                           quotechar = '\"')\n",
    "    data = [data for data in data_iter]\n",
    "f_em = np.array(list(data)).astype('float')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 4. Run operations\n",
    "To obtain Zn and Bn, Zn_tonorm and Bn_tonorm needs to be didvided by the norm vector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "Zn = Zn_tonorm/nor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "Bn = Bn_tonorm/nor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We create the identity matrice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "identity = np.matrix(np.identity(7872), copy=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "An = identity-Zn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "S = np.linalg.inv(An)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "BLCI = Bn*S"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "import numpy as np\n",
    "s=StringIO()\n",
    "np.savetxt('BLCI.csv', BLCI, fmt='%.10f', delimiter=',', newline=\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "F = BLCI*f_cons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "F2 = F+f_em"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "import numpy as np\n",
    "s=StringIO()\n",
    "np.savetxt('F2.csv', F2, fmt='%.10f', delimiter=',', newline=\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}