GRI.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNeRiUVova28Sct86LUdj6F",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/w3c/sustyweb/blob/main/Web_Sustainability_GRI_Impact_est.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "This notebook tries to assess environmental impact of W3C Sustainable Web recommendations across a number of categories aligned with GRI reporting.\n",
        "\n",
        "As an input it takes a spreadsheet with recommendations and their indicators of impact on reduction of server resource usage, network transfer and end user device usage.\n",
        "Then it takes data from greenIT.fr report which estimates environmental impact of above categories across material use, water use, energy use and GHG emissions.\n",
        "It then combines these datasets and estimates comparative impact of given recomendation on different sections of GRI taxonomy."
      ],
      "metadata": {
        "id": "LAiULQzYwhvj"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "p2ZlWXdNbTKj"
      },
      "outputs": [],
      "source": [
        "import gspread\n",
        "import pandas as pd\n",
        "from google.colab import auth\n",
        "from google.colab import files\n",
        "from google.auth import credentials\n",
        "from google.auth import default\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# All the recommendations in the spreadsheet are scored in terms of their impact on end user devices, network and servers in data centre\n",
        "# Scores are from 0 to MAX_THRESHOLD, by default 2\n",
        "# If maximum score in the spreadsheet is changed, it needs to updated here\n",
        "MAX_THRESHOLD = 2\n",
        "\n",
        "# Labels to be assigned to metrics\n",
        "labels = {'Low': MAX_THRESHOLD / 3 ,\n",
        "          'Moderate': MAX_THRESHOLD / 3 * 2,\n",
        "          'High': MAX_THRESHOLD }\n",
        "\n",
        "# Set to missing if assessment undefined\n",
        "MISSING = 'Missing'"
      ],
      "metadata": {
        "id": "NaYxpRteOqam"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Auth and read the spreadsheet into pandas\n",
        "auth.authenticate_user()\n",
        "creds, _ = default()\n",
        "gc = gspread.authorize(creds)\n",
        "\n",
        "sheet_url = 'https://docs.google.com/spreadsheets/d/12nGydnSv24fvmvCM-665_pFGPG9u3RgTwe1sCz4eiGk'\n",
        "df = pd.read_csv(sheet_url + '/export?gid=0&format=csv', skiprows=[0, 1])\n",
        "\n",
        "df.head(5)"
      ],
      "metadata": {
        "id": "SV8OdvVkbpNf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "a0185329-29e0-4bdb-bd9a-0bcbcaff9a84"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                   Guideline Spec  \\\n",
              "0         Undertake Systemic Impacts Mapping   UX   \n",
              "1          Assess And Research Visitor Needs   UX   \n",
              "2                Research Non-visitors Needs   UX   \n",
              "3  Consider Sustainability In Early Ideation   UX   \n",
              "4             Account For Stakeholder Issues   UX   \n",
              "\n",
              "                                                Tags  Data Centre  Network  \\\n",
              "0  Social Equity\\n Accessibility\\n Ideation\\n Res...            1        1   \n",
              "1  Social Equity\\n Accessibility\\n UI\\n Patterns\\...            1        1   \n",
              "2  Social Equity\\n Ideation\\n Research\\n Usabilit...            1        1   \n",
              "3     UI\\n Ideation\\n Research\\n Software\\n Strategy            0        0   \n",
              "4  Social Equity\\n Accessibility\\n Ideation\\n Res...            1        1   \n",
              "\n",
              "   Device Comments  \n",
              "0       1      NaN  \n",
              "1       1      NaN  \n",
              "2       1      NaN  \n",
              "3       0      NaN  \n",
              "4       1      NaN  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b9be604a-a8ab-444c-bf1d-6851c602001d\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Guideline</th>\n",
              "      <th>Spec</th>\n",
              "      <th>Tags</th>\n",
              "      <th>Data Centre</th>\n",
              "      <th>Network</th>\n",
              "      <th>Device</th>\n",
              "      <th>Comments</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Undertake Systemic Impacts Mapping</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n Ideation\\n Res...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Assess And Research Visitor Needs</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n UI\\n Patterns\\...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Research Non-visitors Needs</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Ideation\\n Research\\n Usabilit...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Consider Sustainability In Early Ideation</td>\n",
              "      <td>UX</td>\n",
              "      <td>UI\\n Ideation\\n Research\\n Software\\n Strategy</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Account For Stakeholder Issues</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n Ideation\\n Res...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b9be604a-a8ab-444c-bf1d-6851c602001d')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-b9be604a-a8ab-444c-bf1d-6851c602001d button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-b9be604a-a8ab-444c-bf1d-6851c602001d');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-b8827445-13f4-41a5-bff5-64a50087a206\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-b8827445-13f4-41a5-bff5-64a50087a206')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "    background-color: #E8F0FE;\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: #1967D2;\n",
              "    height: 32px;\n",
              "    padding: 0 0 0 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: #E2EBFA;\n",
              "    box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: #174EA6;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart:hover {\n",
              "    background-color: #434B5C;\n",
              "    box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "    filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "    fill: #FFFFFF;\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const charts = await google.colab.kernel.invokeFunction(\n",
              "          'suggestCharts', [key], {});\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-b8827445-13f4-41a5-bff5-64a50087a206 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "# Impact factors across number of areas split by end user devices, network equipment and data centers\n",
        "# Source: GreenIT.fr: https://www.greenit.fr/wp-content/uploads/2019/11/GREENIT_EENM_etude_EN_accessible.pdf\n",
        "data = [  [.6, .63, .83, .75]\n",
        "        , [.23, .22, .09, .16]\n",
        "        , [.17, .15, .07, .08] ]\n",
        "\n",
        "factors = pd.DataFrame(data, columns=['Energy', 'Emissions', 'Water', 'Materials'],\n",
        "                       index=['Device', 'Network', 'Data Centre'])\n",
        "\n",
        "factors.head()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "id": "fesQf4yAd7La",
        "outputId": "b9e336c6-5de5-4eca-9419-abbba56596bb"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             Energy  Emissions  Water  Materials\n",
              "Device         0.60       0.63   0.83       0.75\n",
              "Network        0.23       0.22   0.09       0.16\n",
              "Data Centre    0.17       0.15   0.07       0.08"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ffcdbe42-ce84-408d-9027-3e817d4defb6\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Energy</th>\n",
              "      <th>Emissions</th>\n",
              "      <th>Water</th>\n",
              "      <th>Materials</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Device</th>\n",
              "      <td>0.60</td>\n",
              "      <td>0.63</td>\n",
              "      <td>0.83</td>\n",
              "      <td>0.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Network</th>\n",
              "      <td>0.23</td>\n",
              "      <td>0.22</td>\n",
              "      <td>0.09</td>\n",
              "      <td>0.16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Data Centre</th>\n",
              "      <td>0.17</td>\n",
              "      <td>0.15</td>\n",
              "      <td>0.07</td>\n",
              "      <td>0.08</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ffcdbe42-ce84-408d-9027-3e817d4defb6')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-ffcdbe42-ce84-408d-9027-3e817d4defb6 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-ffcdbe42-ce84-408d-9027-3e817d4defb6');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-035f0964-c0cc-4e8e-82b0-098e1caa2c01\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-035f0964-c0cc-4e8e-82b0-098e1caa2c01')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "    background-color: #E8F0FE;\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: #1967D2;\n",
              "    height: 32px;\n",
              "    padding: 0 0 0 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: #E2EBFA;\n",
              "    box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: #174EA6;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart:hover {\n",
              "    background-color: #434B5C;\n",
              "    box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "    filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "    fill: #FFFFFF;\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const charts = await google.colab.kernel.invokeFunction(\n",
              "          'suggestCharts', [key], {});\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-035f0964-c0cc-4e8e-82b0-098e1caa2c01 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Calculate impacts (numerical)\n",
        "\n",
        "# GRI-301: Materials\n",
        "df['GRI-301'] = df['Data Centre'] * factors['Materials']['Data Centre'] \\\n",
        "              + df['Network'] * factors['Materials']['Network'] \\\n",
        "              + df['Device'] * factors['Materials']['Device']\n",
        "\n",
        "# GRI-302: Enegry\n",
        "df['GRI-302'] = df['Data Centre'] * factors['Energy']['Data Centre'] \\\n",
        "              + df['Network'] * factors['Energy']['Network'] \\\n",
        "              + df['Device'] * factors['Energy']['Device']\n",
        "\n",
        "# GRI-303: Water\n",
        "df['GRI-303'] = df['Data Centre'] * factors['Water']['Data Centre'] \\\n",
        "              + df['Network'] * factors['Water']['Network'] \\\n",
        "              + df['Device'] * factors['Water']['Device']\n",
        "\n",
        "# GRI-305: Emissions\n",
        "df['GRI-305'] = df['Data Centre'] * factors['Emissions']['Data Centre'] \\\n",
        "              + df['Network'] * factors['Emissions']['Network'] \\\n",
        "              + df['Device'] * factors['Emissions']['Device']\n",
        "\n"
      ],
      "metadata": {
        "id": "fWqVehnyb-7I"
      },
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Assign labels to calculated impacts\n",
        "# If assessment for a given recommendation is missing it will be set to MISSING\n",
        "df['GRI-301 Label'] = df.apply(lambda x: 'Low' if x['GRI-301'] < labels['Low']\n",
        "                                               else ('Moderate' if x['GRI-301'] < labels['Moderate']\n",
        "                                                                else 'High' if x['GRI-301'] <= labels['High']\n",
        "                                                                            else MISSING), axis=1 )\n",
        "\n",
        "df['GRI-302 Label'] = df.apply(lambda x: 'Low' if x['GRI-302'] < labels['Low']\n",
        "                                               else ('Moderate' if x['GRI-302'] < labels['Moderate']\n",
        "                                                                else 'High' if x['GRI-302'] <= labels['High']\n",
        "                                                                            else MISSING), axis=1 )\n",
        "\n",
        "df['GRI-303 Label'] = df.apply(lambda x: 'Low' if x['GRI-303'] < labels['Low']\n",
        "                                               else ('Moderate' if x['GRI-303'] < labels['Moderate']\n",
        "                                                                else 'High' if x['GRI-303'] <= labels['High']\n",
        "                                                                            else MISSING), axis=1 )\n",
        "\n",
        "df['GRI-305 Label'] = df.apply(lambda x: 'Low' if x['GRI-305'] < labels['Low']\n",
        "                                               else ('Moderate' if x['GRI-305'] < labels['Moderate']\n",
        "                                                                else 'High' if x['GRI-305'] <= labels['High']\n",
        "                                                                            else MISSING), axis=1 )\n",
        "\n",
        "\n",
        "df.head()"
      ],
      "metadata": {
        "id": "eY6WRm6jQW6A",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 306
        },
        "outputId": "5fe21fbd-4de5-4a32-f318-dd86d3b6b5f1"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                   Guideline Spec  \\\n",
              "0         Undertake Systemic Impacts Mapping   UX   \n",
              "1          Assess And Research Visitor Needs   UX   \n",
              "2                Research Non-visitors Needs   UX   \n",
              "3  Consider Sustainability In Early Ideation   UX   \n",
              "4             Account For Stakeholder Issues   UX   \n",
              "\n",
              "                                                Tags  Data Centre  Network  \\\n",
              "0  Social Equity\\n Accessibility\\n Ideation\\n Res...            1        1   \n",
              "1  Social Equity\\n Accessibility\\n UI\\n Patterns\\...            1        1   \n",
              "2  Social Equity\\n Ideation\\n Research\\n Usabilit...            1        1   \n",
              "3     UI\\n Ideation\\n Research\\n Software\\n Strategy            0        0   \n",
              "4  Social Equity\\n Accessibility\\n Ideation\\n Res...            1        1   \n",
              "\n",
              "   Device Comments  GRI-301  GRI-302  GRI-303  GRI-305 GRI-301 Label  \\\n",
              "0       1      NaN     0.99      1.0     0.99      1.0      Moderate   \n",
              "1       1      NaN     0.99      1.0     0.99      1.0      Moderate   \n",
              "2       1      NaN     0.99      1.0     0.99      1.0      Moderate   \n",
              "3       0      NaN     0.00      0.0     0.00      0.0           Low   \n",
              "4       1      NaN     0.99      1.0     0.99      1.0      Moderate   \n",
              "\n",
              "  GRI-302 Label GRI-303 Label GRI-305 Label  \n",
              "0      Moderate      Moderate      Moderate  \n",
              "1      Moderate      Moderate      Moderate  \n",
              "2      Moderate      Moderate      Moderate  \n",
              "3           Low           Low           Low  \n",
              "4      Moderate      Moderate      Moderate  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-34dd6afa-bbc4-4729-a84b-3fe1a8135d49\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Guideline</th>\n",
              "      <th>Spec</th>\n",
              "      <th>Tags</th>\n",
              "      <th>Data Centre</th>\n",
              "      <th>Network</th>\n",
              "      <th>Device</th>\n",
              "      <th>Comments</th>\n",
              "      <th>GRI-301</th>\n",
              "      <th>GRI-302</th>\n",
              "      <th>GRI-303</th>\n",
              "      <th>GRI-305</th>\n",
              "      <th>GRI-301 Label</th>\n",
              "      <th>GRI-302 Label</th>\n",
              "      <th>GRI-303 Label</th>\n",
              "      <th>GRI-305 Label</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Undertake Systemic Impacts Mapping</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n Ideation\\n Res...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Assess And Research Visitor Needs</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n UI\\n Patterns\\...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Research Non-visitors Needs</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Ideation\\n Research\\n Usabilit...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Consider Sustainability In Early Ideation</td>\n",
              "      <td>UX</td>\n",
              "      <td>UI\\n Ideation\\n Research\\n Software\\n Strategy</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Low</td>\n",
              "      <td>Low</td>\n",
              "      <td>Low</td>\n",
              "      <td>Low</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Account For Stakeholder Issues</td>\n",
              "      <td>UX</td>\n",
              "      <td>Social Equity\\n Accessibility\\n Ideation\\n Res...</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.99</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-34dd6afa-bbc4-4729-a84b-3fe1a8135d49')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-34dd6afa-bbc4-4729-a84b-3fe1a8135d49 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-34dd6afa-bbc4-4729-a84b-3fe1a8135d49');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-af207b48-bb84-4a61-b4b5-1218ea191174\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-af207b48-bb84-4a61-b4b5-1218ea191174')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"https://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "    background-color: #E8F0FE;\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: #1967D2;\n",
              "    height: 32px;\n",
              "    padding: 0 0 0 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: #E2EBFA;\n",
              "    box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: #174EA6;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "    background-color: #3B4455;\n",
              "    fill: #D2E3FC;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart:hover {\n",
              "    background-color: #434B5C;\n",
              "    box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "    filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "    fill: #FFFFFF;\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const charts = await google.colab.kernel.invokeFunction(\n",
              "          'suggestCharts', [key], {});\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-af207b48-bb84-4a61-b4b5-1218ea191174 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Save to excel and download locally\n",
        "\n",
        "df.to_excel('Recommendations scored.xlsx')\n",
        "\n",
        "files.download('Recommendations scored.xlsx')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "5FtienoEvf7_",
        "outputId": "a825f255-4104-4e6a-932f-7ae4fd6c78ef"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_db96e7f6-f5c1-484d-83b8-4c48871b6b04\", \"Recommendations scored.xlsx\", 13991)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "HNoO30Nqd8_T"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}