{ "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": [ "\"Open" ] }, { "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", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
GuidelineSpecTagsData CentreNetworkDeviceComments
0Undertake Systemic Impacts MappingUXSocial Equity\\n Accessibility\\n Ideation\\n Res...111NaN
1Assess And Research Visitor NeedsUXSocial Equity\\n Accessibility\\n UI\\n Patterns\\...111NaN
2Research Non-visitors NeedsUXSocial Equity\\n Ideation\\n Research\\n Usabilit...111NaN
3Consider Sustainability In Early IdeationUXUI\\n Ideation\\n Research\\n Software\\n Strategy000NaN
4Account For Stakeholder IssuesUXSocial Equity\\n Accessibility\\n Ideation\\n Res...111NaN
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", "\n", "\n", "\n", " \n", "
\n", "
\n", "
\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", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnergyEmissionsWaterMaterials
Device0.600.630.830.75
Network0.230.220.090.16
Data Centre0.170.150.070.08
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", "\n", "\n", "\n", " \n", "
\n", "
\n", "
\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", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
GuidelineSpecTagsData CentreNetworkDeviceCommentsGRI-301GRI-302GRI-303GRI-305GRI-301 LabelGRI-302 LabelGRI-303 LabelGRI-305 Label
0Undertake Systemic Impacts MappingUXSocial Equity\\n Accessibility\\n Ideation\\n Res...111NaN0.991.00.991.0ModerateModerateModerateModerate
1Assess And Research Visitor NeedsUXSocial Equity\\n Accessibility\\n UI\\n Patterns\\...111NaN0.991.00.991.0ModerateModerateModerateModerate
2Research Non-visitors NeedsUXSocial Equity\\n Ideation\\n Research\\n Usabilit...111NaN0.991.00.991.0ModerateModerateModerateModerate
3Consider Sustainability In Early IdeationUXUI\\n Ideation\\n Research\\n Software\\n Strategy000NaN0.000.00.000.0LowLowLowLow
4Account For Stakeholder IssuesUXSocial Equity\\n Accessibility\\n Ideation\\n Res...111NaN0.991.00.991.0ModerateModerateModerateModerate
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", "\n", "\n", "\n", " \n", "
\n", "
\n", "
\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": [ "" ], "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": [ "" ], "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": [] } ] }