Calculate terminated results Exiobase v.3.3.11b1 exc. iLUC, electricity markets and social extensions

Investments are not integrated in the MR_HIOT table. They are accounted for in the Final Demand activities.

This tutorial is divided in 4 sections.

  1. Extract numbers from Excel files
  2. Replace 0s with 1s in norm0 file
  3. Read all the csv files as matrices
  4. Run operations

1. Extract numbers from Excel files

Give the name and location of the excel file containing the HIOT and the FD tables.


In [1]:
HIOT_FD = "/Users/marie/Desktop/MR_HIOT_2011_v3.3.11.xlsx"


/Users/marie/Desktop

In [2]:
import pandas as pd
import csv
### MR-HIOT.csv is created because the excel is too heavy
data_xls = pd.read_excel(HIOT_FD, 'HIOT', index_col=None)
data_xls.to_csv('MR_HIOT.csv', encoding='utf-8')
### FD.csv is created because the excel is too heavy
data_xls = pd.read_excel(HIOT_FD, 'FD', index_col=None, header = None)
data_xls.to_csv('FD.csv', encoding='utf-8')

From MR_HIOT_2011_v3.3.11.xlsx (MR_HIOT.csv & FD.csv), we create:

  • Zn_tonorm.csv (7872 columns & rows)
  • norm.csv (7872 columns)
  • FD.csv (288 columns & 7872 rows)

In [3]:
outfile1 ="/Users/marie/Desktop/Zn_tonorm.csv"
source = pd.read_csv('MR_HIOT.csv', index_col = None, header = None, low_memory = False)
Zn_tonorm = source.iloc[7:7879, 5:7877]
Zn_tonorm.to_csv(outfile1, header = None, index = None)

outfile2 ="/Users/marie/Desktop/norm.csv"
norm = source.iloc[1:2, 5:7877]
norm.to_csv(outfile2, header = None, index = None)

outfile3 ="/Users/marie/Desktop/FD.csv"
source1 = pd.read_csv('FD.csv', index_col = None, header = None, low_memory = False)
FD = source1.iloc[8:7880, 6:294]
FD.to_csv(outfile3, header = None, index = None)

Give the name of the excel file from which the extensions should be extracted

The file from Stefano cannot be used as it is. Water extensions should be corrected, biogenic carbon relocated, biogenic methane recalculated and land occupation flows summed up.


In [1]:
extensions = "/Users/marie/Desktop/MR_HIOT_2011_v3.3.11_extensions_MS.xlsx"

We create Bn_tonorm.csv including the extensions for the 7872 producing activities:

  • 30 resource flows (green water was excluded)
  • 240 land occupation flows
  • 62 direct emissions to Air, Water and Soil

In [4]:
import pandas as pd
data_xls = pd.read_excel(extensions, 'resource_act', index_col=None, header = None, encoding='utf-8')
##outfile4 ="/Users/marie/Desktop/Bn_tonorm_resource.csv"
Bn_tonorm_resource = data_xls.iloc[7:37, 5:7877]
##Bn_tonorm_resource.to_csv(outfile4, header = None, index = None)

In [3]:
data_xls = pd.read_excel(extensions, 'Land_act', index_col=None, header = None, encoding='utf-8')
##outfile5 ="/Users/marie/Desktop/Bn_tonorm_land.csv"
Bn_tonorm_land = data_xls.iloc[247:251, 5:7877]
##Bn_tonorm_land.to_csv(outfile5, header = None, Index = None)

In [4]:
data_xls = pd.read_excel(extensions, 'Emiss_act', index_col=None, header = None, encoding='utf-8')
##outfile6 ="/Users/marie/Desktop/Bn_tonorm_emiss.csv"
Bn_tonorm_emiss = data_xls.iloc[7:70, 5:7877]
##Bn_tonorm_emiss.to_csv(outfile6, header = None, index = None)

In [5]:
outfile ="/Users/marie/Desktop/Bn_tonorm.csv"
frame = [Bn_tonorm_resource, Bn_tonorm_land, Bn_tonorm_emiss]
Bn_tonorm = pd.concat(frame)
Bn_tonorm.to_csv(outfile, header = None, index = None)

We create FD_ext.csv including the extensions for the 288 Final Demand activities.


