oprava_nehodovost.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Oprava výpočtu nehodovosti\n",
    "\n",
    "CBA nezohľadňuje\n",
    "- neohlásené dopravné nehody\n",
    "- počet zranených v nehode\n",
    "\n",
    "Vo výpočte nehodovosti je ďalej použité nevhodné priemerovanie.\n",
    "\n",
    "Pre zachovanie štruktúry hárkov sa oprava deje na dvoch miestach\n",
    "- hárok 09-A Nehodovost - vypocet - korekcia počtu nehôd a oprava výpočtu celkovej nehodovosti\n",
    "- hárok 09 Nehodovost - v záujme zachovania tabuliek Počet nehôd a Náklady na nehodovosť je počet zranených zohľadnený v prepočte medzi tabuľkami"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import openpyxl as opx\n",
    "from os import listdir\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cesta k CBA excelom\n",
    "base_path = \n",
    "# cesta k vystupu\n",
    "out_path = \n",
    "\n",
    "# pozri dir\n",
    "dir_contents = listdir(base_path)\n",
    "# nechaj excely\n",
    "xlsx_files = list(filter(lambda x: 'xlsx' in x, dir_contents))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for cba_filename in xlsx_files:\n",
    "    # otvor excel\n",
    "    wb = opx.load_workbook(base_path + cba_filename)\n",
    "\n",
    "    # Oprava frekvencie nehôd\n",
    "    # CBA nezohľadňuje nenahlásené dopravné nehody.\n",
    "    # Tabuľka nekorigovaných hodnôt je na hárku `09-A Nehodovost - vypocet` v bunkách `N552:Q563`.\n",
    "    # Do tabuľky vpíšeme korigované hodnoty vypočítané z hodnôt v metodike uložených na hárku `A1 Parametre`.\n",
    "\n",
    "    sheet_name = '09-A Nehodovost - vypocet'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'N'\n",
    "    tl_row = '552'\n",
    "    # bottom right\n",
    "    br_col = 'Q'\n",
    "    br_row = '563'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "    # stlpce v tabulke frekvencie nehod na useku cesty\n",
    "    cols_lett = 'CDEF'\n",
    "\n",
    "    # iteruj stlpce - typ nehody\n",
    "    for id_col in range(n_cols):\n",
    "        # zakladna bunka je v prvom riadku\n",
    "        base_cell_coord = tab_nehodovost[0][id_col].coordinate\n",
    "\n",
    "        col_lett_nehoda = cols_lett[id_col]\n",
    "\n",
    "        # vzorec\n",
    "        # zaznamenana frekvencia nehod * korekcny faktor\n",
    "        base_formula = '=Parametre!' + col_lett_nehoda + '149' + '*' + \\\n",
    "                'Parametre!$C$' + str(140+id_col) # korekcia pre nehodu v riadkoch\n",
    "        # iteruj riadky - typ cesty\n",
    "        for row in range(0, n_rows):\n",
    "            current_coord = tab_nehodovost[row][id_col].coordinate\n",
    "            tab_nehodovost[row][id_col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "\n",
    "\n",
    "    # Oprava výpočtu počtu nehôd\n",
    "\n",
    "    # CBA počíta priemernú nehodovosť (hustotu nehôd) na jednotkovú dĺžku. \n",
    "    # Výpočet nie je správny, keďže jednotkové hodnoty nehodovosti sú udávané na vozkm. \n",
    "\n",
    "    # Je potrebné výpočet opraviť.     \n",
    "\n",
    "    # Počet nehôd vo variante V0\n",
    "\n",
    "    # Počty nehôd podľa závažnosti v jednotlivých rokoch sú v bunkách `K422:BC425`.\n",
    "\n",
    "    sheet_name = '09-A Nehodovost - vypocet'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'K'\n",
    "    tl_row = '422'\n",
    "    # bottom right\n",
    "    br_col = 'BC'\n",
    "    br_row = '425'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "\n",
    "    # stlpce v tabulke frekvencie nehod na useku cesty\n",
    "    cols_lett = 'GHIJ'\n",
    "\n",
    "    # iteruj riadky - typ nehody\n",
    "    for id_row in range(n_rows):\n",
    "        # zakladna bunka je v prvom stlpci\n",
    "        base_cell_coord = tab_nehodovost[id_row][0].coordinate\n",
    "\n",
    "        # stlpec v tabulke usekov s hodnotou frekvencie nehod \n",
    "        col_lett_nehoda = cols_lett[id_row]\n",
    "\n",
    "        # vzorec\n",
    "        # sum_i vozkm na useku * frekvencia nehod na danom useku / 100 000 000 vozkm\n",
    "        base_formula = '=SUMPRODUCT(K361:K410,$' + col_lett_nehoda + '$552:$' + col_lett_nehoda + '$601)/100000000'\n",
    "\n",
    "        for col in range(0, n_cols):\n",
    "            current_coord = tab_nehodovost[id_row][col].coordinate\n",
    "            tab_nehodovost[id_row][col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "\n",
    "\n",
    "    #### Počet nehôd vo variante V1 - pôvodná cesta\n",
    "\n",
    "    # Počty nehôd podľa závažnosti v jednotlivých rokoch sú v bunkách `K528:BC531`.\n",
    "\n",
    "    sheet_name = '09-A Nehodovost - vypocet'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'K'\n",
    "    tl_row = '528'\n",
    "    # bottom right\n",
    "    br_col = 'BC'\n",
    "    br_row = '531'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "    # stlpce v tabulke frekvencie nehod na useku cesty\n",
    "    cols_lett = 'WXYZ'\n",
    "\n",
    "    # iteruj riadky - typ nehody\n",
    "    for id_row in range(n_rows):\n",
    "        # zakladna bunka je v prvom stlpci\n",
    "        base_cell_coord = tab_nehodovost[id_row][0].coordinate\n",
    "\n",
    "        # stlpec v tabulke usekov s hodnotou frekvencie nehod \n",
    "        col_lett_nehoda = cols_lett[id_row]\n",
    "\n",
    "        # vzorec\n",
    "        # sum_i vozkm na useku * frekvencia nehod na danom useku / 100 000 000 vozkm\n",
    "        base_formula = '=SUMPRODUCT(K430:K479,$' + col_lett_nehoda + '$552:$' + col_lett_nehoda + '$601)/100000000'\n",
    "\n",
    "        for col in range(0, n_cols):\n",
    "            current_coord = tab_nehodovost[id_row][col].coordinate\n",
    "            tab_nehodovost[id_row][col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "\n",
    "    #### Počet nehôd vo variante V1 - nová cesta\n",
    "\n",
    "    #  Počty nehôd podľa závažnosti v jednotlivých rokoch sú v bunkách `K534:BC537`.\n",
    "\n",
    "    sheet_name = '09-A Nehodovost - vypocet'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'K'\n",
    "    tl_row = '534'\n",
    "    # bottom right\n",
    "    br_col = 'BC'\n",
    "    br_row = '537'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "    # stlpce v tabulke frekvencie nehod na useku cesty\n",
    "    cols_lett = 'GHIJ'\n",
    "\n",
    "    # iteruj riadky - typ nehody\n",
    "    for id_row in range(n_rows):\n",
    "        # zakladna bunka je v prvom stlpci\n",
    "        base_cell_coord = tab_nehodovost[id_row][0].coordinate\n",
    "\n",
    "        # stlpec v tabulke usekov s hodnotou frekvencie nehod \n",
    "        col_lett_nehoda = cols_lett[id_row]\n",
    "\n",
    "        # vzorec\n",
    "        # sum_i vozkm na useku * frekvencia nehod na danom useku / 100 000 000 vozkm\n",
    "        base_formula = '=SUMPRODUCT(K490:K507,$' + col_lett_nehoda + '$612:$' + col_lett_nehoda + '$629)/100000000'\n",
    "\n",
    "        for col in range(0, n_cols):\n",
    "            current_coord = tab_nehodovost[id_row][col].coordinate\n",
    "            tab_nehodovost[id_row][col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "\n",
    "    ### Zohľadnenie počtu zranení\n",
    "    # V0\n",
    "\n",
    "    sheet_name = '09 Nehodovost'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'D'\n",
    "    tl_row = '21'\n",
    "    # bottom right\n",
    "    br_col = 'AK'\n",
    "    br_row = '23'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "    # zakladna bunka je v prvom riadku a prvom stlpci\n",
    "    base_cell_coord = tab_nehodovost[0][0].coordinate\n",
    "\n",
    "    # vzorec\n",
    "    # povodny vzorec * pocet zranenych\n",
    "    base_formula = \"=D6*INDEX('A1 Parametre'!$D114:$BA114,MATCH(D$19,'A1 Parametre'!$D$113:$BA$113,0))\" + \\\n",
    "            \"*'A1 Parametre'!$I120\"\n",
    "\n",
    "    # iteruj riadky - typ nehody\n",
    "    for row in range(n_rows):\n",
    "        # iteruj stlpce  roky\n",
    "        for col in range(0, n_cols):\n",
    "            current_coord = tab_nehodovost[row][col].coordinate\n",
    "            tab_nehodovost[row][col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "\n",
    "    # V1\n",
    "    sheet_name = '09 Nehodovost'\n",
    "    ws = wb[sheet_name]\n",
    "\n",
    "    # top left\n",
    "    tl_col = 'D'\n",
    "    tl_row = '30'\n",
    "    # bottom right\n",
    "    br_col = 'AK'\n",
    "    br_row = '32'\n",
    "\n",
    "    tab_nehodovost = ws[tl_col+tl_row:br_col+br_row]\n",
    "    n_rows = len(tab_nehodovost)\n",
    "    n_cols = len(tab_nehodovost[0])\n",
    "\n",
    "    # zakladna bunka je v prvom riadku a prvom stlpci\n",
    "    base_cell_coord = tab_nehodovost[0][0].coordinate\n",
    "\n",
    "    # vzorec\n",
    "    # povodny vzorec * pocet zranenych\n",
    "    base_formula = \"=D13*INDEX('A1 Parametre'!$D114:$BA114,MATCH(D$19,'A1 Parametre'!$D$113:$BA$113,0))\" + \\\n",
    "            \"*'A1 Parametre'!$I120\"\n",
    "\n",
    "    # iteruj riadky - typ nehody\n",
    "    for row in range(n_rows):\n",
    "        # iteruj stlpce  roky\n",
    "        for col in range(0, n_cols):\n",
    "            current_coord = tab_nehodovost[row][col].coordinate\n",
    "            tab_nehodovost[row][col].value = opx.formula.translate.Translator(base_formula, \\\n",
    "                                            origin=base_cell_coord).translate_formula(current_coord)\n",
    "            \n",
    "            \n",
    "    cba_name = cba_filename.split('.')[0]\n",
    "    \n",
    "    # ulozenie vysledku\n",
    "    wb.save(out_path + cba_name + '_fix_nehodovost.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}