{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vision Conversion\n",
    "\n",
    "This example illustrates conversion from Vision excel export to power-grid-model input data.\n",
    "They function in a similar way since both are [Tabular Converters](../converters/tabular_converter.md).\n",
    "We can then calculate power-flow with it or convert to a different formats like PGM JSON."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vision conversion\n",
    "![Vision example](data/vision/example.png)\n",
    "\n",
    "### 1. Load the Vision data\n",
    "\n",
    "To export the Vision file in Excel format, please follow the instructions given in the [Vision Manual](https://phasetophase.nl/pdf/VisionEN.pdf).\n",
    "\n",
    "Define source and destination paths:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "source_file = \"data/vision/example.xlsx\"\n",
    "destination_file = \"data/vision/sym_output.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instantiate the converter, optionally with a source file path.\n",
    "Then use `load_input_data()` to load the data and convert it to power-grid-model data.\n",
    "The additional information that is not used in the powerflow calculation but may be useful to link the results to the source data is stored in `extra_info`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture cap --no-stderr\n",
    "from power_grid_model_io.converters import VisionExcelConverter\n",
    "\n",
    "converter = VisionExcelConverter(source_file=source_file)\n",
    "input_data, extra_info = converter.load_input_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's investigate the data we have converted, for one of the components: `nodes`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([(0, 400.), (1, 400.), (2, 400.), (3, 400.)],\n",
       "      dtype={'names': [id, u_rated], 'formats': ['<i4', '<f8'], 'offsets': [0, 8], 'itemsize': 16, 'aligned': True})"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>id</th>\n",
       "      <th>u_rated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  u_rated\n",
       "0   0    400.0\n",
       "1   1    400.0\n",
       "2   2    400.0\n",
       "3   3    400.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "{np.int32(0): 'First Node',\n",
       " np.int32(1): 'Fourth Node',\n",
       " np.int32(2): 'Second Node',\n",
       " np.int32(3): 'Third Node'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from power_grid_model import AttributeType, ComponentType\n",
    "\n",
    "# The node data is stored as a numpy structured array in input_data[ComponentType.node]\n",
    "display(input_data[ComponentType.node])\n",
    "\n",
    "# We can use pandas to display the data in a convenient tabular format\n",
    "display(pd.DataFrame(input_data[ComponentType.node]))\n",
    "\n",
    "# Notice that the node names were not stored in the numpy array, as we don't need them for the calculations\n",
    "display({i: extra_info[i][\"Name\"] for i in input_data[ComponentType.node][AttributeType.id]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Validate the data\n",
    "Before we run a power flow calculation, it is wise validate the data. The most basic method is to use `assert_valid_input_data()`, which will raise a `ValueError` when the data is invalid. For more details on data validation, please consult the [validation Example](https://github.com/PowerGridModel/power-grid-model/blob/main/docs/examples/Validation%20Examples.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from power_grid_model import CalculationType\n",
    "from power_grid_model.validation import assert_valid_input_data\n",
    "\n",
    "assert_valid_input_data(input_data, calculation_type=CalculationType.power_flow, symmetric=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Run the calculation\n",
    "\n",
    "Run powerflow calculation with the `input_data` and show the results for `nodes`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>energized</th>\n",
       "      <th>u_pu</th>\n",
       "      <th>u</th>\n",
       "      <th>u_angle</th>\n",
       "      <th>p</th>\n",
       "      <th>q</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.999991</td>\n",
       "      <td>399.996569</td>\n",
       "      <td>-0.000039</td>\n",
       "      <td>9.171362e+04</td>\n",
       "      <td>1.065611e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.983232</td>\n",
       "      <td>393.292609</td>\n",
       "      <td>-0.003435</td>\n",
       "      <td>-3.000000e+04</td>\n",
       "      <td>-1.000000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.987662</td>\n",
       "      <td>395.064831</td>\n",
       "      <td>-0.003235</td>\n",
       "      <td>-2.634484e-09</td>\n",
       "      <td>-8.687020e-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.979773</td>\n",
       "      <td>391.909191</td>\n",
       "      <td>-0.006297</td>\n",
       "      <td>-6.000000e+04</td>\n",
       "      <td>1.218643e-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  energized      u_pu           u   u_angle             p             q\n",
       "0   0          1  0.999991  399.996569 -0.000039  9.171362e+04  1.065611e+04\n",
       "1   1          1  0.983232  393.292609 -0.003435 -3.000000e+04 -1.000000e+04\n",
       "2   2          1  0.987662  395.064831 -0.003235 -2.634484e-09 -8.687020e-10\n",
       "3   3          1  0.979773  391.909191 -0.006297 -6.000000e+04  1.218643e-10"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from power_grid_model import PowerGridModel\n",
    "\n",
    "pgm = PowerGridModel(input_data=input_data)\n",
    "output_data = pgm.calculate_power_flow()\n",
    "\n",
    "display(pd.DataFrame(output_data[ComponentType.node]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cross referencing objects\n",
    "The converter has generated unique numerical IDs for all the components in the VisionExcel file, in fact for some special components like _Transformer loads_, multiple PGM components have been created, each with their own numerical ID. To find out which component belongs to which id, some helper functions have been defined:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PGM object #0: {'table': 'Nodes', 'key': {'Number': np.int64(1)}}\n",
      "PGM object #4: {'table': 'Cables', 'key': {'Number': np.int64(6)}}\n",
      "PGM object #7: {'table': 'Sources', 'key': {'Node.Number': np.int64(1), 'Subnumber': np.int64(1)}}\n",
      "PGM object #9: {'table': 'Loads', 'key': {'Node.Number': np.int64(4), 'Subnumber': np.int64(1)}}\n",
      "Node with Number=1: 0\n",
      "Cables with Number=6: 4\n",
      "Source with Node.Number=1 and Subnumber=1: 7\n",
      "Loads with Node.Number=4 and Subnumber=1: 9\n"
     ]
    }
   ],
   "source": [
    "print(\"PGM object #0:\", converter.lookup_id(0))\n",
    "print(\"PGM object #4:\", converter.lookup_id(4))\n",
    "print(\"PGM object #7:\", converter.lookup_id(7))\n",
    "print(\"PGM object #9:\", converter.lookup_id(9))\n",
    "\n",
    "print(\"Node with Number=1:\", converter.get_node_id(number=1))\n",
    "\n",
    "print(\"Cables with Number=6:\", converter.get_branch_id(table=\"Cables\", number=6))\n",
    "\n",
    "print(\n",
    "    \"Source with Node.Number=1 and Subnumber=1:\",\n",
    "    converter.get_appliance_id(table=\"Sources\", node_number=1, sub_number=1),\n",
    ")\n",
    "\n",
    "print(\n",
    "    \"Loads with Node.Number=4 and Subnumber=1:\", converter.get_appliance_id(table=\"Loads\", node_number=4, sub_number=1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving the data as a JSON file\n",
    "The data can be stored in a json file using the PgmJsonConverter. The file will be saved in the `destination_file` path supplied in the constructor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from power_grid_model_io.converters import PgmJsonConverter\n",
    "\n",
    "json_converter = PgmJsonConverter(destination_file=destination_file)\n",
    "json_converter.save(data=output_data, extra_info=extra_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For debugging purposes, let's check the output JSON. Notice that the node names are added to the nodes data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "<pre style='max-height: 160px; overflow: scroll; white-space: pre'>{\n",
       "  \"node\":\n",
       "    [\n",
       "      {\"id\": 0, \"energized\": 1, \"u_pu\": 0.9999914213020509, \"u\": 399.99656852082035, \"u_angle\": -3.921691889422516e-05, \"p\": 91713.61901001866, \"q\": 10656.105417735625, \"id_reference\": {\"table\": \"Nodes\", \"key\": {\"Number\": 1}}, \"ID\": 101, \"Name\": \"First Node\"},\n",
       "      {\"id\": 1, \"energized\": 1, \"u_pu\": 0.9832315221948486, \"u\": 393.2926088779394, \"u_angle\": -0.0034345813942749746, \"p\": -29999.999999999956, \"q\": -9999.999999999569, \"id_reference\": {\"table\": \"Nodes\", \"key\": {\"Number\": 4}}, \"ID\": 104, \"Name\": \"Fourth Node\"},\n",
       "      {\"id\": 2, \"energized\": 1, \"u_pu\": 0.9876620765421241, \"u\": 395.06483061684963, \"u_angle\": -0.0032350422866854467, \"p\": -2.6344844955208393e-09, \"q\": -8.687020341559513e-10, \"id_reference\": {\"table\": \"Nodes\", \"key\": {\"Number\": 2}}, \"ID\": 102, \"Name\": \"Second Node\"},\n",
       "      {\"id\": 3, \"energized\": 1, \"u_pu\": 0.9797729773054615, \"u\": 391.9091909221846, \"u_angle\": -0.006296501518419477, \"p\": -59999.99999999907, \"q\": 1.218643241873707e-10, \"id_reference\": {\"table\": \"Nodes\", \"key\": {\"Number\": 3}}, \"ID\": 103, \"Name\": \"Third Node\"}\n",
       "    ],\n",
       "  \"line\":\n",
       "    [\n",
       "      {\"id\": 4, \"energized\": 1, \"loading\": 0.15473990756817338, \"p_from\": 30133.17850372892, \"q_from\": 10050.72185372124, \"i_from\": 46.4218094389901, \"s_from\": 31765.16104978, \"p_to\": -29999.999999999956, \"q_to\": -9999.999999999569, \"i_to\": 46.42197227045202, \"s_to\": 31622.776601683618, \"id_reference\": {\"table\": \"Cables\", \"key\": {\"Number\": 6}}},\n",
       "      {\"id\": 5, \"energized\": 1, \"loading\": 0.2946347177166623, \"p_from\": 60482.83508911288, \"q_from\": 184.8146853877713, \"i_from\": 88.39041452674708, \"s_from\": 60483.11745342463, \"p_to\": -59999.99999999907, \"q_to\": 1.218643241873707e-10, \"i_to\": 88.39041531499868, \"s_to\": 59999.99999999907, \"id_reference\": {\"table\": \"Cables\", \"key\": {\"Number\": 5}}},\n",
       "      {\"id\": 6, \"energized\": 1, \"loading\": 0.44422983507392755, \"p_from\": 91713.61901001866, \"q_from\": 10656.105417735625, \"i_from\": 133.26889142389595, \"s_from\": 92330.60432266623, \"p_to\": -90616.0135928418, \"q_to\": -10235.53653910901, \"i_to\": 133.26895052217827, \"s_to\": 91192.25914353436, \"id_reference\": {\"table\": \"Cables\", \"key\": {\"Number\": 4}}}\n",
       "    ],\n",
       "  \"source\":\n",
       "    [\n",
       "      {\"id\": 7, \"energized\": 1, \"p\": 91713.61901001866, \"q\": 10656.105417735625, \"i\": 133.26889142389595, \"s\": 92330.60432266623, \"pf\": 0.9933176510954981, \"id_reference\": {\"table\": \"Sources\", \"key\": {\"Node.Number\": 1, \"Subnumber\": 1}}, \"Name\": \"Grid\"}\n",
       "    ],\n",
       "  \"sym_load\":\n",
       "    [\n",
       "      {\"id\": 8, \"energized\": 1, \"p\": 60000, \"q\": 0, \"i\": 88.39041531500004, \"s\": 60000, \"pf\": 1, \"id_reference\": {\"table\": \"Loads\", \"key\": {\"Node.Number\": 3, \"Subnumber\": 1}}},\n",
       "      {\"id\": 9, \"energized\": 1, \"p\": 30000, \"q\": 10000, \"i\": 46.421972270452265, \"s\": 31622.776601683792, \"pf\": 0.9486832980505139, \"id_reference\": {\"table\": \"Loads\", \"key\": {\"Node.Number\": 4, \"Subnumber\": 1}}}\n",
       "    ]\n",
       "}\n",
       "</pre>"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "with Path(destination_file).open() as json_file:\n",
    "    display(Markdown(f\"<pre style='max-height: 160px; overflow: scroll; white-space: pre'>{json_file.read()}</pre>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GUID (Vision 9.7 and later)\n",
    "To accomdate GUIDs since version 9.7, we have internal conversion mechanism that converts GUID's to unique integers (persession). This change does not impact anything on the user side. Therefore, loading such new excel files is performed exactly as in previous versions. Here, we only show how one could add corresponding GUID information to the extra_info. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the custom mapping file, add `GUID` to the `extra` field of interest:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Custom yaml mapping file\n",
    "\n",
    "```yaml\n",
    "...\n",
    "grid:\n",
    "  Nodes:\n",
    "    node:\n",
    "      id:\n",
    "        auto_id:\n",
    "          key: Number\n",
    "      u_rated: Unom\n",
    "      extra:\n",
    "        - ID\n",
    "        - Name\n",
    "        - GUID\n",
    "  Cables:\n",
    "    line:\n",
    "      id:\n",
    "        auto_id:\n",
    "          key: Number\n",
    "      from_node:\n",
    "        auto_id:\n",
    "          table: Nodes\n",
    "          key:\n",
    "            Number: From.Number\n",
    "...\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When run excel conversion with mapping file that contains this change, `GUID` information will be part of the returned `extra_info`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other languages\n",
    "Currently Dutch is the only extra language that is supported for conversion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture cap --no-stderr\n",
    "\n",
    "converter = VisionExcelConverter(source_file=source_file, language=\"nl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%%capture cap --no-stderr\n",
    "\n",
    "from power_grid_model import CalculationType, PowerGridModel\n",
    "from power_grid_model.validation import assert_valid_input_data\n",
    "\n",
    "from power_grid_model_io.converters import PgmJsonConverter, VisionExcelConverter\n",
    "\n",
    "source_file = \"data/vision/example.xlsx\"\n",
    "destination_file = \"data/vision/sym_output.json\"\n",
    "\n",
    "converter = VisionExcelConverter(source_file=source_file)\n",
    "input_data, extra_info = converter.load_input_data()\n",
    "assert_valid_input_data(input_data, calculation_type=CalculationType.power_flow, symmetric=True)\n",
    "pgm = PowerGridModel(input_data=input_data)\n",
    "output_data = pgm.calculate_power_flow()\n",
    "json_converter = PgmJsonConverter(destination_file=destination_file)\n",
    "json_converter.save(data=output_data, extra_info=extra_info)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "power-grid-model-io",
   "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.14.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}