<jupyter_start><jupyter_text>Data wrangling of the IEA's *Net Zero by 2050 Roadmap* (2021)This notebook transform the data supporting theIEA's [Net Zero by 2050](https://www.iea.org/reports/net-zero-by-2050) flagship report(published May 2021)  to the IAMC format using the **pyam** package ([read the docs](https://pyam-iamc.readthedocs.io)).<jupyter_code>import pandas as pd
import pyam
index = dict(model="IEA", scenario="Netzero2050")<jupyter_output><empty_output><jupyter_text>Annex AAnnex A has the scenario projections supporting and quantifying the "Net Zero by 2050 Roadmap".This script uses the files as published on the IEA data page on May 18, 2021.  Unfortunately, the IEA does not implement clear versioning of the files, so this script may not work as is if the IEA decides to update these files.<jupyter_code>file = "data/NZE2021_AnnexA.xlsx"
df_list = []  # this list will hold the individual refactored IamDataFrames<jupyter_output><empty_output><jupyter_text>Global energy supply ("World_Balance") Total Energy SupplyRead the relevant section of the World_Balance sheet and cast to an **IamDataFrame**.<jupyter_code>balances_totals = pd.read_excel(file, sheet_name="World_Balance", header=4, usecols="A:F")[0:17]
df_balances_totals = pyam.IamDataFrame(balances_totals, **index, region="World", variable="Unnamed: 0", unit="EJ")<jupyter_output><empty_output><jupyter_text>Rename the fuels to IAMC-style variables similar to those used in the IPCC AR6.<jupyter_code>variable_mapping = {
    "Total energy supply" : "Primary Energy",
    "Modern gaseous bioenergy": "Bioenergy|Gaseous",
    "Modern liquid bioenergy": "Bioenergy|Liquid",
    "Modern solid bioenergy": "Bioenergy|Solid",
    "Renewables": "Renewables (excluding traditional biomass)",
    "Traditional use of biomass": "Biomass|Traditional",
    "Coal with CCUS": "Coal|CCUS",
    "Unabated coal": "Coal|Unabated",
    "Natural gas with CCUS": "Natural Gas|CCUS" ,
    "Unabated natural gas": "Natural Gas|Unabated",
    "of which non-energy use": "Oil|Non-energy Use"
}

df_balances_totals.rename(variable=variable_mapping, inplace=True)<jupyter_output><empty_output><jupyter_text>Prefix "Primary Energy" to variables.<jupyter_code>prefix = "Primary Energy"

df_balances_totals.rename(
    variable=dict([(v, f"{prefix}|{v}") for v in df_balances_totals.variable if v != prefix]),
    inplace=True
)<jupyter_output><empty_output><jupyter_text>Compute aggregates of timeseries data that are only given at sub-sectoral detail<jupyter_code>agg_vars = ["Primary Energy|Natural Gas", "Primary Energy|Coal", "Primary Energy|Bioenergy"]

for v in agg_vars:
    df_balances_totals.aggregate(v, append=True)<jupyter_output><empty_output><jupyter_text>Show the timeseries data in the IAMC format.<jupyter_code>df_balances_totals.timeseries()<jupyter_output><empty_output><jupyter_text>Append the cleaned-up data to the list.<jupyter_code>df_list.append(df_balances_totals)<jupyter_output><empty_output><jupyter_text>Electricity and Heat SupplyRead the relevant section of the World_Balance sheet and cast to an **IamDataFrame**.<jupyter_code>balances_elec_heat = pd.read_excel(file, sheet_name="World_Balance", header=4, usecols="A:F")[17:32]
df_balances_elec_heat = pyam.IamDataFrame(balances_elec_heat, **index, region="World", variable="Unnamed: 0", unit="EJ")<jupyter_output><empty_output><jupyter_text>Rename the fuels to IAMC-style variables similar to those used in the IPCC AR6.<jupyter_code>variable_mapping = {
    "Electricity and heat sectors": "Secondary Energy|Electricity and Heat",
    "Coal with CCUS": "Coal|CCUS",
    "Unabated coal": "Coal|Unabated",
    "Natural gas with CCUS": "Natural Gas|CCUS" ,
    "Unabated natural gas": "Natural Gas|Unabated",
}

df_balances_elec_heat.rename(variable=variable_mapping, inplace=True)<jupyter_output><empty_output><jupyter_text>Prefix "Secondary Energy|Electricity and Heat" to variables.<jupyter_code>prefix = "Secondary Energy|Electricity and Heat"

df_balances_elec_heat.rename(
    variable=dict([(v, f"{prefix}|{v}") for v in df_balances_elec_heat.variable if v != prefix]),
    inplace=True
)<jupyter_output><empty_output><jupyter_text>Compute aggregates of timeseries data that are only given at sub-sectoral detail<jupyter_code>agg_vars = [f"Secondary Energy|Electricity and Heat|{v}" for v in ["Natural Gas", "Coal"]]

for v in agg_vars:
    df_balances_elec_heat.aggregate(v, append=True)<jupyter_output><empty_output><jupyter_text>Append the cleaned-up data to the list.<jupyter_code>df_list.append(df_balances_elec_heat)<jupyter_output><empty_output><jupyter_text>Compile individual components of the reformatted data and export as csv<jupyter_code>df = pyam.concat(df_list)
df.to_csv("data/iea_netzero.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11162207357859531, 'fraction_numerical': 0.010660535117056856, 'mean_word_length': 7.350785340314136, 'pattern_counts': {'":': 15, '<': 57, '<?xml version=': 0, '>': 57, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '376501', 'license': 'Apache-2.0', 'n_tokens_mistral': 1734, 'n_tokens_neox': 1629, 'n_words': 395, 'path': 'data-wrangler.ipynb', 'repo_name': 'danielhuppmann/iea-netzero2050-datawrangler', 'size': '4784'}