00_dataJoining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 00 Data joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import headers\n",
    "headers = pd.read_excel('hlavicky.xlsx').iloc[30]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select all files matching criteria\n",
    "all_filenames = [i for i in glob.glob('01_2019_*.csv')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#combine all files in the list + add header\n",
    "combined_csv = pd.concat([pd.read_csv(f, encoding='cp1250', sep=';', low_memory=False, header=None, names=headers) for f in all_filenames], sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>IDENTIFIKAČNÍ ČÍSLO</th>\n",
       "      <th>DRUH POZEMNÍ KOMUNIKACE</th>\n",
       "      <th>ČÍSLO POZEMNÍ KOMUNIKACE</th>\n",
       "      <th>den, měsíc, rok</th>\n",
       "      <th>nan</th>\n",
       "      <th>čas</th>\n",
       "      <th>DRUH NEHODY</th>\n",
       "      <th>DRUH SRÁŽKY JEDOUCÍCH VOZIDEL</th>\n",
       "      <th>DRUH PEVNÉ PŘEKÁŽKY</th>\n",
       "      <th>CHARAKTER NEHODY</th>\n",
       "      <th>...</th>\n",
       "      <th>nan.10</th>\n",
       "      <th>nan.11</th>\n",
       "      <th>nan.12</th>\n",
       "      <th>nan.13</th>\n",
       "      <th>nan.14</th>\n",
       "      <th>nan.15</th>\n",
       "      <th>nan.16</th>\n",
       "      <th>nan.17</th>\n",
       "      <th>nan.18</th>\n",
       "      <th>LOKALITA NEHODY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2100180006</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>1</td>\n",
       "      <td>2560</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>sledovanákomunikace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2100190001</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>uzel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2100190002</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>230</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>sledovanákomunikace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2100190003</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>240</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>místníkomunikace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.68551e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Souhlasnýsesměremúseku</td>\n",
       "      <td>Pomalý</td>\n",
       "      <td>554782.0</td>\n",
       "      <td>487856.0</td>\n",
       "      <td>GN_V0.1UIR-ADR_410</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2100190004</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>260</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>místníkomunikace</td>\n",
       "      <td>NaN</td>\n",
       "      <td>713690</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Souhlasnýsesměremúseku</td>\n",
       "      <td>Pomalý</td>\n",
       "      <td>554782.0</td>\n",
       "      <td>501417.0</td>\n",
       "      <td>GN_V0.1UIR-ADR_410</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   IDENTIFIKAČNÍ ČÍSLO  DRUH POZEMNÍ KOMUNIKACE  ČÍSLO POZEMNÍ KOMUNIKACE  \\\n",
       "0           2100180006                        5                       NaN   \n",
       "1           2100190001                        4                       NaN   \n",
       "2           2100190002                        5                       NaN   \n",
       "3           2100190003                        4                       NaN   \n",
       "4           2100190004                        6                       NaN   \n",
       "\n",
       "  den, měsíc, rok  NaN  čas   DRUH NEHODY  DRUH SRÁŽKY JEDOUCÍCH VOZIDEL  \\\n",
       "0      2018-12-31    1  2560            2                              0   \n",
       "1      2019-01-01    2    35            1                              4   \n",
       "2      2019-01-01    2   230            4                              0   \n",
       "3      2019-01-01    2   240            3                              0   \n",
       "4      2019-01-01    2   260            3                              0   \n",
       "\n",
       "   DRUH PEVNÉ PŘEKÁŽKY  CHARAKTER NEHODY  ...               nan.10  nan.11  \\\n",
       "0                    0                 2  ...  sledovanákomunikace     NaN   \n",
       "1                    0                 2  ...                 uzel     NaN   \n",
       "2                    0                 1  ...  sledovanákomunikace     NaN   \n",
       "3                    6                 2  ...     místníkomunikace     NaN   \n",
       "4                    9                 2  ...     místníkomunikace     NaN   \n",
       "\n",
       "        nan.12  nan.13                  nan.14  nan.15    nan.16    nan.17  \\\n",
       "0          NaN     NaN                     NaN     NaN       NaN       NaN   \n",
       "1          NaN     NaN                     NaN     NaN       NaN       NaN   \n",
       "2          NaN     NaN                     NaN     NaN       NaN       NaN   \n",
       "3  1.68551e+06     NaN  Souhlasnýsesměremúseku  Pomalý  554782.0  487856.0   \n",
       "4       713690     NaN  Souhlasnýsesměremúseku  Pomalý  554782.0  501417.0   \n",
       "\n",
       "               nan.18  LOKALITA NEHODY  \n",
       "0                 NaN                1  \n",
       "1                 NaN                1  \n",
       "2                 NaN                1  \n",
       "3  GN_V0.1UIR-ADR_410                1  \n",
       "4  GN_V0.1UIR-ADR_410                1  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets see the result\n",
    "combined_csv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8454, 64)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_csv.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([                      u'IDENTIFIKAČNÍ ČÍSLO',\n",
      "                         u'DRUH POZEMNÍ KOMUNIKACE',\n",
      "                        u'ČÍSLO POZEMNÍ KOMUNIKACE',\n",
      "                                 u'den, měsíc, rok',\n",
      "                                                nan,\n",
      "                                            u'čas ',\n",
      "                                     u'DRUH NEHODY',\n",
      "                   u'DRUH SRÁŽKY JEDOUCÍCH VOZIDEL',\n",
      "                             u'DRUH PEVNÉ PŘEKÁŽKY',\n",
      "                                u'CHARAKTER NEHODY',\n",
      "                                 u'ZAVINĚNÍ NEHODY',\n",
      "                u'ALKOHOL U VINÍKA NEHODY PŘÍTOMEN',\n",
      "                           u'HLAVNÍ PŘÍČINY NEHODY',\n",
      "                                   u'usmrceno osob',\n",
      "                              u'těžce zraněno osob',\n",
      "                              u'lehce zraněno osob',\n",
      "                            u'CELKOVÁ HMOTNÁ ŠKODA',\n",
      "                            u'DRUH POVRCHU VOZOVKY',\n",
      "              u'STAV POVRCHU VOZOVKY V DOBĚ NEHODY',\n",
      "                                 u'STAV KOMUNIKACE',\n",
      "             u'POVĚTRNOSTNÍ PODMÍNKY V DOBĚ NEHODY',\n",
      "                                     u'VIDITELNOST',\n",
      "                               u'ROZHLEDOVÉ POMĚRY',\n",
      "                               u'DĚLENÍ KOMUNIKACE',\n",
      "                  u'SITUOVÁNÍ NEHODY NA KOMUNIKACI',\n",
      "                    u'ŘÍZENÍ PROVOZU V DOBĚ NEHODY',\n",
      "                 u'MÍSTNÍ ÚPRAVA PŘEDNOSTI V JÍZDĚ',\n",
      "       u'SPECIFICKÁ MÍSTA A OBJEKTY V MÍSTĚ NEHODY',\n",
      "                                  u'SMĚROVÉ POMĚRY',\n",
      "                      u'POČET ZÚČASTNĚNÝCH VOZIDEL',\n",
      "                           u'MÍSTO DOPRAVNÍ NEHODY',\n",
      "                       u'DRUH KŘIŽUJÍCÍ KOMUNIKACE',\n",
      "                                    u'DRUH VOZIDLA',\n",
      "               u'VÝROBNÍ ZNAČKA MOTOROVÉHO VOZIDLA',\n",
      "                              u'ROK VÝROBY VOZIDLA',\n",
      "                        u'CHARAKTERISTIKA VOZIDLA ',\n",
      "                                            u'SMYK',\n",
      "                               u'VOZIDLO PO NEHODĚ',\n",
      "            u'ÚNIK PROVOZNÍCH, PŘEPRAVOVANÝCH HMOT',\n",
      "                u'ZPŮSOB VYPROŠTĚNÍ OSOB Z VOZIDLA',\n",
      "               u'SMĚR JÍZDY NEBO POSTAVENÍ VOZIDLA',\n",
      "                                u'ŠKODA NA VOZIDLE',\n",
      "                                u'KATEGORIE ŘIDIČE',\n",
      "                                     u'STAV ŘIDIČE',\n",
      "                         u'VNĚJŠÍ OVLIVNĚNÍ ŘIDIČE',\n",
      "                                           u'nan.1',\n",
      "                                           u'nan.2',\n",
      "                                           u'nan.3',\n",
      "                                           u'nan.4',\n",
      "                                           u'nan.5',\n",
      "                                           u'nan.6',\n",
      "                                           u'nan.7',\n",
      "                                           u'nan.8',\n",
      "                                           u'nan.9',\n",
      "                                          u'nan.10',\n",
      "                                          u'nan.11',\n",
      "                                          u'nan.12',\n",
      "                                          u'nan.13',\n",
      "                                          u'nan.14',\n",
      "                                          u'nan.15',\n",
      "                                          u'nan.16',\n",
      "                                          u'nan.17',\n",
      "                                          u'nan.18',\n",
      "                                 u'LOKALITA NEHODY'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(combined_csv.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#combined_csv.to_csv(\"praha_2019.csv\", index = False, encoding='cp1250')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "print(\"done\")"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}