<jupyter_start><jupyter_text>Combining COMTRADE and Flaring dataGGFR have a global flare datasets derived from Elvidge at point locations. They further map these point locations to operators via oil field mapping. This means there are a number of datasets:1. Point locations of flaring2. Mapped oil field locations (from Woods Mackenzie)3. Tabulated national accounts (of flaring)Harshit suggested there could be an interesting combination of the COMTRADE data to see what the comparison of nationally generated flaring vs imported flaring.Flaring Supply Index (FSI) is a concept proposed for an indicator that would be able to describe and compare the ’flaring footprint’ of different oil supply corridors from their source to the destination country’s border. The formula(s) for such an index may be described as follows.Flare Supply Index of a Country X= (∑_(i=1)^n▒〖Crude Import by X from Country Yi * Flare Intensity of Country Yi〗)/(Total Crude Imported by Country X) DefinitionsFlare Intensity - ???  TOE - Tonnes of oil equivalent TO DO1. TOE is not the appropriate flaring comparison for intensity, figure out what it is2. Right now, the intensity is taken just from 2019; should use yearly data<jupyter_code>import sys, os, importlib
import json, geojson, pycountry

import pandas as pd
import geopandas as gpd
import numpy as np
# Define input variables
# COMTRADE data can be downloaded here - https://datacatalog.worldbank.org/dataset/global-comtrade-flows-data
#comtrade_file = '/home/wb411133/data/Projects/INFRA/FLOWS/OIL_CRUDE_ONLY_2021/GEOJSON/country_flows_imports.geojson'
comtrade_file = "/home/wb411133/data/Projects/INFRA/FLOWS/Oil UNComtrade 0115.csv"
flare_data = '/home/public/Data/GLOBAL/INFRA/FLARING/2019_flare_catalog.csv'
flare_national_data = '/home/public/Data/GLOBAL/INFRA/FLARING/National_flaring_2019.csv'
out_folder = "/home/wb411133/data/Projects/INFRA/FLARING/Data"
if not os.path.exists(out_folder):
    os.makedirs(out_folder)

inCom = gpd.read_file(comtrade_file)
inFlare = pd.read_csv(flare_data)
inF = pd.read_csv(flare_national_data)<jupyter_output><empty_output><jupyter_text>The location specific flare data is not necessary for calculating the FSI, but it could be used in other calculations<jupyter_code>inFlare.head()
# Limit the flaring summaries to 2019
flare_2019 = inF.filter(regex="2019")
flare_2019.columns = ["Volume","Intensity"]
flare_2019['country'] = inF['country']
flare_2019.head()
def get_country(x):
    ''' Convert country name to ISO3
    
    :param: x [string] - name of country to convert
    :returns: [string] - ISO3 code of country
    '''
    try:
        res = pycountry.countries.search_fuzzy(x)
        return(res[0].alpha_3)
    except:
        return("NA")

#xx = get_country("Canada")
flare_2019['ISO3'] = flare_2019['country'].apply(lambda x: get_country(x))
flare_2019.head()
# 5 of the country names don't grok, set them manually

#flare_2019.loc[flare_2019['ISO3'] == "NA"]
flare_2019.iloc[16, 3] = "COG"
flare_2019.iloc[30, 3] = "ARE"
flare_2019.iloc[44, 3] = "COD"
flare_2019.iloc[60, 3] = "NER"
flare_2019.loc[flare_2019['ISO3'] == "NA"]
#Combine the flare intensity information with the comtrade data
in_flaring_combined = pd.merge(inCom, flare_2019, left_on="Partner ISO", right_on="ISO3", how="left")
#Drop unnecessary columns
in_flaring_combined = in_flaring_combined.drop(['geometry', 'country', 'ISO3'], axis=1)

def tryFloat(x):
    try:
        return(float(x))
    except:
        return(0.)

# Multiply the TOE from the comtrade data by the intensity information
### TODO: TOE is not the unit appropriate for intensity calculations; however, the relative rank is still useful
in_flaring_combined['Qty'] = in_flaring_combined['Qty'].apply(tryFloat)
in_flaring_combined['Trade Value (US$)'] = in_flaring_combined['Trade Value (US$)'].apply(tryFloat)
in_flaring_combined['Intensity'] = in_flaring_combined['Intensity'].apply(tryFloat)
in_flaring_combined['Qty_I'] = in_flaring_combined['Qty'] * in_flaring_combined['Intensity']
tempD = in_flaring_combined.loc[in_flaring_combined['Reporter ISO'].isin(['AUS','USA','CAN', 'NZL','JPN'])]
tempD = tempD.loc[tempD['Commodity Code'] == '333']
tempD.loc[:,['Reporter ISO', 'Partner ISO', 'Year', 'Qty', 'Trade Value (US$)']].groupby(['Reporter ISO', 'Year']).sum()
in_flaring_combined.head()
in_flaring_combined.to_csv(os.path.join(out_folder, "FLARING_TRADE_CRUDEONLY_COMBINED_ALLTRADE_JAN2021.csv"))
# Aggregate imported TOE, Value and TOE by import intensity
agg = {'TOE':'sum','Trade Value (US$)':'sum', 'Intensity':'mean','TOE_I':'sum'}
res = in_flaring_combined.groupby(['Reporter ISO', "Year"]).aggregate(agg)
res = res.reset_index()
res.head(20)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11755983901715739, 'fraction_numerical': 0.031561109934335946, 'mean_word_length': 5.933920704845815, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '304939', 'license': 'CC0-1.0', 'n_tokens_mistral': 1709, 'n_tokens_neox': 1557, 'n_words': 436, 'path': 'src/INFRA_SAP/Notebooks/DP_INFRA_B_MappingImportedFlaring.ipynb', 'repo_name': 'worldbank/Khyber-Pakhtunkhwa-Accessibility-Analysis', 'size': '4721'}