capacityParser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: tabulate in /Users/simon/.pyenv/versions/3.9.4/lib/python3.9/site-packages (0.9.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import json\n",
    "from datetime import datetime\n",
    "import pytz\n",
    "import pandas as pd\n",
    "\n",
    "%pip install tabulate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "INPUT_FOLDER = \"./input\"\n",
    "OUTPUT_FOLDER = \"./output\"\n",
    "\n",
    "CAPACITY_TREE_FOLDER = 'capacity'\n",
    "\n",
    "FACILITIES_FILE = \"au_facilities.json\"\n",
    "\n",
    "MISSING_DATES_FILE = \"missing_dates.csv\"\n",
    "\n",
    "TIME_ZONE = pytz.timezone(\"Australia/Sydney\")\n",
    "\n",
    "FACILITIES_URL = 'https://data.dev.opennem.org.au/v3/geo/au_facilities.json'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## process the facilities geoJSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### load facilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARN: input file ./input/au_facilities.json not found. loading from https://data.dev.opennem.org.au/v3/geo/au_facilities.json\n",
      "INFO: file downloaded successfully and saved as ./input/au_facilities.json\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "# check to see if  INPUT_FILE is in the INPUT_FOLDER and if not, load it from FACILITIES_URL\n",
    "def load_facilities_file():\n",
    "    input_file = os.path.join(INPUT_FOLDER, FACILITIES_FILE)\n",
    "    if os.path.exists(input_file):\n",
    "        print(f\"INFO: input file {input_file} found\")\n",
    "    else:\n",
    "        # Ensure the directory exists\n",
    "        directory = os.path.dirname(input_file)\n",
    "        if not os.path.exists(directory):\n",
    "            os.makedirs(directory)\n",
    "\n",
    "        print(f\"WARN: input file {input_file} not found. loading from {FACILITIES_URL}\")\n",
    "        response = requests.get(FACILITIES_URL)\n",
    "\n",
    "        # Check if the request was successful\n",
    "        if response.status_code == 200:\n",
    "            with open(input_file, 'wb') as file:\n",
    "                file.write(response.content)\n",
    "            print(f\"INFO: file downloaded successfully and saved as {input_file}\")\n",
    "        else:\n",
    "            print(f\"ERROR: failed to download file. status code: {response.status_code}\")\n",
    "\n",
    "load_facilities_file()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARN: 200 units have missing dates. writing to missing_dates.csv\n",
      "There are 481 facilities.\n",
      "There are 728 units.\n",
      "There are 4 unique status codes:\n",
      " * committed\n",
      " * operating\n",
      " * commissioning\n",
      " * retired\n"
     ]
    }
   ],
   "source": [
    "# read and parse the input file\n",
    "from pandas import NaT\n",
    "\n",
    "def read_input(input_file):\n",
    "    with open(input_file) as f:\n",
    "        data = json.load(f)\n",
    "    return data\n",
    "\n",
    "# given a date string in format \"2024-06-10T06:50:00Z\" parse it and format like \"30 May 2024\"\n",
    "# if the input string is empty, return \"**unknown**\"\n",
    "def format_date(date_string):\n",
    "    if date_string is None:\n",
    "        return \"**unknown**\"\n",
    "    else:\n",
    "        return datetime.strptime(date_string, \"%Y-%m-%dT%H:%M:%SZ\").strftime(\"%d %b %Y\")\n",
    "\n",
    "# iterate through all facilities and units within the facility. \n",
    "# for each unit add a pointer to the facility to the unit\n",
    "def add_parent_pointer(data):\n",
    "    for facility in data['features']:\n",
    "        for unit in facility['properties']['duid_data']:\n",
    "            unit['parent_station_code'] = facility['properties']['station_code']\n",
    "\n",
    "def build_unit_string(unit):\n",
    "    # build an operating date string: if operating it should be \"since YYYY-MM-DD\"ArithmeticError\n",
    "    # if retired it should be \"retired (from YYYY-MM-DD to YYYY-MM-DD)\"\n",
    "    # format dates as DD MMM YYYY\n",
    "    if unit['status'] == 'operating':\n",
    "        operating_date = f\"since {format_date(unit['data_first_seen'])}\"\n",
    "    elif unit['status'] == 'retired':\n",
    "        operating_date = f\"from {format_date(unit['data_first_seen'])} to {format_date(unit['data_last_seen'])}\"\n",
    "    else:\n",
    "        operating_date = \"unknown\"\n",
    "    \n",
    "    # put all on a single line for readability\n",
    "    return f\"{unit['parent_station_code']}:{unit['duid']} {unit['capacity_registered']} MW {unit['fuel_tech']} {unit['status']} {operating_date}\"\n",
    "\n",
    "\n",
    "# in the object tree (from au_facilities.json), iterate through each facility and print a summary of the\n",
    "# DUIDs in each (its capacity in MW, its fuel technology, and its status)\n",
    "def print_summary(data):\n",
    "    for facility in data['features']:\n",
    "        print(facility['properties']['name'])\n",
    "        for unit in facility['properties']['duid_data']:\n",
    "           print(\"  \", build_unit_string(unit)) \n",
    "        print()\n",
    "\n",
    "# determine whether a unit has missing dates\n",
    "# if the unit has a status of \"operating\" it needs a data_first_seen date\n",
    "# if the unit has a status of \"retired\" it needs a data_first_seen date and a data_last_seen date\n",
    "\n",
    "def has_missing_dates(status, data_first_seen, data_last_seen):\n",
    "    if data_first_seen == NaT:\n",
    "        data_first_seen = None\n",
    "        \n",
    "    if data_last_seen == NaT:\n",
    "        data_last_seen = None\n",
    "\n",
    "    if status == 'operating':\n",
    "        return data_first_seen is None\n",
    "    elif status == 'retired':\n",
    "        return data_first_seen is None or data_last_seen is None\n",
    "    elif status in {'commissioning', 'committed'}:\n",
    "        return False\n",
    "    else:\n",
    "        # throw an error\n",
    "        raise ValueError(f\"Unknown status: {status}\")\n",
    "\n",
    "def has_missing_dates_units(unit):\n",
    "    return has_missing_dates(unit['status'], unit['data_first_seen'], unit['data_last_seen'])   \n",
    "\n",
    "# function to build a list of units iwth missing dates\n",
    "def log_units_with_missing_dates(data):\n",
    "    missing_dates = []\n",
    "    for facility in data['features']:\n",
    "        for unit in facility['properties']['duid_data']:\n",
    "            if has_missing_dates_units(unit):\n",
    "                missing_dates.append(unit)\n",
    "\n",
    "\n",
    "    # if there are any units with missing dates\n",
    "    if len(missing_dates) > 0:\n",
    "        missing_dates_file = os.path.join(OUTPUT_FOLDER, MISSING_DATES_FILE)\n",
    "        print(f\"WARN: {len(missing_dates)} units have missing dates. writing to {MISSING_DATES_FILE}\")\n",
    "\n",
    "        # make a dataframe of the missing dates and write it to a CSV file\n",
    "        os.makedirs(OUTPUT_FOLDER, exist_ok=True)\n",
    "        missing_dates_df = pd.DataFrame(missing_dates)\n",
    "\n",
    "        # filter to just  duid, fuel_tech, status, capacity_registered, data_first_seen, data_last_seen, network_region, parent_station_code\n",
    "        missing_dates_df = missing_dates_df[['duid', 'fuel_tech', 'status', 'capacity_registered', 'data_first_seen', 'data_last_seen', 'network_region', 'parent_station_code']]\n",
    "        missing_dates_df.to_csv(missing_dates_file, index=False)\n",
    "    else:\n",
    "        print(\"INFO: congrautlations! no units have missing dates!\")\n",
    "\n",
    "# print some simple stats\n",
    "def print_stats(data):\n",
    "    print(f\"There are {len(data['features'])} facilities.\")\n",
    "    print(f\"There are {sum([len(f['properties']['duid_data']) for f in data['features']])} units.\")\n",
    "    print(f\"There are {len(set([u['status'] for f in data['features'] for u in f['properties']['duid_data']]))} unique status codes:\")\n",
    "    # print the status codes\n",
    "    for status in set([u['status'] for f in data['features'] for u in f['properties']['duid_data']]):\n",
    "        print(f\" * {status}\")\n",
    "\n",
    "\n",
    "def load_and_parse_units():\n",
    "    # build the path to the input file\n",
    "    input_file = os.path.join(INPUT_FOLDER, FACILITIES_FILE)\n",
    "    data = read_input(input_file)\n",
    "\n",
    "    add_parent_pointer(data)\n",
    "    log_units_with_missing_dates(data)\n",
    "    print_stats(data)\n",
    "\n",
    "    return data\n",
    "\n",
    "# call the function\n",
    "raw_units_data = load_and_parse_units()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "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>facility_code</th>\n",
       "      <th>duid</th>\n",
       "      <th>network_region</th>\n",
       "      <th>capacity_registered</th>\n",
       "      <th>fuel_tech</th>\n",
       "      <th>status</th>\n",
       "      <th>data_first_seen</th>\n",
       "      <th>data_last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APPIN</td>\n",
       "      <td>APPIN</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>55.0</td>\n",
       "      <td>gas_wcmg</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AVLSF</td>\n",
       "      <td>AVLSF1</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>245.0</td>\n",
       "      <td>solar_utility</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2024-06-10 06:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AWABAREF</td>\n",
       "      <td>AWABAREF</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>bioenergy_biogas</td>\n",
       "      <td>retired</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BANGOWF</td>\n",
       "      <td>BANGOWF2</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>84.8</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2021-06-04 14:25:00</td>\n",
       "      <td>2024-06-10 21:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BANGOWF</td>\n",
       "      <td>BANGOWF1</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>244.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-11-30 14:25:00</td>\n",
       "      <td>2024-06-10 21:15:00</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>696</th>\n",
       "      <td>WARRADARGE</td>\n",
       "      <td>WARRADARGE_WF1</td>\n",
       "      <td>wem</td>\n",
       "      <td>180.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-07-29 00:00:00</td>\n",
       "      <td>2024-01-13 23:55:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>BLAIRFOX_WESTHILLS</td>\n",
       "      <td>BLAIRFOX_WESTHILLS_WF3</td>\n",
       "      <td>wem</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2013-10-10 00:00:00</td>\n",
       "      <td>2024-01-12 11:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>WEST_KALGOORLIE</td>\n",
       "      <td>WEST_KALGOORLIE_GT3</td>\n",
       "      <td>wem</td>\n",
       "      <td>23.3</td>\n",
       "      <td>distillate</td>\n",
       "      <td>operating</td>\n",
       "      <td>2006-09-19 16:00:00</td>\n",
       "      <td>2024-01-13 12:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>WEST_KALGOORLIE</td>\n",
       "      <td>WEST_KALGOORLIE_GT2</td>\n",
       "      <td>wem</td>\n",
       "      <td>41.2</td>\n",
       "      <td>distillate</td>\n",
       "      <td>operating</td>\n",
       "      <td>2006-09-19 16:00:00</td>\n",
       "      <td>2024-01-13 23:55:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>700</th>\n",
       "      <td>YANDIN</td>\n",
       "      <td>YANDIN_WF1</td>\n",
       "      <td>wem</td>\n",
       "      <td>214.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-07-01 00:00:00</td>\n",
       "      <td>2024-01-13 23:20:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>701 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          facility_code                    duid network_region  \\\n",
       "0                 APPIN                   APPIN           nsw1   \n",
       "1                 AVLSF                  AVLSF1           nsw1   \n",
       "2              AWABAREF                AWABAREF           nsw1   \n",
       "3               BANGOWF                BANGOWF2           nsw1   \n",
       "4               BANGOWF                BANGOWF1           nsw1   \n",
       "..                  ...                     ...            ...   \n",
       "696          WARRADARGE          WARRADARGE_WF1            wem   \n",
       "697  BLAIRFOX_WESTHILLS  BLAIRFOX_WESTHILLS_WF3            wem   \n",
       "698     WEST_KALGOORLIE     WEST_KALGOORLIE_GT3            wem   \n",
       "699     WEST_KALGOORLIE     WEST_KALGOORLIE_GT2            wem   \n",
       "700              YANDIN              YANDIN_WF1            wem   \n",
       "\n",
       "     capacity_registered         fuel_tech     status      data_first_seen  \\\n",
       "0                   55.0          gas_wcmg  operating                  NaT   \n",
       "1                  245.0     solar_utility  operating                  NaT   \n",
       "2                    1.0  bioenergy_biogas    retired                  NaT   \n",
       "3                   84.8              wind  operating  2021-06-04 14:25:00   \n",
       "4                  244.0              wind  operating  2020-11-30 14:25:00   \n",
       "..                   ...               ...        ...                  ...   \n",
       "696                180.0              wind  operating  2020-07-29 00:00:00   \n",
       "697                  5.0              wind  operating  2013-10-10 00:00:00   \n",
       "698                 23.3        distillate  operating  2006-09-19 16:00:00   \n",
       "699                 41.2        distillate  operating  2006-09-19 16:00:00   \n",
       "700                214.0              wind  operating  2020-07-01 00:00:00   \n",
       "\n",
       "          data_last_seen  \n",
       "0                    NaT  \n",
       "1    2024-06-10 06:50:00  \n",
       "2                    NaT  \n",
       "3    2024-06-10 21:15:00  \n",
       "4    2024-06-10 21:15:00  \n",
       "..                   ...  \n",
       "696  2024-01-13 23:55:00  \n",
       "697  2024-01-12 11:00:00  \n",
       "698  2024-01-13 12:40:00  \n",
       "699  2024-01-13 23:55:00  \n",
       "700  2024-01-13 23:20:00  \n",
       "\n",
       "[701 rows x 8 columns]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find the earliest data_first_seen date in the list of units\n",
    "def find_first_seen(units):\n",
    "    first_seen = units['data_first_seen'].dropna().min()\n",
    "    return first_seen\n",
    "\n",
    "\n",
    "# build a pandas data from of units\n",
    "# with the following columns:\n",
    "# facility_code, duid, network_region, capacity_registered, fuel_tech, status, data_first_seen, data_last_seen\n",
    "def build_unit_data_frame(data):\n",
    "    rows = []\n",
    "    for facility in data['features']:\n",
    "        for unit in facility['properties']['duid_data']:\n",
    "            # parse the data_first_seen and data_last_seen dates\n",
    "            if unit['data_first_seen'] is not None:\n",
    "                first_seen = datetime.strptime(unit['data_first_seen'], \"%Y-%m-%dT%H:%M:%SZ\")\n",
    "            else:\n",
    "                first_seen = None\n",
    "                \n",
    "            if unit['data_last_seen'] is not None:\n",
    "                last_seen = datetime.strptime(unit['data_last_seen'], \"%Y-%m-%dT%H:%M:%SZ\")\n",
    "            else:\n",
    "                last_seen = None\n",
    "\n",
    "            # change battery_discharging to battery, and skip battery_charging to battery \n",
    "            # so we don't end up with two rows for the same unit\n",
    "            # NOTE: kwinana battery is only listed as battery_discharging, so this keeps it!\n",
    "            fuel_tech = unit['fuel_tech']\n",
    "            if fuel_tech == 'battery_discharging':\n",
    "                fuel_tech = 'battery'\n",
    "\n",
    "            if fuel_tech != 'battery_charging':\n",
    "                rows.append({\n",
    "                    'facility_code': facility['properties']['station_code'],\n",
    "                    'duid': unit['duid'],\n",
    "                    'network_region': unit['network_region'].lower(),\n",
    "                    'capacity_registered': unit['capacity_registered'],\n",
    "                    'fuel_tech': fuel_tech,\n",
    "                    'status': unit['status'],\n",
    "                    'data_first_seen': first_seen,\n",
    "                    'data_last_seen': last_seen\n",
    "                })\n",
    "    df = pd.DataFrame(rows)\n",
    "\n",
    "    # Ensure the 'data_first_seen' and 'data_last_seen' columns are treated as objects\n",
    "    df['data_first_seen'] = df['data_first_seen'].astype('object')\n",
    "    df['data_last_seen'] = df['data_last_seen'].astype('object')\n",
    "\n",
    "    # clean up the data\n",
    "    df['capacity_registered'] = df['capacity_registered'].astype(float)\n",
    "\n",
    "    return df\n",
    "\n",
    "all_units = build_unit_data_frame(raw_units_data)\n",
    "all_units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## check whether a unit was operating on a particular date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, timedelta\n",
    "import unittest\n",
    "\n",
    "\n",
    "def was_operating(status, first_seen, last_seen, given_date):\n",
    "    month_start = date(given_date.year, given_date.month, 1)\n",
    "    next_month = month_start + timedelta(days=31)  # Assuring to get to the next month\n",
    "    month_end = date(next_month.year, next_month.month, 1) - timedelta(days=1)\n",
    "\n",
    "    # print month_end formatted as YYYY-MM-DD HH:MM:SS\n",
    "    # print(\"month_end:\", month_end.strftime(\"%Y-%m-%d %H:%M:%S\"))\n",
    "\n",
    "    if status == \"operating\":\n",
    "        return first_seen <= month_end\n",
    "    elif status == \"retired\":\n",
    "        return first_seen <= month_end and last_seen >= month_start\n",
    "    elif status in {\"commissioning\", \"committed\"}:\n",
    "        return False\n",
    "    else:\n",
    "        # throw an error\n",
    "        raise ValueError(f\"Unknown status: {status}\")\n",
    "    \n",
    "\n",
    "def was_operating_unit(unit, given_date):\n",
    "    return was_operating(unit['status'], unit['data_first_seen'], unit['data_last_seen'], given_date)\n",
    "\n",
    "\n",
    "class TestWasOperating(unittest.TestCase):\n",
    "    def setUp(self):\n",
    "        self.unit = {\n",
    "            \"data_first_seen\": date(1999, 2, 1),\n",
    "            \"data_last_seen\": date(2002, 5, 1),\n",
    "            \"status\": \"retired\",\n",
    "            \"fuel_tech\": \"solar_rooftop\",\n",
    "            \"registered_capacity_MW\": 50.2,\n",
    "        }\n",
    "\n",
    "\n",
    "    def test_was_operating_case1(self):\n",
    "        self.assertTrue(\n",
    "            was_operating_unit(self.unit, date(2000, 1, 1)), \"Test Case 1 Failed\"\n",
    "        )\n",
    "\n",
    "\n",
    "    def test_was_operating_case2(self):\n",
    "        self.assertFalse(\n",
    "            was_operating_unit(self.unit, date(2003, 6, 15)), \"Test Case 2 Failed\"\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## test cases for was_operating_unit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "test_was_operating_case1 (__main__.TestWasOperating) ... ok\n",
      "test_was_operating_case2 (__main__.TestWasOperating) ... ok\n",
      "test_unit1_after_last_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_after_last_seen_boundary_outside (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_after_last_seen_boundary_within (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_during_operation (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_first_seen_boundary_outside (__main__.TestWasOperating2) ... ok\n",
      "test_unit1_first_seen_boundary_within (__main__.TestWasOperating2) ... ok\n",
      "test_unit2_after_last_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit2_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit2_during_operation (__main__.TestWasOperating2) ... ok\n",
      "test_unit3_after_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit3_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit3_future_date (__main__.TestWasOperating2) ... ok\n",
      "test_unit4_after_last_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit4_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit4_during_operation (__main__.TestWasOperating2) ... ok\n",
      "test_unit5_after_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit5_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit5_future_date (__main__.TestWasOperating2) ... ok\n",
      "test_unit6_after_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit6_before_first_seen (__main__.TestWasOperating2) ... ok\n",
      "test_unit6_future_date (__main__.TestWasOperating2) ... ok\n",
      "\n",
      "----------------------------------------------------------------------\n",
      "Ran 24 tests in 0.012s\n",
      "\n",
      "OK\n"
     ]
    }
   ],
   "source": [
    "TEST_UNITS = [\n",
    "    {\n",
    "        \"data_first_seen\": date(1999, 2, 1),\n",
    "        \"data_last_seen\": date(2002, 5, 31),\n",
    "        \"status\": \"retired\",\n",
    "        \"fuel_tech\": \"solar_utility\",\n",
    "        \"capacity_registered\": 50,\n",
    "        \"network_region\": \"NSW1\",\n",
    "    },\n",
    "    {\n",
    "        \"data_first_seen\": date(2000, 6, 1),\n",
    "        \"data_last_seen\": date(2005, 3, 1),\n",
    "        \"status\": \"retired\",\n",
    "        \"fuel_tech\": \"solar_utility\",\n",
    "        \"capacity_registered\": 150,\n",
    "        \"network_region\": \"NSW1\",\n",
    "    },\n",
    "    {\n",
    "        \"data_first_seen\": date(2001, 4, 1),\n",
    "        \"data_last_seen\": NaT,\n",
    "        \"status\": \"operating\",\n",
    "        \"fuel_tech\": \"coal_black\",\n",
    "        \"capacity_registered\": 314.1,\n",
    "        \"network_region\": \"NSW1\",\n",
    "    },\n",
    "    {\n",
    "        \"data_first_seen\": date(1998, 12, 1),\n",
    "        \"data_last_seen\": date(2020, 12, 1),\n",
    "        \"status\": \"retired\",\n",
    "        \"fuel_tech\": \"coal_black\",\n",
    "        \"capacity_registered\": 350,\n",
    "        \"network_region\": \"NSW1\",\n",
    "    },\n",
    "    {\n",
    "        \"data_first_seen\": date(2002, 7, 1),\n",
    "        \"data_last_seen\": NaT,\n",
    "        \"status\": \"operating\",\n",
    "        \"fuel_tech\": \"hydro\",\n",
    "        \"capacity_registered\": 250.0,\n",
    "        \"network_region\": \"SA1\",\n",
    "    },\n",
    "    {\n",
    "        \"data_first_seen\": date(2003, 3, 1),\n",
    "        \"data_last_seen\": NaT,\n",
    "        \"status\": \"committed\",\n",
    "        \"fuel_tech\": \"hydro\",\n",
    "        \"capacity_registered\": 120,\n",
    "        \"network_region\": \"SA1\",\n",
    "    },\n",
    "]\n",
    "\n",
    "\n",
    "class TestWasOperating2(unittest.TestCase):\n",
    "    def setUp(self):\n",
    "        self.units = TEST_UNITS\n",
    "\n",
    "    # Test cases for the first unit (retired)\n",
    "    # unit[0] = {\n",
    "    #     \"data_first_seen\": date(1999, 2, 1),\n",
    "    #     \"data_last_seen\": date(2002, 5, 31),\n",
    "    #     \"status\": \"retired\",\n",
    "    #     \"fuel_tech\": \"solar_rooftop\",\n",
    "    #     \"capacity_registered\": 50.2\n",
    "    # }\n",
    "    def test_unit1_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[0], date(1998, 12, 31)))\n",
    "\n",
    "    def test_unit1_during_operation(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[0], date(2000, 1, 1)))\n",
    "\n",
    "    def test_unit1_after_last_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[0], date(2003, 1, 1)))\n",
    "\n",
    "    def test_unit1_after_last_seen_boundary_within(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[0], date(2002, 5, 31)))\n",
    "\n",
    "    def test_unit1_after_last_seen_boundary_outside(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[0], date(2002, 6, 1)))\n",
    "\n",
    "    def test_unit1_first_seen_boundary_within(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[0], date(1999, 2, 1)))\n",
    "\n",
    "    def test_unit1_first_seen_boundary_outside(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[0], date(1999, 1, 31)))\n",
    "\n",
    "    # Test cases for the second unit (retired)\n",
    "    def test_unit2_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[1], date(1999, 12, 31)))\n",
    "\n",
    "    def test_unit2_during_operation(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[1], date(2002, 1, 1)))\n",
    "\n",
    "    def test_unit2_after_last_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[1], date(2006, 1, 1)))\n",
    "\n",
    "    # Test cases for the third unit (operating, no end date)\n",
    "    def test_unit3_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[2], date(2000, 12, 31)))\n",
    "\n",
    "    def test_unit3_after_first_seen(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[2], date(2002, 1, 1)))\n",
    "\n",
    "    def test_unit3_future_date(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[2], date(2025, 1, 1)))\n",
    "\n",
    "    # Test cases for the fourth unit (retired)\n",
    "    def test_unit4_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[3], date(1998, 11, 30)))\n",
    "\n",
    "    def test_unit4_during_operation(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[3], date(2010, 1, 1)))\n",
    "\n",
    "    def test_unit4_after_last_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[3], date(2021, 1, 1)))\n",
    "\n",
    "    # Test cases for the fifth unit (operating, no end date)\n",
    "    def test_unit5_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[4], date(2000, 6, 30)))\n",
    "\n",
    "    def test_unit5_after_first_seen(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[4], date(2003, 1, 1)))\n",
    "\n",
    "    def test_unit5_future_date(self):\n",
    "        self.assertTrue(was_operating_unit(self.units[4], date(2025, 1, 1)))\n",
    "\n",
    "    # Test cases for the sixth unit (committed/no end date)\n",
    "    def test_unit6_before_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[5], date(2002, 12, 31)))\n",
    "\n",
    "    # unit6 = {\n",
    "    #     \"first_seen\": date(2003, 3, 1),\n",
    "    #     \"status\": \"committed\",\n",
    "    #     \"fuel_tech\": \"gas\",\n",
    "    #     \"registered_capacity_MW\": 120\n",
    "    # }\n",
    "\n",
    "    # failing\n",
    "    def test_unit6_after_first_seen(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[5], date(2004, 1, 1)))\n",
    "\n",
    "    # failing\n",
    "    def test_unit6_future_date(self):\n",
    "        self.assertFalse(was_operating_unit(self.units[5], date(2025, 1, 1)))\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    unittest.main(argv=[\"first-arg-is-ignored\"], verbosity=2, exit=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "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>facility_code</th>\n",
       "      <th>duid</th>\n",
       "      <th>network_region</th>\n",
       "      <th>capacity_registered</th>\n",
       "      <th>fuel_tech</th>\n",
       "      <th>status</th>\n",
       "      <th>data_first_seen</th>\n",
       "      <th>data_last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APPIN</td>\n",
       "      <td>APPIN</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>55.0</td>\n",
       "      <td>gas_wcmg</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AVLSF</td>\n",
       "      <td>AVLSF1</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>245.0</td>\n",
       "      <td>solar_utility</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2024-06-10 06:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AWABAREF</td>\n",
       "      <td>AWABAREF</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>bioenergy_biogas</td>\n",
       "      <td>retired</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BANGOWF</td>\n",
       "      <td>BANGOWF2</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>84.8</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2021-06-04 14:25:00</td>\n",
       "      <td>2024-06-10 21:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BANGOWF</td>\n",
       "      <td>BANGOWF1</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>244.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-11-30 14:25:00</td>\n",
       "      <td>2024-06-10 21:15:00</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>696</th>\n",
       "      <td>WARRADARGE</td>\n",
       "      <td>WARRADARGE_WF1</td>\n",
       "      <td>wem</td>\n",
       "      <td>180.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-07-29 00:00:00</td>\n",
       "      <td>2024-01-13 23:55:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>BLAIRFOX_WESTHILLS</td>\n",
       "      <td>BLAIRFOX_WESTHILLS_WF3</td>\n",
       "      <td>wem</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2013-10-10 00:00:00</td>\n",
       "      <td>2024-01-12 11:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>WEST_KALGOORLIE</td>\n",
       "      <td>WEST_KALGOORLIE_GT3</td>\n",
       "      <td>wem</td>\n",
       "      <td>23.3</td>\n",
       "      <td>distillate</td>\n",
       "      <td>operating</td>\n",
       "      <td>2006-09-19 16:00:00</td>\n",
       "      <td>2024-01-13 12:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>WEST_KALGOORLIE</td>\n",
       "      <td>WEST_KALGOORLIE_GT2</td>\n",
       "      <td>wem</td>\n",
       "      <td>41.2</td>\n",
       "      <td>distillate</td>\n",
       "      <td>operating</td>\n",
       "      <td>2006-09-19 16:00:00</td>\n",
       "      <td>2024-01-13 23:55:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>700</th>\n",
       "      <td>YANDIN</td>\n",
       "      <td>YANDIN_WF1</td>\n",
       "      <td>wem</td>\n",
       "      <td>214.0</td>\n",
       "      <td>wind</td>\n",
       "      <td>operating</td>\n",
       "      <td>2020-07-01 00:00:00</td>\n",
       "      <td>2024-01-13 23:20:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>701 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          facility_code                    duid network_region  \\\n",
       "0                 APPIN                   APPIN           nsw1   \n",
       "1                 AVLSF                  AVLSF1           nsw1   \n",
       "2              AWABAREF                AWABAREF           nsw1   \n",
       "3               BANGOWF                BANGOWF2           nsw1   \n",
       "4               BANGOWF                BANGOWF1           nsw1   \n",
       "..                  ...                     ...            ...   \n",
       "696          WARRADARGE          WARRADARGE_WF1            wem   \n",
       "697  BLAIRFOX_WESTHILLS  BLAIRFOX_WESTHILLS_WF3            wem   \n",
       "698     WEST_KALGOORLIE     WEST_KALGOORLIE_GT3            wem   \n",
       "699     WEST_KALGOORLIE     WEST_KALGOORLIE_GT2            wem   \n",
       "700              YANDIN              YANDIN_WF1            wem   \n",
       "\n",
       "     capacity_registered         fuel_tech     status      data_first_seen  \\\n",
       "0                   55.0          gas_wcmg  operating                  NaT   \n",
       "1                  245.0     solar_utility  operating                  NaT   \n",
       "2                    1.0  bioenergy_biogas    retired                  NaT   \n",
       "3                   84.8              wind  operating  2021-06-04 14:25:00   \n",
       "4                  244.0              wind  operating  2020-11-30 14:25:00   \n",
       "..                   ...               ...        ...                  ...   \n",
       "696                180.0              wind  operating  2020-07-29 00:00:00   \n",
       "697                  5.0              wind  operating  2013-10-10 00:00:00   \n",
       "698                 23.3        distillate  operating  2006-09-19 16:00:00   \n",
       "699                 41.2        distillate  operating  2006-09-19 16:00:00   \n",
       "700                214.0              wind  operating  2020-07-01 00:00:00   \n",
       "\n",
       "          data_last_seen  \n",
       "0                    NaT  \n",
       "1    2024-06-10 06:50:00  \n",
       "2                    NaT  \n",
       "3    2024-06-10 21:15:00  \n",
       "4    2024-06-10 21:15:00  \n",
       "..                   ...  \n",
       "696  2024-01-13 23:55:00  \n",
       "697  2024-01-12 11:00:00  \n",
       "698  2024-01-13 12:40:00  \n",
       "699  2024-01-13 23:55:00  \n",
       "700  2024-01-13 23:20:00  \n",
       "\n",
       "[701 rows x 8 columns]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculate the month ends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.date(1998, 12, 31),\n",
       " datetime.date(1999, 1, 31),\n",
       " datetime.date(1999, 2, 28),\n",
       " datetime.date(1999, 3, 31),\n",
       " datetime.date(1999, 4, 30),\n",
       " datetime.date(1999, 5, 31),\n",
       " datetime.date(1999, 6, 30),\n",
       " datetime.date(1999, 7, 31),\n",
       " datetime.date(1999, 8, 31),\n",
       " datetime.date(1999, 9, 30),\n",
       " datetime.date(1999, 10, 31),\n",
       " datetime.date(1999, 11, 30),\n",
       " datetime.date(1999, 12, 31),\n",
       " datetime.date(2000, 1, 31),\n",
       " datetime.date(2000, 2, 29),\n",
       " datetime.date(2000, 3, 31),\n",
       " datetime.date(2000, 4, 30),\n",
       " datetime.date(2000, 5, 31),\n",
       " datetime.date(2000, 6, 30),\n",
       " datetime.date(2000, 7, 31),\n",
       " datetime.date(2000, 8, 31),\n",
       " datetime.date(2000, 9, 30),\n",
       " datetime.date(2000, 10, 31),\n",
       " datetime.date(2000, 11, 30),\n",
       " datetime.date(2000, 12, 31),\n",
       " datetime.date(2001, 1, 31),\n",
       " datetime.date(2001, 2, 28),\n",
       " datetime.date(2001, 3, 31),\n",
       " datetime.date(2001, 4, 30),\n",
       " datetime.date(2001, 5, 31),\n",
       " datetime.date(2001, 6, 30),\n",
       " datetime.date(2001, 7, 31),\n",
       " datetime.date(2001, 8, 31),\n",
       " datetime.date(2001, 9, 30),\n",
       " datetime.date(2001, 10, 31),\n",
       " datetime.date(2001, 11, 30),\n",
       " datetime.date(2001, 12, 31),\n",
       " datetime.date(2002, 1, 31),\n",
       " datetime.date(2002, 2, 28),\n",
       " datetime.date(2002, 3, 31),\n",
       " datetime.date(2002, 4, 30),\n",
       " datetime.date(2002, 5, 31),\n",
       " datetime.date(2002, 6, 30),\n",
       " datetime.date(2002, 7, 31),\n",
       " datetime.date(2002, 8, 31),\n",
       " datetime.date(2002, 9, 30),\n",
       " datetime.date(2002, 10, 31),\n",
       " datetime.date(2002, 11, 30),\n",
       " datetime.date(2002, 12, 31),\n",
       " datetime.date(2003, 1, 31),\n",
       " datetime.date(2003, 2, 28),\n",
       " datetime.date(2003, 3, 31),\n",
       " datetime.date(2003, 4, 30),\n",
       " datetime.date(2003, 5, 31),\n",
       " datetime.date(2003, 6, 30),\n",
       " datetime.date(2003, 7, 31),\n",
       " datetime.date(2003, 8, 31),\n",
       " datetime.date(2003, 9, 30),\n",
       " datetime.date(2003, 10, 31),\n",
       " datetime.date(2003, 11, 30),\n",
       " datetime.date(2003, 12, 31),\n",
       " datetime.date(2004, 1, 31),\n",
       " datetime.date(2004, 2, 29),\n",
       " datetime.date(2004, 3, 31),\n",
       " datetime.date(2004, 4, 30),\n",
       " datetime.date(2004, 5, 31),\n",
       " datetime.date(2004, 6, 30),\n",
       " datetime.date(2004, 7, 31),\n",
       " datetime.date(2004, 8, 31),\n",
       " datetime.date(2004, 9, 30),\n",
       " datetime.date(2004, 10, 31),\n",
       " datetime.date(2004, 11, 30),\n",
       " datetime.date(2004, 12, 31),\n",
       " datetime.date(2005, 1, 31),\n",
       " datetime.date(2005, 2, 28),\n",
       " datetime.date(2005, 3, 31),\n",
       " datetime.date(2005, 4, 30),\n",
       " datetime.date(2005, 5, 31),\n",
       " datetime.date(2005, 6, 30),\n",
       " datetime.date(2005, 7, 31),\n",
       " datetime.date(2005, 8, 31),\n",
       " datetime.date(2005, 9, 30),\n",
       " datetime.date(2005, 10, 31),\n",
       " datetime.date(2005, 11, 30),\n",
       " datetime.date(2005, 12, 31),\n",
       " datetime.date(2006, 1, 31),\n",
       " datetime.date(2006, 2, 28),\n",
       " datetime.date(2006, 3, 31),\n",
       " datetime.date(2006, 4, 30),\n",
       " datetime.date(2006, 5, 31),\n",
       " datetime.date(2006, 6, 30),\n",
       " datetime.date(2006, 7, 31),\n",
       " datetime.date(2006, 8, 31),\n",
       " datetime.date(2006, 9, 30),\n",
       " datetime.date(2006, 10, 31),\n",
       " datetime.date(2006, 11, 30),\n",
       " datetime.date(2006, 12, 31),\n",
       " datetime.date(2007, 1, 31),\n",
       " datetime.date(2007, 2, 28),\n",
       " datetime.date(2007, 3, 31),\n",
       " datetime.date(2007, 4, 30),\n",
       " datetime.date(2007, 5, 31),\n",
       " datetime.date(2007, 6, 30),\n",
       " datetime.date(2007, 7, 31),\n",
       " datetime.date(2007, 8, 31),\n",
       " datetime.date(2007, 9, 30),\n",
       " datetime.date(2007, 10, 31),\n",
       " datetime.date(2007, 11, 30),\n",
       " datetime.date(2007, 12, 31),\n",
       " datetime.date(2008, 1, 31),\n",
       " datetime.date(2008, 2, 29),\n",
       " datetime.date(2008, 3, 31),\n",
       " datetime.date(2008, 4, 30),\n",
       " datetime.date(2008, 5, 31),\n",
       " datetime.date(2008, 6, 30),\n",
       " datetime.date(2008, 7, 31),\n",
       " datetime.date(2008, 8, 31),\n",
       " datetime.date(2008, 9, 30),\n",
       " datetime.date(2008, 10, 31),\n",
       " datetime.date(2008, 11, 30),\n",
       " datetime.date(2008, 12, 31),\n",
       " datetime.date(2009, 1, 31),\n",
       " datetime.date(2009, 2, 28),\n",
       " datetime.date(2009, 3, 31),\n",
       " datetime.date(2009, 4, 30),\n",
       " datetime.date(2009, 5, 31),\n",
       " datetime.date(2009, 6, 30),\n",
       " datetime.date(2009, 7, 31),\n",
       " datetime.date(2009, 8, 31),\n",
       " datetime.date(2009, 9, 30),\n",
       " datetime.date(2009, 10, 31),\n",
       " datetime.date(2009, 11, 30),\n",
       " datetime.date(2009, 12, 31),\n",
       " datetime.date(2010, 1, 31),\n",
       " datetime.date(2010, 2, 28),\n",
       " datetime.date(2010, 3, 31),\n",
       " datetime.date(2010, 4, 30),\n",
       " datetime.date(2010, 5, 31),\n",
       " datetime.date(2010, 6, 30),\n",
       " datetime.date(2010, 7, 31),\n",
       " datetime.date(2010, 8, 31),\n",
       " datetime.date(2010, 9, 30),\n",
       " datetime.date(2010, 10, 31),\n",
       " datetime.date(2010, 11, 30),\n",
       " datetime.date(2010, 12, 31),\n",
       " datetime.date(2011, 1, 31),\n",
       " datetime.date(2011, 2, 28),\n",
       " datetime.date(2011, 3, 31),\n",
       " datetime.date(2011, 4, 30),\n",
       " datetime.date(2011, 5, 31),\n",
       " datetime.date(2011, 6, 30),\n",
       " datetime.date(2011, 7, 31),\n",
       " datetime.date(2011, 8, 31),\n",
       " datetime.date(2011, 9, 30),\n",
       " datetime.date(2011, 10, 31),\n",
       " datetime.date(2011, 11, 30),\n",
       " datetime.date(2011, 12, 31),\n",
       " datetime.date(2012, 1, 31),\n",
       " datetime.date(2012, 2, 29),\n",
       " datetime.date(2012, 3, 31),\n",
       " datetime.date(2012, 4, 30),\n",
       " datetime.date(2012, 5, 31),\n",
       " datetime.date(2012, 6, 30),\n",
       " datetime.date(2012, 7, 31),\n",
       " datetime.date(2012, 8, 31),\n",
       " datetime.date(2012, 9, 30),\n",
       " datetime.date(2012, 10, 31),\n",
       " datetime.date(2012, 11, 30),\n",
       " datetime.date(2012, 12, 31),\n",
       " datetime.date(2013, 1, 31),\n",
       " datetime.date(2013, 2, 28),\n",
       " datetime.date(2013, 3, 31),\n",
       " datetime.date(2013, 4, 30),\n",
       " datetime.date(2013, 5, 31),\n",
       " datetime.date(2013, 6, 30),\n",
       " datetime.date(2013, 7, 31),\n",
       " datetime.date(2013, 8, 31),\n",
       " datetime.date(2013, 9, 30),\n",
       " datetime.date(2013, 10, 31),\n",
       " datetime.date(2013, 11, 30),\n",
       " datetime.date(2013, 12, 31),\n",
       " datetime.date(2014, 1, 31),\n",
       " datetime.date(2014, 2, 28),\n",
       " datetime.date(2014, 3, 31),\n",
       " datetime.date(2014, 4, 30),\n",
       " datetime.date(2014, 5, 31),\n",
       " datetime.date(2014, 6, 30),\n",
       " datetime.date(2014, 7, 31),\n",
       " datetime.date(2014, 8, 31),\n",
       " datetime.date(2014, 9, 30),\n",
       " datetime.date(2014, 10, 31),\n",
       " datetime.date(2014, 11, 30),\n",
       " datetime.date(2014, 12, 31),\n",
       " datetime.date(2015, 1, 31),\n",
       " datetime.date(2015, 2, 28),\n",
       " datetime.date(2015, 3, 31),\n",
       " datetime.date(2015, 4, 30),\n",
       " datetime.date(2015, 5, 31),\n",
       " datetime.date(2015, 6, 30),\n",
       " datetime.date(2015, 7, 31),\n",
       " datetime.date(2015, 8, 31),\n",
       " datetime.date(2015, 9, 30),\n",
       " datetime.date(2015, 10, 31),\n",
       " datetime.date(2015, 11, 30),\n",
       " datetime.date(2015, 12, 31),\n",
       " datetime.date(2016, 1, 31),\n",
       " datetime.date(2016, 2, 29),\n",
       " datetime.date(2016, 3, 31),\n",
       " datetime.date(2016, 4, 30),\n",
       " datetime.date(2016, 5, 31),\n",
       " datetime.date(2016, 6, 30),\n",
       " datetime.date(2016, 7, 31),\n",
       " datetime.date(2016, 8, 31),\n",
       " datetime.date(2016, 9, 30),\n",
       " datetime.date(2016, 10, 31),\n",
       " datetime.date(2016, 11, 30),\n",
       " datetime.date(2016, 12, 31),\n",
       " datetime.date(2017, 1, 31),\n",
       " datetime.date(2017, 2, 28),\n",
       " datetime.date(2017, 3, 31),\n",
       " datetime.date(2017, 4, 30),\n",
       " datetime.date(2017, 5, 31),\n",
       " datetime.date(2017, 6, 30),\n",
       " datetime.date(2017, 7, 31),\n",
       " datetime.date(2017, 8, 31),\n",
       " datetime.date(2017, 9, 30),\n",
       " datetime.date(2017, 10, 31),\n",
       " datetime.date(2017, 11, 30),\n",
       " datetime.date(2017, 12, 31),\n",
       " datetime.date(2018, 1, 31),\n",
       " datetime.date(2018, 2, 28),\n",
       " datetime.date(2018, 3, 31),\n",
       " datetime.date(2018, 4, 30),\n",
       " datetime.date(2018, 5, 31),\n",
       " datetime.date(2018, 6, 30),\n",
       " datetime.date(2018, 7, 31),\n",
       " datetime.date(2018, 8, 31),\n",
       " datetime.date(2018, 9, 30),\n",
       " datetime.date(2018, 10, 31),\n",
       " datetime.date(2018, 11, 30),\n",
       " datetime.date(2018, 12, 31),\n",
       " datetime.date(2019, 1, 31),\n",
       " datetime.date(2019, 2, 28),\n",
       " datetime.date(2019, 3, 31),\n",
       " datetime.date(2019, 4, 30),\n",
       " datetime.date(2019, 5, 31),\n",
       " datetime.date(2019, 6, 30),\n",
       " datetime.date(2019, 7, 31),\n",
       " datetime.date(2019, 8, 31),\n",
       " datetime.date(2019, 9, 30),\n",
       " datetime.date(2019, 10, 31),\n",
       " datetime.date(2019, 11, 30),\n",
       " datetime.date(2019, 12, 31),\n",
       " datetime.date(2020, 1, 31),\n",
       " datetime.date(2020, 2, 29),\n",
       " datetime.date(2020, 3, 31),\n",
       " datetime.date(2020, 4, 30),\n",
       " datetime.date(2020, 5, 31),\n",
       " datetime.date(2020, 6, 30),\n",
       " datetime.date(2020, 7, 31),\n",
       " datetime.date(2020, 8, 31),\n",
       " datetime.date(2020, 9, 30),\n",
       " datetime.date(2020, 10, 31),\n",
       " datetime.date(2020, 11, 30),\n",
       " datetime.date(2020, 12, 31),\n",
       " datetime.date(2021, 1, 31),\n",
       " datetime.date(2021, 2, 28),\n",
       " datetime.date(2021, 3, 31),\n",
       " datetime.date(2021, 4, 30),\n",
       " datetime.date(2021, 5, 31),\n",
       " datetime.date(2021, 6, 30),\n",
       " datetime.date(2021, 7, 31),\n",
       " datetime.date(2021, 8, 31),\n",
       " datetime.date(2021, 9, 30),\n",
       " datetime.date(2021, 10, 31),\n",
       " datetime.date(2021, 11, 30),\n",
       " datetime.date(2021, 12, 31),\n",
       " datetime.date(2022, 1, 31),\n",
       " datetime.date(2022, 2, 28),\n",
       " datetime.date(2022, 3, 31),\n",
       " datetime.date(2022, 4, 30),\n",
       " datetime.date(2022, 5, 31),\n",
       " datetime.date(2022, 6, 30),\n",
       " datetime.date(2022, 7, 31),\n",
       " datetime.date(2022, 8, 31),\n",
       " datetime.date(2022, 9, 30),\n",
       " datetime.date(2022, 10, 31),\n",
       " datetime.date(2022, 11, 30),\n",
       " datetime.date(2022, 12, 31),\n",
       " datetime.date(2023, 1, 31),\n",
       " datetime.date(2023, 2, 28),\n",
       " datetime.date(2023, 3, 31),\n",
       " datetime.date(2023, 4, 30),\n",
       " datetime.date(2023, 5, 31),\n",
       " datetime.date(2023, 6, 30),\n",
       " datetime.date(2023, 7, 31),\n",
       " datetime.date(2023, 8, 31),\n",
       " datetime.date(2023, 9, 30),\n",
       " datetime.date(2023, 10, 31),\n",
       " datetime.date(2023, 11, 30),\n",
       " datetime.date(2023, 12, 31),\n",
       " datetime.date(2024, 1, 31),\n",
       " datetime.date(2024, 2, 29),\n",
       " datetime.date(2024, 3, 31),\n",
       " datetime.date(2024, 4, 30),\n",
       " datetime.date(2024, 5, 31),\n",
       " datetime.date(2024, 6, 30),\n",
       " datetime.date(2024, 7, 31)]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# given two dates, prepare an array with the dates of the last day of each month\n",
    "# between the two dates (including the month of the first and last date)\n",
    "def generate_month_ends(start_date, end_date):\n",
    "    # Generate a complete range of month-end dates\n",
    "    date_range = pd.date_range(start=start_date, end=end_date + pd.DateOffset(months=1), freq='ME')\n",
    "    \n",
    "    # Extract just the date part\n",
    "    months = [date.date() for date in date_range]\n",
    "    return months\n",
    "\n",
    "\n",
    "st = find_first_seen(all_units)\n",
    "en = date.today()\n",
    "\n",
    "month_ends = generate_month_ends(st, en)\n",
    "month_ends\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## build capacity table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 701 entries, 0 to 700\n",
      "Columns: 312 entries, fuel_tech to 2024-07-31\n",
      "dtypes: float64(309), int64(1), object(2)\n",
      "memory usage: 1.7+ MB\n"
     ]
    },
    {
     "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>fuel_tech</th>\n",
       "      <th>network_region</th>\n",
       "      <th>invalid_count</th>\n",
       "      <th>invalid_capacity</th>\n",
       "      <th>1998-12-31</th>\n",
       "      <th>1999-01-31</th>\n",
       "      <th>1999-02-28</th>\n",
       "      <th>1999-03-31</th>\n",
       "      <th>1999-04-30</th>\n",
       "      <th>1999-05-31</th>\n",
       "      <th>...</th>\n",
       "      <th>2023-10-31</th>\n",
       "      <th>2023-11-30</th>\n",
       "      <th>2023-12-31</th>\n",
       "      <th>2024-01-31</th>\n",
       "      <th>2024-02-29</th>\n",
       "      <th>2024-03-31</th>\n",
       "      <th>2024-04-30</th>\n",
       "      <th>2024-05-31</th>\n",
       "      <th>2024-06-30</th>\n",
       "      <th>2024-07-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>battery</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>6</td>\n",
       "      <td>346.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>battery</td>\n",
       "      <td>qld1</td>\n",
       "      <td>2</td>\n",
       "      <td>184.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "      <td>152.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>battery</td>\n",
       "      <td>sa1</td>\n",
       "      <td>2</td>\n",
       "      <td>300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>battery</td>\n",
       "      <td>vic1</td>\n",
       "      <td>2</td>\n",
       "      <td>207.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "      <td>440.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>battery</td>\n",
       "      <td>wem</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>wind</td>\n",
       "      <td>qld1</td>\n",
       "      <td>3</td>\n",
       "      <td>348.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "      <td>675.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>wind</td>\n",
       "      <td>sa1</td>\n",
       "      <td>3</td>\n",
       "      <td>622.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "      <td>2140.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>wind</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "      <td>567.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>wind</td>\n",
       "      <td>vic1</td>\n",
       "      <td>12</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>wind</td>\n",
       "      <td>wem</td>\n",
       "      <td>1</td>\n",
       "      <td>75.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 312 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   fuel_tech network_region  invalid_count  invalid_capacity  1998-12-31  \\\n",
       "0    battery           nsw1              6             346.0         0.0   \n",
       "1    battery           qld1              2             184.0         0.0   \n",
       "2    battery            sa1              2             300.0         0.0   \n",
       "3    battery           vic1              2             207.0         0.0   \n",
       "4    battery            wem              1             100.0         0.0   \n",
       "..       ...            ...            ...               ...         ...   \n",
       "56      wind           qld1              3             348.0         0.0   \n",
       "57      wind            sa1              3             622.5         0.0   \n",
       "58      wind           tas1              0               0.0         0.0   \n",
       "59      wind           vic1             12             371.0         0.0   \n",
       "60      wind            wem              1              75.6         0.0   \n",
       "\n",
       "    1999-01-31  1999-02-28  1999-03-31  1999-04-30  1999-05-31  ...  \\\n",
       "0          0.0         0.0         0.0         0.0         0.0  ...   \n",
       "1          0.0         0.0         0.0         0.0         0.0  ...   \n",
       "2          0.0         0.0         0.0         0.0         0.0  ...   \n",
       "3          0.0         0.0         0.0         0.0         0.0  ...   \n",
       "4          0.0         0.0         0.0         0.0         0.0  ...   \n",
       "..         ...         ...         ...         ...         ...  ...   \n",
       "56         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "57         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "58         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "59         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "60         0.0         0.0         0.0         0.0         0.0  ...   \n",
       "\n",
       "    2023-10-31  2023-11-30  2023-12-31  2024-01-31  2024-02-29  2024-03-31  \\\n",
       "0         0.00        0.00        0.00        0.00        0.00        0.00   \n",
       "1       152.00      152.00      152.00      152.00      152.00      152.00   \n",
       "2       211.27      211.27      211.27      211.27      211.27      211.27   \n",
       "3       440.00      440.00      440.00      440.00      440.00      440.00   \n",
       "4         0.00        0.00        0.00        0.00        0.00        0.00   \n",
       "..         ...         ...         ...         ...         ...         ...   \n",
       "56      675.20      675.20      675.20      675.20      675.20      675.20   \n",
       "57     2140.90     2140.90     2140.90     2140.90     2140.90     2140.90   \n",
       "58      567.00      567.00      567.00      567.00      567.00      567.00   \n",
       "59     2643.77     2643.77     2643.77     2643.77     2643.77     2643.77   \n",
       "60     1005.57     1005.57     1005.57     1005.57     1005.57     1005.57   \n",
       "\n",
       "    2024-04-30  2024-05-31  2024-06-30  2024-07-31  \n",
       "0         0.00        0.00        0.00        0.00  \n",
       "1       152.00      152.00      152.00      152.00  \n",
       "2       211.27      211.27      211.27      211.27  \n",
       "3       440.00      440.00      440.00      440.00  \n",
       "4         0.00        0.00        0.00        0.00  \n",
       "..         ...         ...         ...         ...  \n",
       "56      675.20      675.20      675.20      675.20  \n",
       "57     2140.90     2140.90     2140.90     2140.90  \n",
       "58      567.00      567.00      567.00      567.00  \n",
       "59     2643.77     2643.77     2643.77     2643.77  \n",
       "60     1005.57     1005.57     1005.57     1005.57  \n",
       "\n",
       "[61 rows x 312 columns]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def time_to_date(dt):\n",
    "    if pd.isnull(dt):\n",
    "        return None\n",
    "    else:\n",
    "        return dt.date()\n",
    "\n",
    "\n",
    "def process_row(row, month_ends):\n",
    "    capacity = row['capacity_registered']\n",
    "    status = row['status']\n",
    "\n",
    "    first_seen = time_to_date(row['data_first_seen'])\n",
    "    last_seen = time_to_date(row['data_last_seen'])\n",
    "\n",
    "    new_row = {\n",
    "        'fuel_tech': row['fuel_tech'],\n",
    "        'network_region': row['network_region']\n",
    "    }\n",
    "\n",
    "    if has_missing_dates(status, first_seen, last_seen):\n",
    "        new_row['invalid_count'] = 1\n",
    "        new_row['invalid_capacity'] = capacity\n",
    "        for month_end in month_ends:\n",
    "            new_row[month_end] = 0\n",
    "    else:\n",
    "        new_row['invalid_count'] = 0\n",
    "        new_row['invalid_capacity'] = 0\n",
    "        for month_end in month_ends:\n",
    "            new_row[month_end] = capacity if was_operating(status, first_seen, last_seen, month_end) else 0\n",
    "\n",
    "    return new_row\n",
    "\n",
    "\n",
    "# construct the capacity table\n",
    "def build_capacity_table(units, month_ends):\n",
    "    processed_rows = [process_row(row, month_ends) for _, row in units.iterrows()]\n",
    "    new_units = pd.DataFrame(processed_rows)\n",
    "\n",
    "    new_units.info()\n",
    "\n",
    "    # Make a summary table of units grouped by fuel_tech, network_region, summing up all the numeric columns\n",
    "    summary = new_units.groupby(['fuel_tech', 'network_region']).sum()\n",
    "    summary.reset_index(inplace=True)\n",
    "    return summary\n",
    "\n",
    "capacity_table = build_capacity_table(all_units, month_ends)\n",
    "capacity_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculate rooftop solar data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### load and cache rooftop solar data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: no cached data found, downloading from https://pv-map.apvi.org.au/data/postcode/monthly/capacity/\n",
      "INFO: region VIC1: loaded 207 records from 2007-01 to 2024-03\n",
      "INFO: region NSW1: loaded 207 records from 2007-01 to 2024-03\n",
      "INFO: region SA1: loaded 207 records from 2007-01 to 2024-03\n",
      "INFO: region WEM: loaded 207 records from 2007-01 to 2024-03\n",
      "INFO: region TAS1: loaded 203 records from 2007-04 to 2024-03\n",
      "INFO: region QLD1: loaded 207 records from 2007-01 to 2024-03\n",
      "INFO: loaded 1238 records from ./input/apvi_data.parquet\n"
     ]
    },
    {
     "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>month</th>\n",
       "      <th>2hf</th>\n",
       "      <th>2hf_4hf</th>\n",
       "      <th>4hf_6hf</th>\n",
       "      <th>6hf_9hf</th>\n",
       "      <th>9hf_14</th>\n",
       "      <th>14_25</th>\n",
       "      <th>25_50</th>\n",
       "      <th>50_100</th>\n",
       "      <th>100_5000</th>\n",
       "      <th>5000_30000</th>\n",
       "      <th>30000</th>\n",
       "      <th>network_region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIC1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIC1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-03</td>\n",
       "      <td>35.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIC1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-04</td>\n",
       "      <td>43.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIC1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-05</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIC1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>2023-11</td>\n",
       "      <td>231.0</td>\n",
       "      <td>938.0</td>\n",
       "      <td>2567.0</td>\n",
       "      <td>19950.0</td>\n",
       "      <td>34574.0</td>\n",
       "      <td>6546.0</td>\n",
       "      <td>5270.0</td>\n",
       "      <td>4387.0</td>\n",
       "      <td>4463.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>QLD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>148.0</td>\n",
       "      <td>784.0</td>\n",
       "      <td>2231.0</td>\n",
       "      <td>16713.0</td>\n",
       "      <td>30635.0</td>\n",
       "      <td>7935.0</td>\n",
       "      <td>8640.0</td>\n",
       "      <td>5099.0</td>\n",
       "      <td>1582.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>QLD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>2024-01</td>\n",
       "      <td>125.0</td>\n",
       "      <td>571.0</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>12028.0</td>\n",
       "      <td>20174.0</td>\n",
       "      <td>2963.0</td>\n",
       "      <td>1649.0</td>\n",
       "      <td>858.0</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>QLD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2024-02</td>\n",
       "      <td>198.0</td>\n",
       "      <td>894.0</td>\n",
       "      <td>2307.0</td>\n",
       "      <td>17292.0</td>\n",
       "      <td>30253.0</td>\n",
       "      <td>4612.0</td>\n",
       "      <td>3352.0</td>\n",
       "      <td>299.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>QLD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>2024-03</td>\n",
       "      <td>92.0</td>\n",
       "      <td>639.0</td>\n",
       "      <td>1710.0</td>\n",
       "      <td>11231.0</td>\n",
       "      <td>20164.0</td>\n",
       "      <td>3526.0</td>\n",
       "      <td>1952.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1175.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>QLD1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1238 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       month    2hf  2hf_4hf  4hf_6hf  6hf_9hf   9hf_14   14_25   25_50  \\\n",
       "0    2007-01   21.0      3.0      0.0      0.0      0.0     0.0     0.0   \n",
       "1    2007-02   22.0      0.0      0.0      0.0      0.0     0.0     0.0   \n",
       "2    2007-03   35.0      6.0      5.0      0.0      0.0     0.0     0.0   \n",
       "3    2007-04   43.0     12.0      5.0      0.0      0.0     0.0     0.0   \n",
       "4    2007-05   55.0      0.0      5.0      0.0      0.0     0.0     0.0   \n",
       "..       ...    ...      ...      ...      ...      ...     ...     ...   \n",
       "202  2023-11  231.0    938.0   2567.0  19950.0  34574.0  6546.0  5270.0   \n",
       "203  2023-12  148.0    784.0   2231.0  16713.0  30635.0  7935.0  8640.0   \n",
       "204  2024-01  125.0    571.0   1725.0  12028.0  20174.0  2963.0  1649.0   \n",
       "205  2024-02  198.0    894.0   2307.0  17292.0  30253.0  4612.0  3352.0   \n",
       "206  2024-03   92.0    639.0   1710.0  11231.0  20164.0  3526.0  1952.0   \n",
       "\n",
       "     50_100  100_5000  5000_30000  30000 network_region  \n",
       "0       0.0       0.0         0.0    0.0           VIC1  \n",
       "1       0.0       0.0         0.0    0.0           VIC1  \n",
       "2       0.0       0.0         0.0    0.0           VIC1  \n",
       "3       0.0       0.0         0.0    0.0           VIC1  \n",
       "4       0.0       0.0         0.0    0.0           VIC1  \n",
       "..      ...       ...         ...    ...            ...  \n",
       "202  4387.0    4463.0         0.0    0.0           QLD1  \n",
       "203  5099.0    1582.0         0.0    0.0           QLD1  \n",
       "204   858.0    1976.0         0.0    0.0           QLD1  \n",
       "205   299.0       0.0         0.0    0.0           QLD1  \n",
       "206    79.0    1175.0         0.0    0.0           QLD1  \n",
       "\n",
       "[1238 rows x 13 columns]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "APVI_BASE_URL = \"https://pv-map.apvi.org.au/data/postcode/monthly/capacity/\"\n",
    "APVI_FILENAME = 'apvi_data.parquet'\n",
    "\n",
    "def download_solar_PV_data():\n",
    "    REGION_TO_POSTCODE = {\n",
    "        \"VIC1\": \"3XXX\",\n",
    "        \"NSW1\": \"2XXX\",\n",
    "        \"SA1\": \"5XXX\",\n",
    "        \"WEM\": \"6XXX\",\n",
    "        \"TAS1\": \"7XXX\",\n",
    "        \"QLD1\": \"4XXX\"\n",
    "    }\n",
    "\n",
    "    frames = []\n",
    "\n",
    "    for region, postcode in REGION_TO_POSTCODE.items():\n",
    "        url = APVI_BASE_URL + postcode\n",
    "        response = requests.get(url)\n",
    "\n",
    "        if response.status_code == 200:\n",
    "            data = response.json()\n",
    "            df = pd.DataFrame(data[postcode])\n",
    "            df['network_region'] = region\n",
    "            frames.append(df)\n",
    "            print(f'INFO: region {region}: loaded {df.shape[0]} records from {df[\"month\"].min()} to {df[\"month\"].max()}')\n",
    "        else:\n",
    "             # die with an error\n",
    "            raise Exception(f'ERROR: region {region} failed to load')\n",
    "\n",
    "    df_all = pd.concat(frames)\n",
    "\n",
    "    return df_all\n",
    "\n",
    "\n",
    "def load_cached_solar_PV_data():\n",
    "    INPUT_FILE = os.path.join(INPUT_FOLDER, APVI_FILENAME)\n",
    "\n",
    "    # create if doesn't exist\n",
    "    if not os.path.exists(INPUT_FILE):\n",
    "        print(f'INFO: no cached data found, downloading from {APVI_BASE_URL}')\n",
    "        downloaded_data = download_solar_PV_data()\n",
    "        downloaded_data.to_parquet(INPUT_FILE)\n",
    "\n",
    "    # load from cace\n",
    "    df = pd.read_parquet(INPUT_FILE)\n",
    "    print(f'INFO: loaded {df.shape[0]} records from {INPUT_FILE}')\n",
    "    return df\n",
    "\n",
    "solar = load_cached_solar_PV_data()\n",
    "solar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### process solar files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "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>month</th>\n",
       "      <th>network_region</th>\n",
       "      <th>capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02-28</td>\n",
       "      <td>vic1</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-03-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-04-30</td>\n",
       "      <td>vic1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-05-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>qld1</td>\n",
       "      <td>78926.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>73767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>42069.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>qld1</td>\n",
       "      <td>59207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>2024-03-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>40568.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1238 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          month network_region  capacity\n",
       "0    2007-01-31           vic1      24.0\n",
       "1    2007-02-28           vic1      22.0\n",
       "2    2007-03-31           vic1      46.0\n",
       "3    2007-04-30           vic1      60.0\n",
       "4    2007-05-31           vic1      60.0\n",
       "..          ...            ...       ...\n",
       "202  2023-11-30           qld1   78926.0\n",
       "203  2023-12-31           qld1   73767.0\n",
       "204  2024-01-31           qld1   42069.0\n",
       "205  2024-02-29           qld1   59207.0\n",
       "206  2024-03-31           qld1   40568.0\n",
       "\n",
       "[1238 rows x 3 columns]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_last_day_of_month(date_str):\n",
    "    # Convert the string to a Period with monthly frequency\n",
    "    period = pd.Period(date_str, freq='M')\n",
    "    \n",
    "    # Convert the Period to a Timestamp representing the end of the month\n",
    "    last_day_of_month = period.to_timestamp(how='end')\n",
    "    \n",
    "    # Extract and return the date part\n",
    "    last_day_of_month = last_day_of_month.date()\n",
    "    # print(f\"changing {date_str} to {last_day_of_month}\")\n",
    "    return last_day_of_month\n",
    "\n",
    "\n",
    "# sum up all capacity up to 5000 kW, convert months as text to end of month, and convert 'network_region' to lowercase\n",
    "def process_data(df_all):\n",
    "    # sum up all capacity up to 5000 kW\n",
    "    df_all['capacity'] = df_all[['2hf', '2hf_4hf', '4hf_6hf', '6hf_9hf', '9hf_14', '14_25', '25_50', '50_100', '100_5000']].sum(axis=1)\n",
    "    \n",
    "    processed = df_all[['month', 'network_region', 'capacity']].copy()\n",
    "    # df['month'] = pd.to_datetime(df['month'])\n",
    "\n",
    "    # convert 'month' column to the end of the month\n",
    "    processed['month'] = processed['month'].apply(get_last_day_of_month)\n",
    "\n",
    "    # convert 'network_region' to lowercase\n",
    "    processed['network_region'] = processed['network_region'].str.lower()\n",
    "\n",
    "    return processed\n",
    "\n",
    "\n",
    "small_scale_solar_build = process_data(solar)\n",
    "small_scale_solar_build"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### backfill missing months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: ensuring that all {len(month_ends)} are filled for each region\n",
      "INFO: vic1 has data for 207 months. filling in the missing 101 months\n",
      "INFO: nsw1 has data for 207 months. filling in the missing 101 months\n",
      "INFO: sa1 has data for 207 months. filling in the missing 101 months\n",
      "INFO: wem has data for 207 months. filling in the missing 101 months\n",
      "INFO: tas1 has data for 203 months. filling in the missing 105 months\n",
      "INFO: qld1 has data for 207 months. filling in the missing 101 months\n"
     ]
    },
    {
     "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>month</th>\n",
       "      <th>network_region</th>\n",
       "      <th>capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02-28</td>\n",
       "      <td>vic1</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-03-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-04-30</td>\n",
       "      <td>vic1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-05-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1843</th>\n",
       "      <td>2006-12-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1844</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1845</th>\n",
       "      <td>2024-05-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1846</th>\n",
       "      <td>2024-06-30</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1847</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1848 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           month network_region  capacity\n",
       "0     2007-01-31           vic1      24.0\n",
       "1     2007-02-28           vic1      22.0\n",
       "2     2007-03-31           vic1      46.0\n",
       "3     2007-04-30           vic1      60.0\n",
       "4     2007-05-31           vic1      60.0\n",
       "...          ...            ...       ...\n",
       "1843  2006-12-31           qld1       0.0\n",
       "1844  2024-04-30           qld1       0.0\n",
       "1845  2024-05-31           qld1       0.0\n",
       "1846  2024-06-30           qld1       0.0\n",
       "1847  2024-07-31           qld1       0.0\n",
       "\n",
       "[1848 rows x 3 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find any missing months and fill with zero new build capacity\n",
    "def fix_missing_months(df, month_ends):\n",
    "    print(\"INFO: ensuring that all {len(month_ends)} are filled for each region\")\n",
    "\n",
    "    # get all the unique network_regions in the data\n",
    "    regions = df['network_region'].unique()\n",
    "\n",
    "    new_rows = []\n",
    "\n",
    "    for network_region in regions:\n",
    "        region_df = small_scale_solar_build[small_scale_solar_build['network_region'] == network_region]\n",
    "\n",
    "        # get just the months from tasmania\n",
    "        region_months = set(region_df['month'].unique())\n",
    "\n",
    "        # create a list of months in month_ends that are not in tasmania_months\n",
    "        missing_months = list(set(month_ends) - region_months)\n",
    "        missing_months.sort()\n",
    "\n",
    "        print(f\"INFO: {network_region} has data for {len(region_months)} months. filling in the missing {len(missing_months)} months\")\n",
    "\n",
    "        # add the missing months to the DataFrame\n",
    "        for month in missing_months:\n",
    "            new_rows.append(dict(network_region=network_region, month=month, capacity=0))\n",
    "    \n",
    "    # add the new rows to the DataFrame\n",
    "    updated_df = pd.concat([df, pd.DataFrame(new_rows)], ignore_index=True)\n",
    "    \n",
    "    # reset the index\n",
    "    updated_df = updated_df.reset_index(drop=True)\n",
    "    \n",
    "    return updated_df\n",
    "\n",
    "\n",
    "filled = fix_missing_months(small_scale_solar_build, month_ends)\n",
    "filled"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### print a summary of the loaded PV data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "| Region   | Capacity (kW)   | Row Count   |\n",
       "|----------|-----------------|-------------|\n",
       "| nsw1     | 6,998,011       | 308         |\n",
       "| qld1     | 6,333,083       | 308         |\n",
       "| sa1      | 2,545,664       | 308         |\n",
       "| tas1     | 315,705         | 308         |\n",
       "| vic1     | 4,701,939       | 308         |\n",
       "| wem      | 2,694,285       | 308         |\n",
       "| Total    | 23,588,687      | 1,848       |"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from tabulate import tabulate\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "def print_markdown_table(df):\n",
    "    # Create a pivot table\n",
    "    pivot_table = df.pivot_table(index='network_region', \n",
    "                                 values='capacity', \n",
    "                                 aggfunc=['sum', 'count']).reset_index()\n",
    "    \n",
    "    # Flatten the column MultiIndex created by pivot_table\n",
    "    pivot_table.columns = ['Region'] + [f'{x} ({y})' for x, y in pivot_table.columns[1:]]\n",
    "    \n",
    "    # Rename the columns appropriately\n",
    "    pivot_table.columns = ['Region', 'Capacity (kW)', 'Row Count']\n",
    "\n",
    "    # Calculate the total capacity and total count of rows\n",
    "    total_capacity = pivot_table['Capacity (kW)'].sum()\n",
    "    total_count = pivot_table['Row Count'].sum()\n",
    "\n",
    "    # Append the total row to the DataFrame\n",
    "    total_row = pd.DataFrame([['Total', total_capacity, total_count]], \n",
    "                             columns=['Region', 'Capacity (kW)', 'Row Count'])\n",
    "    pivot_table = pd.concat([pivot_table, total_row], ignore_index=True)\n",
    "\n",
    "    # Format the capacity values to 0,000 format\n",
    "    pivot_table['Capacity (kW)'] = pivot_table['Capacity (kW)'].apply(lambda x: f\"{x:,.0f}\")\n",
    "    pivot_table['Row Count'] = pivot_table['Row Count'].apply(lambda x: f\"{x:,.0f}\")\n",
    "\n",
    "    # Use tabulate to create a markdown table\n",
    "    markdown_table = tabulate(pivot_table, headers='keys', tablefmt='github', showindex=False)\n",
    "    \n",
    "    # Display the markdown table\n",
    "    display(Markdown(markdown_table))\n",
    "    \n",
    "\n",
    "# Print the markdown table\n",
    "print_markdown_table(filled)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### compute cumulative capacity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 1848 entries, 1339 to 1641\n",
      "Data columns (total 4 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   month                1848 non-null   object \n",
      " 1   network_region       1848 non-null   object \n",
      " 2   capacity             1848 non-null   float64\n",
      " 3   cumulative_capacity  1848 non-null   float64\n",
      "dtypes: float64(2), object(2)\n",
      "memory usage: 72.2+ KB\n"
     ]
    },
    {
     "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>month</th>\n",
       "      <th>network_region</th>\n",
       "      <th>capacity</th>\n",
       "      <th>cumulative_capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1339</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1747</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1440</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>sa1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1642</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1238</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>1847</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6333083.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>sa1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2545664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1746</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>315705.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1338</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>vic1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4701939.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1641</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>wem</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2694285.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1848 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           month network_region  capacity  cumulative_capacity\n",
       "1339  1998-12-31           nsw1       0.0                  0.0\n",
       "1747  1998-12-31           qld1       0.0                  0.0\n",
       "1440  1998-12-31            sa1       0.0                  0.0\n",
       "1642  1998-12-31           tas1       0.0                  0.0\n",
       "1238  1998-12-31           vic1       0.0                  0.0\n",
       "...          ...            ...       ...                  ...\n",
       "1847  2024-07-31           qld1       0.0            6333083.0\n",
       "1540  2024-07-31            sa1       0.0            2545664.0\n",
       "1746  2024-07-31           tas1       0.0             315705.0\n",
       "1338  2024-07-31           vic1       0.0            4701939.0\n",
       "1641  2024-07-31            wem       0.0            2694285.0\n",
       "\n",
       "[1848 rows x 4 columns]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cumulative_capacity(df):\n",
    "    cumulative_capacity = df.copy()\n",
    "\n",
    "    # first sort by month then region\n",
    "    cumulative_capacity.sort_values(by=['month', 'network_region'], inplace=True)\n",
    "\n",
    "    # then groupby month and region and cumsum the capacity\n",
    "    cumulative_capacity['cumulative_capacity'] = cumulative_capacity.groupby('network_region')['capacity'].transform(pd.Series.cumsum)\n",
    "    return cumulative_capacity\n",
    "\n",
    "\n",
    "# all_regions = add_aggregated_region(filled)\n",
    "\n",
    "cumulative = cumulative_capacity(filled)\n",
    "\n",
    "\n",
    "cumulative"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### create a pivot table of solar_rooftop in MW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "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>fuel_tech</th>\n",
       "      <th>network_region</th>\n",
       "      <th>invalid_count</th>\n",
       "      <th>invalid_capacity</th>\n",
       "      <th>1998-12-31</th>\n",
       "      <th>1999-01-31</th>\n",
       "      <th>1999-02-28</th>\n",
       "      <th>1999-03-31</th>\n",
       "      <th>1999-04-30</th>\n",
       "      <th>1999-05-31</th>\n",
       "      <th>...</th>\n",
       "      <th>2023-10-31</th>\n",
       "      <th>2023-11-30</th>\n",
       "      <th>2023-12-31</th>\n",
       "      <th>2024-01-31</th>\n",
       "      <th>2024-02-29</th>\n",
       "      <th>2024-03-31</th>\n",
       "      <th>2024-04-30</th>\n",
       "      <th>2024-05-31</th>\n",
       "      <th>2024-06-30</th>\n",
       "      <th>2024-07-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6578.324</td>\n",
       "      <td>6687.923</td>\n",
       "      <td>6799.86</td>\n",
       "      <td>6864.016</td>\n",
       "      <td>6942.435</td>\n",
       "      <td>6998.011</td>\n",
       "      <td>6998.011</td>\n",
       "      <td>6998.011</td>\n",
       "      <td>6998.011</td>\n",
       "      <td>6998.011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>qld1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6038.546</td>\n",
       "      <td>6117.472</td>\n",
       "      <td>6191.239</td>\n",
       "      <td>6233.308</td>\n",
       "      <td>6292.515</td>\n",
       "      <td>6333.083</td>\n",
       "      <td>6333.083</td>\n",
       "      <td>6333.083</td>\n",
       "      <td>6333.083</td>\n",
       "      <td>6333.083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>sa1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2456.565</td>\n",
       "      <td>2483.698</td>\n",
       "      <td>2508.045</td>\n",
       "      <td>2519.888</td>\n",
       "      <td>2536.352</td>\n",
       "      <td>2545.664</td>\n",
       "      <td>2545.664</td>\n",
       "      <td>2545.664</td>\n",
       "      <td>2545.664</td>\n",
       "      <td>2545.664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>297.704</td>\n",
       "      <td>303.306</td>\n",
       "      <td>307.848</td>\n",
       "      <td>310.092</td>\n",
       "      <td>313.568</td>\n",
       "      <td>315.705</td>\n",
       "      <td>315.705</td>\n",
       "      <td>315.705</td>\n",
       "      <td>315.705</td>\n",
       "      <td>315.705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>vic1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4502.021</td>\n",
       "      <td>4563.727</td>\n",
       "      <td>4621.912</td>\n",
       "      <td>4652.376</td>\n",
       "      <td>4687.728</td>\n",
       "      <td>4701.939</td>\n",
       "      <td>4701.939</td>\n",
       "      <td>4701.939</td>\n",
       "      <td>4701.939</td>\n",
       "      <td>4701.939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>solar_rooftop</td>\n",
       "      <td>wem</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2585.92</td>\n",
       "      <td>2612.518</td>\n",
       "      <td>2638.291</td>\n",
       "      <td>2657.044</td>\n",
       "      <td>2680.393</td>\n",
       "      <td>2694.285</td>\n",
       "      <td>2694.285</td>\n",
       "      <td>2694.285</td>\n",
       "      <td>2694.285</td>\n",
       "      <td>2694.285</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 312 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       fuel_tech network_region invalid_count invalid_capacity 1998-12-31  \\\n",
       "0  solar_rooftop           nsw1             0                0        0.0   \n",
       "1  solar_rooftop           qld1             0                0        0.0   \n",
       "2  solar_rooftop            sa1             0                0        0.0   \n",
       "3  solar_rooftop           tas1             0                0        0.0   \n",
       "4  solar_rooftop           vic1             0                0        0.0   \n",
       "5  solar_rooftop            wem             0                0        0.0   \n",
       "\n",
       "  1999-01-31 1999-02-28 1999-03-31 1999-04-30 1999-05-31  ... 2023-10-31  \\\n",
       "0        0.0        0.0        0.0        0.0        0.0  ...   6578.324   \n",
       "1        0.0        0.0        0.0        0.0        0.0  ...   6038.546   \n",
       "2        0.0        0.0        0.0        0.0        0.0  ...   2456.565   \n",
       "3        0.0        0.0        0.0        0.0        0.0  ...    297.704   \n",
       "4        0.0        0.0        0.0        0.0        0.0  ...   4502.021   \n",
       "5        0.0        0.0        0.0        0.0        0.0  ...    2585.92   \n",
       "\n",
       "  2023-11-30 2023-12-31 2024-01-31 2024-02-29 2024-03-31 2024-04-30  \\\n",
       "0   6687.923    6799.86   6864.016   6942.435   6998.011   6998.011   \n",
       "1   6117.472   6191.239   6233.308   6292.515   6333.083   6333.083   \n",
       "2   2483.698   2508.045   2519.888   2536.352   2545.664   2545.664   \n",
       "3    303.306    307.848    310.092    313.568    315.705    315.705   \n",
       "4   4563.727   4621.912   4652.376   4687.728   4701.939   4701.939   \n",
       "5   2612.518   2638.291   2657.044   2680.393   2694.285   2694.285   \n",
       "\n",
       "  2024-05-31 2024-06-30 2024-07-31  \n",
       "0   6998.011   6998.011   6998.011  \n",
       "1   6333.083   6333.083   6333.083  \n",
       "2   2545.664   2545.664   2545.664  \n",
       "3    315.705    315.705    315.705  \n",
       "4   4701.939   4701.939   4701.939  \n",
       "5   2694.285   2694.285   2694.285  \n",
       "\n",
       "[6 rows x 312 columns]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def create_solar_capacity_table(combined_table):\n",
    "    regions = {}\n",
    "\n",
    "    # prime the output table\n",
    "    for region in {'qld1', 'nsw1', 'vic1', 'sa1', 'tas1', 'wem'}:\n",
    "        regions[region] = {\n",
    "        'fuel_tech': 'solar_rooftop',\n",
    "        'network_region': region,\n",
    "        'invalid_count': 0,\n",
    "        'invalid_capacity': 0\n",
    "    }\n",
    "    \n",
    "    # iterate through the combined_table, and for each row, add the capacity to the correct region\n",
    "    for _, row in combined_table.iterrows():\n",
    "        region = regions[row['network_region']]\n",
    "        region[row['month']] = row['cumulative_capacity'] / 1000\n",
    "\n",
    "    # convert the region_data to a dataframe\n",
    "    df = pd.DataFrame(regions).transpose()\n",
    "\n",
    "    # sort by fuel_tech then network_region\n",
    "    df = df.sort_values(by=['fuel_tech', 'network_region'])\n",
    "\n",
    "    # reindex without the index column\n",
    "    df = df.reset_index()\n",
    "    df = df.drop(columns=['index'])\n",
    "    \n",
    "    return df\n",
    "\n",
    "    \n",
    "rooftop_capacity_table = create_solar_capacity_table(cumulative)\n",
    "rooftop_capacity_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## merge rooftop_solar into with all facilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "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>fuel_tech</th>\n",
       "      <th>network_region</th>\n",
       "      <th>invalid_count</th>\n",
       "      <th>invalid_capacity</th>\n",
       "      <th>1998-12-31</th>\n",
       "      <th>1999-01-31</th>\n",
       "      <th>1999-02-28</th>\n",
       "      <th>1999-03-31</th>\n",
       "      <th>1999-04-30</th>\n",
       "      <th>1999-05-31</th>\n",
       "      <th>...</th>\n",
       "      <th>2023-10-31</th>\n",
       "      <th>2023-11-30</th>\n",
       "      <th>2023-12-31</th>\n",
       "      <th>2024-01-31</th>\n",
       "      <th>2024-02-29</th>\n",
       "      <th>2024-03-31</th>\n",
       "      <th>2024-04-30</th>\n",
       "      <th>2024-05-31</th>\n",
       "      <th>2024-06-30</th>\n",
       "      <th>2024-07-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>battery</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>6</td>\n",
       "      <td>346.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>battery</td>\n",
       "      <td>qld1</td>\n",
       "      <td>2</td>\n",
       "      <td>184.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>battery</td>\n",
       "      <td>sa1</td>\n",
       "      <td>2</td>\n",
       "      <td>300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>battery</td>\n",
       "      <td>vic1</td>\n",
       "      <td>2</td>\n",
       "      <td>207.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>battery</td>\n",
       "      <td>wem</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>wind</td>\n",
       "      <td>qld1</td>\n",
       "      <td>3</td>\n",
       "      <td>348.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>wind</td>\n",
       "      <td>sa1</td>\n",
       "      <td>3</td>\n",
       "      <td>622.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>wind</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>wind</td>\n",
       "      <td>vic1</td>\n",
       "      <td>12</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>wind</td>\n",
       "      <td>wem</td>\n",
       "      <td>1</td>\n",
       "      <td>75.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 312 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   fuel_tech network_region invalid_count invalid_capacity 1998-12-31  \\\n",
       "0    battery           nsw1             6            346.0        0.0   \n",
       "1    battery           qld1             2            184.0        0.0   \n",
       "2    battery            sa1             2            300.0        0.0   \n",
       "3    battery           vic1             2            207.0        0.0   \n",
       "4    battery            wem             1            100.0        0.0   \n",
       "..       ...            ...           ...              ...        ...   \n",
       "62      wind           qld1             3            348.0        0.0   \n",
       "63      wind            sa1             3            622.5        0.0   \n",
       "64      wind           tas1             0              0.0        0.0   \n",
       "65      wind           vic1            12            371.0        0.0   \n",
       "66      wind            wem             1             75.6        0.0   \n",
       "\n",
       "   1999-01-31 1999-02-28 1999-03-31 1999-04-30 1999-05-31  ... 2023-10-31  \\\n",
       "0         0.0        0.0        0.0        0.0        0.0  ...        0.0   \n",
       "1         0.0        0.0        0.0        0.0        0.0  ...      152.0   \n",
       "2         0.0        0.0        0.0        0.0        0.0  ...     211.27   \n",
       "3         0.0        0.0        0.0        0.0        0.0  ...      440.0   \n",
       "4         0.0        0.0        0.0        0.0        0.0  ...        0.0   \n",
       "..        ...        ...        ...        ...        ...  ...        ...   \n",
       "62        0.0        0.0        0.0        0.0        0.0  ...      675.2   \n",
       "63        0.0        0.0        0.0        0.0        0.0  ...     2140.9   \n",
       "64        0.0        0.0        0.0        0.0        0.0  ...      567.0   \n",
       "65        0.0        0.0        0.0        0.0        0.0  ...    2643.77   \n",
       "66        0.0        0.0        0.0        0.0        0.0  ...    1005.57   \n",
       "\n",
       "   2023-11-30 2023-12-31 2024-01-31 2024-02-29 2024-03-31 2024-04-30  \\\n",
       "0         0.0        0.0        0.0        0.0        0.0        0.0   \n",
       "1       152.0      152.0      152.0      152.0      152.0      152.0   \n",
       "2      211.27     211.27     211.27     211.27     211.27     211.27   \n",
       "3       440.0      440.0      440.0      440.0      440.0      440.0   \n",
       "4         0.0        0.0        0.0        0.0        0.0        0.0   \n",
       "..        ...        ...        ...        ...        ...        ...   \n",
       "62      675.2      675.2      675.2      675.2      675.2      675.2   \n",
       "63     2140.9     2140.9     2140.9     2140.9     2140.9     2140.9   \n",
       "64      567.0      567.0      567.0      567.0      567.0      567.0   \n",
       "65    2643.77    2643.77    2643.77    2643.77    2643.77    2643.77   \n",
       "66    1005.57    1005.57    1005.57    1005.57    1005.57    1005.57   \n",
       "\n",
       "   2024-05-31 2024-06-30 2024-07-31  \n",
       "0         0.0        0.0        0.0  \n",
       "1       152.0      152.0      152.0  \n",
       "2      211.27     211.27     211.27  \n",
       "3       440.0      440.0      440.0  \n",
       "4         0.0        0.0        0.0  \n",
       "..        ...        ...        ...  \n",
       "62      675.2      675.2      675.2  \n",
       "63     2140.9     2140.9     2140.9  \n",
       "64      567.0      567.0      567.0  \n",
       "65    2643.77    2643.77    2643.77  \n",
       "66    1005.57    1005.57    1005.57  \n",
       "\n",
       "[67 rows x 312 columns]"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge rooftop solar with the master capacity table\n",
    "def merge_tables(facilities_table, rooftop_table):\n",
    "    # first check that the columns are the same\n",
    "    if not facilities_table.columns.equals(rooftop_table.columns):\n",
    "        print(\"ERROR: columns are not the same\")\n",
    "        print(f\"facilities columns: {facilities_table.columns}\")\n",
    "        print(f\"rooftop columns: {rooftop_table.columns}\")\n",
    "        raise Exception(\"Columns are not the same\")\n",
    "\n",
    "    # concatenate the two tables\n",
    "    unified_table = pd.concat([facilities_table, rooftop_table], axis=0, ignore_index=True)\n",
    "\n",
    "    # sort by fuel_tech then network_region\n",
    "    unified_table = unified_table.sort_values(by=['fuel_tech', 'network_region'])\n",
    "\n",
    "    # reindex the table\n",
    "    unified_table = unified_table.reset_index(drop=True)\n",
    "    return unified_table\n",
    "\n",
    "\n",
    "facilities_with_rooftop_table = merge_tables(capacity_table, rooftop_capacity_table)\n",
    "facilities_with_rooftop_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add in NEM summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "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>fuel_tech</th>\n",
       "      <th>network_region</th>\n",
       "      <th>invalid_count</th>\n",
       "      <th>invalid_capacity</th>\n",
       "      <th>1998-12-31</th>\n",
       "      <th>1999-01-31</th>\n",
       "      <th>1999-02-28</th>\n",
       "      <th>1999-03-31</th>\n",
       "      <th>1999-04-30</th>\n",
       "      <th>1999-05-31</th>\n",
       "      <th>...</th>\n",
       "      <th>2023-10-31</th>\n",
       "      <th>2023-11-30</th>\n",
       "      <th>2023-12-31</th>\n",
       "      <th>2024-01-31</th>\n",
       "      <th>2024-02-29</th>\n",
       "      <th>2024-03-31</th>\n",
       "      <th>2024-04-30</th>\n",
       "      <th>2024-05-31</th>\n",
       "      <th>2024-06-30</th>\n",
       "      <th>2024-07-31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>battery</td>\n",
       "      <td>_au</td>\n",
       "      <td>13</td>\n",
       "      <td>1137.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>battery</td>\n",
       "      <td>_nem</td>\n",
       "      <td>12</td>\n",
       "      <td>1037.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "      <td>803.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>battery</td>\n",
       "      <td>nsw1</td>\n",
       "      <td>6</td>\n",
       "      <td>346.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>battery</td>\n",
       "      <td>qld1</td>\n",
       "      <td>2</td>\n",
       "      <td>184.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>battery</td>\n",
       "      <td>sa1</td>\n",
       "      <td>2</td>\n",
       "      <td>300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</td>\n",
       "      <td>211.27</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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>wind</td>\n",
       "      <td>qld1</td>\n",
       "      <td>3</td>\n",
       "      <td>348.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "      <td>675.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>wind</td>\n",
       "      <td>sa1</td>\n",
       "      <td>3</td>\n",
       "      <td>622.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "      <td>2140.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>wind</td>\n",
       "      <td>tas1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>wind</td>\n",
       "      <td>vic1</td>\n",
       "      <td>12</td>\n",
       "      <td>371.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "      <td>2643.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>wind</td>\n",
       "      <td>wem</td>\n",
       "      <td>1</td>\n",
       "      <td>75.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "      <td>1005.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 312 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   fuel_tech network_region invalid_count invalid_capacity 1998-12-31  \\\n",
       "0    battery            _au            13           1137.0        0.0   \n",
       "1    battery           _nem            12           1037.0        0.0   \n",
       "2    battery           nsw1             6            346.0        0.0   \n",
       "3    battery           qld1             2            184.0        0.0   \n",
       "4    battery            sa1             2            300.0        0.0   \n",
       "..       ...            ...           ...              ...        ...   \n",
       "94      wind           qld1             3            348.0        0.0   \n",
       "95      wind            sa1             3            622.5        0.0   \n",
       "96      wind           tas1             0              0.0        0.0   \n",
       "97      wind           vic1            12            371.0        0.0   \n",
       "98      wind            wem             1             75.6        0.0   \n",
       "\n",
       "   1999-01-31 1999-02-28 1999-03-31 1999-04-30 1999-05-31  ... 2023-10-31  \\\n",
       "0         0.0        0.0        0.0        0.0        0.0  ...     803.27   \n",
       "1         0.0        0.0        0.0        0.0        0.0  ...     803.27   \n",
       "2         0.0        0.0        0.0        0.0        0.0  ...        0.0   \n",
       "3         0.0        0.0        0.0        0.0        0.0  ...      152.0   \n",
       "4         0.0        0.0        0.0        0.0        0.0  ...     211.27   \n",
       "..        ...        ...        ...        ...        ...  ...        ...   \n",
       "94        0.0        0.0        0.0        0.0        0.0  ...      675.2   \n",
       "95        0.0        0.0        0.0        0.0        0.0  ...     2140.9   \n",
       "96        0.0        0.0        0.0        0.0        0.0  ...      567.0   \n",
       "97        0.0        0.0        0.0        0.0        0.0  ...    2643.77   \n",
       "98        0.0        0.0        0.0        0.0        0.0  ...    1005.57   \n",
       "\n",
       "   2023-11-30 2023-12-31 2024-01-31 2024-02-29 2024-03-31 2024-04-30  \\\n",
       "0      803.27     803.27     803.27     803.27     803.27     803.27   \n",
       "1      803.27     803.27     803.27     803.27     803.27     803.27   \n",
       "2         0.0        0.0        0.0        0.0        0.0        0.0   \n",
       "3       152.0      152.0      152.0      152.0      152.0      152.0   \n",
       "4      211.27     211.27     211.27     211.27     211.27     211.27   \n",
       "..        ...        ...        ...        ...        ...        ...   \n",
       "94      675.2      675.2      675.2      675.2      675.2      675.2   \n",
       "95     2140.9     2140.9     2140.9     2140.9     2140.9     2140.9   \n",
       "96      567.0      567.0      567.0      567.0      567.0      567.0   \n",
       "97    2643.77    2643.77    2643.77    2643.77    2643.77    2643.77   \n",
       "98    1005.57    1005.57    1005.57    1005.57    1005.57    1005.57   \n",
       "\n",
       "   2024-05-31 2024-06-30 2024-07-31  \n",
       "0      803.27     803.27     803.27  \n",
       "1      803.27     803.27     803.27  \n",
       "2         0.0        0.0        0.0  \n",
       "3       152.0      152.0      152.0  \n",
       "4      211.27     211.27     211.27  \n",
       "..        ...        ...        ...  \n",
       "94      675.2      675.2      675.2  \n",
       "95     2140.9     2140.9     2140.9  \n",
       "96      567.0      567.0      567.0  \n",
       "97    2643.77    2643.77    2643.77  \n",
       "98    1005.57    1005.57    1005.57  \n",
       "\n",
       "[99 rows x 312 columns]"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# function that sums the data, grouping by fuel_tech, only where network_region is qld1, nsw1, vic1, tas1 and sa1\n",
    "# then, with the result, adds a network_region column = \"_all\"\n",
    "def create_summary(df, regions, summary_name):\n",
    "    # filter the DataFrame using the isin method\n",
    "    df_NEM = df[df['network_region'].isin(regions)]\n",
    "\n",
    "    summed = df_NEM.groupby(['fuel_tech']).sum().reset_index()\n",
    "    summed['network_region'] = summary_name\n",
    "    return summed\n",
    "\n",
    "\n",
    "# create a new table that concatenates nem_table and capacity_table\n",
    "def create_and_merge_summaries(capacity_table):\n",
    "    nem_table = create_summary(capacity_table, ['qld1', 'nsw1', 'vic1', 'tas1', 'sa1'], '_nem')\n",
    "    au_table = create_summary(capacity_table, ['qld1', 'nsw1', 'vic1', 'tas1', 'sa1', 'wem'], '_au')\n",
    "\n",
    "    # Ensure indexes are reset\n",
    "    nem_table.reset_index(drop=True, inplace=True)\n",
    "    capacity_table.reset_index(drop=True, inplace=True)\n",
    "    au_table.reset_index(drop=True, inplace=True)\n",
    "\n",
    "    # concatenate if columns are identical\n",
    "    if set(nem_table.columns) != set(capacity_table.columns) and set(nem_table.columns) != set(au_table.columns):\n",
    "        raise ValueError(\"DataFrames do not have identical columns and cannot be concatenated.\")\n",
    "\n",
    "    combined_table = pd.concat([nem_table, au_table, capacity_table], ignore_index=True)\n",
    "\n",
    "    # sort by fuel_tech then network_region\n",
    "    combined_table.sort_values(by=['fuel_tech', 'network_region'], inplace=True)\n",
    "    combined_table.reset_index(drop=True, inplace=True)\n",
    "    \n",
    "    return combined_table\n",
    "\n",
    "\n",
    "unified_monthly_table = create_and_merge_summaries(facilities_with_rooftop_table)\n",
    "unified_monthly_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create region files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: creating folder ./output\n",
      "INFO: writing capacity file for _au to ./output/capacity/au/monthly.json\n",
      "INFO: writing capacity file for _nem to ./output/capacity/au/NEM/monthly.json\n",
      "INFO: writing capacity file for nsw1 to ./output/capacity/au/NEM/NSW1/monthly.json\n",
      "INFO: writing capacity file for qld1 to ./output/capacity/au/NEM/QLD1/monthly.json\n",
      "INFO: writing capacity file for sa1 to ./output/capacity/au/NEM/SA1/monthly.json\n",
      "INFO: writing capacity file for vic1 to ./output/capacity/au/NEM/VIC1/monthly.json\n",
      "INFO: writing capacity file for wem to ./output/capacity/au/WEM/monthly.json\n",
      "INFO: writing capacity file for tas1 to ./output/capacity/au/NEM/TAS1/monthly.json\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "# put all the elements of the numerical array on the same line\n",
    "def compact_json(json_str):\n",
    "    # regex looks for a _numerical_ data array and captures everything in between [ and ]\n",
    "    pattern = r'(\"data\": \\[)([\\d\\., \\n]*)(\\])'\n",
    "    \n",
    "    # inner function is used to replace newlines and spaces between array items\n",
    "    def replacer(match):\n",
    "        start, middle, end = match.groups()\n",
    "        # Replace newline and possible spaces\n",
    "        compact_middle = middle.replace('\\n', '').replace(' ', '').replace(',', ', ')\n",
    "        return start + compact_middle + end\n",
    "    \n",
    "    return re.sub(pattern, replacer, json_str, flags=re.DOTALL)\n",
    "\n",
    "\n",
    "# pull out all the column names that are date objects as an array\n",
    "def get_date_columns(df):\n",
    "    # cols = [col for col in df.columns if isinstance(df[col][0], datetime)]\n",
    "    cols = [col for col in df.columns if pd.to_datetime(col, errors='coerce', format='%Y-%m-%d') is not pd.NaT]\n",
    "\n",
    "    # check that there's at least one date column\n",
    "    if len(cols) == 0:\n",
    "        df.info()\n",
    "        raise ValueError(\"ERROR: can't have no date columns!\")\n",
    "    \n",
    "    # check that all are in date order\n",
    "    if not all(earlier <= later for earlier, later in zip(cols, cols[1:])):\n",
    "        raise ValueError(\"ERROR: date columns are not in date order\")\n",
    "    \n",
    "    # we're either going to be looking at months or years\n",
    "    # if months, dates could be 28-31 days apart\n",
    "    # if years, dates could be 365-366 days apart\n",
    "    # so we'll look at the difference between max and min intervals, and it should be <= 3\n",
    "    deltas = [(date2 - date1).days for date1, date2 in zip(cols[:-1], cols[1:])]\n",
    "    range = max(deltas) - min(deltas)\n",
    "    if range > 3:\n",
    "        raise ValueError(\"ERROR: date columns appear to have a gap\")\n",
    "\n",
    "    return cols\n",
    "\n",
    "\n",
    "def generate_id(net_name, reg_name, fuel_tech):\n",
    "    id = 'au.'\n",
    "\n",
    "    if net_name:\n",
    "        id += net_name + '.'\n",
    "    \n",
    "    if reg_name:\n",
    "        id += reg_name + '.'\n",
    "\n",
    "    return id + f\"fuel_tech.{fuel_tech}.capacity\"\n",
    "\n",
    "\n",
    "# turn the region into a network and region name\n",
    "def get_network_and_region(region):\n",
    "    if region == '_au':\n",
    "        net_name = None\n",
    "        reg_name = None\n",
    "    elif region == 'wem':\n",
    "        net_name = 'wem'\n",
    "        reg_name = None\n",
    "    elif region == '_nem':\n",
    "        net_name = 'nem'\n",
    "        reg_name = None\n",
    "    else: # every NEM region\n",
    "        net_name = 'nem'\n",
    "        reg_name = region.lower()\n",
    "    \n",
    "    return net_name, reg_name\n",
    "\n",
    "\n",
    "def buildJSON(capacity_table, filter):\n",
    "    data = []\n",
    "\n",
    "    # extract date columns headings to an array\n",
    "    date_cols = get_date_columns(capacity_table)\n",
    "\n",
    "    # find the interval between dates\n",
    "    interval_days = (date_cols[1] - date_cols[0]).days\n",
    "    if (28 <= interval_days <= 31):\n",
    "        interval = \"1M\"\n",
    "    elif (365 <= interval_days <= 366):\n",
    "        interval = \"1Y\"\n",
    "    else:\n",
    "        raise ValueError(\"ERROR: interval between dates is not 1M or 1Y\")\n",
    "\n",
    "    start = f\"{date_cols[0]}\"\n",
    "    last = f\"{date_cols[-1]}\"\n",
    "   \n",
    "    for i, row in capacity_table.iterrows():\n",
    "        # change numbers to just 1 decimal place\n",
    "        capacities = [round(x, 1) for x in row[date_cols]]\n",
    "        \n",
    "        row_net_name, row_reg_name = get_network_and_region(row['network_region'])\n",
    "        row_fuel_tech = row['fuel_tech']\n",
    "        row_id = generate_id(row_net_name, row_reg_name, row_fuel_tech)\n",
    "\n",
    "        # TEMP make region uppercase for now\n",
    "        if row_reg_name:\n",
    "            row_reg_name = row_reg_name.upper()\n",
    "\n",
    "        # create the element\n",
    "        element = {\n",
    "            \"id\": row_id,\n",
    "            \"type\": 'capacity',\n",
    "            \"network\": row_net_name,\n",
    "            \"region\": row_reg_name,\n",
    "            \"fuel_tech\": row_fuel_tech,\n",
    "            \"units\": \"MW\",\n",
    "            \"invalid_count\": row['invalid_count'],\n",
    "            \"invalid_capacity\": round(row['invalid_capacity'], 1),\n",
    "            \"history\": {\n",
    "                \"start\": start,\n",
    "                \"last\": last,\n",
    "                \"interval\": interval,\n",
    "                \"data\": capacities\n",
    "            }\n",
    "        }\n",
    "\n",
    "        # if network or region is none, delete it\n",
    "        if row_net_name is None:\n",
    "            del element['network']\n",
    "\n",
    "        if row_reg_name is None:\n",
    "            del element['region'] \n",
    "            \n",
    "        data.append(element)\n",
    "\n",
    "    # build the header\n",
    "    head_net_name, head_reg_name = get_network_and_region(filter)\n",
    "\n",
    "    # TEMP make region uppercase for now\n",
    "    if head_reg_name:\n",
    "        head_reg_name = head_reg_name.upper()\n",
    "\n",
    "    output_obj = {\n",
    "        \"version\": \"4.2\",\n",
    "        \"network\": head_net_name,\n",
    "        \"region\": head_reg_name,\n",
    "        \"created_at\": datetime.now(TIME_ZONE).strftime(\"%Y-%m-%dT%H:%M:%S%z\"),\n",
    "        \"messages\": [ \"historical capacity derived from facilities data and APVI APIs\" ],\n",
    "        \"data\": data,\n",
    "    }\n",
    "\n",
    "    # if network or region is none or '*', delete it\n",
    "    if head_net_name is None:\n",
    "        del output_obj['network']\n",
    "\n",
    "    if head_reg_name is None:\n",
    "        del output_obj['region'] \n",
    "\n",
    "    json_output = json.dumps(output_obj, indent=2)\n",
    "    return compact_json(json_output)\n",
    "\n",
    "\n",
    "def get_tree_folder(base_folder,region):\n",
    "    if region == '_au':\n",
    "        net_name = None\n",
    "        reg_name = None\n",
    "    elif region == 'wem':\n",
    "        net_name = 'WEM'\n",
    "        reg_name = None\n",
    "    elif region == '_nem':\n",
    "        net_name = 'NEM'\n",
    "        reg_name = None\n",
    "    else: # every NEM region\n",
    "        net_name = 'NEM'\n",
    "        reg_name = region.upper()\n",
    "\n",
    "    path = os.path.join(base_folder, CAPACITY_TREE_FOLDER, \"au\")\n",
    "\n",
    "    if net_name:\n",
    "        path = os.path.join(path, net_name)\n",
    "\n",
    "    if reg_name:\n",
    "        path = os.path.join(path, reg_name)\n",
    "\n",
    "    return path\n",
    "    \n",
    "\n",
    "# create region file\n",
    "def write_region_file(capacity_table, region, file_path):\n",
    "    print(f\"INFO: writing capacity file for {region} to {file_path}\")\n",
    "    json_output = buildJSON(capacity_table, region)\n",
    "\n",
    "    with open(file_path, \"w\") as f:\n",
    "        f.write(json_output)\n",
    "\n",
    "\n",
    "# for each region in combined_table, call a function with all rows in that region\n",
    "def write_region_files(frame, output_dir, file_name):\n",
    "    print(f\"INFO: creating folder {output_dir}\")\n",
    "    os.makedirs(output_dir, exist_ok=True)\n",
    "\n",
    "    # get the list of regions from combined_table\n",
    "    regions = unified_monthly_table['network_region'].unique()\n",
    "\n",
    "    # iterate through each region\n",
    "    for region in regions:\n",
    "        folder = get_tree_folder(output_dir, region)\n",
    "        os.makedirs(folder, exist_ok=True)\n",
    "\n",
    "        output_file_path = os.path.join(folder, file_name)\n",
    "\n",
    "        # filter the combined_table to only include rows with the current region\n",
    "        region_table = frame[frame['network_region'] == region]\n",
    "        write_region_file(region_table, region, output_file_path)\n",
    "\n",
    "\n",
    "write_region_files(unified_monthly_table, OUTPUT_FOLDER, 'monthly.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: creating folder ./output\n",
      "INFO: writing capacity file for _au to ./output/capacity/au/annual.json\n",
      "INFO: writing capacity file for _nem to ./output/capacity/au/NEM/annual.json\n",
      "INFO: writing capacity file for nsw1 to ./output/capacity/au/NEM/NSW1/annual.json\n",
      "INFO: writing capacity file for qld1 to ./output/capacity/au/NEM/QLD1/annual.json\n",
      "INFO: writing capacity file for sa1 to ./output/capacity/au/NEM/SA1/annual.json\n",
      "INFO: writing capacity file for vic1 to ./output/capacity/au/NEM/VIC1/annual.json\n",
      "INFO: writing capacity file for wem to ./output/capacity/au/WEM/annual.json\n",
      "INFO: writing capacity file for tas1 to ./output/capacity/au/NEM/TAS1/annual.json\n"
     ]
    }
   ],
   "source": [
    "def get_annual_capacity_table(df):\n",
    "    my_dates = get_date_columns(unified_monthly_table)\n",
    "\n",
    "    # filter to just 30 june\n",
    "    june_dates = [date for date in my_dates if date.month == 6 and date.day == 30]\n",
    "\n",
    "    # copy unified_table, just the columns fuel_tech, network_region, invalid_count, invalid_capacity and june_dates\n",
    "    annual_table = unified_monthly_table[['fuel_tech', 'network_region', 'invalid_count', 'invalid_capacity', *june_dates]].copy()\n",
    "\n",
    "    new_dates = get_date_columns(annual_table)\n",
    "\n",
    "    # the capacity at the end of june is the capacity at the beginning of july\n",
    "    new_column_names = {date: date + timedelta(days=1) for date in new_dates}\n",
    "    annual_table.rename(columns=new_column_names, inplace=True)\n",
    "\n",
    "    return annual_table\n",
    "\n",
    "\n",
    "unified_annual_table = get_annual_capacity_table(unified_monthly_table)\n",
    "write_region_files(unified_annual_table, OUTPUT_FOLDER, 'annual.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: writing capacity file for _au to ./output/unified_annual_capacity.json\n"
     ]
    }
   ],
   "source": [
    "# write out an annual capacity table for all regions at once\n",
    "write_region_file(unified_annual_table, '_au', os.path.join(OUTPUT_FOLDER, 'unified_annual_capacity.json'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}