1. Home
  2. Article Catalog
  3. Datastream Sustainable Development Goals Country Scores

Datastream Sustainable Development Goals Country Scores

Jonathan Legrand
Developer Advocate Developer Advocate

Environmental, Social and Governance (ESG) data is difficult to come by. It is also becoming critical for effective investment analysis. It helps you assess the risks – and opportunities – posed by companies’ performance in critical areas such as climate change, executive remuneration, and diversity and inclusion. But a definite lack of transparency and standardization in such reporting presents major challenges for investors.

This article attempts to lay a framework to allow any investor/agent to collect, analyse and gather insight into countries' ESG metrics at granular and macro-levels. It reflects the DataStream Sustainable Development Goals Country Scores Excel capability.The file 'ESG-DS.csv' will be needed to collect individual country series codes from our ESG database.

Get Coding

Import Libraries

math, statistics, numpy, pandas and openpyxl are needed for dataset manipulation and their statistical and mathematical manipulations.

    	
            

import math

import statistics

import numpy as np

    	
            

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)

    	
            

# 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

pickle is only used here to record data so that is doesn't have to be collected every time this code is ran (if it is ran several times)

    	
            

# need to ' pip install pickle-mixin '

import pickle

We need to gather our data. Since Refinitiv's DataStream Web Services (DSWS) allows for access to ESG data covering nearly 70% of global market cap and over 400 metrics, naturally it is more than appropriate. We can access DSWS via the Python library "DatastreamDSWS" that can be installed simply by using pip install.

    	
            

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)

For full replication, note that the version of libraries used

    	
            

import sys # ' sys ' is only needed to display our Pyhon version

print("This code is running on Python version " + sys.version[0:5])

This code is running on Python version 3.7.7

    	
            

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__)

The np library imported in this code is version: 1.18.5
The pd library imported in this code is version: 1.0.5
The openpyxl library imported in this code is version: 3.0.3

The 'datetime' library is a Python-built-in library, therefore it does not have a specific version number.

    	
            from datetime import date
        
        
    

Setup Functions

We rate each ESG category on a scale from 1 to 10 as per the function defined bellow

    	
            

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

Collect Data

We first need to collect the individual country series codes to ping DataStream with. There are a great many of them, we thus collect them from the comma-separated values (csv) file 'ESG-DS.csv'.

    	
            

# 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

  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 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 8.1.1 8.2.1 8.2.1.1 8.2.1.2 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.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 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.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
