1-VOI_OriginalandPreQueryTransformation.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Voi original transformation \n",
    "\n",
    "The scope of this script is to assign a trip ID to VOI's original data file and ensure it matches with the pre-query transformation file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "import haversine as hs\n",
    "from haversine import Unit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_original = pd.read_csv('folder/file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_original"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Global cleaning "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1 dropping End-Time & Start Date\n",
    "df=df.drop(['START_DATE'], axis=1)\n",
    "\n",
    "#2 renaming columns to match structure of requests.csv\n",
    "df=df.rename(columns={\"START_TIME\":\"treq\",\"START_LONGITUDE\":\"origin_x\",\"START_LATITUDE\":\"origin_y\",\"END_LONGITUDE\":\"destination_x\",\"END_LATITUDE\":\"destination_y\",})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows removed: 258\n"
     ]
    }
   ],
   "source": [
    "#3 drop out of bound trips\n",
    "minLong = 11.8735\n",
    "minLat = 57.6265\n",
    "maxLong = 12.0427\n",
    "maxLat = 57.7712\n",
    "\n",
    "len1=len(df)\n",
    "len1\n",
    "df.drop(df.loc[df['origin_x']<minLong].index, inplace=True)\n",
    "df.drop(df.loc[df['origin_y']<minLat].index, inplace=True)\n",
    "df.drop(df.loc[df['destination_x']<minLong].index, inplace=True)\n",
    "df.drop(df.loc[df['destination_y']<minLat].index, inplace=True)\n",
    "df.drop(df.loc[df['origin_x']>maxLong].index, inplace=True)\n",
    "df.drop(df.loc[df['origin_y']>maxLat].index, inplace=True)\n",
    "df.drop(df.loc[df['destination_x']>maxLong].index, inplace=True)\n",
    "df.drop(df.loc[df['destination_y']>maxLat].index, inplace=True)\n",
    "len2=len(df)\n",
    "\n",
    "print(\"Rows removed:\", len1-len2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>TRIP_ID</th>\n",
       "      <th>origin_x</th>\n",
       "      <th>origin_y</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>treq</th>\n",
       "      <th>END_TIME</th>\n",
       "      <th>duration_in_m</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6abf88f-5a45-5845-9b76-fba4970c2a68</td>\n",
       "      <td>11.944810</td>\n",
       "      <td>57.698154</td>\n",
       "      <td>11.940518</td>\n",
       "      <td>57.692970</td>\n",
       "      <td>2022-04-27 19:56:15</td>\n",
       "      <td>2022-04-27 20:03:41</td>\n",
       "      <td>7.433333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3c186cb9-15ef-54b6-96a8-0e8eea1299a0</td>\n",
       "      <td>11.984405</td>\n",
       "      <td>57.706665</td>\n",
       "      <td>11.996304</td>\n",
       "      <td>57.691113</td>\n",
       "      <td>2022-04-27 10:25:04</td>\n",
       "      <td>2022-04-27 10:33:09</td>\n",
       "      <td>8.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>f11bf258-bddd-50a2-bee1-37b36590728b</td>\n",
       "      <td>11.991008</td>\n",
       "      <td>57.705502</td>\n",
       "      <td>11.980876</td>\n",
       "      <td>57.703041</td>\n",
       "      <td>2022-04-27 16:21:11</td>\n",
       "      <td>2022-04-27 16:24:52</td>\n",
       "      <td>3.683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8cec0a04-aef3-5ed0-af65-f1e0c4a7d5b0</td>\n",
       "      <td>11.944882</td>\n",
       "      <td>57.717285</td>\n",
       "      <td>11.967620</td>\n",
       "      <td>57.707108</td>\n",
       "      <td>2022-04-27 06:45:57</td>\n",
       "      <td>2022-04-27 06:55:21</td>\n",
       "      <td>9.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1133b471-3459-5de4-b87c-cadb2aec4352</td>\n",
       "      <td>11.963046</td>\n",
       "      <td>57.702522</td>\n",
       "      <td>11.963737</td>\n",
       "      <td>57.700203</td>\n",
       "      <td>2022-04-27 10:22:09</td>\n",
       "      <td>2022-04-27 10:24:28</td>\n",
       "      <td>2.316667</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040626</th>\n",
       "      <td>788203bf-f44a-55fd-a9a7-56d29ba19564</td>\n",
       "      <td>11.928447</td>\n",
       "      <td>57.709794</td>\n",
       "      <td>11.934595</td>\n",
       "      <td>57.705265</td>\n",
       "      <td>2022-10-27 11:32:25</td>\n",
       "      <td>2022-10-27 11:36:07</td>\n",
       "      <td>3.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040627</th>\n",
       "      <td>9ddd1063-37f2-5d02-b6e5-60e6464230b0</td>\n",
       "      <td>11.956453</td>\n",
       "      <td>57.699149</td>\n",
       "      <td>11.994070</td>\n",
       "      <td>57.699961</td>\n",
       "      <td>2022-10-27 20:10:10</td>\n",
       "      <td>2022-10-27 20:23:45</td>\n",
       "      <td>13.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040628</th>\n",
       "      <td>1e291ded-7f32-5164-a651-3f6a65d3e567</td>\n",
       "      <td>11.934535</td>\n",
       "      <td>57.705272</td>\n",
       "      <td>11.928570</td>\n",
       "      <td>57.709841</td>\n",
       "      <td>2022-10-27 11:22:28</td>\n",
       "      <td>2022-10-27 11:26:13</td>\n",
       "      <td>3.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040629</th>\n",
       "      <td>37cd82fe-47c6-5c09-acf6-3dfc3f2a6ff0</td>\n",
       "      <td>12.000091</td>\n",
       "      <td>57.681917</td>\n",
       "      <td>11.981809</td>\n",
       "      <td>57.688730</td>\n",
       "      <td>2022-10-27 10:16:17</td>\n",
       "      <td>2022-10-27 10:22:44</td>\n",
       "      <td>6.450000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040630</th>\n",
       "      <td>6336195b-e230-5d51-813d-6663241f4427</td>\n",
       "      <td>11.982383</td>\n",
       "      <td>57.689704</td>\n",
       "      <td>12.000338</td>\n",
       "      <td>57.681963</td>\n",
       "      <td>2022-10-27 03:52:11</td>\n",
       "      <td>2022-10-27 04:00:31</td>\n",
       "      <td>8.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1040373 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      TRIP_ID   origin_x   origin_y  \\\n",
       "0        d6abf88f-5a45-5845-9b76-fba4970c2a68  11.944810  57.698154   \n",
       "1        3c186cb9-15ef-54b6-96a8-0e8eea1299a0  11.984405  57.706665   \n",
       "2        f11bf258-bddd-50a2-bee1-37b36590728b  11.991008  57.705502   \n",
       "3        8cec0a04-aef3-5ed0-af65-f1e0c4a7d5b0  11.944882  57.717285   \n",
       "4        1133b471-3459-5de4-b87c-cadb2aec4352  11.963046  57.702522   \n",
       "...                                       ...        ...        ...   \n",
       "1040626  788203bf-f44a-55fd-a9a7-56d29ba19564  11.928447  57.709794   \n",
       "1040627  9ddd1063-37f2-5d02-b6e5-60e6464230b0  11.956453  57.699149   \n",
       "1040628  1e291ded-7f32-5164-a651-3f6a65d3e567  11.934535  57.705272   \n",
       "1040629  37cd82fe-47c6-5c09-acf6-3dfc3f2a6ff0  12.000091  57.681917   \n",
       "1040630  6336195b-e230-5d51-813d-6663241f4427  11.982383  57.689704   \n",
       "\n",
       "         destination_x  destination_y                treq            END_TIME  \\\n",
       "0            11.940518      57.692970 2022-04-27 19:56:15 2022-04-27 20:03:41   \n",
       "1            11.996304      57.691113 2022-04-27 10:25:04 2022-04-27 10:33:09   \n",
       "2            11.980876      57.703041 2022-04-27 16:21:11 2022-04-27 16:24:52   \n",
       "3            11.967620      57.707108 2022-04-27 06:45:57 2022-04-27 06:55:21   \n",
       "4            11.963737      57.700203 2022-04-27 10:22:09 2022-04-27 10:24:28   \n",
       "...                ...            ...                 ...                 ...   \n",
       "1040626      11.934595      57.705265 2022-10-27 11:32:25 2022-10-27 11:36:07   \n",
       "1040627      11.994070      57.699961 2022-10-27 20:10:10 2022-10-27 20:23:45   \n",
       "1040628      11.928570      57.709841 2022-10-27 11:22:28 2022-10-27 11:26:13   \n",
       "1040629      11.981809      57.688730 2022-10-27 10:16:17 2022-10-27 10:22:44   \n",
       "1040630      12.000338      57.681963 2022-10-27 03:52:11 2022-10-27 04:00:31   \n",
       "\n",
       "         duration_in_m  \n",
       "0             7.433333  \n",
       "1             8.083333  \n",
       "2             3.683333  \n",
       "3             9.400000  \n",
       "4             2.316667  \n",
       "...                ...  \n",
       "1040626       3.700000  \n",
       "1040627      13.583333  \n",
       "1040628       3.750000  \n",
       "1040629       6.450000  \n",
       "1040630       8.333333  \n",
       "\n",
       "[1040373 rows x 8 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#5. Time and duration: formatting start and end to datetime, incrementing to UTC+1 since we assume that GTFS data is saved in Gothenburg's local time (check with Tobias), AND calculating duration\n",
    "df['treq'] = pd.to_datetime(df['treq'], format='%Y-%m-%d %H:%M:%S')\n",
    "df['treq'] = pd.to_datetime(df['treq'].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "\n",
    "df['END_TIME'] = pd.to_datetime(df['END_TIME'], format='%Y-%m-%d %H:%M:%S')\n",
    "df['END_TIME'] = pd.to_datetime(df['END_TIME'].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "\n",
    "\n",
    "df['duration_in_m']=df['END_TIME']-df['treq']\n",
    "df['duration_in_m']=(df.duration_in_m.dt.total_seconds() / 60)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "## code to remove rows that have distance and duration under a certain value?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "##=============================ASSIGNING ID AFTER ALL CLEANING IS MADE\n",
    "## Sort oldest trip first, and assign id. ID is being assigned BEFORE the dataset is split by schedule to ensure cardinality if the datasets need to be concatenated \n",
    "df.sort_values(by='treq', ascending=True, inplace=True)\n",
    "df['TRIP_ID']=np.arange(len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "#6 split summer and winter trips. IMPORTANT. VOI's ORIGINAL DATA MUST ALSO BE SPLIT TO RETAIN ROW-WISE INDEX IDENTIFIERS\n",
    "start_date = '2022-06-19' #day summer schedule starts\n",
    "end_date   = '2022-08-20' #day summer schedule ends\n",
    "\n",
    "df_summer = df.query('treq > @start_date and treq < @end_date')\n",
    "df_summer.to_csv('VoiData_CleanedIndexed_Summer.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#6 dataframe for winter trips\n",
    "start_date = '2022-06-19' #day summer schedule starts\n",
    "end_date   = '2022-08-20' #day summer schedule ends\n",
    "\n",
    "df_winter1 = df.query('treq < @start_date') #all dates before summer\n",
    "df_winter2 = df.query('treq > @end_date') #all dates after summer\n",
    "df_winter = pd.concat([df_winter1,df_winter2])\n",
    "df_winter.to_csv('VoiData_CleanedIndexed_Winter.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>TRIP_ID</th>\n",
       "      <th>origin_x</th>\n",
       "      <th>origin_y</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>treq</th>\n",
       "      <th>END_TIME</th>\n",
       "      <th>duration_in_m</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>11.946541</td>\n",
       "      <td>57.723145</td>\n",
       "      <td>11.941060</td>\n",
       "      <td>57.721714</td>\n",
       "      <td>2022-01-08 00:00:40</td>\n",
       "      <td>2022-01-01 00:03:47</td>\n",
       "      <td>3.116667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>11.978535</td>\n",
       "      <td>57.697792</td>\n",
       "      <td>11.970809</td>\n",
       "      <td>57.691029</td>\n",
       "      <td>2022-01-08 00:01:01</td>\n",
       "      <td>2022-01-01 00:08:23</td>\n",
       "      <td>7.366667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>11.990700</td>\n",
       "      <td>57.712872</td>\n",
       "      <td>12.028315</td>\n",
       "      <td>57.711536</td>\n",
       "      <td>2022-01-08 00:01:41</td>\n",
       "      <td>2022-01-01 00:25:35</td>\n",
       "      <td>23.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>11.983342</td>\n",
       "      <td>57.688713</td>\n",
       "      <td>11.985471</td>\n",
       "      <td>57.687702</td>\n",
       "      <td>2022-01-08 00:01:50</td>\n",
       "      <td>2022-01-01 00:02:54</td>\n",
       "      <td>1.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>11.952630</td>\n",
       "      <td>57.691753</td>\n",
       "      <td>11.931505</td>\n",
       "      <td>57.690220</td>\n",
       "      <td>2022-01-08 00:02:57</td>\n",
       "      <td>2022-01-01 00:12:00</td>\n",
       "      <td>9.050000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040368</th>\n",
       "      <td>1040368</td>\n",
       "      <td>11.982319</td>\n",
       "      <td>57.698499</td>\n",
       "      <td>11.962712</td>\n",
       "      <td>57.695212</td>\n",
       "      <td>2022-01-07 00:13:55</td>\n",
       "      <td>2022-11-11 00:21:53</td>\n",
       "      <td>7.966667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040369</th>\n",
       "      <td>1040369</td>\n",
       "      <td>11.991790</td>\n",
       "      <td>57.705063</td>\n",
       "      <td>11.978468</td>\n",
       "      <td>57.693824</td>\n",
       "      <td>2022-01-07 00:14:11</td>\n",
       "      <td>2022-11-11 00:23:13</td>\n",
       "      <td>9.033333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040370</th>\n",
       "      <td>1040370</td>\n",
       "      <td>11.971477</td>\n",
       "      <td>57.709383</td>\n",
       "      <td>11.940520</td>\n",
       "      <td>57.726088</td>\n",
       "      <td>2022-01-07 00:14:46</td>\n",
       "      <td>2022-11-11 00:29:07</td>\n",
       "      <td>14.350000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040371</th>\n",
       "      <td>1040371</td>\n",
       "      <td>11.971631</td>\n",
       "      <td>57.700933</td>\n",
       "      <td>11.952006</td>\n",
       "      <td>57.694424</td>\n",
       "      <td>2022-01-07 00:24:12</td>\n",
       "      <td>2022-11-11 00:33:26</td>\n",
       "      <td>9.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040372</th>\n",
       "      <td>1040372</td>\n",
       "      <td>11.961948</td>\n",
       "      <td>57.702266</td>\n",
       "      <td>11.953374</td>\n",
       "      <td>57.700292</td>\n",
       "      <td>2022-01-07 00:29:39</td>\n",
       "      <td>2022-11-11 00:33:14</td>\n",
       "      <td>3.583333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>798096 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         TRIP_ID   origin_x   origin_y  destination_x  destination_y  \\\n",
       "0              0  11.946541  57.723145      11.941060      57.721714   \n",
       "1              1  11.978535  57.697792      11.970809      57.691029   \n",
       "2              2  11.990700  57.712872      12.028315      57.711536   \n",
       "3              3  11.983342  57.688713      11.985471      57.687702   \n",
       "4              4  11.952630  57.691753      11.931505      57.690220   \n",
       "...          ...        ...        ...            ...            ...   \n",
       "1040368  1040368  11.982319  57.698499      11.962712      57.695212   \n",
       "1040369  1040369  11.991790  57.705063      11.978468      57.693824   \n",
       "1040370  1040370  11.971477  57.709383      11.940520      57.726088   \n",
       "1040371  1040371  11.971631  57.700933      11.952006      57.694424   \n",
       "1040372  1040372  11.961948  57.702266      11.953374      57.700292   \n",
       "\n",
       "                        treq             END_TIME  duration_in_m  \n",
       "0        2022-01-08 00:00:40  2022-01-01 00:03:47       3.116667  \n",
       "1        2022-01-08 00:01:01  2022-01-01 00:08:23       7.366667  \n",
       "2        2022-01-08 00:01:41  2022-01-01 00:25:35      23.900000  \n",
       "3        2022-01-08 00:01:50  2022-01-01 00:02:54       1.066667  \n",
       "4        2022-01-08 00:02:57  2022-01-01 00:12:00       9.050000  \n",
       "...                      ...                  ...            ...  \n",
       "1040368  2022-01-07 00:13:55  2022-11-11 00:21:53       7.966667  \n",
       "1040369  2022-01-07 00:14:11  2022-11-11 00:23:13       9.033333  \n",
       "1040370  2022-01-07 00:14:46  2022-11-11 00:29:07      14.350000  \n",
       "1040371  2022-01-07 00:24:12  2022-11-11 00:33:26       9.233333  \n",
       "1040372  2022-01-07 00:29:39  2022-11-11 00:33:14       3.583333  \n",
       "\n",
       "[798096 rows x 8 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_winter"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Transformation for Querying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterMon['treq'] = pd.to_datetime(df_winterMon['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterMon['mondayDate'] = monday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterMon['treq'] = df_winterMon['mondayDate'].astype(str) + df_winterMon['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterTue['treq'] = pd.to_datetime(df_winterTue['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterTue['tuesdayDate'] = tuesday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:29: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterTue['treq'] = df_winterTue['tuesdayDate'].astype(str) + df_winterTue['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterWed['treq'] = pd.to_datetime(df_winterWed['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterWed['wednesdayDate'] = wednesday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterWed['treq'] = df_winterWed['wednesdayDate'].astype(str) + df_winterWed['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:39: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterThur['treq'] = pd.to_datetime(df_winterThur['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:40: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterThur['thursdayDate'] = thursday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:41: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterThur['treq'] = df_winterThur['thursdayDate'].astype(str) + df_winterThur['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:45: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterFri['treq'] = pd.to_datetime(df_winterFri['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterFri['fridayDate'] = friday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterFri['treq'] = df_winterFri['fridayDate'].astype(str) + df_winterFri['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:51: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSat['treq'] = pd.to_datetime(df_winterSat['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:52: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSat['saturdayDate'] = saturday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:53: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSat['treq'] = df_winterSat['saturdayDate'].astype(str) + df_winterSat['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSun['treq'] = pd.to_datetime(df_winterSun['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:58: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSun['sundayDate'] = sunday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\2117655055.py:59: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_winterSun['treq'] = df_winterSun['sundayDate'].astype(str) + df_winterSun['time'].astype(str)\n"
     ]
    }
   ],
   "source": [
    "#7 assigning appropriate day of the week \n",
    "\n",
    "#probably a runner-up as the world's most inneficient piece of code \n",
    "\n",
    "monday = '2022-01-03 '\n",
    "tuesday = '2022-01-04 '\n",
    "wednesday = '2022-01-05 '\n",
    "thursday = '2022-01-06 '\n",
    "friday = '2022-01-07 '\n",
    "saturday = '2022-01-08 '\n",
    "sunday = '2022-01-09 '\n",
    "\n",
    "\n",
    "df_winter['treq'] = pd.to_datetime(df_winter['treq'], format='%Y-%m-%d %H:%M:%S')\n",
    "df_winter['treq'] = pd.to_datetime(df_winter['treq'].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "\n",
    "df_winter['treqDayName']=df_winter['treq'].dt.day_name()\n",
    "df_winter['time'] = [d.time() for d in df_winter['treq']]\n",
    "\n",
    "df_winterMon = df_winter.loc[df_winter['treqDayName'] == 'Monday']\n",
    "df_winterMon['treq'] = pd.to_datetime(df_winterMon['treq'])\n",
    "df_winterMon['mondayDate'] = monday\n",
    "df_winterMon['treq'] = df_winterMon['mondayDate'].astype(str) + df_winterMon['time'].astype(str)\n",
    "df_winterMon=df_winterMon.drop(['treqDayName','time','mondayDate'], axis=1)\n",
    "\n",
    "df_winterTue = df_winter.loc[df_winter['treqDayName'] == 'Tuesday']\n",
    "df_winterTue['treq'] = pd.to_datetime(df_winterTue['treq'])\n",
    "df_winterTue['tuesdayDate'] = tuesday\n",
    "df_winterTue['treq'] = df_winterTue['tuesdayDate'].astype(str) + df_winterTue['time'].astype(str)\n",
    "df_winterTue=df_winterTue.drop(['treqDayName','time','tuesdayDate'], axis=1)\n",
    "\n",
    "df_winterWed= df_winter.loc[df_winter['treqDayName'] == 'Wednesday']\n",
    "df_winterWed['treq'] = pd.to_datetime(df_winterWed['treq'])\n",
    "df_winterWed['wednesdayDate'] = wednesday\n",
    "df_winterWed['treq'] = df_winterWed['wednesdayDate'].astype(str) + df_winterWed['time'].astype(str)\n",
    "df_winterWed=df_winterWed.drop(['treqDayName','time','wednesdayDate'], axis=1)\n",
    "\n",
    "df_winterThur= df_winter.loc[df_winter['treqDayName'] == 'Thursday']\n",
    "df_winterThur['treq'] = pd.to_datetime(df_winterThur['treq'])\n",
    "df_winterThur['thursdayDate'] = thursday\n",
    "df_winterThur['treq'] = df_winterThur['thursdayDate'].astype(str) + df_winterThur['time'].astype(str)\n",
    "df_winterThur=df_winterThur.drop(['treqDayName','time','thursdayDate'], axis=1)\n",
    "\n",
    "df_winterFri= df_winter.loc[df_winter['treqDayName'] == 'Friday']\n",
    "df_winterFri['treq'] = pd.to_datetime(df_winterFri['treq'])\n",
    "df_winterFri['fridayDate'] = friday\n",
    "df_winterFri['treq'] = df_winterFri['fridayDate'].astype(str) + df_winterFri['time'].astype(str)\n",
    "df_winterFri=df_winterFri.drop(['treqDayName','time','fridayDate'], axis=1)\n",
    "\n",
    "df_winterSat= df_winter.loc[df_winter['treqDayName'] == 'Saturday']\n",
    "df_winterSat['treq'] = pd.to_datetime(df_winterSat['treq'])\n",
    "df_winterSat['saturdayDate'] = saturday\n",
    "df_winterSat['treq'] = df_winterSat['saturdayDate'].astype(str) + df_winterSat['time'].astype(str)\n",
    "df_winterSat=df_winterSat.drop(['treqDayName','time','saturdayDate'], axis=1)\n",
    "\n",
    "df_winterSun= df_winter.loc[df_winter['treqDayName'] == 'Sunday']\n",
    "df_winterSun['treq'] = pd.to_datetime(df_winterSun['treq'])\n",
    "df_winterSun['sundayDate'] = sunday\n",
    "df_winterSun['treq'] = df_winterSun['sundayDate'].astype(str) + df_winterSun['time'].astype(str)\n",
    "df_winterSun=df_winterSun.drop(['treqDayName','time','sundayDate'], axis=1)\n",
    "\n",
    "\n",
    "df_winter = pd.concat([df_winterMon,df_winterTue,df_winterWed,df_winterThur,df_winterFri,df_winterSat,df_winterSun])\n",
    "df_winter.sort_values(by='TRIP_ID', ascending=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerMon['treq'] = pd.to_datetime(df_summerMon['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerMon['mondayDate'] = monday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerMon['treq'] = df_summerMon['mondayDate'].astype(str) + df_summerMon['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:26: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerTue['treq'] = pd.to_datetime(df_summerTue['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerTue['tuesdayDate'] = tuesday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerTue['treq'] = df_summerTue['tuesdayDate'].astype(str) + df_summerTue['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerWed['treq'] = pd.to_datetime(df_summerWed['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerWed['wednesdayDate'] = wednesday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerWed['treq'] = df_summerWed['wednesdayDate'].astype(str) + df_summerWed['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:38: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerThur['treq'] = pd.to_datetime(df_summerThur['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:39: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerThur['thursdayDate'] = thursday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:40: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerThur['treq'] = df_summerThur['thursdayDate'].astype(str) + df_summerThur['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:44: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerFri['treq'] = pd.to_datetime(df_summerFri['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:45: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerFri['fridayDate'] = friday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerFri['treq'] = df_summerFri['fridayDate'].astype(str) + df_summerFri['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:50: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSat['treq'] = pd.to_datetime(df_summerSat['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:51: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSat['saturdayDate'] = saturday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:52: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSat['treq'] = df_summerSat['saturdayDate'].astype(str) + df_summerSat['time'].astype(str)\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:56: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSun['treq'] = pd.to_datetime(df_summerSun['treq'])\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSun['sundayDate'] = sunday\n",
      "C:\\Users\\ITM Nord Lånedator\\AppData\\Local\\Temp\\ipykernel_2292\\794768673.py:58: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_summerSun['treq'] = df_summerSun['sundayDate'].astype(str) + df_summerSun['time'].astype(str)\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>TRIP_ID</th>\n",
       "      <th>origin_x</th>\n",
       "      <th>origin_y</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>treq</th>\n",
       "      <th>END_TIME</th>\n",
       "      <th>duration_in_m</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>529866</th>\n",
       "      <td>529866</td>\n",
       "      <td>11.975588</td>\n",
       "      <td>57.708882</td>\n",
       "      <td>12.000563</td>\n",
       "      <td>57.714166</td>\n",
       "      <td>2022-07-03 03:00:59</td>\n",
       "      <td>2022-06-19 03:07:53</td>\n",
       "      <td>6.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529867</th>\n",
       "      <td>529867</td>\n",
       "      <td>11.986027</td>\n",
       "      <td>57.706913</td>\n",
       "      <td>11.985992</td>\n",
       "      <td>57.703012</td>\n",
       "      <td>2022-07-03 03:02:18</td>\n",
       "      <td>2022-06-19 03:11:01</td>\n",
       "      <td>8.716667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529868</th>\n",
       "      <td>529868</td>\n",
       "      <td>11.992002</td>\n",
       "      <td>57.691469</td>\n",
       "      <td>11.993202</td>\n",
       "      <td>57.689584</td>\n",
       "      <td>2022-07-03 03:03:21</td>\n",
       "      <td>2022-06-19 03:05:55</td>\n",
       "      <td>2.566667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529869</th>\n",
       "      <td>529869</td>\n",
       "      <td>11.995759</td>\n",
       "      <td>57.695672</td>\n",
       "      <td>12.007040</td>\n",
       "      <td>57.704726</td>\n",
       "      <td>2022-07-03 03:03:51</td>\n",
       "      <td>2022-06-19 03:09:36</td>\n",
       "      <td>5.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529870</th>\n",
       "      <td>529870</td>\n",
       "      <td>11.965565</td>\n",
       "      <td>57.698309</td>\n",
       "      <td>11.975567</td>\n",
       "      <td>57.699879</td>\n",
       "      <td>2022-07-03 03:04:30</td>\n",
       "      <td>2022-06-19 03:15:36</td>\n",
       "      <td>11.100000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772138</th>\n",
       "      <td>772138</td>\n",
       "      <td>11.947095</td>\n",
       "      <td>57.695297</td>\n",
       "      <td>11.965359</td>\n",
       "      <td>57.697781</td>\n",
       "      <td>2022-07-01 19:56:24</td>\n",
       "      <td>2022-08-19 20:03:38</td>\n",
       "      <td>7.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772139</th>\n",
       "      <td>772139</td>\n",
       "      <td>11.924828</td>\n",
       "      <td>57.690157</td>\n",
       "      <td>11.921186</td>\n",
       "      <td>57.693514</td>\n",
       "      <td>2022-07-01 19:57:03</td>\n",
       "      <td>2022-08-19 19:58:46</td>\n",
       "      <td>1.716667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772140</th>\n",
       "      <td>772140</td>\n",
       "      <td>11.946769</td>\n",
       "      <td>57.693184</td>\n",
       "      <td>11.972190</td>\n",
       "      <td>57.684282</td>\n",
       "      <td>2022-07-01 19:58:26</td>\n",
       "      <td>2022-08-19 20:07:56</td>\n",
       "      <td>9.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772141</th>\n",
       "      <td>772141</td>\n",
       "      <td>11.921893</td>\n",
       "      <td>57.695215</td>\n",
       "      <td>11.959331</td>\n",
       "      <td>57.691441</td>\n",
       "      <td>2022-07-01 19:59:20</td>\n",
       "      <td>2022-08-19 20:10:05</td>\n",
       "      <td>10.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772142</th>\n",
       "      <td>772142</td>\n",
       "      <td>11.940345</td>\n",
       "      <td>57.708272</td>\n",
       "      <td>11.919733</td>\n",
       "      <td>57.708815</td>\n",
       "      <td>2022-07-01 19:59:33</td>\n",
       "      <td>2022-08-19 20:04:27</td>\n",
       "      <td>4.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242277 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        TRIP_ID   origin_x   origin_y  destination_x  destination_y  \\\n",
       "529866   529866  11.975588  57.708882      12.000563      57.714166   \n",
       "529867   529867  11.986027  57.706913      11.985992      57.703012   \n",
       "529868   529868  11.992002  57.691469      11.993202      57.689584   \n",
       "529869   529869  11.995759  57.695672      12.007040      57.704726   \n",
       "529870   529870  11.965565  57.698309      11.975567      57.699879   \n",
       "...         ...        ...        ...            ...            ...   \n",
       "772138   772138  11.947095  57.695297      11.965359      57.697781   \n",
       "772139   772139  11.924828  57.690157      11.921186      57.693514   \n",
       "772140   772140  11.946769  57.693184      11.972190      57.684282   \n",
       "772141   772141  11.921893  57.695215      11.959331      57.691441   \n",
       "772142   772142  11.940345  57.708272      11.919733      57.708815   \n",
       "\n",
       "                       treq             END_TIME  duration_in_m  \n",
       "529866  2022-07-03 03:00:59  2022-06-19 03:07:53       6.900000  \n",
       "529867  2022-07-03 03:02:18  2022-06-19 03:11:01       8.716667  \n",
       "529868  2022-07-03 03:03:21  2022-06-19 03:05:55       2.566667  \n",
       "529869  2022-07-03 03:03:51  2022-06-19 03:09:36       5.750000  \n",
       "529870  2022-07-03 03:04:30  2022-06-19 03:15:36      11.100000  \n",
       "...                     ...                  ...            ...  \n",
       "772138  2022-07-01 19:56:24  2022-08-19 20:03:38       7.233333  \n",
       "772139  2022-07-01 19:57:03  2022-08-19 19:58:46       1.716667  \n",
       "772140  2022-07-01 19:58:26  2022-08-19 20:07:56       9.500000  \n",
       "772141  2022-07-01 19:59:20  2022-08-19 20:10:05      10.750000  \n",
       "772142  2022-07-01 19:59:33  2022-08-19 20:04:27       4.900000  \n",
       "\n",
       "[242277 rows x 8 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#7 assigning appropriate day of the week \n",
    "\n",
    "#probably a runner-up as the world's most inneficient piece of code \n",
    "monday = '2022-06-27 '\n",
    "tuesday = '2022-06-28 '\n",
    "wednesday = '2022-06-29 '\n",
    "thursday = '2022-06-30 '\n",
    "friday = '2022-07-01 '\n",
    "saturday = '2022-07-02 '\n",
    "sunday = '2022-07-03 '\n",
    "\n",
    "\n",
    "df_summer['treq'] = pd.to_datetime(df_summer['treq'], format='%Y-%m-%d %H:%M:%S')\n",
    "df_summer['treq'] = pd.to_datetime(df_summer['treq'].dt.strftime('%Y-%m-%d %H:%M:%S'))\n",
    "\n",
    "df_summer['treqDayName']=df_summer['treq'].dt.day_name()\n",
    "df_summer['time'] = [d.time() for d in df_summer['treq']]\n",
    "\n",
    "df_summerMon = df_summer.loc[df_summer['treqDayName'] == 'Monday']\n",
    "df_summerMon['treq'] = pd.to_datetime(df_summerMon['treq'])\n",
    "df_summerMon['mondayDate'] = monday\n",
    "df_summerMon['treq'] = df_summerMon['mondayDate'].astype(str) + df_summerMon['time'].astype(str)\n",
    "df_summerMon=df_summerMon.drop(['treqDayName','time','mondayDate'], axis=1)\n",
    "\n",
    "df_summerTue = df_summer.loc[df_summer['treqDayName'] == 'Tuesday']\n",
    "df_summerTue['treq'] = pd.to_datetime(df_summerTue['treq'])\n",
    "df_summerTue['tuesdayDate'] = tuesday\n",
    "df_summerTue['treq'] = df_summerTue['tuesdayDate'].astype(str) + df_summerTue['time'].astype(str)\n",
    "df_summerTue=df_summerTue.drop(['treqDayName','time','tuesdayDate'], axis=1)\n",
    "\n",
    "df_summerWed= df_summer.loc[df_summer['treqDayName'] == 'Wednesday']\n",
    "df_summerWed['treq'] = pd.to_datetime(df_summerWed['treq'])\n",
    "df_summerWed['wednesdayDate'] = wednesday\n",
    "df_summerWed['treq'] = df_summerWed['wednesdayDate'].astype(str) + df_summerWed['time'].astype(str)\n",
    "df_summerWed=df_summerWed.drop(['treqDayName','time','wednesdayDate'], axis=1)\n",
    "\n",
    "df_summerThur= df_summer.loc[df_summer['treqDayName'] == 'Thursday']\n",
    "df_summerThur['treq'] = pd.to_datetime(df_summerThur['treq'])\n",
    "df_summerThur['thursdayDate'] = thursday\n",
    "df_summerThur['treq'] = df_summerThur['thursdayDate'].astype(str) + df_summerThur['time'].astype(str)\n",
    "df_summerThur=df_summerThur.drop(['treqDayName','time','thursdayDate'], axis=1)\n",
    "\n",
    "df_summerFri= df_summer.loc[df_summer['treqDayName'] == 'Friday']\n",
    "df_summerFri['treq'] = pd.to_datetime(df_summerFri['treq'])\n",
    "df_summerFri['fridayDate'] = friday\n",
    "df_summerFri['treq'] = df_summerFri['fridayDate'].astype(str) + df_summerFri['time'].astype(str)\n",
    "df_summerFri=df_summerFri.drop(['treqDayName','time','fridayDate'], axis=1)\n",
    "\n",
    "df_summerSat= df_summer.loc[df_summer['treqDayName'] == 'Saturday']\n",
    "df_summerSat['treq'] = pd.to_datetime(df_summerSat['treq'])\n",
    "df_summerSat['saturdayDate'] = saturday\n",
    "df_summerSat['treq'] = df_summerSat['saturdayDate'].astype(str) + df_summerSat['time'].astype(str)\n",
    "df_summerSat=df_summerSat.drop(['treqDayName','time','saturdayDate'], axis=1)\n",
    "\n",
    "df_summerSun= df_summer.loc[df_summer['treqDayName'] == 'Sunday']\n",
    "df_summerSun['treq'] = pd.to_datetime(df_summerSun['treq'])\n",
    "df_summerSun['sundayDate'] = sunday\n",
    "df_summerSun['treq'] = df_summerSun['sundayDate'].astype(str) + df_summerSun['time'].astype(str)\n",
    "df_summerSun=df_summerSun.drop(['treqDayName','time','sundayDate'], axis=1)\n",
    "\n",
    "\n",
    "df_summer = pd.concat([df_summerMon,df_summerTue,df_summerWed,df_summerThur,df_summerFri,df_summerSat,df_summerSun])\n",
    "df_summer.sort_values(by='TRIP_ID', ascending=True, inplace=True)\n",
    "df_summer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#8 export to CSV \n",
    "df_winter.to_csv('Q/georequestsWinter.csv', index=False)\n",
    "df_summer.to_csv('Q/georequestsSummer.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12 (main, Apr  4 2022, 05:22:27) [MSC v.1916 64 bit (AMD64)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}