# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from scipy.stats import chi2_contingency

import nltk
from nltk.tokenize import word_tokenize, sent_tokenize
from nltk.corpus import stopwords
nltk.download('punkt')
nltk.download('stopwords')
from nltk.stem import PorterStemmer
from nltk.stem import SnowballStemmer 
from nltk.stem import WordNetLemmatizer
nltk.download('wordnet')
from nltk.util import ngrams
import string

injury = pd.read_csv('severeinjury.csv', encoding='latin-1')

# We are going to look at OSHA recorded severe injuries between January 2015 and Spetmeber 2020. Interested to see if there is a pattern in what injuries lead to hospitalization and or amputation.

injury.head()

# There are a number of columns that we can elminate to make the data more manageable. Since most labor laws are made on the state and federal level we will drop location data except for state. Further inspection could be done on the dropped columns, but we are going to focus our search for now. The source data noted the the Lat and Long columns may be unreliable so we will be dropping them.
#
# We can also drop the UPA ID as it is a duplicate ID, and the Final Narrative. We could run NLP on the FInal Narrative, but that is beyond the scope of this review. 
#
# We will also drop titles columns after constructing dictionaries for labeling them later.

injury.info()

# Before we drop them we will first fill in our missing fields. 
# - Because we don't intend to use them in our analysis we will ignore the following: Address1, Address2, City, Zip, Latitude, Longitude. 
# - Primary NAICS will be filled with 0 as will hospitalization and amputation. 
# - Inspections will be converted to a binary column as the report numbers don't provide us with further information.
# - Secondary Souce will be filled with 0 and Secondary Source Title will be filled with None

fill = {'Primary NAICS':0, 'Hospitalized':0, 'Amputation':0, 'Inspection':0, 
        'Secondary Source':0, 'Secondary Source Title':'None'}
injury.fillna(value=fill, inplace=True)

injury.loc[injury['Inspection']!=0, 'Inspection'] = 1
injury.head()

injury.columns

injury.drop(columns=['ID', 'UPA','Employer', 'Address1', 'Address2','Latitude', 'Longitude', 
                      'Nature', 'Part of Body', 'Event', 'Source', 'Secondary Source', 
                     'Primary NAICS',] ,inplace=True)
injury.columns

# # Narrative Top Words

injury['Final Narrative']

nar1 = injury['Final Narrative'][0]
nar1

sents = nltk.sent_tokenize(nar1)
words = nltk.word_tokenize(nar1)
unique_tokens = set(words)
average_tokens = round(len(words)/len(sents))
print('Sentences: {}'.format(len(sents)))
print('Words: {}'.format(len(words)))
print('Unique Words: {}'.format(len(unique_tokens)))
print('Average Words per Sentence: {}'.format(average_tokens))

stop_words = set(stopwords.words('english'))
final_tokens=[]
for each in words:
    if each not in stop_words:
        final_tokens.append(each)
print('Non Stop Words: {}'.format(len(final_tokens)))

lemmatizer = WordNetLemmatizer()
lemmatized_words = [lemmatizer.lemmatize(word, pos='v') for word in final_tokens]
# %pprint
lemmatized_words


# %pprint

def prep_narrative(narrative):
    stop_words = set(stopwords.words('english'))|set(string.punctuation)
    sents = nltk.sent_tokenize(narrative)
    prepped_narrative = []
    for sentence in sents:
        words = nltk.word_tokenize(narrative)
        final_tokens=[]
        for each in words:
            if each.lower() not in stop_words:
                lemma = lemmatizer.lemmatize(each.lower(), pos='v') 
                final_tokens.append(lemma)
        prepped_narrative.extend(final_tokens)
    return prepped_narrative


prepped = prep_narrative(injury['Final Narrative'][0])
prepped

injury['lemmatized'] = injury['Final Narrative'].apply(prep_narrative)
#injury['lemmatized'] = pd.read_pickle('Narrative_lemmatized.pkl')

injury['lemmatized']

injury['lemmatized'].to_pickle('Narrative_lemmatized.pkl')


# +
def ranked_words(row):
    frequent = nltk.FreqDist(row)
    return frequent.most_common(5)

def place_words(row, rank):
    frequent = nltk.FreqDist(row)
    if len(frequent) > rank:
        return frequent.most_common(5)[rank-1][0]
    else:
        return None


# -

injury['top_words'] = injury['lemmatized'].apply(ranked_words)

injury['top_words']

for i in range(1,6):
    injury['top_word_{}'.format(i)] = injury['lemmatized'].apply(lambda x: place_words(x, i))

injury

injury.isnull().sum()

injury.fillna({'City':'unknown', 'Zip':0}, inplace=True)

injury['EventDate'] = pd.to_datetime(injury['EventDate'])
injury['Zip'] = injury['Zip'].astype('int')
injury.info()

# # State Names

