ml-model.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f9fe9428",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94c1e6ae",
   "metadata": {},
   "source": [
    "## Elimizde 2 tane veri var. \n",
    "- Beşiktaş'a özel bir veriseti: besiktasHız.csv \n",
    "- Tüm ilçelerin karışık olduğu bir veriseti: fuseedİlce.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cc446476",
   "metadata": {},
   "outputs": [],
   "source": [
    "BESIKTAS_DATA = \"./data/BesiktasHiz.csv\"\n",
    "FUSEED_DATA = \"./data/Fuseed_Data_İlçe.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1cd7d49d",
   "metadata": {},
   "outputs": [],
   "source": [
    "besiktas_df = pd.read_csv(BESIKTAS_DATA, sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1b5dcf11",
   "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>vSegID</th>\n",
       "      <th>vSegDir</th>\n",
       "      <th>fusedYear</th>\n",
       "      <th>fusedMonth</th>\n",
       "      <th>fusedday</th>\n",
       "      <th>fusedHour</th>\n",
       "      <th>avgspeed</th>\n",
       "      <th>GRP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>470</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>108.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3597</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>33.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3639</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2143</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>551</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>41.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vSegID  vSegDir  fusedYear  fusedMonth  fusedday  fusedHour  avgspeed  \\\n",
       "0     470        1       2019           3        17          8     108.0   \n",
       "1    3597        0       2019           3         5          8      33.0   \n",
       "2    3639        1       2019           1         2         22      37.0   \n",
       "3    2143        0       2019           3         2         21      30.0   \n",
       "4     551        0       2019           1        12         21      41.0   \n",
       "\n",
       "       GRP  \n",
       "0  30-60DK  \n",
       "1  30-60DK  \n",
       "2   0-30DK  \n",
       "3  30-60DK  \n",
       "4  30-60DK  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "besiktas_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "50f4696e",
   "metadata": {},
   "outputs": [],
   "source": [
    "fuseed_df = pd.read_csv(FUSEED_DATA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c6c3b6c9",
   "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>vSegID</th>\n",
       "      <th>vSegDir</th>\n",
       "      <th>fusedYear</th>\n",
       "      <th>fusedMonth</th>\n",
       "      <th>fusedday</th>\n",
       "      <th>fusedHour</th>\n",
       "      <th>avgspeed</th>\n",
       "      <th>GRP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>305</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>23</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1107</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>49.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1728</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>14</td>\n",
       "      <td>73.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>762</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3023</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>25.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vSegID  vSegDir  fusedYear  fusedMonth  fusedday  fusedHour  avgspeed  \\\n",
       "0     305        1       2019           6        24         23      28.0   \n",
       "1    1107        1       2019           6         5          2      49.0   \n",
       "2    1728        0       2019           4        15         14      73.0   \n",
       "3     762        0       2019           5         6         10      30.0   \n",
       "4    3023        1       2019           5         4         18      25.0   \n",
       "\n",
       "       GRP  \n",
       "0   0-30DK  \n",
       "1  30-60DK  \n",
       "2   0-30DK  \n",
       "3   0-30DK  \n",
       "4  30-60DK  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuseed_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff295cb8",
   "metadata": {},
   "source": [
    "Tüm ilçelerden sadece beşiktaş verilerini filtreleyelim."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "630a853a",
   "metadata": {},
   "outputs": [],
   "source": [
    "fuseed_besiktas_df = fuseed_df[fuseed_df.vSegID.isin(besiktas_df.vSegID)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6eac4dae",
   "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>vSegID</th>\n",
       "      <th>vSegDir</th>\n",
       "      <th>fusedYear</th>\n",
       "      <th>fusedMonth</th>\n",
       "      <th>fusedday</th>\n",
       "      <th>fusedHour</th>\n",
       "      <th>avgspeed</th>\n",
       "      <th>GRP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1107</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>49.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3552</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>28.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3559</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>44.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3589</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3619</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>37.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    vSegID  vSegDir  fusedYear  fusedMonth  fusedday  fusedHour  avgspeed  \\\n",
       "1     1107        1       2019           6         5          2      49.0   \n",
       "10    3552        0       2019           6         1          8      28.0   \n",
       "15    3559        0       2019           4        14         20      44.0   \n",
       "24    3589        0       2019           6        10         16      34.0   \n",
       "26    3619        0       2019           4         6         12      37.0   \n",
       "\n",
       "        GRP  \n",
       "1   30-60DK  \n",
       "10  30-60DK  \n",
       "15  30-60DK  \n",
       "24   0-30DK  \n",
       "26  30-60DK  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuseed_besiktas_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e70b9bcd",
   "metadata": {},
   "source": [
    "Verileri birleştirerek ana dataframe'i oluşturalım."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a9efcecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = besiktas_df.append(fuseed_besiktas_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "721a7507",
   "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>vSegID</th>\n",
       "      <th>vSegDir</th>\n",
       "      <th>fusedYear</th>\n",
       "      <th>fusedMonth</th>\n",
       "      <th>fusedday</th>\n",
       "      <th>fusedHour</th>\n",
       "      <th>avgspeed</th>\n",
       "      <th>GRP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>470</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>108.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3597</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>33.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3639</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2143</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>551</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>41.0</td>\n",
       "      <td>30-60DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vSegID  vSegDir  fusedYear  fusedMonth  fusedday  fusedHour  avgspeed  \\\n",
       "0     470        1       2019           3        17          8     108.0   \n",
       "1    3597        0       2019           3         5          8      33.0   \n",
       "2    3639        1       2019           1         2         22      37.0   \n",
       "3    2143        0       2019           3         2         21      30.0   \n",
       "4     551        0       2019           1        12         21      41.0   \n",
       "\n",
       "       GRP  \n",
       "0  30-60DK  \n",
       "1  30-60DK  \n",
       "2   0-30DK  \n",
       "3  30-60DK  \n",
       "4  30-60DK  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d0b09c9",
   "metadata": {},
   "source": [
    "## Toplam Satır Sayıları"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "741c13a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Besiktas: 938941 satır\n",
      "Tüm ilçelerde Beşiktaş: 921576 satır\n",
      "Toplam Besiktas: 1860517 satır\n"
     ]
    }
   ],
   "source": [
    "print(f'Besiktas: {len(besiktas_df.index)} satır')\n",
    "print(f'Tüm ilçelerde Beşiktaş: {len(fuseed_besiktas_df.index)} satır')\n",
    "print(f'Toplam Besiktas: {len(df.index)} satır')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "526fb979",
   "metadata": {},
   "source": [
    "## Segment ID'lere karşılık gelen enlem boylam verileri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3de68f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "segment_list = pd.read_excel(\"./data/bjk-segment.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7f6a198a",
   "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>shape</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58</td>\n",
       "      <td>LINESTRING ( 29.01163774408752 41.080480711216...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>58</td>\n",
       "      <td>LINESTRING ( 29.01837697841569 41.088996478005...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>LINESTRING ( 29.01851958661052 41.088856751239...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59</td>\n",
       "      <td>LINESTRING ( 29.03193200006262 41.092135999577...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62</td>\n",
       "      <td>LINESTRING ( 29.00868344598513 41.050657973587...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                              shape\n",
       "0  58  LINESTRING ( 29.01163774408752 41.080480711216...\n",
       "1  58  LINESTRING ( 29.01837697841569 41.088996478005...\n",
       "2  59  LINESTRING ( 29.01851958661052 41.088856751239...\n",
       "3  59  LINESTRING ( 29.03193200006262 41.092135999577...\n",
       "4  62  LINESTRING ( 29.00868344598513 41.050657973587..."
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segment_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "851afae0",
   "metadata": {},
   "source": [
    "# Seçilen yol --> BEŞİKTAŞ BARBAROS BULVARI SEGMENTID = 65"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c35bc0ac",
   "metadata": {},
   "source": [
    "## Seçilen SegmentID'ye Göre Filtrele\n",
    "- Bu yol için hem gidiş hem dönüş verisi var. Şimdilik gidiş yönünü ele alalım. vSegDir = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "08177194",
   "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>vSegID</th>\n",
       "      <th>vSegDir</th>\n",
       "      <th>fusedYear</th>\n",
       "      <th>fusedMonth</th>\n",
       "      <th>fusedday</th>\n",
       "      <th>fusedHour</th>\n",
       "      <th>avgspeed</th>\n",
       "      <th>GRP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>12</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0-30DK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     vSegID  vSegDir  fusedYear  fusedMonth  fusedday  fusedHour  avgspeed  \\\n",
       "287      65        0       2019           1        29         12      13.0   \n",
       "338      65        0       2019           1        10          9      41.0   \n",
       "511      65        0       2019           1        17          6      59.0   \n",
       "653      65        0       2019           1         7          1      54.0   \n",
       "657      65        0       2019           3        19         19      47.0   \n",
       "\n",
       "        GRP  \n",
       "287  0-30DK  \n",
       "338  0-30DK  \n",
       "511  0-30DK  \n",
       "653  0-30DK  \n",
       "657  0-30DK  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.loc[df['vSegID'] == 65]\n",
    "df = df.loc[df['vSegDir'] == 0]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dc312c7",
   "metadata": {},
   "source": [
    "## Yol Bakım\n",
    "2 adet bakım calısması veriseti var.\n",
    "\n",
    "- İki verisetini birleştir.\n",
    "- Beşiktaş Barbaros Bulvarına göre filtrele."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5c605ecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "yol_bakim1 = pd.read_csv(\"./data/bakim-veri/yol_bakım.csv\")\n",
    "yol_bakim2 = pd.read_csv(\"./data/bakim-veri/yol_bakim_2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ef96d7a5",
   "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>id</th>\n",
       "      <th>isin_cinsi</th>\n",
       "      <th>ilce</th>\n",
       "      <th>yol_adi</th>\n",
       "      <th>isin_adi</th>\n",
       "      <th>tarih</th>\n",
       "      <th>gece</th>\n",
       "      <th>harita</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>133679</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-01-05</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133950</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-01-09</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>134346</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-01-13</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>134579</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-01-14</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>134691</td>\n",
       "      <td>ASFALT</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(YILDIZ MAH)</td>\n",
       "      <td>ROBOT (YAMA)</td>\n",
       "      <td>2019-01-15</td>\n",
       "      <td>EVET</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.051016...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>152829</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>41.058968</td>\n",
       "      <td>29.01062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>153422</td>\n",
       "      <td>ASFALT</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>ROBOT (YAMA)</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>EVET</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>41.058968</td>\n",
       "      <td>29.01062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>154041</td>\n",
       "      <td>KORKULUK YAPIMI - ONARIMI</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>OTO KORKULUK</td>\n",
       "      <td>2019-05-26</td>\n",
       "      <td>EVET</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>41.058968</td>\n",
       "      <td>29.01062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>154664</td>\n",
       "      <td>KORKULUK YAPIMI - ONARIMI</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>OTO KORKULUK</td>\n",
       "      <td>2019-05-29</td>\n",
       "      <td>EVET</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>41.058968</td>\n",
       "      <td>29.01062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>155375</td>\n",
       "      <td>UTK</td>\n",
       "      <td>BEŞİKTAŞ</td>\n",
       "      <td>BARBAROS BULVARI(BALMUMCU MAH)</td>\n",
       "      <td>TRETUVAR - PARKE ÇALIŞMASI</td>\n",
       "      <td>2019-06-10</td>\n",
       "      <td>HAYIR</td>\n",
       "      <td>http://sehirharitasi.ibb.gov.tr/?lat=41.058967...</td>\n",
       "      <td>41.058968</td>\n",
       "      <td>29.01062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                 isin_cinsi      ilce  \\\n",
       "0   133679                        UTK  BEŞİKTAŞ   \n",
       "1   133950                        UTK  BEŞİKTAŞ   \n",
       "2   134346                        UTK  BEŞİKTAŞ   \n",
       "3   134579                        UTK  BEŞİKTAŞ   \n",
       "4   134691                     ASFALT  BEŞİKTAŞ   \n",
       "..     ...                        ...       ...   \n",
       "68  152829                        UTK  BEŞİKTAŞ   \n",
       "69  153422                     ASFALT  BEŞİKTAŞ   \n",
       "70  154041  KORKULUK YAPIMI - ONARIMI  BEŞİKTAŞ   \n",
       "71  154664  KORKULUK YAPIMI - ONARIMI  BEŞİKTAŞ   \n",
       "72  155375                        UTK  BEŞİKTAŞ   \n",
       "\n",
       "                           yol_adi                    isin_adi       tarih  \\\n",
       "0   BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-01-05   \n",
       "1   BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-01-09   \n",
       "2   BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-01-13   \n",
       "3   BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-01-14   \n",
       "4     BARBAROS BULVARI(YILDIZ MAH)                ROBOT (YAMA)  2019-01-15   \n",
       "..                             ...                         ...         ...   \n",
       "68  BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-05-16   \n",
       "69  BARBAROS BULVARI(BALMUMCU MAH)                ROBOT (YAMA)  2019-05-21   \n",
       "70  BARBAROS BULVARI(BALMUMCU MAH)                OTO KORKULUK  2019-05-26   \n",
       "71  BARBAROS BULVARI(BALMUMCU MAH)                OTO KORKULUK  2019-05-29   \n",
       "72  BARBAROS BULVARI(BALMUMCU MAH)  TRETUVAR - PARKE ÇALIŞMASI  2019-06-10   \n",
       "\n",
       "     gece                                             harita        lat  \\\n",
       "0   HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...   0.000000   \n",
       "1   HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...   0.000000   \n",
       "2   HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...   0.000000   \n",
       "3   HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...   0.000000   \n",
       "4    EVET  http://sehirharitasi.ibb.gov.tr/?lat=41.051016...   0.000000   \n",
       "..    ...                                                ...        ...   \n",
       "68  HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...  41.058968   \n",
       "69   EVET  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...  41.058968   \n",
       "70   EVET  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...  41.058968   \n",
       "71   EVET  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...  41.058968   \n",
       "72  HAYIR  http://sehirharitasi.ibb.gov.tr/?lat=41.058967...  41.058968   \n",
       "\n",
       "         lon  \n",
       "0    0.00000  \n",
       "1    0.00000  \n",
       "2    0.00000  \n",
       "3    0.00000  \n",
       "4    0.00000  \n",
       "..       ...  \n",
       "68  29.01062  \n",
       "69  29.01062  \n",
       "70  29.01062  \n",
       "71  29.01062  \n",
       "72  29.01062  \n",
       "\n",
       "[73 rows x 10 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.concat([yol_bakim1, yol_bakim2])\n",
    "result = result.loc[result[\"ilce\"] == \"BEŞİKTAŞ\"].reset_index(drop=True)\n",
    "result.loc[result['yol_adi'].str.contains(\"BARBAROS\")].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51186c52",
   "metadata": {},
   "source": [
    "## TKM Duyurular"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "001b4c04",
   "metadata": {},
   "outputs": [],
   "source": [
    "tkm_duyuru = pd.read_csv(\"../Trafik Analizi Verileri/Microsoft_azure_calisma/TKM_Duyurular.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "94cb1c29",
   "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>DUYURUID</th>\n",
       "      <th>DUYURUTIPI</th>\n",
       "      <th>DUYURUTIPI_DESC</th>\n",
       "      <th>KULLANICIID</th>\n",
       "      <th>DUYURUBASLIK</th>\n",
       "      <th>DUYURUMETIN</th>\n",
       "      <th>DUYURULINK</th>\n",
       "      <th>KAZALINK</th>\n",
       "      <th>GIRISTARIHI</th>\n",
       "      <th>BITISTARIHI</th>\n",
       "      <th>...</th>\n",
       "      <th>ULASIMMOD</th>\n",
       "      <th>DUYURUBASLIKING</th>\n",
       "      <th>DUYURUMETINING</th>\n",
       "      <th>MOBILKULLANICIBILDIRIMIACIKLAMA</th>\n",
       "      <th>GIRISYAPANBIRIM</th>\n",
       "      <th>KAZAKARANOKTATIPI</th>\n",
       "      <th>KazaKaraNoktaTipi_desc</th>\n",
       "      <th>VSEGID</th>\n",
       "      <th>VSEGDIR</th>\n",
       "      <th>insert_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>478018</td>\n",
       "      <td>17</td>\n",
       "      <td>Bakım-Onarım Çalışması</td>\n",
       "      <td>696</td>\n",
       "      <td>Balmumcu Alt Geçiti  Beşiktaş - Zincirlikuyu Y...</td>\n",
       "      <td>Balmumcu Alt Geçiti Beşiktaş - Zincirlikuyu Y...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-09 01:37:11</td>\n",
       "      <td>2019-03-09 06:00:00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Balmumcu Alt Geçiti  Beşiktaş - Zincirlikuyu d...</td>\n",
       "      <td>Alt Geçit Balmumcu Beşiktaş - Zincirlikuyu dir...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-09-27 12:10:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>474266</td>\n",
       "      <td>17</td>\n",
       "      <td>Bakım-Onarım Çalışması</td>\n",
       "      <td>162</td>\n",
       "      <td>Beşiktaş Meydanı Çift Yönlü  Bakım-Onarım Çalı...</td>\n",
       "      <td>Beşiktaş Meydanı Çift Yönlü Bakım-Onarım Çalış...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-17 00:30:59</td>\n",
       "      <td>2019-01-17 04:54:00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Beşiktaş Meydanı Çift Yönlü maintenance and re...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-09-27 12:10:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>474267</td>\n",
       "      <td>17</td>\n",
       "      <td>Bakım-Onarım Çalışması</td>\n",
       "      <td>162</td>\n",
       "      <td>Beşiktaş - Dolmabahçe Yönü, sağ şerit Bakım-On...</td>\n",
       "      <td>Beşiktaş - Dolmabahçe Yönü, sağ şerit Bakım-On...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-17 01:31:27</td>\n",
       "      <td>2019-01-17 04:00:00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Beşiktaş - Dolmabahçe direction, right lane ma...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-09-27 12:10:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>475336</td>\n",
       "      <td>17</td>\n",
       "      <td>Bakım-Onarım Çalışması</td>\n",
       "      <td>550</td>\n",
       "      <td>Büyükdere Caddesi Levent - Beşiktaş Yönü, sağ ...</td>\n",
       "      <td>Büyükdere Caddesi Levent - Beşiktaş Yönü, sağ ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-01-31 10:27:58</td>\n",
       "      <td>2019-01-31 11:30:00</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Büyükdere Caddesi Levent - Beşiktaş direction,...</td>\n",
       "      <td>Büyükdere Caddesi Levent - Beşiktaş direction,...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-09-27 12:10:03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     DUYURUID  DUYURUTIPI         DUYURUTIPI_DESC  KULLANICIID  \\\n",
       "13     478018          17  Bakım-Onarım Çalışması          696   \n",
       "257    474266          17  Bakım-Onarım Çalışması          162   \n",
       "258    474267          17  Bakım-Onarım Çalışması          162   \n",
       "288    475336          17  Bakım-Onarım Çalışması          550   \n",
       "\n",
       "                                          DUYURUBASLIK  \\\n",
       "13   Balmumcu Alt Geçiti  Beşiktaş - Zincirlikuyu Y...   \n",
       "257  Beşiktaş Meydanı Çift Yönlü  Bakım-Onarım Çalı...   \n",
       "258  Beşiktaş - Dolmabahçe Yönü, sağ şerit Bakım-On...   \n",
       "288  Büyükdere Caddesi Levent - Beşiktaş Yönü, sağ ...   \n",
       "\n",
       "                                           DUYURUMETIN DUYURULINK  KAZALINK  \\\n",
       "13    Balmumcu Alt Geçiti Beşiktaş - Zincirlikuyu Y...        NaN       NaN   \n",
       "257  Beşiktaş Meydanı Çift Yönlü Bakım-Onarım Çalış...        NaN       NaN   \n",
       "258  Beşiktaş - Dolmabahçe Yönü, sağ şerit Bakım-On...        NaN       NaN   \n",
       "288  Büyükdere Caddesi Levent - Beşiktaş Yönü, sağ ...        NaN       NaN   \n",
       "\n",
       "             GIRISTARIHI          BITISTARIHI  ... ULASIMMOD  \\\n",
       "13   2019-03-09 01:37:11  2019-03-09 06:00:00  ...       NaN   \n",
       "257  2019-01-17 00:30:59  2019-01-17 04:54:00  ...       NaN   \n",
       "258  2019-01-17 01:31:27  2019-01-17 04:00:00  ...       NaN   \n",
       "288  2019-01-31 10:27:58  2019-01-31 11:30:00  ...       NaN   \n",
       "\n",
       "                                       DUYURUBASLIKING  \\\n",
       "13   Balmumcu Alt Geçiti  Beşiktaş - Zincirlikuyu d...   \n",
       "257                                                NaN   \n",
       "258                                                NaN   \n",
       "288  Büyükdere Caddesi Levent - Beşiktaş direction,...   \n",
       "\n",
       "                                        DUYURUMETINING  \\\n",
       "13   Alt Geçit Balmumcu Beşiktaş - Zincirlikuyu dir...   \n",
       "257  Beşiktaş Meydanı Çift Yönlü maintenance and re...   \n",
       "258  Beşiktaş - Dolmabahçe direction, right lane ma...   \n",
       "288  Büyükdere Caddesi Levent - Beşiktaş direction,...   \n",
       "\n",
       "     MOBILKULLANICIBILDIRIMIACIKLAMA  GIRISYAPANBIRIM  KAZAKARANOKTATIPI  \\\n",
       "13                               NaN                0                NaN   \n",
       "257                              NaN                1                NaN   \n",
       "258                              NaN                1                NaN   \n",
       "288                              NaN                1                NaN   \n",
       "\n",
       "     KazaKaraNoktaTipi_desc VSEGID  VSEGDIR          insert_time  \n",
       "13                      NaN    NaN      NaN  2019-09-27 12:10:03  \n",
       "257                     NaN    NaN      NaN  2019-09-27 12:10:03  \n",
       "258                     NaN    NaN      NaN  2019-09-27 12:10:03  \n",
       "288                     NaN    NaN      NaN  2019-09-27 12:10:03  \n",
       "\n",
       "[4 rows x 52 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tkm_duyuru.loc[tkm_duyuru['DUYURUBASLIK'].str.contains(\"Beşiktaş\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a7409e7",
   "metadata": {},
   "source": [
    "# Yol bakım verileri saat bazlı değil. Ana veriye eklenmeyecek."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93f2fa1c",
   "metadata": {},
   "source": [
    "## Sütun İsimleri Düzenleme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c33881d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'vSegID': 'SegmentID',\n",
    "                   'vSegDir': 'SegmentDirection',\n",
    "                   'fusedYear': 'Year',\n",
    "                   'fusedMonth': 'Month',\n",
    "                   'fusedday' : 'Day',\n",
    "                   'fusedHour' : 'Hour' ,\n",
    "                   'avgspeed': 'AverageSpeed',\n",
    "                   'GRP' : 'Minute'\n",
    "                  },\n",
    "          inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc86bda4",
   "metadata": {},
   "source": [
    "### Yıl, Ay, Gün, Saat ile DateTime Bilgisi Oluşturma\n",
    "- Bu sütun hem sıralama hem gruplamada gerekli olacak."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4786c34c",
   "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>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>Minute</th>\n",
       "      <th>DateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>12</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-01-29 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-01-10 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-01-17 06:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-01-07 01:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-03-19 19:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SegmentID  SegmentDirection  Year  Month  Day  Hour  AverageSpeed  \\\n",
       "287         65                 0  2019      1   29    12          13.0   \n",
       "338         65                 0  2019      1   10     9          41.0   \n",
       "511         65                 0  2019      1   17     6          59.0   \n",
       "653         65                 0  2019      1    7     1          54.0   \n",
       "657         65                 0  2019      3   19    19          47.0   \n",
       "\n",
       "     Minute            DateTime  \n",
       "287  0-30DK 2019-01-29 12:00:00  \n",
       "338  0-30DK 2019-01-10 09:00:00  \n",
       "511  0-30DK 2019-01-17 06:00:00  \n",
       "653  0-30DK 2019-01-07 01:00:00  \n",
       "657  0-30DK 2019-03-19 19:00:00  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['DateTime'] = pd.to_datetime(df[['Year','Month','Day','Hour']])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "93102011",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/md/venv/jupyter-venv/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "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>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>Minute</th>\n",
       "      <th>DateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>165013</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-03-11 02:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540315</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>54.0</td>\n",
       "      <td>30-60DK</td>\n",
       "      <td>2019-03-11 02:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        SegmentID  SegmentDirection  Year  Month  Day  Hour  AverageSpeed  \\\n",
       "165013         65                 0  2019      3   11     2          42.0   \n",
       "540315         65                 0  2019      3   11     2          54.0   \n",
       "\n",
       "         Minute            DateTime  \n",
       "165013   0-30DK 2019-03-11 02:00:00  \n",
       "540315  30-60DK 2019-03-11 02:00:00  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['DateTime'] == pd.datetime(2019,3,11, 2)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "75d0fcac",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/md/venv/jupyter-venv/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "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>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>Minute</th>\n",
       "      <th>DateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1279371</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>23</td>\n",
       "      <td>11</td>\n",
       "      <td>45.0</td>\n",
       "      <td>30-60DK</td>\n",
       "      <td>2019-04-23 11:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4841946</th>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>23</td>\n",
       "      <td>11</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0-30DK</td>\n",
       "      <td>2019-04-23 11:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SegmentID  SegmentDirection  Year  Month  Day  Hour  AverageSpeed  \\\n",
       "1279371         65                 0  2019      4   23    11          45.0   \n",
       "4841946         65                 0  2019      4   23    11          38.0   \n",
       "\n",
       "          Minute            DateTime  \n",
       "1279371  30-60DK 2019-04-23 11:00:00  \n",
       "4841946   0-30DK 2019-04-23 11:00:00  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['DateTime'] == pd.datetime(2019,4,23, 11)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54bd7886",
   "metadata": {},
   "source": [
    "## Aynı saatte ölçülmüş 15. ve 45. dakikalardaki veriyi gruplayarak saat bazlı ortalamayla ilerlenecek."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1187d0bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby([\"DateTime\"]).mean().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "612ad126",
   "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>DateTime</th>\n",
       "      <th>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 02:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 03:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>24.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 04:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DateTime  SegmentID  SegmentDirection  Year  Month  Day  Hour  \\\n",
       "0 2019-01-01 00:00:00         65                 0  2019      1    1     0   \n",
       "1 2019-01-01 01:00:00         65                 0  2019      1    1     1   \n",
       "2 2019-01-01 02:00:00         65                 0  2019      1    1     2   \n",
       "3 2019-01-01 03:00:00         65                 0  2019      1    1     3   \n",
       "4 2019-01-01 04:00:00         65                 0  2019      1    1     4   \n",
       "\n",
       "   AverageSpeed  \n",
       "0          32.5  \n",
       "1          20.0  \n",
       "2          36.0  \n",
       "3          24.5  \n",
       "4          49.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3646f2f9",
   "metadata": {},
   "source": [
    "## Yıllık Tatil Günleri Sütunu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a7a5905a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-01-01',\n",
       " '2019-04-23',\n",
       " '2019-05-01',\n",
       " '2019-05-19',\n",
       " '2019-06-04',\n",
       " '2019-06-05',\n",
       " '2019-06-06']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays = pd.read_excel(\"data/resmi_tatiller.xlsx\")\n",
    "holiday_dates = list(holidays['tarih'])\n",
    "\n",
    "holiday_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3ba47c06",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['isNationalHoliday'] = df['DateTime'].apply(lambda x : 1 if str(x.date()) in holiday_dates else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91bd6473",
   "metadata": {},
   "source": [
    "### Datetime Kullanarak Haftasonu Sütunu Ekleme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "27f3d02e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DayName'] = df.apply(lambda tr: tr['DateTime'].day_name(), axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "dcb1d0e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['isWeekend'] = df.apply(lambda tr: 1 if tr['DayName'] in [\"Saturday\", \"Sunday\"] else 0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "557288b5",
   "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>DateTime</th>\n",
       "      <th>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>isNationalHoliday</th>\n",
       "      <th>DayName</th>\n",
       "      <th>isWeekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 02:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 03:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>24.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 04:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DateTime  SegmentID  SegmentDirection  Year  Month  Day  Hour  \\\n",
       "0 2019-01-01 00:00:00         65                 0  2019      1    1     0   \n",
       "1 2019-01-01 01:00:00         65                 0  2019      1    1     1   \n",
       "2 2019-01-01 02:00:00         65                 0  2019      1    1     2   \n",
       "3 2019-01-01 03:00:00         65                 0  2019      1    1     3   \n",
       "4 2019-01-01 04:00:00         65                 0  2019      1    1     4   \n",
       "\n",
       "   AverageSpeed  isNationalHoliday  DayName  isWeekend  \n",
       "0          32.5                  1  Tuesday          0  \n",
       "1          20.0                  1  Tuesday          0  \n",
       "2          36.0                  1  Tuesday          0  \n",
       "3          24.5                  1  Tuesday          0  \n",
       "4          49.0                  1  Tuesday          0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a75041c",
   "metadata": {},
   "source": [
    "### Okul Tatil Günleri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "431d12cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-01-19',\n",
       " '2019-01-20',\n",
       " '2019-01-21',\n",
       " '2019-01-22',\n",
       " '2019-01-23',\n",
       " '2019-01-24',\n",
       " '2019-01-25',\n",
       " '2019-01-26',\n",
       " '2019-01-27',\n",
       " '2019-01-28',\n",
       " '2019-01-29',\n",
       " '2019-01-30',\n",
       " '2019-01-31',\n",
       " '2019-02-01',\n",
       " '2019-02-02',\n",
       " '2019-02-03',\n",
       " '2019-06-15',\n",
       " '2019-06-16',\n",
       " '2019-06-17',\n",
       " '2019-06-18',\n",
       " '2019-06-19',\n",
       " '2019-06-20',\n",
       " '2019-06-21',\n",
       " '2019-06-22',\n",
       " '2019-06-23',\n",
       " '2019-06-24',\n",
       " '2019-06-25',\n",
       " '2019-06-26',\n",
       " '2019-06-27',\n",
       " '2019-06-28',\n",
       " '2019-06-29',\n",
       " '2019-06-30']"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "school_hols = pd.read_csv(\"./data/Okul-Tatilleri.csv\")\n",
    "school_holiday_dates = list(school_hols['Okul Tatilleri'])\n",
    "school_holiday_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e6ac43d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['isSchoolHoliday'] = df['DateTime'].apply(lambda x : 1 if str(x.date()) in school_holiday_dates else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "2f7234ac",
   "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>DateTime</th>\n",
       "      <th>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>isNationalHoliday</th>\n",
       "      <th>DayName</th>\n",
       "      <th>isWeekend</th>\n",
       "      <th>isSchoolHoliday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 02:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 03:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>24.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 04:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-01-01 05:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>54.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-01-01 06:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>60.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-01-01 07:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>61.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-01-01 08:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-01-01 09:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DateTime  SegmentID  SegmentDirection  Year  Month  Day  Hour  \\\n",
       "0 2019-01-01 00:00:00         65                 0  2019      1    1     0   \n",
       "1 2019-01-01 01:00:00         65                 0  2019      1    1     1   \n",
       "2 2019-01-01 02:00:00         65                 0  2019      1    1     2   \n",
       "3 2019-01-01 03:00:00         65                 0  2019      1    1     3   \n",
       "4 2019-01-01 04:00:00         65                 0  2019      1    1     4   \n",
       "5 2019-01-01 05:00:00         65                 0  2019      1    1     5   \n",
       "6 2019-01-01 06:00:00         65                 0  2019      1    1     6   \n",
       "7 2019-01-01 07:00:00         65                 0  2019      1    1     7   \n",
       "8 2019-01-01 08:00:00         65                 0  2019      1    1     8   \n",
       "9 2019-01-01 09:00:00         65                 0  2019      1    1     9   \n",
       "\n",
       "   AverageSpeed  isNationalHoliday  DayName  isWeekend  isSchoolHoliday  \n",
       "0          32.5                  1  Tuesday          0                0  \n",
       "1          20.0                  1  Tuesday          0                0  \n",
       "2          36.0                  1  Tuesday          0                0  \n",
       "3          24.5                  1  Tuesday          0                0  \n",
       "4          49.0                  1  Tuesday          0                0  \n",
       "5          54.5                  1  Tuesday          0                0  \n",
       "6          60.5                  1  Tuesday          0                0  \n",
       "7          61.0                  1  Tuesday          0                0  \n",
       "8          60.0                  1  Tuesday          0                0  \n",
       "9          59.0                  1  Tuesday          0                0  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70b3a034",
   "metadata": {},
   "source": [
    "# Trafik Oranlarını Sınıflandırma\n",
    "\n",
    "- ML modeli 0-90 arası bir hız tahmini yapmak yerine, düşük,ortalama,yüksek hız şeklinde sınıflandırma yapmalı.\n",
    "\n",
    "### K-means ile Kümeleme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "91beb75c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.cluster import KMeans\n",
    "km = KMeans(n_clusters=3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "3479467d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['label'] = km.fit_predict(df[['AverageSpeed']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5be33ac0",
   "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>label</th>\n",
       "      <th>AverageSpeed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>32.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>24.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4327</th>\n",
       "      <td>1</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4328</th>\n",
       "      <td>1</td>\n",
       "      <td>32.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4329</th>\n",
       "      <td>1</td>\n",
       "      <td>41.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4330</th>\n",
       "      <td>1</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4331</th>\n",
       "      <td>1</td>\n",
       "      <td>44.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4332 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      label  AverageSpeed\n",
       "0         1          32.5\n",
       "1         0          20.0\n",
       "2         1          36.0\n",
       "3         0          24.5\n",
       "4         1          49.0\n",
       "...     ...           ...\n",
       "4327      1          35.0\n",
       "4328      1          32.5\n",
       "4329      1          41.5\n",
       "4330      1          41.0\n",
       "4331      1          44.5\n",
       "\n",
       "[4332 rows x 2 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['label', 'AverageSpeed']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d6d24f53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8.5, 30.0)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLASS0 = df.loc[df['label'] == 0]['AverageSpeed'].min(), df.loc[df['label'] == 0]['AverageSpeed'].max()\n",
    "CLASS0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c0167580",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30.5, 49.5)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLASS1 = df.loc[df['label'] == 1]['AverageSpeed'].min(), df.loc[df['label'] == 1]['AverageSpeed'].max()\n",
    "CLASS1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9d219151",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50.0, 75.5)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLASS2 = df.loc[df['label'] == 2]['AverageSpeed'].min(), df.loc[df['label'] == 2]['AverageSpeed'].max()\n",
    "CLASS2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ea5f4f4",
   "metadata": {},
   "source": [
    "## Yağmur Verisi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "0aeff9b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "rain_df = pd.read_csv(\"./ETL/saatlik_ortalamalar.csv\", sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a9fd54c0",
   "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>﻿tarih</th>\n",
       "      <th>saat</th>\n",
       "      <th>ort_sicaklik</th>\n",
       "      <th>ort_nem</th>\n",
       "      <th>ort_ruzgar</th>\n",
       "      <th>ort_yagis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>0</td>\n",
       "      <td>4.821967</td>\n",
       "      <td>91.820000</td>\n",
       "      <td>1.028500</td>\n",
       "      <td>34.728967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>1</td>\n",
       "      <td>4.740767</td>\n",
       "      <td>92.513333</td>\n",
       "      <td>0.854500</td>\n",
       "      <td>115.645617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>2</td>\n",
       "      <td>4.828300</td>\n",
       "      <td>92.213333</td>\n",
       "      <td>0.896167</td>\n",
       "      <td>87.970833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-02-21</td>\n",
       "      <td>5</td>\n",
       "      <td>5.455385</td>\n",
       "      <td>98.003846</td>\n",
       "      <td>0.746538</td>\n",
       "      <td>27.530000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>3</td>\n",
       "      <td>7.335153</td>\n",
       "      <td>95.596610</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>32.268814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>4</td>\n",
       "      <td>8.557544</td>\n",
       "      <td>92.192982</td>\n",
       "      <td>0.876491</td>\n",
       "      <td>15.318070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>7</td>\n",
       "      <td>22.657167</td>\n",
       "      <td>88.507000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>45.087167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>16</td>\n",
       "      <td>24.722787</td>\n",
       "      <td>67.755246</td>\n",
       "      <td>1.260164</td>\n",
       "      <td>15.918361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>17</td>\n",
       "      <td>24.255424</td>\n",
       "      <td>68.891017</td>\n",
       "      <td>0.992881</td>\n",
       "      <td>18.043559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>18</td>\n",
       "      <td>24.325000</td>\n",
       "      <td>70.606724</td>\n",
       "      <td>0.820172</td>\n",
       "      <td>23.874483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>20</td>\n",
       "      <td>23.354667</td>\n",
       "      <td>76.427500</td>\n",
       "      <td>0.945000</td>\n",
       "      <td>15.145667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>21</td>\n",
       "      <td>23.070833</td>\n",
       "      <td>85.828333</td>\n",
       "      <td>1.248667</td>\n",
       "      <td>16.450167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>22</td>\n",
       "      <td>22.614483</td>\n",
       "      <td>86.341379</td>\n",
       "      <td>0.904483</td>\n",
       "      <td>18.143103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-06-21</td>\n",
       "      <td>5</td>\n",
       "      <td>23.958033</td>\n",
       "      <td>86.181967</td>\n",
       "      <td>1.103115</td>\n",
       "      <td>21.273607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>0</td>\n",
       "      <td>23.153500</td>\n",
       "      <td>82.447667</td>\n",
       "      <td>0.542667</td>\n",
       "      <td>30.163000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>1</td>\n",
       "      <td>22.806441</td>\n",
       "      <td>83.528814</td>\n",
       "      <td>0.451017</td>\n",
       "      <td>16.186610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>3</td>\n",
       "      <td>23.212131</td>\n",
       "      <td>81.813770</td>\n",
       "      <td>0.505246</td>\n",
       "      <td>21.623607</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ﻿tarih  saat  ort_sicaklik    ort_nem  ort_ruzgar   ort_yagis\n",
       "0   2019-02-11     0      4.821967  91.820000    1.028500   34.728967\n",
       "1   2019-02-11     1      4.740767  92.513333    0.854500  115.645617\n",
       "2   2019-02-11     2      4.828300  92.213333    0.896167   87.970833\n",
       "3   2019-02-21     5      5.455385  98.003846    0.746538   27.530000\n",
       "4   2019-04-02     3      7.335153  95.596610    0.680000   32.268814\n",
       "5   2019-04-02     4      8.557544  92.192982    0.876491   15.318070\n",
       "6   2019-06-20     7     22.657167  88.507000    1.500000   45.087167\n",
       "7   2019-06-20    16     24.722787  67.755246    1.260164   15.918361\n",
       "8   2019-06-20    17     24.255424  68.891017    0.992881   18.043559\n",
       "9   2019-06-20    18     24.325000  70.606724    0.820172   23.874483\n",
       "10  2019-06-20    20     23.354667  76.427500    0.945000   15.145667\n",
       "11  2019-06-20    21     23.070833  85.828333    1.248667   16.450167\n",
       "12  2019-06-20    22     22.614483  86.341379    0.904483   18.143103\n",
       "13  2019-06-21     5     23.958033  86.181967    1.103115   21.273607\n",
       "14  2019-06-22     0     23.153500  82.447667    0.542667   30.163000\n",
       "15  2019-06-22     1     22.806441  83.528814    0.451017   16.186610\n",
       "16  2019-06-22     3     23.212131  81.813770    0.505246   21.623607"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 0'dan büyük örnek yağmur değerleri\n",
    "rain_df.loc[rain_df.ort_yagis > 15].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "b9e19a84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "﻿tarih\n",
      "tarih\n"
     ]
    }
   ],
   "source": [
    "# tarih sütunu isminde görünmeyen karakterler var. Onu düzenleyelim.\n",
    "print(rain_df.columns[0])\n",
    "rain_df.rename(columns={'\\ufefftarih': 'tarih'}, inplace=True, errors=\"raise\")\n",
    "print(rain_df.columns[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8d7eaed",
   "metadata": {},
   "source": [
    "## Yağmur Verisini Kümeleyelim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "68f9a79b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rain_df['isRainy'] = rain_df['ort_yagis'].apply(lambda x : 1 if x > 10 else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "81d80b04",
   "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>tarih</th>\n",
       "      <th>saat</th>\n",
       "      <th>ort_sicaklik</th>\n",
       "      <th>ort_nem</th>\n",
       "      <th>ort_ruzgar</th>\n",
       "      <th>ort_yagis</th>\n",
       "      <th>isRainy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>984</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>0</td>\n",
       "      <td>4.821967</td>\n",
       "      <td>91.820000</td>\n",
       "      <td>1.028500</td>\n",
       "      <td>34.728967</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>1</td>\n",
       "      <td>4.740767</td>\n",
       "      <td>92.513333</td>\n",
       "      <td>0.854500</td>\n",
       "      <td>115.645617</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>986</th>\n",
       "      <td>2019-02-11</td>\n",
       "      <td>2</td>\n",
       "      <td>4.828300</td>\n",
       "      <td>92.213333</td>\n",
       "      <td>0.896167</td>\n",
       "      <td>87.970833</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1023</th>\n",
       "      <td>2019-02-21</td>\n",
       "      <td>5</td>\n",
       "      <td>5.455385</td>\n",
       "      <td>98.003846</td>\n",
       "      <td>0.746538</td>\n",
       "      <td>27.530000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1957</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>2</td>\n",
       "      <td>7.235770</td>\n",
       "      <td>93.913115</td>\n",
       "      <td>1.228361</td>\n",
       "      <td>10.054098</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1958</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>3</td>\n",
       "      <td>7.335153</td>\n",
       "      <td>95.596610</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>32.268814</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959</th>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>4</td>\n",
       "      <td>8.557544</td>\n",
       "      <td>92.192982</td>\n",
       "      <td>0.876491</td>\n",
       "      <td>15.318070</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3799</th>\n",
       "      <td>2019-06-19</td>\n",
       "      <td>22</td>\n",
       "      <td>23.617333</td>\n",
       "      <td>83.391000</td>\n",
       "      <td>0.742667</td>\n",
       "      <td>10.433167</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3808</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>7</td>\n",
       "      <td>22.657167</td>\n",
       "      <td>88.507000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>45.087167</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3815</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>14</td>\n",
       "      <td>25.838475</td>\n",
       "      <td>70.002542</td>\n",
       "      <td>0.978983</td>\n",
       "      <td>10.213390</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3817</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>16</td>\n",
       "      <td>24.722787</td>\n",
       "      <td>67.755246</td>\n",
       "      <td>1.260164</td>\n",
       "      <td>15.918361</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3818</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>17</td>\n",
       "      <td>24.255424</td>\n",
       "      <td>68.891017</td>\n",
       "      <td>0.992881</td>\n",
       "      <td>18.043559</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3819</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>18</td>\n",
       "      <td>24.325000</td>\n",
       "      <td>70.606724</td>\n",
       "      <td>0.820172</td>\n",
       "      <td>23.874483</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3820</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>19</td>\n",
       "      <td>23.723667</td>\n",
       "      <td>79.728833</td>\n",
       "      <td>0.947000</td>\n",
       "      <td>12.355167</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3821</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>20</td>\n",
       "      <td>23.354667</td>\n",
       "      <td>76.427500</td>\n",
       "      <td>0.945000</td>\n",
       "      <td>15.145667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3822</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>21</td>\n",
       "      <td>23.070833</td>\n",
       "      <td>85.828333</td>\n",
       "      <td>1.248667</td>\n",
       "      <td>16.450167</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3823</th>\n",
       "      <td>2019-06-20</td>\n",
       "      <td>22</td>\n",
       "      <td>22.614483</td>\n",
       "      <td>86.341379</td>\n",
       "      <td>0.904483</td>\n",
       "      <td>18.143103</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3830</th>\n",
       "      <td>2019-06-21</td>\n",
       "      <td>5</td>\n",
       "      <td>23.958033</td>\n",
       "      <td>86.181967</td>\n",
       "      <td>1.103115</td>\n",
       "      <td>21.273607</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3849</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>0</td>\n",
       "      <td>23.153500</td>\n",
       "      <td>82.447667</td>\n",
       "      <td>0.542667</td>\n",
       "      <td>30.163000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3850</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>1</td>\n",
       "      <td>22.806441</td>\n",
       "      <td>83.528814</td>\n",
       "      <td>0.451017</td>\n",
       "      <td>16.186610</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3852</th>\n",
       "      <td>2019-06-22</td>\n",
       "      <td>3</td>\n",
       "      <td>23.212131</td>\n",
       "      <td>81.813770</td>\n",
       "      <td>0.505246</td>\n",
       "      <td>21.623607</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tarih  saat  ort_sicaklik    ort_nem  ort_ruzgar   ort_yagis  \\\n",
       "984   2019-02-11     0      4.821967  91.820000    1.028500   34.728967   \n",
       "985   2019-02-11     1      4.740767  92.513333    0.854500  115.645617   \n",
       "986   2019-02-11     2      4.828300  92.213333    0.896167   87.970833   \n",
       "1023  2019-02-21     5      5.455385  98.003846    0.746538   27.530000   \n",
       "1957  2019-04-02     2      7.235770  93.913115    1.228361   10.054098   \n",
       "1958  2019-04-02     3      7.335153  95.596610    0.680000   32.268814   \n",
       "1959  2019-04-02     4      8.557544  92.192982    0.876491   15.318070   \n",
       "3799  2019-06-19    22     23.617333  83.391000    0.742667   10.433167   \n",
       "3808  2019-06-20     7     22.657167  88.507000    1.500000   45.087167   \n",
       "3815  2019-06-20    14     25.838475  70.002542    0.978983   10.213390   \n",
       "3817  2019-06-20    16     24.722787  67.755246    1.260164   15.918361   \n",
       "3818  2019-06-20    17     24.255424  68.891017    0.992881   18.043559   \n",
       "3819  2019-06-20    18     24.325000  70.606724    0.820172   23.874483   \n",
       "3820  2019-06-20    19     23.723667  79.728833    0.947000   12.355167   \n",
       "3821  2019-06-20    20     23.354667  76.427500    0.945000   15.145667   \n",
       "3822  2019-06-20    21     23.070833  85.828333    1.248667   16.450167   \n",
       "3823  2019-06-20    22     22.614483  86.341379    0.904483   18.143103   \n",
       "3830  2019-06-21     5     23.958033  86.181967    1.103115   21.273607   \n",
       "3849  2019-06-22     0     23.153500  82.447667    0.542667   30.163000   \n",
       "3850  2019-06-22     1     22.806441  83.528814    0.451017   16.186610   \n",
       "3852  2019-06-22     3     23.212131  81.813770    0.505246   21.623607   \n",
       "\n",
       "      isRainy  \n",
       "984         1  \n",
       "985         1  \n",
       "986         1  \n",
       "1023        1  \n",
       "1957        1  \n",
       "1958        1  \n",
       "1959        1  \n",
       "3799        1  \n",
       "3808        1  \n",
       "3815        1  \n",
       "3817        1  \n",
       "3818        1  \n",
       "3819        1  \n",
       "3820        1  \n",
       "3821        1  \n",
       "3822        1  \n",
       "3823        1  \n",
       "3830        1  \n",
       "3849        1  \n",
       "3850        1  \n",
       "3852        1  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_df.loc[rain_df['ort_yagis'] > 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27ed42c7",
   "metadata": {},
   "source": [
    "## Yağmur ortalaması 10'dan büyükse 1 değilse 0 atadık"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f7ed0ca",
   "metadata": {},
   "source": [
    "## Yağmur verisini Ekleyelim\n",
    "\n",
    "**DF** tarafında pd.datetime tipinde tutuyoruz. \n",
    "\n",
    "**Yağmur** üzerinde zaman string halde."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7151b2f6",
   "metadata": {},
   "source": [
    "Verileri birleştirirken sabit bir sütun kullanacağız. Bunun için de\n",
    "- dateTime sütununun date'ini ve saat sütununun değerini kullanacağız."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "10326d5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"period\"] = df['DateTime'].apply(lambda x : x.date())\n",
    "df[\"period\"] = df['period'].astype(str) + \" \" + df['Hour'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "e97fb907",
   "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>DateTime</th>\n",
       "      <th>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>isNationalHoliday</th>\n",
       "      <th>DayName</th>\n",
       "      <th>isWeekend</th>\n",
       "      <th>isSchoolHoliday</th>\n",
       "      <th>label</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-01-01 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 02:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 03:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>24.5</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-01-01 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 04:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DateTime  SegmentID  SegmentDirection  Year  Month  Day  Hour  \\\n",
       "0 2019-01-01 00:00:00         65                 0  2019      1    1     0   \n",
       "1 2019-01-01 01:00:00         65                 0  2019      1    1     1   \n",
       "2 2019-01-01 02:00:00         65                 0  2019      1    1     2   \n",
       "3 2019-01-01 03:00:00         65                 0  2019      1    1     3   \n",
       "4 2019-01-01 04:00:00         65                 0  2019      1    1     4   \n",
       "\n",
       "   AverageSpeed  isNationalHoliday  DayName  isWeekend  isSchoolHoliday  \\\n",
       "0          32.5                  1  Tuesday          0                0   \n",
       "1          20.0                  1  Tuesday          0                0   \n",
       "2          36.0                  1  Tuesday          0                0   \n",
       "3          24.5                  1  Tuesday          0                0   \n",
       "4          49.0                  1  Tuesday          0                0   \n",
       "\n",
       "   label        period  \n",
       "0      1  2019-01-01 0  \n",
       "1      0  2019-01-01 1  \n",
       "2      1  2019-01-01 2  \n",
       "3      0  2019-01-01 3  \n",
       "4      1  2019-01-01 4  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5e83d25",
   "metadata": {},
   "source": [
    "Aynı işlemi bu sefer yağmur verisine uyguluyoruz."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b3db99db",
   "metadata": {},
   "outputs": [],
   "source": [
    "rain_df[\"period\"] = rain_df['tarih'] + \" \" + rain_df['saat'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "b2887d59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-01-08 9\n",
      "2019-01-08 9\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# yaptığımız işlemin doğruluğunu test edelim.\n",
    "IDX = 177\n",
    "print(rain_df['period'][IDX])\n",
    "print(df['period'][IDX])\n",
    "\n",
    "print(df['period'][IDX] == rain_df['period'][IDX])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58aabf2a",
   "metadata": {},
   "source": [
    "##### Evet yukarıdaki teknik ile artık iki veri arasında ortak aynı veri tipinde bir sütun elde ettik. --> **PERIOD** \n",
    "\n",
    "Şimdi birleştirelim."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "46161a98",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# period sütununu index olarak işaretle ve birleştir.\n",
    "df = df.join(rain_df.set_index('period'), on='period')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "1022f36f",
   "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>DateTime</th>\n",
       "      <th>SegmentID</th>\n",
       "      <th>SegmentDirection</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>AverageSpeed</th>\n",
       "      <th>isNationalHoliday</th>\n",
       "      <th>DayName</th>\n",
       "      <th>...</th>\n",
       "      <th>isSchoolHoliday</th>\n",
       "      <th>label</th>\n",
       "      <th>period</th>\n",
       "      <th>tarih</th>\n",
       "      <th>saat</th>\n",
       "      <th>ort_sicaklik</th>\n",
       "      <th>ort_nem</th>\n",
       "      <th>ort_ruzgar</th>\n",
       "      <th>ort_yagis</th>\n",
       "      <th>isRainy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2965</th>\n",
       "      <td>2019-05-05 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-05-05 1</td>\n",
       "      <td>2019-05-05</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.180667</td>\n",
       "      <td>82.209333</td>\n",
       "      <td>0.815333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                DateTime  SegmentID  SegmentDirection  Year  Month  Day  Hour  \\\n",
       "2965 2019-05-05 01:00:00         65                 0  2019      5    5     1   \n",
       "\n",
       "      AverageSpeed  isNationalHoliday DayName  ...  isSchoolHoliday  label  \\\n",
       "2965          25.0                  0  Sunday  ...                0      0   \n",
       "\n",
       "            period       tarih saat  ort_sicaklik    ort_nem  ort_ruzgar  \\\n",
       "2965  2019-05-05 1  2019-05-05  1.0     15.180667  82.209333    0.815333   \n",
       "\n",
       "      ort_yagis  isRainy  \n",
       "2965        0.0      0.0  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['period'] == '2019-05-05 1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "61ed7d2c",
   "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>tarih</th>\n",
       "      <th>saat</th>\n",
       "      <th>ort_sicaklik</th>\n",
       "      <th>ort_nem</th>\n",
       "      <th>ort_ruzgar</th>\n",
       "      <th>ort_yagis</th>\n",
       "      <th>isRainy</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2715</th>\n",
       "      <td>2019-05-05</td>\n",
       "      <td>1</td>\n",
       "      <td>15.180667</td>\n",
       "      <td>82.209333</td>\n",
       "      <td>0.815333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-05-05 1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           tarih  saat  ort_sicaklik    ort_nem  ort_ruzgar  ort_yagis  \\\n",
       "2715  2019-05-05     1     15.180667  82.209333    0.815333        0.0   \n",
       "\n",
       "      isRainy        period  \n",
       "2715        0  2019-05-05 1  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rain_df.loc[rain_df['period'] == '2019-05-05 1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "506d5cbe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100    0.0\n",
      "Name: isRainy, dtype: float64\n",
      "100    0\n",
      "Name: isRainy, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "DATE = '2019-01-05 4'\n",
    "print(df.loc[df['period'] == DATE]['isRainy'])\n",
    "print(rain_df.loc[df['period'] == DATE]['isRainy'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "31ec0edd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(inplace=True)\n",
    "df['isRainy'] = df.isRainy.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "90a58f24",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b797ff9",
   "metadata": {},
   "source": [
    "# Önemli Örnek\n",
    "- ortalama yağış yüksek olsa da hız çok düşmemiş."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "862ec79a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.loc[df['ort_yagis'] > 30][['period', 'ort_yagis', 'AverageSpeed']].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "549f7c14",
   "metadata": {},
   "source": [
    "### İstenmeyen Sütunların Silinmesi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c73ffd7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# prediction tarafında Average Speed'e ihtiyacım var. Ama burada silmem gerekiyor, bir kopya oluştur.\n",
    "copy_df = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "202f31a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['Month', 'Day', 'Hour', 'isNationalHoliday', 'isWeekend',\n",
    "       'isSchoolHoliday', 'isRainy', 'label']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "36b93d41",
   "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>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>isNationalHoliday</th>\n",
       "      <th>isWeekend</th>\n",
       "      <th>isSchoolHoliday</th>\n",
       "      <th>isRainy</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Month  Day  Hour  isNationalHoliday  isWeekend  isSchoolHoliday  isRainy  \\\n",
       "0      1    1     0                  1          0                0        0   \n",
       "1      1    1     1                  1          0                0        0   \n",
       "2      1    1     2                  1          0                0        0   \n",
       "3      1    1     3                  1          0                0        0   \n",
       "4      1    1     4                  1          0                0        0   \n",
       "\n",
       "   label  \n",
       "0      1  \n",
       "1      0  \n",
       "2      1  \n",
       "3      0  \n",
       "4      1  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8bb3674",
   "metadata": {},
   "source": [
    "## Lets Create ML Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "ef022c10",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.tree import DecisionTreeRegressor\n",
    "from sklearn.linear_model import Perceptron, LinearRegression\n",
    "from sklearn.naive_bayes import MultinomialNB\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from sklearn.svm import SVC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "f950e8a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ace027f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Feature ve class ayrımı.\n",
    "y = df['label'].values\n",
    "X = df.drop(['label'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f60d62c",
   "metadata": {},
   "source": [
    "# Prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "ac56c9ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_prediction_example(m, d, h, isN, isW, isS, r):\n",
    "    data = pd.DataFrame(0, index=[0], columns= list(X_train.columns))\n",
    "    data['Month'] = m\n",
    "    data['Day'] = d\n",
    "    data['Hour'] = h\n",
    "    data['isNationalHoliday'] = isN\n",
    "    data['isWeekend'] = isW\n",
    "    data['isSchoolHoliday'] = isS\n",
    "    data['isRainy'] = r\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "614b8e6e",
   "metadata": {},
   "source": [
    "### Cross Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b2ef444b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import KFold\n",
    "\n",
    "\n",
    "def cross_validation(df):\n",
    "    piece = KFold(shuffle=True) # shuffle true olursa ayrılan veriler de karışık gelir.\n",
    "\n",
    "    inputs = df.iloc[:,:-1].values\n",
    "    outputs = df.iloc[:,-1].values\n",
    "    models = {'Perceptron': Perceptron(),\n",
    "              'NaiveBayes': MultinomialNB(),\n",
    "              'KNearest': KNeighborsClassifier(n_neighbors=3),\n",
    "              'DecisionTree': DecisionTreeRegressor(max_depth= 110, min_samples_leaf = 25),\n",
    "              'SupportVector': SVC(kernel=\"linear\")}\n",
    "    # girdiyi train ve test olarak ayırıyor.\n",
    "    for (name, model) in models.items():\n",
    "        \n",
    "        total_score = 0\n",
    "        for idx, (train_idx, test_idx) in enumerate(piece.split(inputs)):\n",
    "            # artık verisetim bölünmüş bir halde.\n",
    "            # eğitim kısmını eğitime, test kısmını teste atayalım.\n",
    "            train_input = inputs[train_idx, :]\n",
    "            test_input = inputs[test_idx, :]\n",
    "            train_output = outputs[train_idx] # output kısmıdan zaten 1 adet sütun var.\n",
    "            test_output = outputs[test_idx]\n",
    "\n",
    "            model.fit(train_input, train_output)\n",
    "            score = round(model.score(test_input, test_output) * 100, 2)\n",
    "            print(f'{str(name):17}{idx+1}x:  %{score}')\n",
    "            total_score += score\n",
    "        \n",
    "        print(f'Average %{round((total_score / 5),2)}')\n",
    "        print(\"-\" * 52)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "bd19fa41",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Perceptron       1x:  %55.56\n",
      "Perceptron       2x:  %51.55\n",
      "Perceptron       3x:  %61.31\n",
      "Perceptron       4x:  %47.59\n",
      "Perceptron       5x:  %35.23\n",
      "Average %50.25\n",
      "----------------------------------------------------\n",
      "NaiveBayes       1x:  %50.49\n",
      "NaiveBayes       2x:  %51.3\n",
      "NaiveBayes       3x:  %51.55\n",
      "NaiveBayes       4x:  %51.67\n",
      "NaiveBayes       5x:  %54.14\n",
      "Average %51.83\n",
      "----------------------------------------------------\n",
      "KNearest         1x:  %73.58\n",
      "KNearest         2x:  %75.77\n",
      "KNearest         3x:  %77.75\n",
      "KNearest         4x:  %73.05\n",
      "KNearest         5x:  %76.39\n",
      "Average %75.31\n",
      "----------------------------------------------------\n",
      "DecisionTree     1x:  %61.5\n",
      "DecisionTree     2x:  %59.84\n",
      "DecisionTree     3x:  %59.04\n",
      "DecisionTree     4x:  %61.13\n",
      "DecisionTree     5x:  %62.44\n",
      "Average %60.79\n",
      "----------------------------------------------------\n",
      "SupportVector    1x:  %62.59\n",
      "SupportVector    2x:  %62.92\n",
      "SupportVector    3x:  %63.54\n",
      "SupportVector    4x:  %66.01\n",
      "SupportVector    5x:  %63.29\n",
      "Average %63.67\n",
      "----------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "cross_validation(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70170d0d",
   "metadata": {},
   "source": [
    "## Saving Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "4c5c354a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['model.joblib']"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model = KNeighborsClassifier(n_neighbors=3)\n",
    "\n",
    "piece = KFold(shuffle=True) # shuffle true olursa ayrılan veriler de karışık gelir.\n",
    "\n",
    "inputs = df.iloc[:,:-1].values\n",
    "outputs = df.iloc[:,-1].values\n",
    "for idx, (train_idx, test_idx) in enumerate(piece.split(inputs)):\n",
    "    # artık verisetim bölünmüş bir halde.\n",
    "    # eğitim kısmını eğitime, test kısmını teste atayalım.\n",
    "    train_input = inputs[train_idx, :]\n",
    "    test_input = inputs[test_idx, :]\n",
    "    train_output = outputs[train_idx] # output kısmıdan zaten 1 adet sütun var.\n",
    "    test_output = outputs[test_idx]\n",
    "\n",
    "    model.fit(train_input, train_output)\n",
    "\n",
    "    \n",
    "from joblib import dump\n",
    "dump(model, 'model.joblib')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "8edd60c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((8.5, 30.0), (30.5, 49.5), (50.0, 75.5))"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLASS0, CLASS1, CLASS2"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "jupyter-kernel",
   "language": "python",
   "name": "jupyter-kernel"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}