{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1c72f232",
   "metadata": {},
   "source": [
    "# Work with your custom database\n",
    "\n",
    "This notebook shows the smallest end-to-end workflow to create a MARIO-readable Excel template and parse it back into a database object.\n",
    "The examples use compact IOT and SUT structures whose labels mirror the packaged MARIO test tables, so the focus stays on the template format rather than on the data values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a40bc6fd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-26T22:41:26.094260Z",
     "iopub.status.busy": "2026-04-26T22:41:26.093898Z",
     "iopub.status.idle": "2026-04-26T22:41:28.315212Z",
     "shell.execute_reply": "2026-04-26T22:41:28.314842Z"
    }
   },
   "outputs": [],
   "source": [
    "import mario"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e40a5b90",
   "metadata": {},
   "source": [
    "## Write an IOT template\n",
    "\n",
    "The first example generates a minimal IOT workbook with regions, sectors, final demand, factors, and satellite accounts already declared."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b95895dc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-26T22:41:28.318266Z",
     "iopub.status.busy": "2026-04-26T22:41:28.317960Z",
     "iopub.status.idle": "2026-04-26T22:41:28.868517Z",
     "shell.execute_reply": "2026-04-26T22:41:28.868244Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Metadata: initialized by dataframes.\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 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 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"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'/Users/lorenzorinaldi/Documents/GitHub/MARIO/mario/test/supporting_files/custom_iot_template.xlsx'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mario.write_parse_template(\n",
    "    \"/path/to/custom_iot_template.xlsx\",\n",
    "    table=\"IOT\",\n",
    "    sets={\n",
    "        \"regions\": [\"Reg1\", \"Reg2\"],\n",
    "        \"sectors\": [\"Agriculture\", \"Services\"],\n",
    "        \"final demand\": [\"Final demand\"],\n",
    "        \"factors\": [\"Taxes\", \"Wages\"],\n",
    "        \"satellites\": [\"CO2\"],\n",
    "    },\n",
    "    units={\n",
    "        \"sectors\": \"M EUR\",\n",
    "        \"factors\": \"M EUR\",\n",
    "        \"satellites\": \"kg\",\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e50e0c6",
   "metadata": {},
   "source": [
    "The figure below shows the structure of the Excel template that MARIO writes for you.\n",
    "\n",
    "![custom database](../../_static/images/custom_template.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f15abfee",
   "metadata": {},
   "source": [
    "## Parse the template back\n",
    "\n",
    "After the workbook is filled, `parse_from_excel()` reads it back and builds a database object with the sets and matrices implied by the template.\n",
    "In this example the numeric cells are empty, so MARIO reads them as zeros. That is still enough to validate the structure before filling real data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04c0512e",
   "metadata": {},
   "source": [
    "### Download the packaged example workbooks\n",
    "\n",
    "The exact workbooks used in this notebook are available here:\n",
    "\n",
    "- [IOT template workbook](../../_static/data/supporting_files/custom_iot_template.xlsx)\n",
    "- [Filled IOT workbook](../../_static/data/supporting_files/custom_iot_filled.xlsx)\n",
    "- [SUT template workbook](../../_static/data/supporting_files/custom_sut_template.xlsx)\n",
    "- [Filled SUT workbook](../../_static/data/supporting_files/custom_sut_filled.xlsx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71bd13a8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-26T22:41:28.870399Z",
     "iopub.status.busy": "2026-04-26T22:41:28.870277Z",
     "iopub.status.idle": "2026-04-26T22:41:28.901870Z",
     "shell.execute_reply": "2026-04-26T22:41:28.901568Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Parser: excel reading IOT flows from /Users/lorenzorinaldi/Documents/GitHub/MARIO/mario/test/supporting_files/custom_iot_filled.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": [
       "{'Factor of production': ['Taxes', 'Wages'],\n",
       " 'Satellite account': ['CO2'],\n",
       " 'Consumption category': ['Final demand'],\n",
       " 'Region': ['Reg1', 'Reg2'],\n",
       " 'Sector': ['Agriculture', 'Services']}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = mario.parse_from_excel(\n",
    "    path=\"/path/to/custom_iot_filled.xlsx\",\n",
    "    table=\"IOT\",\n",
    "    mode=\"flows\",\n",
    ")\n",
    "db.get_index(\"all\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cb5ff100",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-26T22:41:28.903575Z",
     "iopub.status.busy": "2026-04-26T22:41:28.903417Z",
     "iopub.status.idle": "2026-04-26T22:41:28.932432Z",
     "shell.execute_reply": "2026-04-26T22:41:28.932160Z"
    }
   },
   "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>Region</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Reg1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Reg2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Sector</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Services</th>\n",
       "      <th>Agriculture</th>\n",
       "      <th>Services</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Sector</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\">Reg1</th>\n",
       "      <th>Agriculture</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Reg2</th>\n",
       "      <th>Agriculture</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                    Reg1                 Reg2         \n",
       "Sector             Agriculture Services Agriculture Services\n",
       "Region Sector                                               \n",
       "Reg1   Agriculture         1.0      1.0         1.0      1.0\n",
       "       Services            1.0      1.0         1.0      1.0\n",
       "Reg2   Agriculture         1.0      1.0         1.0      1.0\n",
       "       Services            1.0      1.0         1.0      1.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "409e57cd",
   "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>Region</th>\n",
       "      <th>Reg1</th>\n",
       "      <th>Reg2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Consumption category</th>\n",
       "      <th>Final demand</th>\n",
       "      <th>Final demand</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th>Sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Reg1</th>\n",
       "      <th>Agriculture</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Reg2</th>\n",
       "      <th>Agriculture</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Services</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Region                       Reg1         Reg2\n",
       "Consumption category Final demand Final demand\n",
       "Region Sector                                 \n",
       "Reg1   Agriculture            4.0          4.0\n",
       "       Services               4.0          4.0\n",
       "Reg2   Agriculture            4.0          4.0\n",
       "       Services               4.0          4.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Y"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d891871b",
   "metadata": {},
   "source": [
    "## Write a SUT template\n",
    "\n",
    "The same pattern works for SUT tables: here the template is defined with activities and commodities instead of a single sector set, and then parsed back in the same way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "967a852c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-04-26T22:41:28.934062Z",
     "iopub.status.busy": "2026-04-26T22:41:28.933943Z",
     "iopub.status.idle": "2026-04-26T22:41:29.993528Z",
     "shell.execute_reply": "2026-04-26T22:41:29.993247Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO Metadata: initialized by dataframes.\n",
      "INFO Resolver: resolving z for baseline.\n",
      "INFO Resolver: trying z via concat.\n",
      "INFO Resolver: resolved z via concat.\n",
      "INFO Resolver: resolving v for baseline.\n",
      "INFO Resolver: trying v via concat.\n",
      "INFO Resolver: resolved v via concat.\n",
      "INFO Resolver: resolving e for baseline.\n",
      "INFO Resolver: trying e via concat.\n",
      "INFO Resolver: resolved e via concat.\n",
      "INFO Parser: excel reading SUT flows from /Users/lorenzorinaldi/Documents/GitHub/MARIO/mario/test/supporting_files/custom_sut_filled.xlsx.\n",
      "INFO Parser: state payload ready with 10 canonical blocks.\n",
      "INFO Parser: excel state ready for SUT.\n",
      "INFO Metadata: initialized.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'Activity': ['Manufacturing', 'Services'],\n",
       " 'Commodity': ['Goods', 'Services'],\n",
       " 'Factor of production': ['Taxes'],\n",
       " 'Satellite account': ['CO2'],\n",
       " 'Consumption category': ['Final demand'],\n",
       " 'Region': ['Region 1']}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mario.write_parse_template(\n",
    "    path=\"/path/to/custom_sut_template.xlsx\",\n",
    "    table=\"SUT\",\n",
    "    sets={\n",
    "        \"regions\": [\"Region 1\"],\n",
    "        \"activities\": [\"Manufacturing\", \"Services\"],\n",
    "        \"commodities\": [\"Goods\", \"Services\"],\n",
    "        \"final demand\": [\"Final demand\"],\n",
    "        \"factors\": [\"Taxes\"],\n",
    "        \"satellites\": [\"CO2\"],\n",
    "    },\n",
    "    units={\n",
    "        \"activities\": \"M EUR\",\n",
    "        \"commodities\": \"M EUR\",\n",
    "        \"factors\": \"M EUR\",\n",
    "        \"satellites\": \"kg\",\n",
    "    },\n",
    ")\n",
    "\n",
    "sut = mario.parse_from_excel(\n",
    "    path=\"/path/to/custom_sut_filled.xlsx\",\n",
    "    table=\"SUT\",\n",
    "    mode=\"flows\",\n",
    ")\n",
    "sut.get_index(\"all\")"
   ]
  }
 ],
 "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
}
