upload-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f5b4b1d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.5.3'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "61376fe3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE \"green_taxi_data\" (\n",
      "\"VendorID\" INTEGER,\n",
      "  \"lpep_pickup_datetime\" TEXT,\n",
      "  \"lpep_dropoff_datetime\" TEXT,\n",
      "  \"store_and_fwd_flag\" TEXT,\n",
      "  \"RatecodeID\" INTEGER,\n",
      "  \"PULocationID\" INTEGER,\n",
      "  \"DOLocationID\" INTEGER,\n",
      "  \"passenger_count\" INTEGER,\n",
      "  \"trip_distance\" REAL,\n",
      "  \"fare_amount\" REAL,\n",
      "  \"extra\" REAL,\n",
      "  \"mta_tax\" REAL,\n",
      "  \"tip_amount\" REAL,\n",
      "  \"tolls_amount\" REAL,\n",
      "  \"ehail_fee\" REAL,\n",
      "  \"improvement_surcharge\" REAL,\n",
      "  \"total_amount\" REAL,\n",
      "  \"payment_type\" INTEGER,\n",
      "  \"trip_type\" INTEGER,\n",
      "  \"congestion_surcharge\" REAL\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('green_tripdata_2019-01.csv',nrows=100)\n",
    "print(pd.io.sql.get_schema(df, name='green_taxi_data'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0582617f",
   "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>2018-12-21 15:17:29</td>\n",
       "      <td>2018-12-21 15:18:57</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>264</td>\n",
       "      <td>264</td>\n",
       "      <td>5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.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>4.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:10:16</td>\n",
       "      <td>2019-01-01 00:16:32</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>0.86</td>\n",
       "      <td>6.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>7.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:27:11</td>\n",
       "      <td>2019-01-01 00:31:38</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>0.66</td>\n",
       "      <td>4.5</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>5.80</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:46:20</td>\n",
       "      <td>2019-01-01 01:04:54</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>189</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>2.68</td>\n",
       "      <td>13.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>19.71</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:19:06</td>\n",
       "      <td>2019-01-01 00:39:43</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>82</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>4.53</td>\n",
       "      <td>18.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>19.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</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>95</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:15:42</td>\n",
       "      <td>2019-01-01 00:41:04</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "      <td>7.83</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.46</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>32.76</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:21:28</td>\n",
       "      <td>2019-01-01 00:48:15</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>230</td>\n",
       "      <td>1</td>\n",
       "      <td>4.49</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3</td>\n",
       "      <td>25.30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:17:10</td>\n",
       "      <td>2019-01-01 00:23:34</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>1.08</td>\n",
       "      <td>6.5</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>7.80</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:25:01</td>\n",
       "      <td>2019-01-01 00:43:55</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>232</td>\n",
       "      <td>2</td>\n",
       "      <td>8.74</td>\n",
       "      <td>25.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>26.30</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:36:56</td>\n",
       "      <td>2019-01-01 00:43:50</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>223</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1.32</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    VendorID lpep_pickup_datetime lpep_dropoff_datetime store_and_fwd_flag  \\\n",
       "0          2  2018-12-21 15:17:29   2018-12-21 15:18:57                  N   \n",
       "1          2  2019-01-01 00:10:16   2019-01-01 00:16:32                  N   \n",
       "2          2  2019-01-01 00:27:11   2019-01-01 00:31:38                  N   \n",
       "3          2  2019-01-01 00:46:20   2019-01-01 01:04:54                  N   \n",
       "4          2  2019-01-01 00:19:06   2019-01-01 00:39:43                  N   \n",
       "..       ...                  ...                   ...                ...   \n",
       "95         2  2019-01-01 00:15:42   2019-01-01 00:41:04                  N   \n",
       "96         2  2019-01-01 00:21:28   2019-01-01 00:48:15                  N   \n",
       "97         2  2019-01-01 00:17:10   2019-01-01 00:23:34                  N   \n",
       "98         2  2019-01-01 00:25:01   2019-01-01 00:43:55                  N   \n",
       "99         2  2019-01-01 00:36:56   2019-01-01 00:43:50                  N   \n",
       "\n",
       "    RatecodeID  PULocationID  DOLocationID  passenger_count  trip_distance  \\\n",
       "0            1           264           264                5           0.00   \n",
       "1            1            97            49                2           0.86   \n",
       "2            1            49           189                2           0.66   \n",
       "3            1           189            17                2           2.68   \n",
       "4            1            82           258                1           4.53   \n",
       "..         ...           ...           ...              ...            ...   \n",
       "95           1            97           179                1           7.83   \n",
       "96           1             7           230                1           4.49   \n",
       "97           1            42            42                1           1.08   \n",
       "98           1            42           232                2           8.74   \n",
       "99           1           223             7                5           1.32   \n",
       "\n",
       "    fare_amount  extra  mta_tax  tip_amount  tolls_amount  ehail_fee  \\\n",
       "0           3.0    0.5      0.5        0.00           0.0        NaN   \n",
       "1           6.0    0.5      0.5        0.00           0.0        NaN   \n",
       "2           4.5    0.5      0.5        0.00           0.0        NaN   \n",
       "3          13.5    0.5      0.5        2.96           0.0        NaN   \n",
       "4          18.0    0.5      0.5        0.00           0.0        NaN   \n",
       "..          ...    ...      ...         ...           ...        ...   \n",
       "95         26.0    0.5      0.5        5.46           0.0        NaN   \n",
       "96         20.0    0.5      0.5        4.00           0.0        NaN   \n",
       "97          6.5    0.5      0.5        0.00           0.0        NaN   \n",
       "98         25.0    0.5      0.5        0.00           0.0        NaN   \n",
       "99          7.0    0.5      0.5        2.00           0.0        NaN   \n",
       "\n",
       "    improvement_surcharge  total_amount  payment_type  trip_type  \\\n",
       "0                     0.3          4.30             2          1   \n",
       "1                     0.3          7.30             2          1   \n",
       "2                     0.3          5.80             1          1   \n",
       "3                     0.3         19.71             1          1   \n",
       "4                     0.3         19.30             2          1   \n",
       "..                    ...           ...           ...        ...   \n",
       "95                    0.3         32.76             1          1   \n",
       "96                    0.3         25.30             1          1   \n",
       "97                    0.3          7.80             2          1   \n",
       "98                    0.3         26.30             2          1   \n",
       "99                    0.3         10.30             1          1   \n",
       "\n",
       "    congestion_surcharge  \n",
       "0                    NaN  \n",
       "1                    NaN  \n",
       "2                    NaN  \n",
       "3                    NaN  \n",
       "4                    NaN  \n",
       "..                   ...  \n",
       "95                   NaN  \n",
       "96                   NaN  \n",
       "97                   NaN  \n",
       "98                   NaN  \n",
       "99                   NaN  \n",
       "\n",
       "[100 rows x 20 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a155f125",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE green_taxi_data (\n",
      "\t\"VendorID\" BIGINT, \n",
      "\tlpep_pickup_datetime TEXT, \n",
      "\tlpep_dropoff_datetime TEXT, \n",
      "\tstore_and_fwd_flag TEXT, \n",
      "\t\"RatecodeID\" BIGINT, \n",
      "\t\"PULocationID\" BIGINT, \n",
      "\t\"DOLocationID\" BIGINT, \n",
      "\tpassenger_count BIGINT, \n",
      "\ttrip_distance FLOAT(53), \n",
      "\tfare_amount FLOAT(53), \n",
      "\textra FLOAT(53), \n",
      "\tmta_tax FLOAT(53), \n",
      "\ttip_amount FLOAT(53), \n",
      "\ttolls_amount FLOAT(53), \n",
      "\tehail_fee FLOAT(53), \n",
      "\timprovement_surcharge FLOAT(53), \n",
      "\ttotal_amount FLOAT(53), \n",
      "\tpayment_type BIGINT, \n",
      "\ttrip_type BIGINT, \n",
      "\tcongestion_surcharge FLOAT(53)\n",
      ")\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')\n",
    "print(pd.io.sql.get_schema(df, name='green_taxi_data',con=engine))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "69d180e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_iter = pd.read_csv('green_tripdata_2019-01.csv', iterator=True,chunksize=100000,dtype=object)\n",
    "df= next(df_iter)\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e561c6e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.lpep_pickup_datetime=pd.to_datetime(df.lpep_pickup_datetime)\n",
    "df.lpep_dropoff_datetime=pd.to_datetime(df.lpep_dropoff_datetime)\n",
    "\n",
    "df.to_sql(name='green_taxi_data',con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "442e19d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 15.6 ms\n",
      "Wall time: 19 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df.head(n=0).to_sql(name='green_taxi_data',con=engine, if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "97449c69",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE green_taxi_data (\n",
      "\t\"VendorID\" TEXT, \n",
      "\tlpep_pickup_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\tlpep_dropoff_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\tstore_and_fwd_flag TEXT, \n",
      "\t\"RatecodeID\" TEXT, \n",
      "\t\"PULocationID\" TEXT, \n",
      "\t\"DOLocationID\" TEXT, \n",
      "\tpassenger_count TEXT, \n",
      "\ttrip_distance TEXT, \n",
      "\tfare_amount TEXT, \n",
      "\textra TEXT, \n",
      "\tmta_tax TEXT, \n",
      "\ttip_amount TEXT, \n",
      "\ttolls_amount TEXT, \n",
      "\tehail_fee TEXT, \n",
      "\timprovement_surcharge TEXT, \n",
      "\ttotal_amount TEXT, \n",
      "\tpayment_type TEXT, \n",
      "\ttrip_type TEXT, \n",
      "\tcongestion_surcharge TEXT\n",
      ")\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(pd.io.sql.get_schema(df, name='green_taxi_data',con=engine))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f5aeee48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "inserted another chunk, took 9.762 second\n",
      "inserted another chunk, took 8.124 second\n",
      "inserted another chunk, took 7.536 second\n",
      "inserted another chunk, took 8.403 second\n",
      "inserted another chunk, took 7.454 second\n",
      "inserted another chunk, took 2.342 second\n"
     ]
    },
    {
     "ename": "StopIteration",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mStopIteration\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\91962\\Desktop\\DockerDE\\upload-data.ipynb Cell 9\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/91962/Desktop/DockerDE/upload-data.ipynb#X11sZmlsZQ%3D%3D?line=2'>3</a>\u001b[0m \u001b[39mwhile\u001b[39;00m \u001b[39mTrue\u001b[39;00m:\n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/91962/Desktop/DockerDE/upload-data.ipynb#X11sZmlsZQ%3D%3D?line=3'>4</a>\u001b[0m     t_start\u001b[39m=\u001b[39mtime()\n\u001b[1;32m----> <a href='vscode-notebook-cell:/c%3A/Users/91962/Desktop/DockerDE/upload-data.ipynb#X11sZmlsZQ%3D%3D?line=4'>5</a>\u001b[0m     df \u001b[39m=\u001b[39m \u001b[39mnext\u001b[39;49m(df_iter)\n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/91962/Desktop/DockerDE/upload-data.ipynb#X11sZmlsZQ%3D%3D?line=5'>6</a>\u001b[0m     df\u001b[39m.\u001b[39mlpep_pickup_datetime \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mto_datetime(df\u001b[39m.\u001b[39mlpep_pickup_datetime)\n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/91962/Desktop/DockerDE/upload-data.ipynb#X11sZmlsZQ%3D%3D?line=6'>7</a>\u001b[0m     df\u001b[39m.\u001b[39mlpep_dropoff_datetime \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mto_datetime(df\u001b[39m.\u001b[39mlpep_dropoff_datetime)\n",
      "File \u001b[1;32mc:\\Users\\91962\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pandas\\io\\parsers\\readers.py:1698\u001b[0m, in \u001b[0;36mTextFileReader.__next__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1696\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39m__next__\u001b[39m(\u001b[39mself\u001b[39m) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m DataFrame:\n\u001b[0;32m   1697\u001b[0m     \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m-> 1698\u001b[0m         \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mget_chunk()\n\u001b[0;32m   1699\u001b[0m     \u001b[39mexcept\u001b[39;00m \u001b[39mStopIteration\u001b[39;00m:\n\u001b[0;32m   1700\u001b[0m         \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mclose()\n",
      "File \u001b[1;32mc:\\Users\\91962\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pandas\\io\\parsers\\readers.py:1810\u001b[0m, in \u001b[0;36mTextFileReader.get_chunk\u001b[1;34m(self, size)\u001b[0m\n\u001b[0;32m   1808\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mStopIteration\u001b[39;00m\n\u001b[0;32m   1809\u001b[0m     size \u001b[39m=\u001b[39m \u001b[39mmin\u001b[39m(size, \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mnrows \u001b[39m-\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_currow)\n\u001b[1;32m-> 1810\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mread(nrows\u001b[39m=\u001b[39;49msize)\n",
      "File \u001b[1;32mc:\\Users\\91962\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pandas\\io\\parsers\\readers.py:1778\u001b[0m, in \u001b[0;36mTextFileReader.read\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m   1771\u001b[0m nrows \u001b[39m=\u001b[39m validate_integer(\u001b[39m\"\u001b[39m\u001b[39mnrows\u001b[39m\u001b[39m\"\u001b[39m, nrows)\n\u001b[0;32m   1772\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m   1773\u001b[0m     \u001b[39m# error: \"ParserBase\" has no attribute \"read\"\u001b[39;00m\n\u001b[0;32m   1774\u001b[0m     (\n\u001b[0;32m   1775\u001b[0m         index,\n\u001b[0;32m   1776\u001b[0m         columns,\n\u001b[0;32m   1777\u001b[0m         col_dict,\n\u001b[1;32m-> 1778\u001b[0m     ) \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_engine\u001b[39m.\u001b[39;49mread(  \u001b[39m# type: ignore[attr-defined]\u001b[39;49;00m\n\u001b[0;32m   1779\u001b[0m         nrows\n\u001b[0;32m   1780\u001b[0m     )\n\u001b[0;32m   1781\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mException\u001b[39;00m:\n\u001b[0;32m   1782\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mclose()\n",
      "File \u001b[1;32mc:\\Users\\91962\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pandas\\io\\parsers\\c_parser_wrapper.py:230\u001b[0m, in \u001b[0;36mCParserWrapper.read\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m    228\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m    229\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mlow_memory:\n\u001b[1;32m--> 230\u001b[0m         chunks \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_reader\u001b[39m.\u001b[39;49mread_low_memory(nrows)\n\u001b[0;32m    231\u001b[0m         \u001b[39m# destructive to chunks\u001b[39;00m\n\u001b[0;32m    232\u001b[0m         data \u001b[39m=\u001b[39m _concatenate_chunks(chunks)\n",
      "File \u001b[1;32mc:\\Users\\91962\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pandas\\_libs\\parsers.pyx:833\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader.read_low_memory\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mStopIteration\u001b[0m: "
     ]
    }
   ],
   "source": [
    "from time import time\n",
    "len(df)\n",
    "while True:\n",
    "    t_start=time()\n",
    "    df = next(df_iter)\n",
    "    df.lpep_pickup_datetime = pd.to_datetime(df.lpep_pickup_datetime)\n",
    "    df.lpep_dropoff_datetime = pd.to_datetime(df.lpep_dropoff_datetime)\n",
    "    df.to_sql(name='green_taxi_data', con = engine, if_exists='append')\n",
    "    t_end=time()\n",
    "    print(\"inserted another chunk, took %.3f second\" % (t_end-t_start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4984c178",
   "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>index</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>...</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>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-12-21 15:17:29</td>\n",
       "      <td>2018-12-21 15:18:57</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>264</td>\n",
       "      <td>264</td>\n",
       "      <td>5</td>\n",
       "      <td>.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:10:16</td>\n",
       "      <td>2019-01-01 00:16:32</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>97</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>.86</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.3</td>\n",
       "      <td>7.3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:27:11</td>\n",
       "      <td>2019-01-01 00:31:38</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>189</td>\n",
       "      <td>2</td>\n",
       "      <td>.66</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.3</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:46:20</td>\n",
       "      <td>2019-01-01 01:04:54</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>189</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>2.68</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.96</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.3</td>\n",
       "      <td>19.71</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-01-01 00:19:06</td>\n",
       "      <td>2019-01-01 00:39:43</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>82</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>4.53</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.3</td>\n",
       "      <td>19.3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index VendorID lpep_pickup_datetime lpep_dropoff_datetime  \\\n",
       "0      0        2  2018-12-21 15:17:29   2018-12-21 15:18:57   \n",
       "1      1        2  2019-01-01 00:10:16   2019-01-01 00:16:32   \n",
       "2      2        2  2019-01-01 00:27:11   2019-01-01 00:31:38   \n",
       "3      3        2  2019-01-01 00:46:20   2019-01-01 01:04:54   \n",
       "4      4        2  2019-01-01 00:19:06   2019-01-01 00:39:43   \n",
       "\n",
       "  store_and_fwd_flag RatecodeID PULocationID DOLocationID passenger_count  \\\n",
       "0                  N          1          264          264               5   \n",
       "1                  N          1           97           49               2   \n",
       "2                  N          1           49          189               2   \n",
       "3                  N          1          189           17               2   \n",
       "4                  N          1           82          258               1   \n",
       "\n",
       "  trip_distance  ... extra mta_tax tip_amount tolls_amount ehail_fee  \\\n",
       "0           .00  ...   0.5     0.5          0            0      None   \n",
       "1           .86  ...   0.5     0.5          0            0      None   \n",
       "2           .66  ...   0.5     0.5          0            0      None   \n",
       "3          2.68  ...   0.5     0.5       2.96            0      None   \n",
       "4          4.53  ...   0.5     0.5          0            0      None   \n",
       "\n",
       "  improvement_surcharge total_amount payment_type trip_type  \\\n",
       "0                   0.3          4.3            2         1   \n",
       "1                   0.3          7.3            2         1   \n",
       "2                   0.3          5.8            1         1   \n",
       "3                   0.3        19.71            1         1   \n",
       "4                   0.3         19.3            2         1   \n",
       "\n",
       "  congestion_surcharge  \n",
       "0                 None  \n",
       "1                 None  \n",
       "2                 None  \n",
       "3                 None  \n",
       "4                 None  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query =\"\"\"select * from green_taxi_data limit 5\"\"\"\n",
    "pd.read_sql(query,con=engine)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "07a29274",
   "metadata": {},
   "source": [
    "**Query 1** How many taxi trips were totally made on January 15?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "dabcaf85",
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20530</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0  20530"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query =\"\"\"select count(*) from green_taxi_data where lpep_pickup_datetime > '2019-01-15 00:00:00' and lpep_dropoff_datetime < '2019-01-16 00:00:00'  limit 5\"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "60da4121",
   "metadata": {},
   "source": [
    "**Query 2** Which was the day with the largest trip distance Use the pick up time for your calculations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f2c7f04d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE green_taxi_data (\n",
      "\t\"VendorID\" TEXT, \n",
      "\tlpep_pickup_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\tlpep_dropoff_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\tstore_and_fwd_flag TEXT, \n",
      "\t\"RatecodeID\" TEXT, \n",
      "\t\"PULocationID\" TEXT, \n",
      "\t\"DOLocationID\" TEXT, \n",
      "\tpassenger_count TEXT, \n",
      "\ttrip_distance FLOAT(53), \n",
      "\tfare_amount TEXT, \n",
      "\textra TEXT, \n",
      "\tmta_tax TEXT, \n",
      "\ttip_amount TEXT, \n",
      "\ttolls_amount TEXT, \n",
      "\tehail_fee TEXT, \n",
      "\timprovement_surcharge TEXT, \n",
      "\ttotal_amount TEXT, \n",
      "\tpayment_type TEXT, \n",
      "\ttrip_type TEXT, \n",
      "\tcongestion_surcharge TEXT\n",
      ")\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.trip_distance=pd.to_numeric(df.trip_distance)\n",
    "print(pd.io.sql.get_schema(df, name='green_taxi_data',con=engine))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9d878284",
   "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>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2169650.07</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sum  count\n",
       "0  2169650.07      0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query =\"\"\"WITH cte AS (\n",
    "SELECT\n",
    "    CASE \n",
    "        WHEN trip_distance ~ '[0-9]' THEN CAST(trip_distance AS decimal) -- cast to numeric field\n",
    "    END AS num,\n",
    "    CASE \n",
    "        WHEN trip_distance ~ '[a-zA-Z]' THEN trip_distance\n",
    "    END AS a\n",
    "FROM green_taxi_data\n",
    ")\n",
    "SELECT SUM(num), COUNT(a) FROM cte\"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a07f965e",
   "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>date</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-15</td>\n",
       "      <td>117.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  distance\n",
       "0  2019-01-15    117.99"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query=\"\"\"select date(lpep_pickup_datetime),sum(cast(trip_distance as decimal)) as distance from green_taxi_data group by lpep_pickup_datetime order by distance desc limit 1 \"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f92bb730",
   "metadata": {},
   "source": [
    "**Query 3** In 2019-01-01 how many trips had 2 and 3 passengers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a4e18c56",
   "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>passenger_count_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1282</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   passenger_count_2\n",
       "0               1282"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query=\"\"\"select count(*) as passenger_count_2 from green_taxi_data where date(lpep_pickup_datetime)='2019-01-01' and passenger_count='2' limit 1 \"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "35d2e7f4",
   "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>passenger_count_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   passenger_count_3\n",
       "0                254"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query=\"\"\"select count(*) passenger_count_3 from green_taxi_data where date(lpep_pickup_datetime)='2019-01-01' and passenger_count='3' limit 1 \"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fa05799b",
   "metadata": {},
   "source": [
    "**Query 4** For the passengers picked up in the Astoria Zone which was the drop off zone that had the largest tip? We want the name of the zone, not the id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "385a073f",
   "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>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>tip_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>175</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PULocationID DOLocationID tip_amount\n",
       "0            7          175       9.99"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# PULocationID, tip_amount\n",
    "query=\"\"\"select \"PULocationID\",\"DOLocationID\", tip_amount from green_taxi_data where \"PULocationID\" = '7' order by tip_amount desc limit 1\"\"\"\n",
    "pd.read_sql(query,con=engine)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.0"
  },
  "vscode": {
   "interpreter": {
    "hash": "d0754efa56362d41604f3e06d6b8042001933d0fc41d5c940acef8c43e816542"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}