di-tech_data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0. 测试上传数据\n",
    "- 文件名: test_upload.csv\n",
    "- 假设结果正态分布，用random数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2838.000000</td>\n",
       "      <td>2838.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>33.500000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>19.053729</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>17.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>33.500000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>66.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0       2\n",
       "count  2838.000000  2838.0\n",
       "mean     33.500000     0.0\n",
       "std      19.053729     0.0\n",
       "min       1.000000     0.0\n",
       "25%      17.000000     0.0\n",
       "50%      33.500000     0.0\n",
       "75%      50.000000     0.0\n",
       "max      66.000000     0.0"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('season_1/test_0.csv',header=None);test.describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2838.000000</td>\n",
       "      <td>2838.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>33.500000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>19.053729</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>17.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>33.500000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>66.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0       2\n",
       "count  2838.000000  2838.0\n",
       "mean     33.500000     0.0\n",
       "std      19.053729     0.0\n",
       "min       1.000000     0.0\n",
       "25%      17.000000     0.0\n",
       "50%      33.500000     0.0\n",
       "75%      50.000000     0.0\n",
       "max      66.000000     0.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import random\n",
    "test_list = []\n",
    "for i in range(len(test)):\n",
    "    test_list.append(abs(random.gauss(0,20)))\n",
    "random_array = np.array(test_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "numpy.float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(random_array[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "random_forcast = pd.DataFrame(random_array)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test_result = pd.concat([test[0],test[1],random_forcast[0]],axis=1);test_result.columns=[0,1,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_result.to_csv('test_upload.csv',header=False,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据获取和清理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def output(day):\n",
    "    order = pd.read_csv('season_1/training_data/order_data/order_data_{}'.format(day),delimiter='\\t',header=None)\n",
    "    order.columns = [\"order_id\", \"driver_id\",\"passenger_id\",\"start_district_hash\", \"dest_district_hash\", \"Price\",\"Time\"]\n",
    "    order[\"time\"] = pd.to_datetime(order.Time,unit='s')\n",
    "    order[\"time_id\"] = order.time.apply(lambda x: x.hour*6 + int(x.minute/10)+1)\n",
    "    # 地点信息\n",
    "    district_data = pd.read_csv('season_1/training_data/cluster_map/cluster_map', delimiter='\\t',header=None)\n",
    "    district_data.columns = [\"start_district_hash\",\"district_id\"]\n",
    "    # 合并地区信息\n",
    "    order_m = pd.merge(order,district_data,on=\"start_district_hash\")\n",
    "    # 简化表格，并计算gap\n",
    "    std_df = order_m[[\"driver_id\",\"time_id\",\"district_id\"]]\n",
    "    # 整理 driver_id 信息，将 非NaN 都变为0，将NaN变为1\n",
    "    mask = std_df.isnull()\n",
    "    # convert the NaN data (gap) to 1 \n",
    "    std_df['gap'] = np.where(mask.driver_id,1,0)\n",
    "    # rid the hash data\n",
    "    std_df =std_df.drop(\"driver_id\",axis=1)\n",
    "    # \n",
    "    std_output = std_df.groupby([\"district_id\",\"time_id\"]).sum()\n",
    "    \n",
    "    std_output.to_csv('order_data_{}_gap.csv'.format(day),index=True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/whale/.pyenv/versions/anaconda3-4.0.0/lib/python3.5/site-packages/ipykernel/__main__.py:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "for i in range(1,22):\n",
    "    if i < 10:\n",
    "        output(\"2016-01-0{}\".format(i))\n",
    "    else:\n",
    "        output(\"2016-01-{}\".format(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'gap'"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 尝试读取\n",
    "df_test_output = pd.read_csv('./order_data_2016-01-{}_gap.csv'.format(\"15\"))\n",
    "df_test_output.iloc[0].argmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 尝试订单信息获得\n",
    "- 具体步骤分解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "order_1 = pd.read_csv('season_1/training_data/order_data/order_data_2016-01-01', delimiter='\\t',header=None)\n",
    "order_1.columns = [\"order_id\", \"driver_id\",\"passenger_id\",\"start_district_hash\", \"dest_district_hash\", \"Price\",\"Time\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 501287 entries, 0 to 501286\n",
      "Data columns (total 7 columns):\n",
      "order_id               501287 non-null object\n",
      "driver_id              325577 non-null object\n",
      "passenger_id           501287 non-null object\n",
      "start_district_hash    501287 non-null object\n",
      "dest_district_hash     501287 non-null object\n",
      "Price                  501287 non-null float64\n",
      "Time                   501287 non-null object\n",
      "dtypes: float64(1), object(6)\n",
      "memory usage: 26.8+ MB\n"
     ]
    }
   ],
   "source": [
    "order_1.info()\n",
    "# 可见 总体订单gap 为 (501287- 325577) / 501287 = 0.3505"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 订单信息数据整理\n",
    "- 时间切片，获得 time_id 对应 144 个时区\n",
    "- 地点切片，获得 district_id 对应的 66 个地区\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>driver_id</th>\n",
       "      <th>passenger_id</th>\n",
       "      <th>start_district_hash</th>\n",
       "      <th>dest_district_hash</th>\n",
       "      <th>Price</th>\n",
       "      <th>Time</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>97ebd0c6680f7c0535dbfdead6e51b4b</td>\n",
       "      <td>dd65fa250fca2833a3a8c16d2cf0457c</td>\n",
       "      <td>ed180d7daf639d936f1aeae4f7fb482f</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>3e12208dd0be281c92a6ab57d9a6fb32</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01 13:37:23</td>\n",
       "      <td>2016-01-01 13:37:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92c3ac9251cc9b5aab90b114a1e363be</td>\n",
       "      <td>c077e0297639edcb1df6189e8cda2c3d</td>\n",
       "      <td>191a180f0a262aff3267775c4fac8972</td>\n",
       "      <td>82cc4851f9e4faa4e54309f8bb73fd7c</td>\n",
       "      <td>b05379ac3f9b7d99370d443cfd5dcc28</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2016-01-01 09:47:54</td>\n",
       "      <td>2016-01-01 09:47:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>abeefc3e2aec952468e2fd42a1649640</td>\n",
       "      <td>86dbc1b68de435957c61b5a523854b69</td>\n",
       "      <td>7029e813bb3de8cc73a8615e2785070c</td>\n",
       "      <td>fff4e8465d1e12621bc361276b6217cf</td>\n",
       "      <td>fff4e8465d1e12621bc361276b6217cf</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2016-01-01 18:24:02</td>\n",
       "      <td>2016-01-01 18:24:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cb31d0be64cda3cc66b46617bf49a05c</td>\n",
       "      <td>4fadfa6eeaa694742de036dddf02b0c4</td>\n",
       "      <td>21dc133ac68e4c07803d1c2f48988a83</td>\n",
       "      <td>4b7f6f4e2bf237b6cc58f57142bea5c0</td>\n",
       "      <td>4b7f6f4e2bf237b6cc58f57142bea5c0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2016-01-01 22:13:27</td>\n",
       "      <td>2016-01-01 22:13:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>139d492189ae5a933122c098f63252b3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26963cc76da2d8450d8f23fc357db987</td>\n",
       "      <td>fc34648599753c9e74ab238e9a4a07ad</td>\n",
       "      <td>87285a66236346350541b8815c5fae94</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2016-01-01 17:00:06</td>\n",
       "      <td>2016-01-01 17:00:06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                         driver_id  \\\n",
       "0  97ebd0c6680f7c0535dbfdead6e51b4b  dd65fa250fca2833a3a8c16d2cf0457c   \n",
       "1  92c3ac9251cc9b5aab90b114a1e363be  c077e0297639edcb1df6189e8cda2c3d   \n",
       "2  abeefc3e2aec952468e2fd42a1649640  86dbc1b68de435957c61b5a523854b69   \n",
       "3  cb31d0be64cda3cc66b46617bf49a05c  4fadfa6eeaa694742de036dddf02b0c4   \n",
       "4  139d492189ae5a933122c098f63252b3                               NaN   \n",
       "\n",
       "                       passenger_id               start_district_hash  \\\n",
       "0  ed180d7daf639d936f1aeae4f7fb482f  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "1  191a180f0a262aff3267775c4fac8972  82cc4851f9e4faa4e54309f8bb73fd7c   \n",
       "2  7029e813bb3de8cc73a8615e2785070c  fff4e8465d1e12621bc361276b6217cf   \n",
       "3  21dc133ac68e4c07803d1c2f48988a83  4b7f6f4e2bf237b6cc58f57142bea5c0   \n",
       "4  26963cc76da2d8450d8f23fc357db987  fc34648599753c9e74ab238e9a4a07ad   \n",
       "\n",
       "                 dest_district_hash  Price                 Time  \\\n",
       "0  3e12208dd0be281c92a6ab57d9a6fb32   24.0  2016-01-01 13:37:23   \n",
       "1  b05379ac3f9b7d99370d443cfd5dcc28    2.0  2016-01-01 09:47:54   \n",
       "2  fff4e8465d1e12621bc361276b6217cf    9.0  2016-01-01 18:24:02   \n",
       "3  4b7f6f4e2bf237b6cc58f57142bea5c0   11.0  2016-01-01 22:13:27   \n",
       "4  87285a66236346350541b8815c5fae94    4.0  2016-01-01 17:00:06   \n",
       "\n",
       "                 time  \n",
       "0 2016-01-01 13:37:23  \n",
       "1 2016-01-01 09:47:54  \n",
       "2 2016-01-01 18:24:02  \n",
       "3 2016-01-01 22:13:27  \n",
       "4 2016-01-01 17:00:06  "
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如何把时间做切片？\n",
    "order_1[\"time\"] = pd.to_datetime(order_1.Time,unit='s');order_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "order_1[\"time_id\"] = order_1.time.apply(lambda x: x.hour*6 + int(x.minute/10)+1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_district_hash</th>\n",
       "      <th>district_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90c5a34f06ac86aee0fd70e2adce7d8a</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>f2c8c4bb99e6377d21de71275afd6cd2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58c7a4888306d8ff3a641d1c0feccbe3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b26a240205c852804ff8758628c0a86a</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4b9e4cf2fbdc8281b8a1f9f12b80ce4d</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                start_district_hash  district_id\n",
       "0  90c5a34f06ac86aee0fd70e2adce7d8a            1\n",
       "1  f2c8c4bb99e6377d21de71275afd6cd2            2\n",
       "2  58c7a4888306d8ff3a641d1c0feccbe3            3\n",
       "3  b26a240205c852804ff8758628c0a86a            4\n",
       "4  4b9e4cf2fbdc8281b8a1f9f12b80ce4d            5"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 地点信息\n",
    "district_data = pd.read_csv('season_1/training_data/cluster_map/cluster_map', delimiter='\\t',header=None)\n",
    "district_data.columns = [\"start_district_hash\",\"district_id\"];district_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>driver_id</th>\n",
       "      <th>passenger_id</th>\n",
       "      <th>start_district_hash</th>\n",
       "      <th>dest_district_hash</th>\n",
       "      <th>Price</th>\n",
       "      <th>Time</th>\n",
       "      <th>time</th>\n",
       "      <th>time_id</th>\n",
       "      <th>district_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>97ebd0c6680f7c0535dbfdead6e51b4b</td>\n",
       "      <td>dd65fa250fca2833a3a8c16d2cf0457c</td>\n",
       "      <td>ed180d7daf639d936f1aeae4f7fb482f</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>3e12208dd0be281c92a6ab57d9a6fb32</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01 13:37:23</td>\n",
       "      <td>2016-01-01 13:37:23</td>\n",
       "      <td>82</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6fcae38baf2eb52e17273df41bf6fc6f</td>\n",
       "      <td>c586e492c4587c3aa15557a8eb41806d</td>\n",
       "      <td>c706cc35b05d84b224dec9d429c11de0</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2016-01-01 14:28:07</td>\n",
       "      <td>2016-01-01 14:28:07</td>\n",
       "      <td>87</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1b3b4b633578f88325973e084450af6f</td>\n",
       "      <td>7c630c98ba6679b30014807acc856d28</td>\n",
       "      <td>2a6c22435b8a3379a65176f9613458e4</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-01-01 19:39:54</td>\n",
       "      <td>2016-01-01 19:39:54</td>\n",
       "      <td>118</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bf9fcd57aa98208c11de945513e26cce</td>\n",
       "      <td>b29185649bd8d9c0fc04e44828b2a589</td>\n",
       "      <td>365e9ad6c8160b11ede6119b405dfbdd</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2016-01-01 20:51:46</td>\n",
       "      <td>2016-01-01 20:51:46</td>\n",
       "      <td>126</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9a13aebfdf99847fd502815271e45a01</td>\n",
       "      <td>be681893fd4ced097c0f50848d6dc013</td>\n",
       "      <td>637bb09709d9b0482244da8614fc64dd</td>\n",
       "      <td>4725c39a5e5f4c188d382da3910b3f3f</td>\n",
       "      <td>91690261186ae5bee8f83808ea1e4a01</td>\n",
       "      <td>43.3</td>\n",
       "      <td>2016-01-01 19:51:58</td>\n",
       "      <td>2016-01-01 19:51:58</td>\n",
       "      <td>120</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                         driver_id  \\\n",
       "0  97ebd0c6680f7c0535dbfdead6e51b4b  dd65fa250fca2833a3a8c16d2cf0457c   \n",
       "1  6fcae38baf2eb52e17273df41bf6fc6f  c586e492c4587c3aa15557a8eb41806d   \n",
       "2  1b3b4b633578f88325973e084450af6f  7c630c98ba6679b30014807acc856d28   \n",
       "3  bf9fcd57aa98208c11de945513e26cce  b29185649bd8d9c0fc04e44828b2a589   \n",
       "4  9a13aebfdf99847fd502815271e45a01  be681893fd4ced097c0f50848d6dc013   \n",
       "\n",
       "                       passenger_id               start_district_hash  \\\n",
       "0  ed180d7daf639d936f1aeae4f7fb482f  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "1  c706cc35b05d84b224dec9d429c11de0  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "2  2a6c22435b8a3379a65176f9613458e4  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "3  365e9ad6c8160b11ede6119b405dfbdd  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "4  637bb09709d9b0482244da8614fc64dd  4725c39a5e5f4c188d382da3910b3f3f   \n",
       "\n",
       "                 dest_district_hash  Price                 Time  \\\n",
       "0  3e12208dd0be281c92a6ab57d9a6fb32   24.0  2016-01-01 13:37:23   \n",
       "1  4725c39a5e5f4c188d382da3910b3f3f    5.0  2016-01-01 14:28:07   \n",
       "2  4725c39a5e5f4c188d382da3910b3f3f    7.0  2016-01-01 19:39:54   \n",
       "3  4725c39a5e5f4c188d382da3910b3f3f    9.0  2016-01-01 20:51:46   \n",
       "4  91690261186ae5bee8f83808ea1e4a01   43.3  2016-01-01 19:51:58   \n",
       "\n",
       "                 time  time_id  district_id  \n",
       "0 2016-01-01 13:37:23       82           23  \n",
       "1 2016-01-01 14:28:07       87           23  \n",
       "2 2016-01-01 19:39:54      118           23  \n",
       "3 2016-01-01 20:51:46      126           23  \n",
       "4 2016-01-01 19:51:58      120           23  "
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 合并地理信息入数据\n",
    "order_1_m = pd.merge(order_1,district_data,on=\"start_district_hash\");order_1_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/whale/.pyenv/versions/anaconda3-4.0.0/lib/python3.5/site-packages/ipykernel/__main__.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>driver_id</th>\n",
       "      <th>time_id</th>\n",
       "      <th>district_id</th>\n",
       "      <th>gap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dd65fa250fca2833a3a8c16d2cf0457c</td>\n",
       "      <td>82</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c586e492c4587c3aa15557a8eb41806d</td>\n",
       "      <td>87</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7c630c98ba6679b30014807acc856d28</td>\n",
       "      <td>118</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b29185649bd8d9c0fc04e44828b2a589</td>\n",
       "      <td>126</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>be681893fd4ced097c0f50848d6dc013</td>\n",
       "      <td>120</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          driver_id  time_id  district_id  gap\n",
       "0  dd65fa250fca2833a3a8c16d2cf0457c       82           23    0\n",
       "1  c586e492c4587c3aa15557a8eb41806d       87           23    0\n",
       "2  7c630c98ba6679b30014807acc856d28      118           23    0\n",
       "3  b29185649bd8d9c0fc04e44828b2a589      126           23    0\n",
       "4  be681893fd4ced097c0f50848d6dc013      120           23    0"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 简化表格，并计算gap\n",
    "std_df = order_1_m[[\"driver_id\",\"time_id\",\"district_id\"]]\n",
    "# 整理 driver_id 信息，将 非NaN 都变为0，将NaN变为1\n",
    "mask = std_df.isnull();mask\n",
    "# convert the NaN data (gap) to 1 \n",
    "std_df['gap'] = np.where(mask.driver_id,1,0);std_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_id</th>\n",
       "      <th>district_id</th>\n",
       "      <th>gap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>82</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>87</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>118</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>126</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>120</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   time_id  district_id  gap\n",
       "0       82           23    0\n",
       "1       87           23    0\n",
       "2      118           23    0\n",
       "3      126           23    0\n",
       "4      120           23    0"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "std_df =std_df.drop(\"driver_id\",axis=1);std_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "group0 = std_df.groupby([\"district_id\",\"time_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "std_output = group0.sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 获得order_1 的预测值了~\n",
    "std_output.to_csv('order_1_gap.csv',index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 区域的地域属性数据获取"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 基准预测"
   ]
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}