# +
state_mapper = {'NY':'NEW YORK', 'WI':'WISCONSIN', 'PA':'PENNSYLVANIA', 'GA':'GEORGIA', 
                'FL':'FLORIDA', 'CO':'COLORADO', 'OK':'OKLAHOMA', 'TX':'TEXAS', 'LA':'LOUISIANA',
                'MI':'MISSISSIPPI','NJ':'NEW JERSEY', 'OH':'OHIO', 'IL':'ILLINOIS', 'NE':'NEBRASKA',
                'NH':'NEW HAMPSHIRE', 'KS':'KANSAS', 'MA':'MASSACHUSETTS', 'AR':'ARKANSAS', 
                'MI':'MICHIGAN', 'ID':'IDAHO', 'MI':'MISSOURI', 'ME':'MAINE', 'CT':'CONNECTICUT',
                'WV':'WEST VIRGINIA', 'ND':'NORTH DAKOTA', 'MT':'MONTANA', 'NC':'NORTH CAROLINA',
                'DE':'DELAWARE', 'CA':'CALIFORNIA', 'DC':'DISTRICT OF COLUMBIA', 'AL':'ALABAMA',
                'TN':'TENNESSEE', 'OR':'OREGON', 'SD':'SOUTH DAKOTA', 'RI':'RHODE ISLAND', 
                'IN':'INDIANA', 'VA':'VIRGINIA', 'NM':'NEW MEXICO', 'MD':'MARYLAND', 'UT':'UTAH', 
                'VT':'VERMONT', 'AZ':'ARIZONA', 'IA':'IOWA', 'KY':'KENTUCKY', 'MN':'MINNESOTA',
                'WA':'WASHINGTON', 'SC':'SOUTH CAROLINA', 'HI':'HAWAII', 'PR':'PUERTO RICO', 
                'VI':'VIRGIN ISLANDS','GU':'GUAM', 'NV':'NEVADA', 'WY':'WYOMING', 
                'AK':'ALASKA', 'NMI':'NORTHERN MARIANA ISLANDS', 'AS':'AMERICAN SAMOA', 'MO':'MISSOURI',
               'MS':'MISSISSIPPI'} 

injury['State'].replace(state_mapper, inplace=True)
injury['State'].unique()
# -

# # Part of Body

injury['Part of Body Title'].unique()

injury['Part of Body Title Short'] = injury['Part of Body Title'].copy()
injury.loc[injury['Part of Body Title'].str.contains('foot|feet|toe|Foot|Feet|toe|heel|Heel|sole|Sole|Arch|instep|Ankle'), 'Part of Body Title Short'] = 'Foot'
injury.loc[injury['Part of Body Title'].str.contains('finger|hand|Finger|Hand|Wrist'), 'Part of Body Title Short'] = 'Hand'
injury.loc[injury['Part of Body Title'].str.contains('knee|leg|Leg|Knee|Butt|butt|Lower extremities|Thigh'), 'Part of Body Title Short'] = 'Leg'
injury.loc[injury['Part of Body Title'].str.contains('Arm|arm|Elbow|elbow|shoulder|Shoulder|Upper extremities'), 'Part of Body Title Short'] = 'Arm'
injury.loc[injury['Part of Body Title'].str.contains('Head|head|Face|face|Mouth|mouth|Nose|nose|Eye|Ear|Brain|Lip|Skull|Scalp|Tooth|Cranial|Cheek|Jaw'), 'Part of Body Title Short'] = 'Head'
injury.loc[injury['Part of Body Title'].str.contains('Back|back|Lumbar'), 'Part of Body Title Short'] = 'Back'
injury.loc[injury['Part of Body Title'].str.contains('Trunk|trunk|hip|Hip|Chest|chest'), 'Part of Body Title Short'] = 'Core'
injury.loc[injury['Part of Body Title'].str.contains('Multiple|multiple|Whole Body|Upper and Lower'), 'Part of Body Title Short'] = 'Multiple'
injury.loc[injury['Part of Body Title'].str.contains('organ|Internal|Organ|internal|Lung|Liver|Spleen|Heart|Thoracic|Cocc|Sacral'), 'Part of Body Title Short'] = 'Organ'
injury.loc[injury['Part of Body Title'].str.contains('Pelv|Groin|Testis|Scrotum'), 'Part of Body Title Short'] = 'Groin'
injury['Part of Body Title Short'].unique().sort()
injury['Part of Body Title Short'].unique()

# # Right to Work

rtw = ['Alabama', 'Arizona', 'Arkansas', 'Florida', 'Georgia', 'Idaho', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 
       'Louisiana', 'Michigan','Mississippi', 'Nebraska', 'Nevada', 'North Carolina', 'North Dakota', 'Oklahoma', 
       'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Virginia' 'West Virginia', 'Wisconsin', 'Wyoming']
for index in range(len(rtw)):
    rtw[index] = rtw[index].upper()
injury['RTW'] = False
injury.loc[injury['State'].isin(rtw), 'RTW'] = True

injury['RTW']

# # Presidential Voting