Afghanistan AFWD8I78R AFWDNYMYR AFWDYTADR AFACPOVBR AFWDUGUER AFWDA131R AFWDUGUER AFWDA903R AFWDQOG4R AFWDMD93R AFWDLBORR AFWDA378P AFWD1JCLR AFWD2KTMR AFWDYI32R AFGFF42G AFGFF42O AFWDPVWXR AFWD5MSAR AFWD7X4NP AFWDA141R AFWD153FR AFWD6MMLR AFWDHGSZR AFWDXB26P AFWDANDRP AFWDZMVPA AFWDZPVLR AFWDCU7JR AFWD1LNHR AFWD67W8P AFWDTMWJR AFWDTMWJR AFWDTMWJR AFWDTMWJR AFWDTMWJR AFWDTMWJR AFGCIBXV AFWDUGUER AFWDSWAWR AFWD3PM6R AFWDNNO1R AFWD24FSR AFWDGC8WR AFWDBSXAR AFWDA903R AFWDMD93R AFWDBIZXR AFWDOOGER AFWDLP15P AFWDDLO5P AFWD8FD7P AFWDZPVLR AFWDZPVLR AFWDL6Z8R AFWDA121R AFWD9I7VR AFWDA124R AFWD8S7HR AFXTPEN. AFWD2OO1A AFWDB1HMR AFWDWSO8R AFWD8FD7P AFWD3QBGR AFWDDK5XR AFWDAJTXR AFWDZP5NR AFWDFCM9R AFWD7YW0R AFWDIT9QR AFWDA443R AFWDA444R AFWDA445R AFWDVMS9R AFWDVM7CR AFWD6GL2R AFWDEZ1JR AFWDVVWFR AFWDL1BLR AFWDYPL5R AFWD3OFRR AFWDSUEDR AFGCWQYS AFWDA151P AFWDA039P AFGFF0XL AFWD64LUP AFWDXXOQP AFWD5U24P AFWDKN5RR AFWD7MZ1A AFWD8FD7P AFWDL0XWR AFWDIVNLP AFWDQ4L5R AFWDALCKR AFWDGJ88P AFWDA432R AFWDA433R AFWDFW5SF AFWDMD93R AFIFCLPNR AFCRCPRSR AFCRECOSR AFPWLBSH AFCRILLBR AFWD7OM6R AFAMTOTSR AFWDA441P AFWD7TPDP AFWDUYDZR AFWDOS8LR AFWDIVNLP AFWDLIKMP AFWDE0D5R AFWDA150R AFWDY9K8R AFWD1OBUR AFWDA075R AFWDODTKR AFWDJZYDR AFWDGHL4P AFWD3IS4P AFWD75RDP AFWDU54MP AFGFF54G AFGFF54O AFGFF54G AFGFF54O AFWDA160P AFWDA011P AFWD8FD7P AFCRHMTSR AFCRILLSR AFCRGEXSR AFTCPSCO AFACAMLSR AFTCPSCO AFACAMLSR AFCRGVESR AFWDCSMYR AFGCI9SS AFCRHMROR AFWDMD93R AFIFCLPNR AFGFREVO AFGFTRBO AFGFGRVO AFGFORVO AFINVR.. AFWDVN9PR AFGCY4PR AFWDGJ88P
Albania ALWD8I78R ALWDNYMYR ALWDYTADR ALACPOVBR ALWDUGUER ALWDA131R ALWDUGUER ALWDA903R ALWDQOG4R ALWDMD93R ALWDLBORR ALWDA378P ALWD1JCLR ALWD2KTMR ALWDYI32R ALGFF42G ALGFF42O ALWDPVWXR ALWD5MSAR ALWD7X4NP ALWDA141R ALWD153FR ALWD6MMLR ALWDHGSZR ALWDXB26P ALWDANDRP ALWDZMVPA ALWDZPVLR ALWDCU7JR ALWD1LNHR ALWD67W8P ALWDTMWJR ALWDTMWJR ALWDTMWJR ALWDTMWJR ALWDTMWJR ALWDTMWJR ALGCIBXV ALWDUGUER ALWDSWAWR ALWD3PM6R ALWDNNO1R ALWD24FSR ALWDGC8WR ALWDBSXAR ALWDA903R ALWDMD93R ALWDBIZXR ALWDOOGER ALWDLP15P ALWDDLO5P ALWD8FD7P ALWDZPVLR ALWDZPVLR ALWDL6Z8R ALWDA121R ALWD9I7VR ALWDA124R ALWD8S7HR ALXTPEN. ALWD2OO1A ALWDB1HMR ALWDWSO8R ALWD8FD7P ALWD3QBGR ALWDDK5XR ALWDAJTXR ALWDZP5NR ALWDFCM9R ALWD7YW0R ALWDIT9QR ALWDA443R ALWDA444R ALWDA445R ALWDVMS9R ALWDVM7CR ALWD6GL2R ALWDEZ1JR ALWDVVWFR ALWDL1BLR ALWDYPL5R ALWD3OFRR ALWDSUEDR ALGCWQYS ALWDA151P ALWDA039P ALGFF0XL ALWD64LUP ALWDXXOQP ALWD5U24P ALWDKN5RR ALWD7MZ1A ALWD8FD7P ALWDL0XWR ALWDIVNLP ALWDQ4L5R ALWDALCKR ALWDGJ88P ALWDA432R ALWDA433R ALWDFW5SF ALWDMD93R ALIFCLPNR ALCRCPRSR ALCRECOSR ALPWLBSH ALCRILLBR ALWD7OM6R ALAMTOTSR ALWDA441P ALWD7TPDP ALWDUYDZR ALWDOS8LR ALWDIVNLP ALWDLIKMP ALWDE0D5R ALWDA150R ALWDY9K8R ALWD1OBUR ALWDA075R ALWDODTKR ALWDJZYDR ALWDGHL4P ALWD3IS4P ALWD75RDP ALWDU54MP ALGFF54G ALGFF54O ALGFF54G ALGFF54O ALWDA160P ALWDA011P ALWD8FD7P ALCRHMTSR ALCRILLSR ALCRGEXSR ALTCPSCO ALACAMLSR ALTCPSCO ALACAMLSR ALCRGVESR ALWDCSMYR ALGCI9SS ALCRHMROR ALWDMD93R ALIFCLPNR ALGFREVO ALGFTRBO ALGFGRVO ALGFORVO ALINVR.. ALWDVN9PR ALGCY4PR ALWDGJ88P
Algeria AAWD8I78R AAWDNYMYR AAWDYTADR AAACPOVBR AAWDUGUER AAWDA131R AAWDUGUER AAWDA903R AAWDQOG4R AAWDMD93R AAWDLBORR AAWDA378P AAWD1JCLR AAWD2KTMR AAWDYI32R AAGFF42G AAGFF42O AAWDPVWXR AAWD5MSAR AAWD7X4NP AAWDA141R AAWD153FR AAWD6MMLR AAWDHGSZR AAWDXB26P AAWDANDRP AAWDZMVPA AAWDZPVLR AAWDCU7JR AAWD1LNHR AAWD67W8P AAWDTMWJR AAWDTMWJR AAWDTMWJR AAWDTMWJR AAWDTMWJR AAWDTMWJR AAGCIBXV AAWDUGUER AAWDSWAWR AAWD3PM6R AAWDNNO1R AAWD24FSR AAWDGC8WR AAWDBSXAR AAWDA903R AAWDMD93R AAWDBIZXR AAWDOOGER AAWDLP15P AAWDDLO5P AAWD8FD7P AAWDZPVLR AAWDZPVLR AAWDL6Z8R AAWDA121R AAWD9I7VR AAWDA124R AAWD8S7HR AAXTPEN. AAWD2OO1A AAWDB1HMR AAWDWSO8R AAWD8FD7P AAWD3QBGR AAWDDK5XR AAWDAJTXR AAWDZP5NR AAWDFCM9R AAWD7YW0R AAWDIT9QR AAWDA443R AAWDA444R AAWDA445R AAWDVMS9R AAWDVM7CR AAWD6GL2R AAWDEZ1JR AAWDVVWFR AAWDL1BLR AAWDYPL5R AAWD3OFRR AAWDSUEDR AAGCWQYS AAWDA151P AAWDA039P AAGFF0XL AAWD64LUP AAWDXXOQP AAWD5U24P AAWDKN5RR AAWD7MZ1A AAWD8FD7P AAWDL0XWR AAWDIVNLP AAWDQ4L5R AAWDALCKR AAWDGJ88P AAWDA432R AAWDA433R AAWDFW5SF AAWDMD93R AAIFCLPNR AACRCPRSR AACRECOSR AAPWLBSH AACRILLBR AAWD7OM6R AAAMTOTSR AAWDA441P AAWD7TPDP AAWDUYDZR AAWDOS8LR AAWDIVNLP AAWDLIKMP AAWDE0D5R AAWDA150R AAWDY9K8R AAWD1OBUR AAWDA075R AAWDODTKR AAWDJZYDR AAWDGHL4P AAWD3IS4P AAWD75RDP AAWDU54MP AAGFF54G AAGFF54O AAGFF54G AAGFF54O AAWDA160P AAWDA011P AAWD8FD7P AACRHMTSR AACRILLSR AACRGEXSR AATCPSCO AAACAMLSR AATCPSCO AAACAMLSR AACRGVESR AAWDCSMYR AAGCI9SS AACRHMROR AAWDMD93R AAIFCLPNR AAGFREVO AAGFTRBO AAGFGRVO AAGFORVO AAINVR.. AAWDVN9PR AAGCY4PR AAWDGJ88P
American Samoa SMWD8I78R SMWDNYMYR SMWDYTADR SMACPOVBR SMWDUGUER SMWDA131R SMWDUGUER SMWDA903R SMWDQOG4R SMWDMD93R SMWDLBORR SMWDA378P SMWD1JCLR SMWD2KTMR SMWDYI32R SMGFF42G SMGFF42O SMWDPVWXR SMWD5MSAR SMWD7X4NP SMWDA141R SMWD153FR SMWD6MMLR SMWDHGSZR SMWDXB26P SMWDANDRP SMWDZMVPA SMWDZPVLR SMWDCU7JR SMWD1LNHR SMWD67W8P SMWDTMWJR SMWDTMWJR SMWDTMWJR SMWDTMWJR SMWDTMWJR SMWDTMWJR SMGCIBXV SMWDUGUER SMWDSWAWR SMWD3PM6R SMWDNNO1R SMWD24FSR SMWDGC8WR SMWDBSXAR SMWDA903R SMWDMD93R SMWDBIZXR SMWDOOGER SMWDLP15P SMWDDLO5P SMWD8FD7P SMWDZPVLR SMWDZPVLR SMWDL6Z8R SMWDA121R SMWD9I7VR SMWDA124R SMWD8S7HR SMXTPEN. SMWD2OO1A SMWDB1HMR SMWDWSO8R SMWD8FD7P SMWD3QBGR SMWDDK5XR SMWDAJTXR SMWDZP5NR SMWDFCM9R SMWD7YW0R SMWDIT9QR SMWDA443R SMWDA444R SMWDA445R SMWDVMS9R SMWDVM7CR SMWD6GL2R SMWDEZ1JR SMWDVVWFR SMWDL1BLR SMWDYPL5R SMWD3OFRR SMWDSUEDR SMGCWQYS SMWDA151P SMWDA039P SMGFF0XL SMWD64LUP SMWDXXOQP SMWD5U24P SMWDKN5RR SMWD7MZ1A SMWD8FD7P SMWDL0XWR SMWDIVNLP SMWDQ4L5R SMWDALCKR SMWDGJ88P SMWDA432R SMWDA433R SMWDFW5SF SMWDMD93R SMIFCLPNR SMCRCPRSR SMCRECOSR SMPWLBSH SMCRILLBR SMWD7OM6R SMAMTOTSR SMWDA441P SMWD7TPDP SMWDUYDZR SMWDOS8LR SMWDIVNLP SMWDLIKMP SMWDE0D5R SMWDA150R SMWDY9K8R SMWD1OBUR SMWDA075R SMWDODTKR SMWDJZYDR SMWDGHL4P SMWD3IS4P SMWD75RDP SMWDU54MP SMGFF54G SMGFF54O SMGFF54G SMGFF54O SMWDA160P SMWDA011P SMWD8FD7P SMCRHMTSR SMCRILLSR SMCRGEXSR SMTCPSCO SMACAMLSR SMTCPSCO SMACAMLSR SMCRGVESR SMWDCSMYR SMGCI9SS SMCRHMROR SMWDMD93R SMIFCLPNR SMGFREVO SMGFTRBO SMGFGRVO SMGFORVO SMINVR.. SMWDVN9PR SMGCY4PR SMWDGJ88P
Andorra ADWD8I78R ADWDNYMYR ADWDYTADR ADACPOVBR ADWDUGUER ADWDA131R ADWDUGUER ADWDA903R ADWDQOG4R ADWDMD93R ADWDLBORR ADWDA378P ADWD1JCLR ADWD2KTMR ADWDYI32R ADGFF42G ADGFF42O ADWDPVWXR ADWD5MSAR ADWD7X4NP ADWDA141R ADWD153FR ADWD6MMLR ADWDHGSZR ADWDXB26P ADWDANDRP ADWDZMVPA ADWDZPVLR ADWDCU7JR ADWD1LNHR ADWD67W8P ADWDTMWJR ADWDTMWJR ADWDTMWJR ADWDTMWJR ADWDTMWJR ADWDTMWJR ADGCIBXV ADWDUGUER ADWDSWAWR ADWD3PM6R ADWDNNO1R ADWD24FSR ADWDGC8WR ADWDBSXAR ADWDA903R ADWDMD93R ADWDBIZXR ADWDOOGER ADWDLP15P ADWDDLO5P ADWD8FD7P ADWDZPVLR ADWDZPVLR ADWDL6Z8R ADWDA121R ADWD9I7VR ADWDA124R ADWD8S7HR ADXTPEN. ADWD2OO1A ADWDB1HMR ADWDWSO8R ADWD8FD7P ADWD3QBGR ADWDDK5XR ADWDAJTXR ADWDZP5NR ADWDFCM9R ADWD7YW0R ADWDIT9QR ADWDA443R ADWDA444R ADWDA445R ADWDVMS9R ADWDVM7CR ADWD6GL2R ADWDEZ1JR ADWDVVWFR ADWDL1BLR ADWDYPL5R ADWD3OFRR ADWDSUEDR ADGCWQYS ADWDA151P ADWDA039P ADGFF0XL ADWD64LUP ADWDXXOQP ADWD5U24P ADWDKN5RR ADWD7MZ1A ADWD8FD7P ADWDL0XWR ADWDIVNLP ADWDQ4L5R ADWDALCKR ADWDGJ88P ADWDA432R ADWDA433R ADWDFW5SF ADWDMD93R ADIFCLPNR ADCRCPRSR ADCRECOSR ADPWLBSH ADCRILLBR ADWD7OM6R ADAMTOTSR ADWDA441P ADWD7TPDP ADWDUYDZR ADWDOS8LR ADWDIVNLP ADWDLIKMP ADWDE0D5R ADWDA150R ADWDY9K8R ADWD1OBUR ADWDA075R ADWDODTKR ADWDJZYDR ADWDGHL4P ADWD3IS4P ADWD75RDP ADWDU54MP ADGFF54G ADGFF54O ADGFF54G ADGFF54O ADWDA160P ADWDA011P ADWD8FD7P ADCRHMTSR ADCRILLSR ADCRGEXSR ADTCPSCO ADACAMLSR ADTCPSCO ADACAMLSR ADCRGVESR ADWDCSMYR ADGCI9SS ADCRHMROR ADWDMD93R ADIFCLPNR ADGFREVO ADGFTRBO ADGFGRVO ADGFORVO ADINVR.. ADWDVN9PR ADGCY4PR ADWDGJ88P
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
Virgin Islands (United States) VGWD8I78R VGWDNYMYR VGWDYTADR VGACPOVBR VGWDUGUER VGWDA131R VGWDUGUER VGWDA903R VGWDQOG4R VGWDMD93R VGWDLBORR VGWDA378P VGWD1JCLR VGWD2KTMR VGWDYI32R VGGFF42G VGGFF42O VGWDPVWXR VGWD5MSAR VGWD7X4NP VGWDA141R VGWD153FR VGWD6MMLR VGWDHGSZR VGWDXB26P VGWDANDRP VGWDZMVPA VGWDZPVLR VGWDCU7JR VGWD1LNHR VGWD67W8P VGWDTMWJR VGWDTMWJR VGWDTMWJR VGWDTMWJR VGWDTMWJR VGWDTMWJR VGGCIBXV VGWDUGUER VGWDSWAWR VGWD3PM6R VGWDNNO1R VGWD24FSR VGWDGC8WR VGWDBSXAR VGWDA903R VGWDMD93R VGWDBIZXR VGWDOOGER VGWDLP15P VGWDDLO5P VGWD8FD7P VGWDZPVLR VGWDZPVLR VGWDL6Z8R VGWDA121R VGWD9I7VR VGWDA124R VGWD8S7HR VGXTPEN. VGWD2OO1A VGWDB1HMR VGWDWSO8R VGWD8FD7P VGWD3QBGR VGWDDK5XR VGWDAJTXR VGWDZP5NR VGWDFCM9R VGWD7YW0R VGWDIT9QR VGWDA443R VGWDA444R VGWDA445R VGWDVMS9R VGWDVM7CR VGWD6GL2R VGWDEZ1JR VGWDVVWFR VGWDL1BLR VGWDYPL5R VGWD3OFRR VGWDSUEDR VGGCWQYS VGWDA151P VGWDA039P VGGFF0XL VGWD64LUP VGWDXXOQP VGWD5U24P VGWDKN5RR VGWD7MZ1A VGWD8FD7P VGWDL0XWR VGWDIVNLP VGWDQ4L5R VGWDALCKR VGWDGJ88P VGWDA432R VGWDA433R VGWDFW5SF VGWDMD93R VGIFCLPNR VGCRCPRSR VGCRECOSR VGPWLBSH VGCRILLBR VGWD7OM6R VGAMTOTSR VGWDA441P VGWD7TPDP VGWDUYDZR VGWDOS8LR VGWDIVNLP VGWDLIKMP VGWDE0D5R VGWDA150R VGWDY9K8R VGWD1OBUR VGWDA075R VGWDODTKR VGWDJZYDR VGWDGHL4P VGWD3IS4P VGWD75RDP VGWDU54MP VGGFF54G VGGFF54O VGGFF54G VGGFF54O VGWDA160P VGWDA011P VGWD8FD7P VGCRHMTSR VGCRILLSR VGCRGEXSR VGTCPSCO VGACAMLSR VGTCPSCO VGACAMLSR VGCRGVESR VGWDCSMYR VGGCI9SS VGCRHMROR VGWDMD93R VGIFCLPNR VGGFREVO VGGFTRBO VGGFGRVO VGGFORVO VGINVR.. VGWDVN9PR VGGCY4PR VGWDGJ88P
West Bank and Gaza WBWD8I78R WBWDNYMYR WBWDYTADR WBACPOVBR WBWDUGUER WBWDA131R WBWDUGUER WBWDA903R WBWDQOG4R WBWDMD93R WBWDLBORR WBWDA378P WBWD1JCLR WBWD2KTMR WBWDYI32R WBGFF42G WBGFF42O WBWDPVWXR WBWD5MSAR WBWD7X4NP WBWDA141R WBWD153FR WBWD6MMLR WBWDHGSZR WBWDXB26P WBWDANDRP WBWDZMVPA WBWDZPVLR WBWDCU7JR WBWD1LNHR WBWD67W8P WBWDTMWJR WBWDTMWJR WBWDTMWJR WBWDTMWJR WBWDTMWJR WBWDTMWJR WBGCIBXV WBWDUGUER WBWDSWAWR WBWD3PM6R WBWDNNO1R WBWD24FSR WBWDGC8WR WBWDBSXAR WBWDA903R WBWDMD93R WBWDBIZXR WBWDOOGER WBWDLP15P WBWDDLO5P WBWD8FD7P WBWDZPVLR WBWDZPVLR WBWDL6Z8R WBWDA121R WBWD9I7VR WBWDA124R WBWD8S7HR WBXTPEN. WBWD2OO1A WBWDB1HMR WBWDWSO8R WBWD8FD7P WBWD3QBGR WBWDDK5XR WBWDAJTXR WBWDZP5NR WBWDFCM9R WBWD7YW0R WBWDIT9QR WBWDA443R WBWDA444R WBWDA445R WBWDVMS9R WBWDVM7CR WBWD6GL2R WBWDEZ1JR WBWDVVWFR WBWDL1BLR WBWDYPL5R WBWD3OFRR WBWDSUEDR WBGCWQYS WBWDA151P WBWDA039P WBGFF0XL WBWD64LUP WBWDXXOQP WBWD5U24P WBWDKN5RR WBWD7MZ1A WBWD8FD7P WBWDL0XWR WBWDIVNLP WBWDQ4L5R WBWDALCKR WBWDGJ88P WBWDA432R WBWDA433R WBWDFW5SF WBWDMD93R WBIFCLPNR WBCRCPRSR WBCRECOSR WBPWLBSH WBCRILLBR WBWD7OM6R WBAMTOTSR WBWDA441P WBWD7TPDP WBWDUYDZR WBWDOS8LR WBWDIVNLP WBWDLIKMP WBWDE0D5R WBWDA150R WBWDY9K8R WBWD1OBUR WBWDA075R WBWDODTKR WBWDJZYDR WBWDGHL4P WBWD3IS4P WBWD75RDP WBWDU54MP WBGFF54G WBGFF54O WBGFF54G WBGFF54O WBWDA160P WBWDA011P WBWD8FD7P WBCRHMTSR WBCRILLSR WBCRGEXSR WBTCPSCO WBACAMLSR WBTCPSCO WBACAMLSR WBCRGVESR WBWDCSMYR WBGCI9SS WBCRHMROR WBWDMD93R WBIFCLPNR WBGFREVO WBGFTRBO WBGFGRVO WBGFORVO WBINVR.. WBWDVN9PR WBGCY4PR WBWDGJ88P
Yemen YAWD8I78R YAWDNYMYR YAWDYTADR YAACPOVBR YAWDUGUER YAWDA131R YAWDUGUER YAWDA903R YAWDQOG4R YAWDMD93R YAWDLBORR YAWDA378P YAWD1JCLR YAWD2KTMR YAWDYI32R YAGFF42G YAGFF42O YAWDPVWXR YAWD5MSAR YAWD7X4NP YAWDA141R YAWD153FR YAWD6MMLR YAWDHGSZR YAWDXB26P YAWDANDRP YAWDZMVPA YAWDZPVLR YAWDCU7JR YAWD1LNHR YAWD67W8P YAWDTMWJR YAWDTMWJR YAWDTMWJR YAWDTMWJR YAWDTMWJR YAWDTMWJR YAGCIBXV YAWDUGUER YAWDSWAWR YAWD3PM6R YAWDNNO1R YAWD24FSR YAWDGC8WR YAWDBSXAR YAWDA903R YAWDMD93R YAWDBIZXR YAWDOOGER YAWDLP15P YAWDDLO5P YAWD8FD7P YAWDZPVLR YAWDZPVLR YAWDL6Z8R YAWDA121R YAWD9I7VR YAWDA124R YAWD8S7HR YAXTPEN. YAWD2OO1A YAWDB1HMR YAWDWSO8R YAWD8FD7P YAWD3QBGR YAWDDK5XR YAWDAJTXR YAWDZP5NR YAWDFCM9R YAWD7YW0R YAWDIT9QR YAWDA443R YAWDA444R YAWDA445R YAWDVMS9R YAWDVM7CR YAWD6GL2R YAWDEZ1JR YAWDVVWFR YAWDL1BLR YAWDYPL5R YAWD3OFRR YAWDSUEDR YAGCWQYS YAWDA151P YAWDA039P YAGFF0XL YAWD64LUP YAWDXXOQP YAWD5U24P YAWDKN5RR YAWD7MZ1A YAWD8FD7P YAWDL0XWR YAWDIVNLP YAWDQ4L5R YAWDALCKR YAWDGJ88P YAWDA432R YAWDA433R YAWDFW5SF YAWDMD93R YAIFCLPNR YACRCPRSR YACRECOSR YAPWLBSH YACRILLBR YAWD7OM6R YAAMTOTSR YAWDA441P YAWD7TPDP YAWDUYDZR YAWDOS8LR YAWDIVNLP YAWDLIKMP YAWDE0D5R YAWDA150R YAWDY9K8R YAWD1OBUR YAWDA075R YAWDODTKR YAWDJZYDR YAWDGHL4P YAWD3IS4P YAWD75RDP YAWDU54MP YAGFF54G YAGFF54O YAGFF54G YAGFF54O YAWDA160P YAWDA011P YAWD8FD7P YACRHMTSR YACRILLSR YACRGEXSR YATCPSCO YAACAMLSR YATCPSCO YAACAMLSR YACRGVESR YAWDCSMYR YAGCI9SS YACRHMROR YAWDMD93R YAIFCLPNR YAGFREVO YAGFTRBO YAGFGRVO YAGFORVO YAINVR.. YAWDVN9PR YAGCY4PR YAWDGJ88P
Zambia ZMWD8I78R ZMWDNYMYR ZMWDYTADR ZMACPOVBR ZMWDUGUER ZMWDA131R ZMWDUGUER ZMWDA903R ZMWDQOG4R ZMWDMD93R ZMWDLBORR ZMWDA378P ZMWD1JCLR ZMWD2KTMR ZMWDYI32R ZMGFF42G ZMGFF42O ZMWDPVWXR ZMWD5MSAR ZMWD7X4NP ZMWDA141R ZMWD153FR ZMWD6MMLR ZMWDHGSZR ZMWDXB26P ZMWDANDRP ZMWDZMVPA ZMWDZPVLR ZMWDCU7JR ZMWD1LNHR ZMWD67W8P ZMWDTMWJR ZMWDTMWJR ZMWDTMWJR ZMWDTMWJR ZMWDTMWJR ZMWDTMWJR ZMGCIBXV ZMWDUGUER ZMWDSWAWR ZMWD3PM6R ZMWDNNO1R ZMWD24FSR ZMWDGC8WR ZMWDBSXAR ZMWDA903R ZMWDMD93R ZMWDBIZXR ZMWDOOGER ZMWDLP15P ZMWDDLO5P ZMWD8FD7P ZMWDZPVLR ZMWDZPVLR ZMWDL6Z8R ZMWDA121R ZMWD9I7VR ZMWDA124R ZMWD8S7HR ZMXTPEN. ZMWD2OO1A ZMWDB1HMR ZMWDWSO8R ZMWD8FD7P ZMWD3QBGR ZMWDDK5XR ZMWDAJTXR ZMWDZP5NR ZMWDFCM9R ZMWD7YW0R ZMWDIT9QR ZMWDA443R ZMWDA444R ZMWDA445R ZMWDVMS9R ZMWDVM7CR ZMWD6GL2R ZMWDEZ1JR ZMWDVVWFR ZMWDL1BLR ZMWDYPL5R ZMWD3OFRR ZMWDSUEDR ZMGCWQYS ZMWDA151P ZMWDA039P ZMGFF0XL ZMWD64LUP ZMWDXXOQP ZMWD5U24P ZMWDKN5RR ZMWD7MZ1A ZMWD8FD7P ZMWDL0XWR ZMWDIVNLP ZMWDQ4L5R ZMWDALCKR ZMWDGJ88P ZMWDA432R ZMWDA433R ZMWDFW5SF ZMWDMD93R ZMIFCLPNR ZMCRCPRSR ZMCRECOSR ZMPWLBSH ZMCRILLBR ZMWD7OM6R ZMAMTOTSR ZMWDA441P ZMWD7TPDP ZMWDUYDZR ZMWDOS8LR ZMWDIVNLP ZMWDLIKMP ZMWDE0D5R ZMWDA150R ZMWDY9K8R ZMWD1OBUR ZMWDA075R ZMWDODTKR ZMWDJZYDR ZMWDGHL4P ZMWD3IS4P ZMWD75RDP ZMWDU54MP ZMGFF54G ZMGFF54O ZMGFF54G ZMGFF54O ZMWDA160P ZMWDA011P ZMWD8FD7P ZMCRHMTSR ZMCRILLSR ZMCRGEXSR ZMTCPSCO ZMACAMLSR ZMTCPSCO ZMACAMLSR ZMCRGVESR ZMWDCSMYR ZMGCI9SS ZMCRHMROR ZMWDMD93R ZMIFCLPNR ZMGFREVO ZMGFTRBO ZMGFGRVO ZMGFORVO ZMINVR.. ZMWDVN9PR ZMGCY4PR ZMWDGJ88P
Zimbabwe ZIWD8I78R ZIWDNYMYR ZIWDYTADR ZIACPOVBR ZIWDUGUER ZIWDA131R ZIWDUGUER ZIWDA903R ZIWDQOG4R ZIWDMD93R ZIWDLBORR ZIWDA378P ZIWD1JCLR ZIWD2KTMR ZIWDYI32R ZIGFF42G ZIGFF42O ZIWDPVWXR ZIWD5MSAR ZIWD7X4NP ZIWDA141R ZIWD153FR ZIWD6MMLR ZIWDHGSZR ZIWDXB26P ZIWDANDRP ZIWDZMVPA ZIWDZPVLR ZIWDCU7JR ZIWD1LNHR ZIWD67W8P ZIWDTMWJR ZIWDTMWJR ZIWDTMWJR ZIWDTMWJR ZIWDTMWJR ZIWDTMWJR ZIGCIBXV ZIWDUGUER ZIWDSWAWR ZIWD3PM6R ZIWDNNO1R ZIWD24FSR ZIWDGC8WR ZIWDBSXAR ZIWDA903R ZIWDMD93R ZIWDBIZXR ZIWDOOGER ZIWDLP15P ZIWDDLO5P ZIWD8FD7P ZIWDZPVLR ZIWDZPVLR ZIWDL6Z8R ZIWDA121R ZIWD9I7VR ZIWDA124R ZIWD8S7HR ZIXTPEN. ZIWD2OO1A ZIWDB1HMR ZIWDWSO8R ZIWD8FD7P ZIWD3QBGR ZIWDDK5XR ZIWDAJTXR ZIWDZP5NR ZIWDFCM9R ZIWD7YW0R ZIWDIT9QR ZIWDA443R ZIWDA444R ZIWDA445R ZIWDVMS9R ZIWDVM7CR ZIWD6GL2R ZIWDEZ1JR ZIWDVVWFR ZIWDL1BLR ZIWDYPL5R ZIWD3OFRR ZIWDSUEDR ZIGCWQYS ZIWDA151P ZIWDA039P ZIGFF0XL ZIWD64LUP ZIWDXXOQP ZIWD5U24P ZIWDKN5RR ZIWD7MZ1A ZIWD8FD7P ZIWDL0XWR ZIWDIVNLP ZIWDQ4L5R ZIWDALCKR ZIWDGJ88P ZIWDA432R ZIWDA433R ZIWDFW5SF ZIWDMD93R ZIIFCLPNR ZICRCPRSR ZICRECOSR ZIPWLBSH ZICRILLBR ZIWD7OM6R ZIAMTOTSR ZIWDA441P ZIWD7TPDP ZIWDUYDZR ZIWDOS8LR ZIWDIVNLP ZIWDLIKMP ZIWDE0D5R ZIWDA150R ZIWDY9K8R ZIWD1OBUR ZIWDA075R ZIWDODTKR ZIWDJZYDR ZIWDGHL4P ZIWD3IS4P ZIWD75RDP ZIWDU54MP ZIGFF54G ZIGFF54O ZIGFF54G ZIGFF54O ZIWDA160P ZIWDA011P ZIWD8FD7P ZICRHMTSR ZICRILLSR ZICRGEXSR ZITCPSCO ZIACAMLSR ZITCPSCO ZIACAMLSR ZICRGVESR ZIWDCSMYR ZIGCI9SS ZICRHMROR ZIWDMD93R ZIIFCLPNR ZIGFREVO ZIGFTRBO ZIGFGRVO ZIGFORVO ZIINVR.. ZIWDVN9PR ZIGCY4PR ZIWDGJ88P

