01_load_and_validate_raw_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2023-03-08 09:54:19--  https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet\n",
      "Resolving d37ci6vzurychx.cloudfront.net (d37ci6vzurychx.cloudfront.net)... 108.138.245.96, 108.138.245.225, 108.138.245.16, ...\n",
      "Connecting to d37ci6vzurychx.cloudfront.net (d37ci6vzurychx.cloudfront.net)|108.138.245.96|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 38139949 (36M) [application/x-www-form-urlencoded]\n",
      "Saving to: ‘data/raw/rides_2022_01.parquet’\n",
      "\n",
      "data/raw/rides_2022 100%[===================>]  36.37M  2.36MB/s    in 16s     \n",
      "\n",
      "2023-03-08 09:54:36 (2.35 MB/s) - ‘data/raw/rides_2022_01.parquet’ saved [38139949/38139949]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!wget https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet -O data/raw/rides_2022_01.parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-01 00:35:40</td>\n",
       "      <td>2022-01-01 00:53:29</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>142</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>14.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>21.95</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-01 00:33:43</td>\n",
       "      <td>2022-01-01 00:42:07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:53:21</td>\n",
       "      <td>2022-01-01 01:02:19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.97</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "      <td>1</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.76</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>10.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:25:21</td>\n",
       "      <td>2022-01-01 00:35:23</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>114</td>\n",
       "      <td>68</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>11.80</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:36:48</td>\n",
       "      <td>2022-01-01 01:14:20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>68</td>\n",
       "      <td>163</td>\n",
       "      <td>1</td>\n",
       "      <td>23.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>30.30</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-01 00:40:15</td>\n",
       "      <td>2022-01-01 01:09:48</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>161</td>\n",
       "      <td>1</td>\n",
       "      <td>33.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>13.00</td>\n",
       "      <td>6.55</td>\n",
       "      <td>0.3</td>\n",
       "      <td>56.35</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:20:50</td>\n",
       "      <td>2022-01-01 00:34:58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>233</td>\n",
       "      <td>87</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>26.00</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:13:04</td>\n",
       "      <td>2022-01-01 00:22:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.02</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>238</td>\n",
       "      <td>152</td>\n",
       "      <td>2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.80</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:30:02</td>\n",
       "      <td>2022-01-01 00:44:49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.71</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>166</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>18.05</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-01-01 00:48:52</td>\n",
       "      <td>2022-01-01 00:53:28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.78</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>141</td>\n",
       "      <td>2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.80</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1  2022-01-01 00:35:40   2022-01-01 00:53:29              2.0   \n",
       "1         1  2022-01-01 00:33:43   2022-01-01 00:42:07              1.0   \n",
       "2         2  2022-01-01 00:53:21   2022-01-01 01:02:19              1.0   \n",
       "3         2  2022-01-01 00:25:21   2022-01-01 00:35:23              1.0   \n",
       "4         2  2022-01-01 00:36:48   2022-01-01 01:14:20              1.0   \n",
       "5         1  2022-01-01 00:40:15   2022-01-01 01:09:48              1.0   \n",
       "6         2  2022-01-01 00:20:50   2022-01-01 00:34:58              1.0   \n",
       "7         2  2022-01-01 00:13:04   2022-01-01 00:22:45              1.0   \n",
       "8         2  2022-01-01 00:30:02   2022-01-01 00:44:49              1.0   \n",
       "9         2  2022-01-01 00:48:52   2022-01-01 00:53:28              1.0   \n",
       "\n",
       "   trip_distance  RatecodeID store_and_fwd_flag  PULocationID  DOLocationID  \\\n",
       "0           3.80         1.0                  N           142           236   \n",
       "1           2.10         1.0                  N           236            42   \n",
       "2           0.97         1.0                  N           166           166   \n",
       "3           1.09         1.0                  N           114            68   \n",
       "4           4.30         1.0                  N            68           163   \n",
       "5          10.30         1.0                  N           138           161   \n",
       "6           5.07         1.0                  N           233            87   \n",
       "7           2.02         1.0                  N           238           152   \n",
       "8           2.71         1.0                  N           166           236   \n",
       "9           0.78         1.0                  N           236           141   \n",
       "\n",
       "   payment_type  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0             1         14.5    3.0      0.5        3.65          0.00   \n",
       "1             1          8.0    0.5      0.5        4.00          0.00   \n",
       "2             1          7.5    0.5      0.5        1.76          0.00   \n",
       "3             2          8.0    0.5      0.5        0.00          0.00   \n",
       "4             1         23.5    0.5      0.5        3.00          0.00   \n",
       "5             1         33.0    3.0      0.5       13.00          6.55   \n",
       "6             1         17.0    0.5      0.5        5.20          0.00   \n",
       "7             2          9.0    0.5      0.5        0.00          0.00   \n",
       "8             1         12.0    0.5      0.5        2.25          0.00   \n",
       "9             2          5.0    0.5      0.5        0.00          0.00   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge  airport_fee  \n",
       "0                    0.3         21.95                   2.5          0.0  \n",
       "1                    0.3         13.30                   0.0          0.0  \n",
       "2                    0.3         10.56                   0.0          0.0  \n",
       "3                    0.3         11.80                   2.5          0.0  \n",
       "4                    0.3         30.30                   2.5          0.0  \n",
       "5                    0.3         56.35                   2.5          0.0  \n",
       "6                    0.3         26.00                   2.5          0.0  \n",
       "7                    0.3         12.80                   2.5          0.0  \n",
       "8                    0.3         18.05                   2.5          0.0  \n",
       "9                    0.3          8.80                   2.5          0.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "rides = pd.read_parquet('data/raw/rides_2022_01.parquet')\n",
    "\n",
    "rides.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>pickup_datetime</th>\n",
       "      <th>pickup_location_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01 00:35:40</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-01 00:33:43</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-01 00:53:21</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-01 00:25:21</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-01 00:36:48</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-01-01 00:40:15</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-01-01 00:20:50</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-01-01 00:13:04</td>\n",
       "      <td>238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-01-01 00:30:02</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-01-01 00:48:52</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      pickup_datetime  pickup_location_id\n",
       "0 2022-01-01 00:35:40                 142\n",
       "1 2022-01-01 00:33:43                 236\n",
       "2 2022-01-01 00:53:21                 166\n",
       "3 2022-01-01 00:25:21                 114\n",
       "4 2022-01-01 00:36:48                  68\n",
       "5 2022-01-01 00:40:15                 138\n",
       "6 2022-01-01 00:20:50                 233\n",
       "7 2022-01-01 00:13:04                 238\n",
       "8 2022-01-01 00:30:02                 166\n",
       "9 2022-01-01 00:48:52                 236"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides = rides[['tpep_pickup_datetime', 'PULocationID']]\n",
    "\n",
    "rides.rename(columns={\n",
    "    'tpep_pickup_datetime': 'pickup_datetime',\n",
    "    'PULocationID': 'pickup_location_id',\n",
    "}, inplace=True)\n",
    "\n",
    "rides.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_3476/3389344848.py:1: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
      "  rides['pickup_datetime'].describe()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count                 2463931\n",
       "unique                1423522\n",
       "top       2022-01-26 07:57:00\n",
       "freq                       12\n",
       "first     2008-12-31 22:23:09\n",
       "last      2022-05-18 20:41:57\n",
       "Name: pickup_datetime, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides['pickup_datetime'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_3476/124399977.py:3: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
      "  rides['pickup_datetime'].describe()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count                 2463879\n",
       "unique                1423471\n",
       "top       2022-01-26 07:57:00\n",
       "freq                       12\n",
       "first     2022-01-01 00:00:08\n",
       "last      2022-01-31 23:59:58\n",
       "Name: pickup_datetime, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rides = rides[rides.pickup_datetime >= '2022-01-01']\n",
    "rides = rides[rides.pickup_datetime < '2022-02-01']\n",
    "rides['pickup_datetime'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "rides.to_parquet('data/transformed/validated_rides_2022_01.parquet')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.9.16"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "b98d97558a062384a76b0309256306c9ce5dd4e2074fe66c33532239207fc923"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}