11-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "非常感谢您学习到这里，如果有时间的话希望能帮我填一个：[教程反馈](https://www.wjx.cn/jq/61058547.aspx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在这个教程中，我想用taxi gps数据计算每一辆出租车在一天内的收入。如果能够成功计算，再看看高收入的出租车司机的行为，就可以分析出来：  \n",
    ">毕业以后要是失业了，怎么样开出租车才能赚更多的钱！升职加薪赢取白富美就在明天！\n",
    "\n",
    "提供的基础数据是：\n",
    "<div class=\"alert alert-info\"><h2>提供的基础数据是：</h2><p>   \n",
    "    数据：<br>  \n",
    "    1.出租车原始GPS数据<br>  \n",
    "\n",
    " </p></div>\n",
    " \n",
    " <div class=\"alert alert-info\"><h2>深圳出租车计价规则：</h2><p>   \n",
    "(一)起步价：首2公里11.00元;<br>  \n",
    "(二)里程价：超过2公里部分，每公里2.40元;<br>  \n",
    "(三)返空费：每天的23时至次日凌晨6时，超过25公里部分，每公里按上述里程价的30%加收返空费：<br>  \n",
    "(四)夜间附加费：夜间起步价16元，每天的23时至次日凌晨6时，按上述起步价和里程价的20%加收夜间附加费;<br>  \n",
    "(五)候时费：每分钟0.80元;<br>  \n",
    "(六)大件行李费;体积超过0.2立方米、重量超过20公斤的大件行李，每件0.50元。<br>  \n",
    " </p></div>  \n",
    " \n",
    " \n",
    "在计算的时候，我们可以以订单开始的时间判定夜间还是日间。另外，规则（六）也考虑不了"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 思路\n",
    "## 首先，我们的输入和输出是什么？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "输入很简单，TAXIGPS数据  \n",
    "输出的形式，应该是：每条订单，收入  \n",
    "为了后续的分析，在输出的基础上，再增加：订单号，收入，车牌号，订单开始时间，订单结束时间，订单上车坐标，订单下车坐标，行驶里程\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-15T10:04:59.194298Z",
     "start_time": "2019-09-15T10:04:59.171344Z"
    }
   },
   "source": [
    "那么，从输入怎么变成输出？计算出租车费，我们需要几个东西：订单号，行驶里程，订单开始时间（判断是否为夜间），候时的时长"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 任务分解\n",
    "\n",
    "搞清楚输入和输出后，应用我们结构化数据的存储及处理的思维方式，就可以进行任务分解了，我们要解决的就是以下几个任务:  \n",
    "\n",
    "    任务1：由最原始的输入，数据清洗，输出表1，字段与原始数据一致  \n",
    "    \n",
    "    任务2：表1输入，整理出每个订单的轨迹，用来计算里程和候时时长，输出表2，字段为：订单号，轨迹点经纬度，轨迹点的时间（订单号这一列需要我们自己定义）,速度（用来计算候时时长）  \n",
    "    \n",
    "    任务3：表2输入，计算每个订单的里程和候时时长，输出表3，字段为：订单号，订单的里程，候时时长  \n",
    "    \n",
    "    任务4：表1输入，整理出每个订单的其他信息，输出表4，字段为：订单号，车牌号，订单开始时间，订单结束时间，订单上车坐标，订单下车坐标  \n",
    "    \n",
    "    任务5：表3与表4输入，连接两个表，再计算订单收入，输出表5，字段为：订单号，收入，车牌号，订单开始时间，订单结束时间，订单上车坐标，订单下车坐标，行驶里程"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T01:22:44.059974Z",
     "start_time": "2019-10-16T01:21:38.056823Z"
    }
   },
   "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": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#读取数据\n",
    "data2 = pd.read_csv(r'data-sample/TaxiData-Sample',header = None)\n",
    "#给数据命名列\n",
    "data2.columns = ['VehicleNum', 'Stime', 'Lng', 'Lat', 'OpenStatus', 'Speed']\n",
    "data2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 订单收入计算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T01:24:15.989284Z",
     "start_time": "2019-10-16T01:22:49.000250Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1598866"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2=data2.sort_values(by=['VehicleNum','Stime'])\n",
    "#第一节课教的\n",
    "data2 = data2[-((data2['OpenStatus'].shift(1)== data2['OpenStatus'].shift(-1))&\\\n",
    "      (data2['OpenStatus'].shift(1)!= data2['OpenStatus'])&\\\n",
    "      (data2['VehicleNum'].shift(1)==data2['VehicleNum'].shift(-1))&\\\n",
    "      (data2['VehicleNum']==data2['VehicleNum'].shift(1)))]\n",
    "len(data2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "#将时间字符串转换为pd的时间格式，后面可以轻松的做加减\n",
    "data2['Stime'] = pd.to_datetime(data2['Stime'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 订单号生成"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果StatusChange为这一条数据的OpenStatus减去上一条数据的OpenStatus，这样就会出现：\n",
    "\n",
    "|OpenStatus     |   OpenStatus1    |  StatusChange||\n",
    "| ----------- |-----------|||\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "|0         |        0    |             0||\n",
    "|0          |       0    |             0 |    ←此时乘客上车了|\n",
    "|1          |       0    |             1  |   ←此时乘客上车了|\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          |       1    |             0|    ←此时乘客下车了|\n",
    "|0          |       1    |             -1  |  ←此时乘客下车了|\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "|0          |       0    |             0||\n",
    "\n",
    "\n",
    "那么如果我只把所有OpenStatus==1的数据拿出来，就会有：\n",
    "\n",
    "\n",
    "|OpenStatus     |   orderid(订单号：对StatusChange的累加，可以用pandas的.cumsum()功能)    |  StatusChange||\n",
    "| ----------- |-----------|||\n",
    "|1          |       0    |             0||\n",
    "|1          |       0    |             0||\n",
    "|1         |        0    |             0||\n",
    "|1          |       0    |             0 ||\n",
    "|1          |       1    |             1  |   ←新订单|\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          |       1    |             0||\n",
    "|1          |       2   |             1  |  ←新订单|\n",
    "|1          |       2    |             0||\n",
    "|1          |       2    |             0||\n",
    "|1          |       2    |             0||\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T01:24:44.909026Z",
     "start_time": "2019-10-16T01:24:25.064489Z"
    },
    "scrolled": true
   },
   "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",
       "      <th>orderid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1550370</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>2020-01-23 00:00:52</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549617</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:08:37</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547008</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:08:57</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548874</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:09:17</td>\n",
       "      <td>114.081131</td>\n",
       "      <td>22.552799</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550335</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:09:37</td>\n",
       "      <td>114.080681</td>\n",
       "      <td>22.551718</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.081131</td>\n",
       "      <td>22.552799</td>\n",
       "      <td>2020-01-23 00:09:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547678</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:09:57</td>\n",
       "      <td>114.078049</td>\n",
       "      <td>22.551600</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.080681</td>\n",
       "      <td>22.551718</td>\n",
       "      <td>2020-01-23 00:09:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549818</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:10:17</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.078049</td>\n",
       "      <td>22.551600</td>\n",
       "      <td>2020-01-23 00:09:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548021</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:10:37</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>2020-01-23 00:10:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548022</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:10:57</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>2020-01-23 00:10:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548178</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:11:17</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>2020-01-23 00:10:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550695</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:11:37</td>\n",
       "      <td>114.075966</td>\n",
       "      <td>22.551001</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076401</td>\n",
       "      <td>22.551550</td>\n",
       "      <td>2020-01-23 00:11:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549985</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:11:57</td>\n",
       "      <td>114.076035</td>\n",
       "      <td>22.548416</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.075966</td>\n",
       "      <td>22.551001</td>\n",
       "      <td>2020-01-23 00:11:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548329</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:12:17</td>\n",
       "      <td>114.076065</td>\n",
       "      <td>22.547417</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076035</td>\n",
       "      <td>22.548416</td>\n",
       "      <td>2020-01-23 00:11:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547498</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:12:37</td>\n",
       "      <td>114.076065</td>\n",
       "      <td>22.547417</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076065</td>\n",
       "      <td>22.547417</td>\n",
       "      <td>2020-01-23 00:12:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549250</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:12:57</td>\n",
       "      <td>114.076370</td>\n",
       "      <td>22.546217</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076065</td>\n",
       "      <td>22.547417</td>\n",
       "      <td>2020-01-23 00:12:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550328</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:13:17</td>\n",
       "      <td>114.076469</td>\n",
       "      <td>22.545799</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076370</td>\n",
       "      <td>22.546217</td>\n",
       "      <td>2020-01-23 00:12:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548503</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:13:37</td>\n",
       "      <td>114.076530</td>\n",
       "      <td>22.544884</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076469</td>\n",
       "      <td>22.545799</td>\n",
       "      <td>2020-01-23 00:13:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549249</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:13:57</td>\n",
       "      <td>114.076332</td>\n",
       "      <td>22.543266</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076530</td>\n",
       "      <td>22.544884</td>\n",
       "      <td>2020-01-23 00:13:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550168</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:14:17</td>\n",
       "      <td>114.076332</td>\n",
       "      <td>22.543266</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076332</td>\n",
       "      <td>22.543266</td>\n",
       "      <td>2020-01-23 00:13:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547335</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:14:37</td>\n",
       "      <td>114.077217</td>\n",
       "      <td>22.542982</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.076332</td>\n",
       "      <td>22.543266</td>\n",
       "      <td>2020-01-23 00:14:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547679</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:14:57</td>\n",
       "      <td>114.079865</td>\n",
       "      <td>22.543034</td>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.077217</td>\n",
       "      <td>22.542982</td>\n",
       "      <td>2020-01-23 00:14:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547009</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:15:17</td>\n",
       "      <td>114.081314</td>\n",
       "      <td>22.542950</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.079865</td>\n",
       "      <td>22.543034</td>\n",
       "      <td>2020-01-23 00:14:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549272</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:15:37</td>\n",
       "      <td>114.081818</td>\n",
       "      <td>22.540968</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.081314</td>\n",
       "      <td>22.542950</td>\n",
       "      <td>2020-01-23 00:15:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548189</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:15:57</td>\n",
       "      <td>114.081818</td>\n",
       "      <td>22.540968</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.081818</td>\n",
       "      <td>22.540968</td>\n",
       "      <td>2020-01-23 00:15:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549995</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:16:17</td>\n",
       "      <td>114.081947</td>\n",
       "      <td>22.540649</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.081818</td>\n",
       "      <td>22.540968</td>\n",
       "      <td>2020-01-23 00:15:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548030</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:16:37</td>\n",
       "      <td>114.082985</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.081947</td>\n",
       "      <td>22.540649</td>\n",
       "      <td>2020-01-23 00:16:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548353</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:16:57</td>\n",
       "      <td>114.083946</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.082985</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>2020-01-23 00:16:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549826</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:17:17</td>\n",
       "      <td>114.083946</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.083946</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>2020-01-23 00:16:57</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549827</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:17:38</td>\n",
       "      <td>114.084770</td>\n",
       "      <td>22.540867</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.083946</td>\n",
       "      <td>22.540800</td>\n",
       "      <td>2020-01-23 00:17:17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549620</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-23 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334.0</td>\n",
       "      <td>114.084770</td>\n",
       "      <td>22.540867</td>\n",
       "      <td>2020-01-23 00:17:38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VehicleNum               Stime         Lng        Lat  OpenStatus  \\\n",
       "1550370       22334 2020-01-23 00:01:04  114.111130  22.576750           0   \n",
       "1549617       22334 2020-01-23 00:08:37  114.080551  22.554251           1   \n",
       "1547008       22334 2020-01-23 00:08:57  114.080551  22.554251           1   \n",
       "1548874       22334 2020-01-23 00:09:17  114.081131  22.552799           1   \n",
       "1550335       22334 2020-01-23 00:09:37  114.080681  22.551718           1   \n",
       "1547678       22334 2020-01-23 00:09:57  114.078049  22.551600           1   \n",
       "1549818       22334 2020-01-23 00:10:17  114.076401  22.551550           1   \n",
       "1548021       22334 2020-01-23 00:10:37  114.076401  22.551550           1   \n",
       "1548022       22334 2020-01-23 00:10:57  114.076401  22.551550           1   \n",
       "1548178       22334 2020-01-23 00:11:17  114.076401  22.551550           1   \n",
       "1550695       22334 2020-01-23 00:11:37  114.075966  22.551001           1   \n",
       "1549985       22334 2020-01-23 00:11:57  114.076035  22.548416           1   \n",
       "1548329       22334 2020-01-23 00:12:17  114.076065  22.547417           1   \n",
       "1547498       22334 2020-01-23 00:12:37  114.076065  22.547417           1   \n",
       "1549250       22334 2020-01-23 00:12:57  114.076370  22.546217           1   \n",
       "1550328       22334 2020-01-23 00:13:17  114.076469  22.545799           1   \n",
       "1548503       22334 2020-01-23 00:13:37  114.076530  22.544884           1   \n",
       "1549249       22334 2020-01-23 00:13:57  114.076332  22.543266           1   \n",
       "1550168       22334 2020-01-23 00:14:17  114.076332  22.543266           1   \n",
       "1547335       22334 2020-01-23 00:14:37  114.077217  22.542982           1   \n",
       "1547679       22334 2020-01-23 00:14:57  114.079865  22.543034           1   \n",
       "1547009       22334 2020-01-23 00:15:17  114.081314  22.542950           1   \n",
       "1549272       22334 2020-01-23 00:15:37  114.081818  22.540968           1   \n",
       "1548189       22334 2020-01-23 00:15:57  114.081818  22.540968           1   \n",
       "1549995       22334 2020-01-23 00:16:17  114.081947  22.540649           1   \n",
       "1548030       22334 2020-01-23 00:16:37  114.082985  22.540800           1   \n",
       "1548353       22334 2020-01-23 00:16:57  114.083946  22.540800           1   \n",
       "1549826       22334 2020-01-23 00:17:17  114.083946  22.540800           1   \n",
       "1549827       22334 2020-01-23 00:17:38  114.084770  22.540867           1   \n",
       "1549620       22334 2020-01-23 00:17:58  114.084915  22.540850           1   \n",
       "\n",
       "         Speed  OpenStatus1  VehicleNum1        Lng1       Lat1  \\\n",
       "1550370      0          1.0      22334.0  114.111130  22.576750   \n",
       "1549617      0          1.0      22334.0  114.080551  22.554251   \n",
       "1547008      0          1.0      22334.0  114.080551  22.554251   \n",
       "1548874     15          1.0      22334.0  114.080551  22.554251   \n",
       "1550335     30          1.0      22334.0  114.081131  22.552799   \n",
       "1547678     50          1.0      22334.0  114.080681  22.551718   \n",
       "1549818     30          1.0      22334.0  114.078049  22.551600   \n",
       "1548021      0          1.0      22334.0  114.076401  22.551550   \n",
       "1548022      0          1.0      22334.0  114.076401  22.551550   \n",
       "1548178      0          1.0      22334.0  114.076401  22.551550   \n",
       "1550695     12          1.0      22334.0  114.076401  22.551550   \n",
       "1549985     50          1.0      22334.0  114.075966  22.551001   \n",
       "1548329     20          1.0      22334.0  114.076035  22.548416   \n",
       "1547498      0          1.0      22334.0  114.076065  22.547417   \n",
       "1549250     18          1.0      22334.0  114.076065  22.547417   \n",
       "1550328      9          1.0      22334.0  114.076370  22.546217   \n",
       "1548503     11          1.0      22334.0  114.076469  22.545799   \n",
       "1549249     29          1.0      22334.0  114.076530  22.544884   \n",
       "1550168      0          1.0      22334.0  114.076332  22.543266   \n",
       "1547335     20          1.0      22334.0  114.076332  22.543266   \n",
       "1547679     47          1.0      22334.0  114.077217  22.542982   \n",
       "1547009     31          1.0      22334.0  114.079865  22.543034   \n",
       "1549272     33          1.0      22334.0  114.081314  22.542950   \n",
       "1548189      0          1.0      22334.0  114.081818  22.540968   \n",
       "1549995      5          1.0      22334.0  114.081818  22.540968   \n",
       "1548030     11          1.0      22334.0  114.081947  22.540649   \n",
       "1548353     16          1.0      22334.0  114.082985  22.540800   \n",
       "1549826      0          1.0      22334.0  114.083946  22.540800   \n",
       "1549827     10          1.0      22334.0  114.083946  22.540800   \n",
       "1549620      2          1.0      22334.0  114.084770  22.540867   \n",
       "\n",
       "                     Stime1  StatusChange  orderid  \n",
       "1550370 2020-01-23 00:00:52           1.0      1.0  \n",
       "1549617 2020-01-23 00:08:17           0.0      1.0  \n",
       "1547008 2020-01-23 00:08:37           0.0      1.0  \n",
       "1548874 2020-01-23 00:08:57           0.0      1.0  \n",
       "1550335 2020-01-23 00:09:17           0.0      1.0  \n",
       "1547678 2020-01-23 00:09:37           0.0      1.0  \n",
       "1549818 2020-01-23 00:09:57           0.0      1.0  \n",
       "1548021 2020-01-23 00:10:17           0.0      1.0  \n",
       "1548022 2020-01-23 00:10:37           0.0      1.0  \n",
       "1548178 2020-01-23 00:10:57           0.0      1.0  \n",
       "1550695 2020-01-23 00:11:17           0.0      1.0  \n",
       "1549985 2020-01-23 00:11:37           0.0      1.0  \n",
       "1548329 2020-01-23 00:11:57           0.0      1.0  \n",
       "1547498 2020-01-23 00:12:17           0.0      1.0  \n",
       "1549250 2020-01-23 00:12:37           0.0      1.0  \n",
       "1550328 2020-01-23 00:12:57           0.0      1.0  \n",
       "1548503 2020-01-23 00:13:17           0.0      1.0  \n",
       "1549249 2020-01-23 00:13:37           0.0      1.0  \n",
       "1550168 2020-01-23 00:13:57           0.0      1.0  \n",
       "1547335 2020-01-23 00:14:17           0.0      1.0  \n",
       "1547679 2020-01-23 00:14:37           0.0      1.0  \n",
       "1547009 2020-01-23 00:14:57           0.0      1.0  \n",
       "1549272 2020-01-23 00:15:17           0.0      1.0  \n",
       "1548189 2020-01-23 00:15:37           0.0      1.0  \n",
       "1549995 2020-01-23 00:15:57           0.0      1.0  \n",
       "1548030 2020-01-23 00:16:17           0.0      1.0  \n",
       "1548353 2020-01-23 00:16:37           0.0      1.0  \n",
       "1549826 2020-01-23 00:16:57           0.0      1.0  \n",
       "1549827 2020-01-23 00:17:17           0.0      1.0  \n",
       "1549620 2020-01-23 00:17:38           0.0      1.0  "
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2['OpenStatus1'] = data2['OpenStatus'].shift()\n",
    "data2['VehicleNum1'] = data2['VehicleNum'].shift()\n",
    "\n",
    "data2['Lng1'] = data2['Lng'].shift()\n",
    "data2['Lat1'] = data2['Lat'].shift()\n",
    "data2['Stime1'] = data2['Stime'].shift()\n",
    "\n",
    "data2['StatusChange'] = data2['OpenStatus1']-data2['OpenStatus']\n",
    "\n",
    "\n",
    "data2 = data2[data2['OpenStatus1']==1]\n",
    "data2['orderid'] = data2['StatusChange'].cumsum()\n",
    "data2.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T01:28:34.798069Z",
     "start_time": "2019-10-16T01:28:34.479105Z"
    }
   },
   "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>orderid</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>2020-01-23 00:00:52</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550370</td>\n",
       "      <td>1.0</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>2020-01-23 00:01:04</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549616</td>\n",
       "      <td>1.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549617</td>\n",
       "      <td>1.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:37</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1547008</td>\n",
       "      <td>1.0</td>\n",
       "      <td>114.080551</td>\n",
       "      <td>22.554251</td>\n",
       "      <td>2020-01-23 00:08:57</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         orderid         Lng        Lat               Stime  Speed\n",
       "1548741      0.0  114.111130  22.576750 2020-01-23 00:00:52     13\n",
       "1550370      1.0  114.111130  22.576750 2020-01-23 00:01:04      0\n",
       "1549616      1.0  114.080551  22.554251 2020-01-23 00:08:17      0\n",
       "1549617      1.0  114.080551  22.554251 2020-01-23 00:08:37      0\n",
       "1547008      1.0  114.080551  22.554251 2020-01-23 00:08:57      0"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#表2入手\n",
    "table2 = data2[['orderid','Lng','Lat','Stime','Speed']]\n",
    "table2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 订单里程计算与候车时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T01:34:15.534985Z",
     "start_time": "2019-10-16T01:34:15.526479Z"
    }
   },
   "outputs": [],
   "source": [
    "#表2\n",
    "#计算里程\n",
    "\n",
    "#定义计算路径长度函数\n",
    "\n",
    "from math import pi\n",
    "import numpy as np\n",
    "\n",
    "def getdistance(lon1, lat1, lon2, lat2): # 经度1，纬度1，经度2，纬度2 （十进制度数）输入为DataFrame的列\n",
    "    \"\"\"\n",
    "    Calculate the great circle distance between two points \n",
    "    on the earth (specified in decimal degrees)\n",
    "    \"\"\"\n",
    "    # 将十进制度数转化为弧度\n",
    "    lon1, lat1, lon2, lat2 = map(lambda r:r*pi/180, [lon1, lat1, lon2, lat2])\n",
    "    dlon = lon2 - lon1 \n",
    "    dlat = lat2 - lat1 \n",
    "    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2\n",
    "    c = 2 * np.arcsin(a**0.5) \n",
    "    r = 6371 # 地球平均半径，单位为公里\n",
    "    return c * r * 1000\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T02:47:16.973963Z",
     "start_time": "2019-10-16T02:30:27.711471Z"
    },
    "scrolled": true
   },
   "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>orderid</th>\n",
       "      <th>distance</th>\n",
       "      <th>interval</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6785.602632</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>15698.063306</td>\n",
       "      <td>309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12508.941904</td>\n",
       "      <td>140.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15144.760499</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>15458.541238</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3797.074876</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7497.547127</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4287.367646</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5056.517539</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2732.022522</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderid      distance  interval\n",
       "0      1.0   6785.602632     180.0\n",
       "1      2.0  15698.063306     309.0\n",
       "2      3.0  12508.941904     140.0\n",
       "3      4.0  15144.760499     100.0\n",
       "4      5.0  15458.541238      60.0\n",
       "5      6.0   3797.074876      80.0\n",
       "6      7.0   7497.547127      40.0\n",
       "7      8.0   4287.367646      60.0\n",
       "8      9.0   5056.517539     300.0\n",
       "9     10.0   2732.022522     200.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#生成订单的里程\n",
    "\n",
    "table3 = table2.copy()\n",
    "table3['Lng1'] = table3['Lng'].shift(-1)\n",
    "table3['Lat1'] = table3['Lat'].shift(-1)\n",
    "table3['orderid1'] = table3['orderid'].shift(-1)\n",
    "table3['Stime1'] = table3['Stime'].shift(-1)\n",
    "\n",
    "table3 = table3[(table3['orderid1'] == table3['orderid'])]\n",
    "#计算每个点与下一个点的距离\n",
    "lon1 = table3['Lng']\n",
    "lat1 = table3['Lat']\n",
    "lon2 = table3['Lng1']\n",
    "lat2 = table3['Lat1']\n",
    "table3['distance'] = getdistance(lon1, lat1, lon2, lat2)\n",
    "\n",
    "\n",
    "#计算每个点与下一个点的时间差\n",
    "table3['interval'] = (table3['Stime1']-table3['Stime']).apply(lambda r:r.seconds)\n",
    "\n",
    "#集计得到出租车路径长度\n",
    "orderlenth = table3[['orderid','distance']].groupby('orderid').sum().reset_index()\n",
    "\n",
    "waittime = table3[table3['distance']==0][['orderid','interval']].groupby('orderid').sum()\n",
    "\n",
    "\n",
    "table3 = pd.merge(orderlenth,waittime,on = 'orderid',how='left')\n",
    "table3.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 生成表4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:48:22.936059Z",
     "start_time": "2019-10-16T04:48:22.933876Z"
    }
   },
   "outputs": [],
   "source": [
    "#表4的字段\n",
    "#订单号，车牌号，订单开始时间，订单结束时间，订单上车坐标，订单下车坐标  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:37:14.447234Z",
     "start_time": "2019-10-16T04:37:12.208036Z"
    },
    "scrolled": true
   },
   "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>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550370</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549620</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.54085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550727</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.54085</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         orderid  VehicleNum               Stime         Lng       Lat\n",
       "1548741      0.0       22334 2020-01-20 00:00:52  114.111130  22.57675\n",
       "1548741      0.0       22334 2020-01-20 00:00:52  114.111130  22.57675\n",
       "1550370      1.0       22334 2020-01-20 00:01:04  114.111130  22.57675\n",
       "1549620      1.0       22334 2020-01-20 00:17:58  114.084915  22.54085\n",
       "1550727      2.0       22334 2020-01-20 00:18:16  114.084915  22.54085"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将每一个出行的起点与终点提取出来\n",
    "o = data2.iloc[:1].append(data2[data2['StatusChange']==1])\n",
    "d = data2[(data2['StatusChange']==1).shift(-1).fillna(False)]\n",
    "table4 = o.append(d).sort_values(by = ['orderid','Stime'])[['orderid','VehicleNum','Stime','Lng','Lat']]\n",
    "table4.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:39:40.615572Z",
     "start_time": "2019-10-16T04:39:39.911413Z"
    }
   },
   "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>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>isd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1548741</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550370</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.57675</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1549620</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.54085</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1550727</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.54085</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         orderid  VehicleNum               Stime         Lng       Lat  isd\n",
       "1548741      0.0       22334 2020-01-20 00:00:52  114.111130  22.57675    0\n",
       "1548741      0.0       22334 2020-01-20 00:00:52  114.111130  22.57675    1\n",
       "1550370      1.0       22334 2020-01-20 00:01:04  114.111130  22.57675    0\n",
       "1549620      1.0       22334 2020-01-20 00:17:58  114.084915  22.54085    1\n",
       "1550727      2.0       22334 2020-01-20 00:18:16  114.084915  22.54085    0"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#加一列isd，如果该行为起点，则isd=0，如果该行为终点，则isd=1\n",
    "table4['isd'] = [i%2 for i in range(len(table4))]\n",
    "table4.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:43:01.452007Z",
     "start_time": "2019-10-16T04:43:01.412846Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 把O与D的信息放在同一行\n",
    "table4['Etime'] = table4['Stime'].shift(-1)\n",
    "table4['ELng'] = table4['Lng'].shift(-1)\n",
    "table4['ELat'] = table4['Lat'].shift(-1)\n",
    "table4 = table4[(table4['isd']==0)&(-table4['Etime'].isnull())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:44:49.204783Z",
     "start_time": "2019-10-16T04:44:49.049815Z"
    },
    "scrolled": true
   },
   "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>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>isd</th>\n",
       "      <th>Etime</th>\n",
       "      <th>ELng</th>\n",
       "      <th>ELat</th>\n",
       "      <th>distance</th>\n",
       "      <th>interval</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>6785.602632</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>15698.063306</td>\n",
       "      <td>309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:44:52</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 02:46:52</td>\n",
       "      <td>114.093498</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>12508.941904</td>\n",
       "      <td>140.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 02:47:04</td>\n",
       "      <td>114.093536</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 04:13:57</td>\n",
       "      <td>114.052299</td>\n",
       "      <td>22.604366</td>\n",
       "      <td>15144.760499</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderid  VehicleNum               Stime         Lng        Lat  isd  \\\n",
       "0      0.0       22334 2020-01-20 00:00:52  114.111130  22.576750    0   \n",
       "1      1.0       22334 2020-01-20 00:01:04  114.111130  22.576750    0   \n",
       "2      2.0       22334 2020-01-20 00:18:16  114.084915  22.540850    0   \n",
       "3      3.0       22334 2020-01-20 00:44:52  114.056236  22.633383    0   \n",
       "4      4.0       22334 2020-01-20 02:47:04  114.093536  22.554382    0   \n",
       "\n",
       "                Etime        ELng       ELat      distance  interval  \n",
       "0 2020-01-20 00:00:52  114.111130  22.576750           NaN       NaN  \n",
       "1 2020-01-20 00:17:58  114.084915  22.540850   6785.602632     180.0  \n",
       "2 2020-01-20 00:44:47  114.056236  22.633383  15698.063306     309.0  \n",
       "3 2020-01-20 02:46:52  114.093498  22.554382  12508.941904     140.0  \n",
       "4 2020-01-20 04:13:57  114.052299  22.604366  15144.760499     100.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#连接表3与表4成为表5\n",
    "table5 = pd.merge(table4,table3,on = 'orderid',how = 'left')\n",
    "table5.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:47:21.561936Z",
     "start_time": "2019-10-16T04:47:21.555733Z"
    },
    "scrolled": true
   },
   "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>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>isd</th>\n",
       "      <th>Etime</th>\n",
       "      <th>ELng</th>\n",
       "      <th>ELat</th>\n",
       "      <th>distance</th>\n",
       "      <th>interval</th>\n",
       "      <th>isnight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>6785.602632</td>\n",
       "      <td>180.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>15698.063306</td>\n",
       "      <td>309.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:44:52</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 02:46:52</td>\n",
       "      <td>114.093498</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>12508.941904</td>\n",
       "      <td>140.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 02:47:04</td>\n",
       "      <td>114.093536</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 04:13:57</td>\n",
       "      <td>114.052299</td>\n",
       "      <td>22.604366</td>\n",
       "      <td>15144.760499</td>\n",
       "      <td>100.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderid  VehicleNum               Stime         Lng        Lat  isd  \\\n",
       "0      0.0       22334 2020-01-20 00:00:52  114.111130  22.576750    0   \n",
       "1      1.0       22334 2020-01-20 00:01:04  114.111130  22.576750    0   \n",
       "2      2.0       22334 2020-01-20 00:18:16  114.084915  22.540850    0   \n",
       "3      3.0       22334 2020-01-20 00:44:52  114.056236  22.633383    0   \n",
       "4      4.0       22334 2020-01-20 02:47:04  114.093536  22.554382    0   \n",
       "\n",
       "                Etime        ELng       ELat      distance  interval  isnight  \n",
       "0 2020-01-20 00:00:52  114.111130  22.576750           NaN       NaN     True  \n",
       "1 2020-01-20 00:17:58  114.084915  22.540850   6785.602632     180.0     True  \n",
       "2 2020-01-20 00:44:47  114.056236  22.633383  15698.063306     309.0     True  \n",
       "3 2020-01-20 02:46:52  114.093498  22.554382  12508.941904     140.0     True  \n",
       "4 2020-01-20 04:13:57  114.052299  22.604366  15144.760499     100.0     True  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#每天的23时至次日凌晨6时为夜间\n",
    "table5['isnight'] = (table5['Stime'].apply(lambda r:r.hour)<6)|(table5['Stime'].apply(lambda r:r.hour)>=23)\n",
    "table5.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 收入计算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(一)起步价：首2公里11.00元;  \n",
    "(二)里程价：超过2公里部分，每公里2.40元;  \n",
    "(三)返空费：每天的23时至次日凌晨6时，超过25公里部分，每公里按上述里程价的30%加收返空费：  \n",
    "(四)夜间附加费：夜间起步价16元，每天的23时至次日凌晨6时，按上述起步价和里程价的20%加收夜间附加费;  \n",
    "(五)候时费：每分钟0.80元;  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:52:42.803860Z",
     "start_time": "2019-10-16T04:52:42.796123Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#起步价\n",
    "table5['起步价'] = table5['isnight']*(16-11)+11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:51:21.541911Z",
     "start_time": "2019-10-16T04:51:21.530263Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#里程价\n",
    "table5['里程价'] = ((table5['distance']-2000)>0)*(table5['distance']-2000)*2.4/1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:54:24.276762Z",
     "start_time": "2019-10-16T04:54:24.268688Z"
    }
   },
   "outputs": [],
   "source": [
    "#返空费\n",
    "table5['返空费'] = table5['isnight']*((table5['distance']-25000)>0)*((table5['distance']-25000)*2.4*0.3/1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:57:22.076284Z",
     "start_time": "2019-10-16T04:57:22.063714Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#夜间附加费(里程价)\n",
    "table5['夜间附加费'] = table5['isnight']*((table5['distance']-2000)>0)*(table5['distance']-2000)*2.4*0.2/1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T04:58:19.454443Z",
     "start_time": "2019-10-16T04:58:19.445245Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#候时费\n",
    "table5['候时费'] = table5['interval']/60*0.8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-16T05:00:23.846030Z",
     "start_time": "2019-10-16T05:00:23.749978Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>isd</th>\n",
       "      <th>Etime</th>\n",
       "      <th>ELng</th>\n",
       "      <th>ELat</th>\n",
       "      <th>distance</th>\n",
       "      <th>interval</th>\n",
       "      <th>isnight</th>\n",
       "      <th>起步价</th>\n",
       "      <th>里程价</th>\n",
       "      <th>返空费</th>\n",
       "      <th>夜间附加费</th>\n",
       "      <th>候时费</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:00:52</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>6785.602632</td>\n",
       "      <td>180.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>11.485446</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>2.297089</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>32.182536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>15698.063306</td>\n",
       "      <td>309.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>32.875352</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>6.575070</td>\n",
       "      <td>4.120000</td>\n",
       "      <td>59.570422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:44:52</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 02:46:52</td>\n",
       "      <td>114.093498</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>12508.941904</td>\n",
       "      <td>140.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>25.221461</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>5.044292</td>\n",
       "      <td>1.866667</td>\n",
       "      <td>48.132419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 02:47:04</td>\n",
       "      <td>114.093536</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 04:13:57</td>\n",
       "      <td>114.052299</td>\n",
       "      <td>22.604366</td>\n",
       "      <td>15144.760499</td>\n",
       "      <td>100.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>31.547425</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>6.309485</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>55.190244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderid  VehicleNum               Stime         Lng        Lat  isd  \\\n",
       "0      0.0       22334 2020-01-20 00:00:52  114.111130  22.576750    0   \n",
       "1      1.0       22334 2020-01-20 00:01:04  114.111130  22.576750    0   \n",
       "2      2.0       22334 2020-01-20 00:18:16  114.084915  22.540850    0   \n",
       "3      3.0       22334 2020-01-20 00:44:52  114.056236  22.633383    0   \n",
       "4      4.0       22334 2020-01-20 02:47:04  114.093536  22.554382    0   \n",
       "\n",
       "                Etime        ELng       ELat      distance  interval  isnight  \\\n",
       "0 2020-01-20 00:00:52  114.111130  22.576750           NaN       NaN     True   \n",
       "1 2020-01-20 00:17:58  114.084915  22.540850   6785.602632     180.0     True   \n",
       "2 2020-01-20 00:44:47  114.056236  22.633383  15698.063306     309.0     True   \n",
       "3 2020-01-20 02:46:52  114.093498  22.554382  12508.941904     140.0     True   \n",
       "4 2020-01-20 04:13:57  114.052299  22.604366  15144.760499     100.0     True   \n",
       "\n",
       "   起步价        里程价  返空费     夜间附加费       候时费      price  \n",
       "0   16        NaN  NaN       NaN       NaN        NaN  \n",
       "1   16  11.485446 -0.0  2.297089  2.400000  32.182536  \n",
       "2   16  32.875352 -0.0  6.575070  4.120000  59.570422  \n",
       "3   16  25.221461 -0.0  5.044292  1.866667  48.132419  \n",
       "4   16  31.547425 -0.0  6.309485  1.333333  55.190244  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#最终价格\n",
    "table5['price'] = table5['起步价'] + table5['里程价'] + table5['返空费'] + table5['夜间附加费'] + table5['候时费']\n",
    "table5.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>orderid</th>\n",
       "      <th>VehicleNum</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>isd</th>\n",
       "      <th>Etime</th>\n",
       "      <th>ELng</th>\n",
       "      <th>ELat</th>\n",
       "      <th>distance</th>\n",
       "      <th>interval</th>\n",
       "      <th>isnight</th>\n",
       "      <th>起步价</th>\n",
       "      <th>里程价</th>\n",
       "      <th>返空费</th>\n",
       "      <th>夜间附加费</th>\n",
       "      <th>候时费</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:01:04</td>\n",
       "      <td>114.111130</td>\n",
       "      <td>22.576750</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:17:58</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>6785.602632</td>\n",
       "      <td>180.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>11.485446</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>2.297089</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>32.182536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:18:16</td>\n",
       "      <td>114.084915</td>\n",
       "      <td>22.540850</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 00:44:47</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>15698.063306</td>\n",
       "      <td>309.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>32.875352</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>6.575070</td>\n",
       "      <td>4.120000</td>\n",
       "      <td>59.570422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 00:44:52</td>\n",
       "      <td>114.056236</td>\n",
       "      <td>22.633383</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 02:46:52</td>\n",
       "      <td>114.093498</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>12508.941904</td>\n",
       "      <td>140.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>25.221461</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>5.044292</td>\n",
       "      <td>1.866667</td>\n",
       "      <td>48.132419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 02:47:04</td>\n",
       "      <td>114.093536</td>\n",
       "      <td>22.554382</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 04:13:57</td>\n",
       "      <td>114.052299</td>\n",
       "      <td>22.604366</td>\n",
       "      <td>15144.760499</td>\n",
       "      <td>100.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>31.547425</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>6.309485</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>55.190244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>22334</td>\n",
       "      <td>2020-01-20 04:23:07</td>\n",
       "      <td>114.052216</td>\n",
       "      <td>22.602118</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-20 06:41:19</td>\n",
       "      <td>114.067886</td>\n",
       "      <td>22.521299</td>\n",
       "      <td>15458.541238</td>\n",
       "      <td>60.0</td>\n",
       "      <td>True</td>\n",
       "      <td>16</td>\n",
       "      <td>32.300499</td>\n",
       "      <td>-0.0</td>\n",
       "      <td>6.460100</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>55.560599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderid  VehicleNum               Stime         Lng        Lat  isd  \\\n",
       "1      1.0       22334 2020-01-20 00:01:04  114.111130  22.576750    0   \n",
       "2      2.0       22334 2020-01-20 00:18:16  114.084915  22.540850    0   \n",
       "3      3.0       22334 2020-01-20 00:44:52  114.056236  22.633383    0   \n",
       "4      4.0       22334 2020-01-20 02:47:04  114.093536  22.554382    0   \n",
       "5      5.0       22334 2020-01-20 04:23:07  114.052216  22.602118    0   \n",
       "\n",
       "                Etime        ELng       ELat      distance  interval  isnight  \\\n",
       "1 2020-01-20 00:17:58  114.084915  22.540850   6785.602632     180.0     True   \n",
       "2 2020-01-20 00:44:47  114.056236  22.633383  15698.063306     309.0     True   \n",
       "3 2020-01-20 02:46:52  114.093498  22.554382  12508.941904     140.0     True   \n",
       "4 2020-01-20 04:13:57  114.052299  22.604366  15144.760499     100.0     True   \n",
       "5 2020-01-20 06:41:19  114.067886  22.521299  15458.541238      60.0     True   \n",
       "\n",
       "   起步价        里程价  返空费     夜间附加费       候时费      price  \n",
       "1   16  11.485446 -0.0  2.297089  2.400000  32.182536  \n",
       "2   16  32.875352 -0.0  6.575070  4.120000  59.570422  \n",
       "3   16  25.221461 -0.0  5.044292  1.866667  48.132419  \n",
       "4   16  31.547425 -0.0  6.309485  1.333333  55.190244  \n",
       "5   16  32.300499 -0.0  6.460100  0.800000  55.560599  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#筛选掉没有计算出来价格的数据\n",
    "table5 = table5[-table5['price'].isnull()]\n",
    "table5.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "#保存\n",
    "table5.to_csv(r'data-sample/taxi-price.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": "409.091px",
    "left": "141px",
    "top": "214.322px",
    "width": "279px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}