Certain metrics have to be manipulated for them to be comparable to all others. We thus add columns for these additional metrics in the list of columns 'Country_Values_Table_Columns'

    	
            

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']

Bellow we can see the discrepancies in columns

    	
            

# 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))

Now we can collect our data from DSWS

We can call for data for each of the (211∗153=211∗153=) 3228332283 codes, but that would be extremely time consuming. Instead, we will call data in batches of 50 (because we can pull data from DSWS in baches of a maximum size of 50; n note that DataStream accepts calls in batches of up to 50 codes at once).

    	
            

# 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)

    	
            

# 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

    	
            Country_Values_Table
        
        
    
  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 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
Afghanistan 38.3 27.6 54.5 3 2.6 NaN 2.6 15.66 2.93 1.5 29.8 173 38.2 4.1 5.1 19620.55 1.42 638 50.5 62.3 37.1 0.1 0.1 0.1 189 65.141 56.575 55.3 NaN NaN 0.176 2.7 2.7 2.7 2.7 2.7 2.7 NaN 2.6 29.81 55.48 43.02 56.25 74.08 65.42 15.66 1.5 27.87 22.5 24.5 21976355 38041754 NaN 55.3 55.3 43.01 98.71 NaN NaN NaN NaN 1.91E+10 NaN 0.55 43.46 38041754 2.9 NaN 11.12 10.33 14 17.43 21.19 16.2 65.89 18.25 42.01 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 2.233 1.599 0.58 1722612.61 NaN 29.559 11.1 2.24E+09 38041754 NaN 18.16 0.429 NaN NaN 8.26 NaN NaN 27.82 1.5 NaN 1.67 1.62 NaN 2.5 2 0.63 56.911 63.465 2.5 NaN 0.429 0.245 2.07 0.1 0.1 0 0.38 11.84 3.407 16 4 11 5 1258.27 0.09 1258.27 0.09 7.1 25682 38041754 NaN 5.75 6.12 6.17 16 1.43 16 1.43 1.26 42.3 NaN 3.42 1.5 NaN 45.25 9.25 32.74 2.99 NaN 0.31 NaN 8.26
Albania 15.3 13.6 14.3 2 3.5 NaN 3.5 8.44 2.94 4 6.2 52 11.3 16.4 1.6 10329.83 0.63 15 99.8 8.8 6.5 0.1 0.1 0.1 18 19.572 272.204 95.1 51.2 7.1 3.65 80.79 80.79 80.79 80.79 80.79 80.79 4.67 3.5 97.76 98.51 98.14 99.63 99.05 99.33 8.44 4 29.51 46 15.1 2714878 2854191 NaN 95.1 95.1 4.87 100 24.55 0 100 NaN 1.53E+10 NaN 2.65 48.86 2854191 2.21 NaN 12.33 12.85 11.6 28.13 24.31 30.42 25.45 26.2 25.82 83.85 84.14 83.65 9.25 8.04 11.01 1.26 1.19 1.36 80.41 19.403 30.43 4.45 303137 32 0 6.28 9.60E+08 2854191 NaN 20 0.337 0.15 155.528 63.253 2.46 0.81 33.2 4 NaN 5.16 7.2 NaN 3 4 6.02 18.201 42.506 3 61.42 0.337 1.577 28.12 13.53 17.74 2.72 0.16 22.64 0.185 8 44 3 4 14.88 0 14.88 0 2.3 NaN 2854191 NaN 5.75 4.88 8.79 35 4.05 35 4.05 5.11 98.4 70.16 4 4 NaN 27.27 19.44 0.52 1.79 3.4 7.18 58 63.253
Algeria 4.8 5.8 5.5 3 NaN 3.5 NaN 11.43 5.24 NaN 3.9 20 11.7 12.4 4.1 NaN NaN 112 96.6 23.5 14.6 0.1 0.1 0.1 69 9.834 361.729 83.6 30.4 0.7 1.548 79.71 79.71 79.71 79.71 79.71 79.71 3.81 NaN 75.32 87.42 81.41 97.25 97.59 97.43 11.43 NaN 25.76 57.1 7 47154264 43053054 NaN 83.6 83.6 69.42 100 0.03 0.11 0.21 NaN 1.70E+11 NaN -1.13 36.33 43053054 0.8 NaN 11.7 9.7 21.08 29.51 46.29 26.25 31.69 10.94 20.95 NaN NaN NaN NaN NaN NaN NaN NaN NaN 58.76 5.176 9.119 6.15 6442442 1550 28.28 24.28 4.13E+10 43053054 NaN 30.73 0.766 0.54 819.343 38.2 NaN NaN 27.6 NaN 6.7 2.24 0.76 NaN 3.75 NaN 3.18 38.884 34.239 NaN 99.98 0.766 3.699 0.82 7.11 7.5 0.09 0.16 3.11 2.853 15 41 14 22 NaN NaN NaN NaN 1.4 33 43053054 NaN 2.2 2.57 3.43 35 5.06 35 5.06 3.17 100 54.25 4 NaN 6.7 NaN NaN NaN NaN 0.4 0.13 119 38.2
American Samoa NaN NaN NaN 3 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 100 NaN NaN NaN NaN NaN 0 NaN NaN 100 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 2250 55312 NaN 100 100 NaN 59.33 NaN NaN NaN NaN 6.36E+08 NaN 2.47 NaN 55312 2.18 NaN 5.1 4.9 6 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76944 NaN 0.012 NaN NaN 55312 NaN 23.16 NaN 0.39 100.735 NaN NaN NaN NaN NaN NaN 5 5 NaN 3 NaN 6.06 12.474 NaN NaN NaN NaN NaN 87.5 8.72 15.85 8.72 0 15 0.27 8 12 1 1 NaN NaN NaN NaN 5.4 0 55312 NaN 5 5 5 NaN 2.01 NaN 2.01 6.58 NaN NaN 0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Andorra NaN NaN NaN 3 NaN NaN NaN NaN 6.34 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 2.8 1.4 NaN NaN NaN 6.5 NaN 3746.334 100 38.5 28.4 4.765 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 32.1 NaN NaN 71336 70473 NaN 100 100 NaN 100 NaN NaN NaN NaN 3.25E+09 NaN 4.43 NaN 70473 -0.06 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 3.16 1.03E+08 70473 NaN NaN 0.157 NaN NaN 96.91 NaN NaN NaN NaN NaN 9.05 5 NaN 3 NaN 8.22 9.7 31.238 NaN NaN 0.157 6.474 34.04 19.5 19.5 0 0 5.96 0 2 0 2 0 NaN NaN NaN NaN 0 0 70473 NaN 5 5 5 NaN 5.25 NaN 5.25 9.83 100 NaN 2 NaN NaN NaN NaN NaN NaN NaN NaN NaN 96.91
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
Virgin Islands (United States) NaN NaN NaN 3 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 98.5 NaN NaN NaN NaN NaN 7.7 27.648 NaN 100 12.4 7.1 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 78.4 NaN 80300 106631 NaN 100 100 NaN 100 NaN NaN NaN NaN 3.86E+09 NaN -1.47 55.21 106631 -1.7 NaN 8.66 8.07 9.22 21.54 28.68 19.01 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 106631 NaN 19.64 NaN 0.03 73.592 54.839 NaN NaN NaN NaN NaN 5 5 NaN 3 NaN 5.86 10.265 31.101 NaN NaN NaN NaN 49.97 0.98 13.79 0.85 0 2.86 0.199 3 29 1 17 NaN NaN NaN NaN 49.3 0 106631 NaN 5 5 5 NaN 2.01 NaN 2.01 7.4 NaN NaN 0 NaN NaN NaN NaN NaN NaN NaN NaN NaN 54.839
West Bank and Gaza 19.4 26.1 29.2 NaN NaN NaN NaN NaN 7.8 NaN 31.8 211 7.4 8.2 1.2 NaN NaN 27 99.6 20.3 10.9 NaN NaN NaN 0.77 51.883 NaN 58.4 NaN NaN NaN 56.48 56.48 56.48 56.48 56.48 56.48 NaN NaN 95.74 98.66 97.22 99.38 99.29 99.34 NaN NaN NaN 57.2 10.9 4375502 4685306 NaN 58.4 58.4 50.25 100 NaN NaN NaN NaN 1.46E+10 NaN -1.62 32.37 4685306 0.91 NaN 26.17 22.48 40.94 41.96 67.88 36.89 40.4 26.73 33.41 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 13.171 34.641 2.08 NaN NaN NaN 10.99 1.47E+09 4685306 NaN 31.63 0.275 0.49 575.109 57.424 -0.89 -0.55 33.7 NaN NaN NaN NaN NaN NaN NaN NaN 33.226 NaN NaN NaN 0.275 0.741 1.52 0.64 8.36 NaN 0 8.44 NaN 15 2 4 6 NaN NaN NaN NaN 0.7 0 4685306 NaN NaN NaN NaN NaN NaN NaN NaN NaN 96 NaN NaN NaN NaN 11.36 5.56 3.9 1.32 NaN NaN NaN 57.424
Yemen 40.1 20.7 48.6 5 2.3 2.25 2.3 12.49 1.27 1.5 38.9 182 46.4 2.5 16.4 NaN NaN 164 44.7 55 27 0.1 0.1 0.1 48 59.162 79.937 54.9 29.2 7.6 0.785 1.51 1.51 1.51 1.51 1.51 1.51 2.93 2.3 35 73.16 54.1 60.6 92.85 77 12.49 1.5 0.33 33.5 28.7 15297789 29161922 NaN 54.9 54.9 168.57 62 0 0 0 NaN 2.76E+10 NaN -1.59 33.09 29161922 0.75 NaN 12.91 11.91 24.88 24.04 33.97 23.16 69.69 22.05 44.77 70.41 60.98 77.89 27.34 18.49 38.51 1.08 1.65 0.35 0 1.594 6.277 NaN 336310 NaN 3.27 8.56 3.70E+09 29161922 NaN 10.05 0.563 NaN NaN 25.1 NaN NaN 36.7 1.5 NaN 0.87 0.96 NaN 3 1.5 0.51 50.456 76.85 2.5 98.49 0.563 0.39 1.04 0.61 0.77 0.47 0.05 2.36 3.237 16 40 11 163 NaN NaN NaN NaN 6.7 4512 29161922 NaN 5 4.25 3.43 15 1.43 15 1.43 0.29 30.7 38.34 4 1.5 NaN 27.19 6.94 2.55 17.7 0 0.4 125 25.1
Zambia 77.9 27.5 54.4 5 3.4 3.75 3.4 17.05 2.76 3 46.7 405 34.6 5.2 4.2 NaN NaN 213 80.4 57.8 23.5 11.3 2.4 5 346 118.305 85.853 65.4 24.7 3.1 1.338 7.85 7.85 7.85 7.85 7.85 7.85 3.67 3.4 83.08 90.6 86.75 91.63 92.56 92.09 17.05 3 17.96 49.6 19.7 15470270 17861030 NaN 65.4 65.4 1.96 39.81 9.67 0 96.99 NaN 2.31E+10 NaN -1.19 66.09 17861030 1.71 NaN 11.43 10.66 12.24 21.41 22.23 20.6 48.22 37.3 43.05 91.9 90.11 93.57 6.95 5.39 8.63 0.65 0.55 0.76 71.13 3.881 11.455 0.19 8904 NaN 75.077 7.77 2.10E+09 17861030 NaN 10.83 0.19 0.28 41.717 21 -0.59 2.93 57.1 3 NaN 3.8 4.08 NaN 3.25 3.5 3.6 27.438 46.224 3.5 10.56 0.19 0.314 65.2 37.87 37.87 0 3.41 5.11 3.762 20 20 13 23 NaN NaN NaN NaN 5.3 0 17861030 NaN 5.75 3.34 3.43 34 3.78 34 3.78 3.05 14 63.62 4 3 NaN NaN NaN NaN NaN 2 5.5 105 21
Zimbabwe 84.3 46.5 70 5 3.7 NaN 3.7 19.04 2.47 4 51.3 259 23.5 2.5 2.9 NaN NaN 458 78.1 46.2 20.9 12.7 3.2 5.7 210 83.249 57.71 76.9 30.7 1.6 1.935 47.52 47.52 47.52 47.52 47.52 47.52 3.77 3.7 88.28 89.19 88.69 93.19 87.59 90.43 19.04 4 31.85 66.8 10.4 12908992 14645468 NaN 76.9 76.9 29.12 41.04 3.3 1.33 51.4 NaN 2.14E+10 NaN -9.4 78.98 14645468 -8.1 NaN 4.95 4.43 5.46 8.09 9.11 7.18 21.77 11.15 16.56 NaN NaN NaN NaN NaN NaN NaN NaN NaN 62.89 5.35 6.464 NaN 282539 NaN 0.665 10.59 2.58E+09 14645468 NaN 6.58 0.639 NaN 99.519 16.36 -3.75 -3.5 44.3 4 2.8 2.53 1.19 0.55074 2.5 2.5 1.94 22.252 104.896 4 29.1 0.639 0.783 35.54 27.21 27.21 0 3.04 10.34 1.931 19 3 10 52 NaN NaN NaN NaN 6.7 0 14645468 NaN 2.2 5.64 4.6 24 3.42 24 3.42 1.59 38 57.77 4 4 2.8 NaN NaN NaN NaN 1 2.65 85 16.36

