ispparser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 224,
   "id": "9a69f3ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import openpyxl\n",
    "import os\n",
    "import re\n",
    "import timeit\n",
    "import numpy as np\n",
    "import json\n",
    "import pytz\n",
    "import zipfile\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c121de4a",
   "metadata": {},
   "source": [
    "## constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "9a0158c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX_FILES_TO_PROCESS = 99\n",
    "\n",
    "INPUT_FOLDER = \"./input\"\n",
    "OUTPUT_FOLDER = \"./output\"\n",
    "\n",
    "CACHE_FOLDER = \"cache\"\n",
    "OUTLOOKS_FOLDER = \"outlooks\"\n",
    "\n",
    "TIME_ZONE = pytz.timezone(\"Australia/Sydney\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b070f39",
   "metadata": {},
   "source": [
    "## fueltech mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "463c2819",
   "metadata": {},
   "outputs": [],
   "source": [
    "FUELTECH_MAPPINGS = {\n",
    "    \"Brown Coal\": \"coal_brown\",\n",
    "    \"Black Coal\": \"coal_black\",\n",
    "    \"Solar Thermal\": \"solar_thermal\",\n",
    "    \"Utility-scale Solar\": \"solar_utility\",\n",
    "    \"Imports\": \"imports\",\n",
    "    \"Exports\": \"exports\",\n",
    "    \"Distributed PV\": \"solar_rooftop\",\n",
    "    \"Wind\": \"wind\",\n",
    "    \"Hydro\": \"hydro\",\n",
    "    \"Distributed Storage\": \"battery_distributed_discharging\",\n",
    "    \"Distributed Storage Load\": \"battery_distributed_charging\",\n",
    "    \"Mid-merit Gas\": \"gas_ccgt\",\n",
    "    \"Mid-merit Gas with CCS\": \"gas_ccgt_ccs\",\n",
    "    \"Offshore Wind\": \"wind_offshore\",\n",
    "    \"Peaking Gas\\+Liquids\": \"gas_ocgt\",\n",
    "    \"Hydrogen Turbine\": \"gas_hydrogen\",\n",
    "    \"Utility-scale Storage\": \"battery_discharging\",\n",
    "    \"Utility-scale Storage Load\": \"battery_charging\",\n",
    "    \"Offshore wind\": \"wind_offshore\",\n",
    "    \"Coordinated DER Storage\": \"battery_VPP_discharging\",\n",
    "    \"Coordinated DER Storage Load\": \"battery_VPP_charging\",\n",
    "    \"DSP\": \"demand_response\",\n",
    "\n",
    "    # 2024 draft\n",
    "    \"Flexible Gas\": \"gas_ocgt\",\n",
    "    \"Passive CER Storage\": \"battery_distributed_discharging\",\n",
    "    \"Passive CER Storage Load\": \"battery_distributed_charging\",\n",
    "    \"Coordinated CER Storage\": \"battery_VPP_discharging\",\n",
    "    \"Coordinated CER Storage Load\": \"battery_VPP_charging\",\n",
    "    \"Biomass\": \"bioenergy_biomass\",\n",
    "\n",
    "    # 2024 final\n",
    "    \"Brown coal\": \"coal_brown\",\n",
    "    \"Black coal\": \"coal_black\",\n",
    "    \"Mid-merit gas\": \"gas_ccgt\",\n",
    "    \"Flexible gas with CCS\": \"gas_ccgt_ccs\",\n",
    "    \"Flexible gas\": \"gas_ocgt\",\n",
    "\n",
    "    \"Utility storage\": \"battery_discharging\",\n",
    "    \"Utility storage load\": \"battery_charging\",\n",
    "    \"Coordinated CER storage\": \"battery_VPP_discharging\",\n",
    "    \"Coordinated CER storage load\": \"battery_VPP_charging\",\n",
    "    \"Passive CER storage\": \"battery_distributed_discharging\",\n",
    "    \"Passive CER storage load\": \"battery_distributed_charging\",\n",
    "\n",
    "    \"Utility solar\": \"solar_utility\",\n",
    "    \"Other renewable fuels\": \"bioenergy\",\n",
    "}\n",
    "\n",
    "COST_MAPPINGS = {\n",
    "    # 2022\n",
    "    \"Generator Capital\": \"generator_capital\",\n",
    "    \"REZ Augmentation\": \"rez_augmentation\",\n",
    "    \"Flow Path Augmentation\": \"flow_path_augmentation\",\n",
    "\n",
    "    # 2024\n",
    "    \"Generator capital\": \"generator_capital\",\n",
    "    \"FOM\": \"fixed_operating_and_maintenance\",\n",
    "    \"Fuel\": \"fuel\",\n",
    "    \"VOM\": \"variable_operating_and_maintenance\",\n",
    "    \"DSP\\+USE\": \"dsp_use\",\n",
    "    \"REZ augmentation\": \"rez_augmentation\",\n",
    "    \"Flow path augmentation\": \"flow_path_augmentation\",\n",
    "    \"Emissions cost\": \"emissions_cost\",\n",
    "}\n",
    "\n",
    "# Reverse the FUEL_TECH_MAPPINGS dictionary\n",
    "FUELTECH_TO_DESCRIPTION = {v: k for k, v in FUELTECH_MAPPINGS.items()}\n",
    "\n",
    "def get_fuelTechDescription(fuel_tech_id):\n",
    "    # Use the reversed dictionary to get the readable string\n",
    "    return FUELTECH_TO_DESCRIPTION.get(fuel_tech_id, fuel_tech_id)\n",
    "\n",
    "FUELTECH_COLORS = {\n",
    "    'battery_charging': '#4F5FD7',\n",
    "    'battery_discharging': '#3145CE',\n",
    "    'battery_VPP_charging': '#4F5FD7',\n",
    "    'battery_VPP_discharging': '#3145CE',\n",
    "    'battery_distributed_charging': '#4F5FD7',\n",
    "    'battery_distributed_discharging': '#3145CE',\n",
    "    'battery': '#3145CE',\n",
    "\n",
    "    'bioenergy': '#069FAF',\n",
    "    'bioenergy_biogas': '#069FAF',\n",
    "    'bioenergy_biomass': '#0B757C',\n",
    "\n",
    "    'coal': '#251C00',\n",
    "    'coal_black': '#251C00',\n",
    "    'coal_brown': '#675B42',\n",
    "\n",
    "    'distillate': '#E46E56',\n",
    "\n",
    "    'gas': '#E78114',\n",
    "    'gas_ccgt': '#ED9C2C',\n",
    "    'gas_ccgt_ccs': '#F1AB4B',\n",
    "    'gas_ocgt': '#F0AC4A',\n",
    "    'gas_recip': '#F4C379',\n",
    "    'gas_steam': '#E78114',\n",
    "    'gas_wcmg': '#DA630E',\n",
    "    'gas_hydrogen': '#C75338',\n",
    "\n",
    "    'hydro': '#ACE9FE',\n",
    "    'pumps': '#00A5F1',\n",
    "\n",
    "    'solar': '#FECE00',\n",
    "    'solar_utility': '#FECE00',\n",
    "    'solar_thermal': '#FDB200',\n",
    "    'solar_rooftop': '#FFEB5C',\n",
    "\n",
    "    'wind': '#246D36',\n",
    "    'wind_offshore': '#53AD69',\n",
    "\n",
    "    'nuclear': '#C75338',\n",
    "\n",
    "    'imports': '#CFA7FF',\n",
    "    'exports': '#722AF7',\n",
    "    'interconnector': '#7F7F7F',\n",
    "    'demand_response': '#7F7F7F',\n",
    "\n",
    "    'fossil_fuels': '#594929',\n",
    "    'renewables': '#52A972'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "279a725c",
   "metadata": {},
   "source": [
    "## utilities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3345146",
   "metadata": {},
   "source": [
    "### unhide sheets\n",
    "\n",
    "For some reason, AEMO likes to keep most of the interesting sheets in the outlook workbooks hidden. Point this cell at a directory and it'll unhide all the hidden sheets in all the workbooks within.\n",
    "\n",
    "The unlocked file is written out to the root director of the project. You'll then need to open the workbook in Excel, let it repair the file and save it as a new file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "7b5053c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def unhide_sheets(directory):\n",
    "    for filename in os.listdir(directory):\n",
    "        if filename.endswith(\".xlsx\"):\n",
    "            print(\"\\nchecking\", filename)\n",
    "            workbook = openpyxl.load_workbook(os.path.join(directory, filename), keep_links=False)\n",
    "            print(\"opened\", filename)\n",
    "    \n",
    "            for sheet in workbook.sheetnames:\n",
    "                print(\"checking\", sheet)\n",
    "                if workbook[sheet].sheet_state == 'hidden':\n",
    "                    workbook[sheet].sheet_state = 'visible'\n",
    "            workbook.save(filename)\n",
    "\n",
    "# unhide_sheets(\"data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c49adf6f",
   "metadata": {},
   "source": [
    "### print utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "id": "1f5018d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def countEntriesByTechnology(data_frame, type):\n",
    "    print(\"\\nCount of type:\", type)\n",
    "    filtered_data_frame = data_frame[data_frame['Type'] == type]\n",
    "    count_by_technology = filtered_data_frame.pivot_table(index='Technology', columns='CDP', values='Scenario', aggfunc='count', fill_value=0)\n",
    "    print(count_by_technology)\n",
    "\n",
    "def printDataSummary(data, startTime):\n",
    "        countEntriesByTechnology(data, \"energy\")\n",
    "        countEntriesByTechnology(data, \"capacity\")\n",
    "        elapsed = timeit.default_timer() - startTime\n",
    "        print(f\"rows: {len(data)} processed in {elapsed:.1f} seconds\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79f343ea",
   "metadata": {},
   "source": [
    "## loading from outlooks workbook"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e54516a",
   "metadata": {},
   "source": [
    "### checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "c06b4add",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getYearsFromColumnNames(frame):\n",
    "    # for col in frame.columns:\n",
    "    #     print(f\">>> col '{col}': {type(col)}\")\n",
    "\n",
    "    # print(frame.columns)\n",
    "    # frame.info()\n",
    "    \n",
    "    years = [col for col in frame.columns if isinstance(col, int)]\n",
    "\n",
    "    # print(f\">>> years: {years}\")\n",
    "    years.sort()\n",
    "    # print(f\">>> years: {years}\")\n",
    "\n",
    "    if (len(years) == 0):\n",
    "        raise ValueError(\"ERROR: no columns with years found!\")\n",
    "    \n",
    "    return years\n",
    "\n",
    "# check that all technology types in the dataframe are in alphabetical allowing underscrores\n",
    "# fail hard if they're not\n",
    "def checkTechnologyTypesAreLegal(combined):\n",
    "    technology_types = combined['Technology'].unique()\n",
    "\n",
    "    illegals = [tech for tech in technology_types if not tech.replace('_', '').isalpha()]\n",
    "    if illegals:\n",
    "        raise ValueError(f'ERROR: the following technology types are not mapped properly: {illegals}')\n",
    "    print(\"INFO: all technology types appear to be mapped properly\")\n",
    "\n",
    "\n",
    "# get a list of all columns with a name that looks like a year, make sure there are at least 20 and they are sequential\n",
    "def checkYearColumns(combined):\n",
    "    years = getYearsFromColumnNames(combined)\n",
    "\n",
    "    if len(years) <= 20 or not all(y2 == y1 + 1 for y1, y2 in zip(years, years[1:])):\n",
    "        raise ValueError(\"ERROR: year columns should sequential and in format XXXX (eg. not XXXX-YY).\")\n",
    "    print(f\"INFO: years are sequential from {years[0]} to {years[-1]}\")\n",
    "\n",
    "\n",
    "def checkFuelTechValuesLegal(combined):\n",
    "    # get the list of all columns that look like a year\n",
    "    years = getYearsFromColumnNames(combined)\n",
    "\n",
    "    # copy only the rows where any year column has a value less than 0 or NaN\n",
    "    failed_rows = combined[(combined[years] < 0).any(axis=1) | combined[years].isna().any(axis=1)]\n",
    "\n",
    "    if not failed_rows.empty:\n",
    "        # print the number of failed rows\n",
    "        print(f\"ERROR: {len(failed_rows)} of {len(combined)} rows have illegal values\")\n",
    "        print(failed_rows.head())\n",
    "\n",
    "        # build a list of failed tech types\n",
    "        failed_tech_types = failed_rows['Technology'].unique()\n",
    "        raise ValueError(f\"ERROR: the following technology types have illegal values: {failed_tech_types}\")\n",
    "    \n",
    "    print(\"INFO: all technology types have valid values\")\n",
    "\n",
    "\n",
    "def buildRegionLists(df):\n",
    "    print(\"INFO: checking to see that all regions are represented and that no illegal regions are present\")\n",
    "\n",
    "    # Ensure the 'region' column exists in the dataframe\n",
    "    if 'Region' not in df.columns:\n",
    "        raise ValueError(\"ERROR: frame does not have a 'Region' column\")\n",
    "    \n",
    "    # Define the valid regions\n",
    "    REGIONS = ['_all', 'qld1', 'nsw1', 'vic1', 'tas1', 'sa1']\n",
    "\n",
    "    # group by 'Region' and count the rows\n",
    "    region_counts = df['Region'].value_counts().reset_index()\n",
    "    region_counts.columns = ['Region', 'count']\n",
    "    region_list = list(region_counts.itertuples(index=False, name=None))\n",
    "\n",
    "    # initialise lists\n",
    "    good_regions = []\n",
    "    bad_regions = []\n",
    "    missing_regions = []\n",
    "\n",
    "    # populate good_regions and bad_regions\n",
    "    for region, count in region_list:\n",
    "        if region in REGIONS:\n",
    "            good_regions.append((region, count))\n",
    "        else:\n",
    "            bad_regions.append((region, count))\n",
    "\n",
    "    # populate missing_regions\n",
    "    existing_regions = set([region for region, count in region_list])\n",
    "    missing_regions = [region for region in REGIONS if region not in existing_regions]\n",
    "\n",
    "    return good_regions, bad_regions, missing_regions\n",
    "\n",
    "\n",
    "def checkRegions(combined):\n",
    "    good_regions, bad_regions, missing_regions = buildRegionLists(combined)\n",
    "\n",
    "    print(f\"INFO: got good regions: {good_regions}\")\n",
    "\n",
    "    if (len(missing_regions) > 0):\n",
    "        raise ValueError(f\"ERROR: missing regions: {missing_regions}\")\n",
    "    else:\n",
    "        print(\"INFO: all regions are present\")\n",
    "\n",
    "    if (len(bad_regions) > 0):\n",
    "        raise ValueError(f\"ERROR: illegal region names: {bad_regions}\")\n",
    "\n",
    "\n",
    "def runIntegrityChecks(outlooks):\n",
    "    checkRegions(outlooks)\n",
    "    checkTechnologyTypesAreLegal(outlooks)\n",
    "    checkYearColumns(outlooks)\n",
    "    checkFuelTechValuesLegal(outlooks)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1397bf9d",
   "metadata": {},
   "source": [
    "#### unit-tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "id": "00d7061b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "test_buildRegionLists_bad (__main__.TestRegionFunctions) ... ok\n",
      "test_buildRegionLists_good (__main__.TestRegionFunctions) ... ok\n",
      "test_buildRegionLists_missing (__main__.TestRegionFunctions) ... ok\n",
      "test_checkRegions_all_good (__main__.TestRegionFunctions) ... ok\n",
      "test_checkRegions_bad_regions (__main__.TestRegionFunctions) ... ok\n",
      "test_checkRegions_missing_regions (__main__.TestRegionFunctions) ... "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: got good regions: [('_all', 1), ('nsw1', 1), ('sa1', 1), ('vic1', 1), ('tas1', 1), ('qld1', 1)]\n",
      "INFO: all regions are present\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: got good regions: [('_all', 1), ('nsw1', 1), ('sa1', 1), ('vic1', 1), ('tas1', 1), ('qld1', 1)]\n",
      "INFO: all regions are present\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: got good regions: [('_all', 1), ('nsw1', 1), ('sa1', 1)]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ok\n",
      "\n",
      "----------------------------------------------------------------------\n",
      "Ran 6 tests in 0.013s\n",
      "\n",
      "OK\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<unittest.main.TestProgram at 0x16f7fdfd0>"
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import unittest\n",
    "\n",
    "# Define the unit tests\n",
    "class TestRegionFunctions(unittest.TestCase):\n",
    "    def setUp(self):\n",
    "        # Build the dataframes for testing\n",
    "        self.good_df = pd.DataFrame({\n",
    "            'Year': ['2020', '2021', '2022', '2023', '2024', '2025'],\n",
    "            'Region': [\"_all\", \"nsw1\", \"sa1\", 'vic1', 'tas1', 'qld1']\n",
    "        })\n",
    "\n",
    "        self.bad_df = pd.DataFrame({\n",
    "            'Year': ['2020', '2021', '2022', '2023', '2024', '2025', '2026', '2027'],\n",
    "            'Region': [\"_all\", \"nsw1\", \"sa1\", 'vic1', 'tas1', 'qld1', 'NSW1', 'foo']\n",
    "        })\n",
    "        \n",
    "        self.missing_df = pd.DataFrame({\n",
    "            'Year': ['2020', '2021', '2022'],\n",
    "            'Region': [\"_all\", \"nsw1\", \"sa1\"]\n",
    "        })\n",
    "\n",
    "    def test_buildRegionLists_good(self):\n",
    "        # print(\"\\ntest_buildRegionLists_good\")\n",
    "        # print(self.good_df)\n",
    "\n",
    "        good, bad, missing = buildRegionLists(self.good_df)\n",
    "        self.assertEqual(sorted(good), sorted([('_all', 1), ('nsw1', 1), ('sa1', 1), ('vic1', 1), ('tas1', 1), ('qld1', 1)]))\n",
    "        self.assertEqual(bad, [])\n",
    "        self.assertEqual(missing, [])\n",
    "\n",
    "    def test_buildRegionLists_bad(self):\n",
    "        # print(\"\\ntest_buildRegionLists_bad\")\n",
    "        # print(self.bad_df)\n",
    "    \n",
    "        good, bad, missing = buildRegionLists(self.bad_df)\n",
    "        self.assertEqual(sorted(good), sorted([('_all', 1), ('nsw1', 1), ('sa1', 1), ('vic1', 1), ('tas1', 1), ('qld1', 1)]))\n",
    "        self.assertEqual(sorted(bad), sorted([('foo', 1), ('NSW1', 1)]))\n",
    "        self.assertEqual(missing, [])\n",
    "\n",
    "    def test_buildRegionLists_missing(self):\n",
    "        # print(\"\\ntest_buildRegionLists_missing\")\n",
    "        # print(self.missing_df)\n",
    "\n",
    "        good, bad, missing = buildRegionLists(self.missing_df)\n",
    "        self.assertEqual(sorted(good), sorted([('_all', 1), ('nsw1', 1), ('sa1', 1)]))\n",
    "        self.assertEqual(bad, [])\n",
    "        self.assertEqual(sorted(missing), sorted(['qld1', 'vic1', 'tas1']))\n",
    "    \n",
    "    def test_checkRegions_all_good(self):\n",
    "        # print(\"\\ntest_checkRegions_all_good\")\n",
    "        # print(self.good_df)\n",
    "    \n",
    "        try:\n",
    "            checkRegions(self.good_df)\n",
    "        except ValueError as e:\n",
    "            self.fail(f\"checkRegions raised ValueError unexpectedly: {e}\")\n",
    "\n",
    "    def test_checkRegions_bad_regions(self):\n",
    "        # print(\"\\ntest_checkRegions_bad_regions\")\n",
    "        # print(self.bad_df)\n",
    "\n",
    "        with self.assertRaises(ValueError) as context:\n",
    "            checkRegions(self.bad_df)\n",
    "        self.assertIn(\"ERROR: illegal region names:\", str(context.exception))\n",
    "\n",
    "    def test_checkRegions_missing_regions(self):\n",
    "        # print(\"\\ntest_checkRegions_missing_regions\")\n",
    "        # print(self.missing_df)\n",
    "\n",
    "        with self.assertRaises(ValueError) as context:\n",
    "            checkRegions(self.missing_df)\n",
    "        self.assertIn(\"ERROR: missing regions:\", str(context.exception))\n",
    "\n",
    "\n",
    "# Run the tests\n",
    "unittest.main(argv=[''], verbosity=2, exit=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6ed2b1a",
   "metadata": {},
   "source": [
    "### fixes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "3085fb4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def renameRegions(frame):\n",
    "    # if the region is 'nem', change it to '_all', otherwise shift to lower case and append  '1', eg 'nsw1'\n",
    "    frame['Region'] = frame['Region'].apply(lambda x: '_all' if x.lower() == \"nem\" else x.lower() + \"1\")\n",
    "    return frame\n",
    "  \n",
    "\n",
    "def renameTechnologyLabels(frame):  \n",
    "    for old_label, new_label in FUELTECH_MAPPINGS.items():\n",
    "        frame['Technology'] = frame['Technology'].replace(r'^{}$'.format(old_label), new_label, regex=True) \n",
    "    return frame\n",
    "\n",
    "\n",
    "def renameCostLabels(frame):  \n",
    "    for old_label, new_label in COST_MAPPINGS.items():\n",
    "        frame['Technology'] = frame['Technology'].replace(r'^{}$'.format(old_label), new_label, regex=True) \n",
    "    return frame\n",
    "\n",
    "\n",
    "# rename financial year coluumns\n",
    "# take a data frame, and for each column with a title in the form of '20xx-yy', rename it to 20yy (an integer)\n",
    "def renameYearColumnsFromStringToInteger(frame):\n",
    "    count = 0\n",
    "\n",
    "    for col in frame.columns:\n",
    "        if isinstance(col, str) and re.match(r\"^\\d{4}$\", col):\n",
    "            frame.rename(columns={col: int(col)}, inplace=True)\n",
    "            count += 1\n",
    "\n",
    "    if count:\n",
    "        print(f\"INFO: renamed {count} year columns from string to integer\")\n",
    "\n",
    "\n",
    "# rename financial year coluumns\n",
    "# take a data frame, and for each column with a title in the form of '20xx-yy', rename it to 20yy (an integer)\n",
    "def renameFinancialYearColumns(frame):\n",
    "    print(\"INFO: renaming financial year columns…\")\n",
    "    count = 0\n",
    "\n",
    "    for col in frame.columns:\n",
    "        if isinstance(col, str) and re.match(r\"^\\d{4}-\\d{2}$\", col):\n",
    "            new_col_name = int(\"20\" + col[5:])\n",
    "            frame.rename(columns={col: new_col_name}, inplace=True)\n",
    "            count += 1\n",
    "\n",
    "    if count:\n",
    "        print(f\"INFO: renamed {count} financial years\")\n",
    "\n",
    "\n",
    "def makeSpecialTechsPositive(frame):\n",
    "    # print(\"INFO: flipping negative values\")\n",
    "\n",
    "    # get the list of all columns that look like a year\n",
    "    years = getYearsFromColumnNames(frame)\n",
    "\n",
    "    flip_types = [\n",
    "        \"battery_charging\",\n",
    "        \"battery_VPP_charging\",\n",
    "        \"battery_distributed_charging\",\n",
    "        \"exports\",\n",
    "    ]\n",
    "\n",
    "    for tech_type in flip_types:\n",
    "        # pull out the rows with column Technology = tech_type\n",
    "        tech_rows = frame[frame[\"Technology\"] == tech_type]\n",
    "        \n",
    "        # if any of the years are negative for the given tech_type\n",
    "        num_negative = tech_rows[(tech_rows[years] < 0).any(axis=1)].shape[0]\n",
    "        \n",
    "        if num_negative:\n",
    "            # …make them positive\n",
    "            print(f\"INFO: {num_negative} rows with negative values found for '{tech_type}', making them positive\")\n",
    "            frame.loc[frame['Technology'] == tech_type, years] = frame.loc[frame['Technology'] == tech_type, years].abs()\n",
    "\n",
    "\n",
    "def changeNumericColumnsToFloats(frame):\n",
    "    # for all columns with a year header (format yyyy), chnage the type to a float\n",
    "    for col in getYearsFromColumnNames(frame):\n",
    "        frame[col] = frame[col].astype(float)\n",
    "\n",
    "\n",
    "def multiplyBy1e3(frame):\n",
    "    # for all columns with a year header, multiply values by 1e3\n",
    "    for col in getYearsFromColumnNames(frame):\n",
    "        frame[col] = frame[col] * 1e3\n",
    "\n",
    "\n",
    "def collapseSubregions(frame):\n",
    "    # check whether the dataframe has a column called Subregion\n",
    "    if 'Subregion' not in frame.columns:\n",
    "        # print(\"INFO: subregion column not found, no collapsing needed\")\n",
    "        return frame\n",
    "    \n",
    "    print(\"INFO: subregion column found, collapsing\")\n",
    "    before_rows = frame.shape[0]\n",
    "\n",
    "    # make a new dataframe grouping by CDP, Region, Technology\n",
    "    collapsed = frame.groupby(['CDP', 'Region', 'Technology']).sum().reset_index()\n",
    "\n",
    "    # drop the Subregion column\n",
    "    collapsed = collapsed.drop(columns=['Subregion'])\n",
    "\n",
    "    after_rows = collapsed.shape[0]\n",
    "\n",
    "    print(f\"INFO: collapsing subregions went from {before_rows} to {after_rows} rows\")\n",
    "    return collapsed\n",
    "\n",
    "\n",
    "def addSummaryRegion(df):\n",
    "    # create an _all region, being the sum of all regions within each scenario, CDP, type and technology\n",
    "    nem_region = df.groupby([\"Scenario\", \"CDP\", \"Type\", \"Technology\"]).sum().reset_index()\n",
    "    nem_region[\"Region\"] = \"_all\"\n",
    "\n",
    "    # exclude entries where Technology is 'imports' or 'exports'\n",
    "    trim_nem = nem_region[~nem_region['Technology'].isin(['imports', 'exports'])]\n",
    "\n",
    "    # exclude entries where type is 'cost'\n",
    "    trim_nem = trim_nem[~trim_nem['Type'].isin(['cost'])]\n",
    "\n",
    "    return pd.concat([df, trim_nem], ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4242aea9",
   "metadata": {},
   "source": [
    "### test flip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "565fbcb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# generate some demo data to feed into flipNegativeValues\n",
    "def getDemoData():\n",
    "    data = {\n",
    "        'Technology': ['battery_charging', 'exports', 'solar_utility'],\n",
    "        '2019': [\"-10.5\", \"-20.5\", \"-30.4\"],\n",
    "        '2020': [\"-5\", \"-15\", \"-25.1\"],\n",
    "        '2021': [\"-7\", \"-17\", \"-27.5\",]\n",
    "    }\n",
    "    return pd.DataFrame(data)\n",
    "\n",
    "def testFlip():\n",
    "    # Generate demo data\n",
    "    demo = getDemoData()\n",
    "    demo.info()\n",
    "    print(demo)\n",
    "    changeNumericColumnsToFloats(demo)\n",
    "    demo.info()\n",
    "    print(demo)\n",
    "    makeSpecialTechsPositive(demo)\n",
    "    print(demo)\n",
    "\n",
    "# testFlip()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22c166b8",
   "metadata": {},
   "source": [
    "### loading and caching"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "0005d96e",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def loadISPDataFromSheet(excel_file, sheetname):\n",
    "    sheet = excel_file[sheetname]\n",
    "    data = pd.DataFrame(sheet.values)\n",
    "    \n",
    "    # grab the column names from the 3rd row, using integers for the year headings\n",
    "    data.columns = data.iloc[2].apply(lambda x: int(float(x)) if isinstance(x, float) else x)\n",
    "\n",
    "    # remove blank rows\n",
    "    data.drop([0, 1, 2], inplace=True)  \n",
    "    data.dropna(how=\"all\", inplace=True)\n",
    "\n",
    "    return data\n",
    "\n",
    "\n",
    "def check_dataframe_compatibility(dataframes):\n",
    "    \"\"\"\n",
    "    Check if the columns of the DataFrames.\n",
    "    Prints debugging information if the columns are not identical.\n",
    "    \n",
    "    :param dataframes: A list of DataFrames to compare with each other.\n",
    "    :raises ValueError: If the columns are not identical.\n",
    "    \"\"\"\n",
    "    print(\"INFO: checking that dataframes are all compatible\")\n",
    "\n",
    "    def print_column_details(frame, name):\n",
    "        print(f\"{name} columns and types:\")\n",
    "        for col in frame.columns:\n",
    "            print(f\"'{col}' ({type(col)})\")\n",
    "    \n",
    "    ref_frame, ref_name = dataframes[0]\n",
    "    for test_frame, test_name in dataframes:\n",
    "        if set(ref_frame.columns) != set(test_frame.columns):\n",
    "            print(f\"\\nERROR: The column names of {ref_name} and {test_name} are not identical.\")\n",
    "\n",
    "            print(f\"{ref_name} columns:\", list(ref_frame.columns))\n",
    "            print_column_details(ref_frame, ref_name)\n",
    "            \n",
    "            print(f\"{test_name} columns:\", list(test_frame.columns))\n",
    "            print_column_details(test_frame, test_name)\n",
    "            \n",
    "            raise ValueError(f\"ERROR: The column names of {ref_name} and {test_frame.name} are not identical.\")\n",
    "\n",
    "\n",
    "def getWorkbookData(release_id, file_name, label):\n",
    "    workbook_path = os.path.join(INPUT_FOLDER, release_id, file_name)\n",
    "    print(f\"\\nloading release '{release_id}', scenario '{label}' from '{workbook_path}'\")\n",
    "\n",
    "    excel_file = openpyxl.load_workbook(workbook_path)\n",
    "  \n",
    "    # grab generation, using it as the basic structure\n",
    "    print(\"INFO: loading generation\")\n",
    "    generation = loadISPDataFromSheet(excel_file, 'Generation')\n",
    "\n",
    "    # grab the flows\n",
    "    print(\"INFO: loading imports and exports\")\n",
    "    flows = loadISPDataFromSheet(excel_file, 'Imports and Exports')\n",
    "    \n",
    "    if 'Flow' in flows.columns:  # ensure that 'Flow' column exists\n",
    "        flows = flows[flows['Flow'].isin(['Imports', 'Exports'])]\n",
    "        flows = flows.rename(columns={\"Flow\": \"Technology\"})\n",
    "      \n",
    "        # flip the sign of numbers in the row for exports\n",
    "        flows.loc[flows['Technology'] == \"Exports\", generation.select_dtypes(include=[np.number]).columns] *= -1\n",
    "    else:\n",
    "        print(\"Column names of flows:\", list(flows.columns))\n",
    "        raise ValueError(\"ERROR: the Flows column is missing\")\n",
    "      \n",
    "    # grab the capacities \n",
    "    print(\"INFO: loading capacity\")\n",
    "    capacities = loadISPDataFromSheet(excel_file, 'Capacity')\n",
    "\n",
    "    # grab the emissions\n",
    "    print(\"INFO: loading emissions\")\n",
    "    emissions = loadISPDataFromSheet(excel_file, 'Emissions')\n",
    "    emissions = emissions.drop(columns=['Total'])               # remove the Total column\n",
    "    emissions.insert(2, 'Technology', 'none')                   # set the Technology column to \"none\" \n",
    "    # print(\"Emissions columns after adding cols:\", list(emissions.columns))\n",
    "\n",
    "    # grab the costs\n",
    "    print(\"INFO: loading costs\")\n",
    "    costs = loadISPDataFromSheet(excel_file, 'Costs')\n",
    "    costs.insert(1, 'Region', 'nem')                           # insert the Region column after the first column    \n",
    "    costs = costs.rename(columns={\"Category\": \"Technology\"})    # rename the Category column to Technology\n",
    "    # print(\"Costs columns after adding cols:\", list(costs.columns))\n",
    "\n",
    "    # remove the column 'Existing and Committed' \n",
    "    if (release_id == '2022_ISP_final'):\n",
    "        print(\"INFO: removing column 'Existing and Committed' from capacities\")\n",
    "        capacities.drop(columns=['Existing and Committed'], inplace=True)\n",
    "\n",
    "    # 2024 draft\n",
    "    # TODO only drop for 2024 draft?\n",
    "    #capacities = capacities.drop(2024, axis=1)\n",
    "\n",
    "    # 2024 final: remove '2023-24' from capacities\n",
    "    if (release_id == '2024_ISP_final'):\n",
    "        capacities.drop(columns=['2023-24'], inplace=True)\n",
    "\n",
    "    # some workbooks have year columns in format 2023-24, others in 2024, so rename them all to 2024\n",
    "    for frame in [capacities, generation, flows, emissions, costs]:\n",
    "        renameFinancialYearColumns(frame)\n",
    "        changeNumericColumnsToFloats(frame)\n",
    "        renameRegions(frame)\n",
    "\n",
    "    # introduced for 2024 final: remove 'Subregion' column if it exists\n",
    "    capacities = collapseSubregions(capacities)\n",
    "    generation = collapseSubregions(generation)\n",
    "\n",
    "    # check the dataframes are compatible\n",
    "    check_dataframe_compatibility([\n",
    "        (generation, \"generation\"),\n",
    "        (flows, \"flows\"),\n",
    "        (capacities, \"capacities\"),\n",
    "        (emissions, \"emissions\"),\n",
    "        (costs, \"costs\")\n",
    "    ])\n",
    "\n",
    "    # touch up the energies\n",
    "    energies = pd.concat([generation, flows], ignore_index=True)\n",
    "    energies.insert(0, \"Type\", \"energy\")\n",
    "    energies = renameTechnologyLabels(energies)\n",
    "    makeSpecialTechsPositive(energies)                   # flip the sign of negative values\n",
    "  \n",
    "    # touch up the capacities\n",
    "    capacities.insert(1, \"Type\", \"capacity\")\n",
    "    capacities = renameTechnologyLabels(capacities)\n",
    "    capacities['Technology'] = capacities['Technology'].apply(lambda x: x[:-12] if x.endswith(\"_discharging\") else x)  # remove _discharging from tech labels\n",
    "    \n",
    "    # touch up the emissions\n",
    "    emissions.insert(1, \"Type\", \"emissions\")\n",
    "    multiplyBy1e3(emissions)\n",
    "\n",
    "    # touch up costs\n",
    "    costs.insert(1, \"Type\", \"cost\")\n",
    "    costs = renameCostLabels(costs)\n",
    "\n",
    "    # now combine them and add a column with the file label (in snake case)\n",
    "    combined = pd.concat([energies, capacities, emissions, costs], ignore_index=True)\n",
    "    combined.insert(0, \"Scenario\", re.sub(r'\\W+', '_', label.strip().lower()))\n",
    "\n",
    "    combined = addSummaryRegion(combined)\n",
    "  \n",
    "    return combined\n",
    "\n",
    "\n",
    "def loadScenariosList(release_name):\n",
    "    # load the list of ISP files from the scenarios JSON file\n",
    "    index_file = os.path.join(INPUT_FOLDER, release_name, \"scenarios.json\")\n",
    "    if not os.path.exists(index_file):\n",
    "        raise ValueError(f\"ERROR: the scenario index file '{index_file}' does not exist.\")\n",
    "    \n",
    "    with open(index_file) as f:\n",
    "        scenario_files = json.load(f)    \n",
    "\n",
    "    if len(scenario_files) == 0:\n",
    "        raise ValueError(f\"ERROR: the scenarios list from '{index_file}' is empty.\")\n",
    "    \n",
    "    return scenario_files\n",
    "\n",
    "\n",
    "def processGenerationOutlookFiles(release_id):\n",
    "    # process_start_time = timeit.default_timer()\n",
    "    combined_data = pd.DataFrame()\n",
    "\n",
    "    num_files_processed = 0\n",
    "\n",
    "    scenario_files = loadScenariosList(release_id)\n",
    "    for file_info in scenario_files:\n",
    "        if num_files_processed >= MAX_FILES_TO_PROCESS:\n",
    "            break\n",
    "      \n",
    "        # file_start_time = timeit.default_timer()\n",
    "        scenario_label = file_info[\"label\"]\n",
    "        file_name = file_info['file_name']\n",
    "        outlook_data = getWorkbookData(release_id, file_name, scenario_label)\n",
    "        # printDataSummary(outlook_data, file_start_time)\n",
    "        \n",
    "        combined_data = pd.concat([combined_data, outlook_data], ignore_index=True)\n",
    "        num_files_processed += 1\n",
    "\n",
    "    # print final summary and write CSV file\n",
    "    # print(\"\\n\\naggregated data\")\n",
    "    # printDataSummary(combined_data, process_start_time)\n",
    "\n",
    "    return combined_data\n",
    "\n",
    "\n",
    "def processAndCacheOutlooks(filename_parquet, release_name):\n",
    "    if os.path.exists(filename_parquet):\n",
    "        print(f\"WARNING: '{filename_parquet}' exists, skipping processing and will used cached version\")\n",
    "        return\n",
    "\n",
    "    print(f\"\\nINFO: processing ISP outlook workbooks for release '{release_name}'\")\n",
    "    combined_data = processGenerationOutlookFiles(release_name)\n",
    "\n",
    "    runIntegrityChecks(combined_data)\n",
    "\n",
    "    # convert all column headers to strings\n",
    "    print(f\"\\nwriting to cache '{filename_parquet}'\")\n",
    "    frame_copy = combined_data.copy()\n",
    "    frame_copy.columns = frame_copy.columns.map(str)\n",
    "    frame_copy.to_parquet(filename_parquet)\n",
    "\n",
    "\n",
    "# read from cache if it exists, otherwise process and write to cache\n",
    "def loadGenerationOutlooks(release_name):\n",
    "    # compute the cache director name and ensure that it exists\n",
    "    cache_path = os.path.join(OUTPUT_FOLDER, CACHE_FOLDER)\n",
    "    if not os.path.exists(cache_path):\n",
    "        print(\"creating cache directory\")\n",
    "        os.makedirs(cache_path)\n",
    "\n",
    "    filename_parquet = os.path.join(cache_path, release_name + \".outlook.parquet\")\n",
    "\n",
    "    # process and cache\n",
    "    processAndCacheOutlooks(filename_parquet, release_name)\n",
    "\n",
    "    # load from cache\n",
    "    combined_data = pd.read_parquet(filename_parquet)\n",
    "    renameYearColumnsFromStringToInteger(combined_data)\n",
    "    runIntegrityChecks(combined_data)\n",
    "    \n",
    "    return combined_data\n",
    "\n",
    "\n",
    "# outlooks = loadGenerationOutlooks('2024_ISP_final')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f03cff7",
   "metadata": {},
   "source": [
    "## build a simple chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "id": "f13fb23e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%config InlineBackend.figure_format = 'retina'  # For high-resolution displays\n",
    "\n",
    "def reorder_columns(data):\n",
    "    FUELTECH_ORDER = [\n",
    "        'battery_charging',\n",
    "        'battery_VPP_discharging',\n",
    "        'battery_distributed_discharging',\n",
    "        'pumps',\n",
    "        'exports',\n",
    "        'coal_brown',\n",
    "        'coal_black',\n",
    "        'bioenergy',\n",
    "        'bioenergy_biogas',\n",
    "        'bioenergy_biomass',\n",
    "        'distillate',\n",
    "        'gas_steam',\n",
    "        'gas_ccgt',\n",
    "        'gas_ocgt',\n",
    "        'gas_recip',\n",
    "        'gas_wcmg',\n",
    "        'gas_ccgt_ccs',\n",
    "        'gas_ocgt_ccs',\n",
    "        'gas_hydrogen',\n",
    "        'battery_discharging',\n",
    "        'hydro',\n",
    "        'wind_offshore',\n",
    "        'wind',\n",
    "        'solar_thermal',\n",
    "        'solar_utility',\n",
    "        'solar_rooftop',\n",
    "        'demand_response'\n",
    "    ]\n",
    "\n",
    "    # Get the intersection of the order and the columns in the data\n",
    "    common_columns = [col for col in FUELTECH_ORDER if col in data.columns]\n",
    "\n",
    "    # Check if there are any columns in the data that are not in the order\n",
    "    extra_columns = [col for col in data.columns if col not in FUELTECH_ORDER]\n",
    "    if extra_columns:\n",
    "        print(f\"WARN: data contains the following fuel techs not found in FUEL_TECH_ORDER: {', '.join(extra_columns)}\")\n",
    "\n",
    "    # Reorder the columns in data\n",
    "    reordered_data = data[common_columns]\n",
    "    return reordered_data\n",
    "\n",
    "\n",
    "def create_area_chart(outlooks, scenario, type, CDP, region):\n",
    "    # Filter the data based on the provided parameters\n",
    "    filtered_data = outlooks[(outlooks['Scenario'] == scenario) & (outlooks['Type'] == type) & (outlooks['CDP'] == CDP) & (outlooks['Region'] == region)]\n",
    "\n",
    "    # Reshape the data so that each year is a separate row\n",
    "    reshaped_data = filtered_data.melt(id_vars=['Scenario', 'Type', 'CDP', 'Region', 'Technology'], var_name='Year', value_name='Value')\n",
    "\n",
    "    # Pivot the data so that each technology is a separate column\n",
    "    pivoted_data = reshaped_data.pivot(index='Year', columns='Technology', values='Value')\n",
    "\n",
    "    # Remove columns ending with '_charging'\n",
    "    pivoted_data = pivoted_data[pivoted_data.columns.drop(list(pivoted_data.filter(regex='_charging')))]\n",
    "\n",
    "    # Divide all values in the DataFrame by 1000\n",
    "    pivoted_data = pivoted_data.divide(1000)\n",
    "\n",
    "    # Reorder the columns in pivoted_data\n",
    "    pivoted_data = reorder_columns(pivoted_data)\n",
    "\n",
    "    # Create a new figure with a specific size\n",
    "    plt.figure(figsize=(10, 5))\n",
    "\n",
    "    # Create a list of colors for the columns in pivoted_data\n",
    "    colors = [FUELTECH_COLORS.get(fueltech_id, '#000000') for fueltech_id in pivoted_data.columns]\n",
    "\n",
    "    # Plot the stacked area chart\n",
    "    plt.stackplot(pivoted_data.index, pivoted_data.T, colors=colors)\n",
    "\n",
    "    # Rotate x-axis labels 90 degrees\n",
    "    plt.xticks(rotation=90)\n",
    "\n",
    "    plt.ylabel('TWh')\n",
    "    plt.title(f'Generation — {scenario}/{region}/{CDP}', fontname='Arial Black', fontsize=20)\n",
    "\n",
    "    # Map the column names to their display names\n",
    "    display_names = [get_fuelTechDescription(fueltech_id) for fueltech_id in pivoted_data.columns]\n",
    "\n",
    "    # Move the legend to the right of the chart\n",
    "    plt.legend(display_names, bbox_to_anchor=(1.00, 1), loc='upper left', frameon=False)\n",
    "\n",
    "    # Remove the right and top spines\n",
    "    plt.gca().spines['right'].set_visible(False)\n",
    "    plt.gca().spines['top'].set_visible(False)\n",
    "    plt.gca().spines['left'].set_color('lightgrey')    \n",
    "    plt.gca().spines['bottom'].set_color('lightgrey')\n",
    "    \n",
    "    # Add x-axis and y-axis in light grey\n",
    "    plt.axhline(0, color='grey')\n",
    "\n",
    "    # Make all axes and tick marks in light grey\n",
    "    plt.tick_params(colors='grey')\n",
    "\n",
    "    plt.show()\n",
    "\n",
    "# create_area_chart(outlooks, 'step_change', 'energy', 'CDP3', 'nem')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02d2a4b1",
   "metadata": {},
   "source": [
    "# TESTING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cba0fd8e",
   "metadata": {},
   "source": [
    "### testing collapsing subregions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "id": "60752727",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show all the entries for CDP=CDP1 in Region=NSW1 and Technology=coal_black\n",
    "def printCDP1_NSW1_Coal(frame):\n",
    "    print(frame[(frame['CDP'] == 'CDP1') & (frame['Region'] == 'NSW1') & (frame['Technology'] == 'coal_black')])\n",
    "\n",
    "\n",
    "def myGenTest():\n",
    "    # load the generation parquet file\n",
    "    energies = pd.read_parquet(\"generation.parquet\")\n",
    "    renameTechnologyLabels(energies)\n",
    "    renameRegions(energies)\n",
    "    makeSpecialTechsPositive(energies)\n",
    "\n",
    "    print(\"\\n\\n>>> before:\")\n",
    "    printCDP1_NSW1_Coal(energies)\n",
    "\n",
    "    # make a new dataframe with all columns except the Subregion column, grouping by CDP, Region, Technology\n",
    "    new_energies = collapseSubregions(energies)\n",
    "\n",
    "    print(\"\\n\\n>>> after:\")\n",
    "    printCDP1_NSW1_Coal(new_energies)\n",
    "\n",
    "    new_energies.info()\n",
    "    print(new_energies.head())\n",
    "\n",
    "    return new_energies\n",
    "\n",
    "# my_gen = myGenTest()\n",
    "# my_gen"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2218ad0b",
   "metadata": {},
   "source": [
    "## output to JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a635dd61",
   "metadata": {},
   "source": [
    "### JSON utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "da72d3d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# put all the elements of the numerical array on the same line\n",
    "def compact_json(json_str):\n",
    "    # This regex looks for the _numerical_ data array and captures everything in between [ and ]\n",
    "    pattern = r'(\"data\": \\[)([\\d\\., \\n]*)(\\])'\n",
    "    \n",
    "    # The 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",
    "def generate_id(row):\n",
    "  # locator\n",
    "  if row['Region'] == 'nem':\n",
    "    locator = \"au.nem.\"\n",
    "  else:\n",
    "    locator = f\"au.nem.{row['Region'].lower()}.\"\n",
    "\n",
    "  # first deal with costs, because they're a bit special\n",
    "  if row['Type'] == 'cost':\n",
    "    id = locator + f\"cost.{row['Technology']}.{row['Scenario']}.{row['CDP'].lower()}\"\n",
    "  else:\n",
    "    # otherwise it's emissions, generation, or capacity\n",
    "    id = locator\n",
    "    if row['Technology'] != 'none':\n",
    "        id += f\"fuel_tech.{row['Technology']}.\"\n",
    "    id += f\"{row['Type']}.{row['Scenario']}.{row['CDP'].lower()}\"\n",
    "\n",
    "  return id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "914f0d43",
   "metadata": {},
   "source": [
    "### zip utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "c7c61acb",
   "metadata": {
    "lines_to_next_cell": 1
   },
   "outputs": [],
   "source": [
    "def zipdir(path, zip_path):\n",
    "    zipf = zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED)\n",
    "    for root, dirs, files in os.walk(path):\n",
    "      for file in files:\n",
    "        zipf.write(os.path.join(root, file), os.path.relpath(os.path.join(root, file), os.path.join(path, '..')))\n",
    "    zipf.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5df8bb14",
   "metadata": {},
   "source": [
    "## generate JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "d97343f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "INFO: processing ISP outlook workbooks for release '2024_ISP_final'\n",
      "\n",
      "loading release '2024_ISP_final', scenario 'Step Change' from './input/2024_ISP_final/2024 ISP - Step Change - Core.xlsx'\n",
      "INFO: loading generation\n",
      "INFO: loading imports and exports\n",
      "INFO: loading capacity\n",
      "INFO: loading emissions\n",
      "INFO: loading costs\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 4290 to 1768 rows\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 5226 to 2158 rows\n",
      "INFO: checking that dataframes are all compatible\n",
      "INFO: 130 rows with negative values found for 'battery_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_VPP_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_distributed_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'exports', making them positive\n",
      "\n",
      "loading release '2024_ISP_final', scenario 'Green Energy Exports' from './input/2024_ISP_final/2024 ISP - Green Energy Exports - Core.xlsx'\n",
      "INFO: loading generation\n",
      "INFO: loading imports and exports\n",
      "INFO: loading capacity\n",
      "INFO: loading emissions\n",
      "INFO: loading costs\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 4290 to 1768 rows\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 5226 to 2158 rows\n",
      "INFO: checking that dataframes are all compatible\n",
      "INFO: 130 rows with negative values found for 'battery_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_VPP_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_distributed_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'exports', making them positive\n",
      "\n",
      "loading release '2024_ISP_final', scenario 'Progressive Change' from './input/2024_ISP_final/2024 ISP - Progressive Change - Core.xlsx'\n",
      "INFO: loading generation\n",
      "INFO: loading imports and exports\n",
      "INFO: loading capacity\n",
      "INFO: loading emissions\n",
      "INFO: loading costs\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: renaming financial year columns…\n",
      "INFO: renamed 28 financial years\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 4290 to 1768 rows\n",
      "INFO: subregion column found, collapsing\n",
      "INFO: collapsing subregions went from 5226 to 2158 rows\n",
      "INFO: checking that dataframes are all compatible\n",
      "INFO: 130 rows with negative values found for 'battery_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_VPP_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'battery_distributed_charging', making them positive\n",
      "INFO: 130 rows with negative values found for 'exports', making them positive\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: got good regions: [('_all', 3276), ('nsw1', 2652), ('qld1', 2652), ('vic1', 2652), ('sa1', 2496), ('tas1', 2496)]\n",
      "INFO: all regions are present\n",
      "INFO: all technology types appear to be mapped properly\n",
      "INFO: years are sequential from 2025 to 2052\n",
      "INFO: all technology types have valid values\n",
      "\n",
      "writing to cache './output/cache/2024_ISP_final.outlook.parquet'\n",
      "INFO: renamed 28 year columns from string to integer\n",
      "INFO: checking to see that all regions are represented and that no illegal regions are present\n",
      "INFO: got good regions: [('_all', 3276), ('nsw1', 2652), ('qld1', 2652), ('vic1', 2652), ('sa1', 2496), ('tas1', 2496)]\n",
      "INFO: all regions are present\n",
      "INFO: all technology types appear to be mapped properly\n",
      "INFO: years are sequential from 2025 to 2052\n",
      "INFO: all technology types have valid values\n",
      "INFO: creating folder ./output/releases/2024_ISP_final\n",
      "INFO: writing 2024_ISP_final/step_change\n",
      "INFO: writing 2024_ISP_final/green_energy_exports\n",
      "INFO: writing 2024_ISP_final/progressive_change\n"
     ]
    }
   ],
   "source": [
    "RELEASES_FOLDER = \"releases\"\n",
    "\n",
    "def getUnitFromType(series_type):\n",
    "    if series_type == \"energy\":\n",
    "        return \"GWh\"\n",
    "    elif series_type == \"capacity\":\n",
    "        return \"MW\"\n",
    "    elif series_type == \"emissions\":\n",
    "        return \"ktCO2e\"\n",
    "    elif series_type == \"cost\":\n",
    "        return \"$000s\"\n",
    "    else:\n",
    "        raise ValueError(f\"ERROR: unknown type '{series_type}'\")\n",
    "\n",
    "def buildNewJSON(outlooks, release, scenario):\n",
    "    data = []\n",
    "\n",
    "    # extract start and end year\n",
    "    years = getYearsFromColumnNames(outlooks)\n",
    "    start_year = years[0] - 1   # subtract 1 to account for start of financial year\n",
    "    last_year = years[-1] - 1\n",
    "    start = f\"{start_year}-07-01T00:00:00+10:00\"\n",
    "    last = f\"{last_year}-07-01T00:00:00+10:00\"\n",
    "   \n",
    "    for i, row in outlooks.iterrows():\n",
    "        if row['Scenario'] == scenario:\n",
    "            # change numbers to just 1 decimal place\n",
    "            year_data = [round(x, 1) for x in row[pd.to_numeric(row.index, errors='coerce')>=2022].tolist()]  # selecting only year data\n",
    "            \n",
    "            element = {\n",
    "                \"id\": generate_id(row),\n",
    "                \"type\": row['Type'],\n",
    "                \"network\": \"nem\",\n",
    "                \"region\": row['Region'],\n",
    "                \"fuel_tech\": row['Technology'],         # add both fuel_tech and category\n",
    "                \"category\": row['Technology'],\n",
    "                \"scenario\": row['Scenario'],\n",
    "                \"pathway\": row['CDP'],\n",
    "                \"units\": getUnitFromType(row['Type']),\n",
    "                \"projection\": {\n",
    "                    \"start\": start,\n",
    "                    \"last\": last,\n",
    "                    \"interval\": \"1Y\",\n",
    "                    \"data\": year_data\n",
    "                }\n",
    "            }\n",
    "\n",
    "            # make sure we have either fuel_tech or category, but not both\n",
    "            if row['Type'] == \"cost\":\n",
    "                del element['fuel_tech']\n",
    "            else:\n",
    "                del element['category']\n",
    "\n",
    "            # remove empty fuel_tech\n",
    "            if row['Technology'] == 'none':\n",
    "                del element['fuel_tech']\n",
    "              \n",
    "            data.append(element)\n",
    "\n",
    "    output_obj = {\n",
    "        \"version\": \"4.2\",\n",
    "        \"release\": release,\n",
    "        \"created_at\": datetime.now(TIME_ZONE).strftime(\"%Y-%m-%dT%H:%M:%S%z\"),\n",
    "        \"messages\": [ f\"projections from AEMO's {release} generation outlooks\" ],\n",
    "        \"data\": data,\n",
    "    }\n",
    "\n",
    "    json_output = json.dumps(output_obj, indent=2)\n",
    "    return compact_json(json_output)\n",
    "\n",
    "\n",
    "def writeNewJSON(root, outlooks, release, scenario):\n",
    "    print(f\"INFO: writing {release}/{scenario}\")\n",
    "\n",
    "    json_output = buildNewJSON(outlooks, release, scenario)\n",
    "    filename = f\"{scenario}.json\"\n",
    "    path = os.path.join(root, filename)\n",
    "    with open(path, \"w\") as f:\n",
    "        f.write(json_output)\n",
    "\n",
    "\n",
    "def writeNewJSONs(release):\n",
    "    outlooks = loadGenerationOutlooks(release)\n",
    "\n",
    "    # make the enclosing folder\n",
    "    output_dir = os.path.join(OUTPUT_FOLDER, RELEASES_FOLDER, release)\n",
    "    print(f\"INFO: creating folder {output_dir}\")\n",
    "    os.makedirs(output_dir, exist_ok=True)\n",
    "\n",
    "    for scenario in outlooks['Scenario'].unique():\n",
    "        writeNewJSON(output_dir, outlooks, release, scenario)\n",
    "\n",
    "    distro_file = os.path.join(OUTPUT_FOLDER, RELEASES_FOLDER, f\"{release}.zip\")\n",
    "    zipdir(output_dir, distro_file)\n",
    "\n",
    "# output them all\n",
    "writeNewJSONs(\"2022_ISP_final\")\n",
    "writeNewJSONs(\"2024_ISP_final\")\n"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": ".venv",
   "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": 5
}