<jupyter_start><jupyter_text>Imports and exports[Eurostat](https://ec.europa.eu/eurostat/web/energy/data/energy-balances)<jupyter_code>import pandas as pd
import os
import pyxlsb
import numpy as np
tj_ktoe = 41.868
def get_variable(file, variable, col_start, col_end):
    """Reads variable for fuel in multiple sheets and converts 
    the value from ktoe to TJ.
    """
    trajectory = {}
    for year in range(2002,2019):
        df = pd.read_excel(
            file,
            engine='pyxlsb',
            sheet_name=str(year),
            skiprows=[0,1,2,3],
            index_col=1
            )   
        trajectory[year] = sum(df.loc[variable, col_start:col_end]) * tj_ktoe
    return pd.Series(trajectory)<jupyter_output><empty_output><jupyter_text>Imports<jupyter_code># Imports of bioenergy

imports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Imports', 'Bioenergy', 'Bioenergy')
        imports[f'{file[:2]}'] = data

imports.to_csv(
    'imports_bioenergy_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

imports
# Imports of solid biomass

imports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Imports', 'Primary solid biofuels', 'Primary solid biofuels')
        imports[f'{file[:2]}'] = data

imports.to_csv(
    'imports_solid_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

imports
# Imports of Biofuels
imports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Imports', 'Pure biogasoline', 'Other liquid biofuels')
        imports[f'{file[:2]}'] = data

imports.to_csv(
    'imports_biofuels_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

imports<jupyter_output><empty_output><jupyter_text>Exports<jupyter_code># Exports of bioenergy

exports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Exports', 'Bioenergy', 'Bioenergy')
        exports[f'{file[:2]}'] = data

exports.to_csv(
    'exports_bioenergy_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

exports
# Exports of solid biomass

exports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Exports', 'Primary solid biofuels', 'Primary solid biofuels')
        exports[f'{file[:2]}'] = data

exports.to_csv(
    'exports_solid_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

exports
# Exports of biofuels

exports = pd.DataFrame()

countries = ('CZ', 'AT', 'DK', 'NL', 'PL', 'SK')

for file in os.listdir('Energy-Balances-June-2020-edition'):
    if file.startswith(countries):
        data = get_variable('Energy-Balances-June-2020-edition/' + file, 'Exports', 'Pure biogasoline', 'Other liquid biofuels')
        exports[f'{file[:2]}'] = data

exports.to_csv(
    'exports_biofuels_2002_2018.csv',
    index_label='Rok',
    decimal=',',
    columns=countries,
    )

exports<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16192217289109428, 'fraction_numerical': 0.031339775398276315, 'mean_word_length': 3.8236775818639797, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 1, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '538707', 'license': 'MIT', 'n_tokens_mistral': 1517, 'n_tokens_neox': 1404, 'n_words': 262, 'path': '2020/selection/old/trade.ipynb', 'repo_name': 'jandolezal/balances', 'size': '3829'}