NYC311_Data_Transformation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6783c466",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61e2bc5e",
   "metadata": {},
   "source": [
    "### 1. Create an engine that connects to PostgreSQL server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f1bd202e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import necessary packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine, inspect\n",
    "\n",
    "# Pass the connection string to a variable, conn_url\n",
    "conn_url = 'postgresql://postgres:123@localhost/NYC311'\n",
    "\n",
    "# Create an engine that connects to PostgreSQL server\n",
    "engine = create_engine(conn_url)\n",
    "\n",
    "# Establish a connection\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0438b69",
   "metadata": {},
   "source": [
    "### 2. Create 15 necessary tables to organize data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f6f2e439",
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "(psycopg2.errors.SyntaxError) syntax error at or near \"##\"\nLINE 28:  ## due date date(), ##\n          ^\n\n[SQL: \n\nCreate table Agency(\n Agency_id varchar(50),\n Agency_name char(50),\nprimary key(Agency_id)\n);\n\nCreate table Complaint(\n Complaint_id varchar(50),\n Complaint_Type char(50),\n Descriptor char(50),\n Status char(20),\nprimary key(Complaint_id)\n);\n\nCreate table Agency_Complaint(\n Agency_id varchar(50),\n Complaint_id varchar(50),\n Agency_Name char(50),\n Complaint_Type char(50),\nprimary key(Agency_id,Complaint_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\nCreate table Resolution(\n Resolution_id varchar(50),\n Resolution_Description char(500),\n ## due date date(), ##\nprimary key(Resolution_id);\n\nCreate table Service_Request(\n Unique_Key varchar(50),\n Open_data_channel_type char(20),\n Created_date date,\n Closed_date date,\n Agency_id varchar(50),\n Complaint_id varchar(50) ,\n Resolution_id varchar(50),\nprimary key(Unique_Key),\nforeign key (Agency_id) references Agency(Agency_id),\nforeign key (Complaint_id) references Complaint(Complaint_id),\nforeign key (Resolution_id) references Resolution (Resolution_id));\n\n\nCreate table Complaint_address(\n Complaint_id varchar(50),\n Incident_id varchar(50),\n Complaint_Type char(50),\n Borough char(50),\nprimary key(Incident_id,Complaint_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\nCreate table Incident_info(\n Incident_id varchar(50),\n Complaint_id varchar(50),\n Submitter_id varchar(50),\n Community_id varchar(50),\n City_id varchar(50),\n Geo_id varchar(50),\n Park_id varchar(50),\n Transportation_id varchar(50),\nprimary key(Incident_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\n\nCreate table Incident_Location_bySubmitter(\n Submitter_id int,\n incident_id varchar(50),\n Location_Type char(20),\n Incident_Address char(50),\n Street_Name char(30),\n Borough char(50),\nprimary key(Submitter_id),\nforeign key(incident_id) references Incident_info(incident_id));\n\n\nCreate table Incident_Location_byCommunity(\n Community_id varchar(50),\n Community_Board char(30),\n BBL char(50),\n Borough char(50),\nprimary key(Community_id));\n\n\nCreate table Incident_Location_byBroadInfo(\n Broad_id\tint,\n Address_Type char(50),\t\n Landmark char(50),\n Facility_Type char(20),\nprimary key(Broad_id));\n\nCreate table Incident_Location_byCity(\n City_id int,\n City char(50),\n Incident_Zip int,\nprimary key(City_id));\n\nCreate table Incident_Location_byGeographicCordinate(\n Geo_id int,\n Location varchar(50),\n Latitude numeric (25,20),\n Longitude numeric (25,20),\n x_coordinate_state_plane numeric (25,20),\n y_coordinate_state_plane numeric (25,20),\nprimary key(Geo_id));\n\n\nCreate table Incident_Location_byPark(\n Park_id int,\n Facility_Type char(20),\n Park_Facility_Name\tvarchar(20),\n Park_Borough char(20),\nprimary key(Park_id));\n\nCreate table Incident_Location_byTransportation(\n Transportation_id varchar(50),\n Incident_id varchar(50),\n Facility_Type char(20),\n Vehicle_Type char(20),\n Taxi_Company_Borough char(30),\t\n Taxi_Pick_Up_Location char(30),\nprimary key(Transportation_id,Incident_id));\n\nCreate table Incident_Location_byBridgeHwy(\n BridgeHwy_id varchar(50),\n Incident_id varchar(50),\n Bridge_Highway_Name varchar(50),\n Bridge_Highway_Direction varchar(50),\n Bridge_Highway_Segment varchar(50),\nprimary key(BridgeHwy_id,Incident_id));\n\n]\n(Background on this error at: https://sqlalche.me/e/14/f405)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mSyntaxError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1798\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1799\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1800\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mSyntaxError\u001b[0m: syntax error at or near \"##\"\nLINE 28:  ## due date date(), ##\n          ^\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/8w/6b66wxsn07g9170r88jjcs6h0000gn/T/ipykernel_56585/3178079233.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m    133\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    134\u001b[0m \u001b[0;31m# Execute the statement to create tables\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 135\u001b[0;31m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1269\u001b[0m             )\n\u001b[1;32m   1270\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1271\u001b[0;31m             return self._exec_driver_sql(\n\u001b[0m\u001b[1;32m   1272\u001b[0m                 \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1273\u001b[0m                 \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_exec_driver_sql\u001b[0;34m(self, statement, multiparams, params, execution_options, future)\u001b[0m\n\u001b[1;32m   1573\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1574\u001b[0m         \u001b[0mdialect\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1575\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1576\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1577\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_statement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1840\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1841\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1842\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1843\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1844\u001b[0m             )\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   2021\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2022\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2023\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   2024\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2025\u001b[0m                 )\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1797\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1798\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1799\u001b[0;31m                     self.dialect.do_execute(\n\u001b[0m\u001b[1;32m   1800\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1801\u001b[0m                     )\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    715\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    716\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 717\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    719\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (psycopg2.errors.SyntaxError) syntax error at or near \"##\"\nLINE 28:  ## due date date(), ##\n          ^\n\n[SQL: \n\nCreate table Agency(\n Agency_id varchar(50),\n Agency_name char(50),\nprimary key(Agency_id)\n);\n\nCreate table Complaint(\n Complaint_id varchar(50),\n Complaint_Type char(50),\n Descriptor char(50),\n Status char(20),\nprimary key(Complaint_id)\n);\n\nCreate table Agency_Complaint(\n Agency_id varchar(50),\n Complaint_id varchar(50),\n Agency_Name char(50),\n Complaint_Type char(50),\nprimary key(Agency_id,Complaint_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\nCreate table Resolution(\n Resolution_id varchar(50),\n Resolution_Description char(500),\n ## due date date(), ##\nprimary key(Resolution_id);\n\nCreate table Service_Request(\n Unique_Key varchar(50),\n Open_data_channel_type char(20),\n Created_date date,\n Closed_date date,\n Agency_id varchar(50),\n Complaint_id varchar(50) ,\n Resolution_id varchar(50),\nprimary key(Unique_Key),\nforeign key (Agency_id) references Agency(Agency_id),\nforeign key (Complaint_id) references Complaint(Complaint_id),\nforeign key (Resolution_id) references Resolution (Resolution_id));\n\n\nCreate table Complaint_address(\n Complaint_id varchar(50),\n Incident_id varchar(50),\n Complaint_Type char(50),\n Borough char(50),\nprimary key(Incident_id,Complaint_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\nCreate table Incident_info(\n Incident_id varchar(50),\n Complaint_id varchar(50),\n Submitter_id varchar(50),\n Community_id varchar(50),\n City_id varchar(50),\n Geo_id varchar(50),\n Park_id varchar(50),\n Transportation_id varchar(50),\nprimary key(Incident_id),\nforeign key(Complaint_id) references Complaint(Complaint_id));\n\n\nCreate table Incident_Location_bySubmitter(\n Submitter_id int,\n incident_id varchar(50),\n Location_Type char(20),\n Incident_Address char(50),\n Street_Name char(30),\n Borough char(50),\nprimary key(Submitter_id),\nforeign key(incident_id) references Incident_info(incident_id));\n\n\nCreate table Incident_Location_byCommunity(\n Community_id varchar(50),\n Community_Board char(30),\n BBL char(50),\n Borough char(50),\nprimary key(Community_id));\n\n\nCreate table Incident_Location_byBroadInfo(\n Broad_id\tint,\n Address_Type char(50),\t\n Landmark char(50),\n Facility_Type char(20),\nprimary key(Broad_id));\n\nCreate table Incident_Location_byCity(\n City_id int,\n City char(50),\n Incident_Zip int,\nprimary key(City_id));\n\nCreate table Incident_Location_byGeographicCordinate(\n Geo_id int,\n Location varchar(50),\n Latitude numeric (25,20),\n Longitude numeric (25,20),\n x_coordinate_state_plane numeric (25,20),\n y_coordinate_state_plane numeric (25,20),\nprimary key(Geo_id));\n\n\nCreate table Incident_Location_byPark(\n Park_id int,\n Facility_Type char(20),\n Park_Facility_Name\tvarchar(20),\n Park_Borough char(20),\nprimary key(Park_id));\n\nCreate table Incident_Location_byTransportation(\n Transportation_id varchar(50),\n Incident_id varchar(50),\n Facility_Type char(20),\n Vehicle_Type char(20),\n Taxi_Company_Borough char(30),\t\n Taxi_Pick_Up_Location char(30),\nprimary key(Transportation_id,Incident_id));\n\nCreate table Incident_Location_byBridgeHwy(\n BridgeHwy_id varchar(50),\n Incident_id varchar(50),\n Bridge_Highway_Name varchar(50),\n Bridge_Highway_Direction varchar(50),\n Bridge_Highway_Segment varchar(50),\nprimary key(BridgeHwy_id,Incident_id));\n\n]\n(Background on this error at: https://sqlalche.me/e/14/f405)"
     ]
    }
   ],
   "source": [
    "\n",
    "stmt = \"\"\"\n",
    "\n",
    "Create table Agency(\n",
    " Agency_id varchar(50),\n",
    " Agency_name char(50),\n",
    "primary key(Agency_id)\n",
    ");\n",
    "\n",
    "Create table Complaint(\n",
    " Complaint_id varchar(50),\n",
    " Complaint_Type char(50),\n",
    " Descriptor char(50),\n",
    " Status char(20),\n",
    "primary key(Complaint_id)\n",
    ");\n",
    "\n",
    "Create table Agency_Complaint(\n",
    " Agency_id varchar(50),\n",
    " Complaint_id varchar(50),\n",
    " Agency_Name char(50),\n",
    " Complaint_Type char(50),\n",
    "primary key(Agency_id,Complaint_id),\n",
    "foreign key(Complaint_id) references Complaint(Complaint_id));\n",
    "\n",
    "Create table Resolution(\n",
    " Resolution_id varchar(50),\n",
    " Resolution_Description char(500),\n",
    " ## due date date(), ##\n",
    "primary key(Resolution_id);\n",
    "\n",
    "Create table Service_Request(\n",
    " Unique_Key varchar(50),\n",
    " Open_data_channel_type char(20),\n",
    " Created_date date,\n",
    " Closed_date date,\n",
    " Agency_id varchar(50),\n",
    " Complaint_id varchar(50) ,\n",
    " Resolution_id varchar(50),\n",
    "primary key(Unique_Key),\n",
    "foreign key (Agency_id) references Agency(Agency_id),\n",
    "foreign key (Complaint_id) references Complaint(Complaint_id),\n",
    "foreign key (Resolution_id) references Resolution (Resolution_id));\n",
    "\n",
    "\n",
    "Create table Complaint_address(\n",
    " Complaint_id varchar(50),\n",
    " Incident_id varchar(50),\n",
    " Complaint_Type char(50),\n",
    " Borough char(50),\n",
    "primary key(Incident_id,Complaint_id),\n",
    "foreign key(Complaint_id) references Complaint(Complaint_id));\n",
    "\n",
    "Create table Incident_info(\n",
    " Incident_id varchar(50),\n",
    " Complaint_id varchar(50),\n",
    " Submitter_id varchar(50),\n",
    " Community_id varchar(50),\n",
    " City_id varchar(50),\n",
    " Geo_id varchar(50),\n",
    " Park_id varchar(50),\n",
    " Transportation_id varchar(50),\n",
    "primary key(Incident_id),\n",
    "foreign key(Complaint_id) references Complaint(Complaint_id));\n",
    "\n",
    "\n",
    "Create table Incident_Location_bySubmitter(\n",
    " Submitter_id int,\n",
    " incident_id varchar(50),\n",
    " Location_Type char(20),\n",
    " Incident_Address char(50),\n",
    " Street_Name char(30),\n",
    " Borough char(50),\n",
    "primary key(Submitter_id),\n",
    "foreign key(incident_id) references Incident_info(incident_id));\n",
    "\n",
    "\n",
    "Create table Incident_Location_byCommunity(\n",
    " Community_id varchar(50),\n",
    " Community_Board char(30),\n",
    " BBL char(50),\n",
    " Borough char(50),\n",
    "primary key(Community_id));\n",
    "\n",
    "\n",
    "Create table Incident_Location_byBroadInfo(\n",
    " Broad_id\tint,\n",
    " Address_Type char(50),\t\n",
    " Landmark char(50),\n",
    "primary key(Broad_id));\n",
    "\n",
    "Create table Incident_Location_byCity(\n",
    " City_id int,\n",
    " City char(50),\n",
    " Incident_Zip int,\n",
    "primary key(City_id));\n",
    "\n",
    "Create table Incident_Location_byGeographicCordinate(\n",
    " Geo_id int,\n",
    " Location varchar(50),\n",
    " Latitude numeric (25,20),\n",
    " Longitude numeric (25,20),\n",
    " x_coordinate_state_plane numeric (25,20),\n",
    " y_coordinate_state_plane numeric (25,20),\n",
    "primary key(Geo_id));\n",
    "\n",
    "\n",
    "Create table Incident_Location_byPark(\n",
    " Park_id int,\n",
    " Facility_Type char(20),\n",
    " Park_Facility_Name\tvarchar(20),\n",
    " Park_Borough char(20),\n",
    "primary key(Park_id));\n",
    "\n",
    "Create table Incident_Location_byTransportation(\n",
    " Transportation_id varchar(50),\n",
    " Incident_id varchar(50),\n",
    " Facility_Type char(20),\n",
    " Vehicle_Type char(20),\n",
    " Taxi_Company_Borough char(30),\t\n",
    " Taxi_Pick_Up_Location char(30),\n",
    "primary key(Transportation_id,Incident_id));\n",
    "\n",
    "Create table Incident_Location_byBridgeHwy(\n",
    " BridgeHwy_id varchar(50),\n",
    " Incident_id varchar(50),\n",
    " Bridge_Highway_Name varchar(50),\n",
    " Bridge_Highway_Direction varchar(50),\n",
    " Bridge_Highway_Segment varchar(50),\n",
    "primary key(BridgeHwy_id,Incident_id));\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "# Execute the statement to create tables\n",
    "connection.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e175f7a",
   "metadata": {},
   "source": [
    "### 3.0 Create a tempeoray table that includes all the data information from the csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "587cfbb5",
   "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>unique key</th>\n",
       "      <th>created date</th>\n",
       "      <th>closed date</th>\n",
       "      <th>agency</th>\n",
       "      <th>agency name</th>\n",
       "      <th>complaint type</th>\n",
       "      <th>descriptor</th>\n",
       "      <th>location type</th>\n",
       "      <th>incident zip</th>\n",
       "      <th>incident address</th>\n",
       "      <th>...</th>\n",
       "      <th>vehicle type</th>\n",
       "      <th>taxi company borough</th>\n",
       "      <th>taxi pick up location</th>\n",
       "      <th>bridge highway name</th>\n",
       "      <th>bridge highway direction</th>\n",
       "      <th>road ramp</th>\n",
       "      <th>bridge highway segment</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29614410</td>\n",
       "      <td>1/1/15 0:00</td>\n",
       "      <td>1/6/15 0:00</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>10470</td>\n",
       "      <td>660 NEREID AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.898995</td>\n",
       "      <td>-73.855719</td>\n",
       "      <td>(40.898994888191034, -73.85571871543965)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29608104</td>\n",
       "      <td>1/1/15 0:00</td>\n",
       "      <td>1/8/15 0:00</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>11212</td>\n",
       "      <td>1803 PITKIN AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.670334</td>\n",
       "      <td>-73.906425</td>\n",
       "      <td>(40.670333991079154, -73.9064253399482)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29608861</td>\n",
       "      <td>1/1/15 0:00</td>\n",
       "      <td>1/4/15 0:00</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>11221</td>\n",
       "      <td>558 LEXINGTON AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.689168</td>\n",
       "      <td>-73.937878</td>\n",
       "      <td>(40.6891679550618, -73.9378781329754)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29608914</td>\n",
       "      <td>1/1/15 0:00</td>\n",
       "      <td>1/5/15 0:00</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>10468</td>\n",
       "      <td>2683 MORRIS AVENUE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.867598</td>\n",
       "      <td>-73.895883</td>\n",
       "      <td>(40.86759767804488, -73.89588323198846)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29608966</td>\n",
       "      <td>1/1/15 0:00</td>\n",
       "      <td>1/3/15 0:00</td>\n",
       "      <td>HPD</td>\n",
       "      <td>Department of Housing Preservation and Develop...</td>\n",
       "      <td>HEAT/HOT WATER</td>\n",
       "      <td>ENTIRE BUILDING</td>\n",
       "      <td>RESIDENTIAL BUILDING</td>\n",
       "      <td>11209</td>\n",
       "      <td>9511 SHORE ROAD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.616370</td>\n",
       "      <td>-74.038267</td>\n",
       "      <td>(40.61637026983262, -74.03826694457253)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique key created date  closed date agency  \\\n",
       "0    29614410  1/1/15 0:00  1/6/15 0:00    HPD   \n",
       "1    29608104  1/1/15 0:00  1/8/15 0:00    HPD   \n",
       "2    29608861  1/1/15 0:00  1/4/15 0:00    HPD   \n",
       "3    29608914  1/1/15 0:00  1/5/15 0:00    HPD   \n",
       "4    29608966  1/1/15 0:00  1/3/15 0:00    HPD   \n",
       "\n",
       "                                         agency name  complaint type  \\\n",
       "0  Department of Housing Preservation and Develop...  HEAT/HOT WATER   \n",
       "1  Department of Housing Preservation and Develop...  HEAT/HOT WATER   \n",
       "2  Department of Housing Preservation and Develop...  HEAT/HOT WATER   \n",
       "3  Department of Housing Preservation and Develop...  HEAT/HOT WATER   \n",
       "4  Department of Housing Preservation and Develop...  HEAT/HOT WATER   \n",
       "\n",
       "        descriptor         location type  incident zip      incident address  \\\n",
       "0  ENTIRE BUILDING  RESIDENTIAL BUILDING         10470     660 NEREID AVENUE   \n",
       "1  ENTIRE BUILDING  RESIDENTIAL BUILDING         11212    1803 PITKIN AVENUE   \n",
       "2  ENTIRE BUILDING  RESIDENTIAL BUILDING         11221  558 LEXINGTON AVENUE   \n",
       "3  ENTIRE BUILDING  RESIDENTIAL BUILDING         10468    2683 MORRIS AVENUE   \n",
       "4  ENTIRE BUILDING  RESIDENTIAL BUILDING         11209       9511 SHORE ROAD   \n",
       "\n",
       "   ... vehicle type taxi company borough taxi pick up location  \\\n",
       "0  ...          NaN                  NaN                   NaN   \n",
       "1  ...          NaN                  NaN                   NaN   \n",
       "2  ...          NaN                  NaN                   NaN   \n",
       "3  ...          NaN                  NaN                   NaN   \n",
       "4  ...          NaN                  NaN                   NaN   \n",
       "\n",
       "   bridge highway name  bridge highway direction road ramp  \\\n",
       "0                  NaN                       NaN       NaN   \n",
       "1                  NaN                       NaN       NaN   \n",
       "2                  NaN                       NaN       NaN   \n",
       "3                  NaN                       NaN       NaN   \n",
       "4                  NaN                       NaN       NaN   \n",
       "\n",
       "  bridge highway segment   latitude  longitude  \\\n",
       "0                    NaN  40.898995 -73.855719   \n",
       "1                    NaN  40.670334 -73.906425   \n",
       "2                    NaN  40.689168 -73.937878   \n",
       "3                    NaN  40.867598 -73.895883   \n",
       "4                    NaN  40.616370 -74.038267   \n",
       "\n",
       "                                   location  \n",
       "0  (40.898994888191034, -73.85571871543965)  \n",
       "1   (40.670333991079154, -73.9064253399482)  \n",
       "2     (40.6891679550618, -73.9378781329754)  \n",
       "3   (40.86759767804488, -73.89588323198846)  \n",
       "4   (40.61637026983262, -74.03826694457253)  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CREATE TABLE TEMP\n",
    "import pandas as pd\n",
    "df = pd.read_csv('311_Service_Request_from_2015_to_Present.csv')\n",
    "df.columns = [c.lower() for c in df.columns] \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98cb9ddc",
   "metadata": {},
   "source": [
    "### 3.1 Transform columns into appropriate name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "09da2cfe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1652 entries, 0 to 1651\n",
      "Data columns (total 41 columns):\n",
      " #   Column                          Non-Null Count  Dtype  \n",
      "---  ------                          --------------  -----  \n",
      " 0   unique_key                      1652 non-null   int64  \n",
      " 1   created_date                    1652 non-null   object \n",
      " 2   closed_date                     1647 non-null   object \n",
      " 3   agency                          1652 non-null   object \n",
      " 4   agency_name                     1652 non-null   object \n",
      " 5   complaint_type                  1652 non-null   object \n",
      " 6   descriptor                      1652 non-null   object \n",
      " 7   location_type                   1652 non-null   object \n",
      " 8   incident_zip                    1652 non-null   int64  \n",
      " 9   incident_address                1652 non-null   object \n",
      " 10  street_name                     1652 non-null   object \n",
      " 11  cross_street_1                  1648 non-null   object \n",
      " 12  cross_street_2                  1647 non-null   object \n",
      " 13  intersection_street_1           0 non-null      float64\n",
      " 14  intersection_street_2           0 non-null      float64\n",
      " 15  address_type                    1652 non-null   object \n",
      " 16  city                            1652 non-null   object \n",
      " 17  landmark                        0 non-null      float64\n",
      " 18  x                               0 non-null      float64\n",
      " 19  status                          1652 non-null   object \n",
      " 20  due_date                        16 non-null     object \n",
      " 21  resolution_description          1652 non-null   object \n",
      " 22  resolution_action_updated_date  1652 non-null   object \n",
      " 23  community_board                 1652 non-null   object \n",
      " 24  bbl                             1641 non-null   float64\n",
      " 25  borough                         1652 non-null   object \n",
      " 26  x_coordinate_(state_plane)      1652 non-null   object \n",
      " 27  y_coordinate_(state_plane)      1652 non-null   object \n",
      " 28  open_data_channel_type          1652 non-null   object \n",
      " 29  park_facility_name              1652 non-null   object \n",
      " 30  park_borough                    1652 non-null   object \n",
      " 31  vehicle_type                    0 non-null      float64\n",
      " 32  taxi_company_borough            0 non-null      float64\n",
      " 33  taxi_pick_up_location           0 non-null      float64\n",
      " 34  bridge_highway_name             0 non-null      float64\n",
      " 35  bridge_highway_direction        0 non-null      float64\n",
      " 36  road_ramp                       0 non-null      float64\n",
      " 37  bridge_highway_segment          0 non-null      float64\n",
      " 38  latitude                        1652 non-null   float64\n",
      " 39  longitude                       1652 non-null   float64\n",
      " 40  location                        1652 non-null   object \n",
      "dtypes: float64(14), int64(2), object(25)\n",
      "memory usage: 529.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.columns=df.columns.str.replace(\" \", \"_\")\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40960e95",
   "metadata": {},
   "source": [
    "### 4. Insert data into the 15 tables in the Postgres database \n",
    "1.Agency table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fac1b3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Agency\n",
    "temp_agency_df = pd.DataFrame(df.agency_name.unique(), columns=['agency_name'])\n",
    "temp_agency_df.insert(0, 'agency_id', range(1, 1 + len(temp_agency_df)))\n",
    "\n",
    "temp_agency_df.to_sql(name='agency', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6ef0af9",
   "metadata": {},
   "source": [
    "2.Complaint Table <br>\n",
    "3.Agency_Complaint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bae8564a",
   "metadata": {
    "id": "bae8564a",
    "outputId": "0546cdf0-573e-4dea-9bd4-49d48679b7d7"
   },
   "outputs": [],
   "source": [
    "# Create table Complaint\n",
    "temp_complaint_df = pd.DataFrame(df.complaint_type.unique(), columns=['complaint_type'])\n",
    "temp_complaint_df.insert(0, 'complaint_id', range(1, 1 + len(temp_complaint_df)))\n",
    "\n",
    "complaint_id_list = [temp_complaint_df.complaint_id[temp_complaint_df.complaint_type == i].values[0] for i in df.complaint_type]\n",
    "df.insert(0, 'complaint_id', complaint_id_list)\n",
    "\n",
    "df[['complaint_id', 'complaint_type', 'descriptor']]. \\\n",
    "    drop_duplicates(subset = [\"complaint_id\"]).to_sql(name='complaint', con=engine, if_exists='append', index=False)\n",
    "\n",
    "\n",
    "# Create table Agency_Complaint\n",
    "agency_id_list = [temp_agency_df.agency_id[temp_agency_df.agency_name == i].values[0] for i in df.agency_name]\n",
    "df.insert(0, 'agency_id', agency_id_list)\n",
    "\n",
    "df[['agency_id','complaint_id', 'agency_name','complaint_type']]. \\\n",
    "    drop_duplicates().to_sql(name='agency_complaint', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0757eab",
   "metadata": {},
   "source": [
    "4.Resolution table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d42ff70e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table resolution\n",
    "df = df.fillna(value = \"NA\")\n",
    "temp_resolution_df = pd.DataFrame(df.resolution_description.unique(), columns=['resolution_description'])\n",
    "temp_resolution_df.insert(0, 'resolution_id', range(1, 1 + len(temp_resolution_df)))\n",
    "temp_resolution_df = temp_resolution_df.fillna(value = 'NA')\n",
    "\n",
    "resolution_id_list = [temp_resolution_df.resolution_id[temp_resolution_df.resolution_description == i].values[0] for i in df.resolution_description]\n",
    "df.insert(0, 'resolution_id', resolution_id_list)\n",
    "\n",
    "df[['resolution_id', 'resolution_description']]. \\\n",
    "    drop_duplicates().to_sql(name='resolution', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b2c181e",
   "metadata": {},
   "source": [
    "5.Service_Request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1eca47d5",
   "metadata": {
    "id": "1eca47d5",
    "outputId": "5bc01b99-6908-4cce-c3c1-d1392997956c"
   },
   "outputs": [],
   "source": [
    "# Create table Service_Request\n",
    "Service_Request_df = df[['unique_key','open_data_channel_type','created_date','closed_date','agency_id','complaint_id', 'resolution_id']]\n",
    "Service_Request_df.to_sql(name='service_request', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a5b3346",
   "metadata": {},
   "source": [
    "6.Complaint_address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "92176871",
   "metadata": {
    "id": "92176871",
    "outputId": "c9bb8fe8-1666-45c9-8d8c-8a83c1ac10d5"
   },
   "outputs": [],
   "source": [
    "# Create table Complaint_address\n",
    "df.insert(0, 'incident_id', range(1, 1 + len(df)))\n",
    "\n",
    "complaint_address_df = df[['complaint_id','incident_id','complaint_type','borough']]\n",
    "complaint_address_df.to_sql(name='complaint_address', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5526556a",
   "metadata": {},
   "source": [
    "7.Incident_Location_bySubmitter: no data available in current dataset, but the variables in the table are showcased in  the data dictionary/metadata\n",
    "- we use table Incident_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b96978df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Incident_info\n",
    "incident_info_df = df[['complaint_id','incident_id','community_id','city_id','geo_id','park_id','transportation_id']]\n",
    "incident_info_df.to_sql(name='incident_info', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b20f37a9",
   "metadata": {},
   "source": [
    "8.Incident_Location_byCommunity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1ce269a0",
   "metadata": {
    "id": "1ce269a0",
    "outputId": "87be3543-0bb7-48a7-a848-6a39dd5f0008"
   },
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byCommunity\n",
    "temp_ilc_df = pd.DataFrame(df.community_board.unique(), columns=['community_board'])\n",
    "temp_ilc_df.insert(0, 'community_id', range(1, 1 + len(temp_ilc_df)))\n",
    "\n",
    "community_id_list = [temp_ilc_df.community_id[temp_ilc_df.community_board == i].values[0] for i in df.community_board]\n",
    "df.insert(0, 'community_id', community_id_list)\n",
    "\n",
    "df[['community_id', 'community_board', 'bbl','borough']]. \\\n",
    "    drop_duplicates(subset = [\"community_id\"]).to_sql(name='incident_location_bycommunity', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31699e9c",
   "metadata": {},
   "source": [
    "9. Incident_Location_byBroadInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "43d2359d",
   "metadata": {
    "id": "43d2359d",
    "outputId": "b894c1f8-b195-4e15-9187-21ebbc50ec3a"
   },
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byBroadInfo\n",
    "temp_ilb_df = pd.DataFrame(df.address_type.unique(), columns=['address_type'])\n",
    "temp_ilb_df.insert(0, 'broad_id', range(1, 1 + len(temp_ilb_df)))\n",
    "\n",
    "address_id_list = [temp_ilb_df.broad_id[temp_ilb_df.address_type == i].values[0] for i in df.address_type]\n",
    "df.insert(0, 'broad_id', address_id_list)\n",
    "\n",
    "# since facility_type did not exist in the current dataset, but it was showcased in the dictionary. \n",
    "# therefore, we will add this column with no data\n",
    "df['facility_type']= np.nan\n",
    "df[['broad_id', 'address_type', 'landmark','facility_type']]. \\\n",
    "    drop_duplicates(subset = [\"broad_id\"]).to_sql(name='incident_location_bybroadinfo', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f0499cc",
   "metadata": {},
   "source": [
    "11.Incident_Location_byCity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ecde48d3",
   "metadata": {
    "id": "ecde48d3",
    "outputId": "b524d3eb-3817-4ef9-96a9-c6aca77f0692"
   },
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byCity\n",
    "temp_ilcity_df = pd.DataFrame(df.city.unique(), columns=['city'])\n",
    "temp_ilcity_df.insert(0, 'city_id', range(1, 1 + len(temp_ilcity_df)))\n",
    "\n",
    "city_id_list = [temp_ilcity_df.city_id[temp_ilcity_df.city == i].values[0] for i in df.city]\n",
    "df.insert(0, 'city_id', city_id_list)\n",
    "\n",
    "df[['city_id', 'city', 'incident_zip']]. \\\n",
    "    drop_duplicates(subset = [\"city_id\"]).to_sql(name='incident_location_bycity', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8123bf4",
   "metadata": {},
   "source": [
    "12.Incident_Location_byGeographicCordinate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4a3c7edf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byGeographicCordinate\n",
    "df['location'] = df['location'].astype(str)\n",
    "\n",
    "temp_ilg_df = pd.DataFrame(df.location.unique(), columns=['location'])\n",
    "temp_ilg_df.insert(0, 'geo_id', range(1, 1 + len(temp_ilg_df)))\n",
    "\n",
    "geo_id_list = [temp_ilg_df.geo_id[temp_ilg_df.location == i].values[0] for i in df.location]\n",
    "df.insert(0, 'geo_id', geo_id_list)\n",
    "\n",
    "df[['geo_id', 'location', 'latitude','longitude','x_coordinate_(state_plane)','y_coordinate_(state_plane)']]. \\\n",
    "    drop_duplicates(subset = [\"geo_id\"]).to_sql(name='incident_location_bygeographiccordinate', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5fe8620",
   "metadata": {},
   "source": [
    "13.Incident_Location_byPark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "25505771",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byPark\n",
    "temp_ilp_df = pd.DataFrame(df.park_facility_name.unique(), columns=['park_facility_name'])\n",
    "temp_ilp_df.insert(0, 'park_id', range(1, 1 + len(temp_ilp_df)))\n",
    "\n",
    "park_id_list = [temp_ilp_df.park_id[temp_ilp_df.park_facility_name == i].values[0] for i in df.park_facility_name]\n",
    "df.insert(0, 'park_id', park_id_list)\n",
    "\n",
    "df[['park_id', 'facility_type', 'park_facility_name','park_borough']]. \\\n",
    "    drop_duplicates(subset = [\"park_id\"]).to_sql(name='incident_location_bypark', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dea1379f",
   "metadata": {},
   "source": [
    "14.Incident_Location_byTransportation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "38332442",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byTransportation\n",
    "temp_ilt_df = pd.DataFrame(df.vehicle_type.unique(), columns=['vehicle_type'])\n",
    "temp_ilt_df.insert(0, 'transportation_id', range(1, 1 + len(temp_ilt_df)))\n",
    "\n",
    "transportation_id_list = [temp_ilt_df.transportation_id[temp_ilt_df.vehicle_type == i].values[0] for i in df.vehicle_type]\n",
    "df.insert(0, 'transportation_id', transportation_id_list)\n",
    "\n",
    "df[['transportation_id', 'incident_id','facility_type', 'vehicle_type','taxi_company_borough','taxi_pick_up_location']]. \\\n",
    "    drop_duplicates(subset = [\"transportation_id\"]).to_sql(name='incident_location_bytransportation', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0b905c0",
   "metadata": {},
   "source": [
    "15.Incident_Location_byBridgeHwy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2d6feb2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table Incident_Location_byBridgeHwy\n",
    "temp_ilbridge_df = pd.DataFrame(df.bridge_highway_name.unique(), columns=['bridge_highway_name'])\n",
    "temp_ilbridge_df.insert(0, 'bridgehwy_id', range(1, 1 + len(temp_ilbridge_df)))\n",
    "\n",
    "bridgehwy_id_list = [temp_ilbridge_df.bridgehwy_id[temp_ilbridge_df.bridge_highway_name == i].values[0] for i in df.bridge_highway_name]\n",
    "df.insert(0, 'bridgehwy_id', bridgehwy_id_list)\n",
    "\n",
    "df[['bridgehwy_id', 'incident_id','bridge_highway_name', 'bridge_highway_direction','bridge_highway_segment']]. \\\n",
    "    drop_duplicates(subset = [\"bridgehwy_id\"]).to_sql(name='incident_location_bybridgehwy', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "344d9c69",
   "metadata": {},
   "source": [
    "\n",
    "- Now we have successfully imported the data from the csv file into the Postgres Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32874f74",
   "metadata": {},
   "source": [
    "### 5. since tableau public does not support connection with PostgreSQL, we need to extract the 15 tables and save it to a xml file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "97499e4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create an engine instance\n",
    "engine = create_engine(conn_url)\n",
    "\n",
    "# Establish a connection\n",
    "connection = engine.connect()\n",
    "\n",
    "# Read data from PostgreSQL database table and load into a DataFrame instance\n",
    "agency = pd.read_sql(\"select * from \\\"agency\\\"\",connection);\n",
    "agency_complaint = pd.read_sql(\"select * from \\\"agency_complaint\\\"\",connection);\n",
    "complaint = pd.read_sql(\"select * from \\\"complaint\\\"\",connection);\n",
    "complaint_address = pd.read_sql(\"select * from \\\"complaint_address\\\"\",connection);\n",
    "incident_info = pd.read_sql(\"select * from \\\"incident_info\\\"\",connection);\n",
    "incident_location_bybridgehwy = pd.read_sql(\"select * from \\\"incident_location_bybridgehwy\\\"\",connection);\n",
    "incident_location_bybroadinfo = pd.read_sql(\"select * from \\\"incident_location_bybroadinfo\\\"\",connection);\n",
    "incident_location_bycity = pd.read_sql(\"select * from \\\"incident_location_bycity\\\"\",connection);\n",
    "incident_location_bycommunity = pd.read_sql(\"select * from \\\"incident_location_bycommunity\\\"\",connection);\n",
    "incident_location_bygeographiccordinate = pd.read_sql(\"select * from \\\"incident_location_bygeographiccordinate\\\"\",connection);\n",
    "incident_location_bypark = pd.read_sql(\"select * from \\\"incident_location_bypark\\\"\",connection);\n",
    "incident_location_bytransportation = pd.read_sql(\"select * from \\\"incident_location_bytransportation\\\"\",connection);\n",
    "resolutin = pd.read_sql(\"select * from \\\"resolution\\\"\",connection);\n",
    "service_request = pd.read_sql(\"select * from \\\"service_request\\\"\",connection);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ef6ceb40",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip3 install xlsxwriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "9795be1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
    "writer = pd.ExcelWriter('tables.xlsx', engine='xlsxwriter')\n",
    "\n",
    "# Write each dataframe to a different worksheet.\n",
    "agency.to_excel(writer, sheet_name='Sheet1')\n",
    "agency_complaint.to_excel(writer, sheet_name='Sheet2')\n",
    "complaint.to_excel(writer, sheet_name='Sheet3')\n",
    "complaint_address.to_excel(writer, sheet_name='Sheet4')\n",
    "incident_info.to_excel(writer, sheet_name='Sheet5')\n",
    "incident_location_bybridgehwy.to_excel(writer, sheet_name='Sheet6')\n",
    "incident_location_bybroadinfo.to_excel(writer, sheet_name='Sheet7')\n",
    "incident_location_bycity.to_excel(writer, sheet_name='Sheet8')\n",
    "incident_location_bycommunity.to_excel(writer, sheet_name='Sheet9')\n",
    "incident_location_bygeographiccordinate.to_excel(writer, sheet_name='Sheet10')\n",
    "incident_location_bypark.to_excel(writer, sheet_name='Sheet11')\n",
    "incident_location_bytransportation.to_excel(writer, sheet_name='Sheet12')\n",
    "resolutin.to_excel(writer, sheet_name='Sheet13')\n",
    "service_request.to_excel(writer, sheet_name='Sheet14')\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad607530",
   "metadata": {},
   "source": [
    "### 6. disconnect the database when we are done with the data insertion "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "255011e9",
   "metadata": {
    "id": "255011e9"
   },
   "outputs": [],
   "source": [
    "# Import necessary packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Pass the connection string to a variable, conn_url\n",
    "conn_url = 'postgresql://postgres:123@localhost:5432/NYC311'\n",
    "\n",
    "# Create an engine that connects to PostgreSQL server\n",
    "engine = create_engine(conn_url)\n",
    "\n",
    "# Establish a connection\n",
    "connection = engine.connect()\n",
    "\n",
    "# Pass the SQL statements that create all views\n",
    "stmt = \"\"\"\n",
    "            drop table agency_complaint;\n",
    "            drop table complaint_address;\n",
    "            drop table incident_location_bycommunity;\n",
    "            drop table incident_location_bybroadinfo;\n",
    "            drop table incident_location_bycity;\n",
    "            drop table incident_location_bygeographiccordinate;\n",
    "            drop table incident_location_bypark;\n",
    "            drop table incident_location_bytransportation;\n",
    "            drop table incident_location_bybridgehwy;\n",
    "            drop table incident_info; \n",
    "            drop table service_request;\n",
    "            drop table agency;\n",
    "            drop table resolution;\n",
    "            drop table complaint;\n",
    "\n",
    "        \"\"\"\n",
    "\n",
    "# Execute the statement to create tables\n",
    "connection.execute(stmt)\n",
    "\n",
    "#close the connection\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67a6408a",
   "metadata": {},
   "source": [
    "### Next Step: use dashboard showcase some trends and results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "642551f1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "APAN5310_PROJECT_Data_Transformation.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}