import math
import statistics
import numpy as np

        
        Copy
    

    	
            
import pandas as pd
# This line will ensure that all columns of our data-frames are always shown:
pd.set_option('display.max_columns', None)

        
        Copy
    

    	
            
# xlrd and openpyxl is needed to export data-frames to Excel files.
# The former doesn't need to be imported and can be installed via 'pip install xlrd'
# import openpyxl

        
        Copy
    

    	
            
# need to ' pip install pickle-mixin '
import pickle

        
        Copy
    

    	
            
import DatastreamDSWS as DSWS
 
# We can use our Refinitiv's Datastream Web Socket (DSWS) API keys that allows us to be identified by
# Refinitiv's back-end services and enables us to request (and fetch) data:
# Credentials are placed in a text file so that it may be used in this code without showing it itself.
(DSWS_username, DSWS_password) = (open("Datastream_username.txt","r"),
                                  open("Datastream_password.txt","r"))
 
ds = DSWS.Datastream(username = str(DSWS_username.read()),
                     password = str(DSWS_password.read()))
 
# It is best to close the files we opened in order to make sure that we don't stop any other
# services/programs from accessing them if they need to.
DSWS_username.close()
DSWS_password.close()
 
 
# # Alternatively one can use the following:
# import getpass
# dsusername = input()
# dspassword = getpass.getpass()
# ds = DSWS.Datastream(username = dsusername, password = dspassword)

        
        Copy
    

    	
            
import sys # ' sys ' is only needed to display our Pyhon version
print("This code is running on Python version " + sys.version[0:5])

        
        Copy
    

    	
            
import numpy as np
import pandas as pd
import openpyxl
for i,j in zip(["np", "pd", "openpyxl"], [np, pd, openpyxl]):
    print("The " + str(i) + " library imported in this code is version: " + j.__version__)

        
        Copy
    

    	
            from datetime import date
        
        Copy
    

    	
            
def Point(value, # ' value ' as an integer or float between 0 and 1 (inclusive)
          polarity = "Positive"): # ' polarity ' informs us if we are grading the value as 'higher is better' (i.e.: positively polarised) or 'lower is better'
    
    if math.isnan(value):
        # This if function captures the eventuality when we don't have a value passed through this function
        result = np.nan
    elif value >= 0.9:
        result = 10
    elif value >= 0.8:
        result = 9
    elif value >= 0.7:
        result = 8
    elif value >= 0.6:
        result = 7
    elif value >= 0.5:
        result = 6
    elif value >= 0.4:
        result = 5
    elif value >= 0.3:
        result = 4
    elif value >= 0.2:
        result = 3
    elif value >= 0.1:
        result = 2
    elif value >= 0.0:
        result = 1
    
    if polarity == "Positive":
        # This function this far assumes positive polarity
        return result
    elif polarity == "Negative":
        # We now can look into negatively polarised data
        if math.isnan(value):
            return result
        elif result <= 2:
            return 10
        elif value >= 1:
            return 1
        else:
            return 12 - result

        
        Copy
    

    	
            
# This file includes polarity information as well as series codes, the ' .iloc[:,6:-1] ' bellow ensures that we only collect the latter.
df = pd.read_csv("ESG-DS.csv", header = 1, index_col = 6).iloc[:,6:]
df

        
        Copy
    

    	
            