Pickle

It is quite time consuming to request DSWS for all these codes. Let's save our progress this far using Pickle:

    	
            

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()

The cell bellow can be run to load these variables back into the kernel

    	
            

# 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.

Sorting Out Our Data

The use of ' Country_Values_Table2 ' is only there to deliminate between before and after our 'pickling':

    	
            Country_Values_Table2 = Country_Values_Table.copy()
        
        
    

Certain metrics have to be manipulated for them to be comparable to all others:

    	
            

# 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"])

    	
            

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"])

    	
            

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"])

    	
            

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"])

    	
            

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"])

Country Points Table

We can now apply the points system defined above to our data in ' Country_Values_Table2 '

    	
            

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()

  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.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.2 8.1.1 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..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 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.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
Afghanistan 0.514563 0.65 0.881356 0.6 0.227273 NaN 0.227273 0.60452 0.346821 0.125 0.828829 0.759259 0.888889 0.264368 0.518072 0.742857 0.931034 0.939655 0.144231 0.847134 0.979021 0.025 0.043478 0.034483 0.759494 0.725389 0.162304 0.082645 NaN NaN 0.026455 0.015873 0.015873 0.015873 0.015873 0.015873 0.015873 NaN 0.227273 0.038217 0.064103 0.051282 0.08209 0.125 0.086957 0.60452 0.125 0.686047 0.141975 0.75 0.138095 0.082645 0.082645 0.803571 0.88764 NaN NaN NaN NaN 0.359116 0.380435 0.802139 0.828125 0.821053 0.642105 0.668394 0.638743 0.987342 0.666667 0.948052 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.055249 0.043011 0.247312 0.5 NaN 0.530864 0.534392 0.116162 0.420513 0.58427 NaN NaN 0.10396 NaN NaN 0.104 0.125 NaN 0.188679 0.157303 NaN 0.5 0.375 0.026882 0.927461 0.820106 0.333333 NaN 0.58427 0.089552 0.108911 0.036269 0.014634 0.008264 0.307692 0.55 0.57764 0.283333 0.060241 0.222222 0.06 0.615385 0.5 0.615385 0.5 0.508475 0.986301 0.666667 0.783582 0.72 0.063492 0.045802 0.063492 0.045802 0.104396 0.202703 NaN 0.833333 0.125 NaN 0.864078 0.057692 1 0.236559 NaN 0.054264 NaN 0.10396
Albania 0.165049 0.3 0.211864 0.4 0.681818 NaN 0.681818 0.079096 0.352601 0.875 0.216216 0.37963 0.316239 0.885057 0.144578 0.685714 0.551724 0.12069 0.971154 0.254777 0.293706 0.025 0.043478 0.034483 0.297468 0.336788 0.439791 0.710744 0.905063 0.426357 0.544974 0.645503 0.645503 0.645503 0.645503 0.645503 0.645503 0.69 0.681818 0.732484 0.74359 0.75641 0.843284 0.6875 0.789855 0.079096 0.875 0.715116 0.364198 0.410714 0.357143 0.710744 0.710744 0.369048 1 0.909836 0.009615 1 NaN 0.696133 0.755435 0.850267 0.90625 0.773684 0.842105 0.725389 0.905759 0.550633 0.852564 0.668831 0.725806 0.783333 0.672131 0.258065 0.295082 0.283333 0.216667 0.189655 0.203704 0.660714 0.685083 0.392473 0.72043 0.252809 0.078431 0.006173 0.269841 0.373737 0.569231 0.483146 0.128713 0.310811 0.628713 0.582609 0.339286 0.296 0.875 NaN 0.660377 0.747191 NaN 0.6 1 0.672043 0.404145 0.619048 0.5 0.330935 0.483146 0.348259 0.435644 0.61658 0.57561 0.487603 0.144231 0.79 0.074534 0.15 0.506024 0.074074 0.05 0.153846 0.055556 0.153846 0.055556 0.20339 NaN 0.666667 0.619403 0.96 0.333333 0.351145 0.333333 0.351145 0.565934 0.932432 0.549618 1 0.875 NaN 0.300971 0.461538 0.431034 0.075269 0.675325 0.72093 0.461538 0.628713
Algeria 0.048544 0.12 0.059322 0.6 NaN 0.642857 NaN 0.271186 0.710983 NaN 0.099099 0.148148 0.34188 0.816092 0.421687 NaN NaN 0.456897 0.769231 0.528662 0.58042 0.025 0.043478 0.034483 0.544304 0.212435 0.502618 0.404959 0.462025 0.054264 0.312169 0.624339 0.624339 0.624339 0.624339 0.624339 0.624339 0.37 NaN 0.318471 0.371795 0.326923 0.5 0.479167 0.492754 0.271186 NaN 0.639535 0.530864 0.116071 0.519048 0.404959 0.404959 0.857143 1 0.032787 0.057692 0.041667 NaN 0.187845 0.228261 0.823529 0.786458 0.910526 0.857895 0.953368 0.853403 0.696203 0.384615 0.590909 NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.107143 0.226519 0.204301 0.827957 0.674157 0.519608 0.524691 0.952381 0.611111 0.907692 0.825843 0.445545 0.594595 0.381188 NaN NaN 0.088 NaN 0.8 0.226415 0.05618 NaN 0.8 NaN 0.268817 0.787565 0.502646 NaN 0.985612 0.825843 0.587065 0.059406 0.430052 0.268293 0.07438 0.144231 0.25 0.52795 0.266667 0.46988 0.277778 0.21 NaN NaN NaN NaN 0.118644 0.260274 0.333333 0.149254 0.36 0.333333 0.519084 0.333333 0.519084 0.340659 1 0.19084 1 NaN 0.8 NaN NaN NaN NaN 0.246753 0.031008 0.948718 0.381188
American Samoa NaN NaN NaN 0.6 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 1 NaN NaN NaN NaN NaN 0.006329 NaN NaN 1 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.004762 1 1 NaN 0.370787 NaN NaN NaN NaN 0.679558 NaN 0.449198 0.479167 0.510526 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.151685 NaN 0.030864 NaN NaN 0.707692 NaN 0.346535 0.25 NaN NaN NaN NaN NaN NaN 0.641509 0.511236 NaN 0.6 NaN 0.682796 0.227979 NaN NaN NaN NaN NaN 0.975248 0.487047 0.517073 0.677686 0.009615 0.64 0.10559 0.15 0.144578 0.037037 0.02 NaN NaN NaN NaN 0.423729 0.013699 0.5 0.656716 0.52 NaN 0.091603 NaN 0.091603 0.714286 NaN NaN 0.166667 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Andorra NaN NaN NaN 0.6 NaN NaN NaN NaN 0.791908 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.050955 0.041958 NaN NaN NaN 0.170886 NaN 0.900524 1 0.664557 0.922481 0.645503 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.790698 NaN NaN 0.433333 1 1 NaN 1 NaN NaN NaN NaN 0.889503 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.111111 0.69697 NaN 0.11236 NaN NaN 0.985149 NaN NaN NaN NaN NaN 1 0.511236 NaN 0.6 NaN 0.876344 0.088083 0.412698 NaN NaN 0.11236 0.781095 0.539604 0.756477 0.64878 0.008264 0.009615 0.34 0.006211 0.05 0.012048 0.055556 0.01 NaN NaN NaN NaN 0.008475 0.013699 0.5 0.656716 0.52 NaN 0.564885 NaN 0.564885 0.978022 1 NaN 0.5 NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.985149
    	
            

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()

  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.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.2 8.1.1 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..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 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.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
