<jupyter_start><jupyter_text>EGEDA cleaning script For cleaning the EGEDA data sent by Edito: 00_APEC_EGEDA_20190925.xlsx<jupyter_code># import packages

import numpy as np
import pandas as pd
# read raw data

RawEGEDA = pd.read_excel('../data/raw/EGEDA/00_APEC_EGEDA_20190925.xlsx', 
                         sheet_name = None, 
                         na_values = ['x', 'X', ''])
# inspect a dataframe
RawEGEDA['01_AUS'].head(2)
# define year range

years = list(range(1980, 2017, 1))
df_list = []

economies = RawEGEDA.keys()

for economy in economies:
    _df_economy = RawEGEDA[economy]
    _df = pd.melt(_df_economy, 
                  id_vars=['Product Code','Item Code'], 
                  value_vars=years, 
                  var_name='Year',
                  value_name='Value'
                 )
    _df['Economy'] = economy 
    df_list.append(_df)

df = pd.concat(df_list)    
df.head(2)
df.info()
# rename using APEC approved abbreviations

EconomyNames = {
        '03_CAN':'03_CDA',
        '20_USA':'20_USA'
        }
# replace with APEC approved economy names

df = df.replace(EconomyNames)
df.head(2)
## create dictionary of EGEDA Product Codes and APERC Fuel codes

Fuelcodes = {
        '1 Coal':'Coal',
        '1.1 Hard coal':'CoalH',
        '1.1.1 Coking coal':'CoalHC',   
        '1.1.2 Other bituminous coal':'CoalHB',
        '1.1.3 Sub-bituminous coal':'CoalHS',
        '1.2 Anthracite':'CoalA',
        '1.3 Lignite':'CoalL',
        '1.4 Peat':'CoalO',
        '2 Coal products':'CoalP',
        '2.1 Coke oven coke':'CoalPC',
        '2.2 Coke oven gas':'CoalPO',
        '2.3 Blast furnace gas':'CoalPF',
        '2.4 Oxygen steel furnace gas':'CoalPS',
        '2.5 Patent fuel':'CoalPP',
        '2.6 Coal tar':'CoalPT',
        '2.7 BKB/PB':'CoalPB',
        '3 Crude oil & NGL':'Oil',
        '3.1 Crude Oil':'OilC',
        '3.2 Natural gas liquids':'OilN',
        '3.3 Refinery feedstocks':'OilOR',
        '3.4 Additives/oxygenates':'OilOA',
        '3.5 Other hydrocarbons':'OilOO',
        '4 Petroleum products':'PetP',
        '4.1 Gasoline':'PetPGx',
        '4.1.1 Motor gasoline':'PetPG',
        '4.1.2 Aviation gasoline':'PetPJG',
        '4.2 Naphtha':'PetPN',        
        '4.3 Jet fuel':'PetPJ',        
        '4.3.1 Gasoline type jet fuel':'PetPJO',
        '4.3.2 Kerosene type jet fuel':'PetPJK',
        '4.4 Other kerosene':'PetPK',
        '4.5 Gas/diesel oil':'PetPD',
        '4.6 Fuel oil':'PetPF',
        '4.7 LPG':'PetPL',        
        '4.8 Refinery gas (not liq.)':'PetPR',        
        '4.9 Ethane':'PetPE',        
        '4.10 Other petroleum products':'PetPO',
        '4.10.1 White spirit SBP':'PetPOW',
        '4.10.2 Lubricants':'PetPOL',
        '4.10.3 Bitumen':'PetPOB',
        '4.10.4 Paraffin waxes':'PetPOP',
        '4.10.4 Paraffin  waxes':'PetPOP',
        '4.10.5 Petroleum coke':'PetPOC',
        '4.10.6 Other products':'PetPOO',
        '5 Gas':'Gas',        
        '5.1 Natural gas':'GasN',
        '5.2 LNG':'GasL',
        '5.3 Gas works gas':'GasO',
        '6 Hydro':'RenH',        
        '7 Nuclear':'Nuc',        
        '8 Geothermal, solar etc.':'RenNRE',        
        '8.1 Geothermal power':'RenGE',
        '8.2 Other power':'RenOO',
        '8.2.1 Photovoltaic':'RenSE',
        '8.2.2 Tide, wave, ocean':'RenO',
        '8.2.3 Wind':'RenW',
        '8.2.4 Solar':'RenSO',
        '8.3 Geothermal heat':'RenGH',
        '8.4 Solar heat':'RenSH',
        '9 Others':'Oth',
        '9.1 Fuel wood & woodwaste':'RenBSF',
        '9.2 Bagasse':'RenBSB',
        '9.3 Charcoal':'RenBSC',
        '9.4 Other biomass':'RenBSO',
        '9.5 Biogas':'RenBG',
        '9.6 Industrial waste':'OthI',
        '9.7 Municipal solid waste':'RenMSW',
        '9.7.1 Municipal solid waste (renewable)':'RenBSW',
        '9.7.2 Municipal solid waste (non-renewable)':'OthM',
        '9.8 Liquid biofuels':'RenBL',
        '9.8.1 Biogasoline':'RenBLE',
        '9.8.2 Biodiesel':'RenBLD',
        '9.8.3 Bio jet kerosene':'RenBLJ',
        '9.8.4 Other liquid biofuels':'RenBLO',
        '9.9 Other sources':'OthO',        
        '10 Electricity':'Elec',
        '11 Heat':'Heat',
        '12 Total':'Tot',
        '13 Total renewables':'TotRen'       
        }