Country_Values_Table_Columns = [
    '1.1.1', '1.1.1.1', '1.2.1', '1.2.2', '1.3.1', '1.5.3', '1.A.1', '1.A.2', '1.A.2.1', '1.B.1',
    '2.1.1', '2.1.2', '2.2.1', '2.2.2', '2.2.2.1', '2.A.1', '2.A.2',
    '3.1.1', '3.1.2', '3.2.1', '3.2.2', '3.3.1', '3.3.1.1', '3.3.1.2', '3.3.2', '3.7.2', '3.8.2', '3.9.2', '3.A.1', '3.A.1.1', '3.C.1',
    '4.1.1', '4.1.1.1', '4.1.1.2', '4.1.1.3', '4.1.1.4', '4.1.1.5', '4.4.1', '4.5.1', '4.6.1', '4.6.1.1', '4.6.1.2', '4.6.1.3', '4.6.1.4', '4.6.1.5', '4.A.1',
    '5.1.1', '5.5.1', '5.6.1', '5.6.1.1', '5.B.1', '5.B.1.1', '5.B.1.2',
    '6.1.1', '6.2.1', '6.4.2',
    '7.1.1', '7.1.2', '7.2.1', '7.2.1.1', '7.3.1', '7.3.1.1', '7.3.1.2',
    '8.1.1', '8.2.1', '8.2.1.1', '8.2.1.2', '8.2.1.3', '8.5.2', '8.5.2.1', '8.5.2.2', '8.5.2.3', '8.5.2.4', '8.5.2.5', '8.6.1', '8.6.1.1', '8.6.1.2', '8.7.1', '8.7.1.1', '8.7.1.2', '8.7.1.3', '8.7.1.4', '8.7.1.5', '8.7.1.6', '8.7.1.7', '8.7.1.8', '8.8.2', '8.10.1', '8.10.1.1', '8.B.1',
    '9.1.2.', '9.1.2..1', '9.1.2..2', '9.2.1.', '9.2.1..1', '9.2.1..2', '9.2.1..3', '9.2.2.', '9.4.1', '9.5.1', '9.5.2', '9.C.1',
    '10.1.1', '10.1.1.1', '10.2.1', '10.3.1', '10.3.1.1', '10.3.1.2', '10.3.1.3', '10.4.1', '10.5.1', '10.5.1.1', '10.5.1.2',
    '11.6.2', '11.6.2.1',
    '12.7.1', '12.C.1',
    '13.2.1', '13.2.1.1', # There indeed is no 14th category
    '15.1.1', '15.1.2', '15.1.2.1', '15.1.2.2', '15.2.1', '15.3.1', '15.5.1', '15.9.1', '15.9.1.1', '15.9.1.2', '15.9.1.3', '15.A.1', '15.A.1.1', '15.B.1', '15.B.1.1',
    '16.1.1', '16.1.2', '16.1.2.1', '16.1.2.2', '16.2.2', '16.4.1', '16.4.2', '16.5.1', '16.5.1.1', '16.5.2', '16.5.2.1', '16.6.2', '16.9.1', '16.10.2', '16.A.1', '16.B.1', '16.B.1.1',
    '17.1.1', '17.1.1.1', '17.1.1.2', '17.1.1.3', '17.3.1', '17.4.1', '17.6.2', '17.8.1']

        
        Copy
    

    	
            
# yields the elements in `Country_Values_Table_Columns` that are NOT in `df.columns`.
columns_in_Country_Values_Table_not_in_df = list(np.setdiff1d(Country_Values_Table_Columns,
                                                              df.columns))

        
        Copy
    

    	
            
# First: create a pandas data-frame to be populated with our data.
Country_Values_Table = pd.DataFrame(index = df.index, columns = Country_Values_Table_Columns)

        
        Copy
    

    	
            
# Second: Collect data in batches:
 
for i in range(len(df.columns)):
    
    # List and placeholders to be populated:
    data_points = [] # List to be populated
    count = 0 # Dynamic placeholder
    
    # For each set of 50 countries (or less, lastly)
    # The ' math.ceil( ' is there to make sure we run through and collect DSWS data for the last batch of codes even if it summs up to less than 50 codes
    for times in range(math.ceil(len(df.index)/50)):
        
        # Create a string of the 50 (or less) codes to pull from DSWS in one go:
        codes = str( # Change the following list into a sting
            list( # Change the following pandas data-frame into a list
                df.iloc[count:(count+50), # From the data-frame ' df ' choose the row that starts at the multiple of 50 we left of from and ends 50 rows later
                        i] # From the same ' df ' choose only from column ' i ', i.e.: go from ESG category to ESG category
            )).replace("[", "").replace("]", "").replace("'", "") # Finally: replace the scuare brackets and (single) inverted commas left off from the list 'grammar'
        
        sum_placeholder = len(data_points) + len(codes.split(","))
        
        # Collect data from DSWS for the ' codes ' list of country ESG category codes
        batch = ds.get_data(tickers = codes, fields = "X", start = '2000-01-01', freq = 'Y')
        
        # Now we can collect the last populated value for each country's SG category called from DSWS
        for k in batch.columns:
            try:
                data_points.append(float(batch[k].dropna().iloc[-1]))
            except:
                data_points.append(np.nan)
            
        count += 50
    
    # Not all countries report for all ESG cattegories.
    # The following if statement will account for when all of the coutries encapculated
    # in ' data_points ' do not repport for the ESG category 'i' in our loop. 
    if sum_placeholder != len(data_points):
        for times in range(sum_placeholder - len(data_points)):
            data_points.append(np.nan)
    
    Country_Values_Table[df.columns[i]] = data_points

        
        Copy
    

    	
            Country_Values_Table
        
        Copy
    

    	
            
