<jupyter_start><jupyter_text>Data Wrangling Assessment Data Cleaning<jupyter_code>path = '/Users/sinasinai/Desktop/table_01_33_102020.xlsx'
df = pd.read_excel(path,engine='openpyxl',header=1,skipfooter=13)
df = df.rename(columns={'Unnamed: 0':'year'})
df = df.set_index('year')
df = df.transpose()
df = df.rename(columns={'Percent available for servicea':'Year-End Average Percentage Daily Availability',
                        'Average age (years)b':'Average Age - Years'}
              )
colmap = {0:[1,2],3:[4,5]}
rename_list = []

for key in colmap.keys():
    rename_list.append(df.columns[key])
    for stat in colmap[key]:
        rename_list.append(df.columns[key] + ' - ' + df.columns[stat])
        
df.columns = rename_list
df = df.drop(columns= ['Locomotives','Passenger and other train cars'])
df = df.replace('U','NaN').astype(float)
df.head()<jupyter_output><empty_output><jupyter_text>Data Joining<jupyter_code>patha = '/Users/sinasinai/Desktop/dl1c.xls'
pathb = '/Users/sinasinai/Desktop/dl22.xls'
a = pd.read_excel(patha,header=13,skipfooter=2)
b = pd.read_excel(pathb,header=13,usecols="A:Q,T:AC")
# renaming these directly for ease of use

a.columns = [
    'STATE',
    'LICENSED DRIVERS - MALE',
    'LICENSED DRIVERS - MALE PCT OF TOTAL DRIVERS',
    'LICENSED DRIVERS - FEMALE',
    'LICENSED DRIVERS - FEMALE PCT OF TOTAL DRIVERS',
    'LICENSED DRIVERS - TOTAL DRIVERS',
    'RATIO - LICENSED DRIVERS TO PRIVATE AND COMMERCIAL MVS REGISTERED',
    'POPULATION - TOTAL RESIDENT',
    'POPULATION - DRIVING AGE 16+ - MALE',
    'POPULATION - DRIVING AGE 16+ - FEMALE',
    'POPULATION - DRIVING AGE 16+ - TOTAL',
    'DRIVERS - PER 1000 TOTAL RESIDENT POPULATION',
    'DRIVERS - PER 1000 DRIVING AGE POPULATION'
]
b.columns = [
    'STATE',
    'LICENSED MALE DRIVERS - 19 AND UNDER',
    'LICENSED MALE DRIVERS - 20 - 24',
    'LICENSED MALE DRIVERS - 25 - 29',
    'LICENSED MALE DRIVERS - 30 - 34',
    'LICENSED MALE DRIVERS - 35 - 39',
    'LICENSED MALE DRIVERS - 40 - 44',
    'LICENSED MALE DRIVERS - 45 - 49',
    'LICENSED MALE DRIVERS - 50 - 54',
    'LICENSED MALE DRIVERS - 55 - 59',
    'LICENSED MALE DRIVERS - 60 - 64',
    'LICENSED MALE DRIVERS - 65 - 69',
    'LICENSED MALE DRIVERS - 70 - 74',
    'LICENSED MALE DRIVERS - 75 - 79',
    'LICENSED MALE DRIVERS - 80 - 84',
    'LICENSED MALE DRIVERS - 85 AND OVER',
    'LICENSED MALE DRIVERS - TOTAL',
    'LICENSED YOUNG MALE DRIVERS - UNDER 16',
    'LICENSED YOUNG MALE DRIVERS - 16',
    'LICENSED YOUNG MALE DRIVERS - 17',
    'LICENSED YOUNG MALE DRIVERS - 18',
    'LICENSED YOUNG MALE DRIVERS - 19',
    'LICENSED YOUNG MALE DRIVERS - 20',
    'LICENSED YOUNG MALE DRIVERS - 21',
    'LICENSED YOUNG MALE DRIVERS - 22',
    'LICENSED YOUNG MALE DRIVERS - 23',
    'LICENSED YOUNG MALE DRIVERS - 24'
    
]
a.head()
b.head()
a['STATE'] = a['STATE'].replace(to_replace='[0-9]/',value='',regex=True).replace(' +$','',regex=True)
b['STATE'] = b['STATE'].replace(to_replace='[0-9]/',value='',regex=True).replace(' +$','',regex=True)
merged = a.merge(b,on='STATE',how='left')
merged = merged.set_index('STATE')
merged.head()
merged.shape<jupyter_output><empty_output><jupyter_text>Study Design and Data Analysis Assessment<jupyter_code># The file encoding was weird, so I ended up using somthing besides utf-8
# I'm primarily analyzing the ACCIDENTS files

path2019 = '/Users/sinasinai/Desktop/FARS2019NationalCSV/accident.csv'
df2019 = pd.read_csv(path2019,encoding='latin-1')

