1-taxigps_to_od_en.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial, you will learn how to use Python's pandas package to clean the taxi GPS data  and identify the origin-destination (OD) of travel flow\n",
    "\n",
    "<div class=\"alert alert-info\"><h2>The data provided:</h2><p>    data：<br>  \n",
    "    1.taxi GPS data(in the data-sample folder, data of 500 vehicles sampled from the original data set)</p></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[pandas documentation](https://pandas.pydata.org/pandas-docs/stable/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, read the taxi GPS data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:51:53.552930Z",
     "start_time": "2020-01-18T04:51:52.397018Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "#Read data\n",
    "data = pd.read_csv(r'data-sample/TaxiData-Sample',header = None)\n",
    "#rename the columns for the data\n",
    "data.columns = ['VehicleNum', 'Stime', 'Lng', 'Lat', 'OpenStatus', 'Speed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:51:58.299239Z",
     "start_time": "2020-01-18T04:51:58.271312Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22271</td>\n",
       "      <td>22:54:04</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:26:26</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:35:18</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22271</td>\n",
       "      <td>16:02:46</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22271</td>\n",
       "      <td>21:41:17</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "0       22271  22:54:04  114.167000  22.718399           0      0\n",
       "1       22271  18:26:26  114.190598  22.647800           0      4\n",
       "2       22271  18:35:18  114.201401  22.649700           0      0\n",
       "3       22271  16:02:46  114.233498  22.725901           0     24\n",
       "4       22271  21:41:17  114.233597  22.720900           0     19"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#display the first 5 rows of the data\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Format of data：\n",
    "\n",
    ">VehicleNum —— Vehicle Number(license plate)  \n",
    "Stime —— GPS time  \n",
    "Lng —— Longitude  \n",
    "Lat —— Latitude  \n",
    "OpenStatus —— is it occupied(0 - No，1 - Yes)  \n",
    "Speed —— Speed  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic data processing methods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame和Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame和Series\n",
    "\n",
    "   > When we read a data, we read in the data table in the format of **DataFrame**, and each column in a dataframe is a **Series**  \n",
    "    In another word, a **DataFrame** consists of multiple **Series**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:52:25.713432Z",
     "start_time": "2020-01-18T04:52:25.708450Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to get a column of **DataFrame**, and want to get **Series**, we can use the following code\n",
    "\n",
    "   > data[**column name**]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:52:32.097575Z",
     "start_time": "2020-01-18T04:52:32.090592Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data['Lng'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-06T09:22:43.642625Z",
     "start_time": "2019-09-06T09:22:43.638487Z"
    }
   },
   "source": [
    "If we want to get a column or columns of **DataFrame**, and what we want is a **DataFrame**,  use the following code\n",
    "\n",
    "> data2[[**column name1**,**column name2**]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:52:33.013124Z",
     "start_time": "2020-01-18T04:52:32.990186Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data[['Lng']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data filtering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data filtering:\n",
    "\n",
    ">When filtering data, we usually use the code: **data[condition]**  \n",
    "The condition is the series of true and false Boolean for each row of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">For example, if we want to get all the data with a license plate of 22271  \n",
    "First of all, we need to obtain a series of Boolean variables, which corresponds to each row of data. If the license plate is \"22271\", it is true. If not, it is false  \n",
    "Such a sub series is easy to obtain:  \n",
    "data['VehicleNum']==22271"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:52:44.078571Z",
     "start_time": "2020-01-18T04:52:44.049646Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    True\n",
       "1    True\n",
       "2    True\n",
       "3    True\n",
       "4    True\n",
       "Name: VehicleNum, dtype: bool"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(data['VehicleNum']==22271).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:52:51.723416Z",
     "start_time": "2020-01-18T04:52:51.688510Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22271</td>\n",
       "      <td>22:54:04</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:26:26</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:35:18</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22271</td>\n",
       "      <td>16:02:46</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22271</td>\n",
       "      <td>21:41:17</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "0       22271  22:54:04  114.167000  22.718399           0      0\n",
       "1       22271  18:26:26  114.190598  22.647800           0      4\n",
       "2       22271  18:35:18  114.201401  22.649700           0      0\n",
       "3       22271  16:02:46  114.233498  22.725901           0     24\n",
       "4       22271  21:41:17  114.233597  22.720900           0     19"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get all data with license plate 22271\n",
    "data[data['VehicleNum']==22271].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to delete all the data with the license plate of 22271 from the data, it is also very easy:\n",
    "    \n",
    "> data[-(condition)]\n",
    "    \n",
    "Note: if you don't want strange bugs, please filter the data according to the format I give above. I don't recommend using **data.drop()** to delete row data. **data.drop()** is only used when you want to delete a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:05.516849Z",
     "start_time": "2020-01-18T04:53:05.403154Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1437</td>\n",
       "      <td>35807</td>\n",
       "      <td>01:53:46</td>\n",
       "      <td>113.809898</td>\n",
       "      <td>22.626801</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1438</td>\n",
       "      <td>35807</td>\n",
       "      <td>01:43:46</td>\n",
       "      <td>113.813301</td>\n",
       "      <td>22.623600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1439</td>\n",
       "      <td>35807</td>\n",
       "      <td>01:14:15</td>\n",
       "      <td>113.847000</td>\n",
       "      <td>22.594700</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1440</td>\n",
       "      <td>35807</td>\n",
       "      <td>02:01:41</td>\n",
       "      <td>113.852501</td>\n",
       "      <td>22.625700</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1441</td>\n",
       "      <td>35807</td>\n",
       "      <td>01:01:59</td>\n",
       "      <td>113.897003</td>\n",
       "      <td>22.551901</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "1437       35807  01:53:46  113.809898  22.626801           0      0\n",
       "1438       35807  01:43:46  113.813301  22.623600           0      0\n",
       "1439       35807  01:14:15  113.847000  22.594700           0     41\n",
       "1440       35807  02:01:41  113.852501  22.625700           0     22\n",
       "1441       35807  01:01:59  113.897003  22.551901           0     42"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Delete data with license plate 22271\n",
    "data[-(data['VehicleNum']==22271)].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How to obtain/delete/define a column of dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:06.731768Z",
     "start_time": "2020-01-18T04:53:06.688883Z"
    }
   },
   "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>Stime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22:54:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>18:26:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>18:35:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>16:02:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>21:41:17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Stime\n",
       "0  22:54:04\n",
       "1  18:26:26\n",
       "2  18:35:18\n",
       "3  16:02:46\n",
       "4  21:41:17"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Obtain the 'Stime' column from the data. Note that this operation will not affect data. \n",
    "#You must reassign the resulting table to data after the operation\n",
    "data[['Stime']].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:07.602784Z",
     "start_time": "2020-01-18T04:53:07.471136Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Speed1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22271</td>\n",
       "      <td>22:54:04</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:26:26</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:35:18</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22271</td>\n",
       "      <td>16:02:46</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22271</td>\n",
       "      <td>21:41:17</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VehicleNum     Stime         Lng        Lat  OpenStatus  Speed  Speed1\n",
       "0       22271  22:54:04  114.167000  22.718399           0      0       0\n",
       "1       22271  18:26:26  114.190598  22.647800           0      4       8\n",
       "2       22271  18:35:18  114.201401  22.649700           0      0       0\n",
       "3       22271  16:02:46  114.233498  22.725901           0     24      48\n",
       "4       22271  21:41:17  114.233597  22.720900           0     19      38"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Define column 'speed1' as twice as the 'speed' column, \n",
    "#Note that this operation will affect data\n",
    "data['Speed1']=data['Speed']*2\n",
    "\n",
    "#Or\n",
    "\n",
    "data.loc[:,'Speed1']=data['Speed']*2\n",
    "\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:45.170336Z",
     "start_time": "2020-01-18T04:53:45.109499Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Speed1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22271</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22271</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22271</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22271</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22271</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VehicleNum         Lng        Lat  OpenStatus  Speed  Speed1\n",
       "0       22271  114.167000  22.718399           0      0       0\n",
       "1       22271  114.190598  22.647800           0      4       8\n",
       "2       22271  114.201401  22.649700           0      0       0\n",
       "3       22271  114.233498  22.725901           0     24      48\n",
       "4       22271  114.233597  22.720900           0     19      38"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Delete column 'Stime'. Note that this operation will not affect data. \n",
    "#You must reassign the resulting table to data after the operation\n",
    "data.drop(['Stime'],axis=1).head(5)# will not affect data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:48.056620Z",
     "start_time": "2020-01-18T04:53:47.972844Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22271</td>\n",
       "      <td>22:54:04</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:26:26</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22271</td>\n",
       "      <td>18:35:18</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22271</td>\n",
       "      <td>16:02:46</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22271</td>\n",
       "      <td>21:41:17</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "0       22271  22:54:04  114.167000  22.718399           0      0\n",
       "1       22271  18:26:26  114.190598  22.647800           0      4\n",
       "2       22271  18:35:18  114.201401  22.649700           0      0\n",
       "3       22271  16:02:46  114.233498  22.725901           0     24\n",
       "4       22271  21:41:17  114.233597  22.720900           0     19"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Delete column 'Stime'. Note that this operation will not affect data. \n",
    "#You must reassign the resulting table to data after the operation\n",
    "\n",
    "\n",
    "data = data.drop(['Speed1'],axis=1) #reassign to data. \n",
    "#The parameter axis = 1 means to delete a column. \n",
    "#If axis = 0, it means to delete a row. \n",
    "#However, this function is not recommended to delete rows.\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Obtain a row from a column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When obtaining a row, remember to use **iloc** (get by the current order of the table), not **loc** (get by index)\n",
    "\n",
    "Because after we do filtering, sorting and other operations many times, the tables are not arranged by index.  \n",
    "If we use **loc** to get the columns, we will get the wrong columns, or directly report an error (can't find this index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:50.234795Z",
     "start_time": "2020-01-18T04:53:50.226816Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'16:02:46'"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get 4th row data of Stime column\n",
    "data['Stime'].iloc[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First of all, we sort the data according to the license plate and time.   \n",
    "Remember, after sorting the data, we need to reassign it to data, otherwise it will not work"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:52.863800Z",
     "start_time": "2020-01-18T04:53:51.815569Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:00:49</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>397</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:01:48</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1413</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:02:47</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>244</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:03:46</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>247</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:04:45</td>\n",
       "      <td>114.268898</td>\n",
       "      <td>22.729500</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "39         22271  00:00:49  114.266502  22.728201           0      0\n",
       "397        22271  00:01:48  114.266502  22.728201           0      0\n",
       "1413       22271  00:02:47  114.266502  22.728201           0      0\n",
       "244        22271  00:03:46  114.266502  22.728201           0      0\n",
       "247        22271  00:04:45  114.268898  22.729500           0     11"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the data and reassign the sorted data to the original data\n",
    "data = data.sort_values(by = ['VehicleNum','Stime'])\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we need to do now is to identify origin and destination OD of trips from taxi GPS data:\n",
    "After the data is sorted by license plate and time, the **openstatus** is as follows:\n",
    "\n",
    "|OpenStatus |  |\n",
    "| :-----------: |-----------|\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|1|←the trip begin|\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|0| ←the trip end|\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "\n",
    "However, sometimes there are abnormals , such as:\n",
    "\n",
    "|OpenStatus |  |\n",
    "| ----------- |-----------|\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|1|←abnormal|\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "|0||\n",
    "\n",
    "or\n",
    "\n",
    "|OpenStatus |  |\n",
    "| ----------- |-----------|\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|0|←abnormal|\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "|1||\n",
    "\n",
    "All of a sudden, a piece of data becomes 1, or all of a sudden, it becomes 0.   \n",
    "We are going to rule out this kind of abnormal situation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In pandas, the best way to filter out the data we don't want is\n",
    "\n",
    ">**data[condition]** is to keep qualified data  \n",
    "**data[-(condition)]** is to delete qualified data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And there is:\n",
    ">The **shift()** function of **Series** moves data one row backward in order  \n",
    "The **shift(-1)** function of **Series** moves data one row forward  in order  \n",
    "So we have to judge:  \n",
    "For every three row, if the latter row equals to the former row, but the latter row and the middle row are different, then the data in the middle will be deleted (and the license plate of these three row must be the same)\n",
    "    \n",
    "So, let's delete the abnormal data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:55.724226Z",
     "start_time": "2020-01-18T04:53:55.717245Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1601307"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data volumne before filtering\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:56.442306Z",
     "start_time": "2020-01-18T04:53:56.438317Z"
    }
   },
   "outputs": [],
   "source": [
    "###########################You need to write code here#############################\n",
    "#Here, clean the abnormal data mentioned above\n",
    "#The conditions used are:\n",
    "#1.latter row equals to the former row\n",
    "#2.but the latter row and the middle row are different\n",
    "#3.the license plate of latter row equals to the former row\n",
    "#4.the license plate of middle row equals to the latter row\n",
    "\n",
    "#The intersection of conditions\n",
    "#data[(condition1)&(condition2)]\n",
    "#The union between the conditions\n",
    "#data[(condition1)|(condition2)]\n",
    "\n",
    "\n",
    "#data = \n",
    "\n",
    "\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:57.340933Z",
     "start_time": "2020-01-18T04:53:57.057662Z"
    }
   },
   "outputs": [],
   "source": [
    "###############################     Answer   ##################################\n",
    "data = data[-((data['OpenStatus'].shift(-1) == data['OpenStatus'].shift())&\n",
    "(data['OpenStatus'].shift(-1) != data['OpenStatus'])&\n",
    "(data['VehicleNum'].shift(-1) == data['VehicleNum'].shift())&\n",
    "(data['VehicleNum'].shift(-1) == data['VehicleNum']))]\n",
    "\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:53:58.198611Z",
     "start_time": "2020-01-18T04:53:58.191630Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1598866"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#If your code is correct, the amount of data after filter should be\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identification of OD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## identify state change of passengers getting on and off"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:00.064621Z",
     "start_time": "2020-01-18T04:54:00.050659Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:00:49</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>397</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:01:48</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1413</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:02:47</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>244</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:03:46</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>247</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:04:45</td>\n",
       "      <td>114.268898</td>\n",
       "      <td>22.729500</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      VehicleNum     Stime         Lng        Lat  OpenStatus  Speed\n",
       "39         22271  00:00:49  114.266502  22.728201           0      0\n",
       "397        22271  00:01:48  114.266502  22.728201           0      0\n",
       "1413       22271  00:02:47  114.266502  22.728201           0      0\n",
       "244        22271  00:03:46  114.266502  22.728201           0      0\n",
       "247        22271  00:04:45  114.268898  22.729500           0     11"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we put the information of the next row on the previous row. In this way, we can easily compare the difference between this row and the next row\n",
    "\n",
    "Create new column to represent the value of the next row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition, we define the column **StatusChange** equals to the **OpenStatus** of the next row minus the **OpenStatus** of this row, and then it will become:\n",
    "\n",
    "\n",
    "\n",
    "|OpenStatus     |   OpenStatus1(next row)    |  StatusChange||\n",
    "| ----------- |-----------|||\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "|0         |        0    |             0||\n",
    "|0          |       1    |             1 |    ←the trip begin|\n",
    "|1          |       1    |             0  |   ←the trip begin|\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       1    |             0||\n",
    "|1          |       0    |             -1|    ←the trip end|\n",
    "|0          |       0    |             0  |  ←the trip end|\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "\n",
    "Note that there are two rows representing passengers get on and get off. Generally speaking, we think the location and time of these two data are very close, which can both be considered."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:01.994461Z",
     "start_time": "2020-01-18T04:54:01.989475Z"
    }
   },
   "outputs": [],
   "source": [
    "###########################You need to write code here#############################\n",
    "#Assign the next row of these fields to the new field. \n",
    "#Add '1' to the field name to represent the value of the next row\n",
    "#data.loc[:,'OpenStatus1'] = \n",
    "#data.loc[:,'VehicleNum1'] = \n",
    "#data.loc[:,'Lng1'] = \n",
    "#data.loc[:,'Lat1'] = \n",
    "#data.loc[:,'Stime1'] = \n",
    "\n",
    "#data.loc[:,'StatusChange'] = \n",
    "\n",
    "\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:03.862529Z",
     "start_time": "2020-01-18T04:54:02.623838Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "      <th>OpenStatus1</th>\n",
       "      <th>VehicleNum1</th>\n",
       "      <th>Lng1</th>\n",
       "      <th>Lat1</th>\n",
       "      <th>Stime1</th>\n",
       "      <th>StatusChange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:00:49</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22271.0</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>00:01:48</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>397</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:01:48</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22271.0</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>00:02:47</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1413</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:02:47</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22271.0</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>00:03:46</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>244</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:03:46</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22271.0</td>\n",
       "      <td>114.268898</td>\n",
       "      <td>22.729500</td>\n",
       "      <td>00:04:45</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>247</td>\n",
       "      <td>22271</td>\n",
       "      <td>00:04:45</td>\n",
       "      <td>114.268898</td>\n",
       "      <td>22.729500</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22271.0</td>\n",
       "      <td>114.272003</td>\n",
       "      <td>22.731199</td>\n",
       "      <td>00:05:44</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      VehicleNum     Stime         Lng        Lat  OpenStatus  Speed  \\\n",
       "39         22271  00:00:49  114.266502  22.728201           0      0   \n",
       "397        22271  00:01:48  114.266502  22.728201           0      0   \n",
       "1413       22271  00:02:47  114.266502  22.728201           0      0   \n",
       "244        22271  00:03:46  114.266502  22.728201           0      0   \n",
       "247        22271  00:04:45  114.268898  22.729500           0     11   \n",
       "\n",
       "      OpenStatus1  VehicleNum1        Lng1       Lat1    Stime1  StatusChange  \n",
       "39            0.0      22271.0  114.266502  22.728201  00:01:48           0.0  \n",
       "397           0.0      22271.0  114.266502  22.728201  00:02:47           0.0  \n",
       "1413          0.0      22271.0  114.266502  22.728201  00:03:46           0.0  \n",
       "244           0.0      22271.0  114.268898  22.729500  00:04:45           0.0  \n",
       "247           0.0      22271.0  114.272003  22.731199  00:05:44           0.0  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###############################     Answer   ##################################\n",
    "data.loc[:,'OpenStatus1'] = data['OpenStatus'].shift(-1)\n",
    "data.loc[:,'VehicleNum1'] = data['VehicleNum'].shift(-1)\n",
    "data.loc[:,'Lng1'] = data['Lng'].shift(-1)\n",
    "data.loc[:,'Lat1'] = data['Lat'].shift(-1)\n",
    "data.loc[:,'Stime1'] = data['Stime'].shift(-1)\n",
    "\n",
    "data.loc[:,'StatusChange'] = data['OpenStatus1']-data['OpenStatus']\n",
    "\n",
    "\n",
    "###################################################################################\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sort out the boarding and alighting status into OD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, I just want to keep the rows whose **statuschange** field is 1 or - 1\n",
    "\n",
    "However, the vehicle ID of this row and the next row must be the same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:05.800844Z",
     "start_time": "2020-01-18T04:54:05.795858Z"
    }
   },
   "outputs": [],
   "source": [
    "###########################You need to write code here#############################\n",
    "#two conditions：\n",
    "#1.StatusChange is 1 or - 1\n",
    "#2.the vehicle ID of this row and the next row must be the same\n",
    "#data=\n",
    "\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:06.863033Z",
     "start_time": "2020-01-18T04:54:06.800172Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>OpenStatus</th>\n",
       "      <th>Speed</th>\n",
       "      <th>OpenStatus1</th>\n",
       "      <th>VehicleNum1</th>\n",
       "      <th>Lng1</th>\n",
       "      <th>Lat1</th>\n",
       "      <th>Stime1</th>\n",
       "      <th>StatusChange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>00:01:04</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548351</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:07:44</td>\n",
       "      <td>114.080498</td>\n",
       "      <td>22.554182</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.080498</td>\n",
       "      <td>22.554182</td>\n",
       "      <td>00:07:57</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549620</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>00:18:16</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547182</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:18:56</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>00:19:05</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547627</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>00:44:52</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VehicleNum     Stime         Lng        Lat  OpenStatus  Speed  \\\n",
       "1548741       22334  00:00:52  114.111130  22.576750           1     13   \n",
       "1548351       22334  00:07:44  114.080498  22.554182           0     11   \n",
       "1549620       22334  00:17:58  114.084915  22.540850           1      2   \n",
       "1547182       22334  00:18:56  114.084915  22.540850           0      0   \n",
       "1547627       22334  00:44:47  114.056236  22.633383           1      3   \n",
       "\n",
       "         OpenStatus1  VehicleNum1        Lng1       Lat1    Stime1  \\\n",
       "1548741          0.0      22334.0  114.111130  22.576750  00:01:04   \n",
       "1548351          1.0      22334.0  114.080498  22.554182  00:07:57   \n",
       "1549620          0.0      22334.0  114.084915  22.540850  00:18:16   \n",
       "1547182          1.0      22334.0  114.084915  22.540850  00:19:05   \n",
       "1547627          0.0      22334.0  114.056236  22.633383  00:44:52   \n",
       "\n",
       "         StatusChange  \n",
       "1548741          -1.0  \n",
       "1548351           1.0  \n",
       "1549620          -1.0  \n",
       "1547182           1.0  \n",
       "1547627          -1.0  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###############################     Answer   ##################################\n",
    "data = data[((data['StatusChange'] == 1)|(data['StatusChange'] == -1))\n",
    "&(data['VehicleNum'] == data['VehicleNum1'])]\n",
    "\n",
    "###################################################################################\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:07.783542Z",
     "start_time": "2020-01-18T04:54:07.764594Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>StatusChange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548351</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:07:44</td>\n",
       "      <td>114.080498</td>\n",
       "      <td>22.554182</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549620</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547182</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:18:56</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547627</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VehicleNum     Stime         Lng        Lat  StatusChange\n",
       "1548741       22334  00:00:52  114.111130  22.576750          -1.0\n",
       "1548351       22334  00:07:44  114.080498  22.554182           1.0\n",
       "1549620       22334  00:17:58  114.084915  22.540850          -1.0\n",
       "1547182       22334  00:18:56  114.084915  22.540850           1.0\n",
       "1547627       22334  00:44:47  114.056236  22.633383          -1.0"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#only keeps some fields we need\n",
    "data = data[['VehicleNum','Stime','Lng','Lat','StatusChange']]\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have the data with the boarding and alighting information.\n",
    "\n",
    "We want to get OD data in the form that each row of record includes information: vehicle ID, boarding time, boarding place, alighting time, alighting place\n",
    "\n",
    "Such a row of data is an OD\n",
    "\n",
    "So, the next step is..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:15.682458Z",
     "start_time": "2020-01-18T04:54:15.669456Z"
    }
   },
   "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>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>SLng</th>\n",
       "      <th>SLat</th>\n",
       "      <th>ELng</th>\n",
       "      <th>ELat</th>\n",
       "      <th>Etime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548351</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:07:44</td>\n",
       "      <td>114.080498</td>\n",
       "      <td>22.554182</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>00:17:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547182</td>\n",
       "      <td>22334</td>\n",
       "      <td>00:18:56</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>00:44:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547511</td>\n",
       "      <td>22334</td>\n",
       "      <td>02:38:35</td>\n",
       "      <td>114.091637</td>\n",
       "      <td>22.543200</td>\n",
       "      <td>114.093498</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>02:46:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547789</td>\n",
       "      <td>22334</td>\n",
       "      <td>03:58:46</td>\n",
       "      <td>114.038818</td>\n",
       "      <td>22.553232</td>\n",
       "      <td>114.052299</td>\n",
       "      <td>22.604366</td>\n",
       "      <td>04:13:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547764</td>\n",
       "      <td>22334</td>\n",
       "      <td>06:30:11</td>\n",
       "      <td>114.031250</td>\n",
       "      <td>22.519550</td>\n",
       "      <td>114.067886</td>\n",
       "      <td>22.521299</td>\n",
       "      <td>06:41:19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VehicleNum     Stime        SLng       SLat        ELng       ELat  \\\n",
       "1548351       22334  00:07:44  114.080498  22.554182  114.084915  22.540850   \n",
       "1547182       22334  00:18:56  114.084915  22.540850  114.056236  22.633383   \n",
       "1547511       22334  02:38:35  114.091637  22.543200  114.093498  22.554382   \n",
       "1547789       22334  03:58:46  114.038818  22.553232  114.052299  22.604366   \n",
       "1547764       22334  06:30:11  114.031250  22.519550  114.067886  22.521299   \n",
       "\n",
       "            Etime  \n",
       "1548351  00:17:58  \n",
       "1547182  00:44:47  \n",
       "1547511  02:46:52  \n",
       "1547789  04:13:57  \n",
       "1547764  06:41:19  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###########################You need to write code here#############################\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "###################################################################################\n",
    "data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:54:12.768242Z",
     "start_time": "2020-01-18T04:54:12.732310Z"
    }
   },
   "outputs": [],
   "source": [
    "###############################     Answer   ##################################\n",
    "data = data.rename(columns = {'Lng':'SLng','Lat':'SLat'})\n",
    "data['ELng'] = data['SLng'].shift(-1)\n",
    "data['ELat'] = data['SLat'].shift(-1)\n",
    "data['Etime'] = data['Stime'].shift(-1)\n",
    "data = data[data['StatusChange'] == 1]\n",
    "data = data.drop('StatusChange',axis = 1)\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finished! The next step is to save  \n",
    "\n",
    "There is a taxiod.csv file in the data folder, which I use 1.7GB of all data to calculate the OD. We can use that in the next tutorial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-18T04:20:14.690034Z",
     "start_time": "2020-01-18T04:20:11.579344Z"
    }
   },
   "outputs": [],
   "source": [
    "data.to_csv(r'data-sample\\TaxiOD-Sample.csv',index = None)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "211px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}