ESGGraphPlot.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Data Platform API to request ESG data on Jupyter Notebook\n",
    "\n",
    "## INTRODUCTION\n",
    "\n",
    "Environmental, Social and Governance (ESG) is a set of standards for a company's operations that investors use to evaluate corporate behavior, determine the future financial performance and decide whether it will be beneficial to invest in a company or not. The ESG information from Refinitv enables our clients to benchmark, compare and integrate extra-financial information into their investment processes to identify companies with quality management and reduced risk exposure. Please refer to the [ESG Learning Section](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/quick-start#quickstart-guide-for-esg) on the [Developer Community](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/documentation#esg-user-guide) for more details about the ESG data and its coverage provided by LSEG.\n",
    "\n",
    "This article will demonstrate how we can retrieve [ESG data](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/documentation#esg-data-guide) from [Data Platform (RDP)](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/quick-start#quickstart-guide-for-refinitiv-data-platform). We will be using Python with RDP API to request ESG data on the [Jupyter Notebook](https://jupyter.org/index.html). The notebook allows the user to create and share documents that contain live code, narrative text, visualizations and we can also plot the graph on the notebook.\n",
    "\n",
    "The Jupyter Notebook with Python codes will be provided on GitHub.\n",
    "\n",
    "## PRE-REQUISITES:\n",
    "\n",
    "* Python 3.9 or later version.\n",
    "* Required Python Packages: getpass, json, requests, pandas, numpy, matplotlib.\n",
    "* [Jupyter Notebook](https://jupyter.org/install). You can install Jupyter Notebook on your local machine and then test the example on the machine. Alternate choice is a free Juputer Notebook on cloud environment such as [Azure Notebook](https://notebooks.azure.com/) provided by Microsoft. You can find more details from [this tutorial](https://docs.microsoft.com/en-us/azure/notebooks/tutorial-create-run-jupyter-notebook).If you are not familiar with Jupyter Notebook, the following [tutorial](https://www.datacamp.com/community/tutorials/tutorial-jupyter-notebook?utm_source=adwords_ppc&utm_campaignid=1455363063&utm_adgroupid=65083631748&utm_device=c&utm_keyword=&utm_matchtype=b&utm_network=g&utm_adpostion=1t1&utm_creative=332602034364&utm_targetid=aud-748597547652:dsa-473406581035&utm_loc_interest_ms=&utm_loc_physical_ms=1012728&gclid=CjwKCAjwiZnnBRBQEiwAcWKfYtOonT1GbauG4cpg4BYnMowI6EOcYxUAUTz_ywny2KjyBchUeULgGxoCkoEQAvD_BwE) created by DataCamp may help.\n",
    "* DP account with a permission to access ESG basic or premium data. \n",
    "\n",
    "## IMPREMENTATIONS\n",
    "\n",
    "There are two main steps for the implementation according to [the tutorial](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/tutorials#esg-data-in-python).\n",
    "\n",
    "* Get Access Token from RDP Authentication Endpoint.\n",
    "* Get specific ESG data like the basic score from the REST endpoint. \n",
    "* Display data and plot a graph.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Access Token\n",
    "\n",
    "__Get RDP Username and Password__\n",
    "\n",
    "The user must have a valid RDP account with a permission to access ESG data. The application will use getpass to get the username and password from user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass as gp\n",
    "\n",
    "username=input('Enter RDP username:')\n",
    "password=gp.getpass('Enter RDP Password:')\n",
    "client_id=input('Enter client id or appKey:')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from json import dumps, loads, load\n",
    "from requests import post,get\n",
    "\n",
    "#Get access token from RDP server \n",
    "getTokenEndpoint=\"https://api.refinitiv.com/auth/oauth2/v1/token\"\n",
    "refreshToken=None\n",
    "accessToken=None\n",
    "_header= {}\n",
    "_header['Accept']='application/json'\n",
    "_params={}\n",
    "\n",
    "if refreshToken is None:\n",
    "    _params={\n",
    "        \"username\": username,\n",
    "        \"password\": password,\n",
    "        \"client_id\": client_id,\n",
    "        \"grant_type\": \"password\",\n",
    "        \"scope\": \"trapi\",\n",
    "        \"takeExclusiveSignOnControl\": \"true\"\n",
    "    }\n",
    "else:\n",
    "    _params={\n",
    "\t\t\t\"refresh_token\": refreshToken,\n",
    "\t\t\t\"username\": username,\n",
    "\t\t\t\"grant_type\": \"refresh_token\"\n",
    "    }\n",
    "\n",
    "resp=post(url=getTokenEndpoint,data=_params,headers=_header,auth=(username,\"\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the returned status code from HTTP response is not 200 (OK), we assume that there is an error occurred, therefore, the application will print the status code and the response status message then exit the application. Otherwise, get the Refresh Token and Access Token from the HTTP response message and print it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if resp.status_code!=200:\n",
    "    print(\"Status Code:\",resp.status_code,\" Text:\",dumps(loads(resp.text),indent=4))\n",
    "    exit()\n",
    "else:\n",
    "    #print(dumps(loads(resp.text),indent=4))\n",
    "    from collections import OrderedDict\n",
    "    auth_object=loads(resp.text,object_pairs_hook=OrderedDict)\n",
    "    accessToken=auth_object[\"access_token\"]\n",
    "    refreshToken=auth_object[\"refresh_token\"]\n",
    "\n",
    "print(\"Refresh Token:\",refreshToken)\n",
    "print(\"Access Token:\",accessToken)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retreive ESG score\n",
    "\n",
    "In this example, we want to retrieve the ESG Score standard. The score standard operation returns all available scores for a company with three years of historical data. Application has to use the following Endpoint to retrieve the data.\n",
    "\n",
    "```html\n",
    "https://api.lseg.com/data/environmental-social-governance/v2/views/scores-standard\n",
    "```\n",
    "There is an alternate option for a professional account to get ESG data with full score and it returns scores with full history. The user can use the following Endpoint instead.\n",
    "\n",
    "```html\n",
    "https://api.lseg.com/data/environmental-social-governance/v2/views/scores-full\n",
    "```\n",
    "The application has to send the server certain GET parameters through the requested endpoint/URL. In this case, it has to pass a universe as HTTP get parameters. The universe could be RIC name or ISIN or any other supported value.  \n",
    "The application needs to set a Bearer access token in the Authorization header of the HTTP request message. See below snippet of codes for the API usage to request full ESG score for the RIC MSFT.O."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ricList=\"MSFT.O\"\n",
    "esgScoreFullEndpoint=\"https://api.refinitiv.com/data/environmental-social-governance/v2/views/scores-full?universe=\"\n",
    "resp=get(url=esgScoreFullEndpoint+ricList,headers={\"Authorization\": \"Bearer \" + accessToken})\n",
    "\n",
    "if resp.status_code!=200:\n",
    "    print(\"Status Code:\",resp.status_code,\" Text:\",dumps(loads(resp.text),indent=4))\n",
    "    exit()\n",
    "\n",
    "esg_object=loads(resp.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To process the ESG data, we need to convert the data from JSON object to a pandas data frame. The application need to get the data and column name from JSON object and then re-construct a new dataframe. The applicaiton need to converts the title from a header to numpy arrary and convert data array to numpy array as well. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "headers=esg_object['headers']\n",
    "#Get column headers/titles using lambda\n",
    "titles=map(lambda header:header['title'], headers)\n",
    "\n",
    "dataArray=np.array(esg_object['data'])\n",
    "df=pd.DataFrame(data=dataArray,columns=titles)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It should return the data like the following sample output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if df.empty is False:\n",
    "    print(df.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plot Graph from ESG Score\n",
    "\n",
    "To compare specific ESG data between the years, Jupyter Notebook user may use the mathplotlib to plot the graph.\n",
    "\n",
    "For this example, we want to compare columns \"ESG Score\",\"ESG Combined Score\" and \"Innovation Score\" between the years. I have to create a new data frame from the origina data frame df using the following codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataPlot=pd.DataFrame(df,columns=['Instrument','Period End Date','ESG Score','ESG Combined Score','Innovation Score'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data for the Y-axis is a Period End Date and the X-axis is the ESG scores. We want to display only the year (eg \"2017\", \"2016\") therefore we need to reformat the data in \"Period End Date\" column using below codes. The example also sorting the year ascending. \n",
    "\n",
    "Note that you can change the column in dataPlot to plot the graph for specific data you want. If you have permission to request only a basic score you might need to change the column accordingly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataPlot['Period End Date']= dataPlot['Period End Date'].str.split('-').str[0]\n",
    "dataPlot.sort_values('Period End Date',ascending=True,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It will be displaying the following sample graphs on the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataPlot.plot(x='Period End Date',y=['ESG Score','ESG Combined Score','Innovation Score'],figsize=(14,7))\n",
    "dataPlot.plot(x='Period End Date',y=['ESG Score','ESG Combined Score','Innovation Score'],kind='bar',figsize=(14,7))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare ESG Score for multiple universe\n",
    "\n",
    "The next step, I'm interested in comparing the value of a number of Woman Manager and CO2 Emission Total used by a top tech company such as Microsoft, IBM, Facebook, Google/Alphabet, and Amazon. \n",
    "\n",
    "Based on the details from EDP Swagger page,  we can get the data using basic score endpoint and we just need to pass a list of RIC for each company as a universe parameters. Below are a snippet of codes to demonstrate the API usage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ricList=\"IBM,AMZN.O,MSFT.O,GOOGL.O,FB.O,APPL.O\"\n",
    "esgฺBasicEndpoint=\"https://api.refinitiv.com/data/environmental-social-governance/v1/views/basic?universe=\"\n",
    "resp=get(url=esgฺBasicEndpoint+ricList,headers={\"Authorization\": \"Bearer \" + accessToken})\n",
    "if resp.status_code!=200:\n",
    "    print(\"Status Code:\",resp.status_code,\" Text:\",dumps(loads(resp.text),indent=4))\n",
    "    exit()\n",
    "\n",
    "esg_BasicObject=loads(resp.text)\n",
    "\n",
    "headers=esg_BasicObject['headers']\n",
    "\n",
    "#Get column headers/titles using lambda\n",
    "titles=map(lambda header:header['title'], headers)\n",
    "\n",
    "basicDataArray=np.array(esg_BasicObject['data'])\n",
    "basicDf=pd.DataFrame(data=basicDataArray,columns=titles)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print sample basic score result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if basicDf.empty is False:\n",
    "    print(basicDf.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Display Componay Name__\n",
    "\n",
    "We have an additional requirement to display Company Name rather than using RIC codes. Basically, the data returned by the ESG basic score has only an Instrument name but it does not have a company named. Therefore, I need to create a function for getting the company name from ESG universe data. I found that the ESG universe endpoint can provide the company names that I want. I will use it later when I plot the graph. Anyway, we found that we can't find some RIC code using the universe Endpoint so it will return the original RIC name instead.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "esgUniverseEndpoint=\"https://api.refinitiv.com/data/environmental-social-governance/v2/universe\"\n",
    "\n",
    "resp=get(url=esgUniverseEndpoint,headers={\"Authorization\": \"Bearer \" + accessToken})\n",
    "if resp.status_code!=200:\n",
    "    print(\"Status Code:\",resp.status_code,\" Text:\",dumps(loads(resp.text),indent=4))\n",
    "    exit()\n",
    "\n",
    "esg_universe = loads(resp.text)\n",
    "\n",
    "def GetRicName(ricName):\n",
    "    if 'data' in esg_universe:\n",
    "        searchItem = list(filter(lambda data: data[1]==ricName, list(esg_universe['data'])))\n",
    "        if len(searchItem)>0:\n",
    "            return searchItem[0][2]\n",
    "    return None\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display Company Name for MSFT.O ric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if GetRicName('MSFT.O') is not None:\n",
    "    print(\"MSFT.O is \\\"\"+ GetRicName(\"MSFT.O\")+\"\\\"\")\n",
    "else:\n",
    "    print(\"Unable to find name for MSFT.O\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next step we will be using the data from a **basicDf** dataframe to plot horizontal bar chart for comparing a **Woman Managers** and **CO2 Emission Total** from each company. Base on a result from a dataframe, we found that column number 5 is a data for \"**CO2 Equivalents Emission Total**\" and column number 6 is a data for \"**Women Managers**\", then we will use the index to extract the data. Below are the codes we use to extract both data from the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract CO2, Woman Manager and Instrument from the dataframe and convert to numpy array\n",
    "co2= [val for sublist in np.array(basicDf.iloc[:,5:6]) for val in sublist]\n",
    "\n",
    "woman=[val for sublist in np.array(basicDf.iloc[:,6:7]) for val in sublist] \n",
    "\n",
    "instrument=[val for sublist in np.array(basicDf.iloc[:,0:1]) for val in sublist]  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print data inside co2,woman and instrument variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(co2)\n",
    "print(woman)\n",
    "print(instrument)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next step we need to get a company name for each instrument from the instrument list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "instrumentorg=np.array([])\n",
    "\n",
    "for val in instrument:\n",
    "    if GetRicName(val) is None:\n",
    "        instrumentorg=np.append(instrumentorg,val)\n",
    "    else:\n",
    "        instrumentorg=np.append(instrumentorg,GetRicName(val))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display instrument name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(instrumentorg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last step we construct dataframe for plotting the graph for displaying a data for **CO2 Emission Total** with a **Woman Managers** separately because of the difference between the scale of each data. We will be using the company name as an index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({\"Woman Managers\":woman}, index=instrumentorg)\n",
    "df1.plot.barh(y='Woman Managers')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like currently we do not have \"Woman Managers\" report for IBM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame({\"CO2 Emission Total\":co2}, index=instrumentorg)\n",
    "df2.plot.barh(y='CO2 Emission Total',color=(0.5, 0.25, 0.15, 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For CO2 Emision Totla, it looks like currently we do not have a CO2 report for Amazon.\n",
    "You can change or modify Python codes in this notebook to displaying data from other columns of the dataframe for basic or full ESG Score."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## REFERENCES\n",
    "\n",
    "* [Data Platform Document](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/documentation)\n",
    "* [Data Platform Tutorial](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/tutorials)\n",
    "* [ESG User Guide](https://developers.lseg.com/en/api-catalog/refinitiv-data-platform/refinitiv-data-platform-apis/documentation#esg-data-guide)\n",
    "\n",
    "## DOWNLOADS\n",
    "* [Github](https://github.com/LSEG-API-Samples/Example.RDP.Python.ESGGraphPlot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}