Template-notebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9c438538",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ifrs17_template.template import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b8767a8b",
   "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>DataNode</th>\n",
       "      <th>AocType</th>\n",
       "      <th>Novelty</th>\n",
       "      <th>AmountType</th>\n",
       "      <th>AccidentYear</th>\n",
       "      <th>EstimateType</th>\n",
       "      <th>EconomicBasis</th>\n",
       "      <th>Value</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>...</th>\n",
       "      <th>FunctionalCurrency</th>\n",
       "      <th>LineOfBusiness</th>\n",
       "      <th>ValuationApproach</th>\n",
       "      <th>OciType</th>\n",
       "      <th>AnnualCohort</th>\n",
       "      <th>LiabilityType</th>\n",
       "      <th>Profitability</th>\n",
       "      <th>Portfolio</th>\n",
       "      <th>YieldCurveName</th>\n",
       "      <th>Partner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DT3.1</td>\n",
       "      <td>BOP</td>\n",
       "      <td>I</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>72.200000</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DT3.1</td>\n",
       "      <td>BOP</td>\n",
       "      <td>I</td>\n",
       "      <td>PR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>AA</td>\n",
       "      <td>None</td>\n",
       "      <td>-1.500000</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DT3.1</td>\n",
       "      <td>BOP</td>\n",
       "      <td>I</td>\n",
       "      <td>PR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OA</td>\n",
       "      <td>None</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DT1.1</td>\n",
       "      <td>AM</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>L</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DT1.1</td>\n",
       "      <td>AM</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>None</td>\n",
       "      <td>-180.009175</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628</th>\n",
       "      <td>DT5.1</td>\n",
       "      <td>AM</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>-82.022711</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629</th>\n",
       "      <td>DT5.1</td>\n",
       "      <td>EOP</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DA</td>\n",
       "      <td>None</td>\n",
       "      <td>-5.156552</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1630</th>\n",
       "      <td>DT5.1</td>\n",
       "      <td>EOP</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>42.968114</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1631</th>\n",
       "      <td>DT5.1</td>\n",
       "      <td>BOP</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>139.908492</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1632</th>\n",
       "      <td>DT5.1</td>\n",
       "      <td>IA</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>0.082333</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>CHF</td>\n",
       "      <td>ANN</td>\n",
       "      <td>BBA</td>\n",
       "      <td>Default</td>\n",
       "      <td>2020</td>\n",
       "      <td>LRC</td>\n",
       "      <td>P</td>\n",
       "      <td>DT5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1633 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     DataNode AocType Novelty AmountType  AccidentYear EstimateType  \\\n",
       "0       DT3.1     BOP       I       None           0.0            C   \n",
       "1       DT3.1     BOP       I         PR           0.0           AA   \n",
       "2       DT3.1     BOP       I         PR           0.0           OA   \n",
       "3       DT1.1      AM       C       None           NaN            F   \n",
       "4       DT1.1      AM       C       None           NaN            L   \n",
       "...       ...     ...     ...        ...           ...          ...   \n",
       "1628    DT5.1      AM       C       None           0.0            C   \n",
       "1629    DT5.1     EOP       C       None           0.0           DA   \n",
       "1630    DT5.1     EOP       C       None           0.0            C   \n",
       "1631    DT5.1     BOP       N       None           0.0            C   \n",
       "1632    DT5.1      IA       N       None           0.0            C   \n",
       "\n",
       "     EconomicBasis       Value  Year  Month  ... FunctionalCurrency  \\\n",
       "0             None   72.200000  2020     12  ...                CHF   \n",
       "1             None   -1.500000  2020     12  ...                CHF   \n",
       "2             None    1.500000  2020     12  ...                CHF   \n",
       "3                L    1.000000  2020     12  ...                CHF   \n",
       "4             None -180.009175  2020     12  ...                CHF   \n",
       "...            ...         ...   ...    ...  ...                ...   \n",
       "1628          None  -82.022711  2020     12  ...                CHF   \n",
       "1629          None   -5.156552  2020     12  ...                CHF   \n",
       "1630          None   42.968114  2020     12  ...                CHF   \n",
       "1631          None  139.908492  2020     12  ...                CHF   \n",
       "1632          None    0.082333  2020     12  ...                CHF   \n",
       "\n",
       "     LineOfBusiness ValuationApproach  OciType AnnualCohort LiabilityType  \\\n",
       "0               ANN               BBA  Default         2020           LRC   \n",
       "1               ANN               BBA  Default         2020           LRC   \n",
       "2               ANN               BBA  Default         2020           LRC   \n",
       "3               ANN               BBA  Default         2020           LRC   \n",
       "4               ANN               BBA  Default         2020           LRC   \n",
       "...             ...               ...      ...          ...           ...   \n",
       "1628            ANN               BBA  Default         2020           LRC   \n",
       "1629            ANN               BBA  Default         2020           LRC   \n",
       "1630            ANN               BBA  Default         2020           LRC   \n",
       "1631            ANN               BBA  Default         2020           LRC   \n",
       "1632            ANN               BBA  Default         2020           LRC   \n",
       "\n",
       "      Profitability Portfolio YieldCurveName Partner  \n",
       "0                 P       DT3                         \n",
       "1                 P       DT3                         \n",
       "2                 P       DT3                         \n",
       "3                 P       DT1                         \n",
       "4                 P       DT1                         \n",
       "...             ...       ...            ...     ...  \n",
       "1628              P       DT5                         \n",
       "1629              P       DT5                         \n",
       "1630              P       DT5                         \n",
       "1631              P       DT5                         \n",
       "1632              P       DT5                         \n",
       "\n",
       "[1633 rows x 22 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c8ca5792",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2103 = df.loc[(df['Year'] == 2021) & (df['Month'] == 3)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "42e14786",
   "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>LiabilityType</th>\n",
       "      <th colspan=\"2\" halign=\"left\">LRC</th>\n",
       "      <th colspan=\"2\" halign=\"left\">LIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>EconomicBasis</th>\n",
       "      <th>C</th>\n",
       "      <th>L</th>\n",
       "      <th>C</th>\n",
       "      <th>L</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Novelty</th>\n",
       "      <th>AocType</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\">C</th>\n",
       "      <th>CL</th>\n",
       "      <td>74.987448</td>\n",
       "      <td>74.994992</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EOP</th>\n",
       "      <td>-379.954884</td>\n",
       "      <td>-380.062226</td>\n",
       "      <td>271.367348</td>\n",
       "      <td>271.706254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">I</th>\n",
       "      <th>BOP</th>\n",
       "      <td>-499.330813</td>\n",
       "      <td>-499.330813</td>\n",
       "      <td>299.906093</td>\n",
       "      <td>299.906093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CF</th>\n",
       "      <td>249.832118</td>\n",
       "      <td>249.832118</td>\n",
       "      <td>-60.083941</td>\n",
       "      <td>-60.083941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CRU</th>\n",
       "      <td>2.369316</td>\n",
       "      <td>2.373251</td>\n",
       "      <td>1.184658</td>\n",
       "      <td>1.186626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>-0.366044</td>\n",
       "      <td>-0.366044</td>\n",
       "      <td>0.109789</td>\n",
       "      <td>0.109789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MC</th>\n",
       "      <td>-499.584732</td>\n",
       "      <td>-499.584732</td>\n",
       "      <td>-59.981219</td>\n",
       "      <td>-59.981219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YCU</th>\n",
       "      <td>0.522270</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.223970</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">N</th>\n",
       "      <th>BOP</th>\n",
       "      <td>60.913655</td>\n",
       "      <td>61.072687</td>\n",
       "      <td>60.425664</td>\n",
       "      <td>60.523857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CF</th>\n",
       "      <td>-15.534843</td>\n",
       "      <td>-15.534843</td>\n",
       "      <td>-15.267421</td>\n",
       "      <td>-15.267421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EV</th>\n",
       "      <td>246.140729</td>\n",
       "      <td>246.442443</td>\n",
       "      <td>45.227891</td>\n",
       "      <td>45.284376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>0.093247</td>\n",
       "      <td>0.037419</td>\n",
       "      <td>0.068421</td>\n",
       "      <td>0.027447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "LiabilityType           LRC                     LIC            \n",
       "EconomicBasis             C           L           C           L\n",
       "Novelty AocType                                                \n",
       "C       CL        74.987448   74.994992         NaN         NaN\n",
       "        EOP     -379.954884 -380.062226  271.367348  271.706254\n",
       "I       BOP     -499.330813 -499.330813  299.906093  299.906093\n",
       "        CF       249.832118  249.832118  -60.083941  -60.083941\n",
       "        CRU        2.369316    2.373251    1.184658    1.186626\n",
       "        IA        -0.366044   -0.366044    0.109789    0.109789\n",
       "        MC      -499.584732 -499.584732  -59.981219  -59.981219\n",
       "        YCU        0.522270         NaN   -0.223970         NaN\n",
       "N       BOP       60.913655   61.072687   60.425664   60.523857\n",
       "        CF       -15.534843  -15.534843  -15.267421  -15.267421\n",
       "        EV       246.140729  246.442443   45.227891   45.284376\n",
       "        IA         0.093247    0.037419    0.068421    0.027447"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_BE = df_2103.loc[df['EstimateType'] == 'BE'][['AocType', 'Novelty', 'AmountType', 'EconomicBasis', 'Value', 'LiabilityType']]\n",
    "PVs = df_BE.set_index(['Novelty', 'AocType', 'EconomicBasis', 'LiabilityType'])['Value'].groupby(level=list(range(4))).sum().unstack(level=[-1, -2])\n",
    "PVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cc67c272",
   "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>EconomicBasis</th>\n",
       "      <th>C</th>\n",
       "      <th>L</th>\n",
       "      <th>C</th>\n",
       "      <th>L</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>LiabilityType</th>\n",
       "      <th>LRC</th>\n",
       "      <th>LRC</th>\n",
       "      <th>LIC</th>\n",
       "      <th>LIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Novelty</th>\n",
       "      <th>AocType</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\">C</th>\n",
       "      <th>CL</th>\n",
       "      <td>-22.453303</td>\n",
       "      <td>-22.481279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EOP</th>\n",
       "      <td>89.813213</td>\n",
       "      <td>89.925114</td>\n",
       "      <td>22.453303</td>\n",
       "      <td>22.481279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">I</th>\n",
       "      <th>BOP</th>\n",
       "      <td>149.837775</td>\n",
       "      <td>149.837775</td>\n",
       "      <td>29.967555</td>\n",
       "      <td>29.967555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CF</th>\n",
       "      <td>-30.000000</td>\n",
       "      <td>-30.000000</td>\n",
       "      <td>-6.000000</td>\n",
       "      <td>-6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>0.054895</td>\n",
       "      <td>0.054895</td>\n",
       "      <td>0.010979</td>\n",
       "      <td>0.010979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MC</th>\n",
       "      <td>-29.967555</td>\n",
       "      <td>-29.967555</td>\n",
       "      <td>-5.993511</td>\n",
       "      <td>-5.993511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YCU</th>\n",
       "      <td>-0.111901</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.022380</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">N</th>\n",
       "      <th>BOP</th>\n",
       "      <td>59.838185</td>\n",
       "      <td>59.935110</td>\n",
       "      <td>11.967637</td>\n",
       "      <td>11.987022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CF</th>\n",
       "      <td>-15.000000</td>\n",
       "      <td>-15.000000</td>\n",
       "      <td>-3.000000</td>\n",
       "      <td>-3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EV</th>\n",
       "      <td>-22.453303</td>\n",
       "      <td>-22.481279</td>\n",
       "      <td>-4.490661</td>\n",
       "      <td>-4.496256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>0.068421</td>\n",
       "      <td>0.027447</td>\n",
       "      <td>0.013684</td>\n",
       "      <td>0.005489</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "EconomicBasis             C           L          C          L\n",
       "LiabilityType           LRC         LRC        LIC        LIC\n",
       "Novelty AocType                                              \n",
       "C       CL       -22.453303  -22.481279        NaN        NaN\n",
       "        EOP       89.813213   89.925114  22.453303  22.481279\n",
       "I       BOP      149.837775  149.837775  29.967555  29.967555\n",
       "        CF       -30.000000  -30.000000  -6.000000  -6.000000\n",
       "        IA         0.054895    0.054895   0.010979   0.010979\n",
       "        MC       -29.967555  -29.967555  -5.993511  -5.993511\n",
       "        YCU       -0.111901         NaN  -0.022380        NaN\n",
       "N       BOP       59.838185   59.935110  11.967637  11.987022\n",
       "        CF       -15.000000  -15.000000  -3.000000  -3.000000\n",
       "        EV       -22.453303  -22.481279  -4.490661  -4.496256\n",
       "        IA         0.068421    0.027447   0.013684   0.005489"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_RA = df_2103.loc[df['EstimateType'] == 'RA'][['AocType', 'Novelty', 'AmountType', 'EconomicBasis', 'Value', 'LiabilityType']]\n",
    "RA = df_RA.set_index(['Novelty', 'AocType', 'LiabilityType', 'EconomicBasis'])['Value'].groupby(level=list(range(4))).sum().unstack(level=[-1, -2])\n",
    "RA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "85d8d91a",
   "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>LiabilityType</th>\n",
       "      <th>LIC</th>\n",
       "      <th>LRC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AocType</th>\n",
       "      <th>AmountType</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">CF</th>\n",
       "      <th>ACA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AEA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ICO</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NIC</th>\n",
       "      <td>-82.0</td>\n",
       "      <td>-350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PR</th>\n",
       "      <td>NaN</td>\n",
       "      <td>450.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "LiabilityType        LIC    LRC\n",
       "AocType AmountType             \n",
       "CF      ACA          NaN  -70.0\n",
       "        AEA          NaN  -35.0\n",
       "        ICO          NaN  -42.0\n",
       "        NIC        -82.0 -350.0\n",
       "        PR           NaN  450.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_WA = df_2103.loc[df['EstimateType'] == 'A'][['AocType', 'Novelty', 'AmountType', 'Value', 'LiabilityType']]\n",
    "WA = df_WA.set_index(['AocType', 'AmountType' , 'LiabilityType'])['Value'].groupby(level=list(range(3))).sum().unstack(level=[-1])\n",
    "WA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "08e11b78",
   "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>EstimateType</th>\n",
       "      <th>AA</th>\n",
       "      <th>OA</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AocType</th>\n",
       "      <th>AmountType</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">CF</th>\n",
       "      <th>NIC</th>\n",
       "      <td>-4.0</td>\n",
       "      <td>-8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PR</th>\n",
       "      <td>6.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">WO</th>\n",
       "      <th>NIC</th>\n",
       "      <td>5.0</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PR</th>\n",
       "      <td>3.5</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "EstimateType         AA    OA\n",
       "AocType AmountType           \n",
       "CF      NIC        -4.0  -8.0\n",
       "        PR          6.5   5.5\n",
       "WO      NIC         5.0  12.5\n",
       "        PR          3.5  17.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_AAOA = df_2103.loc[(df['EstimateType'] == 'AA') | (df['EstimateType'] == 'OA')][['AocType', 'Novelty', 'AmountType', 'Value', 'EstimateType']]\n",
    "AAOA = df_AAOA.set_index(['AocType', 'AmountType', 'EstimateType'])['Value'].groupby(level=list(range(3))).sum().unstack(level=[-1])\n",
    "AAOA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "53c3709a",
   "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>EstimateType</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AocType</th>\n",
       "      <th>AmountType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_DA = df_2103.loc[df['EstimateType'] == 'DA'][['AocType', 'Novelty', 'AmountType', 'Value', 'EstimateType']]\n",
    "DA = df_DA.set_index(['AocType', 'AmountType', 'EstimateType'])['Value'].groupby(level=list(range(3))).sum().unstack(level=[-1])\n",
    "DA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6cdc68f2",
   "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>DataNode</th>\n",
       "      <th>AocType</th>\n",
       "      <th>Novelty</th>\n",
       "      <th>AmountType</th>\n",
       "      <th>AccidentYear</th>\n",
       "      <th>EstimateType</th>\n",
       "      <th>EconomicBasis</th>\n",
       "      <th>Value</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>...</th>\n",
       "      <th>FunctionalCurrency</th>\n",
       "      <th>LineOfBusiness</th>\n",
       "      <th>ValuationApproach</th>\n",
       "      <th>OciType</th>\n",
       "      <th>AnnualCohort</th>\n",
       "      <th>LiabilityType</th>\n",
       "      <th>Profitability</th>\n",
       "      <th>Portfolio</th>\n",
       "      <th>YieldCurveName</th>\n",
       "      <th>Partner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [DataNode, AocType, Novelty, AmountType, AccidentYear, EstimateType, EconomicBasis, Value, Year, Month, DisplayName, ContractualCurrency, FunctionalCurrency, LineOfBusiness, ValuationApproach, OciType, AnnualCohort, LiabilityType, Profitability, Portfolio, YieldCurveName, Partner]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 22 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2103.loc[df['EstimateType'] == 'DA']"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}