{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7882d7b7-bff3-4231-99a4-3c2e5a604ee4",
   "metadata": {},
   "source": [
    "# Emissions Intensity Data Per DUID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9fc09c8-7289-4da7-92dd-d3f9402e69e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "from io import BytesIO\n",
    "from zipfile import ZipFile\n",
    "\n",
    "import polars as pl\n",
    "import polars.selectors as cs\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2634583-9ad1-487e-87e5-bc7ae2972b87",
   "metadata": {},
   "source": [
    "# Static Data\n",
    "\n",
    "First we get the emissions intensity data, `GENSETID` and `DUID` from the `GENUNITS` table.\n",
    "\n",
    "Note that this is one row per `GENSETID`, not one row per `DUID`.\n",
    "Most power/energy data (e.g. `DISPATCHUNITSCADA`, `P5UNITSOLUTION`) is at the `DUID` level. \n",
    "So if we want to multiply emissions per unit of energy by energy, we need to somehow aggregate emissions intensity to the `DUID` level. (I am not aware of `GENSETID` level power measurements in the public data.)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a61a7ac4-07c6-4b40-b550-d14aaf7580bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_csv_from_url(url) -> pl.DataFrame:\n",
    "    # Since these files are small, I am just downloading from nemweb each time,\n",
    "    # instead of caching files locally.\n",
    "    # Once you start downloading bigger data (e.g. power data),\n",
    "    # you should download the files first (if not already downloaded from the last run),\n",
    "    # and then read from disk.\n",
    "    # The purpose of this example is to show the joins later on.\n",
    "    # This part is not the focus.\n",
    "\n",
    "    r = requests.get(url)\n",
    "    r.raise_for_status()\n",
    "\n",
    "    assert url.upper().endswith(\".ZIP\"), \"Expected URL to zip\"\n",
    "    with ZipFile(BytesIO(r.content)) as zf:\n",
    "        assert len(zf.namelist()) == 1, (\n",
    "            f\"Expected 1 file inside the zip, got {len(zf.namelist())}\"\n",
    "        )\n",
    "        csv_name = zf.namelist()[0]\n",
    "        assert csv_name.upper().endswith(\".CSV\"), \"File inside zip is not a CSV\"\n",
    "        with zf.open(csv_name) as csv_f:\n",
    "            df = (\n",
    "                pl.read_csv(\n",
    "                    csv_f,\n",
    "                    skip_rows=1,  # skip the header\n",
    "                    comment_prefix='C,\"END OF REPORT\",',  # skip the footer\n",
    "                ).select(~cs.by_index(range(4)))  # drop metadata columns\n",
    "            )\n",
    "\n",
    "    return df\n",
    "\n",
    "\n",
    "def read_table(table):\n",
    "    # take current date, minus 2 months. That should contain what we need.\n",
    "    today = dt.date.today()\n",
    "    ago = today - dt.timedelta(days=30 + 31)\n",
    "    url = (\n",
    "        f\"https://www.nemweb.com.au/Data_Archive/Wholesale_Electricity/\"\n",
    "        f\"MMSDM/2025/MMSDM_{ago.year}_{ago.month:02d}/MMSDM_Historical_Data_SQLLoader/DATA/\"\n",
    "        f\"PUBLIC_ARCHIVE%23{table}%23FILE01%23{ago.year}{ago.month:02d}010000.zip\"\n",
    "    )\n",
    "    return read_csv_from_url(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "578f8912-7362-467a-96e4-270a388715db",
   "metadata": {},
   "outputs": [],
   "source": [
    "emissions_per_genset = read_table(\"GENUNITS\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aea0a630-443a-4a52-a859-8c983a185232",
   "metadata": {},
   "outputs": [],
   "source": [
    "genset_to_duid = (\n",
    "    read_table(\"DUALLOC\")\n",
    "    # deduplicate to get the latest record per GENSETID\n",
    "    .sort(\"EFFECTIVEDATE\", \"LASTCHANGED\", descending=True)\n",
    "    .select(\"DUID\", \"GENSETID\")\n",
    "    .unique([\"DUID\", \"GENSETID\"])\n",
    ")\n",
    "genset_to_duid"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "415a4924-82df-4175-8c07-8a1822401aa8",
   "metadata": {},
   "source": [
    "We could blindly take the mean across all `GENSETID`s for each `DUID`.\n",
    "Let's try to do one step better, and take a weighted mean based on capacity.\n",
    "\n",
    "Which Capacity? \"Max\" or \"Registered\"? It is not clear.\n",
    "See https://wattclarity.com.au/articles/2022/09/analyticalchallenge-installedcapacity/\n",
    "I'll choose arbitrarily."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b22172a-cb1c-42fa-aecd-6825a83da57b",
   "metadata": {},
   "outputs": [],
   "source": [
    "capacity_col = \"REGISTEREDCAPACITY\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78541da7-2b03-4052-92a8-29244bf58f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "emissions_per_duid = (\n",
    "    emissions_per_genset.join(genset_to_duid, how=\"left\", on=\"GENSETID\")\n",
    "    .group_by(\"DUID\")\n",
    "    .agg(\n",
    "        # weighted mean\n",
    "        (\n",
    "            (pl.col(\"CO2E_EMISSIONS_FACTOR\") * pl.col(capacity_col)).sum()\n",
    "            / pl.col(capacity_col).sum()\n",
    "        ).alias(\"CO2E_EMISSIONS_FACTOR\")\n",
    "    )\n",
    "    .sort(\"CO2E_EMISSIONS_FACTOR\")\n",
    ")\n",
    "\n",
    "emissions_per_duid"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c93f304d-1dde-497d-a509-152bfaef73dd",
   "metadata": {},
   "source": [
    "Note that the units of `CO2E_EMISSIONS_FACTOR` are not documented. My guess is that they are tonnes of CO2e per MWh."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5707ae67-4067-4d56-b4e7-47e5a3a5e66d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}