andres.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pyarrow.parquet as pq\n",
    "import pyarrow as pa\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_yellow_tripdata = pd.read_parquet('sources\\yellow_tripdata_2024-01.parquet')\n",
    "df_green_tripdata = pd.read_parquet('sources\\green_tripdata_2024-01.parquet')\n",
    "df_fhv_tripdata = pd.read_parquet('sources\\\\fhv_tripdata_2024-01.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>2</td>\n",
       "      <td>2024-01-01 00:57:55</td>\n",
       "      <td>2024-01-01 01:17:43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.72</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>186</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>17.70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.70</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>2024-01-01 00:03:00</td>\n",
       "      <td>2024-01-01 00:09:36</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>140</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.75</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.75</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:17:06</td>\n",
       "      <td>2024-01-01 00:35:01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.70</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>23.30</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.30</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:36:38</td>\n",
       "      <td>2024-01-01 00:44:56</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>79</td>\n",
       "      <td>211</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:46:51</td>\n",
       "      <td>2024-01-01 00:52:57</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>211</td>\n",
       "      <td>148</td>\n",
       "      <td>1</td>\n",
       "      <td>7.90</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.10</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964619</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:45:59</td>\n",
       "      <td>2024-01-31 23:54:36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>107</td>\n",
       "      <td>263</td>\n",
       "      <td>0</td>\n",
       "      <td>15.77</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964620</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-31 23:13:07</td>\n",
       "      <td>2024-01-31 23:27:52</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>114</td>\n",
       "      <td>236</td>\n",
       "      <td>0</td>\n",
       "      <td>18.40</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.34</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964621</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:19:00</td>\n",
       "      <td>2024-01-31 23:38:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>211</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>19.97</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.97</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964622</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:07:23</td>\n",
       "      <td>2024-01-31 23:25:14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>107</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>23.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.58</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964623</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-31 23:58:25</td>\n",
       "      <td>2024-02-01 00:13:30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>138</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>32.40</td>\n",
       "      <td>7.75</td>\n",
       "      <td>0.5</td>\n",
       "      <td>7.29</td>\n",
       "      <td>6.94</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2964624 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0               2  2024-01-01 00:57:55   2024-01-01 01:17:43              1.0   \n",
       "1               1  2024-01-01 00:03:00   2024-01-01 00:09:36              1.0   \n",
       "2               1  2024-01-01 00:17:06   2024-01-01 00:35:01              1.0   \n",
       "3               1  2024-01-01 00:36:38   2024-01-01 00:44:56              1.0   \n",
       "4               1  2024-01-01 00:46:51   2024-01-01 00:52:57              1.0   \n",
       "...           ...                  ...                   ...              ...   \n",
       "2964619         2  2024-01-31 23:45:59   2024-01-31 23:54:36              NaN   \n",
       "2964620         1  2024-01-31 23:13:07   2024-01-31 23:27:52              NaN   \n",
       "2964621         2  2024-01-31 23:19:00   2024-01-31 23:38:00              NaN   \n",
       "2964622         2  2024-01-31 23:07:23   2024-01-31 23:25:14              NaN   \n",
       "2964623         1  2024-01-31 23:58:25   2024-02-01 00:13:30              NaN   \n",
       "\n",
       "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "0                 1.72         1.0                  N           186   \n",
       "1                 1.80         1.0                  N           140   \n",
       "2                 4.70         1.0                  N           236   \n",
       "3                 1.40         1.0                  N            79   \n",
       "4                 0.80         1.0                  N           211   \n",
       "...                ...         ...                ...           ...   \n",
       "2964619           3.18         NaN               None           107   \n",
       "2964620           4.00         NaN               None           114   \n",
       "2964621           3.33         NaN               None           211   \n",
       "2964622           3.06         NaN               None           107   \n",
       "2964623           8.10         NaN               None           138   \n",
       "\n",
       "         DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "0                  79             2        17.70   1.00      0.5        0.00   \n",
       "1                 236             1        10.00   3.50      0.5        3.75   \n",
       "2                  79             1        23.30   3.50      0.5        3.00   \n",
       "3                 211             1        10.00   3.50      0.5        2.00   \n",
       "4                 148             1         7.90   3.50      0.5        3.20   \n",
       "...               ...           ...          ...    ...      ...         ...   \n",
       "2964619           263             0        15.77   0.00      0.5        2.00   \n",
       "2964620           236             0        18.40   1.00      0.5        2.34   \n",
       "2964621            25             0        19.97   0.00      0.5        0.00   \n",
       "2964622            13             0        23.88   0.00      0.5        5.58   \n",
       "2964623            75             0        32.40   7.75      0.5        7.29   \n",
       "\n",
       "         tolls_amount  improvement_surcharge  total_amount  \\\n",
       "0                0.00                    1.0         22.70   \n",
       "1                0.00                    1.0         18.75   \n",
       "2                0.00                    1.0         31.30   \n",
       "3                0.00                    1.0         17.00   \n",
       "4                0.00                    1.0         16.10   \n",
       "...               ...                    ...           ...   \n",
       "2964619          0.00                    1.0         21.77   \n",
       "2964620          0.00                    1.0         25.74   \n",
       "2964621          0.00                    1.0         23.97   \n",
       "2964622          0.00                    1.0         33.46   \n",
       "2964623          6.94                    1.0         55.88   \n",
       "\n",
       "         congestion_surcharge  Airport_fee  \n",
       "0                         2.5          0.0  \n",
       "1                         2.5          0.0  \n",
       "2                         2.5          0.0  \n",
       "3                         2.5          0.0  \n",
       "4                         2.5          0.0  \n",
       "...                       ...          ...  \n",
       "2964619                   NaN          NaN  \n",
       "2964620                   NaN          NaN  \n",
       "2964621                   NaN          NaN  \n",
       "2964622                   NaN          NaN  \n",
       "2964623                   NaN          NaN  \n",
       "\n",
       "[2964624 rows x 19 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_yellow_tripdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Función para leer y limpiar los datos PARQUET\n",
    "def read_and_clean_parquet(df):    \n",
    "    # Limpiar datos: eliminar duplicados\n",
    "    df = df.drop_duplicates()\n",
    "    \n",
    "    # Limpiar datos: manejar valores faltantes\n",
    "    df = df.fillna({'some_column': 'default_value'})  # Ajusta según las columnas que manejen valores nulos\n",
    "    \n",
    "    # Transformaciones adicionales (ejemplo: convertir formatos de fecha)\n",
    "    if 'pickup_datetime' in df.columns:\n",
    "        df['pickup_datetime'] = pd.to_datetime(df['pickup_datetime'])\n",
    "    if 'dropoff_datetime' in df.columns:\n",
    "        df['dropoff_datetime'] = pd.to_datetime(df['dropoff_datetime'])\n",
    "    \n",
    "    # Otros procesos de transformación...\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>2</td>\n",
       "      <td>2024-01-01 00:57:55</td>\n",
       "      <td>2024-01-01 01:17:43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.72</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>186</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>17.70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.70</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>2024-01-01 00:03:00</td>\n",
       "      <td>2024-01-01 00:09:36</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>140</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.75</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.75</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:17:06</td>\n",
       "      <td>2024-01-01 00:35:01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.70</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>23.30</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.30</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:36:38</td>\n",
       "      <td>2024-01-01 00:44:56</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.40</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>79</td>\n",
       "      <td>211</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17.00</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:46:51</td>\n",
       "      <td>2024-01-01 00:52:57</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>211</td>\n",
       "      <td>148</td>\n",
       "      <td>1</td>\n",
       "      <td>7.90</td>\n",
       "      <td>3.50</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.10</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964619</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:45:59</td>\n",
       "      <td>2024-01-31 23:54:36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>107</td>\n",
       "      <td>263</td>\n",
       "      <td>0</td>\n",
       "      <td>15.77</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964620</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-31 23:13:07</td>\n",
       "      <td>2024-01-31 23:27:52</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>114</td>\n",
       "      <td>236</td>\n",
       "      <td>0</td>\n",
       "      <td>18.40</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.34</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964621</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:19:00</td>\n",
       "      <td>2024-01-31 23:38:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>211</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>19.97</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.97</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964622</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 23:07:23</td>\n",
       "      <td>2024-01-31 23:25:14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>107</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>23.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.58</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964623</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-31 23:58:25</td>\n",
       "      <td>2024-02-01 00:13:30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>138</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>32.40</td>\n",
       "      <td>7.75</td>\n",
       "      <td>0.5</td>\n",
       "      <td>7.29</td>\n",
       "      <td>6.94</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2964624 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0               2  2024-01-01 00:57:55   2024-01-01 01:17:43              1.0   \n",
       "1               1  2024-01-01 00:03:00   2024-01-01 00:09:36              1.0   \n",
       "2               1  2024-01-01 00:17:06   2024-01-01 00:35:01              1.0   \n",
       "3               1  2024-01-01 00:36:38   2024-01-01 00:44:56              1.0   \n",
       "4               1  2024-01-01 00:46:51   2024-01-01 00:52:57              1.0   \n",
       "...           ...                  ...                   ...              ...   \n",
       "2964619         2  2024-01-31 23:45:59   2024-01-31 23:54:36              NaN   \n",
       "2964620         1  2024-01-31 23:13:07   2024-01-31 23:27:52              NaN   \n",
       "2964621         2  2024-01-31 23:19:00   2024-01-31 23:38:00              NaN   \n",
       "2964622         2  2024-01-31 23:07:23   2024-01-31 23:25:14              NaN   \n",
       "2964623         1  2024-01-31 23:58:25   2024-02-01 00:13:30              NaN   \n",
       "\n",
       "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "0                 1.72         1.0                  N           186   \n",
       "1                 1.80         1.0                  N           140   \n",
       "2                 4.70         1.0                  N           236   \n",
       "3                 1.40         1.0                  N            79   \n",
       "4                 0.80         1.0                  N           211   \n",
       "...                ...         ...                ...           ...   \n",
       "2964619           3.18         NaN               None           107   \n",
       "2964620           4.00         NaN               None           114   \n",
       "2964621           3.33         NaN               None           211   \n",
       "2964622           3.06         NaN               None           107   \n",
       "2964623           8.10         NaN               None           138   \n",
       "\n",
       "         DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "0                  79             2        17.70   1.00      0.5        0.00   \n",
       "1                 236             1        10.00   3.50      0.5        3.75   \n",
       "2                  79             1        23.30   3.50      0.5        3.00   \n",
       "3                 211             1        10.00   3.50      0.5        2.00   \n",
       "4                 148             1         7.90   3.50      0.5        3.20   \n",
       "...               ...           ...          ...    ...      ...         ...   \n",
       "2964619           263             0        15.77   0.00      0.5        2.00   \n",
       "2964620           236             0        18.40   1.00      0.5        2.34   \n",
       "2964621            25             0        19.97   0.00      0.5        0.00   \n",
       "2964622            13             0        23.88   0.00      0.5        5.58   \n",
       "2964623            75             0        32.40   7.75      0.5        7.29   \n",
       "\n",
       "         tolls_amount  improvement_surcharge  total_amount  \\\n",
       "0                0.00                    1.0         22.70   \n",
       "1                0.00                    1.0         18.75   \n",
       "2                0.00                    1.0         31.30   \n",
       "3                0.00                    1.0         17.00   \n",
       "4                0.00                    1.0         16.10   \n",
       "...               ...                    ...           ...   \n",
       "2964619          0.00                    1.0         21.77   \n",
       "2964620          0.00                    1.0         25.74   \n",
       "2964621          0.00                    1.0         23.97   \n",
       "2964622          0.00                    1.0         33.46   \n",
       "2964623          6.94                    1.0         55.88   \n",
       "\n",
       "         congestion_surcharge  Airport_fee  \n",
       "0                         2.5          0.0  \n",
       "1                         2.5          0.0  \n",
       "2                         2.5          0.0  \n",
       "3                         2.5          0.0  \n",
       "4                         2.5          0.0  \n",
       "...                       ...          ...  \n",
       "2964619                   NaN          NaN  \n",
       "2964620                   NaN          NaN  \n",
       "2964621                   NaN          NaN  \n",
       "2964622                   NaN          NaN  \n",
       "2964623                   NaN          NaN  \n",
       "\n",
       "[2964624 rows x 19 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_and_clean_parquet(df_yellow_tripdata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>lpep_pickup_datetime</th>\n",
       "      <th>lpep_dropoff_datetime</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</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>ehail_fee</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>trip_type</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-01 00:46:55</td>\n",
       "      <td>2024-01-01 00:58:25</td>\n",
       "      <td>N</td>\n",
       "      <td>1.0</td>\n",
       "      <td>236</td>\n",
       "      <td>239</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.98</td>\n",
       "      <td>12.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.66</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-01 00:31:42</td>\n",
       "      <td>2024-01-01 00:52:34</td>\n",
       "      <td>N</td>\n",
       "      <td>1.0</td>\n",
       "      <td>65</td>\n",
       "      <td>170</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.54</td>\n",
       "      <td>30.30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>7.11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>42.66</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-01 00:30:21</td>\n",
       "      <td>2024-01-01 00:49:23</td>\n",
       "      <td>N</td>\n",
       "      <td>1.0</td>\n",
       "      <td>74</td>\n",
       "      <td>262</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.08</td>\n",
       "      <td>19.80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>28.05</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-01 00:30:20</td>\n",
       "      <td>2024-01-01 00:42:12</td>\n",
       "      <td>N</td>\n",
       "      <td>1.0</td>\n",
       "      <td>74</td>\n",
       "      <td>116</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.40</td>\n",
       "      <td>14.20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.70</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-01 00:32:38</td>\n",
       "      <td>2024-01-01 00:43:37</td>\n",
       "      <td>N</td>\n",
       "      <td>1.0</td>\n",
       "      <td>74</td>\n",
       "      <td>243</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.14</td>\n",
       "      <td>22.60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.38</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56546</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 20:46:00</td>\n",
       "      <td>2024-01-31 20:55:00</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56547</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 21:06:00</td>\n",
       "      <td>2024-01-31 21:11:00</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.49</td>\n",
       "      <td>11.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56548</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 21:36:00</td>\n",
       "      <td>2024-01-31 21:40:00</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.52</td>\n",
       "      <td>11.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.52</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56549</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 22:45:00</td>\n",
       "      <td>2024-01-31 22:51:00</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.17</td>\n",
       "      <td>14.22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56550</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-31 22:28:00</td>\n",
       "      <td>2024-01-31 22:59:00</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.27</td>\n",
       "      <td>44.62</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>50.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>56551 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VendorID lpep_pickup_datetime lpep_dropoff_datetime store_and_fwd_flag  \\\n",
       "0             2  2024-01-01 00:46:55   2024-01-01 00:58:25                  N   \n",
       "1             2  2024-01-01 00:31:42   2024-01-01 00:52:34                  N   \n",
       "2             2  2024-01-01 00:30:21   2024-01-01 00:49:23                  N   \n",
       "3             1  2024-01-01 00:30:20   2024-01-01 00:42:12                  N   \n",
       "4             2  2024-01-01 00:32:38   2024-01-01 00:43:37                  N   \n",
       "...         ...                  ...                   ...                ...   \n",
       "56546         2  2024-01-31 20:46:00   2024-01-31 20:55:00               None   \n",
       "56547         2  2024-01-31 21:06:00   2024-01-31 21:11:00               None   \n",
       "56548         2  2024-01-31 21:36:00   2024-01-31 21:40:00               None   \n",
       "56549         2  2024-01-31 22:45:00   2024-01-31 22:51:00               None   \n",
       "56550         2  2024-01-31 22:28:00   2024-01-31 22:59:00               None   \n",
       "\n",
       "       RatecodeID  PULocationID  DOLocationID  passenger_count  trip_distance  \\\n",
       "0             1.0           236           239              1.0           1.98   \n",
       "1             1.0            65           170              5.0           6.54   \n",
       "2             1.0            74           262              1.0           3.08   \n",
       "3             1.0            74           116              1.0           2.40   \n",
       "4             1.0            74           243              1.0           5.14   \n",
       "...           ...           ...           ...              ...            ...   \n",
       "56546         NaN            33            25              NaN           0.00   \n",
       "56547         NaN            72            72              NaN           0.49   \n",
       "56548         NaN            72            72              NaN           0.52   \n",
       "56549         NaN            41            42              NaN           1.17   \n",
       "56550         NaN            33            91              NaN           9.27   \n",
       "\n",
       "       fare_amount  extra  mta_tax  tip_amount  tolls_amount  ehail_fee  \\\n",
       "0            12.80    1.0      0.5        3.61           0.0        NaN   \n",
       "1            30.30    1.0      0.5        7.11           0.0        NaN   \n",
       "2            19.80    1.0      0.5        3.00           0.0        NaN   \n",
       "3            14.20    1.0      1.5        0.00           0.0        NaN   \n",
       "4            22.60    1.0      0.5        6.28           0.0        NaN   \n",
       "...            ...    ...      ...         ...           ...        ...   \n",
       "56546        11.58    0.0      0.0        3.14           0.0        NaN   \n",
       "56547        11.58    0.0      0.0        0.00           0.0        NaN   \n",
       "56548        11.58    0.0      0.0        2.52           0.0        NaN   \n",
       "56549        14.22    0.0      0.0        0.00           0.0        NaN   \n",
       "56550        44.62    0.0      0.0        4.56           0.0        NaN   \n",
       "\n",
       "       improvement_surcharge  total_amount  payment_type  trip_type  \\\n",
       "0                        1.0         21.66           1.0        1.0   \n",
       "1                        1.0         42.66           1.0        1.0   \n",
       "2                        1.0         28.05           1.0        1.0   \n",
       "3                        1.0         16.70           2.0        1.0   \n",
       "4                        1.0         31.38           1.0        1.0   \n",
       "...                      ...           ...           ...        ...   \n",
       "56546                    1.0         15.72           NaN        NaN   \n",
       "56547                    1.0         12.58           NaN        NaN   \n",
       "56548                    1.0         15.10           NaN        NaN   \n",
       "56549                    1.0         15.22           NaN        NaN   \n",
       "56550                    1.0         50.18           NaN        NaN   \n",
       "\n",
       "       congestion_surcharge  \n",
       "0                      2.75  \n",
       "1                      2.75  \n",
       "2                      2.75  \n",
       "3                      0.00  \n",
       "4                      0.00  \n",
       "...                     ...  \n",
       "56546                   NaN  \n",
       "56547                   NaN  \n",
       "56548                   NaN  \n",
       "56549                   NaN  \n",
       "56550                   NaN  \n",
       "\n",
       "[56551 rows x 20 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_and_clean_parquet(df_green_tripdata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>dispatching_base_num</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>dropOff_datetime</th>\n",
       "      <th>PUlocationID</th>\n",
       "      <th>DOlocationID</th>\n",
       "      <th>SR_Flag</th>\n",
       "      <th>Affiliated_base_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B00053</td>\n",
       "      <td>2024-01-01 00:15:00</td>\n",
       "      <td>2024-01-01 02:13:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B00014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B00111</td>\n",
       "      <td>2024-01-01 00:30:00</td>\n",
       "      <td>2024-01-01 02:37:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B00111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B00112</td>\n",
       "      <td>2024-01-01 00:27:24</td>\n",
       "      <td>2024-01-01 01:12:05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B00112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B00112</td>\n",
       "      <td>2024-01-01 00:10:09</td>\n",
       "      <td>2024-01-01 00:25:39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>133.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B00112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B00112</td>\n",
       "      <td>2024-01-01 00:57:07</td>\n",
       "      <td>2024-01-01 01:05:04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B00112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1290111</th>\n",
       "      <td>B03492</td>\n",
       "      <td>2024-01-31 23:51:21</td>\n",
       "      <td>2024-01-31 23:57:09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B03492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1290112</th>\n",
       "      <td>B03492</td>\n",
       "      <td>2024-01-31 23:05:06</td>\n",
       "      <td>2024-01-31 23:20:13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B03492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1290113</th>\n",
       "      <td>B03492</td>\n",
       "      <td>2024-01-31 23:49:07</td>\n",
       "      <td>2024-02-01 00:16:25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B03492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1290114</th>\n",
       "      <td>B03505</td>\n",
       "      <td>2024-01-31 23:04:46</td>\n",
       "      <td>2024-01-31 23:18:22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B03505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1290115</th>\n",
       "      <td>B03529</td>\n",
       "      <td>2024-01-31 23:45:30</td>\n",
       "      <td>2024-01-31 23:59:25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B03404</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1287590 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        dispatching_base_num     pickup_datetime    dropOff_datetime  \\\n",
       "0                     B00053 2024-01-01 00:15:00 2024-01-01 02:13:00   \n",
       "1                     B00111 2024-01-01 00:30:00 2024-01-01 02:37:00   \n",
       "2                     B00112 2024-01-01 00:27:24 2024-01-01 01:12:05   \n",
       "3                     B00112 2024-01-01 00:10:09 2024-01-01 00:25:39   \n",
       "4                     B00112 2024-01-01 00:57:07 2024-01-01 01:05:04   \n",
       "...                      ...                 ...                 ...   \n",
       "1290111               B03492 2024-01-31 23:51:21 2024-01-31 23:57:09   \n",
       "1290112               B03492 2024-01-31 23:05:06 2024-01-31 23:20:13   \n",
       "1290113               B03492 2024-01-31 23:49:07 2024-02-01 00:16:25   \n",
       "1290114               B03505 2024-01-31 23:04:46 2024-01-31 23:18:22   \n",
       "1290115               B03529 2024-01-31 23:45:30 2024-01-31 23:59:25   \n",
       "\n",
       "         PUlocationID  DOlocationID  SR_Flag Affiliated_base_number  \n",
       "0                 NaN           NaN      NaN                 B00014  \n",
       "1                 NaN           NaN      NaN                 B00111  \n",
       "2                 NaN          14.0      NaN                 B00112  \n",
       "3                 NaN         133.0      NaN                 B00112  \n",
       "4                 NaN          14.0      NaN                 B00112  \n",
       "...               ...           ...      ...                    ...  \n",
       "1290111           NaN          14.0      NaN                 B03492  \n",
       "1290112           NaN          21.0      NaN                 B03492  \n",
       "1290113           NaN          14.0      NaN                 B03492  \n",
       "1290114           NaN          76.0      NaN                 B03505  \n",
       "1290115           NaN          76.0      NaN                 B03404  \n",
       "\n",
       "[1287590 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_and_clean_parquet(df_fhv_tripdata)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "---\n",
    "\n",
    "## 1. **Trip Record User Guide**\n",
    "**Descripción:**\n",
    "- Este documento proporciona una **guía completa** sobre cómo entender y utilizar los registros de viajes proporcionados por la Comisión de Taxis y Limusinas de Nueva York (TLC).\n",
    "- Incluye información sobre el **formato de los datos**, **definiciones de campos**, y **consideraciones especiales** al trabajar con los distintos datasets.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Esencial** para comprender la estructura y el contenido de los diferentes datasets.\n",
    "- Ayuda a **interpretar correctamente los datos** y asegura que el análisis sea preciso y coherente.\n",
    "- Proporciona **contexto y aclaraciones** sobre posibles anomalías o particularidades en los datos.\n",
    "\n",
    "---\n",
    "\n",
    "## 2. **High Volume FHV Trips Data Dictionary**\n",
    "**Descripción:**\n",
    "- Contiene la **descripción detallada de los campos** presentes en el dataset de viajes de **vehículos de alta demanda (FHV)**, que incluyen servicios como **Uber, Lyft y otros servicios de ride-sharing**.\n",
    "- Proporciona información sobre **horarios, ubicaciones de recogida y destino**, tiempos de viaje, y otros datos relevantes.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Altamente relevante** para analizar y **optimizar tiempos de espera y rutas** específicamente en el contexto de servicios como Uber.\n",
    "- Permite **identificar patrones de demanda**, tiempos de espera promedio, y **eficiencia en la asignación de vehículos**.\n",
    "- Facilita el **análisis comparativo** entre diferentes zonas y horarios, ayudando a **mejorar la distribución de recursos** y reducir tiempos de espera.\n",
    "- Los datos pueden ser utilizados para **desarrollar modelos predictivos** que anticipen la demanda y optimicen la disponibilidad de vehículos.\n",
    "\n",
    "---\n",
    "\n",
    "## 3. **FHV Trips Data Dictionary**\n",
    "**Descripción:**\n",
    "- Describe los campos del dataset relacionado con **viajes de vehículos contratados (For-Hire Vehicles)** que no necesariamente pertenecen a servicios de alta demanda. Esto incluye **car services tradicionales y limusinas**.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Útil para obtener una perspectiva más amplia** del servicio de transporte contratado en NYC.\n",
    "- Puede ayudar a **comparar la eficiencia operativa** entre servicios tradicionales y de alta demanda.\n",
    "- Los datos pueden ser utilizados para **identificar oportunidades de mejora** y **entender el comportamiento del mercado** en diferentes segmentos.\n",
    "\n",
    "---\n",
    "\n",
    "## 4. **Yellow Trips Data Dictionary**\n",
    "**Descripción:**\n",
    "- Proporciona detalles sobre los campos del dataset de **viajes de taxis amarillos**, que operan principalmente en **Manhattan** y son una parte significativa del sistema de transporte de NYC.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Beneficioso para entender el panorama general** del transporte en NYC.\n",
    "- Permite **comparar los tiempos de espera y eficiencia** entre taxis tradicionales y servicios de ride-sharing.\n",
    "- Los datos pueden ayudar a **identificar áreas con alta demanda de transporte** y posibles **cuellos de botella** en el tráfico.\n",
    "- Útil para **analizar tendencias históricas** y **complementar el análisis** de los servicios de alta demanda.\n",
    "\n",
    "---\n",
    "\n",
    "## 5. **Green Trips Data Dictionary**\n",
    "**Descripción:**\n",
    "- Detalla los campos del dataset de **viajes de taxis verdes**, que operan principalmente en los **distritos exteriores** de NYC (Brooklyn, Queens, Bronx, Staten Island) y **norte de Manhattan**.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Importante para analizar la eficiencia del transporte** en áreas fuera del centro de la ciudad.\n",
    "- Permite **identificar oportunidades de optimización** en zonas que pueden estar menos atendidas por servicios de ride-sharing.\n",
    "- Ayuda a **comprender patrones de demanda y tiempos de espera** en diferentes regiones geográficas.\n",
    "- Puede ser utilizado para **diseñar estrategias específicas** que aborden las necesidades de transporte en estas áreas.\n",
    "\n",
    "---\n",
    "\n",
    "## 6. **Working With PARQUET Format**\n",
    "**Descripción:**\n",
    "- Este documento ofrece **instrucciones y mejores prácticas** para trabajar con archivos en **formato PARQUET**, un formato de almacenamiento columnar eficiente utilizado para **grandes conjuntos de datos**.\n",
    "\n",
    "**Utilidad para tu objetivo:**\n",
    "- **Crucial para manejar y procesar eficientemente** los datasets mencionados, que suelen ser de gran tamaño.\n",
    "- El formato PARQUET **mejora el rendimiento** en operaciones de lectura/escritura y **reduce el uso de espacio de almacenamiento**.\n",
    "- Facilita la **integración con herramientas de análisis de datos** como Apache Spark, Hadoop, y lenguajes de programación como Python (usando librerías como **pandas** y **pyarrow**).\n",
    "- Ayuda a **optimizar los procesos de extracción, transformación y carga (ETL)**, acelerando el análisis y la generación de insights.\n",
    "\n",
    "---\n",
    "\n",
    "## **Recomendación General**\n",
    "\n",
    "Para **maximizar la eficiencia operativa y reducir los tiempos de espera**, te sugiero:\n",
    "\n",
    "1. **Priorizar el uso del**:\n",
    "   - **High Volume FHV Trips Data Dictionary**: Para obtener datos específicos de servicios como Uber y analizar directamente los tiempos de espera y rutas utilizadas.\n",
    "   - **Trip Record User Guide**: Para asegurar una comprensión profunda y correcta de los datos que vas a manejar.\n",
    "\n",
    "2. **Complementar con**:\n",
    "   - **FHV Trips Data Dictionary**: Para comparar y entender diferencias con otros servicios de transporte contratado.\n",
    "   - **Yellow y Green Trips Data Dictionaries**: Para obtener una **visión integral del sistema de transporte** en NYC y **identificar patrones comunes o diferencias significativas** que puedan informar estrategias de optimización más amplias.\n",
    "   \n",
    "3. **Utilizar el documento**:\n",
    "   - **Working With PARQUET Format**: Para **manejar eficientemente los grandes volúmenes de datos**, asegurando que tus procesos de análisis sean rápidos y escalables.\n",
    "\n",
    "---\n",
    "\n",
    "## **Pasos Sugeridos para tu Análisis**\n",
    "\n",
    "1. **Data Ingestion**:\n",
    "   - **Importa y almacena los datasets relevantes** utilizando el formato PARQUET para eficiencia.\n",
    "   - **Verifica y limpia los datos** asegurando su calidad para el análisis.\n",
    "\n",
    "2. **Análisis Exploratorio**:\n",
    "   - **Identifica patrones de demanda**, tiempos de espera promedio, y rutas más comunes.\n",
    "   - **Detecta áreas con altos tiempos de espera** y posibles causas (e.g., baja disponibilidad de vehículos, tráfico, eventos especiales).\n",
    "\n",
    "3. **Modelado y Optimización**:\n",
    "   - **Desarrolla modelos predictivos** que anticipen la demanda en diferentes zonas y horarios.\n",
    "   - **Simula y propone rutas óptimas** que reduzcan los tiempos de espera y mejoren la eficiencia operativa.\n",
    "\n",
    "4. **Implementación y Monitoreo**:\n",
    "   - **Implementa las estrategias propuestas** y monitorea su impacto en tiempo real.\n",
    "   - **Ajusta y mejora continuamente** las estrategias basándote en nuevos datos y feedback obtenido.\n",
    "\n",
    "5. **Reporte y Visualización**:\n",
    "   - **Crea dashboards y reportes interactivos** que muestren el desempeño del KPI y faciliten la toma de decisiones.\n",
    "   - **Comunica los hallazgos y mejoras** a los stakeholders de manera clara y efectiva.\n",
    "\n",
    "---\n",
    "\n",
    "## **Conclusión**\n",
    "\n",
    "Al **combinar y analizar estratégicamente estos datasets**, podrás **obtener insights profundos** sobre la operación de servicios de transporte en NYC y **diseñar soluciones efectivas** para **reducir los tiempos de espera** de los pasajeros. El uso eficiente de los datos, junto con herramientas y metodologías adecuadas, será clave para **lograr y mantener mejoras sostenibles en la eficiencia operativa**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para optimizar rutas y tiempos de espera, hay varias APIs disponibles que pueden ofrecer datos útiles. A continuación, te presento algunas opciones con propósitos claros que podrías considerar:\n",
    "\n",
    "### 1. **Google Maps API**\n",
    "\n",
    "**Propósito**: Ofrecer información sobre rutas, estimaciones de tiempo, tráfico y más.\n",
    "\n",
    "- **Directions API**: Proporciona rutas entre dos ubicaciones, incluyendo tiempos estimados de viaje y distancias.\n",
    "- **Distance Matrix API**: Ofrece tiempos y distancias entre múltiples pares de ubicaciones, lo que es útil para la planificación de rutas y la optimización.\n",
    "- **Traffic Data**: Proporciona información sobre el tráfico en tiempo real y condiciones de carretera.\n",
    "\n",
    "**Documentación**: [Google Maps Directions API](https://developers.google.com/maps/documentation/directions/start)  \n",
    "**Ejemplo de uso**: Obtener tiempos estimados de viaje entre varias ubicaciones.\n",
    "\n",
    "### 2. **Mapbox API**\n",
    "\n",
    "**Propósito**: Proporcionar servicios de mapas y navegación, incluyendo enrutamiento y tráfico.\n",
    "\n",
    "- **Mapbox Directions API**: Ofrece enrutamiento y estimaciones de tiempo de viaje.\n",
    "- **Mapbox Traffic Data**: Proporciona información en tiempo real sobre el tráfico y las condiciones de las carreteras.\n",
    "\n",
    "**Documentación**: [Mapbox Directions API](https://docs.mapbox.com/mapbox-gl-js/example/mapbox-gl-directions/)  \n",
    "**Ejemplo de uso**: Obtener rutas y tiempos estimados con datos de tráfico en tiempo real.\n",
    "\n",
    "### 3. **Here API**\n",
    "\n",
    "**Propósito**: Proporcionar servicios de localización, navegación y tráfico.\n",
    "\n",
    "- **Here Routing API**: Ofrece rutas entre ubicaciones, incluyendo estimaciones de tiempo y distancias.\n",
    "- **Here Traffic API**: Proporciona información sobre el tráfico en tiempo real y predicciones de tráfico.\n",
    "\n",
    "**Documentación**: [Here Routing API](https://developer.here.com/documentation/routing-api/8.11.0/dev_guide/index.html)  \n",
    "**Ejemplo de uso**: Planificar rutas y analizar el tráfico en tiempo real.\n",
    "\n",
    "### 4. **TomTom API**\n",
    "\n",
    "**Propósito**: Ofrecer servicios de navegación, enrutamiento y datos de tráfico.\n",
    "\n",
    "- **TomTom Routing API**: Ofrece enrutamiento y tiempos estimados de viaje.\n",
    "- **TomTom Traffic API**: Proporciona datos de tráfico en tiempo real y predicciones de tráfico.\n",
    "\n",
    "**Documentación**: [TomTom Routing API](https://developer.tomtom.com/routing-api/routing-api-documentation)  \n",
    "**Ejemplo de uso**: Obtener estimaciones de tiempo y rutas optimizadas con datos de tráfico en tiempo real.\n",
    "\n",
    "### 5. **OpenRouteService API**\n",
    "\n",
    "**Propósito**: Ofrecer servicios de enrutamiento y análisis de datos geoespaciales.\n",
    "\n",
    "- **ORS Routing API**: Proporciona rutas y estimaciones de tiempo de viaje, incluyendo opciones para diferentes modos de transporte.\n",
    "- **ORS Matrix API**: Ofrece matrices de distancias y tiempos entre múltiples ubicaciones.\n",
    "\n",
    "**Documentación**: [OpenRouteService API](https://openrouteservice.org/sign-up/)  \n",
    "**Ejemplo de uso**: Calcular rutas y tiempos entre varias ubicaciones, y analizar matrices de distancia.\n",
    "\n",
    "```\n",
    "\n",
    "### Resumen\n",
    "\n",
    "Para la optimización de rutas y tiempos de espera, **Google Maps API**, **Mapbox API**, **Here API**, **TomTom API**, y **OpenRouteService API** son opciones excelentes. Cada una ofrece características específicas que pueden ayudarte a lograr tus objetivos, como enrutamiento, estimaciones de tiempo, y datos de tráfico en tiempo real.\n",
    "\n",
    "Si necesitas más detalles o ejemplos adicionales sobre alguna de estas APIs, ¡házmelo saber!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}