pickle_out = open("ESG-DS.pickle","wb")
pickl = (df, Country_Values_Table_Columns,
         columns_in_Country_Values_Table_not_in_df,
         Country_Values_Table)
pickle.dump(pickl, pickle_out)
pickle_out.close()

        
        Copy
    

    	
            
# pickle_in = open("ESG-DS.pickle","rb")
# df, Country_Values_Table_Columns, columns_in_Country_Values_Table_not_in_df, Country_Values_Table = pickle.load(pickle_in)
# pickle_in.close() # We ought to close the file we opened to allow any other programs access if they need it.

        
        Copy
    

    	
            Country_Values_Table2 = Country_Values_Table.copy()
        
        Copy
    

    	
            
# Going through the mathematical manipulation:
Country_Values_Table2["5.B.1.2"] = Country_Values_Table2["5.B.1"] / Country_Values_Table2["5.B.1.1"]
# Removing unnecessary columns:
Country_Values_Table2 = Country_Values_Table2.drop(columns = ["5.B.1", "5.B.1.1"])

        
        Copy
    

    	
            
Country_Values_Table2["7.3.1.2"] = Country_Values_Table2["7.3.1"] / Country_Values_Table2["7.3.1.1"]
Country_Values_Table2 = Country_Values_Table2.drop(columns = ["7.3.1", "7.3.1.1"])

        
        Copy
    

    	
            
Country_Values_Table2["8.2.1.3"] = Country_Values_Table2["8.2.1.2"] / (Country_Values_Table2["8.2.1"] * Country_Values_Table2["8.2.1.1"])
Country_Values_Table2 = Country_Values_Table2.drop(columns = ["8.2.1.2", "8.2.1" , "8.2.1.1"])

        
        Copy
    

    	
            
Country_Values_Table2["9.2.1..3"] = Country_Values_Table2["9.2.1..1"] / Country_Values_Table2["9.2.1..2"]
Country_Values_Table2 = Country_Values_Table2.drop(columns = ["9.2.1..1", "9.2.1..2"])

        
        Copy
    

    	
            
Country_Values_Table2["16.1.2.2"] = Country_Values_Table2["16.1.2"] / Country_Values_Table2["16.1.2.1"]
Country_Values_Table2 = Country_Values_Table2.drop(columns = ["16.1.2", "16.1.2.1"])

        
        Copy
    

    	
            
Country_Points_Table1 = pd.DataFrame(index = df.index)
 
# NOTE THAT THIS IS NOT THE SAME WRANKING AS IN THE EXCEL SHEET, BUT IT IS A MORE MATHEMATICALLY COMMON AND LESS AMBIGUOUS ONE
for j in range(len(Country_Values_Table2.columns)):
    Country_Points_Table1[Country_Values_Table2.columns[j]] = list(Country_Values_Table2.iloc[:,j].rank(method = "dense",
                                                                                                        na_option = "keep",
                                                                                                        pct = True))
 
Country_Points_Table1.head()

        
        Copy
    

    	
            
Country_Points_Table2 = pd.DataFrame(index = Country_Values_Table2.index)
 
 
for j in range(len(Country_Values_Table2.columns)):
    Country_Points_Table2_column_j = [] # Create a list to be populated
    for i in range(len(Country_Values_Table2.index)):
        
        if math.isnan(Country_Values_Table2.iloc[i,j]):
            # Accounting fo rhte possibility that we did not collect a value
            val = np.nan
            
        else:
            
            # The following 3 lines are used to recreate Excel's ' PERCENTRANK(...) ' function which is different to
            # median percentage, percentile, ' scipy.stats.percentileofscore ' and ' pd.DataFrame.rank(pct = True)) '.
            # Note also that valus might differ slightly due to (backend) rounding errors on Excel
            array_of_lower_vals = Country_Values_Table2.iloc[:,j][Country_Values_Table2.iloc[:,j] < Country_Values_Table2.iloc[i,j]]
            column_len_no_na = len(Country_Values_Table2.iloc[:,j].dropna()) - 1
            val = len(array_of_lower_vals) / column_len_no_na
        
        Country_Points_Table2_column_j.append(val)
    
    Country_Points_Table2[Country_Values_Table2.columns[j]] = Country_Points_Table2_column_j
 
