11-1.ipynb · ni1o1/pygeo-tutorial

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

克隆/下载

11-1.ipynb 80.21 KB

{
 "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
}

一键复制编辑原始数据按行查看历史

提交于 2021-03-21 20:02 . fix bug

Python

https://gitee.com/ni1o1/pygeo-tutorial.git

git@gitee.com:ni1o1/pygeo-tutorial.git

ni1o1

pygeo-tutorial

master

ni1o1 / pygeo-tutorial

简介

发行版

贡献者

近期动态

ni1o1 / pygeo-tutorial .gitee-modal { width: 500px !important; }

简介

发行版

开源评估指数源自 OSS-Compass 评估体系，评估体系围绕以下三个维度对项目展开评估：

贡献者

近期动态

搜索帮助

ni1o1 / pygeo-tutorial