<jupyter_start><jupyter_text>Bioenergy from Eurostat's SHARES databaseData from the tsv tables accessed from the [Energy database](https://ec.europa.eu/eurostat/web/energy/data/database) under energy indicators as 'Share of energy from renewable sources until 2020' (nrg_ind_share). Three tsv tables for each sector (electricity, heating and cooling, transport).I will work on a tidier version of the output csv tables in another notebook.Very helpful are [Standard international energy product classification (SIEC)](http://dd.eionet.europa.eu/vocabulary/eurostat/siec/) vocabulary and [Energy balance](http://dd.eionet.europa.eu/vocabulary/eurostat/nrg_bal/) vocabulary. FuelsBiomass-based fuels I cover here (SIEC codes in brackets):* Primary solid biofuels (R5110-5150_W6000RI)* Biogases (R5300)* Liquid biofuels (R5200)* Renewable municipal waste (W6210) VariablesIf I understand it correctly in the SHARES database in the **electricity sector** I should look for 'Gross electricity generation' for biomass-based fuels, in the **heating sector** it seems as a sum of 'Final consumption in industry and others sectors' and 'Gross heat production' and in the **transport sector** the variables are probably 'Final consumption' variable for road, rail and other sectors.Depending on the sector (electricity, heating and cooling, transport) we are interested in the following variables: Electricity* Gross electricity production - Renewable Energy Directive (GEP_RED) Heating and cooling* Gross heat production - Renewable Energy Directive (GHP_RED)* Final consumption - industry and other sectors - energy use (FC_IND_OTH_E) Transport* Final consumption - other transport sector- energy use - Renewable Energy Directive (FC_TRA_OTH_E_RED)* Final consumption - transport sector - road - energy use - Renewable Energy Directive (FC_TRA_ROAD_E_RED)* Final consumption - transport sector - rail - energy use - Renewable Energy Directive (FC_TRA_RAIL_E_RED)Disclaimer: I might got something wrong. I still have to go through the whole [Energy balance guide](https://ec.europa.eu/eurostat/documents/38154/4956218/ENERGY-BALANCE-GUIDE-DRAFT-31JANUARY2019.pdf/cf121393-919f-4b84-9059-cdf0f69ec045). I hope I do not aggregate variables which should be kept separate or miss some variable to add it to the bioenergy totals.<jupyter_code>import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
csv_output_dir = 'output'

if not os.path.exists(csv_output_dir ):
    os.mkdir(csv_output_dir )
# Standard international energy product classification (SIEC)
# https://dd.eionet.europa.eu
# Dictionary from siec codes to human readable labels
siec = pd.read_csv('siec.csv')
siec
fuels_dict = {k: v for k, v in zip(siec['Label'], siec['Notation'])}
# We are interested in renewables and bioenergy
selected_fuels = [
    'Total',
    'Renewables and biofuels',
    'Primary solid biofuels',
    'Biogases',
    'Liquid biofuels',
    'Renewable municipal waste',
    ]

selected_fuels = [
    'Primary solid biofuels',
    'Biogases',
    'Liquid biofuels',
    'Renewable municipal waste',
    ]

selected_fuel_codes = [v for k,v in fuels_dict.items() if k in selected_fuels]
selected_fuel_codes<jupyter_output><empty_output><jupyter_text>Electricity<jupyter_code># The source file is hybrid with tabs and commas as separators
el = pd.read_table('nrg_ind_ured.tsv', delimiter='[\t,]+')
el.rename(columns={'geo\\time': 'geo'}, inplace=True)
el.rename(columns={str(year) + ' ': year for year in range(2004, 2019)}, inplace=True)
el.rename(columns={'2004': 2004}, inplace=True)
el['geo'].unique()
el.head()
# https://stackoverflow.com/questions/26577516/how-to-test-if-a-string-contains-one-of-the-substrings-in-a-list-in-pandas

