postgres_ingestion.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_string = \"postgresql://airflow:airflow@localhost:5432/airflow\"\n",
    "db = create_engine(conn_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dtype = {\n",
    "        \"VendorID\": \"Int8\",\n",
    "        \"store_and_fwd_flag\": \"category\",\n",
    "        \"RatecodeID\": \"Int8\",\n",
    "        \"PULocationID\": \"Int16\",\n",
    "        \"DOLocationID\": \"Int16\",\n",
    "        \"payment_type\": \"Int8\",\n",
    "        \"passenger_count\": \"Int8\",\n",
    "        \"trip_type\": \"Int8\",\n",
    "    }\n",
    "\n",
    "\n",
    "green_tripdata_df = pd.read_csv(\n",
    "    \"https://github.com/DataTalksClub/nyc-tlc-data/releases/download/green/green_tripdata_2019-09.csv.gz\",\n",
    "    compression=\"gzip\",\n",
    "    parse_dates=[\"lpep_pickup_datetime\", \"lpep_dropoff_datetime\"],\n",
    "    date_format=\"%Y-%m-%d %H:%M:%S\",\n",
    "    dtype=dtype,\n",
    ")\n",
    "    \n",
    "\n",
    "taxi_zone_lookup_df = pd.read_csv(\n",
    "    \"https://s3.amazonaws.com/nyc-tlc/misc/taxi+_zone_lookup.csv\",\n",
    ")"
   ]
  },
  {
   "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>2019-09-01 00:10:53</td>\n",
       "      <td>2019-09-01 00:23:46</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>65</td>\n",
       "      <td>189</td>\n",
       "      <td>5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.36</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>14.16</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-09-01 00:31:22</td>\n",
       "      <td>2019-09-01 00:44:37</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>225</td>\n",
       "      <td>5</td>\n",
       "      <td>3.20</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-09-01 00:50:24</td>\n",
       "      <td>2019-09-01 01:03:20</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>61</td>\n",
       "      <td>5</td>\n",
       "      <td>2.99</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>13.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-09-01 00:27:06</td>\n",
       "      <td>2019-09-01 00:33:22</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>145</td>\n",
       "      <td>112</td>\n",
       "      <td>1</td>\n",
       "      <td>1.73</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>10.30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-09-01 00:43:23</td>\n",
       "      <td>2019-09-01 00:59:54</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>112</td>\n",
       "      <td>198</td>\n",
       "      <td>1</td>\n",
       "      <td>3.42</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>18.36</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID lpep_pickup_datetime lpep_dropoff_datetime store_and_fwd_flag  \\\n",
       "0         2  2019-09-01 00:10:53   2019-09-01 00:23:46                  N   \n",
       "1         2  2019-09-01 00:31:22   2019-09-01 00:44:37                  N   \n",
       "2         2  2019-09-01 00:50:24   2019-09-01 01:03:20                  N   \n",
       "3         2  2019-09-01 00:27:06   2019-09-01 00:33:22                  N   \n",
       "4         2  2019-09-01 00:43:23   2019-09-01 00:59:54                  N   \n",
       "\n",
       "   RatecodeID  PULocationID  DOLocationID  passenger_count  trip_distance  \\\n",
       "0           1            65           189                5           2.00   \n",
       "1           1            97           225                5           3.20   \n",
       "2           1            37            61                5           2.99   \n",
       "3           1           145           112                1           1.73   \n",
       "4           1           112           198                1           3.42   \n",
       "\n",
       "   fare_amount  extra  mta_tax  tip_amount  tolls_amount  ehail_fee  \\\n",
       "0         10.5    0.5      0.5        2.36           0.0        NaN   \n",
       "1         12.0    0.5      0.5        0.00           0.0        NaN   \n",
       "2         12.0    0.5      0.5        0.00           0.0        NaN   \n",
       "3          7.5    0.5      0.5        1.50           0.0        NaN   \n",
       "4         14.0    0.5      0.5        3.06           0.0        NaN   \n",
       "\n",
       "   improvement_surcharge  total_amount  payment_type  trip_type  \\\n",
       "0                    0.3         14.16             1          1   \n",
       "1                    0.3         13.30             2          1   \n",
       "2                    0.3         13.30             2          1   \n",
       "3                    0.3         10.30             1          1   \n",
       "4                    0.3         18.36             1          1   \n",
       "\n",
       "   congestion_surcharge  \n",
       "0                   0.0  \n",
       "1                   0.0  \n",
       "2                   0.0  \n",
       "3                   0.0  \n",
       "4                   0.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_tripdata_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "green_tripdata_df['lpep_pickup_datetime'] = pd.to_datetime(green_tripdata_df['lpep_pickup_datetime'])\n",
    "green_tripdata_df['lpep_dropoff_datetime'] = pd.to_datetime(green_tripdata_df['lpep_dropoff_datetime'])\n",
    "\n",
    "# Lower all column names\n",
    "green_tripdata_df.columns = green_tripdata_df.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "63"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_tripdata_df.to_sql(\"green_tripdata\", db, schema=\"dbt\", if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count number of trips in this day 2019-09-18\n",
    "green_tripdata_df[\n",
    "    green_tripdata_df['lpep_pickup_datetime'].dt.date == pd.Timestamp('2019-09-18').date()\n",
    "].shape[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the day with the longest trip\n",
    "green_tripdata_df['trip_duration'] = green_tripdata_df['lpep_dropoff_datetime'] - green_tripdata_df['lpep_pickup_datetime']\n",
    "green_tripdata_df['trip_duration'] = green_tripdata_df['trip_duration'].dt.total_seconds()\n",
    "\n",
    "green_tripdata_df.loc[green_tripdata_df['trip_duration'].idxmax()]['lpep_pickup_datetime'].date()\n"
   ]
  }
 ],
 "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}