{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9f3d15a0",
   "metadata": {},
   "source": [
    "# Adding and splitting sectors\n",
    "\n",
    "\n",
    "This notebook uses the packaged MARIO IOT test table to show the Excel workflow for extending a database with new sectors.\n",
    "\n",
    "The screenshots below refer to the IOT workbook. SUT follows the same workflow, but the `Master` sheet uses `Activity` and `Commodity`, includes `Market share`, and does not expose `Add or Split`.\n",
    "\n",
    "The `Split` workflow is for the moment supported only for the IOT case and relies on a cross-minimum entropy optimization model written in the [CVXlab](https://cvxlab.readthedocs.io/en/latest/) framework and embedded into MARIO. A publications on this model will be soon sent to peer review.\n",
    "\n",
    "\n",
    "The overall logic of the algorithm is depicted in the figure below, while a complete example of the add-sector workflow (including `split=True`) is available [at the following repository](https://github.com/it-is-me-mario/MRIO-disaggregation-model).\n",
    "\n",
    "\n",
    "![The add-sectors logic](../../_static/images/add_sector_logic.png)\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "* N.B. the implemented model relies on a non-linear formulation and for large models, the [MOSEK](https://www.mosek.com) solver may represent the best solution. In that case, follow instructions on how to get a MOSEK license and be sure you install MOSEK via pip in your environment. Consider also that the splitting algorithm is still under development.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38b3c479",
   "metadata": {},
   "source": [
    "## Load an IOT table\n",
    "\n",
    "We start from a simplified IOT table, downloadable [here](../../_static/data/supporting_files/add_sector_iot_table.xlsx)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4dd8297a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:07.321894Z",
     "iopub.status.busy": "2026-05-04T08:46:07.321614Z",
     "iopub.status.idle": "2026-05-04T08:46:07.430739Z",
     "shell.execute_reply": "2026-05-04T08:46:07.430450Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading IOT flows from /Users/lorenzorinaldi/Documents/GitHub/MARIO/mario/test/supporting_files/add_sectors/add_sector_iot_table.xlsx.\n",
      "INFO Parser: state payload ready with 6 canonical blocks.\n",
      "INFO Parser: excel state ready for IOT.\n",
      "INFO Metadata: initialized.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['s1', 's2', 's3', 's4']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import mario\n",
    "db = mario.parse_from_excel(\n",
    "    path = \"path/to/source IOT workbook.xlsx\",\n",
    "    table = \"IOT\",\n",
    "    mode = \"flows\"\n",
    "    )\n",
    "db.get_index(\"Sector\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcce9729",
   "metadata": {},
   "source": [
    "___\n",
    "## Generate an empty add-sector template\n",
    "\n",
    "The add-sectors workbook is usually filled in two passes:\n",
    "\n",
    "1. generate the empty workbook;\n",
    "2. fill the `Master` sheet;\n",
    "3. let MARIO create the missing inventory sheets;\n",
    "4. fill the inventory sheets in Excel;\n",
    "5. read the completed workbook and apply it.\n",
    "\n",
    "The next sections explain what each sheet is for before running the workflow."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9da09baf",
   "metadata": {},
   "source": [
    "### Download the packaged example workbooks\n",
    "\n",
    "The exact files used in this example are available here:\n",
    "\n",
    "- [Source IOT workbook](../../_static/data/supporting_files/add_sector_iot_table.xlsx)\n",
    "- [Empty add-sector template](../../_static/data/supporting_files/add_sector_iot_template.xlsx)\n",
    "- [Filled master workbook](../../_static/data/supporting_files/add_sector_iot_master_filled.xlsx)\n",
    "- [Completed inventories workbook](../../_static/data/supporting_files/add_sector_iot_inventories_filled.xlsx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e9ae017",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:07.432733Z",
     "iopub.status.busy": "2026-05-04T08:46:07.432608Z",
     "iopub.status.idle": "2026-05-04T08:46:07.449219Z",
     "shell.execute_reply": "2026-05-04T08:46:07.448960Z"
    }
   },
   "outputs": [],
   "source": [
    "db.get_add_sectors_excel(\n",
    "    path=\"path/where/to/download/empty add-sector template.xlsx\",\n",
    "    overwrite=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbdb0720",
   "metadata": {},
   "source": [
    "### The Master sheet\n",
    "\n",
    "![The add-sectors master sheet](../../_static/images/add_sector_master.png)\n",
    "\n",
    "The `Master` sheet defines the high-level structure of the extension:\n",
    "\n",
    "- `Region`: target database region. You can also use a region cluster name to replicate the same characterization in multiple regions.\n",
    "- `Sector`: name of the new sector.\n",
    "- `Inventory sheet`: name of the sheet where the inventory is described.\n",
    "- `Quantity` and `Unit`: functional unit of the new sector's output.\n",
    "- `Final consumption` and `Consumption category`: optional final demand attached to the new sector.\n",
    "- `Parent Sector`: existing sector used as the reference when coefficients or percentage changes should inherit an existing structure.\n",
    "- `Leave empty`: when set to `TRUE`, MARIO skips that inventory sheet.\n",
    "- `Add or Split`: use `Add` for a fully new sector, or `Split` when the new sector must be extracted from a parent sector.\n",
    "\n",
    "The same inventory sheet name can be reused across multiple `Master` rows when one characterization should be applied to multiple target regions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49e856cd",
   "metadata": {},
   "source": [
    "### The Clusters sheets\n",
    "\n",
    "![The add-sectors cluster sheets](../../_static/images/add_sector_clusters.png)\n",
    "\n",
    "Cluster sheets are optional, but they are useful whenever the same assumptions must be repeated:\n",
    "\n",
    "- `Regions Clusters`: each column header is one cluster name, and each non-empty cell below it is one database region included in that cluster.\n",
    "- `Sectors Clusters`: in IOT, this lets you group multiple existing sectors under one reusable label.\n",
    "- `Commodities Clusters`: in SUT, the item-cluster sheet works in the same way for commodity inputs.\n",
    "\n",
    "Region clusters can be referenced in `Master.Region` and in `Inventory.DB Region`. Item clusters can be referenced in `Inventory.DB Item`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea810a8e",
   "metadata": {},
   "source": [
    "___\n",
    "## Create inventory sheets from a filled Master sheet\n",
    "\n",
    "Fill the `Master` sheet in Excel first. Once the rows and inventory-sheet names are ready, read the workbook back with `get_inventories=True` so MARIO creates the missing inventory tabs inside the same file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88294953",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:07.450995Z",
     "iopub.status.busy": "2026-05-04T08:46:07.450878Z",
     "iopub.status.idle": "2026-05-04T08:46:07.520449Z",
     "shell.execute_reply": "2026-05-04T08:46:07.520128Z"
    }
   },
   "outputs": [],
   "source": [
    "db.read_add_sectors_excel(\n",
    "    path=\"path/to/filled master workbook.xlsx\",\n",
    "    get_inventories=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cd40d5b",
   "metadata": {},
   "source": [
    "### The inventory sheets\n",
    "\n",
    "![The add-sectors inventory sheet](../../_static/images/add_sector_inventories.png)\n",
    "\n",
    "Each inventory sheet describes the inputs used by the new sector:\n",
    "\n",
    "- `Quantity`: technical coefficient per unit of output declared in `Master`.\n",
    "- `Unit`: unit of that coefficient.\n",
    "- `Input`: free user label; MARIO does not use it as a database key.\n",
    "- `Item type`: database set to target, such as `Sector`, `Factor of production`, or `Satellite account`.\n",
    "- `DB Item`: existing database label, or an item-cluster name when clusters are used.\n",
    "- `DB Region`: source region of the input. This can be a database region or a region cluster.\n",
    "- `Change type`: use `Update` for an absolute coefficient, or `Percentage` to scale the parent structure.\n",
    "- `Source` and `Notes`: support columns for references and comments.\n",
    "\n",
    "The `DB units` sheet in the workbook is a reference tab that helps you keep units consistent with the database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8025d97e",
   "metadata": {},
   "source": [
    "### Split-specific sheets\n",
    "\n",
    "If at least one row in `Master` is marked as `Split`, MARIO also relies on `Total outputs`, `Trades`, (both mandatory) `Exclusions` (optional), and `Tolerances` (mandatory but prefilled).\n",
    "For the moment, this is an IOT-only workflow. In practice, `Split` means that the new sector is extracted from an existing parent sector rather than added as a completely independent structure.\n",
    "\n",
    "\n",
    "#### The Total outputs sheet\n",
    "\n",
    "![The add-sectors total outputs sheet](../../_static/images/add_sector_total_outputs.png)\n",
    "\n",
    "For each new sector, the user must specify the production level in each region of the *database*\n",
    "\n",
    "\n",
    "#### The Trades sheet\n",
    "\n",
    "![The add-sectors trades sheet](../../_static/images/add_sector_trades.png)\n",
    "\n",
    "For each new sector in each region, the user must specify the amount of product imported by the other regions\n",
    "\n",
    "\n",
    "#### The Exclusion sheet\n",
    "\n",
    "Here you can specify whether some of those sectors indicated as `Sector_to` do not consume any input coming from `Sector_from`. In our simplified example, we did not implement any exclusion\n",
    "\n",
    "\n",
    "#### The Tolerances sheet\n",
    "\n",
    "Here you can modify the rigidity of the optimization problem by acting on the `delta` and `eps` parameters. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17ccd238",
   "metadata": {},
   "source": [
    "## Read the completed workbook\n",
    "\n",
    "After filling the generated inventory tabs and, if necessary, the split-specific data sheets, read the completed workbook with `read_inventories=True`. MARIO will normalize the `Master` sheet, load clusters, and group the inventories by target sector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e4da8b37",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:07.522196Z",
     "iopub.status.busy": "2026-05-04T08:46:07.522106Z",
     "iopub.status.idle": "2026-05-04T08:46:07.541168Z",
     "shell.execute_reply": "2026-05-04T08:46:07.540920Z"
    }
   },
   "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>Quantity</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Input</th>\n",
       "      <th>Item type</th>\n",
       "      <th>DB Item</th>\n",
       "      <th>DB Region</th>\n",
       "      <th>Change type</th>\n",
       "      <th>Source</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.065574</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s1</td>\n",
       "      <td>r1</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.081967</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2</td>\n",
       "      <td>r1</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.229508</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2b</td>\n",
       "      <td>r1</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.073770</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s3</td>\n",
       "      <td>r1</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.057377</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s4</td>\n",
       "      <td>r1</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.024590</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s1</td>\n",
       "      <td>r2</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.024590</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2</td>\n",
       "      <td>r2</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.016393</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2b</td>\n",
       "      <td>r2</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.016393</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s3</td>\n",
       "      <td>r2</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.008197</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s4</td>\n",
       "      <td>r2</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.032787</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s1</td>\n",
       "      <td>r3</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.024590</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2</td>\n",
       "      <td>r3</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.024590</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s2b</td>\n",
       "      <td>r3</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.016393</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s3</td>\n",
       "      <td>r3</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.016393</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Sector</td>\n",
       "      <td>s4</td>\n",
       "      <td>r3</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.286885</td>\n",
       "      <td>EUR</td>\n",
       "      <td>None</td>\n",
       "      <td>Factor of production</td>\n",
       "      <td>VA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Update</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Quantity Unit Input             Item type DB Item DB Region Change type  \\\n",
       "0   0.065574  EUR  None                Sector      s1        r1      Update   \n",
       "1   0.081967  EUR  None                Sector      s2        r1      Update   \n",
       "2   0.229508  EUR  None                Sector     s2b        r1      Update   \n",
       "3   0.073770  EUR  None                Sector      s3        r1      Update   \n",
       "4   0.057377  EUR  None                Sector      s4        r1      Update   \n",
       "5   0.024590  EUR  None                Sector      s1        r2      Update   \n",
       "6   0.024590  EUR  None                Sector      s2        r2      Update   \n",
       "7   0.016393  EUR  None                Sector     s2b        r2      Update   \n",
       "8   0.016393  EUR  None                Sector      s3        r2      Update   \n",
       "9   0.008197  EUR  None                Sector      s4        r2      Update   \n",
       "10  0.032787  EUR  None                Sector      s1        r3      Update   \n",
       "11  0.024590  EUR  None                Sector      s2        r3      Update   \n",
       "12  0.024590  EUR  None                Sector     s2b        r3      Update   \n",
       "13  0.016393  EUR  None                Sector      s3        r3      Update   \n",
       "14  0.016393  EUR  None                Sector      s4        r3      Update   \n",
       "15  0.286885  EUR  None  Factor of production      VA       NaN      Update   \n",
       "\n",
       "   Source Notes  \n",
       "0    None  None  \n",
       "1    None  None  \n",
       "2    None  None  \n",
       "3    None  None  \n",
       "4    None  None  \n",
       "5    None  None  \n",
       "6    None  None  \n",
       "7    None  None  \n",
       "8    None  None  \n",
       "9    None  None  \n",
       "10   None  None  \n",
       "11   None  None  \n",
       "12   None  None  \n",
       "13   None  None  \n",
       "14   None  None  \n",
       "15   None  None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.read_add_sectors_excel(\n",
    "    path=\"path/to/completed inventories workbook.xlsx\",\n",
    "    read_inventories=True,\n",
    ")\n",
    "\n",
    "db.inventories['s2b']['s2b_r1'] # or any other inventory you input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbe564c1",
   "metadata": {},
   "source": [
    "## Apply the completed workbook\n",
    "\n",
    "Once the inventories are loaded, `db.add_sectors(...)` inserts the new sectors into the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "17a7fcad",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:07.542669Z",
     "iopub.status.busy": "2026-05-04T08:46:07.542584Z",
     "iopub.status.idle": "2026-05-04T08:46:08.031157Z",
     "shell.execute_reply": "2026-05-04T08:46:08.030814Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING Database: All scenarios will be deleted from the database after add_sectors.\n",
      "INFO Resolver: resolving z for baseline.\n",
      "INFO Resolver: trying z via formula build_iot_z_from_Z_X.\n",
      "INFO Resolver: resolved z via formula build_iot_z_from_Z_X.\n",
      "INFO Resolver: resolving e for baseline.\n",
      "INFO Resolver: trying e via formula build_iot_e_from_E_X.\n",
      "INFO Resolver: resolved e via formula build_iot_e_from_E_X.\n",
      "INFO Resolver: resolving v for baseline.\n",
      "INFO Resolver: trying v via formula build_iot_v_from_V_X.\n",
      "INFO Resolver: resolved v via formula build_iot_v_from_V_X.\n",
      "INFO New Sector: ['s2b'] added to the database\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['s1', 's2', 's2b', 's3', 's4']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expanded_db = db.add_sectors(inplace=False)\n",
    "expanded_db.get_index(\"Sector\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b919b34",
   "metadata": {},
   "source": [
    "The updated coefficient and final-demand blocks now contain the new sectors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "28637264",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:08.032990Z",
     "iopub.status.busy": "2026-05-04T08:46:08.032854Z",
     "iopub.status.idle": "2026-05-04T08:46:08.216496Z",
     "shell.execute_reply": "2026-05-04T08:46:08.216155Z"
    }
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r1</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r2</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Level</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Item</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Level</th>\n",
       "      <th>Item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r1</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>0.217391</td>\n",
       "      <td>0.068182</td>\n",
       "      <td>0.065574</td>\n",
       "      <td>0.054054</td>\n",
       "      <td>0.051546</td>\n",
       "      <td>0.027211</td>\n",
       "      <td>0.017921</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>0.009615</td>\n",
       "      <td>0.017341</td>\n",
       "      <td>0.012422</td>\n",
       "      <td>0.013158</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>0.115942</td>\n",
       "      <td>0.310606</td>\n",
       "      <td>0.081967</td>\n",
       "      <td>0.144144</td>\n",
       "      <td>0.134021</td>\n",
       "      <td>0.034014</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.025862</td>\n",
       "      <td>0.019231</td>\n",
       "      <td>0.023121</td>\n",
       "      <td>0.018634</td>\n",
       "      <td>0.013158</td>\n",
       "      <td>0.014925</td>\n",
       "      <td>0.016667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.229508</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>0.043478</td>\n",
       "      <td>0.060606</td>\n",
       "      <td>0.073770</td>\n",
       "      <td>0.225225</td>\n",
       "      <td>0.082474</td>\n",
       "      <td>0.013605</td>\n",
       "      <td>0.010753</td>\n",
       "      <td>0.007692</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.009615</td>\n",
       "      <td>0.005780</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>0.036232</td>\n",
       "      <td>0.049242</td>\n",
       "      <td>0.057377</td>\n",
       "      <td>0.072072</td>\n",
       "      <td>0.226804</td>\n",
       "      <td>0.006803</td>\n",
       "      <td>0.007168</td>\n",
       "      <td>0.007692</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.009615</td>\n",
       "      <td>0.005780</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r2</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>0.036232</td>\n",
       "      <td>0.026515</td>\n",
       "      <td>0.024590</td>\n",
       "      <td>0.018018</td>\n",
       "      <td>0.020619</td>\n",
       "      <td>0.190476</td>\n",
       "      <td>0.057348</td>\n",
       "      <td>0.053846</td>\n",
       "      <td>0.051724</td>\n",
       "      <td>0.048077</td>\n",
       "      <td>0.017341</td>\n",
       "      <td>0.012422</td>\n",
       "      <td>0.013158</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>0.050725</td>\n",
       "      <td>0.041667</td>\n",
       "      <td>0.024590</td>\n",
       "      <td>0.036036</td>\n",
       "      <td>0.030928</td>\n",
       "      <td>0.095238</td>\n",
       "      <td>0.275986</td>\n",
       "      <td>0.069231</td>\n",
       "      <td>0.129310</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.023121</td>\n",
       "      <td>0.018634</td>\n",
       "      <td>0.013158</td>\n",
       "      <td>0.014925</td>\n",
       "      <td>0.016667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016393</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.207692</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.015152</td>\n",
       "      <td>0.016393</td>\n",
       "      <td>0.009009</td>\n",
       "      <td>0.010309</td>\n",
       "      <td>0.034014</td>\n",
       "      <td>0.053763</td>\n",
       "      <td>0.061538</td>\n",
       "      <td>0.206897</td>\n",
       "      <td>0.067308</td>\n",
       "      <td>0.005780</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.011364</td>\n",
       "      <td>0.008197</td>\n",
       "      <td>0.009009</td>\n",
       "      <td>0.010309</td>\n",
       "      <td>0.027211</td>\n",
       "      <td>0.046595</td>\n",
       "      <td>0.053846</td>\n",
       "      <td>0.060345</td>\n",
       "      <td>0.201923</td>\n",
       "      <td>0.005780</td>\n",
       "      <td>0.006211</td>\n",
       "      <td>0.006579</td>\n",
       "      <td>0.007463</td>\n",
       "      <td>0.008333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r3</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>0.043478</td>\n",
       "      <td>0.034091</td>\n",
       "      <td>0.032787</td>\n",
       "      <td>0.027027</td>\n",
       "      <td>0.020619</td>\n",
       "      <td>0.034014</td>\n",
       "      <td>0.025090</td>\n",
       "      <td>0.023077</td>\n",
       "      <td>0.017241</td>\n",
       "      <td>0.019231</td>\n",
       "      <td>0.184971</td>\n",
       "      <td>0.062112</td>\n",
       "      <td>0.059211</td>\n",
       "      <td>0.052239</td>\n",
       "      <td>0.050000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>0.065217</td>\n",
       "      <td>0.049242</td>\n",
       "      <td>0.024590</td>\n",
       "      <td>0.045045</td>\n",
       "      <td>0.041237</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>0.039427</td>\n",
       "      <td>0.023077</td>\n",
       "      <td>0.034483</td>\n",
       "      <td>0.028846</td>\n",
       "      <td>0.104046</td>\n",
       "      <td>0.270186</td>\n",
       "      <td>0.072368</td>\n",
       "      <td>0.126866</td>\n",
       "      <td>0.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.024590</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.197368</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>0.021739</td>\n",
       "      <td>0.018939</td>\n",
       "      <td>0.016393</td>\n",
       "      <td>0.018018</td>\n",
       "      <td>0.010309</td>\n",
       "      <td>0.013605</td>\n",
       "      <td>0.014337</td>\n",
       "      <td>0.015385</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.009615</td>\n",
       "      <td>0.040462</td>\n",
       "      <td>0.052795</td>\n",
       "      <td>0.059211</td>\n",
       "      <td>0.194030</td>\n",
       "      <td>0.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.015152</td>\n",
       "      <td>0.016393</td>\n",
       "      <td>0.009009</td>\n",
       "      <td>0.010309</td>\n",
       "      <td>0.013605</td>\n",
       "      <td>0.010753</td>\n",
       "      <td>0.007692</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.009615</td>\n",
       "      <td>0.034682</td>\n",
       "      <td>0.046584</td>\n",
       "      <td>0.052632</td>\n",
       "      <td>0.059701</td>\n",
       "      <td>0.191667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                    r1                                          \\\n",
       "Level                 Sector                                           \n",
       "Item                      s1        s2       s2b        s3        s4   \n",
       "Region Level  Item                                                     \n",
       "r1     Sector s1    0.217391  0.068182  0.065574  0.054054  0.051546   \n",
       "              s2    0.115942  0.310606  0.081967  0.144144  0.134021   \n",
       "              s2b   0.000000  0.000000  0.229508  0.000000  0.000000   \n",
       "              s3    0.043478  0.060606  0.073770  0.225225  0.082474   \n",
       "              s4    0.036232  0.049242  0.057377  0.072072  0.226804   \n",
       "r2     Sector s1    0.036232  0.026515  0.024590  0.018018  0.020619   \n",
       "              s2    0.050725  0.041667  0.024590  0.036036  0.030928   \n",
       "              s2b   0.000000  0.000000  0.016393  0.000000  0.000000   \n",
       "              s3    0.014493  0.015152  0.016393  0.009009  0.010309   \n",
       "              s4    0.014493  0.011364  0.008197  0.009009  0.010309   \n",
       "r3     Sector s1    0.043478  0.034091  0.032787  0.027027  0.020619   \n",
       "              s2    0.065217  0.049242  0.024590  0.045045  0.041237   \n",
       "              s2b   0.000000  0.000000  0.024590  0.000000  0.000000   \n",
       "              s3    0.021739  0.018939  0.016393  0.018018  0.010309   \n",
       "              s4    0.014493  0.015152  0.016393  0.009009  0.010309   \n",
       "\n",
       "Region                    r2                                          \\\n",
       "Level                 Sector                                           \n",
       "Item                      s1        s2       s2b        s3        s4   \n",
       "Region Level  Item                                                     \n",
       "r1     Sector s1    0.027211  0.017921  0.015385  0.017241  0.009615   \n",
       "              s2    0.034014  0.032258  0.015385  0.025862  0.019231   \n",
       "              s2b   0.000000  0.000000  0.015385  0.000000  0.000000   \n",
       "              s3    0.013605  0.010753  0.007692  0.008621  0.009615   \n",
       "              s4    0.006803  0.007168  0.007692  0.008621  0.009615   \n",
       "r2     Sector s1    0.190476  0.057348  0.053846  0.051724  0.048077   \n",
       "              s2    0.095238  0.275986  0.069231  0.129310  0.125000   \n",
       "              s2b   0.000000  0.000000  0.207692  0.000000  0.000000   \n",
       "              s3    0.034014  0.053763  0.061538  0.206897  0.067308   \n",
       "              s4    0.027211  0.046595  0.053846  0.060345  0.201923   \n",
       "r3     Sector s1    0.034014  0.025090  0.023077  0.017241  0.019231   \n",
       "              s2    0.047619  0.039427  0.023077  0.034483  0.028846   \n",
       "              s2b   0.000000  0.000000  0.015385  0.000000  0.000000   \n",
       "              s3    0.013605  0.014337  0.015385  0.008621  0.009615   \n",
       "              s4    0.013605  0.010753  0.007692  0.008621  0.009615   \n",
       "\n",
       "Region                    r3                                          \n",
       "Level                 Sector                                          \n",
       "Item                      s1        s2       s2b        s3        s4  \n",
       "Region Level  Item                                                    \n",
       "r1     Sector s1    0.017341  0.012422  0.013158  0.007463  0.008333  \n",
       "              s2    0.023121  0.018634  0.013158  0.014925  0.016667  \n",
       "              s2b   0.000000  0.000000  0.006579  0.000000  0.000000  \n",
       "              s3    0.005780  0.006211  0.006579  0.007463  0.008333  \n",
       "              s4    0.005780  0.006211  0.006579  0.007463  0.008333  \n",
       "r2     Sector s1    0.017341  0.012422  0.013158  0.007463  0.008333  \n",
       "              s2    0.023121  0.018634  0.013158  0.014925  0.016667  \n",
       "              s2b   0.000000  0.000000  0.006579  0.000000  0.000000  \n",
       "              s3    0.005780  0.006211  0.006579  0.007463  0.008333  \n",
       "              s4    0.005780  0.006211  0.006579  0.007463  0.008333  \n",
       "r3     Sector s1    0.184971  0.062112  0.059211  0.052239  0.050000  \n",
       "              s2    0.104046  0.270186  0.072368  0.126866  0.125000  \n",
       "              s2b   0.000000  0.000000  0.197368  0.000000  0.000000  \n",
       "              s3    0.040462  0.052795  0.059211  0.194030  0.066667  \n",
       "              s4    0.034682  0.046584  0.052632  0.059701  0.191667  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expanded_db.z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "aa72aaa9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:08.218181Z",
     "iopub.status.busy": "2026-05-04T08:46:08.218045Z",
     "iopub.status.idle": "2026-05-04T08:46:08.246409Z",
     "shell.execute_reply": "2026-05-04T08:46:08.246140Z"
    }
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>r1</th>\n",
       "      <th>r2</th>\n",
       "      <th>r3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Level</th>\n",
       "      <th>Consumption category</th>\n",
       "      <th>Consumption category</th>\n",
       "      <th>Consumption category</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Item</th>\n",
       "      <th>CC</th>\n",
       "      <th>CC</th>\n",
       "      <th>CC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Level</th>\n",
       "      <th>Item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r1</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>40.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>65.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>28.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>25.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r2</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>12.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>19.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>8.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>7.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r3</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>17.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                               r1                   r2  \\\n",
       "Level              Consumption category Consumption category   \n",
       "Item                                 CC                   CC   \n",
       "Region Level  Item                                             \n",
       "r1     Sector s1                   40.0                 10.0   \n",
       "              s2                   65.0                 22.0   \n",
       "              s2b                   0.0                  0.0   \n",
       "              s3                   28.0                  9.0   \n",
       "              s4                   25.0                  8.0   \n",
       "r2     Sector s1                   12.0                 45.0   \n",
       "              s2                   19.0                 80.0   \n",
       "              s2b                   0.0                  0.0   \n",
       "              s3                    8.0                 35.0   \n",
       "              s4                    7.0                 32.0   \n",
       "r3     Sector s1                   10.0                 12.0   \n",
       "              s2                   17.0                 19.0   \n",
       "              s2b                   0.0                  0.0   \n",
       "              s3                    7.0                  8.0   \n",
       "              s4                    6.0                  7.0   \n",
       "\n",
       "Region                               r3  \n",
       "Level              Consumption category  \n",
       "Item                                 CC  \n",
       "Region Level  Item                       \n",
       "r1     Sector s1                    8.0  \n",
       "              s2                   17.0  \n",
       "              s2b                   0.0  \n",
       "              s3                    7.0  \n",
       "              s4                    6.0  \n",
       "r2     Sector s1                   10.0  \n",
       "              s2                   22.0  \n",
       "              s2b                   0.0  \n",
       "              s3                    9.0  \n",
       "              s4                    8.0  \n",
       "r3     Sector s1                   50.0  \n",
       "              s2                   93.0  \n",
       "              s2b                   0.0  \n",
       "              s3                   42.0  \n",
       "              s4                   40.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expanded_db.Y"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14b16ea2",
   "metadata": {},
   "source": [
    "## Disaggregate a new sector from an existing one (with CVXLab)\n",
    "\n",
    "By \"splitting\", we mean \"disaggregating a new sector from an existing one\". This workflow is for IOT only and is supported by a CVXlab model.\n",
    "\n",
    "### Download the bundled split-model assets\n",
    "\n",
    "For transparency, the exact CVXLab assets used by this workflow are available here:\n",
    "\n",
    "- [Model formulation (pdf)](../../_static/data/add_sectors/Model%20formulation.pdf)\n",
    "- [CVXlab model settings](../../_static/data/add_sectors/model_settings.xlsx)\n",
    "- [Mapping file between MARIO and CVXlab](../../_static/data/add_sectors/mapping.xlsx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "48ea1348",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:08.248110Z",
     "iopub.status.busy": "2026-05-04T08:46:08.247988Z",
     "iopub.status.idle": "2026-05-04T08:46:10.299523Z",
     "shell.execute_reply": "2026-05-04T08:46:10.299232Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING Database: All scenarios will be deleted from the database after add_sectors.\n",
      "INFO Resolver: resolving z for baseline.\n",
      "INFO Resolver: trying z via formula build_iot_z_from_Z_X.\n",
      "INFO Resolver: resolved z via formula build_iot_z_from_Z_X.\n",
      "INFO Resolver: resolving e for baseline.\n",
      "INFO Resolver: trying e via formula build_iot_e_from_E_X.\n",
      "INFO Resolver: resolved e via formula build_iot_e_from_E_X.\n",
      "INFO Resolver: resolving v for baseline.\n",
      "INFO Resolver: trying v via formula build_iot_v_from_V_X.\n",
      "INFO Resolver: resolved v via formula build_iot_v_from_V_X.\n",
      "INFO New Sector: ['s2b'] added to the database\n",
      "INFO Split scenario 'split_baseline' generated for sectors ['s2b'].\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO | Model | Model instance generation...\u001b[0m\n",
      "INFO | Model | Custom 'operators' import | Imported 2 custom function(s) from 'user_defined_operators.py'.\u001b[0m\n",
      "INFO | Model | Custom 'operators' import | Imported 2 custom function(s) from 'user_defined_operators.py'.\u001b[0m\n",
      "INFO | Model | Custom 'constants' import | Imported 4 custom function(s) from 'user_defined_constants.py'.\u001b[0m\n",
      "INFO | Model | Custom 'constants' import | Imported 4 custom function(s) from 'user_defined_constants.py'.\u001b[0m\n",
      "INFO | Model | Custom 'constants' import | Imported 4 custom function(s) from 'user_defined_constants.py'.\u001b[0m\n",
      "INFO | Model | Custom 'constants' import | Imported 4 custom function(s) from 'user_defined_constants.py'.\u001b[0m\n",
      "INFO | Model.core.database | Generating new sets excel file 'sets.xlsx'.\u001b[0m\n",
      "INFO | Model | Model instance generation... DONE (0.06 seconds)\u001b[0m\n",
      "INFO | Model | Loading sets and variables coordinates...\u001b[0m\n",
      "INFO | Model | Loading sets and variables coordinates... DONE (0.02 seconds)\u001b[0m\n",
      "INFO | Model | Generation of blank data structures...\u001b[0m\n",
      "INFO | Model | Creating new blank SQLite database 'database.db'.\u001b[0m\n",
      "INFO | Model | Generating new blank input data directory and related file/s.\u001b[0m\n",
      "INFO | Model | Generation of blank data structures... DONE (0.36 seconds)\u001b[0m\n",
      "INFO | Model | Loading input data to SQLite database...\u001b[0m\n",
      "INFO | Model | Loading input data to SQLite database... DONE (0.13 seconds)\u001b[0m\n",
      "INFO | Model | Numerical model generation...\u001b[0m\n",
      "INFO | Model.core | Loading and validating symbolic problem...\u001b[0m\n",
      "INFO | Model.core | Loading and validating symbolic problem... DONE (0.02 seconds)\u001b[0m\n",
      "INFO | Model.core | Checking exogenous data coherence...\u001b[0m\n",
      "INFO | Model.core | Checking exogenous data coherence... DONE (0.00 seconds)\u001b[0m\n",
      "INFO | Model.core | Generating data structures for endogenous data tables...\u001b[0m\n",
      "INFO | Model.core | Generating data structures for endogenous data tables... DONE (0.02 seconds)\u001b[0m\n",
      "INFO | Model.core | Generating data structures for all variables and constants...\u001b[0m\n",
      "INFO | Model.core | Generating data structures for all variables and constants... DONE (0.20 seconds)\u001b[0m\n",
      "INFO | Model.core | Fetching data from 'database.db' to cvxpy exogenous variables...\u001b[0m\n",
      "INFO | Model.core | Fetching data from 'database.db' to cvxpy exogenous variables... DONE (0.19 seconds)\u001b[0m\n",
      "INFO | Model.core.problem | Generating cvxpy numerical problem/s...\u001b[0m\n",
      "INFO | Model.core.problem | Generating cvxpy numerical problem/s... DONE (0.05 seconds)\u001b[0m\n",
      "INFO | Model | Numerical model generation... DONE (0.49 seconds)\u001b[0m\n",
      "INFO | Model | Model run | Solution mode: independent' | Solver: 'SCS' | Problems: 1 | Scenarios: 1\u001b[0m\n",
      "INFO | Model | Solving numerical problems...\u001b[0m\n",
      "INFO | Model.core.problem | Solving cvxpy sub-problem 'nan'\u001b[0m\n",
      "INFO | Model.core.problem | Problem status: 'optimal_inaccurate'\u001b[0m\n",
      "INFO | Model | Solving numerical problems... DONE (4.01 seconds)\u001b[0m\n",
      "INFO | Model | =================================\u001b[0m\n",
      "INFO | Model | Numerical problems status report:\u001b[0m\n",
      "INFO | Model | Sub-problem [nan]: optimal_inaccurate\u001b[0m\n",
      "INFO | Model | Exporting endogenous model results to SQLite database...\u001b[0m\n",
      "INFO | Model | Exporting endogenous model results to SQLite database... DONE (0.04 seconds)\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "expanded_db_split = db.add_sectors(\n",
    "    inplace=False,\n",
    "    split=True,\n",
    "    cvxlab_path=\"path/where/to/store/cvxlab/model/files\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6108ec75",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-05-04T08:46:10.301214Z",
     "iopub.status.busy": "2026-05-04T08:46:10.301123Z",
     "iopub.status.idle": "2026-05-04T08:46:10.476087Z",
     "shell.execute_reply": "2026-05-04T08:46:10.475808Z"
    }
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r1</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r2</th>\n",
       "      <th colspan=\"5\" halign=\"left\">r3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Level</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Item</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s2b</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Level</th>\n",
       "      <th>Item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r1</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>7.771668</td>\n",
       "      <td>10.228326</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.465351</td>\n",
       "      <td>2.534586</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.616242</td>\n",
       "      <td>2.383706</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>8.509342</td>\n",
       "      <td>34.310533</td>\n",
       "      <td>6.052092</td>\n",
       "      <td>8.509342</td>\n",
       "      <td>6.709267</td>\n",
       "      <td>2.504708</td>\n",
       "      <td>3.821973</td>\n",
       "      <td>1.134627</td>\n",
       "      <td>1.436139</td>\n",
       "      <td>1.397857</td>\n",
       "      <td>1.756019</td>\n",
       "      <td>2.454768</td>\n",
       "      <td>1.085127</td>\n",
       "      <td>1.074038</td>\n",
       "      <td>1.074038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>7.490658</td>\n",
       "      <td>24.691524</td>\n",
       "      <td>16.945851</td>\n",
       "      <td>7.490658</td>\n",
       "      <td>6.290603</td>\n",
       "      <td>2.495292</td>\n",
       "      <td>2.908773</td>\n",
       "      <td>1.134627</td>\n",
       "      <td>1.563861</td>\n",
       "      <td>0.602143</td>\n",
       "      <td>2.243981</td>\n",
       "      <td>1.917541</td>\n",
       "      <td>0.542564</td>\n",
       "      <td>0.925962</td>\n",
       "      <td>0.925962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.136160</td>\n",
       "      <td>10.863834</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.693869</td>\n",
       "      <td>1.306088</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.808117</td>\n",
       "      <td>1.191851</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>4.453046</td>\n",
       "      <td>8.546813</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.786730</td>\n",
       "      <td>1.213238</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.808117</td>\n",
       "      <td>1.191851</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r2</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.133566</td>\n",
       "      <td>3.866353</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>7.274624</td>\n",
       "      <td>8.725369</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.616242</td>\n",
       "      <td>2.383706</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>3.645617</td>\n",
       "      <td>4.229987</td>\n",
       "      <td>1.815629</td>\n",
       "      <td>1.845619</td>\n",
       "      <td>1.617539</td>\n",
       "      <td>6.949532</td>\n",
       "      <td>32.495614</td>\n",
       "      <td>5.105818</td>\n",
       "      <td>7.549525</td>\n",
       "      <td>6.349539</td>\n",
       "      <td>1.737605</td>\n",
       "      <td>2.486847</td>\n",
       "      <td>1.085127</td>\n",
       "      <td>1.076414</td>\n",
       "      <td>1.076414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>3.354383</td>\n",
       "      <td>3.743964</td>\n",
       "      <td>1.210420</td>\n",
       "      <td>2.154381</td>\n",
       "      <td>1.382461</td>\n",
       "      <td>7.050328</td>\n",
       "      <td>24.081120</td>\n",
       "      <td>15.317448</td>\n",
       "      <td>7.450325</td>\n",
       "      <td>6.650331</td>\n",
       "      <td>2.262395</td>\n",
       "      <td>1.885462</td>\n",
       "      <td>0.542564</td>\n",
       "      <td>0.923586</td>\n",
       "      <td>0.923586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.512190</td>\n",
       "      <td>2.487758</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.430020</td>\n",
       "      <td>9.569975</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.808117</td>\n",
       "      <td>1.191851</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.645999</td>\n",
       "      <td>1.353959</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.644238</td>\n",
       "      <td>8.355621</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.808117</td>\n",
       "      <td>1.191851</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">r3</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Sector</th>\n",
       "      <th>s1</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>3.885788</td>\n",
       "      <td>5.114110</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.245825</td>\n",
       "      <td>3.754093</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>9.063352</td>\n",
       "      <td>10.936642</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2</th>\n",
       "      <td>4.887794</td>\n",
       "      <td>5.109093</td>\n",
       "      <td>1.815629</td>\n",
       "      <td>2.487851</td>\n",
       "      <td>2.011632</td>\n",
       "      <td>3.603584</td>\n",
       "      <td>4.376721</td>\n",
       "      <td>1.701940</td>\n",
       "      <td>1.865326</td>\n",
       "      <td>1.648853</td>\n",
       "      <td>8.675647</td>\n",
       "      <td>36.728651</td>\n",
       "      <td>5.968198</td>\n",
       "      <td>8.075654</td>\n",
       "      <td>6.875668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s2b</th>\n",
       "      <td>4.112116</td>\n",
       "      <td>4.259649</td>\n",
       "      <td>1.815629</td>\n",
       "      <td>2.512149</td>\n",
       "      <td>1.988368</td>\n",
       "      <td>3.396416</td>\n",
       "      <td>3.786712</td>\n",
       "      <td>1.134627</td>\n",
       "      <td>2.134674</td>\n",
       "      <td>1.351147</td>\n",
       "      <td>9.324173</td>\n",
       "      <td>28.026252</td>\n",
       "      <td>16.276899</td>\n",
       "      <td>8.924176</td>\n",
       "      <td>8.124182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s3</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.384603</td>\n",
       "      <td>2.615335</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.573468</td>\n",
       "      <td>2.426480</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>6.392908</td>\n",
       "      <td>10.607086</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s4</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.512190</td>\n",
       "      <td>2.487758</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.693869</td>\n",
       "      <td>1.306088</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5.585424</td>\n",
       "      <td>9.414414</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>23.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                     r1                                              \\\n",
       "Level                  Sector                                               \n",
       "Item                       s1         s2        s2b         s3         s4   \n",
       "Region Level  Item                                                          \n",
       "r1     Sector s1    30.000000   7.771668  10.228326   6.000000   5.000000   \n",
       "              s2     8.509342  34.310533   6.052092   8.509342   6.709267   \n",
       "              s2b    7.490658  24.691524  16.945851   7.490658   6.290603   \n",
       "              s3     6.000000   5.136160  10.863834  25.000000   8.000000   \n",
       "              s4     5.000000   4.453046   8.546813   8.000000  22.000000   \n",
       "r2     Sector s1     5.000000   3.133566   3.866353   2.000000   2.000000   \n",
       "              s2     3.645617   4.229987   1.815629   1.845619   1.617539   \n",
       "              s2b    3.354383   3.743964   1.210420   2.154381   1.382461   \n",
       "              s3     2.000000   1.512190   2.487758   1.000000   1.000000   \n",
       "              s4     2.000000   1.645999   1.353959   1.000000   1.000000   \n",
       "r3     Sector s1     6.000000   3.885788   5.114110   3.000000   2.000000   \n",
       "              s2     4.887794   5.109093   1.815629   2.487851   2.011632   \n",
       "              s2b    4.112116   4.259649   1.815629   2.512149   1.988368   \n",
       "              s3     3.000000   2.384603   2.615335   2.000000   1.000000   \n",
       "              s4     2.000000   1.512190   2.487758   1.000000   1.000000   \n",
       "\n",
       "Region                     r2                                              \\\n",
       "Level                  Sector                                               \n",
       "Item                       s1         s2        s2b         s3         s4   \n",
       "Region Level  Item                                                          \n",
       "r1     Sector s1     4.000000   2.465351   2.534586   2.000000   1.000000   \n",
       "              s2     2.504708   3.821973   1.134627   1.436139   1.397857   \n",
       "              s2b    2.495292   2.908773   1.134627   1.563861   0.602143   \n",
       "              s3     2.000000   1.693869   1.306088   1.000000   1.000000   \n",
       "              s4     1.000000   0.786730   1.213238   1.000000   1.000000   \n",
       "r2     Sector s1    28.000000   7.274624   8.725369   6.000000   5.000000   \n",
       "              s2     6.949532  32.495614   5.105818   7.549525   6.349539   \n",
       "              s2b    7.050328  24.081120  15.317448   7.450325   6.650331   \n",
       "              s3     5.000000   5.430020   9.569975  24.000000   7.000000   \n",
       "              s4     4.000000   4.644238   8.355621   7.000000  21.000000   \n",
       "r3     Sector s1     5.000000   3.245825   3.754093   2.000000   2.000000   \n",
       "              s2     3.603584   4.376721   1.701940   1.865326   1.648853   \n",
       "              s2b    3.396416   3.786712   1.134627   2.134674   1.351147   \n",
       "              s3     2.000000   1.573468   2.426480   1.000000   1.000000   \n",
       "              s4     2.000000   1.693869   1.306088   1.000000   1.000000   \n",
       "\n",
       "Region                     r3                                              \n",
       "Level                  Sector                                              \n",
       "Item                       s1         s2        s2b         s3         s4  \n",
       "Region Level  Item                                                         \n",
       "r1     Sector s1     3.000000   1.616242   2.383706   1.000000   1.000000  \n",
       "              s2     1.756019   2.454768   1.085127   1.074038   1.074038  \n",
       "              s2b    2.243981   1.917541   0.542564   0.925962   0.925962  \n",
       "              s3     1.000000   0.808117   1.191851   1.000000   1.000000  \n",
       "              s4     1.000000   0.808117   1.191851   1.000000   1.000000  \n",
       "r2     Sector s1     3.000000   1.616242   2.383706   1.000000   1.000000  \n",
       "              s2     1.737605   2.486847   1.085127   1.076414   1.076414  \n",
       "              s2b    2.262395   1.885462   0.542564   0.923586   0.923586  \n",
       "              s3     1.000000   0.808117   1.191851   1.000000   1.000000  \n",
       "              s4     1.000000   0.808117   1.191851   1.000000   1.000000  \n",
       "r3     Sector s1    32.000000   9.063352  10.936642   7.000000   6.000000  \n",
       "              s2     8.675647  36.728651   5.968198   8.075654   6.875668  \n",
       "              s2b    9.324173  28.026252  16.276899   8.924176   8.124182  \n",
       "              s3     7.000000   6.392908  10.607086  26.000000   8.000000  \n",
       "              s4     6.000000   5.585424   9.414414   8.000000  23.000000  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expanded_db_split.Z"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mario",
   "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.13.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
