01-docker-terraform/upload_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-02-03T21:48:22.994979600Z",
     "start_time": "2024-02-03T21:48:22.990963600Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Printing out the current version of pandas\n",
    "pd.__version__"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "6bb1ca2c8796b4d3",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Reading the first hundred rows\n",
    "df = pd.read_csv('yellow_tripdata_2021-01.csv', nrows=100)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "d1f2211dcac0f90f",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Generating the postgres schema\n",
    "print(pd.io.sql.get_schema(df, name='yellow_taxi_data'))\n",
    "\n",
    "# NOTE: Notice that datetime columns are marked as TEXT columns "
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "86e34aeb00b05f8",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Parsing incorrectly marked TEXT date columns as datetime\n",
    "df['tpep_pickup_datetime'] = pd.to_datetime(df['tpep_pickup_datetime'])\n",
    "df['tpep_dropoff_datetime'] = pd.to_datetime(df['tpep_dropoff_datetime'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-03T21:56:01.511908700Z",
     "start_time": "2024-02-03T21:56:01.502351200Z"
    }
   },
   "id": "4a5084e9eed90f2",
   "execution_count": 14
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Now the datetime columns are marked as TIMESTAMP in the db schema\n",
    "print(pd.io.sql.get_schema(df, name='yellow_taxi_data'))"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e1141764e39424c0",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Creating a postgres connection\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('postgresql://root:root@localhost:5431/ny_taxi')\n",
    "# Checking the postgres connection\n",
    "engine.connect()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e971c74ac3ad2891",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Database Schema\n",
    "print(pd.io.sql.get_schema(df, name='yellow_taxi_data', con=engine))"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "222bbb32e2461e7c",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pandas.io.parsers.readers.TextFileReader object at 0x00000153CEC4D010>\n"
     ]
    }
   ],
   "source": [
    "# Reading all the elements from the .csv file using Iterators\n",
    "df_iter = pd.read_csv('yellow_tripdata_2021-01.csv', iterator=True, chunksize=100_000)\n",
    "print(df_iter)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-03T22:18:45.559294700Z",
     "start_time": "2024-02-03T22:18:45.547371900Z"
    }
   },
   "id": "f52a7cd338ad0265",
   "execution_count": 22
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserted another chunk...; Took 19.29582715034485 seconds\n",
      "Inserted another chunk...; Took 16.002540826797485 seconds\n",
      "Inserted another chunk...; Took 14.814648151397705 seconds\n",
      "Inserted another chunk...; Took 14.267481088638306 seconds\n",
      "Inserted another chunk...; Took 14.716943264007568 seconds\n",
      "Inserted another chunk...; Took 14.478227376937866 seconds\n",
      "Inserted another chunk...; Took 15.141005754470825 seconds\n",
      "Inserted another chunk...; Took 14.168020486831665 seconds\n",
      "Inserted another chunk...; Took 14.824027299880981 seconds\n",
      "Inserted another chunk...; Took 14.232245445251465 seconds\n",
      "Inserted another chunk...; Took 15.072717905044556 seconds\n",
      "Inserted another chunk...; Took 14.304361343383789 seconds\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jxareas\\AppData\\Local\\Temp\\ipykernel_10000\\1509494939.py:7: DtypeWarning: Columns (6) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = next(df_iter)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserted another chunk...; Took 14.305968523025513 seconds\n",
      "Inserted another chunk...; Took 9.414040803909302 seconds\n"
     ]
    },
    {
     "ename": "StopIteration",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mStopIteration\u001B[0m                             Traceback (most recent call last)",
      "Cell \u001B[1;32mIn[23], line 7\u001B[0m\n\u001B[0;32m      4\u001B[0m \u001B[38;5;28;01mwhile\u001B[39;00m \u001B[38;5;28;01mTrue\u001B[39;00m:\n\u001B[0;32m      5\u001B[0m     t_start \u001B[38;5;241m=\u001B[39m time()\n\u001B[1;32m----> 7\u001B[0m     df \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mnext\u001B[39m(df_iter)\n\u001B[0;32m      9\u001B[0m     df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtpep_pickup_datetime\u001B[39m\u001B[38;5;124m'\u001B[39m] \u001B[38;5;241m=\u001B[39m pd\u001B[38;5;241m.\u001B[39mto_datetime(df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtpep_pickup_datetime\u001B[39m\u001B[38;5;124m'\u001B[39m])\n\u001B[0;32m     10\u001B[0m     df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtpep_dropoff_datetime\u001B[39m\u001B[38;5;124m'\u001B[39m] \u001B[38;5;241m=\u001B[39m pd\u001B[38;5;241m.\u001B[39mto_datetime(df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtpep_dropoff_datetime\u001B[39m\u001B[38;5;124m'\u001B[39m])\n",
      "File \u001B[1;32m~\\.conda\\envs\\deeplearner\\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[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21m__next__\u001B[39m(\u001B[38;5;28mself\u001B[39m) \u001B[38;5;241m-\u001B[39m\u001B[38;5;241m>\u001B[39m DataFrame:\n\u001B[0;32m   1697\u001B[0m     \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[1;32m-> 1698\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mget_chunk()\n\u001B[0;32m   1699\u001B[0m     \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mStopIteration\u001B[39;00m:\n\u001B[0;32m   1700\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mclose()\n",
      "File \u001B[1;32m~\\.conda\\envs\\deeplearner\\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[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mStopIteration\u001B[39;00m\n\u001B[0;32m   1809\u001B[0m     size \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mmin\u001B[39m(size, \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mnrows \u001B[38;5;241m-\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_currow)\n\u001B[1;32m-> 1810\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mread(nrows\u001B[38;5;241m=\u001B[39msize)\n",
      "File \u001B[1;32m~\\.conda\\envs\\deeplearner\\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[38;5;241m=\u001B[39m validate_integer(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mnrows\u001B[39m\u001B[38;5;124m\"\u001B[39m, nrows)\n\u001B[0;32m   1772\u001B[0m \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[0;32m   1773\u001B[0m     \u001B[38;5;66;03m# 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[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_engine\u001B[38;5;241m.\u001B[39mread(  \u001B[38;5;66;03m# type: ignore[attr-defined]\u001B[39;00m\n\u001B[0;32m   1779\u001B[0m         nrows\n\u001B[0;32m   1780\u001B[0m     )\n\u001B[0;32m   1781\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mException\u001B[39;00m:\n\u001B[0;32m   1782\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mclose()\n",
      "File \u001B[1;32m~\\.conda\\envs\\deeplearner\\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[38;5;28;01mtry\u001B[39;00m:\n\u001B[0;32m    229\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mlow_memory:\n\u001B[1;32m--> 230\u001B[0m         chunks \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_reader\u001B[38;5;241m.\u001B[39mread_low_memory(nrows)\n\u001B[0;32m    231\u001B[0m         \u001B[38;5;66;03m# destructive to chunks\u001B[39;00m\n\u001B[0;32m    232\u001B[0m         data \u001B[38;5;241m=\u001B[39m _concatenate_chunks(chunks)\n",
      "File \u001B[1;32m~\\.conda\\envs\\deeplearner\\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": [
    "# Inserting data\n",
    "from time import time\n",
    "\n",
    "while True:\n",
    "    t_start = time()\n",
    "\n",
    "    df = next(df_iter)\n",
    "\n",
    "    df['tpep_pickup_datetime'] = pd.to_datetime(df['tpep_pickup_datetime'])\n",
    "    df['tpep_dropoff_datetime'] = pd.to_datetime(df['tpep_dropoff_datetime'])\n",
    "\n",
    "    df.to_sql(name='yellow_taxi_data', con=engine, if_exists='append')\n",
    "\n",
    "    t_end = time()\n",
    "\n",
    "    print(f'Inserted another chunk...; Took {t_end - t_start} seconds')\n",
    "    "
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-03T22:22:12.697967100Z",
     "start_time": "2024-02-03T22:18:47.206769800Z"
    }
   },
   "id": "2ab21cd787e2d14f",
   "execution_count": 23
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "deep-learner",
   "language": "python",
   "display_name": "deep-learner"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}