Afghanistan 0.509091 0.645455 0.896296 0.302885 0.059524 NaN 0.059524 0.598901 0.350785 0 0.895028 0.848315 0.905405 0.319728 0.591837 0.742857 0.942857 0.956522 0.072539 0.875 0.984293 0 0 0 0.814634 0.723958 0.157895 0.044776 NaN NaN 0.021053 0.010417 0.010417 0.010417 0.010417 0.010417 0.010417 NaN 0.059524 0.030303 0.054545 0.042424 0.060976 0.103659 0.066667 0.598901 0 0.696335 0.127168 0.753846 0.133971 0.044776 0.044776 0.804598 0.373206 NaN NaN NaN NaN 0.336585 0.377049 0.814634 0.833333 0.833333 0.646154 0.664948 0.639175 0.987421 0.672956 0.950311 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.048913 0.037634 0.255102 0.5 NaN 0.565714 0.532995 0.111675 0.422886 0.604061 NaN NaN 0.097087 NaN NaN 0.0875 0 NaN 0.173077 0.134615 NaN 0.096154 0.02381 0.019231 0.927461 0.819149 0.071429 NaN 0.604061 0.084577 0.120192 0.067308 0.014354 0 0.629268 0.563107 0.653659 0.52381 0.095238 0.557143 0.257143 0.622642 0.716981 0.622642 0.716981 0.697561 0.994413 0.447115 0.855769 0.706731 0.018072 0.024038 0.018072 0.024038 0.086538 0.088235 NaN 0.331731 0 NaN 0.864078 0.048544 1 0.234043 NaN 0.051095 NaN 0.097087
Albania 0.145455 0.281818 0.192593 0.024038 0.559524 NaN 0.559524 0.071429 0.366492 0.702381 0.430939 0.561798 0.344595 0.931973 0.115646 0.685714 0.585714 0.26087 0.756477 0.328125 0.376963 0 0 0 0.341463 0.333333 0.436842 0.477612 0.912088 0.546961 0.542105 0.640625 0.640625 0.640625 0.640625 0.640625 0.640625 0.705426 0.559524 0.709091 0.715152 0.727273 0.768293 0.621951 0.690909 0.071429 0.702381 0.722513 0.358382 0.407692 0.354067 0.477612 0.477612 0.385057 0.430622 0.920863 0 0.992806 NaN 0.682927 0.754098 0.863415 0.906863 0.784314 0.841026 0.721649 0.907216 0.553459 0.855346 0.68323 0.721311 0.766667 0.666667 0.245902 0.283333 0.266667 0.229508 0.20339 0.254237 0.715385 0.690217 0.387097 0.734694 0.252809 0.078431 0 0.263959 0.370558 0.58209 0.502538 0.206667 0.306122 0.626214 0.588235 0.336134 0.2875 0.702381 NaN 0.514423 0.774038 NaN 0.163462 0.97619 0.658654 0.398964 0.617021 0.22619 0.43787 0.502538 0.348259 0.442308 0.639423 0.574163 0.692308 0.468293 0.796117 0.15122 0.204762 0.7 0.157143 0.209524 0.169811 0 0.169811 0 0.395122 NaN 0.447115 0.461538 0.903846 0.379518 0.403846 0.379518 0.403846 0.596154 0.552941 0.541353 0.336538 0.702381 NaN 0.300971 0.456311 0.581395 0.06383 0.820225 0.737226 0.438017 0.626214
Algeria 0.036364 0.1 0.044444 0.302885 NaN 0.52439 NaN 0.263736 0.73822 NaN 0.331492 0.303371 0.378378 0.863946 0.482993 NaN NaN 0.63587 0.466321 0.604167 0.649215 0 0 0 0.619512 0.208333 0.5 0.248756 0.483516 0.066298 0.310526 0.614583 0.614583 0.614583 0.614583 0.614583 0.614583 0.356589 NaN 0.29697 0.345455 0.309091 0.402439 0.420732 0.412121 0.263736 NaN 0.65445 0.537572 0.092308 0.516746 0.248756 0.248756 0.862069 0.430622 0.107914 0.28777 0.165468 NaN 0.165854 0.224044 0.834146 0.789216 0.916667 0.85641 0.953608 0.85567 0.698113 0.383648 0.596273 NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.123077 0.228261 0.198925 0.836735 0.674157 0.519608 0.56 0.954315 0.609137 0.910448 0.84264 0.553333 0.591837 0.378641 NaN NaN 0.075 NaN 0.805195 0.192308 0.048077 NaN 0.817308 NaN 0.264423 0.782383 0.5 NaN 0.982249 0.84264 0.587065 0.072115 0.461538 0.272727 0.355769 0.468293 0.252427 0.614634 0.495238 0.671429 0.638095 0.509524 NaN NaN NaN NaN 0.258537 0.698324 0.100962 0.100962 0.235577 0.379518 0.524038 0.379518 0.524038 0.317308 0.641176 0.180451 0.336538 NaN 0.805195 NaN NaN NaN NaN 0.213483 0.021898 0.909091 0.378641
American Samoa NaN NaN NaN 0.302885 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.911917 NaN NaN NaN NaN NaN 0 NaN NaN 0.80597 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0 0.80597 0.80597 NaN 0.15311 NaN NaN NaN NaN 0.668293 NaN 0.458537 0.480392 0.519608 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.151685 NaN 0.097143 NaN NaN 0.716418 NaN 0.466667 0.244898 NaN NaN NaN NaN NaN NaN 0.432692 0.466346 NaN 0.163462 NaN 0.668269 0.222798 NaN NaN NaN NaN NaN 0.975962 0.514423 0.516746 0.8125 0 0.650485 0.195122 0.204762 0.204762 0.02381 0.104762 NaN NaN NaN NaN 0.634146 0 0.302885 0.485577 0.442308 NaN 0.057692 NaN 0.057692 0.745192 NaN NaN 0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Andorra NaN NaN NaN 0.302885 NaN NaN NaN NaN 0.811518 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.0625 0.041885 NaN NaN NaN 0.180488 NaN 0.9 0.80597 0.686813 0.933702 0.642105 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.806283 NaN NaN 0.430622 0.80597 0.80597 NaN 0.430622 NaN NaN NaN NaN 0.887805 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.101523 0.695431 NaN 0.106599 NaN NaN 0.985437 NaN NaN NaN NaN NaN 0.802885 0.466346 NaN 0.163462 NaN 0.865385 0.082902 0.409574 NaN NaN 0.106599 0.781095 0.548077 0.774038 0.650718 0 0 0.34466 0 0.02381 0 0.066667 0 NaN NaN NaN NaN 0 0 0.302885 0.485577 0.442308 NaN 0.586538 NaN 0.586538 0.980769 0.641176 NaN 0.086538 NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.985437