needed_fuels = el['siec'].str.fullmatch('|'.join(selected_fuel_codes))
gep = el['nrg_bal'].str.fullmatch('GEP_RED')
el[needed_fuels].sort_values('geo')
bioel = el[needed_fuels & gep].groupby(['geo']).sum() * 3.6 / 41.868
bioel = bioel.transpose().sort_index()
bioel
bioel.sort_index()
# TJ
bioel_tj = bioel * 41.868
bioel.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioelectricity_ktoe.csv'), decimal=','
)
bioel_tj.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioelectricity_tj.csv'), decimal=','
)

bioel['EU27_2020'].plot(title='Bioelectricity EU27', ylabel='GEP_RED (ktoe)', xlabel='year')<jupyter_output><empty_output><jupyter_text>Heating and cooling sector<jupyter_code>hc = pd.read_table('nrg_ind_urhcd.tsv', delimiter='[\t,]+')
hc.rename(columns={'geo\\time': 'geo'}, inplace=True)
hc.rename(columns={str(year) + ' ': year for year in range(2004, 2019)}, inplace=True)
hc.rename(columns={'2004': 2004}, inplace=True)

for col in range(2004,2019):
    hc[col] = pd.to_numeric(hc[col], errors='coerce')

needed_fuels = hc['siec'].str.fullmatch('|'.join(selected_fuel_codes))
ghp = hc['nrg_bal'].str.fullmatch('GHP_RED')
fc = hc['nrg_bal'].str.fullmatch('FC_IND_OTH_E')
hc[needed_fuels & (fc | ghp)].sort_values('geo')
biohc = hc[needed_fuels & (fc | ghp)].groupby('geo').sum()
biohc = biohc.transpose().sort_index()
biohc
biohc_tj = biohc * 41.868
biohc.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioheat_ktoe.csv'), decimal=',')

biohc_tj.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioheat_tj.csv'), decimal=',')

biohc['EU27_2020'].plot(title='Bioheat', ylabel='FC_IND_OTH_E + GHP_RED (ktoe)', xlabel='year')<jupyter_output><empty_output><jupyter_text>Transport<jupyter_code>tr = pd.read_table('nrg_ind_urtd.tsv', delimiter='[\t,]+')
tr.rename(columns={'geo\\time': 'geo'}, inplace=True)
tr.rename(columns={str(year) + ' ': year for year in range(2004, 2019)}, inplace=True)
tr.rename(columns={'2004': 2004}, inplace=True)

for col in range(2004,2019):
    tr[col] = pd.to_numeric(tr[col], errors='coerce')

needed_fuels = tr['siec'].str.fullmatch('|'.join(selected_fuel_codes))
selected_variables = ['FC_TRA_OTH_E_RED', 'FC_TRA_RAIL_E_RED', 'FC_TRA_ROAD_E_RED']
fc = tr['nrg_bal'].str.fullmatch('|'.join(selected_variables))

tr[needed_fuels & fc].sort_values('geo')
biotr = tr[needed_fuels & fc].groupby('geo').sum()
biotr = biotr.transpose().sort_index()
biotr
biotr_tj = biotr * 41.868
biotr_tj
biotr.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'biotrans_ktoe.csv'), decimal=',')
biotr_tj.to_csv(os.path.join(os.path.abspath(csv_output_dir ),'biotrans_tj.csv'), decimal=',')

biotr['EU27_2020'].plot(title='Biotransport', ylabel=' FC_TRA_ROAD_E_RED (ktoe)', xlabel='year')<jupyter_output><empty_output><jupyter_text>Bioenergy (all sectors)<jupyter_code>bioenergy = bioel + biohc + biotr
bioenergy_tj = bioenergy * 41.868

bioenergy.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioenergy_ktoe.csv'), decimal=',')
bioenergy_tj.to_csv(os.path.join(os.path.abspath(csv_output_dir ), 'bioenergy_tj.csv'), decimal=',')

bioenergy['EU27_2020'].plot(title='Bioenergy', ylabel=' Bioenergy from SHARES (ktoe)', xlabel='year')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14326771087799972, 'fraction_numerical': 0.027733869808880585, 'mean_word_length': 6.451820128479658, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '130485', 'license': 'MIT', 'n_tokens_mistral': 2702, 'n_tokens_neox': 2487, 'n_words': 543, 'path': 'eu/untidy/shares.ipynb', 'repo_name': 'jandolezal/shares', 'size': '6959'}