L5_GC_Footprint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1cd297d4",
   "metadata": {},
   "source": [
    "# Lesson 5: Understanding your Google Cloud Footprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a6f14f7-f9af-4e17-8dc1-1bea92847d53",
   "metadata": {
    "height": 47
   },
   "outputs": [],
   "source": [
    "from helper import authenticate\n",
    "CREDENTIALS, PROJECT_ID = authenticate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7b2f2ca4-7058-4b08-9c95-04c8b307de18",
   "metadata": {
    "height": 30
   },
   "outputs": [],
   "source": [
    "from google.cloud import bigquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e23a8578-4940-4f64-a2ce-f2b844b28800",
   "metadata": {
    "height": 30
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bce5027",
   "metadata": {},
   "source": [
    "* A function to export the dataset as pandas data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "447040c7-537c-4ee8-9050-df482caf5ada",
   "metadata": {
    "height": 285
   },
   "outputs": [],
   "source": [
    "def run_bq_query(sql):\n",
    "\n",
    "    bq_client = bigquery.Client(\n",
    "        project = PROJECT_ID,\n",
    "        credentials = CREDENTIALS)\n",
    "\n",
    "    job_config = bigquery.QueryJobConfig()\n",
    "    client_result = bq_client.query(\n",
    "        sql,\n",
    "        job_config=job_config)\n",
    "\n",
    "    job_id = client_result.job_id\n",
    "    \n",
    "    df = client_result.result().to_arrow().to_pandas()\n",
    "    print(f\"Finished job_id: {job_id}\")\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db209949",
   "metadata": {},
   "source": [
    "* Define the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bf6b4053-67d3-4ff2-a3c5-0de0898a94d3",
   "metadata": {
    "height": 81
   },
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT * from `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b576f6a4-e6cb-42a4-b5b2-fd1c4a70faaa",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: 13ed8921-9f83-4d89-857e-4bb462b2e06c\n"
     ]
    }
   ],
   "source": [
    "sample_df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "54e97033-be31-4f41-bff0-6e48d2365f91",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>location</th>\n",
       "      <th>billing_account_id</th>\n",
       "      <th>carbon_footprint_kgCO2e</th>\n",
       "      <th>project</th>\n",
       "      <th>usage_month</th>\n",
       "      <th>carbon_footprint_total_kgCO2e</th>\n",
       "      <th>service</th>\n",
       "      <th>carbon_model_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'location': 'europe-southwest1', 'region': 'e...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 1.9656928365246684e-07, 'scope2': {...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>{'location_based': 0.00016346808320641425}</td>\n",
       "      <td>{'description': 'Cloud Run', 'id': '152E-C115-...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 9.133650100104014e-11, 'scope2': {'...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>{'location_based': 1.0665550669434009e-07}</td>\n",
       "      <td>{'description': 'Cloud Pub/Sub', 'id': 'A1E8-B...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 7.276418114294598e-05, 'scope2': {'...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>{'location_based': 0.08484602626557447}</td>\n",
       "      <td>{'description': 'Cloud Dataflow', 'id': '57D6-...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.06795997971538453, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>{'location_based': 73.26497053897295}</td>\n",
       "      <td>{'description': 'Cloud Vision API', 'id': 'C08...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'location': 'us', 'region': None}</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 2.8320308582156193e-08, 'scope2': {...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>{'location_based': 2.607932476900128e-05}</td>\n",
       "      <td>{'description': 'BigQuery', 'id': '24E6-581D-3...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            location  billing_account_id  \\\n",
       "0  {'location': 'europe-southwest1', 'region': 'e...               12345   \n",
       "1  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "2  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "3  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "4                 {'location': 'us', 'region': None}               12345   \n",
       "\n",
       "                             carbon_footprint_kgCO2e  \\\n",
       "0  {'scope1': 1.9656928365246684e-07, 'scope2': {...   \n",
       "1  {'scope1': 9.133650100104014e-11, 'scope2': {'...   \n",
       "2  {'scope1': 7.276418114294598e-05, 'scope2': {'...   \n",
       "3  {'scope1': 0.06795997971538453, 'scope2': {'lo...   \n",
       "4  {'scope1': 2.8320308582156193e-08, 'scope2': {...   \n",
       "\n",
       "                                    project usage_month  \\\n",
       "0  {'id': 'genai-sandbox', 'number': 11111}  2022-12-01   \n",
       "1  {'id': 'genai-sandbox', 'number': 11111}  2022-12-01   \n",
       "2  {'id': 'genai-sandbox', 'number': 11111}  2021-08-01   \n",
       "3  {'id': 'genai-sandbox', 'number': 11111}  2021-08-01   \n",
       "4  {'id': 'genai-sandbox', 'number': 11111}  2022-11-01   \n",
       "\n",
       "                carbon_footprint_total_kgCO2e  \\\n",
       "0  {'location_based': 0.00016346808320641425}   \n",
       "1  {'location_based': 1.0665550669434009e-07}   \n",
       "2     {'location_based': 0.08484602626557447}   \n",
       "3       {'location_based': 73.26497053897295}   \n",
       "4   {'location_based': 2.607932476900128e-05}   \n",
       "\n",
       "                                             service  carbon_model_version  \n",
       "0  {'description': 'Cloud Run', 'id': '152E-C115-...                     7  \n",
       "1  {'description': 'Cloud Pub/Sub', 'id': 'A1E8-B...                     7  \n",
       "2  {'description': 'Cloud Dataflow', 'id': '57D6-...                     6  \n",
       "3  {'description': 'Cloud Vision API', 'id': 'C08...                     6  \n",
       "4  {'description': 'BigQuery', 'id': '24E6-581D-3...                     6  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print the dataframe\n",
    "sample_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "262cfb30-98fb-4037-a26d-71f18f8592a0",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'scope1': 1.9656928365246684e-07, 'scope2': {'location_based': 4.966157377571001e-05}, 'scope3': 0.00011360994014705176}\n"
     ]
    }
   ],
   "source": [
    "print(sample_df['carbon_footprint_kgCO2e'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bc3ddeb9-c389-4110-9185-7ae36c41c977",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'description': 'Cloud Run', 'id': '152E-C115-5142'}\n"
     ]
    }
   ],
   "source": [
    "print(sample_df['service'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0b381ab0-edde-4426-8556-8edca20edb37",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'location_based': 0.00016346808320641425}\n"
     ]
    }
   ],
   "source": [
    "print(sample_df['carbon_footprint_total_kgCO2e'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b7983887-d6be-46bc-a973-f7511f10e019",
   "metadata": {
    "height": 81
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.00016346808320641425"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate total carbon footprint\n",
    "sample_df['carbon_footprint_kgCO2e'][0]['scope1'] + \\\n",
    "sample_df['carbon_footprint_kgCO2e'][0]['scope2']['location_based'] +\\\n",
    "sample_df['carbon_footprint_kgCO2e'][0]['scope3']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82b7d67d",
   "metadata": {},
   "source": [
    "* More examples of query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d51d3e4e-8df0-4e6d-9273-81a04f4797b2",
   "metadata": {
    "height": 115
   },
   "outputs": [],
   "source": [
    "# Select from a specific service, in this case BigQuery\n",
    "query = f\"\"\"\n",
    "SELECT SUM(carbon_footprint_kgCO2e.scope2.location_based)\n",
    "FROM `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "WHERE service.description = \"BigQuery\"\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c14abb2a-4d20-4da3-9632-8e43b0d6f671",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: 4c6991fa-c125-4b1d-986e-2fdedfb97491\n"
     ]
    }
   ],
   "source": [
    "df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "af07f769-db76-499c-b9a5-2b021306ec3d",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>f0_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.199101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        f0_\n",
       "0  0.199101"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b8d1687b-22cc-4188-8c13-445d958a0eb6",
   "metadata": {
    "height": 200
   },
   "outputs": [],
   "source": [
    "# Select specific column values\n",
    "query = f\"\"\"\n",
    "SELECT\n",
    "    usage_month,\n",
    "    service.description,\n",
    "    location.location,\n",
    "    carbon_footprint_total_kgCO2e.location_based\n",
    "FROM `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "WHERE project.number = 11111\n",
    "ORDER BY usage_month, service.description\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7e855b7a-49bf-479b-be8e-89714572cb25",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: 5697f71f-6794-4d2d-b9f5-db7d2ee7cd70\n"
     ]
    }
   ],
   "source": [
    "df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2fc46855-c992-408b-abe1-a1c57ec407f7",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>usage_month</th>\n",
       "      <th>description</th>\n",
       "      <th>location</th>\n",
       "      <th>location_based</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>Cloud Build</td>\n",
       "      <td>us-east1-d</td>\n",
       "      <td>0.001797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>Cloud Build</td>\n",
       "      <td>us-east4-c</td>\n",
       "      <td>0.001940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>Cloud Machine Learning Engine</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>49.691373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>Cloud Storage</td>\n",
       "      <td>us</td>\n",
       "      <td>0.011047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>Cloud Storage</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>0.008751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>Cloud Storage</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>0.933052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>Compute Engine</td>\n",
       "      <td>us-west1</td>\n",
       "      <td>1.026506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>Compute Engine</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>12.219985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>Notebooks</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>6.250810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>2024-01-01</td>\n",
       "      <td>Vertex AI</td>\n",
       "      <td>us-central1</td>\n",
       "      <td>221.627364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>352 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    usage_month                    description     location  location_based\n",
       "0    2021-06-01                    Cloud Build   us-east1-d        0.001797\n",
       "1    2021-06-01                    Cloud Build   us-east4-c        0.001940\n",
       "2    2021-06-01  Cloud Machine Learning Engine  us-central1       49.691373\n",
       "3    2021-06-01                  Cloud Storage           us        0.011047\n",
       "4    2021-06-01                  Cloud Storage  us-central1        0.008751\n",
       "..          ...                            ...          ...             ...\n",
       "347  2024-01-01                  Cloud Storage  us-central1        0.933052\n",
       "348  2024-01-01                 Compute Engine     us-west1        1.026506\n",
       "349  2024-01-01                 Compute Engine  us-central1       12.219985\n",
       "350  2024-01-01                      Notebooks  us-central1        6.250810\n",
       "351  2024-01-01                      Vertex AI  us-central1      221.627364\n",
       "\n",
       "[352 rows x 4 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "353c1911-744f-4875-940b-4a60e04c5410",
   "metadata": {
    "height": 115
   },
   "outputs": [],
   "source": [
    "# Total amount of emisions from all projects \n",
    "query = f\"\"\"\n",
    "SELECT DISTINCT SUM(carbon_footprint_total_kgCO2e.location_based) as carbon_emissions, project.number\n",
    "FROM `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "GROUP BY project.number\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "216d2b31-32cf-48bf-a429-b83389cf681a",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: efac274e-f7d4-4cf2-b060-93bb3c72726f\n"
     ]
    }
   ],
   "source": [
    "df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "312e3f79-ebd9-4efd-8d7f-1ec9aea75320",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>carbon_emissions</th>\n",
       "      <th>number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15854.736771</td>\n",
       "      <td>11111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12035.135505</td>\n",
       "      <td>33333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>520.707209</td>\n",
       "      <td>22222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carbon_emissions  number\n",
       "0      15854.736771   11111\n",
       "1      12035.135505   33333\n",
       "2        520.707209   22222"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9aa90bfd-e344-4c9a-8d5c-61973b7bd6af",
   "metadata": {
    "height": 81
   },
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT DISTINCT SUM(carbon_footprint_total_kgCO2e.location_based)\n",
    "FROM `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "99352ee3-4cbd-4630-96ad-223915712b9c",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: 926560eb-1172-4c7d-bcc9-c4fd52d74ab7\n"
     ]
    }
   ],
   "source": [
    "df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e342923a-4b4e-4932-9565-a1ca7532f459",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28410.579485000002"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add the results before to compare the result from the recent query\n",
    "15854.736771 + 12035.135505 + 520.707209"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "aa069ad3-ade4-48ad-85ea-570c10fa5c56",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>f0_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28410.579484</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            f0_\n",
       "0  28410.579484"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "39211ab2-d639-467c-894a-d4fe71b44881",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28.81338742393509"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "28410/986"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55dc3c85",
   "metadata": {},
   "source": [
    "* Load data into pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f328bd71-d373-429d-90d3-a8feeed8bcc5",
   "metadata": {
    "height": 81
   },
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT *\n",
    "FROM `sc-gcp-c5-carbon-emissions.carbonfootprint.sample_data`\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d50f2274-940e-4442-8f29-bade40f05878",
   "metadata": {
    "height": 30
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished job_id: e781a024-a70d-4692-adc3-c4ed5d87b7ec\n"
     ]
    }
   ],
   "source": [
    "df = run_bq_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "31082a1f-757c-4cf1-aa4b-3237f9f44fb4",
   "metadata": {
    "height": 47
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>location</th>\n",
       "      <th>billing_account_id</th>\n",
       "      <th>carbon_footprint_kgCO2e</th>\n",
       "      <th>project</th>\n",
       "      <th>usage_month</th>\n",
       "      <th>carbon_footprint_total_kgCO2e</th>\n",
       "      <th>service</th>\n",
       "      <th>carbon_model_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'location': 'europe-southwest1', 'region': 'e...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 1.9656928365246684e-07, 'scope2': {...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>{'location_based': 0.00016346808320641425}</td>\n",
       "      <td>{'description': 'Cloud Run', 'id': '152E-C115-...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 9.133650100104014e-11, 'scope2': {'...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>{'location_based': 1.0665550669434009e-07}</td>\n",
       "      <td>{'description': 'Cloud Pub/Sub', 'id': 'A1E8-B...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 7.276418114294598e-05, 'scope2': {'...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>{'location_based': 0.08484602626557447}</td>\n",
       "      <td>{'description': 'Cloud Dataflow', 'id': '57D6-...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.06795997971538453, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>{'location_based': 73.26497053897295}</td>\n",
       "      <td>{'description': 'Cloud Vision API', 'id': 'C08...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'location': 'us', 'region': None}</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 2.8320308582156193e-08, 'scope2': {...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>{'location_based': 2.607932476900128e-05}</td>\n",
       "      <td>{'description': 'BigQuery', 'id': '24E6-581D-3...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.0005837179844260626, 'scope2': {'...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>{'location_based': 0.6031637397335557}</td>\n",
       "      <td>{'description': 'Stackdriver Monitoring', 'id'...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>572</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.13040957993318006, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>{'location_based': 135.32412273047785}</td>\n",
       "      <td>{'description': 'Cloud Machine Learning Engine...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>573</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.04651798649950839, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-07-01</td>\n",
       "      <td>{'location_based': 50.880526007500706}</td>\n",
       "      <td>{'description': 'Cloud Machine Learning Engine...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>574</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.06828994782612086, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>{'location_based': 73.57722669866956}</td>\n",
       "      <td>{'description': 'Cloud Machine Learning Engine...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>{'location': 'us-central1', 'region': 'us-cent...</td>\n",
       "      <td>12345</td>\n",
       "      <td>{'scope1': 0.04637991919683636, 'scope2': {'lo...</td>\n",
       "      <td>{'id': 'genai-sandbox', 'number': 11111}</td>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>{'location_based': 49.69137320650013}</td>\n",
       "      <td>{'description': 'Cloud Machine Learning Engine...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>576 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              location  billing_account_id  \\\n",
       "0    {'location': 'europe-southwest1', 'region': 'e...               12345   \n",
       "1    {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "2    {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "3    {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "4                   {'location': 'us', 'region': None}               12345   \n",
       "..                                                 ...                 ...   \n",
       "571  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "572  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "573  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "574  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "575  {'location': 'us-central1', 'region': 'us-cent...               12345   \n",
       "\n",
       "                               carbon_footprint_kgCO2e  \\\n",
       "0    {'scope1': 1.9656928365246684e-07, 'scope2': {...   \n",
       "1    {'scope1': 9.133650100104014e-11, 'scope2': {'...   \n",
       "2    {'scope1': 7.276418114294598e-05, 'scope2': {'...   \n",
       "3    {'scope1': 0.06795997971538453, 'scope2': {'lo...   \n",
       "4    {'scope1': 2.8320308582156193e-08, 'scope2': {...   \n",
       "..                                                 ...   \n",
       "571  {'scope1': 0.0005837179844260626, 'scope2': {'...   \n",
       "572  {'scope1': 0.13040957993318006, 'scope2': {'lo...   \n",
       "573  {'scope1': 0.04651798649950839, 'scope2': {'lo...   \n",
       "574  {'scope1': 0.06828994782612086, 'scope2': {'lo...   \n",
       "575  {'scope1': 0.04637991919683636, 'scope2': {'lo...   \n",
       "\n",
       "                                      project usage_month  \\\n",
       "0    {'id': 'genai-sandbox', 'number': 11111}  2022-12-01   \n",
       "1    {'id': 'genai-sandbox', 'number': 11111}  2022-12-01   \n",
       "2    {'id': 'genai-sandbox', 'number': 11111}  2021-08-01   \n",
       "3    {'id': 'genai-sandbox', 'number': 11111}  2021-08-01   \n",
       "4    {'id': 'genai-sandbox', 'number': 11111}  2022-11-01   \n",
       "..                                        ...         ...   \n",
       "571  {'id': 'genai-sandbox', 'number': 11111}  2023-02-01   \n",
       "572  {'id': 'genai-sandbox', 'number': 11111}  2021-09-01   \n",
       "573  {'id': 'genai-sandbox', 'number': 11111}  2021-07-01   \n",
       "574  {'id': 'genai-sandbox', 'number': 11111}  2021-08-01   \n",
       "575  {'id': 'genai-sandbox', 'number': 11111}  2021-06-01   \n",
       "\n",
       "                  carbon_footprint_total_kgCO2e  \\\n",
       "0    {'location_based': 0.00016346808320641425}   \n",
       "1    {'location_based': 1.0665550669434009e-07}   \n",
       "2       {'location_based': 0.08484602626557447}   \n",
       "3         {'location_based': 73.26497053897295}   \n",
       "4     {'location_based': 2.607932476900128e-05}   \n",
       "..                                          ...   \n",
       "571      {'location_based': 0.6031637397335557}   \n",
       "572      {'location_based': 135.32412273047785}   \n",
       "573      {'location_based': 50.880526007500706}   \n",
       "574       {'location_based': 73.57722669866956}   \n",
       "575       {'location_based': 49.69137320650013}   \n",
       "\n",
       "                                               service  carbon_model_version  \n",
       "0    {'description': 'Cloud Run', 'id': '152E-C115-...                     7  \n",
       "1    {'description': 'Cloud Pub/Sub', 'id': 'A1E8-B...                     7  \n",
       "2    {'description': 'Cloud Dataflow', 'id': '57D6-...                     6  \n",
       "3    {'description': 'Cloud Vision API', 'id': 'C08...                     6  \n",
       "4    {'description': 'BigQuery', 'id': '24E6-581D-3...                     6  \n",
       "..                                                 ...                   ...  \n",
       "571  {'description': 'Stackdriver Monitoring', 'id'...                     8  \n",
       "572  {'description': 'Cloud Machine Learning Engine...                     6  \n",
       "573  {'description': 'Cloud Machine Learning Engine...                     6  \n",
       "574  {'description': 'Cloud Machine Learning Engine...                     6  \n",
       "575  {'description': 'Cloud Machine Learning Engine...                     6  \n",
       "\n",
       "[576 rows x 8 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}