<jupyter_start><jupyter_text>Group Members:  <NAME>, <NAME>  &  <NAME> UCD Bootcamp Visualization Project Data Source: https://data.world/awram/us-mass-shootings   (E) - Data Extraction* Import Needed Libraries* Load Excel File* Read Excel File & Store in Dataframe<jupyter_code>#####  *** Set Up ***  #####

### Import Dependencies
import pandas as pd
import numpy as np
import datetime as dt

### Python SQL Toolkit & Object Relational Mapper (ORM)
import psycopg2
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, func

### Display Options - Set Dataframe to Show All Columns in Output Window
pd.set_option("display.max_columns", None)

### File to Load 
US_Shooting_data = "Resources/US_Mass_Shootings_Data_1982_2019.xlsx"

### Read Excel Files & Store in Pandas Dataframes
US_Shooting_df = pd.read_excel(US_Shooting_data, encoding='unicode_escape')
#### Check Dataframe Datatypes
# US_Shooting.dtypes

#### Display Dataframe
US_Shooting_df.head()
#### Display Statistical Summary
US_Shooting_df.describe()<jupyter_output><empty_output><jupyter_text>(T) - Data Transformation* Drop Columns that are Not Needed* Create Columns that are Needed* Reorder Columns * Recode Field Values for Consistency & Analysis<jupyter_code>#### Drop Columns that Aren't Needed (we are not doing text analysis, so dropping notes fields/string fields)
US_Shooting_df = US_Shooting_df.drop(columns=["mental_health_details", "summary", "where_obtained", "weapon_details", 
                                      "mental_health_sources", "sources_additional_age", "type", "weapon_type",
                                       "prior_signs_mental_health_issues", "weapons_obtained_legally"], axis=1)

#### Create Additional Date Field
US_Shooting_df["month"] = US_Shooting_df["date"].dt.month


#### Rename One of the "Location"  Fields (dataset came with two location fields) 
US_Shooting_df = US_Shooting_df.rename(columns={"location": "city_state", 
                                          "location.1": "site"})

#### Reorder Dataframe
US_Shooting_df = US_Shooting_df[["case", "city_state", "site",
                           "date", "month", "year",
                           "injured", "fatalities","total_victims",
                          "age_of_shooter", "race",
                          "gender", "longitude", "latitude"]]

#### Display Dataframe
US_Shooting_df.head()
#####  *** Recode Fields for Consistency & Analysis  ***  #####

### Recode "age_of_shooter"
US_Shooting_df["age_of_shooter"].replace({"-": 0}, inplace=True)

### Recode "race"
US_Shooting_df["race"].replace({"-": "Other/Unknown", 
                              "unclear": "Other/Unknown",
                              "Other":  "Other/Unknown",
                              "White ": "White",
                              "white": "White",
                               "black": "Black"}, inplace=True)


### Recode "gender"
US_Shooting_df["gender"].replace({"-": "Other/Unknown", 
                              "Male & Female": "Other/Unknown",
                              "F":  "Female",
                              "M": "Male"}, inplace=True)

### Recode "site"
## This isn't recoding correctly, go back and revisit
US_Shooting_df["site"].replace({"Other\n ": "Other", 
                              "\nWorkplace ": "Workplace"}, inplace=True)

#### Display Dataframe
US_Shooting_df.head()<jupyter_output><empty_output><jupyter_text>(L) - Data Load* Create Engine & Connection Layer to Database* Use Pandas to Load CSV Converted Dataframe to PostgreSQL DB* Create a Session (link) from Python to PostgreSQL DB  Prior to Executing the Following Code, Set Up PostgreSQL Database* Using pgAdim, create a new database named "Mass_Shootings" (right-click on Databases in left menu list, create "database")* Click on the new database and navigate under "Schemas" > "Public" > "Tables" and create a new table named "Mass_Shootings" (right-click, create "table")* Navigate to "Columns", right-click and create "column". A new pop up window will display and you need to fill out the following elements:    1) "General Tab" > "Name" -  name as it appears in the list below    2) "Definition Tab" > "Data Type" - type is shown in list below    3) Repeat the addition of columns for entire list (14 columns total)<jupyter_code>from IPython.display import Image
Image("images/Mass_Shootings_Columns.png")
##### ***  Create Engine & Connection Layer ***  #####
connection_string = "postgres:&MF28wsac@localhost:5432/Mass_Shootings"
engine = create_engine(f'postgresql://{connection_string}')
connection = engine.connect()
##### *** Use Pandas to Load CSV Converted Dataframe into Database *** #####
US_Shooting_df.to_sql(name="Mass_Shootings", con=engine, if_exists="replace", index=False)
### Check Table Loaded to Database
engine.table_names()
##### ***  Create Our Session (link) from Python to the Database ***  #####
session = Session(engine)
### Confirm data has been added by querying the database
pd.read_sql_table("Mass_Shootings", con=engine).head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12556672580327222, 'fraction_numerical': 0.0045338064261778045, 'mean_word_length': 3.5547576301615798, 'pattern_counts': {'":': 15, '<': 16, '<?xml version=': 0, '>': 20, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '150020', 'license': 'ADSL', 'n_tokens_mistral': 1549, 'n_tokens_neox': 1453, 'n_words': 434, 'path': '.ipynb_checkpoints/Project_2 _ETL-checkpoint.ipynb', 'repo_name': 'joelias1/Project-2', 'size': '5094'}