In [6]:
data_xls = pd.read_excel(extensions, 'resource_FD', index_col=None, header = None, encoding='utf-8')
##outfile8 ="/Users/marie/Desktop/FD_resource.csv"
FD_resource = data_xls.iloc[7:37, 5:293]
##FD_resource.to_csv(outfile8, header = None, index = None)

In [7]:
data_xls = pd.read_excel(extensions, 'Land_FD', index_col=None, header = None, encoding='utf-8')
##outfile9 ="/Users/marie/Desktop/FD_land.csv"
FD_land = data_xls.iloc[247:251, 5:293]
##FD_land.to_csv(outfile9, header = None, index = None)

In [8]:
data_xls = pd.read_excel(extensions, 'Emiss_FD', index_col=None, header = None,encoding='utf-8')
##outfile10 ="/Users/marie/Desktop/FD_emiss.csv"
FD_emiss = data_xls.iloc[7:70, 5:293]
##FD_emiss.to_csv(outfile10, header = None, Index = None)

In [9]:
outfile ="/Users/marie/Desktop/FD_ext.csv"
frame = [FD_resource, FD_land, FD_emiss]
FD_ext = pd.concat(frame)
FD_ext.to_csv(outfile, header = None, index = None)

2. Replace 0s with 1s in norm0 file

Replace 0s with 1s in norm.csv (matrices can't be divided by 0)


In [17]:
def replace_0with1(source, result):
    with open(source,"r") as source:
        rdr = csv.reader(source)
        with open (result, "w") as result:
            wtr = csv.writer(result)
            for row in rdr:
                row = [x.replace('0', '1') if x == '0' else x for x in row]
                wtr.writerow(row)

In [18]:
replace_0with1("norm0.csv", "norm1.csv")

3. Read CSV files as matrices

To make operations with the numpy package, read the following files extracted previously:

  • Zn_tonorm.csv as a matrice
  • norm1.csv as a vector
  • Bn_tonorm.csv as a matrice
  • FD.csv as a matrice
  • FD_ext.csv as a matrice

In [10]:
import csv
import numpy as np

with open('norm1.csv','r') as dest_f:
    data_iter = csv.reader(dest_f, 
                           delimiter = ',', 
                           quotechar = '"')
    data = [data for data in data_iter]
nor = np.asarray(data, dtype='float')

In [11]:
with open("Zn_tonorm.csv",'r') as dest_f:
    data_iter = csv.reader(dest_f, 
                           delimiter = ',', 
                           quotechar = '"')
    data = [data for data in data_iter]
Zn_tonorm = np.array(list(data)).astype('float')

In [12]:
with open("Bn_tonorm.csv",'r') as dest_f:
    data_iter = csv.reader(dest_f, 
                           delimiter = ',', 
                           quotechar = '"')
    data = [data for data in data_iter]
Bn_tonorm = np.array(list(data)).astype('float')

In [13]:
with open("FD.csv",'r') as dest_f:
    data_iter = csv.reader(dest_f, 
                           delimiter = ',', 
                           quotechar = '"')
    data = [data for data in data_iter]
f_cons = np.array(list(data)).astype('float')

In [14]:
with open("FD_ext.csv",'r') as dest_f:
    data_iter = csv.reader(dest_f, 
                           delimiter = ',', 
                           quotechar = '"')
    data = [data for data in data_iter]
f_em = np.array(list(data)).astype('float')

4. Run operations

To obtain Zn and Bn, Zn_tonorm and Bn_tonorm needs to be didvided by the norm vector.


In [15]:
Zn = Zn_tonorm/nor

In [16]:
Bn = Bn_tonorm/nor

We create the identity matrice


In [17]:
identity = np.matrix(np.identity(7872), copy=False)

In [18]:
An = identity-Zn

In [19]:
S = np.linalg.inv(An)

In [20]:
BLCI = Bn*S

In [21]:
from io import StringIO
import numpy as np
s=StringIO()
np.savetxt('BLCI.csv', BLCI, fmt='%.10f', delimiter=',', newline="\n")

In [22]:
F = BLCI*f_cons

In [23]:
F2 = F+f_em

In [24]:
from io import StringIO
import numpy as np
s=StringIO()
np.savetxt('F2.csv', F2, fmt='%.10f', delimiter=',', newline="\n")