path2018 = '/Users/sinasinai/Desktop/FARS2018NationalCSV/accident.csv'
df2018 = pd.read_csv(path2018,encoding='latin-1')

path2017 = '/Users/sinasinai/Desktop/FARS2017NationalCSV/accident.csv'
df2017 = pd.read_csv(path2017,encoding='latin-1')

path2016 = '/Users/sinasinai/Desktop/FARS2016NationalCSV/accident.csv'
df2016 = pd.read_csv(path2016,encoding='latin-1')

path2015 = '/Users/sinasinai/Desktop/FARS2015NationalCSV/accident.csv'
df2015 = pd.read_csv(path2015,encoding='latin-1')
df2019 = df2019.loc[(df2019.STATE.isin([26,36])) & (df2019.CITY.isin([1260,4170]))]
df2018 = df2018.loc[(df2018.STATE.isin([26,36])) & (df2018.CITY.isin([1260,4170]))]
df2017 = df2017.loc[(df2017.STATE.isin([26,36])) & (df2017.CITY.isin([1260,4170]))]
df2016 = df2016.loc[(df2016.STATE.isin([26,36])) & (df2016.CITY.isin([1260,4170]))]
df2015 = df2015.loc[(df2015.STATE.isin([26,36])) & (df2015.CITY.isin([1260,4170]))]
df = pd.concat([df2015,df2016,df2017,df2018,df2019],join='inner')
df['CITYNAME'] = df.CITY.map({1260:'Detroit',4170:'New York City'})
df['YEAR'] = df.YEAR.astype(str)
# Filtering to accidents involving pedestrians (i.e. folks who weren't in motor vehicles at the time)
# All accidents appear to have at least one fatality, so no need to filter on Fatals
df = df.loc[df.PERNOTMVIT > 0]
accidents = df.groupby(['CITYNAME','YEAR']).count()['ST_CASE'].reset_index().rename(columns={'ST_CASE':'Accidents'})
detroit = accidents.loc[accidents['CITYNAME'] == 'Detroit']
nyc = accidents.loc[accidents['CITYNAME'] == 'New York City']

f = sns.lineplot(x=detroit.YEAR,y=detroit.Accidents,label='Detroit')
sns.lineplot(x=nyc.YEAR,y=nyc.Accidents,label='NYC')

f.set_title('Pedestrian Accidents - NYC vs. Detroit')
plt.show()
accidents = df.groupby(['CITYNAME','MONTH']).count()['ST_CASE'].reset_index().rename(columns={'ST_CASE':'Accidents'})

detroit = accidents.loc[accidents['CITYNAME'] == 'Detroit']
nyc = accidents.loc[accidents['CITYNAME'] == 'New York City']

f = sns.lineplot(x=detroit.MONTH,y=detroit.Accidents,label='Detroit')
sns.lineplot(x=nyc.MONTH,y=nyc.Accidents,label='NYC')

f.set_title('By Month - Pedestrian Accidents - NYC vs. Detroit')
plt.show()
accidents = df.groupby(['CITYNAME','HOUR']).count()['ST_CASE'].reset_index().rename(columns={'ST_CASE':'Accidents'})

detroit = accidents.loc[accidents['CITYNAME'] == 'Detroit']
nyc = accidents.loc[accidents['CITYNAME'] == 'New York City']

f = sns.lineplot(x=detroit.HOUR,y=detroit.Accidents,label='Detroit')
sns.lineplot(x=nyc.HOUR,y=nyc.Accidents,label='NYC')

f.set_title('By Hour of Day - Pedestrian Accidents - NYC vs. Detroit')
plt.show()
df.loc['dfgroupby(['YEAR'])
drunk = df.loc[df.DRUNK_DR > 0]
drunk = drunk.groupby(['CITYNAME','YEAR']).count()['ST_CASE'].reset_index().rename(columns={'ST_CASE':'Accidents'})

detroit = drunk.loc[drunk['CITYNAME'] == 'Detroit']
nyc = drunk.loc[drunk['CITYNAME'] == 'New York City']

f = sns.lineplot(x=detroit.YEAR,y=detroit.Accidents,label='Detroit')
sns.lineplot(x=nyc.YEAR,y=nyc.Accidents,label='NYC')

f.set_title('Pedestrian Accidents w/ Drunk Drivers - NYC vs. Detroit')
plt.show()
df.groupby(['CITYNAME','ROUTE']).count()['ST_CASE']/df.groupby(['CITYNAME']).count()['ST_CASE']
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17391952309985098, 'fraction_numerical': 0.05633383010432191, 'mean_word_length': 4.960035523978686, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '445099', 'license': 'MIT', 'n_tokens_mistral': 2955, 'n_tokens_neox': 2629, 'n_words': 475, 'path': 'USG/USG Data Challenge.ipynb', 'repo_name': 'sinasinai/data_challenges', 'size': '6710'}