Polarity

Certain data-points are better when lower (e.g.: poverty levels), others better when heigher (e.g.: availability of affordable and clean energy). We thus use a Polarity array to denote when which rule is applied.

    	
            

# You will need the ' ESG-DS.csv ' file

Polarity = pd.read_csv("ESG-DS.csv", header = 1).iloc[:,1:3].dropna()

    	
            Polarity
        
        
    
  polar Country Points Table full
0 Negative 1.1.1
1 Negative 1.1.1
2 Negative 1.2.1
3 Negative 1.2.2
4 Positive 1.3.1
... ... ...
143 Positive 17.1.1
144 Positive 17.3.1
145 Positive 17.4.1
146 Negative 17.6.2
147 Positive 17.8.1
    	
            

# 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"

Polarised Table

We may now apply polarity rules to our data-points

    	
            

# Create a data-frame to be populated

Country_Polarised_Points_Table1 = pd.DataFrame(index = Country_Points_Table2.index,

                                               columns = Country_Points_Table2.columns)

    	
            

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

Note that not all the individual country series codes are retrievable (e.g.: 'AAGFORVO')

SDG Aggregate Ranks

    	
            

# 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

Now we tally up the scores:

    	
            

# 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

    	
            SDG_Aggregate_Ranks
        
        
    
  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 Overall  
  Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better) Scores Available Median Points (Higher is better)