Country_Points_Table2.head()

        
        Copy
    

    	
            
# You will need the ' ESG-DS.csv ' file
Polarity = pd.read_csv("ESG-DS.csv", header = 1).iloc[:,1:3].dropna()

        
        Copy
    

    	
            Polarity
        
        Copy
    

    	
            
# Lists to be populated.
Negatives, Positives = [], []
 
# Copy of the ' Polarity ' table to delaminate 'before' and 'after' this point in the code
Polarity_temp = Polarity.copy()
 
for i in range(len(Country_Points_Table2.columns)):
    for j in range(len(Polarity_temp["Country Points Table full"])):
        if Country_Points_Table2.columns[i].split(".")[0:3] == Polarity_temp["Country Points Table full"][j].split(".")[0:3]:
            # For each ESG category in ' Country_Points_Table2 ', find this category in ' Polarity_temp '
            # and save the placement of the specified polarity of that category.
            if Polarity_temp.polar[j] == "Negative":
                Negatives.append(j)
            if Polarity_temp.polar[j] == "Positive":
                Positives.append(j)
            # Once that placement is saved, replace ' Polarity_temp ' data so that it isn't mistakenly used again
            Polarity_temp["Country Points Table full"][j] = "u.s.e.d"

        
        Copy
    

    	
            
# Create a data-frame to be populated
Country_Polarised_Points_Table1 = pd.DataFrame(index = Country_Points_Table2.index,
                                               columns = Country_Points_Table2.columns)

        
        Copy
    

    	
            
for k in [[Negatives, "Negative"], [Positives, "Positive"]]:
    for j in k[0]:
        Country_Polarised_Points_Table1_column_j = []
        for i in range(len(Country_Points_Table2.index)):
            Country_Polarised_Points_Table1_column_j.append(Point(Country_Points_Table2.iloc[i,j], polarity = k[1]))
        Country_Polarised_Points_Table1[Country_Points_Table2.columns[j]] = Country_Polarised_Points_Table1_column_j

        
        Copy
    

    	
            
# Create a data-frame to be populated
SDG_Aggregate_Ranks = pd.DataFrame({("No Poverty", "Scores Available") : list(np.full(len(Country_Polarised_Points_Table1.index), np.nan))},
                                   index = Country_Polarised_Points_Table1.index)
 
for j,k,m in zip(range(1,18),
                 ["No Poverty", "Zero Hunger", "Good Healthcare and Wellbeing", "Quality of Education",
                  "Gender Equality", "Clean Water and Sanitation", "Affordable and Clean Energy", "Decent Work and Economic Growth",
                  "Industry, Innovation and Infrastructure", "Reduced Inequalities", "Sustainable Cities and Communities", "Responsible Consumption",
                  "Climate Action", "Life Bellow Water", "Life on Land", "Peace, Justice and Strong Institutions", "Partnerships for the Goals"],
                 [3,3,3,5,
                  2,1,1,7,
                  3,4,1,0,
                  0,0,5,5,3]):
    
    # Create lists to be populated:
    col, SDG_Aggregate_Ranks_col1_j, SDG_Aggregate_Ranks_col2_j = [], [], []
    for i in range(len(Country_Polarised_Points_Table1.columns)):
        if Country_Polarised_Points_Table1.columns[i].split(".")[0:3][0] == str(j):
            # I fhte three fist strings (delimited by a full stop '.') ar the same,
            # then it must be the same ESG category. Here we focus on each category
            col.append(i)
    
    for i in range(len(Country_Polarised_Points_Table1.iloc[:,col])):
        # For each category, we tally up the number of observations we have
        SDG_Aggregate_Ranks_col1_j.append(str(len(Country_Polarised_Points_Table1.iloc[i,col].dropna())) + "/" + 
                                         str(len(Country_Polarised_Points_Table1.iloc[i,col])))
        
        # It was decided that only if enough records are found should we consider
        # the median score for a country's ESG category to contain significant insight:
        if len(Country_Polarised_Points_Table1.iloc[i,col].dropna()) > m:
            SDG_Aggregate_Ranks_col2_j.append(Country_Polarised_Points_Table1.iloc[i,col].median())
        else:
            SDG_Aggregate_Ranks_col2_j.append("insufficient scores (<=" + str(m+1) + ")")
    
    SDG_Aggregate_Ranks[(k, "Scores Available")] = SDG_Aggregate_Ranks_col1_j
    SDG_Aggregate_Ranks[(k, "Median Points (Higher is better)")] = SDG_Aggregate_Ranks_col2_j

        
        Copy
    

    	
            
