UseCases/Carbon_Footprint_Analytics/Carbon_Footprint_Analytics_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "16bfe802-dd40-4682-b341-d9e3fa28b0e5",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Carbon Footprint Analytics\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcacace8-ee13-414a-9f09-56d45985939d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89c664a9-54a7-4895-9761-50438c59ae26",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    " We all know that Earth's environment is changing, and it has harmful effects on all of us. \n",
    "The rising temperature of Earth is attributed to the Greenhouse effect.\n",
    "The Greenhouse Effect is the way in which heat is trapped close to Earth's surface by “greenhouse gases” \n",
    "Carbon Dioxide (CO2) being the most significant one though there are other gases which are contributors.\n",
    "<br><br>As the climate emergency keeps aggravating, regulatory requirements, will ask for an enterprise view also from a greenhouse gas perspective.\n",
    "Requirements on this type of reporting will increase year by year.\n",
    "<br><br>\n",
    "    We have developed a prototype of a <b>Carbon Footprint Analytics</b> which can be used for this type of analysis and reporting. There are already a lot of data sources available in the company, financial and applications and there data around almost every aspect of a company.\n",
    "The challenge for great analysis and reporting on greenhouse gases will not be so much to create additional data, but the challenge will rather be to transform the existing data into a greenhouse gas emission view of the enterprise.\n",
    "All the enterprise data already available needs to be converted to greenhouse gas emissions.\n",
    "To properly manage and govern all these needed data transformations over time is the pivotal point of greenhouse gas reporting, and this is exactly where the Carbon Footprint Analytics aims at."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "835c1d84-f170-49c9-b315-5c9c2cfbaca5",
   "metadata": {},
   "source": [
    "<img id=\"DM\" src=\"images/architecture.png\" alt=\"architecture\" width=\"1200\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cc1f94b-b9cd-43f7-9f41-c96a1cc38ea8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The basic principle how to use the Carbon Footprint Analytics works is quite simple. It consists of three steps.<br>\n",
    "One, provide your company specific data according to the input interface of the engine.\n",
    "This data will represent all the activities and consumption of the enterprise that entail greenhouse gas emissions.\n",
    "<br>\n",
    "Step two, run the program engine. Let the engine process your input and convert it into the greenhouse gas emissions of your enterprise.<br>\n",
    "Finally, the engine will expose the greenhouse gas footprint of your company."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b62ba5f-1624-478d-9bcb-ac015a7541d7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "835e00d1-8681-41bf-aa05-c6b4b80ab3ad",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Carbon Footprint Calculations</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac48ed1e-342c-4638-967a-0a6fbb76bcbd",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    " <b>Calculating a carbon footprint is seemingly simple, but its complexity grows exponentially as you go more into details </b>\n",
    "<table border=\"0\">\n",
    " <tr>\n",
    "    <td><b style=\"font-size:20px;font-family:Arial;color:#00233C\">Carbon Footprint calculation is basically a sum of products :</b></td>\n",
    "    <td><b style=\"font-size:20px;font-family:Arial;color:#00233C\">But a lot of criteria make it more complex than it seems\n",
    "</b></td>\n",
    " </tr>\n",
    "  <tr>\n",
    "      <td align=\"center\"><b style='font-size:18px;font-family:Arial;color:#00233C'> Consumption X Emission Factor = Greenhouse Gases</b></td>\n",
    "     <td align=\"center\"><b style='font-size:18px;font-family:Arial;color:#00233C'> Emission Factors</b></td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <td align=\"center\"><b style='font-size:18px;font-family:Arial;'>10 MWh X 0.3 Kg CO2e = 3 Kg CO2e</b></td>\n",
    "      <td align=\"center\" style='font-size:18px;font-family:Arial;'><b>Change over Space</b>: not the same GHG emissions for 1kWh produced in Alaska vs California</td>\n",
    "    </tr> \n",
    "     <tr>\n",
    "      <td align=\"center\"> </td>\n",
    "      <td align=\"center\" style='font-size:18px;font-family:Arial;'><b>Change over Time</b>: not the same GHG emissions for 1kWh produced at 12am vs 12pm</td>\n",
    "    </tr> \n",
    "      <tr>\n",
    "          <td align=\"center\" ><b style='font-size:18px;font-family:Arial;'>$\\sum_{i=0}^{n} ca_{i} * ef_{i}$</b></td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td align=\"left\"><b style='font-size:16px;font-family:Arial;color:#00233C;'>Where:</b></td>\n",
    "        <td align=\"left\"><b style='font-size:16px;font-family:Arial;color:#00233C;'>+ Data management challenges</b></td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td align=\"left\" style='font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "        $ca_{i}$ is a qualified company activity that emits greenhouse gases (example consumption of 10MWh of electricity)</td>\n",
    "        <td align=\"left\" style='font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "         * Need to combine very different sources</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <td align=\"left\" style='font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "        $ef_{i}$ is the emission factor related to the activity that define the amount of Greenhouse gas emitted for each unit (example 0.3Kg per MWh consumed )\n",
    "           </td>\n",
    "         <td align=\"left\" style='font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "         * Varying quality, level of detail, units (pound vs Kg)</td>\n",
    "         </tr>  \n",
    " <tr>\n",
    " </tr>\n",
    "</table>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1f71ee0-c5eb-4f42-9fee-bc1ca53cf737",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In a glance, the calculation for carbon footprint looks really easy; take the activities (the consumption) the company has multiply it with emission factors and sum it up.\n",
    "Like when you consume 10 megawatts of electricity with an activity and each MW hour is worth 0.3 kilogram of CO2 equivalents, then the greenhouse gas emissions of these activities are three kilogram CO2 equivalent. <br>\n",
    "In reality, the factors change over space and are regionally different.<br>\n",
    "The carbon footprint of a MW hour depends on how the electricity is produced. Is it based on a brown coal power plant or generated by photovoltaic system? One kWh produced in Alaska is different from one in California. In addition to that factors may change over different times of the day. The electricity mix is different at noon compared to midnight for optimizing the carbon footprint and working towards net zero, these may be valuable differences to be tracked.\n",
    "<br> For Enterprise reporting there are potentially a lot of different data sources to be combined which will differ in quality, level of detail and different units of measurements. Operating this enterprise reporting will get out of hand rather quickly and can cause a lot of non-productive efforts. The carbon footprint analytics is designed to deal with this complexity. The model is flexible to handle all sort of accreditation levels, units of measurements. It is leveraging ClearScape analytics to deal with time travel, handle geospatial challenges and improve data quality via text analysis functions.<br><br>Now that we got why carbon footprint analytics is needed and what calculations it does let's  look at some of the it's tables and the type of output it generates."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ee35dbb-2683-4d75-8a4a-069ea90e3b5d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc479868-a900-4aaf-9208-097fee4645d8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Import python packages, connect to Vantage and explore the dataset</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30cd313d-ef7e-43d0-adfc-546c34f753f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "import warnings\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "# from teradataml.dataframe.dataframe import DataFrame\n",
    "# from teradataml.analytics.sqle import NGramSplitter\n",
    "# from teradataml.dataframe.dataframe import in_schema\n",
    "# from teradataml.context.context import create_context, remove_context, get_context\n",
    "# from teradataml.dataframe.copy_to import copy_to_sql\n",
    "# from teradataml.options.display import display\n",
    "from teradataml import *\n",
    "\n",
    "from teradatasqlalchemy.types import *\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "warnings.filterwarnings('ignore')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5fbcc09-6b8c-41d5-9e42-eeb5158ee9d4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell.<br>Below command will make a connection to the Vantage environment.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb7012f6-8b6a-4d52-b5f6-757fe643f4f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4955511-199c-41aa-b5a9-6a17e8c17a39",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=CarbonFootprintAnalytics.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a4e1b87-ff04-416a-8fbc-c4a09720c65a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ddeb42b-e449-468e-a67b-2a1cf445d09b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. There are two statements in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18d86871-db86-4c82-822c-5d2b93ee063a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_ESG_cloud');\"\n",
    " # takes about 25 seconds, estimated space: 0 MB\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_ESG_local');\" \n",
    "# takes about 1minute 20 seconds, estimated space: 70 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2caf4c34-ec8b-4624-9d0a-05ff0ff7ee8d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5a907b7-6d29-4752-8a86-44cc89b17c80",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93bbb0fe-477f-409a-9667-58f3327935a7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "106771e3-296f-49ca-939d-60738ab1bee7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 3. Data Source and DataModel </b> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bec6a3de-a302-4696-9c98-6875e29df1a8",
   "metadata": {},
   "source": [
    "<img id=\"DM\" src=\"images/data_source.png\" alt=\"co2_calculation\" width=\"1200\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce34b287-f4ab-4206-83c0-9b2739874fb1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data sources for the Carbon Footprint Analytics can be broadly broken down into four categories <ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>Emission Factor</li><li>Geospatial Reference Data</li><li>Greenhouse Gases</li><li>Company's data like electricity consumption, travels etc.</li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Apart from company's activity the standard data is pre-populated. Now let's look at the DataModel of Carbon Footprint Analytics.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccd5a91c-48fd-429c-b45b-466f6875b2fa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Data Model of Carbon Footprint Analytics</p>\n",
    "<img id=\"DM\" src=\"images/DM.png\" alt=\"Data Model\" width=\"1200\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e73e8bb1-851b-4242-9309-9fb26487b6e9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The standard reference tables are pre-built while the other information related to the Enterprise is gathered by the Enterprise's available data. The engine processes the input data and generates the tables needed for its calculations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "855a316b-3100-4436-8126-e9fed3caff43",
   "metadata": {},
   "outputs": [],
   "source": [
    "#standard reference table pre-built in Carbon Footprint Analytics engine\n",
    "df1 = DataFrame(in_schema(\"DEMO_ESG\",\"GHG_Ref\"))\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ad3c580-7a98-4a2a-b3eb-335f1895ff3e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Carbon Footprint Analytics calculates the emissions of three types(called scopes) for each of the event or activity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaeacb4e-97cc-4bce-9e02-71592efa57dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = DataFrame(in_schema(\"DEMO_ESG\",\"Scope_Ref\"))\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69fbf0a0-2141-410b-a9ab-711dc3505682",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "724a1e7e-0996-4ef8-9a24-3d5d4f9f082d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 4. Geospatial Enhancer </b> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92f35efa-77c1-434d-a166-f2c5ec60fc14",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Another important feature in Carbon Footprint Analytics is the Geospatial Enhancer. As we have seen before the carbon footprint calculations differ across the regions; we needed geospatial data to assign the right emission factor for their emissions. We have collected millions of geolocated points from <b>geonames.org</b>, a website that gathers geospatial data from multiple sources.\n",
    "We also integrated electricity grid shape files from the <b>US Environmental Protection Agency</b>, which are necessary to correct the estimate carbon footprint of electricity consumption in the US.<br>Let us take a look at this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34b1481a-f48a-4958-a7bd-1807b82c637d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = DataFrame.from_query(\"select * from DEMO_ESG.Geo_T_Ref sample 10;\")\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c41dd19d-9ba4-44a0-9e3f-9531445c951a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As we can see from the sample data above, The Geospatial enhancer does not only provide coordinates of the locations it also has some additional information about what type of site it is. This information is for both artificial and natural sites in the world.\n",
    "Here you can see for example all the forests in the US."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "519ce128-a825-4f21-bfcb-7c70a0fbba57",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Forests all US\n",
    "query= '''select geo_name,geo_latitude,geo_longitude, country_code_iso,geo_feature_code_name \n",
    "from DEMO_ESG.Geo_T_Ref\n",
    "where country_code_iso = 'US' \n",
    "and geo_feature_code_name like '%forest%';\n",
    "'''\n",
    "\n",
    "out1=DataFrame.from_query(query)\n",
    "geo = out1.to_pandas()\n",
    "fig1 = px.scatter_mapbox(geo, lat=\"geo_latitude\", lon=\"geo_longitude\", hover_name=\"geo_name\", \n",
    "                         color=\"country_code_iso\", zoom=3, height=100)\n",
    "fig1.update_layout(mapbox_style=\"open-street-map\")\n",
    "fig1.update_layout(margin={\"r\":0,\"t\":0,\"l\":0,\"b\":0})\n",
    "fig1.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3f7859f-a813-4cdf-b5c1-e4ca214d47dc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the Geospatial enhancer to get the location of the event/activity for example the list of all the airports is very useful. With geo co-ordinates we can calculate the spherical distance between two airports."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ac71ce3-239e-462a-9df1-095d19fe5424",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4811fde7-a4b4-41a6-b471-a87ec1f6c5b0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 5. Effect View </b> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca438602-295a-413a-8554-9fd6935ccd42",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We embraced all the complexity of carbon footprint calculations in the engine, but the purpose is to provide the end user the simplest experience possible. Hence, we created a single object the <b>Effect View</b>. Once the input information is processed by Carbon Footprint Analytics it generates the Effect View which contains the carbon footprints details of all events in the company that generate greenhouse gas emissions. All the different types of reports and dashboards can be created by this view."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96143c3e-72b9-4445-b9b8-bc2c4f2ea3a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = DataFrame(in_schema(\"DEMO_ESG\",\"Effect\"))\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e9ed2b6-774d-4a29-b432-f5e920343ee6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This table contains the main characteristic of the greenhouse gas emitting event and the calculation details of its carbon footprint in kilogram of CO2 equivalents. It also contains all the details of the carbon footprint calculation of every single event, the Geo location, the protocol used to determine the right emission factor, the type of greenhouse gas emitted, the original and converted units.<br> Let us build some reports from this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ca58e6b-acf8-4e0d-888e-67b15a95cfe7",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = ''' \n",
    "SELECT scope_number ||'_'||scope_category_description as scope_category\n",
    ",count(*) as cnt\n",
    ",sum(co2e_emission) as co2_emission \n",
    "from DEMO_ESG.Effect\n",
    "group by 1;\n",
    "'''\n",
    "\n",
    "out1=DataFrame.from_query(qry)\n",
    "res = out1.to_pandas()\n",
    "\n",
    "res.head(5)\n",
    "ax = res.plot.bar(x='scope_category', y='co2_emission')\n",
    "plt.title(\"Carbon Footprint per activity\")\n",
    "plt.xlabel(\"scope_category\")\n",
    "plt.ylabel(\"co2_emission\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e6c493e-b69f-4169-8dbf-b918bb9206cf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> This chart displays the CO2 emissions for different categories.\n",
    "With the sample data that we currently have, two electricity consumption is by far the most emitting activity in the company."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "032c0049-5346-40e7-a803-19148f34d7d7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us now see the total carbon footprint generated over the years for Geo locations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa860181-b77a-4959-b071-ae553b2b71ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = ''' \n",
    "select   extract (year from begin_valid_date ) as yr \n",
    ",event_geo_name\n",
    ",event_geo_latitude\n",
    ",event_geo_longitude\n",
    ",event_country_code\n",
    ",sum(co2e_emission ) as co2 from DEMO_ESG.Effect group by 1,2,3,4,5;\n",
    "'''\n",
    "\n",
    "out1=DataFrame.from_query(qry)\n",
    "df = out1.to_pandas()\n",
    "\n",
    "fig2 = px.scatter_mapbox(df, lat=\"event_geo_latitude\", lon=\"event_geo_longitude\", hover_name=\"event_geo_name\",size=\"co2\",\n",
    "                         color=\"co2\", size_max=70, zoom=0.75, \n",
    "                         animation_frame=\"yr\",\n",
    "                         category_orders={\"yr\": [2018,2019,2020,2021,2022]}, \n",
    "                         color_continuous_scale=px.colors.sequential.Bluered, \n",
    "                         height = 600\n",
    "                  )\n",
    "fig2.update_layout(mapbox_style=\"open-street-map\")\n",
    "fig2.update_layout(margin={\"r\":0,\"t\":0,\"l\":0,\"b\":0})\n",
    "fig2.update_layout(title_text = 'Carbon Emissions generated over the years' ,title_y=1)\n",
    "fig2.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b295aad-63e8-4d71-8365-261c9c072395",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the map above we can see that San Diego is the biggest point of emission in the map as it this is where the headquarters of the company is located. But in 2020 you can see some emissions from Hawaii which at the first glance looks like a data quality issue but it is actually not. It's due to a global event that took place in Hawaii in 2020."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9621044d-d59c-41b0-abe5-60e79d8423cb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>How much carbon emissions generated by region over the years?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b07b54b-380d-4982-b40a-2e316cdc0181",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = ''' \n",
    "select  extract (year from begin_valid_date ) as yr,\n",
    "event_reporting_region,\n",
    "sum(co2e_emission ) as co2 from DEMO_ESG.Effect group by 1,2;\n",
    "'''\n",
    "\n",
    "out1=DataFrame.from_query(qry)\n",
    "df = out1.to_pandas()\n",
    "\n",
    "fig = px.bar(df, x=\"yr\", y=\"co2\", color=\"event_reporting_region\", barmode=\"group\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dff6333c-0070-4a3c-90cd-9a1e6fa1bc19",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> How much emission is generated per activity?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53511f61-ef41-4cb4-a306-60dc9828cb43",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = ''' \n",
    "select  extract (year from begin_valid_date ) as yr,\n",
    "activity_type_desc as activity\n",
    ",sum(co2e_emission ) as co2 from DEMO_ESG.Effect  group by 1,2 ;\n",
    "'''\n",
    "\n",
    "out1=DataFrame.from_query(qry)\n",
    "df = out1.to_pandas()\n",
    "\n",
    "fig = px.bar(df, x=\"co2\", y=\"yr\", color=\"activity\", orientation='h')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95b6cdc1-3bd9-4c0a-9662-43e23aee51f5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Here we can see from the graph that carbon emission significantly decreased from 2020 to 2021 due to pandemic."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e8f8c0f-1251-4551-86ce-f3b74914d898",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aefd01f2-1c4f-4815-bfdf-d796143338d1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>6. Cleanup </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02a131c5-5360-44fc-bc1c-ff7d62e22d73",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ESG');\" \n",
    "#Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10b516f9-c79b-4ffc-adfd-683774bb97da",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16865256-aefd-4edc-92ec-91004bea8106",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a8e168-eb4f-4218-a394-d64c567e2793",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>7. Conclusion </b> </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bec5e195-1c06-482d-9033-1624fbf4a4c1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demonstration we have seen here that there is a need for companies to calculate their carbon footprint which will increase in future due to its environmental impact. We have seen how the carbon footprint engine works and what are some of the multiple usages that can be done with it. It can really help companies better understanding greenhouse gas emissions and take the best decision possible to decrease their carbon footprint. Based on the emission data generated companies can also build AI/ML models which can predict carbon footprint of a company and they can plan activities for reducing carbon footprint.<br><br>\n",
    "<i>*sample data collected from Jan2018 to Jan2022<br>\n",
    "*Internal reference tables are calculated by engine itself using company's data hence can vary from company to company<br>    \n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a5bb9e8-4e5d-4fc5-a314-bb74e80bb402",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023,2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "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.9.10"
  },
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}