coefficients.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cloud Carbon Coefficients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# ^ Hide the output of this cell (must be on the first line)\n",
    "\n",
    "# Install dependencies\n",
    "%pip install -r requirements.txt\n",
    "\n",
    "import csv\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import warnings; warnings.simplefilter('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use stage emissions\n",
    "\n",
    "### Constants\n",
    "\n",
    "These constants are used as part of the calculations for the use stage \n",
    "emissions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.cloudcarbonfootprint.org/docs/methodology#memory\n",
    "MEMORY_COEFFICIENT = 0.000392 # kWh / Gb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Embodied emissions\n",
    "\n",
    "### Constants\n",
    "\n",
    "These constants are used as part of the calculations for the embodied emissions\n",
    "factors for each instance type. They're based on [the work done by\n",
    "Teads](https://medium.com/teads-engineering/building-an-aws-ec2-carbon-emissions-dataset-3f0fd76c98ac)\n",
    "and extracted from [the source Google\n",
    "Sheet](https://docs.google.com/spreadsheets/d/1YhtGO_UU9Hc162m7eQKYFQOnV4_yEK5_lgHYfl02JPE/edit#gid=2090774556)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manufacturing emissions for a mono socket, low DRAM, no local storage \n",
    "# commodity rack server\n",
    "BASE_MANUFACTURING_EMISSIONS = 1000 # kgCO2eq\n",
    "\n",
    "# Commodity rack server lifespan\n",
    "RACK_SERVER_LIFESPAN = 4 # years\n",
    "\n",
    "# Hourly manufacturing emissions conversion factor - linearly amortised\n",
    "MANUFACTURING_EMISSIONS = BASE_MANUFACTURING_EMISSIONS / RACK_SERVER_LIFESPAN / 12 / 30 / 24 # gCO2eq/hour\n",
    "\n",
    "# DRAM Threshold to unlock additional Scope 3 emissions\n",
    "DRAM_THRESHOLD = 16 # GB\n",
    "\n",
    "# Manufacturing emissions for the threshold DRAM amount\n",
    "# Based on Dell PowerEdge R740 Life-Cycle Assessment\n",
    "# https://docs.google.com/spreadsheets/d/1YhtGO_UU9Hc162m7eQKYFQOnV4_yEK5_lgHYfl02JPE/edit#gid=954946016\n",
    "# = 533 kgCO₂eq for 12*32GB DIMMs Memory (384 GB).\n",
    "DRAM_MANUFACTURING_EMISSIONS = (533 / 384) * DRAM_THRESHOLD\n",
    "\n",
    "# Manufacturing emissions per additional CPU\n",
    "CPU_MANUFACTURING_EMISSIONS = 100 # kgCO2eq\n",
    "\n",
    "# Manufacturing emissions per additional HDD\n",
    "HDD_MANUFACTURING_EMISSIONS = 50 # kgCO2eq\n",
    "\n",
    "# Manufacturing emissions per additional SSD\n",
    "SSD_MANUFACTURING_EMISSIONS = 100 # kgCO2eq\n",
    "\n",
    "# Manufacturing emissions per additional GPU Card\n",
    "GPU_MANUFACTURING_EMISSIONS = 150 # kgCO2eq\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculations\n",
    "\n",
    "Embodied emissions are based on a representative baseline\n",
    "(`BASE_MANUFACTURING_EMISSIONS`) with additional factor added for extra\n",
    "components - memory, storage, CPUs and GPUs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "azure_cpus = pd.read_csv(f'data/azure-instances-cpus.csv')\n",
    "aws_cpus = pd.read_csv(f'data/aws-instances-cpus.csv')\n",
    "gcp_cpus = pd.read_csv(f'data/gcp-instances-cpus.csv')\n",
    "\n",
    "def calculate_additional_memory_emissions(platform_memory):\n",
    "    \"\"\"If the platform memory is greater than the baseline, calculate the   \n",
    "    additional emissions.\"\"\"\n",
    "\n",
    "    if float(platform_memory) > DRAM_THRESHOLD:\n",
    "        #print(instance)\n",
    "        additional_emissions = float((float(platform_memory) - DRAM_THRESHOLD) * (DRAM_MANUFACTURING_EMISSIONS / DRAM_THRESHOLD))\n",
    "        \n",
    "    else: \n",
    "        additional_emissions = 0.0\n",
    "\n",
    "    return additional_emissions\n",
    "\n",
    "def calculate_additional_storage_emissions(storage_type, drive_quantity):\n",
    "    \"\"\"Calculate additional emissions for storage, depending on the storage \n",
    "    type.\"\"\"\n",
    "\n",
    "    if drive_quantity <= 0:\n",
    "        return 0.0\n",
    "\n",
    "    if storage_type.lower() == 'ssd':\n",
    "        factor = SSD_MANUFACTURING_EMISSIONS\n",
    "    else:\n",
    "        factor = HDD_MANUFACTURING_EMISSIONS\n",
    "\n",
    "    return float(drive_quantity * factor)\n",
    "\n",
    "def calculate_additional_cpu_emissions(platform_name, cpu_name):\n",
    "    \"\"\"Calculate emissions for additional CPUs for the specified cloud\n",
    "    platform.\"\"\"\n",
    "\n",
    "    # For AWS we know the precise CPU\n",
    "    if platform_name == 'aws':\n",
    "        cpus = aws_cpus\n",
    "\n",
    "        cpu = cpus.query(f'`CPU Name` == \\\"{cpu_name}\\\"')\n",
    "\n",
    "        if int(cpu['Platform Number of CPU Socket(s)']) > 0:\n",
    "            return float((int(cpu['Platform Number of CPU Socket(s)']) - 1) * CPU_MANUFACTURING_EMISSIONS)\n",
    "        else:\n",
    "            return 0.0\n",
    "    # For Azure & GCP we only know the general CPU architecture\n",
    "    elif platform_name == 'azure' or 'gcp':\n",
    "        cpus = gcp_cpus\n",
    "\n",
    "        cpu = cpus.query(f'`Microarchitecture` == \\\"{cpu_name}\\\"')\n",
    "\n",
    "        if int(cpu['CPU Sockets']) > 0:\n",
    "            return float((int(cpu['CPU Sockets']) - 1) * CPU_MANUFACTURING_EMISSIONS)\n",
    "        else:\n",
    "            return 0.0\n",
    "    else:\n",
    "        return 0.\n",
    "\n",
    "def calculate_additional_gpu_emissions(gpu_quantity):\n",
    "    \"\"\"Calculate additional emissions for any GPUs.\"\"\"\n",
    "\n",
    "    if gpu_quantity > 0:\n",
    "        return float(gpu_quantity * GPU_MANUFACTURING_EMISSIONS)\n",
    "    else:\n",
    "        return 0.0\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processor types\n",
    "\n",
    "Processors are grouped into types by each vendor e.g. Intel Broadwell CPUs. Cloud providers provide the CPU types for each of their instance types, but not the precise CPU details. As such, we calculate the average wattage by processor type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_append_list(file_name):\n",
    "    \"\"\"Loads a CSV file then returns each row appended to a list.\"\"\"\n",
    "\n",
    "    with open(f'data/{file_name}', 'r') as csvfile:\n",
    "        reader = csv.reader(csvfile)\n",
    "\n",
    "        data = []\n",
    "        for row in reader:\n",
    "            data.append(row[0])\n",
    "        \n",
    "        return data\n",
    "\n",
    "cpus_amd_epyc_gen1 = load_append_list('amd-epyc-gen1.csv')\n",
    "assert 'EPYC 7601' in cpus_amd_epyc_gen1\n",
    "cpus_amd_epyc_gen2 = load_append_list('amd-epyc-gen2.csv')\n",
    "assert 'EPYC 7742' in cpus_amd_epyc_gen2\n",
    "cpus_amd_epyc_gen3 = load_append_list('amd-epyc-gen3.csv')\n",
    "assert 'EPYC 75F3' in cpus_amd_epyc_gen3\n",
    "cpus_intel_sandybridge = load_append_list('intel-sandybridge.csv')\n",
    "assert 'E5-4610' in cpus_intel_sandybridge\n",
    "cpus_intel_ivybridge = load_append_list('intel-ivybridge.csv')\n",
    "assert 'E5-2609 v2' in cpus_intel_ivybridge\n",
    "cpus_intel_haswell = load_append_list('intel-haswell.csv')\n",
    "assert 'E5-2630 v3' in cpus_intel_haswell\n",
    "cpus_intel_broadwell = load_append_list('intel-broadwell.csv')\n",
    "assert 'E5-2683 v4' in cpus_intel_broadwell\n",
    "cpus_intel_skylake = load_append_list('intel-skylake.csv')\n",
    "assert 'Platinum 8160T' in cpus_intel_skylake\n",
    "cpus_intel_cascadelake = load_append_list('intel-cascadelake.csv')\n",
    "assert 'Gold 6230R' in cpus_intel_cascadelake\n",
    "cpus_intel_coffeelake = load_append_list('intel-coffeelake.csv')\n",
    "assert 'E-2246G' in cpus_intel_coffeelake"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processor lists\n",
    "\n",
    "Now we know which processors are in which type, we can group all the tested servers by their CPU type to calculate: average idle watts, average watts at 100% utilization, average GB/chip."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load all servers from SPECpower results CSV\n",
    "servers = pd.read_csv('data/SPECpower-full-results.csv', na_values=['NC'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Regex match\n",
    "\n",
    "The regex to match the CPU names matches to the end of the line using `$` because some chips have version numbers after, so we can't just do a substring match e.g. `Intel E3-1230` is a Sandy Bridge chip but `Intel E3-1230 v3` is Haswell. It is case insensitive due to the use of `v3` and `V3` inconsistently in the definitions.\n",
    "\n",
    "#### Clean data\n",
    "\n",
    "The SPECpower results often appends extra info to the `CPU Description` column which is unecessary. For example, `Intel Xeon E5-2470 (Intel Turbo Boost Technology up to 3.10 GHz)`. This extra info needs to be stripped e.g. to `Intel Xeon E5-2470` otherwise the regex match will not work.\n",
    "\n",
    "The check below will error if the data is not clean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(servers[servers['CPU Description'].str.contains('Ghz')]) > 0:\n",
    "    print('Data not clean')\n",
    "    import sys\n",
    "    sys.exit(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AMD: EPYC Gen 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.82\n",
      "Average: Max Watts = 2.55\n",
      "Average: Max Watts (GCP) = 2.49\n",
      "Average: GB/Chip = 89.60\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_amd_epyc_gen1]\n",
    "servers_amd_epyc_gen1 = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "amd_epyc_gen1 = {}\n",
    "amd_epyc_gen1['Idle watts'] = (servers_amd_epyc_gen1['avg. watts @ active idle'].astype(float) / servers_amd_epyc_gen1['Total Threads']).mean()\n",
    "amd_epyc_gen1['100% watts'] = (servers_amd_epyc_gen1['avg. watts @ 100%'].astype(float) / servers_amd_epyc_gen1['Total Threads']).mean()\n",
    "amd_epyc_gen1['GB/Chip'] = (servers_amd_epyc_gen1['Total Memory (GB)'] / servers_amd_epyc_gen1['Chips']).mean()\n",
    "amd_epyc_gen1['Total memory'] = servers_amd_epyc_gen1['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "amd_epyc_gen1['100% watts GCP adjusted'] = amd_epyc_gen1['100% watts'] - (amd_epyc_gen1['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {amd_epyc_gen1[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen1[\"Idle watts\"].mean())) == 0.82\n",
    "\n",
    "print(f'Average: Max Watts = {amd_epyc_gen1[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen1[\"100% watts\"].mean())) == 2.55\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {amd_epyc_gen1[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen1[\"100% watts GCP adjusted\"].mean())) == 2.49\n",
    "\n",
    "print(f'Average: GB/Chip = {amd_epyc_gen1[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen1[\"GB/Chip\"].mean())) == 89.60"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AMD: EPYC Gen 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.47\n",
      "Average: Max Watts = 1.69\n",
      "Average: Max Watts (GCP) = 1.58\n",
      "Average: GB/Chip = 129.78\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_amd_epyc_gen2]\n",
    "servers_amd_epyc_gen2 = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "amd_epyc_gen2 = {}\n",
    "amd_epyc_gen2['Idle watts'] = (servers_amd_epyc_gen2['avg. watts @ active idle'].astype(float) / servers_amd_epyc_gen2['Total Threads']).mean()\n",
    "amd_epyc_gen2['100% watts'] = (servers_amd_epyc_gen2['avg. watts @ 100%'].astype(float) / servers_amd_epyc_gen2['Total Threads']).mean()\n",
    "amd_epyc_gen2['GB/Chip'] = (servers_amd_epyc_gen2['Total Memory (GB)'] / servers_amd_epyc_gen2['Chips']).mean()\n",
    "amd_epyc_gen2['Total memory'] = servers_amd_epyc_gen2['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "amd_epyc_gen2['100% watts GCP adjusted'] = amd_epyc_gen2['100% watts'] - (amd_epyc_gen2['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {amd_epyc_gen2[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen2[\"Idle watts\"].mean())) == 0.47\n",
    "\n",
    "print(f'Average: Max Watts = {amd_epyc_gen2[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen2[\"100% watts\"].mean())) == 1.69\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {amd_epyc_gen2[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen2[\"100% watts GCP adjusted\"].mean())) == 1.58\n",
    "\n",
    "print(f'Average: GB/Chip = {amd_epyc_gen2[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen2[\"GB/Chip\"].mean())) == 129.78"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### AMD: EPYC Gen 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.45\n",
      "Average: Max Watts = 2.02\n",
      "Average: Max Watts (GCP) = 1.87\n",
      "Average: GB/Chip = 128.00\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_amd_epyc_gen3]\n",
    "servers_amd_epyc_gen3 = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "amd_epyc_gen3 = {}\n",
    "amd_epyc_gen3['Idle watts'] = (servers_amd_epyc_gen3['avg. watts @ active idle'].astype(float) / servers_amd_epyc_gen3['Total Threads']).mean()\n",
    "amd_epyc_gen3['100% watts'] = (servers_amd_epyc_gen3['avg. watts @ 100%'].astype(float) / servers_amd_epyc_gen3['Total Threads']).mean()\n",
    "amd_epyc_gen3['GB/Chip'] = (servers_amd_epyc_gen3['Total Memory (GB)'] / servers_amd_epyc_gen3['Chips']).mean()\n",
    "amd_epyc_gen3['Total memory'] = servers_amd_epyc_gen3['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "amd_epyc_gen3['100% watts GCP adjusted'] = amd_epyc_gen3['100% watts'] - (amd_epyc_gen3['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {amd_epyc_gen3[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen3[\"Idle watts\"].mean())) == 0.45\n",
    "\n",
    "print(f'Average: Max Watts = {amd_epyc_gen3[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen3[\"100% watts\"].mean())) == 2.02\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {amd_epyc_gen3[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen3[\"100% watts GCP adjusted\"].mean())) == 1.87\n",
    "\n",
    "print(f'Average: GB/Chip = {amd_epyc_gen3[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(amd_epyc_gen3[\"GB/Chip\"].mean())) == 128.00"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Sandy Bridge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 2.17\n",
      "Average: Max Watts = 8.58\n",
      "Average: Max Watts (GCP) = 8.55\n",
      "Average: GB/Chip = 16.48\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_sandybridge]\n",
    "servers_intel_sandybridge = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_sandybridge = {}\n",
    "intel_sandybridge['Idle watts'] = (servers_intel_sandybridge['avg. watts @ active idle'].astype(float) / servers_intel_sandybridge['Total Threads']).mean()\n",
    "intel_sandybridge['100% watts'] = (servers_intel_sandybridge['avg. watts @ 100%'].astype(float) / servers_intel_sandybridge['Total Threads']).mean()\n",
    "intel_sandybridge['GB/Chip'] = (servers_intel_sandybridge['Total Memory (GB)'] / servers_intel_sandybridge['Chips']).mean()\n",
    "intel_sandybridge['Total memory'] = servers_intel_sandybridge['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_sandybridge['100% watts GCP adjusted'] = intel_sandybridge['100% watts'] - (intel_sandybridge['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_sandybridge[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_sandybridge[\"Idle watts\"].mean())) == 2.17\n",
    "\n",
    "print(f'Average: Max Watts = {intel_sandybridge[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_sandybridge[\"100% watts\"].mean())) == 8.58\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_sandybridge[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_sandybridge[\"100% watts GCP adjusted\"].mean())) == 8.55\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_sandybridge[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_sandybridge[\"GB/Chip\"].mean())) == 16.48"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Ivy Bridge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 3.04\n",
      "Average: Max Watts = 8.25\n",
      "Average: Max Watts (GCP) = 8.20\n",
      "Average: GB/Chip = 14.93\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_ivybridge]\n",
    "servers_intel_ivybridge = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_ivybridge = {}\n",
    "intel_ivybridge['Idle watts'] = (servers_intel_ivybridge['avg. watts @ active idle'].astype(float) / servers_intel_ivybridge['Total Threads']).mean()\n",
    "intel_ivybridge['100% watts'] = (servers_intel_ivybridge['avg. watts @ 100%'].astype(float) / servers_intel_ivybridge['Total Threads']).mean()\n",
    "intel_ivybridge['GB/Chip'] = (servers_intel_ivybridge['Total Memory (GB)'] / servers_intel_ivybridge['Chips']).mean()\n",
    "intel_ivybridge['Total memory'] = servers_intel_ivybridge['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_ivybridge['100% watts GCP adjusted'] = intel_ivybridge['100% watts'] - (intel_ivybridge['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_ivybridge[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_ivybridge[\"Idle watts\"].mean())) == 3.04\n",
    "\n",
    "print(f'Average: Max Watts = {intel_ivybridge[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_ivybridge[\"100% watts\"].mean())) == 8.25\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_ivybridge[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_ivybridge[\"100% watts GCP adjusted\"].mean())) == 8.20\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_ivybridge[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_ivybridge[\"GB/Chip\"].mean())) == 14.93"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Haswell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 1.90\n",
      "Average: Max Watts = 6.01\n",
      "Average: Max Watts (GCP) = 5.97\n",
      "Average: GB/Chip = 27.31\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_haswell]\n",
    "servers_intel_haswell = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_haswell = {}\n",
    "intel_haswell['Idle watts'] = (servers_intel_haswell['avg. watts @ active idle'].astype(float) / servers_intel_haswell['Total Threads']).mean()\n",
    "intel_haswell['100% watts'] = (servers_intel_haswell['avg. watts @ 100%'].astype(float) / servers_intel_haswell['Total Threads']).mean()\n",
    "intel_haswell['GB/Chip'] = (servers_intel_haswell['Total Memory (GB)'] / servers_intel_haswell['Chips']).mean()\n",
    "intel_haswell['Total memory'] = servers_intel_haswell['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_haswell['100% watts GCP adjusted'] = intel_haswell['100% watts'] - (intel_haswell['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_haswell[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_haswell[\"Idle watts\"].mean())) == 1.90\n",
    "\n",
    "print(f'Average: Max Watts = {intel_haswell[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_haswell[\"100% watts\"].mean())) == 6.01\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_haswell[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_haswell[\"100% watts GCP adjusted\"].mean())) == 5.97\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_haswell[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_haswell[\"GB/Chip\"].mean())) == 27.31"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Broadwell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.71\n",
      "Average: Max Watts = 3.69\n",
      "Average: Max Watts (GCP) = 3.39\n",
      "Average: GB/Chip = 69.65\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_broadwell]\n",
    "servers_intel_broadwell = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_broadwell = {}\n",
    "intel_broadwell['Idle watts'] = (servers_intel_broadwell['avg. watts @ active idle'].astype(float) / servers_intel_broadwell['Total Threads']).mean()\n",
    "intel_broadwell['100% watts'] = (servers_intel_broadwell['avg. watts @ 100%'].astype(float) / servers_intel_broadwell['Total Threads']).mean()\n",
    "intel_broadwell['GB/Chip'] = (servers_intel_broadwell['Total Memory (GB)'] / servers_intel_broadwell['Chips']).mean()\n",
    "intel_broadwell['Total memory'] = servers_intel_broadwell['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_broadwell['100% watts GCP adjusted'] = intel_broadwell['100% watts'] - (intel_broadwell['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_broadwell[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_broadwell[\"Idle watts\"].mean())) == 0.71\n",
    "\n",
    "print(f'Average: Max Watts = {intel_broadwell[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_broadwell[\"100% watts\"].mean())) == 3.69\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_broadwell[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_broadwell[\"100% watts GCP adjusted\"].mean())) == 3.39\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_broadwell[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_broadwell[\"GB/Chip\"].mean())) == 69.65"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Skylake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.64\n",
      "Average: Max Watts = 4.19\n",
      "Average: Max Watts (GCP) = 3.90\n",
      "Average: GB/Chip = 80.43\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_skylake]\n",
    "servers_intel_skylake = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_skylake = {}\n",
    "intel_skylake['Idle watts'] = (servers_intel_skylake['avg. watts @ active idle'].astype(float) / servers_intel_skylake['Total Threads']).mean()\n",
    "intel_skylake['100% watts'] = (servers_intel_skylake['avg. watts @ 100%'].astype(float) / servers_intel_skylake['Total Threads']).mean()\n",
    "intel_skylake['GB/Chip'] = (servers_intel_skylake['Total Memory (GB)'] / servers_intel_skylake['Chips']).mean()\n",
    "intel_skylake['Total memory'] = servers_intel_skylake['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_skylake['100% watts GCP adjusted'] = intel_skylake['100% watts'] - (intel_skylake['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_skylake[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_skylake[\"Idle watts\"].mean())) == 0.64\n",
    "\n",
    "print(f'Average: Max Watts = {intel_skylake[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_skylake[\"100% watts\"].mean())) == 4.19\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_skylake[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_skylake[\"100% watts GCP adjusted\"].mean())) == 3.90\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_skylake[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_skylake[\"GB/Chip\"].mean())) == 80.43"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Cascade Lake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 0.64\n",
      "Average: Max Watts = 3.97\n",
      "Average: Max Watts (GCP) = 3.64\n",
      "Average: GB/Chip = 98.12\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_cascadelake]\n",
    "servers_intel_cascadelake = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_cascadelake = {}\n",
    "intel_cascadelake['Idle watts'] = (servers_intel_cascadelake['avg. watts @ active idle'].astype(float) / servers_intel_cascadelake['Total Threads']).mean()\n",
    "intel_cascadelake['100% watts'] = (servers_intel_cascadelake['avg. watts @ 100%'].astype(float) / servers_intel_cascadelake['Total Threads']).mean()\n",
    "intel_cascadelake['GB/Chip'] = (servers_intel_cascadelake['Total Memory (GB)'] / servers_intel_cascadelake['Chips']).mean()\n",
    "intel_cascadelake['Total memory'] = servers_intel_cascadelake['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_cascadelake['100% watts GCP adjusted'] = intel_cascadelake['100% watts'] - (intel_cascadelake['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_cascadelake[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_cascadelake[\"Idle watts\"].mean())) == 0.64\n",
    "\n",
    "print(f'Average: Max Watts = {intel_cascadelake[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_cascadelake[\"100% watts\"].mean())) == 3.97\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_cascadelake[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_cascadelake[\"100% watts GCP adjusted\"].mean())) == 3.64\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_cascadelake[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_cascadelake[\"GB/Chip\"].mean())) == 98.12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intel: Coffee Lake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average: Min Watts = 1.14\n",
      "Average: Max Watts = 5.42\n",
      "Average: Max Watts (GCP) = 5.41\n",
      "Average: GB/Chip = 19.56\n"
     ]
    }
   ],
   "source": [
    "# Construct regex to match the chip name exactly to the end of the line\n",
    "# (See notes above on regex and clean data)\n",
    "cpus_re = [rf'(?i)(\\b{string}$)' for string in cpus_intel_coffeelake]\n",
    "servers_intel_coffeelake = servers[servers['CPU Description'].str.contains('|'.join(cpus_re))]\n",
    "\n",
    "intel_coffeelake = {}\n",
    "intel_coffeelake['Idle watts'] = (servers_intel_coffeelake['avg. watts @ active idle'].astype(float) / servers_intel_coffeelake['Total Threads']).mean()\n",
    "intel_coffeelake['100% watts'] = (servers_intel_coffeelake['avg. watts @ 100%'].astype(float) / servers_intel_coffeelake['Total Threads']).mean()\n",
    "intel_coffeelake['GB/Chip'] = (servers_intel_coffeelake['Total Memory (GB)'] / servers_intel_coffeelake['Chips']).mean()\n",
    "intel_coffeelake['Total memory'] = servers_intel_coffeelake['Total Memory (GB)']\n",
    "\n",
    "# Adjustment for GCP\n",
    "# See https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/issues/73\n",
    "intel_coffeelake['100% watts GCP adjusted'] = intel_coffeelake['100% watts'] - (intel_coffeelake['Total memory'] * MEMORY_COEFFICIENT)\n",
    "\n",
    "print(f'Average: Min Watts = {intel_coffeelake[\"Idle watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_coffeelake[\"Idle watts\"].mean())) == 1.14\n",
    "\n",
    "print(f'Average: Max Watts = {intel_coffeelake[\"100% watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_coffeelake[\"100% watts\"].mean())) == 5.42\n",
    "\n",
    "print(f'Average: Max Watts (GCP) = {intel_coffeelake[\"100% watts GCP adjusted\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_coffeelake[\"100% watts GCP adjusted\"].mean())) == 5.41\n",
    "\n",
    "print(f'Average: GB/Chip = {intel_coffeelake[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(intel_coffeelake[\"GB/Chip\"].mean())) == 19.56"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Azure\n",
    "\n",
    "### Use stage coefficients\n",
    "\n",
    "These values go in [`packages/azure/src/domain/AzureFootprintEstimationConstants.ts`](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/blob/trunk/packages/azure/src/domain/AzureFootprintEstimationConstants.ts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No match for CPU: Unknown\n",
      "Average: Min Watts = 0.85\n",
      "Average: Max Watts = 3.69\n",
      "Average: GB/Chip = 80.30\n"
     ]
    }
   ],
   "source": [
    "azure_instances = pd.read_csv('data/azure-instances.csv', na_values=['NC'])\n",
    "azure_architectures = azure_instances['Microarchitecture'].unique()\n",
    "azure_coefficients = []\n",
    "\n",
    "for cpu in azure_architectures:\n",
    "    if cpu == 'EPYC 1st Gen':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen1['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen1['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen1['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'EPYC 2nd Gen':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen2['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen2['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen2['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'EPYC 3rd Gen':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen3['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen3['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen3['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Sandy Bridge':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_sandybridge['Idle watts'],\n",
    "            'Max Watts': intel_sandybridge['100% watts'],\n",
    "            'GB/Chip': intel_sandybridge['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Ivy Bridge':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_ivybridge['Idle watts'],\n",
    "            'Max Watts': intel_ivybridge['100% watts'],\n",
    "            'GB/Chip': intel_ivybridge['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Haswell':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_haswell['Idle watts'],\n",
    "            'Max Watts': intel_haswell['100% watts'],\n",
    "            'GB/Chip': intel_haswell['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Broadwell':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_broadwell['Idle watts'],\n",
    "            'Max Watts': intel_broadwell['100% watts'],\n",
    "            'GB/Chip': intel_broadwell['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Skylake':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_skylake['Idle watts'],\n",
    "            'Max Watts': intel_skylake['100% watts'],\n",
    "            'GB/Chip': intel_skylake['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Cascade Lake':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_cascadelake['Idle watts'],\n",
    "            'Max Watts': intel_cascadelake['100% watts'],\n",
    "            'GB/Chip': intel_cascadelake['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Coffee Lake':\n",
    "        azure_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_coffeelake['Idle watts'],\n",
    "            'Max Watts': intel_coffeelake['100% watts'],\n",
    "            'GB/Chip': intel_coffeelake['GB/Chip']\n",
    "        })\n",
    "\n",
    "    else:\n",
    "        print(f'No match for CPU: {cpu}')    \n",
    "\n",
    "azure_coefficients = pd.DataFrame(azure_coefficients)\n",
    "azure_coefficients = azure_coefficients.drop_duplicates(ignore_index=True)\n",
    "\n",
    "print(f'Average: Min Watts = {azure_coefficients[\"Min Watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(azure_coefficients[\"Min Watts\"].mean())) == 0.85\n",
    "\n",
    "print(f'Average: Max Watts = {azure_coefficients[\"Max Watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(azure_coefficients[\"Max Watts\"].mean())) == 3.69\n",
    "\n",
    "print(f'Average: GB/Chip = {azure_coefficients[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(azure_coefficients[\"GB/Chip\"].mean())) == 80.30\n",
    "\n",
    "azure_coefficients.to_csv('output/coefficients-azure-use.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Embodied emissions\n",
    "\n",
    "Outputs embodied emissions values for each instance type and CPU architecture.\n",
    "\n",
    "The list of instances with embodied emissions is output to\n",
    "`output/coefficients-azure-embodied.csv` ready for import into\n",
    "[CCF](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "azure_instances_embodied = []\n",
    "\n",
    "for key, instance in azure_instances.iterrows():\n",
    "    # Call our calculation methods for each of the additional components\n",
    "    additional_memory = calculate_additional_memory_emissions(\n",
    "        instance['Platform Memory'])\n",
    "\n",
    "    additional_storage = calculate_additional_storage_emissions(\n",
    "        instance['Platform Storage Type'],\n",
    "        instance['Platform (largest instance) Storage Drive quantity'])\n",
    "\n",
    "    additional_cpus = calculate_additional_cpu_emissions(\n",
    "        'azure',\n",
    "        instance['Microarchitecture'])\n",
    "\n",
    "    additional_gpus = calculate_additional_gpu_emissions(\n",
    "        instance['Platform GPU'])\n",
    "    \n",
    "    # Build a dictionary of the instance emissions\n",
    "    azure_instances_embodied.append({\n",
    "        'family': instance['Series'],\n",
    "        'type': instance['Virtual Machine'],\n",
    "        'microarchitecture': instance['Microarchitecture'],\n",
    "        'additional_memory': round(additional_memory, 2),\n",
    "        'additional_storage': round(additional_storage, 2),\n",
    "        'additional_cpus': round(additional_cpus, 2),\n",
    "        'additional_gpus': round(additional_gpus, 2),\n",
    "        'total': round(BASE_MANUFACTURING_EMISSIONS + additional_memory + additional_storage + additional_cpus + additional_gpus, 2)\n",
    "    })\n",
    "\n",
    "azure_instances_embodied = pd.DataFrame(azure_instances_embodied)\n",
    "\n",
    "# Pick some random instances to test the results are as expected\n",
    "result = azure_instances_embodied.query('type == \"A1 v2\"')\n",
    "assert np.isclose(result['additional_memory'], 66.62)\n",
    "assert np.isclose(result['additional_storage'], 50.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 0.0)\n",
    "assert np.isclose(result['total'], 1216.62)\n",
    "\n",
    "result = azure_instances_embodied.query('type == \"NC24s v3\"')\n",
    "assert np.isclose(result['additional_memory'], 599.62)\n",
    "assert np.isclose(result['additional_storage'], 50.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 600.0)\n",
    "assert np.isclose(result['total'], 2349.62)\n",
    "\n",
    "result = azure_instances_embodied.query('type == \"S896om\"')\n",
    "assert np.isclose(result['additional_memory'], 51145.79)\n",
    "assert np.isclose(result['additional_storage'], 50.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 0.0)\n",
    "assert np.isclose(result['total'], 52295.79)\n",
    "\n",
    "azure_instances_embodied\n",
    "azure_instances_embodied.to_csv('output/coefficients-azure-embodied.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## AWS\n",
    "\n",
    "### Use stage coefficients\n",
    "\n",
    "These values go in [`packages/aws/src/domain/AwsFootprintEstimationConstants.ts`](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/blob/trunk/packages/aws/src/domain/AwsFootprintEstimationConstants.ts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No match for CPU: 8375C\n",
      "Average: Min Watts = 1.14\n",
      "Average: Max Watts = 4.34\n",
      "Average: GB/Chip = 73.22\n"
     ]
    }
   ],
   "source": [
    "aws_instances = pd.read_csv('data/aws-instances.csv', na_values=['NC'])\n",
    "aws_platforms = aws_instances['Platform CPU Name'].unique()\n",
    "aws_coefficients = []\n",
    "aws_platforms_check = []\n",
    "\n",
    "for cpu in aws_platforms:\n",
    "    # Clean the CSV data to allow it to match with CPU lists\n",
    "    cpu = cpu.replace('Xeon Platinum', '')\n",
    "    cpu = cpu.replace('Xeon', '')\n",
    "    cpu = cpu.strip()\n",
    "\n",
    "    if cpu in cpus_amd_epyc_gen1:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'EPYC 1st Gen',\n",
    "            'Min Watts': amd_epyc_gen1['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen1['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen1['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_amd_epyc_gen2:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'EPYC 2nd Gen',\n",
    "            'Min Watts': amd_epyc_gen2['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen2['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen2['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_sandybridge:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Sandy Bridge',\n",
    "            'Min Watts': intel_sandybridge['Idle watts'],\n",
    "            'Max Watts': intel_sandybridge['100% watts'],\n",
    "            'GB/Chip': intel_sandybridge['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_ivybridge:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Ivy Bridge',\n",
    "            'Min Watts': intel_ivybridge['Idle watts'],\n",
    "            'Max Watts': intel_ivybridge['100% watts'],\n",
    "            'GB/Chip': intel_ivybridge['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "    \n",
    "    elif cpu in cpus_intel_haswell:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Haswell',\n",
    "            'Min Watts': intel_haswell['Idle watts'],\n",
    "            'Max Watts': intel_haswell['100% watts'],\n",
    "            'GB/Chip': intel_haswell['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_broadwell:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Broadwell',\n",
    "            'Min Watts': intel_broadwell['Idle watts'],\n",
    "            'Max Watts': intel_broadwell['100% watts'],\n",
    "            'GB/Chip': intel_broadwell['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_skylake:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Sky Lake',\n",
    "            'Min Watts': intel_skylake['Idle watts'],\n",
    "            'Max Watts': intel_skylake['100% watts'],\n",
    "            'GB/Chip': intel_skylake['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_cascadelake:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Cascade Lake',\n",
    "            'Min Watts': intel_cascadelake['Idle watts'],\n",
    "            'Max Watts': intel_cascadelake['100% watts'],\n",
    "            'GB/Chip': intel_cascadelake['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu in cpus_intel_coffeelake:\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Coffee Lake',\n",
    "            'Min Watts': intel_coffeelake['Idle watts'],\n",
    "            'Max Watts': intel_coffeelake['100% watts'],\n",
    "            'GB/Chip': intel_coffeelake['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    # We don't know the values for the Graviton chips so\n",
    "    # assume they are the same spec as AMD EPYC Gen 2 but listed separately\n",
    "    elif cpu == 'Graviton':\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Graviton',\n",
    "            'Min Watts': amd_epyc_gen2['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen2['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen2['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "\n",
    "    elif cpu == 'Graviton2':\n",
    "        aws_coefficients.append({\n",
    "            'Architecture': 'Graviton2',\n",
    "            'Min Watts': amd_epyc_gen2['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen2['100% watts'],\n",
    "            'GB/Chip': amd_epyc_gen2['GB/Chip']\n",
    "        })\n",
    "        aws_platforms_check.append(cpu)\n",
    "    \n",
    "    else:\n",
    "        print(f'No match for CPU: {cpu}')\n",
    "\n",
    "# We expect to detect the following platforms\n",
    "# Based on https://docs.google.com/spreadsheets/d/1YhtGO_UU9Hc162m7eQKYFQOnV4_yEK5_lgHYfl02JPE/edit#gid=1695769209\n",
    "assert 'E5-2666 v3' in aws_platforms_check\n",
    "assert 'E5-2676 v3' in aws_platforms_check\n",
    "assert 'E5-2686 v4' in aws_platforms_check\n",
    "assert 'E5-2650' in aws_platforms_check\n",
    "assert 'E5-2665' in aws_platforms_check\n",
    "assert 'E5-2670' in aws_platforms_check\n",
    "assert 'E5-2651 v2' in aws_platforms_check\n",
    "assert 'E5-2670 v2' in aws_platforms_check\n",
    "assert 'E5-2680 v2' in aws_platforms_check\n",
    "assert 'E7-8880 v3' in aws_platforms_check\n",
    "assert '8124M' in aws_platforms_check\n",
    "assert '8151' in aws_platforms_check\n",
    "assert '8175M' in aws_platforms_check\n",
    "assert '8176M' in aws_platforms_check\n",
    "assert '8252C' in aws_platforms_check\n",
    "assert '8259CL' in aws_platforms_check\n",
    "assert '8275CL' in aws_platforms_check\n",
    "# Commented out due to lack of Ice Lake SPECpower results\n",
    "#assert '8375C' in aws_platforms_check\n",
    "assert 'EPYC 7571' in aws_platforms_check\n",
    "assert 'EPYC 7R32' in aws_platforms_check\n",
    "assert 'Graviton' in aws_platforms_check\n",
    "assert 'Graviton2' in aws_platforms_check\n",
    "assert 'Core i7-8700B' in aws_platforms_check\n",
    "\n",
    "aws_coefficients = pd.DataFrame(aws_coefficients)\n",
    "aws_coefficients = aws_coefficients.drop_duplicates(ignore_index=True)\n",
    "\n",
    "print(f'Average: Min Watts = {aws_coefficients[\"Min Watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(aws_coefficients[\"Min Watts\"].mean())) == 1.14\n",
    "\n",
    "print(f'Average: Max Watts = {aws_coefficients[\"Max Watts\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(aws_coefficients[\"Max Watts\"].mean())) == 4.34\n",
    "\n",
    "print(f'Average: GB/Chip = {aws_coefficients[\"GB/Chip\"].mean():,.2f}')\n",
    "assert float('{:,.2f}'.format(aws_coefficients[\"GB/Chip\"].mean())) == 73.22\n",
    "\n",
    "aws_coefficients.to_csv('output/coefficients-aws-use.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Embodied emissions\n",
    "\n",
    "The list of instances with embodied emissions is output to\n",
    "`output/coefficients-aws-embodied.csv` ready for import into\n",
    "[CCF](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aws_instances_embodied = []\n",
    "\n",
    "for key, instance in aws_instances.iterrows():\n",
    "    # Call our calculation methods for each of the additional components\n",
    "    additional_memory = calculate_additional_memory_emissions(\n",
    "        instance['Platform Memory (in GB)'])\n",
    "\n",
    "    additional_storage = calculate_additional_storage_emissions(\n",
    "        instance['Storage Type'],\n",
    "        instance['Platform Storage Drive Quantity'])\n",
    "\n",
    "    additional_cpus = calculate_additional_cpu_emissions(\n",
    "        'aws',\n",
    "        instance['Platform CPU Name'])\n",
    "    \n",
    "    additional_gpus = calculate_additional_gpu_emissions(\n",
    "        instance['Platform GPU Quantity'])\n",
    "\n",
    "    # Build a dictionary of the instance emissions\n",
    "    aws_instances_embodied.append({\n",
    "        'type': instance['Instance type'],\n",
    "        'additional_memory': round(additional_memory, 2),\n",
    "        'additional_storage': round(additional_storage, 2),\n",
    "        'additional_cpus': round(additional_cpus, 2),\n",
    "        'additional_gpus': round(additional_gpus, 2),\n",
    "        'total': round(BASE_MANUFACTURING_EMISSIONS + additional_memory + additional_storage + additional_cpus + additional_gpus, 2)})\n",
    "\n",
    "aws_instances_embodied = pd.DataFrame(aws_instances_embodied)\n",
    "\n",
    "# Pick some random instances to test the results are as expected\n",
    "result = aws_instances_embodied.query('type == \"a1.medium\"')\n",
    "assert np.isclose(result['additional_memory'], 22.21)\n",
    "assert np.isclose(result['additional_storage'], 0)\n",
    "assert np.isclose(result['additional_cpus'], 0)\n",
    "assert np.isclose(result['additional_gpus'], 0)\n",
    "assert np.isclose(result['total'], 1022.21)\n",
    "\n",
    "result = aws_instances_embodied.query('type == \"c3.xlarge\"')\n",
    "assert np.isclose(result['additional_memory'], 61.07)\n",
    "assert np.isclose(result['additional_storage'], 200.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 0)\n",
    "assert np.isclose(result['total'], 1361.07)\n",
    "\n",
    "result = aws_instances_embodied.query('type == \"g4dn.xlarge\"')\n",
    "assert np.isclose(result['additional_memory'], 510.79)\n",
    "assert np.isclose(result['additional_storage'], 200.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 1200.0)\n",
    "assert np.isclose(result['total'], 3010.79)\n",
    "\n",
    "aws_instances_embodied.to_csv('output/coefficients-aws-embodied.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GCP\n",
    "\n",
    "### Use stage coefficients\n",
    "\n",
    "These values go in [`packages/gcp/src/domain/GcpFootprintEstimationConstants.ts`](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint/blob/trunk/packages/gcp/src/domain/GcpFootprintEstimationConstants.ts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Median: Min Watts = 0.68\n",
      "Median: Max Watts = 3.77\n"
     ]
    }
   ],
   "source": [
    "gcp_instances = pd.read_csv('data/gcp-instances.csv', na_values=['NC'])\n",
    "gcp_architectures = gcp_instances['Microarchitecture'].unique()\n",
    "gcp_coefficients = []\n",
    "\n",
    "for cpu in gcp_architectures:\n",
    "    if cpu == 'EPYC 1st Gen':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen1['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen1['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': amd_epyc_gen1['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'EPYC 2nd Gen':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen2['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen2['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': amd_epyc_gen2['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'EPYC 3rd Gen':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': amd_epyc_gen3['Idle watts'],\n",
    "            'Max Watts': amd_epyc_gen3['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': amd_epyc_gen3['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Sandy Bridge':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_sandybridge['Idle watts'],\n",
    "            'Max Watts': intel_sandybridge['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_sandybridge['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Ivy Bridge':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_ivybridge['Idle watts'],\n",
    "            'Max Watts': intel_ivybridge['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_ivybridge['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Haswell':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_haswell['Idle watts'],\n",
    "            'Max Watts': intel_haswell['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_haswell['GB/Chip']\n",
    "        })\n",
    "    elif cpu == 'Broadwell':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_broadwell['Idle watts'],\n",
    "            'Max Watts': intel_broadwell['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_broadwell['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Skylake':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_skylake['Idle watts'],\n",
    "            'Max Watts': intel_skylake['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_skylake['GB/Chip']\n",
    "        })\n",
    "\n",
    "    elif cpu == 'Cascade Lake':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_cascadelake['Idle watts'],\n",
    "            'Max Watts': intel_cascadelake['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_cascadelake['GB/Chip']\n",
    "        })\n",
    "    elif cpu == 'Coffee Lake':\n",
    "        gcp_coefficients.append({\n",
    "            'Architecture': cpu,\n",
    "            'Min Watts': intel_coffeelake['Idle watts'],\n",
    "            'Max Watts': intel_coffeelake['100% watts GCP adjusted'].mean(),\n",
    "            'GB/Chip': intel_coffeelake['GB/Chip']\n",
    "        })\n",
    "        \n",
    "    else:\n",
    "        print(f'No match for CPU: {cpu}')\n",
    "\n",
    "gcp_coefficients = pd.DataFrame(gcp_coefficients)\n",
    "gcp_coefficients = gcp_coefficients.drop_duplicates(ignore_index=True)\n",
    "\n",
    "print(f'Median: Min Watts = {gcp_coefficients[\"Min Watts\"].median():,.2f}')\n",
    "assert float('{:,.2f}'.format(gcp_coefficients[\"Min Watts\"].median())) == 0.68\n",
    "\n",
    "print(f'Median: Max Watts = {gcp_coefficients[\"Max Watts\"].median():,.2f}')\n",
    "assert float('{:,.2f}'.format(gcp_coefficients[\"Max Watts\"].median())) == 3.77\n",
    "\n",
    "gcp_coefficients.to_csv('output/coefficients-gcp-use.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Embodied emissions\n",
    "\n",
    "The first iteration outputs embodied emissions values for each instance type and\n",
    "CPU architecture. The second iteration outputs the mean value of the total\n",
    "embodied emissions for all CPU architectures associated with the instance type.\n",
    "\n",
    "This is because we don't know the precise architectures each instance type runs\n",
    "on, so we take an average across all possible architectures.\n",
    "\n",
    "The list of instances with embodied emissions is output to\n",
    "`output/coefficients-gcp-embodied.csv` and grouped by type at\n",
    "`coefficients-gcp-embodied-mean.csv` ready for import into\n",
    "[CCF](https://github.com/cloud-carbon-footprint/cloud-carbon-footprint)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First iteration for all instance types and all CPU types\n",
    "gcp_instances_embodied = []\n",
    "\n",
    "for key, instance in gcp_instances.iterrows():\n",
    "    # Call our calculation methods for each of the additional components\n",
    "    additional_memory = calculate_additional_memory_emissions(\n",
    "        instance['Platform Memory'])\n",
    "\n",
    "    additional_storage = calculate_additional_storage_emissions(\n",
    "        instance['Platform Storage Type'],\n",
    "        instance['Platform (largest instance) Storage Drive quantity'])\n",
    "\n",
    "    additional_cpus = calculate_additional_cpu_emissions(\n",
    "        'gcp',\n",
    "        instance['Microarchitecture'])\n",
    "\n",
    "    additional_gpus = calculate_additional_gpu_emissions(\n",
    "        instance['Platform GPU'])\n",
    "    \n",
    "    # Build a dictionary of the instance emissions\n",
    "    gcp_instances_embodied.append({\n",
    "        'family': instance['Machine Family'],\n",
    "        'type': instance['Machine type'],\n",
    "        'microarchitecture': instance['Microarchitecture'],\n",
    "        'additional_memory': round(additional_memory, 2),\n",
    "        'additional_storage': round(additional_storage, 2),\n",
    "        'additional_cpus': round(additional_cpus, 2),\n",
    "        'additional_gpus': round(additional_gpus, 2),\n",
    "        'total': round(BASE_MANUFACTURING_EMISSIONS + additional_memory + additional_storage + additional_cpus + additional_gpus, 2)\n",
    "    })\n",
    "\n",
    "gcp_instances_embodied = pd.DataFrame(gcp_instances_embodied)\n",
    "\n",
    "# Pick some random instances to test the results are as expected\n",
    "result = gcp_instances_embodied.query('type == \"e2-standard-2\" and microarchitecture == \"Skylake\"')\n",
    "assert np.isclose(result['additional_memory'], 155.46)\n",
    "assert np.isclose(result['additional_storage'], 0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 0)\n",
    "assert np.isclose(result['total'], 1255.46)\n",
    "\n",
    "result = gcp_instances_embodied.query('type == \"n2-standard-4\" and microarchitecture == \"Cascade Lake\"')\n",
    "assert np.isclose(result['additional_memory'], 688.46)\n",
    "assert np.isclose(result['additional_storage'], 100.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 0)\n",
    "assert np.isclose(result['total'], 1888.46)\n",
    "\n",
    "result = gcp_instances_embodied.query('type == \"a2-highgpu-8g\" and microarchitecture == \"Cascade Lake\"')\n",
    "assert np.isclose(result['additional_memory'], 1865.5)\n",
    "assert np.isclose(result['additional_storage'], 100.0)\n",
    "assert np.isclose(result['additional_cpus'], 100.0)\n",
    "assert np.isclose(result['additional_gpus'], 2400)\n",
    "assert np.isclose(result['total'], 5465.5)\n",
    "\n",
    "gcp_instances_embodied.to_csv('output/coefficients-gcp-embodied.csv')\n",
    "\n",
    "# Second iteration to aggregate by instance type and output the mean\n",
    "instance_types = gcp_instances_embodied.drop_duplicates(subset=\"type\")\n",
    "\n",
    "gcp_instances_type_embodied = []\n",
    "\n",
    "for key, instance in instance_types.iterrows():\n",
    "    instanceType = str(instance['type'])\n",
    "    result = gcp_instances_embodied.query(f'`type` == \"{instanceType}\"')\n",
    "\n",
    "    gcp_instances_type_embodied.append({\n",
    "        'type': instance['type'],\n",
    "        'total_mean': result['total'].mean()})\n",
    "\n",
    "gcp_instances_type_embodied = pd.DataFrame(gcp_instances_type_embodied)\n",
    "\n",
    "# Pick some random instances to test the results are as expected\n",
    "result = gcp_instances_type_embodied.query('type == \"e2-standard-2\"')\n",
    "assert np.isclose(result['total_mean'], 1230.46)\n",
    "\n",
    "result = gcp_instances_type_embodied.query('type == \"n2-standard-4\"')\n",
    "assert np.isclose(result['total_mean'], 1888.46)\n",
    "\n",
    "result = gcp_instances_type_embodied.query('type == \"a2-highgpu-8g\"')\n",
    "assert np.isclose(result['total_mean'], 5465.5)\n",
    "\n",
    "gcp_instances_type_embodied.to_csv('output/coefficients-gcp-embodied-mean.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}