BK_Caller_w_Drive.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "scrolled": false
   },
   "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>Name</th>\n",
       "      <th>Anschaffungskosten [€]</th>\n",
       "      <th>Staatliche Förderung [€]</th>\n",
       "      <th>Anzahlung [€]</th>\n",
       "      <th>Finanzierungszins [%]</th>\n",
       "      <th>Restwert [€]</th>\n",
       "      <th>Versicherungskosten [€/a]</th>\n",
       "      <th>Steuern [€/a]</th>\n",
       "      <th>Verbrauch in E/100 km</th>\n",
       "      <th>Durchschnittlicher Spritpreis über Haltedauer [€/E]</th>\n",
       "      <th>Reparatur und Wartungskosten [€/a]</th>\n",
       "      <th>Jahreslaufleistung [km]</th>\n",
       "      <th>Haltedauer [a]</th>\n",
       "      <th>Leasing</th>\n",
       "      <th>Leasingrate [€]</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Model Y '23</td>\n",
       "      <td>60945.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>20945.0</td>\n",
       "      <td>1.91</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>541.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>0.31</td>\n",
       "      <td>500.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Skoda Kodiaq M</td>\n",
       "      <td>38600.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18600.0</td>\n",
       "      <td>2.00</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>486.05</td>\n",
       "      <td>230.0</td>\n",
       "      <td>7.95</td>\n",
       "      <td>2.05</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Renault ZOE</td>\n",
       "      <td>13990.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13990.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>500.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>3200</td>\n",
       "      <td>6.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MY 24</td>\n",
       "      <td>60170.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>15170.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>620.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>0.30</td>\n",
       "      <td>500.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Model 3 Std. '22</td>\n",
       "      <td>43970.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>12970.0</td>\n",
       "      <td>1.91</td>\n",
       "      <td>8500.0</td>\n",
       "      <td>613.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.53</td>\n",
       "      <td>500.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name  Anschaffungskosten [€]  Staatliche Förderung [€]  \\\n",
       "id                                                                       \n",
       "23       Model Y '23                 60945.0                    4000.0   \n",
       "24    Skoda Kodiaq M                 38600.0                       0.0   \n",
       "25       Renault ZOE                 13990.0                       0.0   \n",
       "26             MY 24                 60170.0                    3000.0   \n",
       "27  Model 3 Std. '22                 43970.0                    6000.0   \n",
       "\n",
       "    Anzahlung [€]  Finanzierungszins [%]  Restwert [€]  \\\n",
       "id                                                       \n",
       "23        20945.0                   1.91       12000.0   \n",
       "24        18600.0                   2.00       15000.0   \n",
       "25        13990.0                   0.00        3000.0   \n",
       "26        15170.0                   3.00       10000.0   \n",
       "27        12970.0                   1.91        8500.0   \n",
       "\n",
       "    Versicherungskosten [€/a]  Steuern [€/a]  Verbrauch in E/100 km  \\\n",
       "id                                                                    \n",
       "23                     541.02            0.0                  17.00   \n",
       "24                     486.05          230.0                   7.95   \n",
       "25                     500.00            0.0                  15.00   \n",
       "26                     620.00            0.0                  17.00   \n",
       "27                     613.25            0.0                  15.00   \n",
       "\n",
       "    Durchschnittlicher Spritpreis über Haltedauer [€/E]  \\\n",
       "id                                                        \n",
       "23                                               0.31     \n",
       "24                                               2.05     \n",
       "25                                               0.30     \n",
       "26                                               0.30     \n",
       "27                                               0.53     \n",
       "\n",
       "    Reparatur und Wartungskosten [€/a]  Jahreslaufleistung [km]  \\\n",
       "id                                                                \n",
       "23                               500.0                    15000   \n",
       "24                              1200.0                    15000   \n",
       "25                              1700.0                     3200   \n",
       "26                               500.0                    15000   \n",
       "27                               500.0                    15000   \n",
       "\n",
       "    Haltedauer [a]  Leasing  Leasingrate [€]  \n",
       "id                                            \n",
       "23             7.0    False              0.0  \n",
       "24             7.0    False              0.0  \n",
       "25             6.0    False              0.0  \n",
       "26             7.0    False              0.0  \n",
       "27             7.0    False              0.0  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spreadsheet_id = '1_r9UF8tqLGP_5HFXEdtEs4cwCEHgMZJm25sLPqW5CUc'\n",
    "sheet_id = 'Eingaben'\n",
    "url = f'https://docs.google.com/spreadsheets/d/{spreadsheet_id}/gviz/tq?tqx=out:csv&sheet={sheet_id}'\n",
    "\n",
    "def german_number(x):\n",
    "    if x == '':\n",
    "        return 0\n",
    "    else:\n",
    "        return float( x[:-1].replace('.', '').replace(',','.') )\n",
    "                                 \n",
    "df = pd.read_csv(url, \n",
    "                 index_col=0,\n",
    "                 usecols=range(16),\n",
    "                 decimal=',',\n",
    "                 converters={'Anschaffungskosten [€]': german_number,\n",
    "                             'Staatliche Förderung [€]': german_number,\n",
    "                             'Anzahlung [€]': german_number,\n",
    "                             'Finanzierungszins [%]': german_number,\n",
    "                             'Restwert [€]': german_number,\n",
    "                             'Versicherungskosten [€/a]': german_number,\n",
    "                             'Steuern [€/a]': german_number,\n",
    "                             'Durchschnittlicher Spritpreis über Haltedauer [€/E]': german_number,\n",
    "                             'Reparatur und Wartungskosten [€/a]': german_number,\n",
    "                             'Leasingrate [€]': german_number})\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 28 entries, 0 to 27\n",
      "Data columns (total 15 columns):\n",
      " #   Column                                               Non-Null Count  Dtype  \n",
      "---  ------                                               --------------  -----  \n",
      " 0   Name                                                 28 non-null     object \n",
      " 1   Anschaffungskosten [€]                               28 non-null     float64\n",
      " 2   Staatliche Förderung [€]                             28 non-null     float64\n",
      " 3   Anzahlung [€]                                        28 non-null     float64\n",
      " 4   Finanzierungszins [%]                                28 non-null     float64\n",
      " 5   Restwert [€]                                         28 non-null     float64\n",
      " 6   Versicherungskosten [€/a]                            28 non-null     float64\n",
      " 7   Steuern [€/a]                                        28 non-null     float64\n",
      " 8   Verbrauch in E/100 km                                28 non-null     float64\n",
      " 9   Durchschnittlicher Spritpreis über Haltedauer [€/E]  28 non-null     float64\n",
      " 10  Reparatur und Wartungskosten [€/a]                   28 non-null     float64\n",
      " 11  Jahreslaufleistung [km]                              28 non-null     int64  \n",
      " 12  Haltedauer [a]                                       28 non-null     float64\n",
      " 13  Leasing                                              28 non-null     bool   \n",
      " 14  Leasingrate [€]                                      28 non-null     float64\n",
      "dtypes: bool(1), float64(12), int64(1), object(1)\n",
      "memory usage: 3.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import os\n",
    "# \n",
    "# import dataframe_image as dfi\n",
    "# \n",
    "from slugify import slugify\n",
    "# from pathlib import Path\n",
    "# from bk import Operating_Cost\n",
    "# \n",
    "# def df_style(val):\n",
    "#     # return \"font-weight: bold; background: yellow\"\n",
    "#     return \"font-weight: bold;\"\n",
    "# \n",
    "# def index_style(val):\n",
    "#     # return \"font-weight: bold; background: yellow\"\n",
    "#     return \"font-weight: bold; text-align: left;\"\n",
    "# \n",
    "# def light_style(val):\n",
    "#     return \"color: grey\"\n",
    "# \n",
    "import shutil\n",
    "from pathlib import Path\n",
    " \n",
    "images_path = Path('images2')\n",
    "if images_path.exists():\n",
    "    shutil.rmtree(images_path)\n",
    "images_path.mkdir(parents=True, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id\n",
       "0          Model Y '22\n",
       "1          Mazda 6 '22\n",
       "2          BMW 3er '11\n",
       "3         Nissan Micra\n",
       "4      Mercedes C 220d\n",
       "5       Mercedes C 200\n",
       "6       Mercedes C 180\n",
       "7         Mazda 6 geb.\n",
       "8             Kia ceed\n",
       "9         Skoda Superb\n",
       "10       Skoda Octavia\n",
       "11     BMW Gran Tourer\n",
       "12    Skoda Kodiaq TSI\n",
       "13    Skoda Kodiaq TDI\n",
       "14           Fiat Tipo\n",
       "15           VW Passat\n",
       "16    Model 3 Std. '22\n",
       "17    Model 3 Std. '22\n",
       "18           VW Touran\n",
       "19           VW Touran\n",
       "20           VW Touran\n",
       "21            BMW 330i\n",
       "22    Tesla 3 Std. '23\n",
       "23         Model Y '23\n",
       "24      Skoda Kodiaq M\n",
       "25         Renault ZOE\n",
       "26               MY 24\n",
       "27    Model 3 Std. '22\n",
       "Name: Name, dtype: object"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "ids = [17, 19, 23, 26]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Grzegorz\\AppData\\Local\\Temp/ipykernel_10716/3972547335.py:110: FutureWarning: this method is deprecated in favour of `Styler.format(precision=..)`\n",
      "  dfi.export(calc_values.style.applymap(df_style, subset=bold_row)\\\n"
     ]
    }
   ],
   "source": [
    "latex = ''\n",
    "\n",
    "overview = pd.DataFrame()\n",
    "overview.name='Monatliche Kosten im Verlgeich'\n",
    "\n",
    "comparison_columns = ['Preis', 'Kredit',\n",
    "                      'Raten', 'Vers.', 'Steuern', 'Repar.', 'Verbr.',\n",
    "                      'Summe', 'Anzahlung', 'Restwert', 'Gesamtsumme']\n",
    "comparison = pd.DataFrame(columns=comparison_columns)\n",
    "comparison.index.name='Fahrzeug'\n",
    "\n",
    "for car in df.iloc[ids].to_dict('records'):\n",
    "    \n",
    "    label = car['Name']\n",
    "    \n",
    "    if car['Leasing']:\n",
    "        \n",
    "        title = label + ', Leasing'\n",
    "        bk_inputs = {'instalment' : car['Leasingrate [€]'],\n",
    "                     'government_funding' : car['Staatliche Förderung [€]'],\n",
    "                     'down_payment' : car['Anzahlung [€]'],\n",
    "                     'holding_period' : car['Haltedauer [a]'],\n",
    "                     'millage_yearly' : car['Jahreslaufleistung [km]'],\n",
    "                     'consumption' : car['Verbrauch in E/100 km'],\n",
    "                     'fuel_price' : car['Durchschnittlicher Spritpreis über Haltedauer [€/E]'],\n",
    "                     'repairs_yearly' : car['Reparatur und Wartungskosten [€/a]'],\n",
    "                     'taxes_yearly' : car['Steuern [€/a]'],\n",
    "                     'insurance_yearly' : car['Versicherungskosten [€/a]']}\n",
    "        (input_values, calc_values) = Operating_Cost.lease(**bk_inputs).get_frames()\n",
    "        \n",
    "    else:\n",
    "        \n",
    "        title = label + ', Kredit'\n",
    "        bk_inputs = {'acquisition_cost' : car['Anschaffungskosten [€]'], \n",
    "                     'government_funding': car['Staatliche Förderung [€]'],\n",
    "                     'down_payment': car['Anzahlung [€]'],\n",
    "                     'holding_period': car['Haltedauer [a]'],\n",
    "                     'millage_yearly': car['Jahreslaufleistung [km]'],\n",
    "                     'consumption': car['Verbrauch in E/100 km'],\n",
    "                     'fuel_price': car['Durchschnittlicher Spritpreis über Haltedauer [€/E]'],\n",
    "                     'repairs_yearly': car['Reparatur und Wartungskosten [€/a]'],\n",
    "                     'interest_rate': car['Finanzierungszins [%]'],\n",
    "                     'taxes_yearly': car['Steuern [€/a]'],\n",
    "                     'insurance_yearly' : car['Versicherungskosten [€/a]'],\n",
    "                     'rest_value': car['Restwert [€]']}\n",
    "        (input_values, calc_values) = Operating_Cost.buy(**bk_inputs).get_frames()\n",
    "        \n",
    "    # pepper = sha256(car)[:12]\n",
    "    input_values_path = images_path / (slugify(label) + '-' + offer_class + '-boundaries_' + '.png')\n",
    "    calc_df_path = images_path / (slugify(label) + '-' + offer_class + '-calc_df_' + '.png')\n",
    "    \n",
    "    comparison = comparison.append(pd.DataFrame(data=[[car['Anschaffungskosten [€]'] * (not car['Leasing']) + car['Anzahlung [€]'] * car['Leasing'] - car['Staatliche Förderung [€]'],\n",
    "                                                           (car['Anschaffungskosten [€]']-car['Anzahlung [€]']) * (not car['Leasing']),\n",
    "                                                       calc_values.loc['Raten', 'Monatlich'],\n",
    "                                                       calc_values.loc['Versicherung', 'Monatlich'],\n",
    "                                                       calc_values.loc['Steuern', 'Monatlich'],\n",
    "                                                       calc_values.loc['Reparaturen', 'Monatlich'],\n",
    "                                                       calc_values.loc['Verbrauch', 'Monatlich'],\n",
    "                                                       calc_values.loc['Summe', 'Monatlich'],\n",
    "                                                       calc_values.loc['Anzahlung', 'Monatlich'],\n",
    "                                                       calc_values.loc['Restwert', 'Monatlich'],\n",
    "                                                       calc_values.loc['Gesamtsumme', 'Monatlich']\n",
    "                                                      ]\n",
    "                                                     ],\n",
    "                                                columns=comparison_columns,\n",
    "                                                index=[label]))\n",
    "    factor = 0.5 ** ( car['Haltedauer [a]']/3 )\n",
    "    rest_value_thumb_value = r'Restwert nach Faustformel (Kaufpreis $\\times$ %.1f\\%%): %.2f \\euro{}' % \\\n",
    "        (factor*100, car['Anschaffungskosten [€]'] * factor)\n",
    "    \n",
    "    latex = r\"\"\"%s\n",
    "\\twocolumn\n",
    "\n",
    "\\section*{%s}\n",
    "\n",
    "\\begin{itemize}\n",
    "    \\item Laufzeit: %.0f Jahre\n",
    "    \\item Spritpreis \\\"uber Haltedauer: %.2f \\euro{}/E\n",
    "    \\item %s\n",
    "\\end{itemize}\n",
    "\n",
    "\\pagebreak\n",
    "\n",
    "\\begin{center}\n",
    "\\includegraphics[width=0.5\\textheight]{%s}\n",
    "\\null\n",
    "\\vspace{0.5cm}\n",
    "\\includegraphics[width=0.5\\textheight]{%s}\n",
    "\\end{center}\n",
    "\n",
    "\\pagebreak\n",
    "\n",
    "\"\"\" %(latex,\n",
    "      title,\n",
    "      car['Haltedauer [a]'],\n",
    "      car['Durchschnittlicher Spritpreis über Haltedauer [€/E]'],\n",
    "      rest_value_thumb_value,\n",
    "      input_values_path.as_posix(),\n",
    "      calc_df_path.as_posix(),\n",
    "     )\n",
    "    \n",
    "    overview[label] = calc_values['Monatlich']\n",
    "    \n",
    "    input_values_path.parent.mkdir(parents=True, exist_ok=True)\n",
    "    \n",
    "    bold_row = pd.IndexSlice[calc_values.index[(calc_values.index == \"Summe\") | (calc_values.index == \"Gesamtsumme\")], :]\n",
    "    light_row = pd.IndexSlice[calc_values.index[6:], :]\n",
    "    \n",
    "    dfi.export(input_values.round(2), str(input_values_path))\n",
    "    dfi.export(calc_values.style.applymap(df_style, subset=bold_row)\\\n",
    "               .applymap(light_style, subset=light_row).set_precision(2), str(calc_df_path))\n",
    "#    display(input_values.round(2))\n",
    "#    display(calc_values.style.applymap(df_style, subset=bold_row)\\\n",
    "#               .applymap(light_style, subset=light_row).set_precision(2))\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "comparison_caption = 'Vergleich der monatlichen Kosten'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "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>Fahrzeug</th>\n",
       "      <th>Preis</th>\n",
       "      <th>Kredit</th>\n",
       "      <th>Raten</th>\n",
       "      <th>Vers.</th>\n",
       "      <th>Steuern</th>\n",
       "      <th>Repar.</th>\n",
       "      <th>Verbr.</th>\n",
       "      <th>Summe</th>\n",
       "      <th>Anzahlung</th>\n",
       "      <th>Restwert</th>\n",
       "      <th>Gesamtsumme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Model 3 Std. '22</td>\n",
       "      <td>1000.00</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>272.00</td>\n",
       "      <td>51.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>41.67</td>\n",
       "      <td>58.12</td>\n",
       "      <td>422.90</td>\n",
       "      <td>20.83</td>\n",
       "      <td>0.00</td>\n",
       "      <td>443.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VW Touran</td>\n",
       "      <td>39986.31</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>318.19</td>\n",
       "      <td>26.46</td>\n",
       "      <td>7.25</td>\n",
       "      <td>83.33</td>\n",
       "      <td>180.38</td>\n",
       "      <td>615.61</td>\n",
       "      <td>178.41</td>\n",
       "      <td>-89.29</td>\n",
       "      <td>704.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Model Y '23</td>\n",
       "      <td>56945.00</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>509.11</td>\n",
       "      <td>45.08</td>\n",
       "      <td>0.00</td>\n",
       "      <td>41.67</td>\n",
       "      <td>65.88</td>\n",
       "      <td>661.74</td>\n",
       "      <td>201.73</td>\n",
       "      <td>-142.86</td>\n",
       "      <td>720.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MY 24</td>\n",
       "      <td>57170.00</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>594.60</td>\n",
       "      <td>51.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>41.67</td>\n",
       "      <td>63.75</td>\n",
       "      <td>751.68</td>\n",
       "      <td>144.88</td>\n",
       "      <td>-119.05</td>\n",
       "      <td>777.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Fahrzeug     Preis   Kredit   Raten  Vers.  Steuern  Repar.  \\\n",
       "0  Model 3 Std. '22   1000.00     -0.0  272.00  51.10     0.00   41.67   \n",
       "1         VW Touran  39986.31  25000.0  318.19  26.46     7.25   83.33   \n",
       "2       Model Y '23  56945.00  40000.0  509.11  45.08     0.00   41.67   \n",
       "3             MY 24  57170.00  45000.0  594.60  51.67     0.00   41.67   \n",
       "\n",
       "   Verbr.   Summe  Anzahlung  Restwert  Gesamtsumme  \n",
       "0   58.12  422.90      20.83      0.00       443.73  \n",
       "1  180.38  615.61     178.41    -89.29       704.74  \n",
       "2   65.88  661.74     201.73   -142.86       720.61  \n",
       "3   63.75  751.68     144.88   -119.05       777.52  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison = comparison.reset_index().rename(columns={'index':'Fahrzeug'})\n",
    "comparison.round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "bold_column = pd.IndexSlice[comparison.index , ['Summe']]\n",
    "index_column = pd.IndexSlice[comparison.index , ['Fahrzeug']]\n",
    "light_column = pd.IndexSlice[comparison.index, 'Anzahlung':'Gesamtsumme']\n",
    "#light_row = pd.IndexSlice[['BMW 318d Kredit', 'Nissan Micra Kredit', 'VW up! Leasing'], :]\n",
    "format_column0 = pd.IndexSlice[comparison.index, 'Preis':'Kredit']\n",
    "format_column1 = pd.IndexSlice[comparison.index, 'Raten':'Gesamtsumme']\n",
    "\n",
    "overview_path_1 = images_path / 'overview1.png'\n",
    "\n",
    "dfi.export(comparison.sort_values('Summe',\n",
    "                                  ascending=True).style.applymap(df_style, subset=bold_column)\\\n",
    "                                                        .applymap(light_style, subset=light_column)\\\n",
    "                                                        .applymap(index_style, subset=index_column)\\\n",
    "                                                        .format('{:.2f}', subset=format_column0)\\\n",
    "                                                        .format('{:.2f}', subset=format_column1)\\\n",
    "                                                        .hide_index(),\n",
    "           overview_path_1.as_posix())\n",
    "\n",
    "#display(comparison.sort_values(comparison_columns[9],\n",
    "#                               ascending=True).style.applymap(df_style, subset=bold_column)\\\n",
    "#                                                     .applymap(light_style, subset=light_column)\\\n",
    "#                                                     .format('{:.2f}', subset=format_column))\n",
    "\n",
    "latex = r\"\"\"%s\n",
    "\n",
    "\\pagebreak\n",
    "\n",
    "\\onecolumn\n",
    "\\begin{figure}\n",
    "\\centering\n",
    "%s\n",
    "\\vspace{1em}\n",
    "\\includegraphics[width=0.95\\columnwidth]{%s}\n",
    "\\end{figure}\n",
    "\\vfill \n",
    "\n",
    "\"\"\" % (latex,\n",
    "       comparison_caption,\n",
    "       overview_path_1.as_posix())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "tex_file = r'report2.tex'\n",
    "with open(tex_file, 'w', encoding='utf-8') as fid:\n",
    "    fid.write(r\"\"\"\n",
    "\\documentclass[landscape, DIV=99, 14pt]{scrartcl}\n",
    "\n",
    "\\renewcommand*{\\familydefault}{\\sfdefault}\n",
    "\n",
    "\\RequirePackage{graphicx}\n",
    "\\RequirePackage[utf8]{inputenc}\n",
    "\\RequirePackage{hyperref}\n",
    "\\RequirePackage[gen]{eurosym}\n",
    "\\RequirePackage{multirow}\n",
    "\\RequirePackage{booktabs}\n",
    "\\RequirePackage[table]{xcolor}\n",
    "\\RequirePackage{siunitx}\n",
    "\\RequirePackage{etoolbox}\n",
    "\\RequirePackage{etoolbox}\n",
    "\n",
    "\\setlength{\\parindent}{0cm}\n",
    "\n",
    "\\robustify\\bfseries\n",
    "\\robustify\\itshape\n",
    "\n",
    "\\begin{document}\n",
    "\n",
    "%s\n",
    "\n",
    "\\end{document}\n",
    "\"\"\" % (latex))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.system('pdflatex.exe %s' % (tex_file))\n",
    "os.system('pdflatex.exe %s' % (tex_file))\n",
    "os.system('start %spdf' % (tex_file[:-3]))"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}