prez_red = ['Alaska', 'Alabama', 'Arkansas', 'Florida', 'Idaho', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 
       'Louisiana', 'Missouri','Mississippi', 'Montana', 'Nebraska', 'North Carolina', 'North Dakota', 'Ohio','Oklahoma', 
       'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'West Virginia', 'Wyoming']
for index in range(len(prez_red)):
    prez_red[index] = prez_red[index].upper()
injury['prez_red'] = 'Democrat'
injury.loc[injury['State'].isin(prez_red), 'prez_red'] = 'Republican'

injury

# # Spending

# ### Public

public = pd.read_csv('slstate.csv')
public.head()

public.columns

public.drop(columns=['2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', 'Unnamed: 0', 'Unnamed: 16', 'Unnamed: 17'], inplace=True)
public.rename(columns={'Unnamed: 1':'state', '2018r':'2018'}, inplace=True)
public

public.dropna(inplace=True)
public

public_state = public.set_index('state')
pub_stack = pd.DataFrame(public_state.stack(), columns=['public']).sort_index()
pub_stack

# ### Private

private = pd.read_csv('nrstate.csv')
private.head()

private.columns

private.drop(columns=['Unnamed: 19', 'Unnamed: 20', 'RSE(%)', '2003', '2004', '2005', '2006', '2007', 
                      '2008', '2009', '2010', '2011', '2012', '2013', '2014'], inplace=True)
private.rename(columns={'Unnamed: 0':'state', '2018r':'2018'}, inplace=True)
private

private.dropna(inplace=True)
private

private_state = private.set_index('state')
priv_stack = pd.DataFrame(private_state.stack(), columns=['private']).sort_index()
priv_stack

pub_stack.index.rename(['state','year'], inplace=True)

pub_stack.insert(1, value=priv_stack['private'], column='private')
pub_stack

# ### Spending Ratio

spending = pub_stack.reset_index()
spending

spending['public'] = spending['public'].str.replace(',', '')
spending['private'] = spending['private'].str.replace(',', '')
spending = spending.astype({'public':'int', 'private':'int'})
spending['year'] = pd.to_datetime(spending['year'])
spending['year'] = spending['year'].dt.year
spending

spending.info()

spending['ratio'] = spending['private']/spending['public']
spending

spending['total'] = spending['private']+spending['public']
spending

spending['state'] = spending['state'].str.upper()
spending

injury['year']=injury['EventDate'].dt.year

injury['year']

spending.rename(columns={'state':'State'}, inplace=True)
spending

aggs = injury.groupby(['State','year'])[['EventDate', 'Hospitalized', 'Amputation']].agg({'EventDate':'count', 'Hospitalized':'sum', 
                                                                                    'Amputation':'sum'})
aggs = aggs.reset_index()
aggs.rename(columns={'EventDate':'injuries'}, inplace=True)
aggs

combined = aggs.merge(spending, on=['State', 'year'], how='outer')
combined

# +
# Right to Work

rtw = ['Alabama', 'Arizona', 'Arkansas', 'Florida', 'Georgia', 'Idaho', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 
       'Louisiana', 'Michigan','Mississippi', 'Nebraska', 'Nevada', 'North Carolina', 'North Dakota', 'Oklahoma', 
       'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Virginia' 'West Virginia', 'Wisconsin', 'Wyoming']
for index in range(len(rtw)):
    rtw[index] = rtw[index].upper()
combined['RTW'] = False
combined.loc[combined['State'].isin(rtw), 'RTW'] = True

combined['RTW']

# Presidential Voting

prez_red = ['Alaska', 'Alabama', 'Arkansas', 'Florida', 'Idaho', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 
       'Louisiana', 'Missouri','Mississippi', 'Montana', 'Nebraska', 'North Carolina', 'North Dakota', 'Ohio','Oklahoma', 
       'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'West Virginia', 'Wyoming']
for index in range(len(prez_red)):
    prez_red[index] = prez_red[index].upper()
combined['prez_red'] = 'Democrat'
combined.loc[combined['State'].isin(prez_red), 'prez_red'] = 'Republican'

combined
# -

remove = ['PUERTO RICO', 'VIRGIN ISLANDS','GUAM','NORTHERN MARIANA ISLANDS', 'AMERICAN SAMOA']
combined_clean = combined.loc[~combined['State'].isin(remove)]
combined_clean['State'].unique()

combined_clean['State'].value_counts()

OSHA = combined_clean.fillna(0).copy()
OSHA

pob = injury.groupby(['State', 'year'])['Part of Body Title Short'].value_counts().unstack(level=-1)
pob = pob.fillna(0).copy()
pob

pob.reset_index()

OSHA = OSHA.merge(pob, on=['State', 'year'], how='outer')
OSHA

OSHA = OSHA.loc[~OSHA['State'].isin(remove)]
OSHA

OSHA = OSHA.fillna(0).copy()
OSHA

OSHA['ratio'].describe()

injury.to_csv('Injuries.csv')
spending.to_csv('Spending.csv')
OSHA.to_csv('OSHA.csv')

StarcoderdataJupyterScriptsDedupFiltered