Gun_Violence.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1494737</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>California</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1494530</td>\n",
       "      <td>1-Sep-19</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>Carriere</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1495103</td>\n",
       "      <td>1-Sep-19</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Geneva</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1491718</td>\n",
       "      <td>29-Aug-19</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1490546</td>\n",
       "      <td>28-Aug-19</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Petersburg</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date        State CityorCounty  Num_Killed  \\\n",
       "0      1494737      2-Sep-19   California      Oakland           1   \n",
       "1      1494530      1-Sep-19  Mississippi     Carriere           1   \n",
       "2      1495103      1-Sep-19      Alabama       Geneva           1   \n",
       "3      1491718     29-Aug-19     Michigan      Detroit           1   \n",
       "4      1490546     28-Aug-19     Virginia   Petersburg           1   \n",
       "\n",
       "   Num_Injured  \n",
       "0            0  \n",
       "1            0  \n",
       "2            0  \n",
       "3            1  \n",
       "4            0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Store Accidental Death CSV into DataFrame\n",
    "csv_file1 = \"Accidental_Death_Data.csv\"\n",
    "accidental_death_df = pd.read_csv(csv_file1)\n",
    "accidental_death_df.head()\n",
    "\n",
    "#Clean Accidental Death DataFrame\n",
    "new_accidental_death_df = accidental_death_df[['Incident ID', 'Incident Date', 'State', 'City Or County', '# Killed', '# Injured']].copy()\n",
    "new_accidental_death_df.head()\n",
    "\n",
    "#Rename Columns\n",
    "clean_accidental_death_df = new_accidental_death_df.rename(columns={\"Incident ID\":\"Incident_ID\", \"Incident Date\":\"Incident_Date\", \"State\":\"State\", \"City Or County\":\"CityorCounty\", \"# Killed\":\"Num_Killed\", \"# Injured\":\"Num_Injured\"})\n",
    "clean_accidental_death_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1495009</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Port Neches</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1494710</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Whitehall</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1494614</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1494595</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1494523</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Burnside</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date         State CityorCounty  Num_Killed  \\\n",
       "0      1495009      2-Sep-19         Texas  Port Neches           0   \n",
       "1      1494710      2-Sep-19  Pennsylvania    Whitehall           0   \n",
       "2      1494614      2-Sep-19      Michigan      Detroit           0   \n",
       "3      1494595      2-Sep-19      Michigan      Detroit           0   \n",
       "4      1494523      2-Sep-19      Kentucky     Burnside           0   \n",
       "\n",
       "   Num_Injured  \n",
       "0            1  \n",
       "1            1  \n",
       "2            1  \n",
       "3            1  \n",
       "4            1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Store Accidental Injury CSV into DataFrame\n",
    "csv_file2 = \"Accidental_Injury_Data.csv\"\n",
    "accidental_injury_df = pd.read_csv(csv_file2)\n",
    "accidental_injury_df.head()\n",
    "\n",
    "#Clean Accidental Injury DataFrame\n",
    "new_accidental_injury_df = accidental_injury_df[['Incident ID', 'Incident Date', 'State', 'City Or County', '# Killed', '# Injured']].copy()\n",
    "new_accidental_injury_df.head()\n",
    "\n",
    "#Rename Columns\n",
    "clean_accidental_injury_df = new_accidental_injury_df.rename(columns={\"Incident ID\":\"Incident_ID\", \"Incident Date\":\"Incident_Date\", \"State\":\"State\", \"City Or County\":\"CityorCounty\", \"# Killed\":\"Num_Killed\", \"# Injured\":\"Num_Injured\"})\n",
    "clean_accidental_injury_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1494697</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1495192</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Elkmont</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1495141</td>\n",
       "      <td>2-Sep-19</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1494669</td>\n",
       "      <td>1-Sep-19</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Rocky Mount</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1493374</td>\n",
       "      <td>1-Sep-19</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>Toledo</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date           State CityorCounty  Num_Killed  \\\n",
       "0      1494697      2-Sep-19  North Carolina   Greensboro           2   \n",
       "1      1495192      2-Sep-19         Alabama      Elkmont           5   \n",
       "2      1495141      2-Sep-19        Illinois      Chicago           0   \n",
       "3      1494669      1-Sep-19  North Carolina  Rocky Mount           0   \n",
       "4      1493374      1-Sep-19            Ohio       Toledo           0   \n",
       "\n",
       "   Num_Injured  \n",
       "0            2  \n",
       "1            0  \n",
       "2            4  \n",
       "3            4  \n",
       "4            4  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Store Mass Shooting CSV into DataFrame\n",
    "csv_file3 = \"Mass_Shooting_Data.csv\"\n",
    "mass_shooting_df = pd.read_csv(csv_file3)\n",
    "mass_shooting_df.head()\n",
    "\n",
    "#Clean Mass Shooting DataFrame\n",
    "new_mass_shooting_df = mass_shooting_df[['Incident ID', 'Incident Date', 'State', 'City Or County', '# Killed', '# Injured']].copy()\n",
    "new_mass_shooting_df.head()\n",
    "\n",
    "#Rename Columns\n",
    "clean_mass_shooting_df = new_mass_shooting_df.rename(columns={\"Incident ID\":\"Incident_ID\", \"Incident Date\":\"Incident_Date\", \"State\":\"State\", \"City Or County\":\"CityorCounty\", \"# Killed\":\"Num_Killed\", \"# Injured\":\"Num_Injured\"})\n",
    "clean_mass_shooting_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create Engine and connection to Database\n",
    "engine = create_engine('postgres://postgres:PASSWORD@localhost:5432/Gun_Violence')\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['mass_shootings',\n",
       " 'accidental_deaths',\n",
       " 'accidental_injuries',\n",
       " 'Mass_Shootings']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Verify tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use pandas to load mass shooting csv into converted DataFrame into database\n",
    "clean_mass_shooting_df.to_sql(name='mass_shootings', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1494697</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1495192</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Elkmont</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1495141</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1494669</td>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Rocky Mount</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1493374</td>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>Toledo</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date           State CityorCounty  Num_Killed  \\\n",
       "0      1494697    2019-09-02  North Carolina   Greensboro           2   \n",
       "1      1495192    2019-09-02         Alabama      Elkmont           5   \n",
       "2      1495141    2019-09-02        Illinois      Chicago           0   \n",
       "3      1494669    2019-09-01  North Carolina  Rocky Mount           0   \n",
       "4      1493374    2019-09-01            Ohio       Toledo           0   \n",
       "\n",
       "   Num_Injured  \n",
       "0            2  \n",
       "1            0  \n",
       "2            4  \n",
       "3            4  \n",
       "4            4  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#confirm data has been added by querying table\n",
    "pd.read_sql_query('select * from mass_shootings', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use pandas to load mass shooting csv into converted DataFrame into database\n",
    "clean_accidental_injury_df.to_sql(name='accidental_injuries', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1495009</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Port Neches</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1494710</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Whitehall</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1494614</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1494595</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1494523</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Burnside</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date         State CityorCounty  Num_Killed  \\\n",
       "0      1495009    2019-09-02         Texas  Port Neches           0   \n",
       "1      1494710    2019-09-02  Pennsylvania    Whitehall           0   \n",
       "2      1494614    2019-09-02      Michigan      Detroit           0   \n",
       "3      1494595    2019-09-02      Michigan      Detroit           0   \n",
       "4      1494523    2019-09-02      Kentucky     Burnside           0   \n",
       "\n",
       "   Num_Injured  \n",
       "0            1  \n",
       "1            1  \n",
       "2            1  \n",
       "3            1  \n",
       "4            1  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#confirm data has been added by querying table\n",
    "pd.read_sql_query('select * from accidental_injuries', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use pandas to load mass shooting csv into converted DataFrame into database\n",
    "clean_accidental_death_df.to_sql(name='accidental_deaths', con=engine, if_exists='append', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>State</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1494737</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>California</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1494530</td>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>Carriere</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1495103</td>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Geneva</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1491718</td>\n",
       "      <td>2019-08-29</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1490546</td>\n",
       "      <td>2019-08-28</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Petersburg</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident_ID Incident_Date        State CityorCounty  Num_Killed  \\\n",
       "0      1494737    2019-09-02   California      Oakland           1   \n",
       "1      1494530    2019-09-01  Mississippi     Carriere           1   \n",
       "2      1495103    2019-09-01      Alabama       Geneva           1   \n",
       "3      1491718    2019-08-29     Michigan      Detroit           1   \n",
       "4      1490546    2019-08-28     Virginia   Petersburg           1   \n",
       "\n",
       "   Num_Injured  \n",
       "0            0  \n",
       "1            0  \n",
       "2            0  \n",
       "3            1  \n",
       "4            0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#confirm data has been added by querying table\n",
    "pd.read_sql_query('select * from accidental_deaths', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Incident_ID</th>\n",
       "      <th>Incident_Date</th>\n",
       "      <th>CityorCounty</th>\n",
       "      <th>Num_Killed</th>\n",
       "      <th>Num_Injured</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>57</td>\n",
       "      <td>57</td>\n",
       "      <td>57</td>\n",
       "      <td>57</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District of Columbia</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nevada</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Delaware</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montana</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Incident_ID  Incident_Date  CityorCounty  Num_Killed  \\\n",
       "State                                                                        \n",
       "California                     59             59            59          59   \n",
       "Illinois                       57             57            57          57   \n",
       "Texas                          30             30            30          30   \n",
       "Pennsylvania                   28             28            28          28   \n",
       "Maryland                       24             24            24          24   \n",
       "Florida                        23             23            23          23   \n",
       "Georgia                        21             21            21          21   \n",
       "Missouri                       20             20            20          20   \n",
       "Tennessee                      19             19            19          19   \n",
       "Louisiana                      19             19            19          19   \n",
       "Ohio                           18             18            18          18   \n",
       "Alabama                        16             16            16          16   \n",
       "New Jersey                     16             16            16          16   \n",
       "New York                       14             14            14          14   \n",
       "Virginia                       12             12            12          12   \n",
       "South Carolina                 10             10            10          10   \n",
       "North Carolina                 10             10            10          10   \n",
       "District of Columbia            9              9             9           9   \n",
       "Indiana                         9              9             9           9   \n",
       "Colorado                        8              8             8           8   \n",
       "Nevada                          7              7             7           7   \n",
       "Arizona                         7              7             7           7   \n",
       "Arkansas                        7              7             7           7   \n",
       "Wisconsin                       6              6             6           6   \n",
       "Michigan                        6              6             6           6   \n",
       "New Mexico                      5              5             5           5   \n",
       "Mississippi                     5              5             5           5   \n",
       "Kentucky                        5              5             5           5   \n",
       "Washington                      5              5             5           5   \n",
       "Oklahoma                        4              4             4           4   \n",
       "Connecticut                     4              4             4           4   \n",
       "Massachusetts                   3              3             3           3   \n",
       "Kansas                          3              3             3           3   \n",
       "Delaware                        3              3             3           3   \n",
       "Minnesota                       3              3             3           3   \n",
       "Iowa                            2              2             2           2   \n",
       "Nebraska                        1              1             1           1   \n",
       "Montana                         1              1             1           1   \n",
       "Oregon                          1              1             1           1   \n",
       "\n",
       "                      Num_Injured  \n",
       "State                              \n",
       "California                     59  \n",
       "Illinois                       57  \n",
       "Texas                          30  \n",
       "Pennsylvania                   28  \n",
       "Maryland                       24  \n",
       "Florida                        23  \n",
       "Georgia                        21  \n",
       "Missouri                       20  \n",
       "Tennessee                      19  \n",
       "Louisiana                      19  \n",
       "Ohio                           18  \n",
       "Alabama                        16  \n",
       "New Jersey                     16  \n",
       "New York                       14  \n",
       "Virginia                       12  \n",
       "South Carolina                 10  \n",
       "North Carolina                 10  \n",
       "District of Columbia            9  \n",
       "Indiana                         9  \n",
       "Colorado                        8  \n",
       "Nevada                          7  \n",
       "Arizona                         7  \n",
       "Arkansas                        7  \n",
       "Wisconsin                       6  \n",
       "Michigan                        6  \n",
       "New Mexico                      5  \n",
       "Mississippi                     5  \n",
       "Kentucky                        5  \n",
       "Washington                      5  \n",
       "Oklahoma                        4  \n",
       "Connecticut                     4  \n",
       "Massachusetts                   3  \n",
       "Kansas                          3  \n",
       "Delaware                        3  \n",
       "Minnesota                       3  \n",
       "Iowa                            2  \n",
       "Nebraska                        1  \n",
       "Montana                         1  \n",
       "Oregon                          1  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Sample queries to address some of project questions\n",
    "\n",
    "#Which States have the highest number of incidents (killed v. injured)\n",
    "# Mass Shootings:\n",
    "\n",
    "Mass_Shootings_State = clean_mass_shooting_df.groupby(\"State\").count().sort_values([\"Num_Killed\"], ascending=False)\n",
    "Mass_Shootings_State\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}