Afghanistan 9/10 4 7/7 6 12/14 3 14/15 1 5/5 2 3/3 1 1/5 insufficient scores (<=2) 14/24 3 7/10 5 7/11 2 2/2 2.5 1/2 1 2/2 7.5 0/0 insufficient scores (<=1) 15/15 6.0 13/15 1 6/8 2 118/148 2.50
Albania 9/10 8 7/7 7 14/14 8 15/15 7 5/5 7 3/3 5 4/5 7.5 24/24 5.5 10/10 3.5 9/11 8 2/2 6.5 2/2 5 2/2 7 0/0 insufficient scores (<=1) 15/15 6.0 13/15 5 8/8 6.5 142/148 6.75
Algeria 7/10 8 5/7 8 14/14 5.5 14/15 5 4/5 6.5 3/3 3 4/5 2.5 15/24 3 10/10 6 6/11 3 2/2 5 1/2 2 2/2 4.5 0/0 insufficient scores (<=1) 11/15 5.0 14/15 4 4/8 2.5 116/148 4.75
American Samoa 1/10 insufficient scores (<=4) 0/7 insufficient scores (<=4) 3/14 insufficient scores (<=4) 0/15 insufficient scores (<=6) 1/5 insufficient scores (<=3) 2/3 9 1/5 insufficient scores (<=2) 4/24 insufficient scores (<=8) 5/10 3 4/11 insufficient scores (<=5) 1/2 insufficient scores (<=2) 0/2 insufficient scores (<=1) 0/2 insufficient scores (<=1) 0/0 insufficient scores (<=1) 11/15 9.0 9/15 5 0/8 insufficient scores (<=4) 42/148 7.00
Andorra 2/10 insufficient scores (<=4) 0/7 insufficient scores (<=4) 8/14 8 0/15 insufficient scores (<=6) 2/5 insufficient scores (<=3) 2/3 9 1/5 insufficient scores (<=2) 1/24 insufficient scores (<=8) 4/10 8.5 4/11 insufficient scores (<=5) 2/2 8.5 0/2 insufficient scores (<=1) 2/2 7 0/0 insufficient scores (<=1) 11/15 8.0 10/15 6 1/8 insufficient scores (<=4) 50/148 8.00
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
Virgin Islands (United States) 1/10 insufficient scores (<=4) 0/7 insufficient scores (<=4) 6/14 8 0/15 insufficient scores (<=6) 2/5 insufficient scores (<=3) 2/3 9 1/5 insufficient scores (<=2) 8/24 4 4/10 4.5 4/11 insufficient scores (<=5) 2/2 8.5 0/2 insufficient scores (<=1) 0/2 insufficient scores (<=1) 0/0 insufficient scores (<=1) 11/15 7.0 9/15 4 1/8 insufficient scores (<=4) 51/148 7.00
West Bank and Gaza 4/10 7.5 5/7 5 7/14 6 12/15 6 3/5 6 3/3 1 1/5 insufficient scores (<=2) 14/24 2.5 7/10 6 3/11 insufficient scores (<=5) 1/2 insufficient scores (<=2) 0/2 insufficient scores (<=1) 2/2 8.5 0/0 insufficient scores (<=1) 9/15 7.0 3/15 insufficient scores (<=6) 5/8 1 79/148 6.00
Yemen 10/10 1.5 5/7 2 14/14 5.5 15/15 1 5/5 2 3/3 1 4/5 1 23/24 3 7/10 4 7/11 1 2/2 3 2/2 1.5 2/2 7 0/0 insufficient scores (<=1) 11/15 5.0 14/15 1 8/8 2.5 132/148 2.00
Zambia 10/10 4.5 5/7 3 14/14 3 15/15 3 5/5 4 3/3 2 4/5 4.5 24/24 3 9/10 3 9/11 4 2/2 5 2/2 8 2/2 10 0/0 insufficient scores (<=1) 11/15 6.0 14/15 3.5 4/8 5 133/148 4.00
Zimbabwe 9/10 3 5/7 5 14/14 3 15/15 4 5/5 8 3/3 2 4/5 5 14/24 7 8/10 3 11/11 2 2/2 4 2/2 9 2/2 6 0/0 insufficient scores (<=1) 11/15 6.0 15/15 2 4/8 4.5 124/148 4.25

Overall Results Chart

We can now proceed in creating a horizontal bar graph to overview results on a country level

    	
            

# 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)

Let's view our results in assending order

    	
            Overall_Results_Chart.dropna().sort_values(ascending = True, by = ["Overall Results"])
        
        
    
  Overall Results Countries
Somalia 1.0 Somalia
Yemen 2.0 Yemen
Central African Republic 2.0 Central African Republic
Eritrea 2.0 Eritrea
Afghanistan 2.5 Afghanistan
... ... ...
Netherlands 9.0 Netherlands
Norway 9.0 Norway
Denmark 9.0 Denmark
Iceland 9.0 Iceland
Monaco 9.5 Monaco

Plots

 

    	
            

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

)

The cell bellow produces the same chart via Plotly. Plotly offers a range of advantaged and is more dynamic than what is show this far. Note that in order to use plotly you will need the jupyterlab-chart-editor and @jupyterlab/plotly-extension extensions

    	
            

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()

Saving Our Data In A CSV

    	
            

# ' 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")