2017-11-26_FR_clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run utils.py\n",
    "# chunks = pd.read_csv('data/yellow_tripdata_2009-01_train.csv', chunksize=100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/yellow_tripdata_2009-01_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>duration</th>\n",
       "      <th>Trip_Distance</th>\n",
       "      <th>Start_Lon</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>End_Lon</th>\n",
       "      <th>End_Lat</th>\n",
       "      <th>week_0</th>\n",
       "      <th>week_1</th>\n",
       "      <th>week_2</th>\n",
       "      <th>week_3</th>\n",
       "      <th>...</th>\n",
       "      <th>hour_14</th>\n",
       "      <th>hour_15</th>\n",
       "      <th>hour_16</th>\n",
       "      <th>hour_17</th>\n",
       "      <th>hour_18</th>\n",
       "      <th>hour_19</th>\n",
       "      <th>hour_20</th>\n",
       "      <th>hour_21</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>300.0</td>\n",
       "      <td>-0.265036</td>\n",
       "      <td>-0.600812</td>\n",
       "      <td>-0.214163</td>\n",
       "      <td>-0.739884</td>\n",
       "      <td>-0.961962</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>739.0</td>\n",
       "      <td>-0.411026</td>\n",
       "      <td>0.370949</td>\n",
       "      <td>-0.178940</td>\n",
       "      <td>-0.019851</td>\n",
       "      <td>0.218675</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>442.0</td>\n",
       "      <td>-0.411026</td>\n",
       "      <td>0.818415</td>\n",
       "      <td>1.322792</td>\n",
       "      <td>0.578054</td>\n",
       "      <td>0.586712</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>960.0</td>\n",
       "      <td>1.003253</td>\n",
       "      <td>-0.516251</td>\n",
       "      <td>-0.810082</td>\n",
       "      <td>1.366615</td>\n",
       "      <td>-1.343592</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>394.0</td>\n",
       "      <td>-0.274160</td>\n",
       "      <td>-0.171194</td>\n",
       "      <td>-0.580138</td>\n",
       "      <td>-0.547264</td>\n",
       "      <td>0.124720</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration  Trip_Distance  Start_Lon  Start_Lat   End_Lon   End_Lat  week_0  \\\n",
       "0     300.0      -0.265036  -0.600812  -0.214163 -0.739884 -0.961962       0   \n",
       "1     739.0      -0.411026   0.370949  -0.178940 -0.019851  0.218675       0   \n",
       "2     442.0      -0.411026   0.818415   1.322792  0.578054  0.586712       1   \n",
       "3     960.0       1.003253  -0.516251  -0.810082  1.366615 -1.343592       0   \n",
       "4     394.0      -0.274160  -0.171194  -0.580138 -0.547264  0.124720       0   \n",
       "\n",
       "   week_1  week_2  week_3   ...     hour_14  hour_15  hour_16  hour_17  \\\n",
       "0       0       0       0   ...           0        0        0        0   \n",
       "1       1       0       0   ...           1        0        0        0   \n",
       "2       0       0       0   ...           0        0        0        0   \n",
       "3       0       0       0   ...           0        0        0        1   \n",
       "4       0       0       0   ...           0        0        0        0   \n",
       "\n",
       "   hour_18  hour_19  hour_20  hour_21  hour_22  hour_23  \n",
       "0        0        0        0        0        0        0  \n",
       "1        0        0        0        0        0        0  \n",
       "2        1        0        0        0        0        0  \n",
       "3        0        0        0        0        0        0  \n",
       "4        0        0        0        0        0        0  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/franciscorivera/Documents/Francisco/Harvard/07 Junior Fall/CS 281/final-project/utils.py:123: 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",
      "  df['start_x'] = (starts[:, 0] + 84.125113) / 0.005\n",
      "/Users/franciscorivera/Documents/Francisco/Harvard/07 Junior Fall/CS 281/final-project/utils.py:124: 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",
      "  df['start_y'] = (starts[:, 1] + 7.853407) / 0.005\n",
      "/Users/franciscorivera/Documents/Francisco/Harvard/07 Junior Fall/CS 281/final-project/utils.py:127: 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",
      "  df['end_x'] = (ends[:, 0] + 84.125113) / 0.005\n",
      "/Users/franciscorivera/Documents/Francisco/Harvard/07 Junior Fall/CS 281/final-project/utils.py:128: 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",
      "  df['end_y'] = (ends[:, 1] + 7.853407) / 0.005\n"
     ]
    }
   ],
   "source": [
    "df = rotate_manhattan(filter_manhattan(unnormalize(df)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>duration</th>\n",
       "      <th>Trip_Distance</th>\n",
       "      <th>Start_Lon</th>\n",
       "      <th>Start_Lat</th>\n",
       "      <th>End_Lon</th>\n",
       "      <th>End_Lat</th>\n",
       "      <th>week_0</th>\n",
       "      <th>week_1</th>\n",
       "      <th>week_2</th>\n",
       "      <th>week_3</th>\n",
       "      <th>...</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "      <th>start_lon_raw</th>\n",
       "      <th>start_lat_raw</th>\n",
       "      <th>end_lon_raw</th>\n",
       "      <th>end_lat_raw</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>300.0</td>\n",
       "      <td>-0.265036</td>\n",
       "      <td>-0.600812</td>\n",
       "      <td>-0.214163</td>\n",
       "      <td>-0.739884</td>\n",
       "      <td>-0.961962</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-73.992635</td>\n",
       "      <td>40.748362</td>\n",
       "      <td>-73.995585</td>\n",
       "      <td>40.728307</td>\n",
       "      <td>3.065135</td>\n",
       "      <td>10.134181</td>\n",
       "      <td>4.827980</td>\n",
       "      <td>6.483345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>739.0</td>\n",
       "      <td>-0.411026</td>\n",
       "      <td>0.370949</td>\n",
       "      <td>-0.178940</td>\n",
       "      <td>-0.019851</td>\n",
       "      <td>0.218675</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-73.969387</td>\n",
       "      <td>40.749159</td>\n",
       "      <td>-73.976872</td>\n",
       "      <td>40.758792</td>\n",
       "      <td>6.823909</td>\n",
       "      <td>12.875678</td>\n",
       "      <td>4.503622</td>\n",
       "      <td>13.630041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>442.0</td>\n",
       "      <td>-0.411026</td>\n",
       "      <td>0.818415</td>\n",
       "      <td>1.322792</td>\n",
       "      <td>0.578054</td>\n",
       "      <td>0.586712</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-73.958682</td>\n",
       "      <td>40.783139</td>\n",
       "      <td>-73.961333</td>\n",
       "      <td>40.768295</td>\n",
       "      <td>4.781677</td>\n",
       "      <td>19.702008</td>\n",
       "      <td>6.009085</td>\n",
       "      <td>16.947311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>394.0</td>\n",
       "      <td>-0.274160</td>\n",
       "      <td>-0.171194</td>\n",
       "      <td>-0.580138</td>\n",
       "      <td>-0.547264</td>\n",
       "      <td>0.124720</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-73.982357</td>\n",
       "      <td>40.740081</td>\n",
       "      <td>-73.990579</td>\n",
       "      <td>40.756366</td>\n",
       "      <td>5.695986</td>\n",
       "      <td>9.917126</td>\n",
       "      <td>2.507022</td>\n",
       "      <td>11.689866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>547.0</td>\n",
       "      <td>-0.091673</td>\n",
       "      <td>-0.023599</td>\n",
       "      <td>1.061381</td>\n",
       "      <td>0.038058</td>\n",
       "      <td>0.156865</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>-73.978826</td>\n",
       "      <td>40.777224</td>\n",
       "      <td>-73.975367</td>\n",
       "      <td>40.757196</td>\n",
       "      <td>2.111199</td>\n",
       "      <td>16.461751</td>\n",
       "      <td>4.931894</td>\n",
       "      <td>13.534790</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration  Trip_Distance  Start_Lon  Start_Lat   End_Lon   End_Lat  week_0  \\\n",
       "0     300.0      -0.265036  -0.600812  -0.214163 -0.739884 -0.961962       0   \n",
       "1     739.0      -0.411026   0.370949  -0.178940 -0.019851  0.218675       0   \n",
       "2     442.0      -0.411026   0.818415   1.322792  0.578054  0.586712       1   \n",
       "4     394.0      -0.274160  -0.171194  -0.580138 -0.547264  0.124720       0   \n",
       "5     547.0      -0.091673  -0.023599   1.061381  0.038058  0.156865       1   \n",
       "\n",
       "   week_1  week_2  week_3    ...      hour_22  hour_23  start_lon_raw  \\\n",
       "0       0       0       0    ...            0        0     -73.992635   \n",
       "1       1       0       0    ...            0        0     -73.969387   \n",
       "2       0       0       0    ...            0        0     -73.958682   \n",
       "4       0       0       0    ...            0        0     -73.982357   \n",
       "5       0       0       0    ...            1        0     -73.978826   \n",
       "\n",
       "   start_lat_raw  end_lon_raw  end_lat_raw   start_x    start_y     end_x  \\\n",
       "0      40.748362   -73.995585    40.728307  3.065135  10.134181  4.827980   \n",
       "1      40.749159   -73.976872    40.758792  6.823909  12.875678  4.503622   \n",
       "2      40.783139   -73.961333    40.768295  4.781677  19.702008  6.009085   \n",
       "4      40.740081   -73.990579    40.756366  5.695986   9.917126  2.507022   \n",
       "5      40.777224   -73.975367    40.757196  2.111199  16.461751  4.931894   \n",
       "\n",
       "       end_y  \n",
       "0   6.483345  \n",
       "1  13.630041  \n",
       "2  16.947311  \n",
       "4  11.689866  \n",
       "5  13.534790  \n",
       "\n",
       "[5 rows x 45 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "03348b4e78eb4172ae12547bb4664798",
       "version_major": 2,
       "version_minor": 0
      },
      "text/html": [
       "<p>Failed to display Jupyter Widget of type <code>HBox</code>.</p>\n",
       "<p>\n",
       "  If you're reading this message in the Jupyter Notebook or JupyterLab Notebook, it may mean\n",
       "  that the widgets JavaScript is still loading. If this message persists, it\n",
       "  likely means that the widgets JavaScript library is either not installed or\n",
       "  not enabled. See the <a href=\"https://ipywidgets.readthedocs.io/en/stable/user_install.html\">Jupyter\n",
       "  Widgets Documentation</a> for setup instructions.\n",
       "</p>\n",
       "<p>\n",
       "  If you're reading this message in another frontend (for example, a static\n",
       "  rendering on GitHub or <a href=\"https://nbviewer.jupyter.org/\">NBViewer</a>),\n",
       "  it may mean that your frontend doesn't currently support widgets.\n",
       "</p>\n"
      ],
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=24), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "M, N = 40, 140\n",
    "\n",
    "for hour in tqdm(range(24)):\n",
    "    for day in range(7):\n",
    "        filename = 'data/split_test/JC_week{}_hour{}_test_40x140.csv'.format(day, hour)\n",
    "        df['sx'] = np.clip(np.round(M/10 * df['start_x'] ), 0, M-1) \n",
    "        df['sy'] = np.clip(np.round(M/10 * df['start_y'] ), 0, N-1) \n",
    "        df['tx'] = np.clip(np.round(M/10 * df['end_x'] ), 0, M-1) \n",
    "        df['ty'] = np.clip(np.round(M/10 * df['end_y'] ), 0, N-1) \n",
    "        df.loc[(df['duration'] < 3600 * 2) & (df['duration'] > 120) &\n",
    "               (df['week_{}'.format(day)] == 1) & (df['hour_{}'.format(hour)] == 1),\n",
    "               ['duration','sx','sy','tx','ty']].to_csv(filename, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}