06-sql-data-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load data (San Frasisco Police Department)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. download the sample data from the below website  \n",
    "**[SFData](https://data.sfgov.org/Public-Safety/Police-Department-Incident-Reports-Historical-2003/tmnf-yvry/data)**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. save the csv file in the rdbms/databases/csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. prepare the database and load data  \n",
    "`$ docker run -d -v /var/run/docker.sock:/tmp/docker.sock -v /etc/hosts:/tmp/hosts asami76/docker-hoster`  \n",
    "`$ docker-compose -f postgresql.yml up`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Attach to the postgresql container shell and load the csv into a new database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "# psql -U postgres\n",
    "postgres=# CREATE DATABASE sfpolice;\n",
    "poatgres=# \\c sfpolice\n",
    "poatgres=# CREATE TABLE\n",
    "    police_incident_reports (\n",
    "        pd_id BIGINT,\n",
    "        IncidentNum VARCHAR(10),\n",
    "        \"Incident Code\" VARCHAR(10),\n",
    "        Category VARCHAR(50),\n",
    "        Descript VARCHAR(100),\n",
    "        DayOfWeek VARCHAR(10),\n",
    "        Date DATE,\n",
    "        Time TIME,\n",
    "        PdDistrict VARCHAR(10),\n",
    "        Resolution VARCHAR(50),\n",
    "        Address VARCHAR(100),\n",
    "        X NUMERIC(9, 6),\n",
    "        Y NUMERIC(9, 6),\n",
    "        location VARCHAR(55),\n",
    "        \"SF Find Neighborhoods 2 2\" FLOAT,\n",
    "        \"Current Police Districts 2 2\" INT,\n",
    "        \"Current Supervisor Districts 2 2\" INT,\n",
    "        \"Analysis Neighborhoods 2 2\" INT,\n",
    "        \"DELETE - Fire Prevention Districts 2 2\" INT,\n",
    "        \"DELETE - Police Districts 2 2\" INT,\n",
    "        \"DELETE - Supervisor Districts 2 2\" INT,\n",
    "        \"DELETE - Zip Codes 2 2\" INT,\n",
    "        \"DELETE - Neighborhoods 2 2\" INT,\n",
    "        \"DELETE - 2017 Fix It Zones 2 2\" INT,\n",
    "        \"Civic Center Harm Reduction Project Boundary 2 2\" INT,\n",
    "        \"Fix It Zones as of 2017-11-06 2 2\" INT,\n",
    "        \"DELETE - HSOC Zones 2 2\" INT,\n",
    "        \"Fix It Zones as of 2018-02-07 2 2\" INT,\n",
    "        \"CBD, BID and GBD Boundaries as of 2017 2 2\" INT,\n",
    "        \"Areas of Vulnerability, 2016 2 2\" INT,\n",
    "        \"Central Market/Tenderloin Boundary 2 2\" INT,\n",
    "        \"Central Market/Tenderloin Boundary Polygon - Updated 2 2\" INT,\n",
    "        \"HSOC Zones as of 2018-06-05 2 2\" INT,\n",
    "        \"OWED Public Spaces 2 2\" INT,\n",
    "        \"Neighborhoods 2\" INT\n",
    "    );\n",
    "postgres=# \\COPY police_incident_reports FROM '/usr/databases/csv/Police_Department_Incident_Reports__Historical_2003_to_May_2018.csv'  DELIMITER ',' CSV HEADER QUOTE '\"';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL For Data Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Data Analysis Stages](./images/01sqlda.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Data Analysis Stages](./images/02sqlda.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Profiling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Exploratory Data Analysis  \n",
    "* What is the data structure?  \n",
    "* Quantitative Distribution  \n",
    "* Visualizing results  \n",
    "* Data Quality  \n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleanup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Duplication  \n",
    "* Data Cleaning  \n",
    "* Nulls  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}