# Create lists to be populated
list_of_scores, country_median = [], []
 
for j in range(len(SDG_Aggregate_Ranks.index)):
    
    # Create lists to be populated
    scores, scores_max, country_median_i = [], [], []
    
    for i in range(0,len(SDG_Aggregate_Ranks.columns),2):
        scores.append(int(SDG_Aggregate_Ranks.iloc[j,i].split("/")[0]))
        scores_max.append(int(SDG_Aggregate_Ranks.iloc[j,i].split("/")[-1]))
    list_of_scores.append(str(sum(scores)) + "/" + str(sum(scores_max)))
    
    for i in range(1,len(SDG_Aggregate_Ranks.columns),2):
        try:
            # The try loop here allows us to account for the lack of sufficient data-points
            country_median_i.append(float(SDG_Aggregate_Ranks.iloc[j,i]))
        except:
            pass
        
    country_median.append(statistics.median(country_median_i))
    
SDG_Aggregate_Ranks[("Overall", "Scores Available")] = list_of_scores
SDG_Aggregate_Ranks[("Overall", "Median Points (Higher is better)")] = country_median

        
        Copy
    

    	
            SDG_Aggregate_Ranks
        
        Copy
    

    	
            
# Create a data-frame from the list of our results this far
Overall_Results_Chart = pd.DataFrame(country_median,
                                     index = Country_Polarised_Points_Table1.index,
                                     columns = ["Overall Results"])
Overall_Results_Chart["Countries"] = list(Country_Polarised_Points_Table1.index)

        
        Copy
    

    	
            Overall_Results_Chart.dropna().sort_values(ascending = True, by = ["Overall Results"])
        
        Copy
    

    	
            
ax1 = Overall_Results_Chart.dropna().sort_values(
    ascending = True,
    by = ["Overall Results"]
).plot.barh(
    x = "Countries",
    y = "Overall Results",
    figsize = (10,40),
    title = "Overall ESG Scores (out of 10)\n",
    grid = True
)

        
        Copy
    

    	
            
import plotly.graph_objects as go
 
dataf = Overall_Results_Chart.dropna().sort_values(ascending = True, by = ["Overall Results"])
 
fig = go.Figure(go.Bar(x = dataf["Overall Results"],
                       y = dataf["Countries"],
                       orientation = "h"))
 
fig.show()

        
        Copy
    

    	
            
# ' ExcelWriter ' is needed to create an Excel Workbook with multiple tabs/sheets
with pd.ExcelWriter("ESG-DS_output.xlsx") as writer:
    Overall_Results_Chart.to_excel(writer, sheet_name = "Overall_Results_Chart")
    SDG_Aggregate_Ranks.to_excel(writer, sheet_name = "SDG_aggregate_ranks")
    Country_Polarised_Points_Table1.to_excel(writer, sheet_name = "country_polarised_points_table1")
    Polarity_temp.to_excel(writer, sheet_name = "polarity.u.s.e.d")
    Polarity.to_excel(writer, sheet_name = "polarity")
    Country_Points_Table2.to_excel(writer, sheet_name = "country_points_table2")
    Country_Points_Table1.to_excel(writer, sheet_name = "country_values_table1")
    Country_Values_Table2.to_excel(writer, sheet_name = "country_values_table2")
    Country_Values_Table.to_excel(writer, sheet_name = "country_values_table1")
    df.to_excel(writer, sheet_name = "individual_country_series_codes")

        
        Copy