{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "from_excel-01",
   "metadata": {},
   "source": [
    "# Excel custom parser walkthrough\n",
    "\n",
    "This notebook shows how to parse existing MARIO-readable Excel workbooks with `mario.parse_from_excel(...)`. It uses the small test workbooks shipped with MARIO, so the focus is on reading a completed workbook rather than generating a new template."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-02",
   "metadata": {},
   "source": [
    "## What this notebook covers\n",
    "\n",
    "- parse standard IOT and SUT Excel workbooks;\n",
    "- parse Excel workbooks that need explicit `matrix_layouts`;\n",
    "- inspect the parsed baseline blocks and index labels.\n",
    "\n",
    "Creating a blank workbook from sets and units is covered in the dedicated workflow on providing a custom database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-03",
   "metadata": {},
   "source": [
    "## Example workbooks\n",
    "\n",
    "The examples use these packaged workbooks:\n",
    "\n",
    "- `test_IOT_standard.xlsx`: standard IOT layout;\n",
    "- `test_IOT_special.xlsx`: IOT layout with extra semantic levels on `V` and `E`;\n",
    "- `test_SUT_standard.xlsx`: standard SUT layout;\n",
    "- `test_SUT_special.xlsx`: SUT layout with extra semantic levels on satellite extensions.\n",
    "\n",
    "The code below uses paths relative to this notebook source location in the MARIO repository. If you run the notebook elsewhere, replace the path strings with the location of your local Excel files."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c0dec23",
   "metadata": {},
   "source": [
    "## Main entry point and key arguments\n",
    "\n",
    "Use `mario.parse_from_excel(...)` when `path` points to one completed MARIO-readable workbook.\n",
    "\n",
    "Key arguments:\n",
    "\n",
    "- `path`: workbook to parse;\n",
    "- `table`: choose `\"IOT\"` or `\"SUT\"`;\n",
    "- `mode`: choose `\"flows\"` or `\"coefficients\"`;\n",
    "- `data_sheet`: optional sheet selector for the table payload;\n",
    "- `unit_sheet`: optional sheet selector for the units sheet;\n",
    "- `matrix_layouts`: optional semantic declaration for non-standard matrix layouts;\n",
    "- `tech_assumption`: optional `SUT` selector for `IT` or `PT`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8249f0f8",
   "metadata": {},
   "source": [
    "## Workbook structure and packaged examples\n",
    "\n",
    "`path` must point to one Excel workbook.\n",
    "\n",
    "```text\n",
    "custom_database.xlsx\n",
    "├── data sheet\n",
    "└── units sheet\n",
    "```\n",
    "\n",
    "The data sheet can use the canonical MARIO matrix layout exported by MARIO or a flat template generated with `mario.write_parse_template(...)`. The units sheet must contain the units for the labels used by sectors, activities, commodities, factors, final demand, and satellites.\n",
    "\n",
    "Packaged example workbooks used in this notebook:\n",
    "\n",
    "- [test_IOT_standard.xlsx](../../../_static/data/test_tables/test_IOT_standard.xlsx)\n",
    "- [test_IOT_special.xlsx](../../../_static/data/test_tables/test_IOT_special.xlsx)\n",
    "- [test_SUT_standard.xlsx](../../../_static/data/test_tables/test_SUT_standard.xlsx)\n",
    "- [test_SUT_special.xlsx](../../../_static/data/test_tables/test_SUT_special.xlsx)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28d0f045",
   "metadata": {},
   "source": [
    "## Standard and special layouts\n",
    "\n",
    "In the standard workbook layout, the table uses three index levels on both rows and columns: region, level name, and level label. Use `mode=\"flows\"` or `mode=\"coefficients\"` to tell MARIO how to interpret the numbers.\n",
    "\n",
    "For non-standard layouts, use `matrix_layouts=` to declare extra semantic layers, especially on `V` and `E`. `VY` and `EY` inherit the same extra layout as `V` and `E`, so you do not declare them separately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "from_excel-04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mario"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-06",
   "metadata": {},
   "source": [
    "## Parse a standard IOT workbook\n",
    "\n",
    "For a standard IOT workbook, only the file path, table kind and mode are required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "from_excel-07",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading IOT flows from /path/to/test_IOT_standard.xlsx.\n",
      "INFO Parser: state payload ready with 6 canonical blocks.\n",
      "INFO Parser: excel state ready for IOT.\n",
      "INFO Metadata: initialized.\n"
     ]
    }
   ],
   "source": [
    "iot = mario.parse_from_excel(\n",
    "    path=\"/path/to/test_IOT_standard.xlsx\",\n",
    "    table=\"IOT\",\n",
    "    mode=\"flows\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9d3f107e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'IOT'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot.meta.table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "644da108",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Reg1', 'Reg2']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot.get_index(\"Region\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b97093fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Agriculture', 'Services', 'Industry']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot.get_index(\"Sector\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d80180f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>Region</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Reg1</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Reg2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Level</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Sector</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Services</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Services</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item</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>Taxes</th>\n",
       "      <td>5.517199e+05</td>\n",
       "      <td>3.047096e+06</td>\n",
       "      <td>5.010693e+06</td>\n",
       "      <td>4908.415181</td>\n",
       "      <td>36852.481913</td>\n",
       "      <td>143818.770826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wages</th>\n",
       "      <td>2.576824e+06</td>\n",
       "      <td>6.311953e+06</td>\n",
       "      <td>3.339472e+07</td>\n",
       "      <td>9698.892565</td>\n",
       "      <td>213257.479272</td>\n",
       "      <td>754513.227394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Capital</th>\n",
       "      <td>2.887880e+06</td>\n",
       "      <td>5.616641e+06</td>\n",
       "      <td>2.366083e+07</td>\n",
       "      <td>30784.073998</td>\n",
       "      <td>101949.241428</td>\n",
       "      <td>529918.086220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region           Reg1                                      Reg2  \\\n",
       "Level          Sector                                    Sector   \n",
       "Item      Agriculture      Industry      Services   Agriculture   \n",
       "Item                                                              \n",
       "Taxes    5.517199e+05  3.047096e+06  5.010693e+06   4908.415181   \n",
       "Wages    2.576824e+06  6.311953e+06  3.339472e+07   9698.892565   \n",
       "Capital  2.887880e+06  5.616641e+06  2.366083e+07  30784.073998   \n",
       "\n",
       "Region                                 \n",
       "Level                                  \n",
       "Item          Industry       Services  \n",
       "Item                                   \n",
       "Taxes     36852.481913  143818.770826  \n",
       "Wages    213257.479272  754513.227394  \n",
       "Capital  101949.241428  529918.086220  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot.V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-08",
   "metadata": {},
   "source": [
    "## Parse an IOT workbook with custom matrix layouts\n",
    "\n",
    "Use `matrix_layouts` when specific matrices carry extra semantic levels beyond the default layout. In this example, `V` is indexed by region and sector, while `E` is indexed by region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "from_excel-09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading IOT flows from /path/to/test_IOT_special.xlsx.\n",
      "INFO Parser: state payload ready with 6 canonical blocks.\n",
      "INFO Parser: excel state ready for IOT.\n",
      "INFO Metadata: initialized.\n"
     ]
    }
   ],
   "source": [
    "iot_special = mario.parse_from_excel(\n",
    "    path=\"/path/to/test_IOT_special.xlsx\",\n",
    "    table=\"IOT\",\n",
    "    mode=\"flows\",\n",
    "    matrix_layouts={\n",
    "        \"V\": (\"Region\", \"Sector\"),\n",
    "        \"E\": \"Region\",\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6f1c14bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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=\"3\" halign=\"left\">Reg1</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Reg2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Sector</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Services</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Services</th>\n",
       "      <th>Industry</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Factor of production</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=\"3\" valign=\"top\">Reg1</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>2.206879e+05</td>\n",
       "      <td>2.004277e+06</td>\n",
       "      <td>1.218838e+06</td>\n",
       "      <td>1963.366072</td>\n",
       "      <td>57527.508330</td>\n",
       "      <td>14740.992765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>5.517199e+04</td>\n",
       "      <td>5.010693e+05</td>\n",
       "      <td>3.047096e+05</td>\n",
       "      <td>490.841518</td>\n",
       "      <td>14381.877083</td>\n",
       "      <td>3685.248191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>2.758599e+04</td>\n",
       "      <td>2.505346e+05</td>\n",
       "      <td>1.523548e+05</td>\n",
       "      <td>245.420759</td>\n",
       "      <td>7190.938541</td>\n",
       "      <td>1842.624096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reg2</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>1.931020e+05</td>\n",
       "      <td>1.753742e+06</td>\n",
       "      <td>1.066483e+06</td>\n",
       "      <td>1717.945313</td>\n",
       "      <td>50336.569789</td>\n",
       "      <td>12898.368670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>2.758599e+04</td>\n",
       "      <td>2.505346e+05</td>\n",
       "      <td>1.523548e+05</td>\n",
       "      <td>245.420759</td>\n",
       "      <td>7190.938541</td>\n",
       "      <td>1842.624096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Taxes</th>\n",
       "      <td>2.758599e+04</td>\n",
       "      <td>2.505346e+05</td>\n",
       "      <td>1.523548e+05</td>\n",
       "      <td>245.420759</td>\n",
       "      <td>7190.938541</td>\n",
       "      <td>1842.624096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reg1</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Wages</th>\n",
       "      <td>1.030730e+06</td>\n",
       "      <td>1.335789e+07</td>\n",
       "      <td>2.524781e+06</td>\n",
       "      <td>3879.557026</td>\n",
       "      <td>301805.290958</td>\n",
       "      <td>85302.991709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Wages</th>\n",
       "      <td>2.576824e+05</td>\n",
       "      <td>3.339472e+06</td>\n",
       "      <td>6.311953e+05</td>\n",
       "      <td>969.889257</td>\n",
       "      <td>75451.322739</td>\n",
       "      <td>21325.747927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Wages</th>\n",
       "      <td>1.288412e+05</td>\n",
       "      <td>1.669736e+06</td>\n",
       "      <td>3.155977e+05</td>\n",
       "      <td>484.944628</td>\n",
       "      <td>37725.661370</td>\n",
       "      <td>10662.873964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reg2</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Wages</th>\n",
       "      <td>9.018884e+05</td>\n",
       "      <td>1.168815e+07</td>\n",
       "      <td>2.209184e+06</td>\n",
       "      <td>3394.612398</td>\n",
       "      <td>264079.629588</td>\n",
       "      <td>74640.117745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Wages</th>\n",
       "      <td>1.288412e+05</td>\n",
       "      <td>1.669736e+06</td>\n",
       "      <td>3.155977e+05</td>\n",
       "      <td>484.944628</td>\n",
       "      <td>37725.661370</td>\n",
       "      <td>10662.873964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Wages</th>\n",
       "      <td>1.288412e+05</td>\n",
       "      <td>1.669736e+06</td>\n",
       "      <td>3.155977e+05</td>\n",
       "      <td>484.944628</td>\n",
       "      <td>37725.661370</td>\n",
       "      <td>10662.873964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reg1</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Capital</th>\n",
       "      <td>1.155152e+06</td>\n",
       "      <td>9.464332e+06</td>\n",
       "      <td>2.246656e+06</td>\n",
       "      <td>12313.629599</td>\n",
       "      <td>211967.234488</td>\n",
       "      <td>40779.696571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Capital</th>\n",
       "      <td>2.887880e+05</td>\n",
       "      <td>2.366083e+06</td>\n",
       "      <td>5.616641e+05</td>\n",
       "      <td>3078.407400</td>\n",
       "      <td>52991.808622</td>\n",
       "      <td>10194.924143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Capital</th>\n",
       "      <td>1.443940e+05</td>\n",
       "      <td>1.183041e+06</td>\n",
       "      <td>2.808320e+05</td>\n",
       "      <td>1539.203700</td>\n",
       "      <td>26495.904311</td>\n",
       "      <td>5097.462071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reg2</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Capital</th>\n",
       "      <td>1.010758e+06</td>\n",
       "      <td>8.281290e+06</td>\n",
       "      <td>1.965824e+06</td>\n",
       "      <td>10774.425899</td>\n",
       "      <td>185471.330177</td>\n",
       "      <td>35682.234500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Capital</th>\n",
       "      <td>1.443940e+05</td>\n",
       "      <td>1.183041e+06</td>\n",
       "      <td>2.808320e+05</td>\n",
       "      <td>1539.203700</td>\n",
       "      <td>26495.904311</td>\n",
       "      <td>5097.462071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <th>Capital</th>\n",
       "      <td>1.443940e+05</td>\n",
       "      <td>1.183041e+06</td>\n",
       "      <td>2.808320e+05</td>\n",
       "      <td>1539.203700</td>\n",
       "      <td>26495.904311</td>\n",
       "      <td>5097.462071</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                                           Reg1                \\\n",
       "Sector                                    Agriculture      Services   \n",
       "Region Sector      Factor of production                               \n",
       "Reg1   Agriculture Taxes                 2.206879e+05  2.004277e+06   \n",
       "       Services    Taxes                 5.517199e+04  5.010693e+05   \n",
       "       Industry    Taxes                 2.758599e+04  2.505346e+05   \n",
       "Reg2   Agriculture Taxes                 1.931020e+05  1.753742e+06   \n",
       "       Services    Taxes                 2.758599e+04  2.505346e+05   \n",
       "       Industry    Taxes                 2.758599e+04  2.505346e+05   \n",
       "Reg1   Agriculture Wages                 1.030730e+06  1.335789e+07   \n",
       "       Services    Wages                 2.576824e+05  3.339472e+06   \n",
       "       Industry    Wages                 1.288412e+05  1.669736e+06   \n",
       "Reg2   Agriculture Wages                 9.018884e+05  1.168815e+07   \n",
       "       Services    Wages                 1.288412e+05  1.669736e+06   \n",
       "       Industry    Wages                 1.288412e+05  1.669736e+06   \n",
       "Reg1   Agriculture Capital               1.155152e+06  9.464332e+06   \n",
       "       Services    Capital               2.887880e+05  2.366083e+06   \n",
       "       Industry    Capital               1.443940e+05  1.183041e+06   \n",
       "Reg2   Agriculture Capital               1.010758e+06  8.281290e+06   \n",
       "       Services    Capital               1.443940e+05  1.183041e+06   \n",
       "       Industry    Capital               1.443940e+05  1.183041e+06   \n",
       "\n",
       "Region                                                         Reg2  \\\n",
       "Sector                                       Industry   Agriculture   \n",
       "Region Sector      Factor of production                               \n",
       "Reg1   Agriculture Taxes                 1.218838e+06   1963.366072   \n",
       "       Services    Taxes                 3.047096e+05    490.841518   \n",
       "       Industry    Taxes                 1.523548e+05    245.420759   \n",
       "Reg2   Agriculture Taxes                 1.066483e+06   1717.945313   \n",
       "       Services    Taxes                 1.523548e+05    245.420759   \n",
       "       Industry    Taxes                 1.523548e+05    245.420759   \n",
       "Reg1   Agriculture Wages                 2.524781e+06   3879.557026   \n",
       "       Services    Wages                 6.311953e+05    969.889257   \n",
       "       Industry    Wages                 3.155977e+05    484.944628   \n",
       "Reg2   Agriculture Wages                 2.209184e+06   3394.612398   \n",
       "       Services    Wages                 3.155977e+05    484.944628   \n",
       "       Industry    Wages                 3.155977e+05    484.944628   \n",
       "Reg1   Agriculture Capital               2.246656e+06  12313.629599   \n",
       "       Services    Capital               5.616641e+05   3078.407400   \n",
       "       Industry    Capital               2.808320e+05   1539.203700   \n",
       "Reg2   Agriculture Capital               1.965824e+06  10774.425899   \n",
       "       Services    Capital               2.808320e+05   1539.203700   \n",
       "       Industry    Capital               2.808320e+05   1539.203700   \n",
       "\n",
       "Region                                                                \n",
       "Sector                                        Services      Industry  \n",
       "Region Sector      Factor of production                               \n",
       "Reg1   Agriculture Taxes                  57527.508330  14740.992765  \n",
       "       Services    Taxes                  14381.877083   3685.248191  \n",
       "       Industry    Taxes                   7190.938541   1842.624096  \n",
       "Reg2   Agriculture Taxes                  50336.569789  12898.368670  \n",
       "       Services    Taxes                   7190.938541   1842.624096  \n",
       "       Industry    Taxes                   7190.938541   1842.624096  \n",
       "Reg1   Agriculture Wages                 301805.290958  85302.991709  \n",
       "       Services    Wages                  75451.322739  21325.747927  \n",
       "       Industry    Wages                  37725.661370  10662.873964  \n",
       "Reg2   Agriculture Wages                 264079.629588  74640.117745  \n",
       "       Services    Wages                  37725.661370  10662.873964  \n",
       "       Industry    Wages                  37725.661370  10662.873964  \n",
       "Reg1   Agriculture Capital               211967.234488  40779.696571  \n",
       "       Services    Capital                52991.808622  10194.924143  \n",
       "       Industry    Capital                26495.904311   5097.462071  \n",
       "Reg2   Agriculture Capital               185471.330177  35682.234500  \n",
       "       Services    Capital                26495.904311   5097.462071  \n",
       "       Industry    Capital                26495.904311   5097.462071  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot_special.V"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-10",
   "metadata": {},
   "source": [
    "## Parse a standard SUT workbook\n",
    "\n",
    "For SUT workbooks, pass the table kind as `\"SUT\"`. You can also pass `tech_assumption=` when you want MARIO to record the technology assumption used downstream."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "from_excel-11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading SUT flows from /path/to/test_SUT_standard.xlsx.\n",
      "INFO Parser: state payload ready with 10 canonical blocks.\n",
      "INFO Parser: excel state ready for SUT.\n",
      "INFO Metadata: initialized.\n"
     ]
    }
   ],
   "source": [
    "sut = mario.parse_from_excel(\n",
    "    path=\"/path/to/test_SUT_standard.xlsx\",\n",
    "    table=\"SUT\",\n",
    "    mode=\"flows\",\n",
    "    tech_assumption=\"IT\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "da9643c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SUT'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sut.meta.table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9c0102b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['EY',\n",
       " 'Ea',\n",
       " 'Ec',\n",
       " 'S',\n",
       " 'U',\n",
       " 'VY',\n",
       " 'Va',\n",
       " 'Vc',\n",
       " 'Xa',\n",
       " 'Xc',\n",
       " 'Ya',\n",
       " 'Yc',\n",
       " 'ea',\n",
       " 's',\n",
       " 'u',\n",
       " 'wcc']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(sut[\"baseline\"].keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dfa5cd6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Resolver: resolving E for baseline.\n",
      "INFO Resolver: trying E via concat.\n",
      "INFO Resolver: resolved E via concat.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>Region</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 2</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Level</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Activity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Activity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Commodity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Commodity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item</th>\n",
       "      <th>Manufacturing</th>\n",
       "      <th>Services</th>\n",
       "      <th>Manufacturing</th>\n",
       "      <th>Services</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Services</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Services</th>\n",
       "    </tr>\n",
       "    <tr>\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Employment</th>\n",
       "      <td>3.752922e+06</td>\n",
       "      <td>6.547713e+05</td>\n",
       "      <td>1.926382e+04</td>\n",
       "      <td>3.939633e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO2</th>\n",
       "      <td>2.220336e+13</td>\n",
       "      <td>8.884627e+12</td>\n",
       "      <td>1.910855e+11</td>\n",
       "      <td>4.839515e+10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region          Region 1                    Region 2                Region 1  \\\n",
       "Level           Activity                    Activity               Commodity   \n",
       "Item       Manufacturing      Services Manufacturing      Services     Goods   \n",
       "Item                                                                           \n",
       "Employment  3.752922e+06  6.547713e+05  1.926382e+04  3.939633e+03       0.0   \n",
       "CO2         2.220336e+13  8.884627e+12  1.910855e+11  4.839515e+10       0.0   \n",
       "\n",
       "Region               Region 2           \n",
       "Level               Commodity           \n",
       "Item       Services     Goods Services  \n",
       "Item                                    \n",
       "Employment      0.0       0.0      0.0  \n",
       "CO2             0.0       0.0      0.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sut.E"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-12",
   "metadata": {},
   "source": [
    "## Parse a SUT workbook with custom matrix layouts\n",
    "\n",
    "The same `matrix_layouts` mechanism applies to SUT workbooks. Here, the satellite-account matrices are declared with region and commodity detail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "from_excel-13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading SUT flows from /path/to/test_SUT_special.xlsx.\n",
      "INFO Parser: state payload ready with 10 canonical blocks.\n",
      "INFO Parser: excel state ready for SUT.\n",
      "INFO Metadata: initialized.\n"
     ]
    }
   ],
   "source": [
    "sut_special = mario.parse_from_excel(\n",
    "    path=\"/path/to/test_SUT_special.xlsx\",\n",
    "    table=\"SUT\",\n",
    "    mode=\"flows\",\n",
    "    tech_assumption=\"IT\",\n",
    "    matrix_layouts={\n",
    "        \"E\": (\"Region\", \"Commodity\"),\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "db583213",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Resolver: resolving E for baseline.\n",
      "INFO Resolver: trying E via concat.\n",
      "INFO Resolver: resolved E via concat.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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=\"2\" halign=\"left\">Region 1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 2</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Region 2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Level</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Activity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Activity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Commodity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Commodity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Item</th>\n",
       "      <th>Manufacturing</th>\n",
       "      <th>Services</th>\n",
       "      <th>Manufacturing</th>\n",
       "      <th>Services</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Services</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Services</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Commodity</th>\n",
       "      <th>Satellite account</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=\"2\" valign=\"top\">Region 1</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Employment</th>\n",
       "      <td>1.125877e+06</td>\n",
       "      <td>1.964314e+05</td>\n",
       "      <td>5.779146e+03</td>\n",
       "      <td>1.181890e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Employment</th>\n",
       "      <td>7.505844e+05</td>\n",
       "      <td>1.309543e+05</td>\n",
       "      <td>3.852764e+03</td>\n",
       "      <td>7.879266e+02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Region 2</th>\n",
       "      <th>Goods</th>\n",
       "      <th>Employment</th>\n",
       "      <td>1.501169e+06</td>\n",
       "      <td>2.619085e+05</td>\n",
       "      <td>7.705528e+03</td>\n",
       "      <td>1.575853e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>Employment</th>\n",
       "      <td>3.752922e+05</td>\n",
       "      <td>6.547713e+04</td>\n",
       "      <td>1.926382e+03</td>\n",
       "      <td>3.939633e+02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Region 1</th>\n",
       "      <th>Goods</th>\n",
       "      <th>CO2</th>\n",
       "      <td>7.771177e+12</td>\n",
       "      <td>3.109620e+12</td>\n",
       "      <td>6.687991e+10</td>\n",
       "      <td>1.693830e+10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>CO2</th>\n",
       "      <td>3.330504e+12</td>\n",
       "      <td>1.332694e+12</td>\n",
       "      <td>2.866282e+10</td>\n",
       "      <td>7.259272e+09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Region 2</th>\n",
       "      <th>Goods</th>\n",
       "      <th>CO2</th>\n",
       "      <td>8.881345e+12</td>\n",
       "      <td>3.553851e+12</td>\n",
       "      <td>7.643418e+10</td>\n",
       "      <td>1.935806e+10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <th>CO2</th>\n",
       "      <td>2.220336e+12</td>\n",
       "      <td>8.884627e+11</td>\n",
       "      <td>1.910855e+10</td>\n",
       "      <td>4.839515e+09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                                    Region 1                \\\n",
       "Level                                     Activity                 \n",
       "Item                                 Manufacturing      Services   \n",
       "Region   Commodity Satellite account                               \n",
       "Region 1 Goods     Employment         1.125877e+06  1.964314e+05   \n",
       "         Services  Employment         7.505844e+05  1.309543e+05   \n",
       "Region 2 Goods     Employment         1.501169e+06  2.619085e+05   \n",
       "         Services  Employment         3.752922e+05  6.547713e+04   \n",
       "Region 1 Goods     CO2                7.771177e+12  3.109620e+12   \n",
       "         Services  CO2                3.330504e+12  1.332694e+12   \n",
       "Region 2 Goods     CO2                8.881345e+12  3.553851e+12   \n",
       "         Services  CO2                2.220336e+12  8.884627e+11   \n",
       "\n",
       "Region                                    Region 2                Region 1  \\\n",
       "Level                                     Activity               Commodity   \n",
       "Item                                 Manufacturing      Services     Goods   \n",
       "Region   Commodity Satellite account                                         \n",
       "Region 1 Goods     Employment         5.779146e+03  1.181890e+03       0.0   \n",
       "         Services  Employment         3.852764e+03  7.879266e+02       0.0   \n",
       "Region 2 Goods     Employment         7.705528e+03  1.575853e+03       0.0   \n",
       "         Services  Employment         1.926382e+03  3.939633e+02       0.0   \n",
       "Region 1 Goods     CO2                6.687991e+10  1.693830e+10       0.0   \n",
       "         Services  CO2                2.866282e+10  7.259272e+09       0.0   \n",
       "Region 2 Goods     CO2                7.643418e+10  1.935806e+10       0.0   \n",
       "         Services  CO2                1.910855e+10  4.839515e+09       0.0   \n",
       "\n",
       "Region                                         Region 2           \n",
       "Level                                         Commodity           \n",
       "Item                                 Services     Goods Services  \n",
       "Region   Commodity Satellite account                              \n",
       "Region 1 Goods     Employment             0.0       0.0      0.0  \n",
       "         Services  Employment             0.0       0.0      0.0  \n",
       "Region 2 Goods     Employment             0.0       0.0      0.0  \n",
       "         Services  Employment             0.0       0.0      0.0  \n",
       "Region 1 Goods     CO2                    0.0       0.0      0.0  \n",
       "         Services  CO2                    0.0       0.0      0.0  \n",
       "Region 2 Goods     CO2                    0.0       0.0      0.0  \n",
       "         Services  CO2                    0.0       0.0      0.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sut_special.E"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "from_excel-14",
   "metadata": {},
   "source": [
    "## Practical notes\n",
    "\n",
    "- `mode=\"flows\"` reads flow matrices; use `mode=\"coefficients\"` only for coefficient workbooks.\n",
    "- `matrix_layouts` must use MARIO semantic index names, such as `Region`, `Sector`, `Activity`, `Commodity`, `Factor of production`, `Final demand`, and `Satellite account`.\n",
    "- Fully empty matrices are accepted and filled with zeros; partially empty matrices are treated as invalid input."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a28d2955",
   "metadata": {},
   "source": [
    "## Layout notes and caveats\n",
    "\n",
    "The workbook that `mario.parse_from_excel(...)` reads is always one Excel file with at least a data sheet and a units sheet.\n",
    "\n",
    "In the standard layout, rows and columns use three index levels: region, level name, and level label. The units sheet lists the units for sectors or activities or commodities, factors of production, and satellite accounts used by the workbook. If the sheet names are not the defaults, use `data_sheet=` and `unit_sheet=` explicitly.\n",
    "\n",
    "For non-standard layouts, `matrix_layouts=` declares the extra semantic levels carried by matrices such as `V` and `E`. When extra levels are defined for `V` or `E`, MARIO applies the same logic to `VY` and `EY`, so they do not need separate declarations.\n",
    "\n",
    "Practical caveats:\n",
    "\n",
    "- Excel is usually the easiest custom entry point, but it is not the best format for large roundtrip workflows.\n",
    "- `mode=` is never inferred automatically: choose `\"flows\"` or `\"coefficients\"` explicitly.\n",
    "- When you define extra indices for `E` or `V`, provide the complete label set under those indices, not only the labels visible in one sub-block."
   ]
  }
 ],
 "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
}