# code to replace fuel abbreviations

df = df.replace(Fuelcodes)

df = df.rename(columns = {'Product Code':'Fuel'})
df.head(2)
df['Fuel'].unique()
df_pivot = df.pivot_table(values='Value',index=['Economy','Year','Item Code'],columns='Fuel')
df_pivot['RenGE']
df_pivot['RenG'] = df_pivot['RenGE'].add(df_pivot['RenGH'])
df_pivot['RenS'] = df_pivot['RenSE'].add(df_pivot['RenSH']).add(df_pivot['RenSO'])
df_pivot['RenBS'] = (df_pivot['RenBSF']
                     .add(df_pivot['RenBSB'])
                     .add(df_pivot['RenBSC'])
                     .add(df_pivot['RenBSO'])
                    )
df_pivot['RenB'] = df_pivot['RenBS'].add(df_pivot['RenBL']).add(df_pivot['RenBG'])
df_pivot.head(1)
stacked = df_pivot.stack()
df_agg = pd.DataFrame(stacked,columns=['Value'])
df_agg = df_agg.reset_index()
df_agg = df_agg.rename({'Value':'ktoe'},axis=1)
conversion_to_PJ = 41.868
#http://w.astro.berkeley.edu/~wright/fuel_energy.html

df_agg['PJ'] = np.multiply(df_agg['ktoe'],conversion_to_PJ)
df_final = pd.melt(df_agg,id_vars=['Economy','Year','Item Code','Fuel'],
                   value_vars=['ktoe','PJ'],
                   var_name='Units',
                   value_name='Value')
df_final.head(2)
# write to csv

df_final.to_csv("../data/final/EGEDA_2019_09_25_tidy.csv", index=False)
#optional: export fuel list

#fuels = pd.DataFrame(dfResults.index.unique(level = -1))
#fuels.to_csv("../data/final/fuel_list_2019_09_25.csv", index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17480451781059947, 'fraction_numerical': 0.043266724587315376, 'mean_word_length': 2.424152290303391, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '154021', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2401, 'n_tokens_neox': 2124, 'n_words': 395, 'path': 'notebooks/EGEDA.ipynb', 'repo_name': 'asia-pacific-energy-research-centre/CO2-Report', 'size': '5755'}