ALIGNED project: Import data from Excel template¶
Aligning Life Cycle Assessment methods and bio-based sectors for improved environmental performance
Horizon Europe grant agreement N° 101059430. Views and opinions expressed are however those of the author(s) only and do not necessarily reflect those of the European Union or the European Research Executive Agency.
WP1 Shared modelling framework and learnings¶
Task 1.1 Framework for foreground inventory of biobased products¶
Deliverable 1.2 Description of scientific methods¶
Importer for LCI data from the ALIGNED template.¶
Massimo Pizzol, Aalborg University (AAU), 2024¶
This notebook shows how to import life cycle inventory data using the LCI data template of the ALIGNED project.
Requires as minimum:
- Python Data Analysis Library pandas
To import into brightway:
- brightway2 LCA software library
- a
lci_to_bw2.pyconverter - ecoinvent version 3.9 consequential system model and biosphere3 database
Steps
- Prepare your inventory in MS Excel using the template. See the example file ALIGNED-LCI-template-example.xlsx
- Convert either manually or automatically the relevant MS Excel sheet as .csv file, see the example file ALIGNED-LCI-template-example.csv
- Import the .csv file as a dataframe with the pandas function
.read_csv(). Clean it up for unnecessary columns. - Convert the dataframe into a dict using the function
lci_to_bw2()(must be included in the same folder as the file) - Import as Brightway2 database using Brightway's
Database()and.write()functions. Perform calculation
NOTE: this importer contains no automated tests so you need to make sure manually that the excel and csv files are in good order.
See an example below.
In [10]:
# import all libraries
import pandas as pd
import brightway2 as bw
from lci_to_bw2 import * # import all the functions of this module
First we import the .csv file with the data
In [2]:
mydb = pd.read_csv('ALIGNED-LCI-template-example.csv', header = 0, sep = ",") # using csv file avoids encoding problem
mydb.head()
Out[2]:
| Activity database | Activity code | Activity name | Activity unit | Activity type | Exchange database | Exchange input | Exchange amount | Exchange unit | Exchange type | Exchange uncertainty type | Exchange loc | Exchange scale | Exchange negative | Notes | Other | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | 10.0 | kilowatt hour | production | NaN | NaN | NaN | NaN | Production output of electricity from a power ... | NaN |
| 1 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | 2.0 | kilogram | technosphere | 2.0 | 1.0 | 1.01 | False | Input of fuel needed in the production of elec... | NaN |
| 2 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | biosphere3 | 349b29d1-3e58-4c66-98b9-9d1a076efd2e | 1.0 | kilogram | biosphere | 2.0 | 1.0 | 1.01 | False | Emission of Carbon dioxide,UUID taken from Bio... | NaN |
| 3 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | 45ce81c4-fafe-4b2f-8404-b4cdc73e904c | 0.1 | kilogram | biosphere | 0.0 | 2.0 | 2.00 | True | Emission of Sulphur dioxide, user-defined envi... | NaN |
| 4 | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | Fuel production | kilogram | process | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | 100.0 | kilogram | production | NaN | NaN | NaN | NaN | Production output of fuel from diesel production | NaN |
Some of the unnecessary columns are removed form the data table.
In [3]:
# clean up a bit
mydb = mydb.drop(columns=['Notes', 'Other']) # remove the columns not needed
mydb['Exchange uncertainty type'] = mydb['Exchange uncertainty type'].fillna(0).astype(int) # uncertainty as integers
# Note: to avoid having both nan and values in the uncertainty column I use zero as default
mydb.head()
Out[3]:
| Activity database | Activity code | Activity name | Activity unit | Activity type | Exchange database | Exchange input | Exchange amount | Exchange unit | Exchange type | Exchange uncertainty type | Exchange loc | Exchange scale | Exchange negative | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | 10.0 | kilowatt hour | production | 0 | NaN | NaN | NaN |
| 1 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | 2.0 | kilogram | technosphere | 2 | 1.0 | 1.01 | False |
| 2 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | biosphere3 | 349b29d1-3e58-4c66-98b9-9d1a076efd2e | 1.0 | kilogram | biosphere | 2 | 1.0 | 1.01 | False |
| 3 | ALIGNED-LCI-template | 3955bc3b-db9f-4d01-8510-90a551f9caeb | Electricity production | kilowatt hour | process | ALIGNED-LCI-template | 45ce81c4-fafe-4b2f-8404-b4cdc73e904c | 0.1 | kilogram | biosphere | 0 | 2.0 | 2.00 | True |
| 4 | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | Fuel production | kilogram | process | ALIGNED-LCI-template | b8f80db7-4511-4970-af59-f40b23e733b1 | 100.0 | kilogram | production | 0 | NaN | NaN | NaN |
The 'lci_to_bw2.py' file contains code that converts the data in tabular format into a python dictionary that can be written by the brightway software into a database.
In [4]:
# Create a dict that can be written as database
bw2_db = lci_to_bw2(mydb) # a function from the lci_to_bw2 module
bw2_db
Out[4]:
{('ALIGNED-LCI-template',
'3955bc3b-db9f-4d01-8510-90a551f9caeb'): {'name': 'Electricity production', 'unit': '\ufeffkilowatt hour', 'type': 'process', 'exchanges': [{'input': ('ALIGNED-LCI-template',
'3955bc3b-db9f-4d01-8510-90a551f9caeb'),
'amount': 10.0,
'unit': 'kilowatt hour',
'type': 'production',
'uncertainty type': 0},
{'input': ('ALIGNED-LCI-template', 'b8f80db7-4511-4970-af59-f40b23e733b1'),
'amount': 2.0,
'unit': 'kilogram',
'type': 'technosphere',
'uncertainty type': 2,
'loc': 1.0,
'scale': 1.01,
'negative': False},
{'input': ('biosphere3', '349b29d1-3e58-4c66-98b9-9d1a076efd2e'),
'amount': 1.0,
'unit': 'kilogram',
'type': 'biosphere',
'uncertainty type': 2,
'loc': 1.0,
'scale': 1.01,
'negative': False},
{'input': ('ALIGNED-LCI-template', '45ce81c4-fafe-4b2f-8404-b4cdc73e904c'),
'amount': 0.1,
'unit': 'kilogram',
'type': 'biosphere',
'uncertainty type': 0,
'loc': 2.0,
'scale': 2.0,
'negative': True}]},
('ALIGNED-LCI-template',
'b8f80db7-4511-4970-af59-f40b23e733b1'): {'name': 'Fuel production', 'unit': 'kilogram', 'type': 'process', 'exchanges': [{'input': ('ALIGNED-LCI-template',
'b8f80db7-4511-4970-af59-f40b23e733b1'),
'amount': 100.0,
'unit': 'kilogram',
'type': 'production',
'uncertainty type': 0},
{'input': ('ecoinvent 3.9 conseq', 'cea1e434115aa84cbfd7dc3086b61e80'),
'amount': 100.0,
'unit': 'kilogram',
'type': 'technosphere',
'uncertainty type': 0},
{'input': ('biosphere3', '349b29d1-3e58-4c66-98b9-9d1a076efd2e'),
'amount': 10.0,
'unit': 'kilogram',
'type': 'biosphere',
'uncertainty type': 0},
{'input': ('ALIGNED-LCI-template', '45ce81c4-fafe-4b2f-8404-b4cdc73e904c'),
'amount': 2.0,
'unit': 'kilogram',
'type': 'biosphere',
'uncertainty type': 0},
{'input': ('ALIGNED-LCI-template', '044f84a9-833c-4918-bae0-c70221c0c764'),
'amount': -50.0,
'unit': 'kilogram',
'type': 'biosphere',
'uncertainty type': 0}]},
('ALIGNED-LCI-template',
'45ce81c4-fafe-4b2f-8404-b4cdc73e904c'): {'name': 'Sulphur dioxide', 'unit': 'kilogram', 'type': 'biosphere'},
('ALIGNED-LCI-template',
'044f84a9-833c-4918-bae0-c70221c0c764'): {'name': 'Crude oil', 'unit': 'kilogram', 'type': 'biosphere'}}
We now open a brightway project with the right bacground database included.
In [5]:
bw.projects.set_current('advlca23') # Project with 'ecoinvent 3.9.1 conseq' and 'biosphere3' already imported
Time to write the data on a database.
Important:
The database name should be the same as in the excel file...
make sure you shut down all other notebooks using the same bw project before you run this. Only one user at the time can write on a database. Otherwise you'll get a "Database locked" error.
In [6]:
t_db = bw.Database('ALIGNED-LCI-template') # it works because the database name in the excel file is the same
# shut down all other notebooks using the same project
t_db.write(bw2_db)
Writing activities to SQLite3 database: 0% [####] 100% | ETA: 00:00:00 Total time elapsed: 00:00:00
Title: Writing activities to SQLite3 database: Started: 03/19/2024 11:18:22 Finished: 03/19/2024 11:18:22 Total time elapsed: 00:00:00 CPU %: 47.30 Memory %: 1.27
We give a look at the imported database
In [7]:
[print(act, act['code']) for act in t_db] # check activities and their codes
print('---------')
[[print(act, exc) for exc in list(act.exchanges())]for act in t_db] # check exchanges in each activity
print('---------')
[[print(exc.uncertainty) for exc in list(act.exchanges())]for act in t_db] # check if uncertainy is stored
'Sulphur dioxide' (kilogram, None, None) 45ce81c4-fafe-4b2f-8404-b4cdc73e904c
'Fuel production' (kilogram, None, None) b8f80db7-4511-4970-af59-f40b23e733b1
'Electricity production' (kilowatt hour, None, None) 3955bc3b-db9f-4d01-8510-90a551f9caeb
'Crude oil' (kilogram, None, None) 044f84a9-833c-4918-bae0-c70221c0c764
---------
'Fuel production' (kilogram, None, None) Exchange: 100.0 kilogram 'Fuel production' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>
'Fuel production' (kilogram, None, None) Exchange: 100.0 kilogram 'diesel production, low-sulfur' (kilogram, CH, None) to 'Fuel production' (kilogram, None, None)>
'Fuel production' (kilogram, None, None) Exchange: 10.0 kilogram 'Carbon dioxide, fossil' (kilogram, None, ('air',)) to 'Fuel production' (kilogram, None, None)>
'Fuel production' (kilogram, None, None) Exchange: 2.0 kilogram 'Sulphur dioxide' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>
'Fuel production' (kilogram, None, None) Exchange: -50.0 kilogram 'Crude oil' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>
'Electricity production' (kilowatt hour, None, None) Exchange: 10.0 kilowatt hour 'Electricity production' (kilowatt hour, None, None) to 'Electricity production' (kilowatt hour, None, None)>
'Electricity production' (kilowatt hour, None, None) Exchange: 2.0 kilogram 'Fuel production' (kilogram, None, None) to 'Electricity production' (kilowatt hour, None, None)>
'Electricity production' (kilowatt hour, None, None) Exchange: 1.0 kilogram 'Carbon dioxide, fossil' (kilogram, None, ('air',)) to 'Electricity production' (kilowatt hour, None, None)>
'Electricity production' (kilowatt hour, None, None) Exchange: 0.1 kilogram 'Sulphur dioxide' (kilogram, None, None) to 'Electricity production' (kilowatt hour, None, None)>
---------
{'uncertainty type': 0}
{'uncertainty type': 2, 'loc': 1.0, 'scale': 1.01, 'negative': False}
{'uncertainty type': 2, 'loc': 1.0, 'scale': 1.01, 'negative': False}
{'uncertainty type': 0, 'loc': 2.0, 'scale': 2.0, 'negative': True}
{'uncertainty type': 0}
{'uncertainty type': 0}
{'uncertainty type': 0}
{'uncertainty type': 0}
{'uncertainty type': 0}
Out[7]:
[[None, None, None, None], [None, None, None, None, None], [], []]
We perform a calculation to see if everything works.
In [8]:
myact = bw.Database('ALIGNED-LCI-template').get('b8f80db7-4511-4970-af59-f40b23e733b1') # Fuel production
list(myact.exchanges())
Out[8]:
[Exchange: 100.0 kilogram 'Fuel production' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>,
Exchange: 100.0 kilogram 'diesel production, low-sulfur' (kilogram, CH, None) to 'Fuel production' (kilogram, None, None)>,
Exchange: 10.0 kilogram 'Carbon dioxide, fossil' (kilogram, None, ('air',)) to 'Fuel production' (kilogram, None, None)>,
Exchange: 2.0 kilogram 'Sulphur dioxide' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>,
Exchange: -50.0 kilogram 'Crude oil' (kilogram, None, None) to 'Fuel production' (kilogram, None, None)>]
In [9]:
mymethod = ('IPCC 2013', 'climate change', 'global warming potential (GWP100)')
el = t_db.get('3955bc3b-db9f-4d01-8510-90a551f9caeb') # Electricity production
functional_unit = {el: 1000}
lca = bw.LCA(functional_unit, mymethod)
lca.lci()
lca.lcia()
print(lca.score)
324.1823648287357