Midterm 2: Spring 2024¶

import re
import pandas as pd
import numpy as np
import sqlite3

conn = sqlite3.connect('file:resource/asnlib/publicdata/traffic.db?mode=ro', uri=True)

### Solution - Exercise 0  

def get_table_cols(table_name, conn):
    # GOAL: Determine if table_name contains only alphanumeric characters and underscores. If not, raise ValueError.
    # If so, return sorted list of column names in the table.
    
    # INPUT: 
    # 'table_name' is a string
    # 'conn' is a DB connection
    
    # STRATEGY:
    # 1. Check if each character in table_name is alphanumeric or an underscore. If not, raise Value Error.
    # 2. Find columns in table_name. Sort and return.
    
    # SOLUTION (VERSION 1):
    for character in table_name:
        if not (character.isalnum() or character == '_'):
            raise ValueError
    
    query = f'select * from {table_name} limit 1'
    entire_table = pd.read_sql(query, conn)
    column_names = entire_table.columns
    return sorted(column_names)

    # ------------------------------------------------------------------------------------

    # SOLUTION (VERSION 2):
    for character in table_name:
        # If you don't remember regex, .isalnum(), .isalpha(), .isnumeric().. You can always do something like this!
        if character not in 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_':
            raise ValueError

    # Google Search: 'sqlite get column names from table'
    # Google Result: https://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-database
    alternative_query = f'PRAGMA table_info({table_name})'
    column_info = pd.read_sql(alternative_query, conn)
    column_name_list = sorted(column_info['name'])
    return column_name_list
    

### Demo function call
print(get_table_cols(table_name='vehicles', conn=conn))

def get_table_cols_wrapper(table_name, conn):
    out = None
    err = False
    try:
        out = get_table_cols(table_name, conn)
    except ValueError:
        err = True
    finally:
        return err, out

### Test Cell - Exercise 0  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['get_table_cols']['config']

ex_conf['func'] = get_table_cols_wrapper

tester = Tester(ex_conf, key=b'bCims7OtaJU-ugwkHsiGLrZlwMV_w-IQXM4JkYtV-m8=', path='resource/asnlib/publicdata/')
for _ in range(100):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 1  

def intersection_of_cols(lol: list, num=None) -> set:
    assert len(lol) > 1, '`lol` must have at least 2 items'
    if num is None:
        num = len(lol)
    ###
    ### YOUR CODE HERE
    ###
    # GOAL: Identify column names that exist in at least num sublists
    
    # INPUT: 
    # list of lists called lol
    # num is an integer
    
    # STRATEGY:
    # 1. Create a new empty list to hold all column names
    # 2. Add column names from the sub-lists to this new list
    # 3. Count number of times each column name appears in this big list
    # 4. Create a set of the column names where the count is greater than or equal to num. Return this set.
    
    # SOLUTION (VERSION 1):
    all_cols_list = []
    
    for sublist in lol:
        all_cols_list += sublist
    
    from collections import Counter
    cols_count_dict = Counter(all_cols_list)
    
    final_cols_set = set()
    
    for col_key, count_val in cols_count_dict.items():
        if count_val >= num:
            final_cols_set.add(col_key)
    
    return final_cols_set

    # ------------------------------------------------------------------------------------

    # SOLUTION (VERSION 2) - Using List and Set Comprehensions:
    all_cols_list = [col for sublist in lol for col in sublist]
    
    from collections import Counter
    cols_count_dict = Counter(all_cols_list)
    
    return {col_key for col_key, count_val in cols_count_dict.items() if count_val >= num}
    

### Demo function call
for num in [2,3]:
    demo_intersection = intersection_of_cols(
        lol=[['ringo', 'phyllis', 'angela', 'paul'], 
             ['kevin', 'paul', 'oscar', 'kelly', 'phyllis'], 
             ['phyllis', 'oscar', 'ryan', 'john', 'toby']],
        num=num)
    print(f'num={num}; intersection={demo_intersection}')

### Test Cell - Exercise 1  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['intersection_of_cols']['config']

ex_conf['func'] = intersection_of_cols

tester = Tester(ex_conf, key=b'2Vf2WPfW2j0DLeUMjMzLkjVc8oiSatIE1WdsEL-_X5Q=', path='resource/asnlib/publicdata/')
for _ in range(100):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 2  

def summarize_person(conn):
    # GOAL: Return data frame with columns:
    # 1. YEAR
    # 2. INJURY_COUNT: count of records having value = 'Injured' in PERSON_INJURY column for that year
    # 3. DEATHS_COUNT: count of records having value = 'Killed' in PERSON_INJURY column for that year
    # 4. UNSPECIFIED_COUNT: count of records having value = 'Unspecified' in PERSON_INJURY column for that year
    # Get all info from table PERSON, group by YEAR column
    
    # INPUT: conn is a DB connection

    # First I'm going to look at crash_date and person_injury data and data types:
#     query_test = '''
#     select 
#     crash_date,
#     person_injury
#     from person
#     limit 5
#     '''
#     return pd.read_sql(query_test, conn)
#     return pd.read_sql(query_test, conn).dtypes

    # Google Search: 'pandas data types'
    # Google Result: https://pbpython.com/pandas_dtypes.html

    # We see the crash_date field is of object data type, so it's not a datetime. 
    # It's a string or some mix of data types. So let's try to proceed with grabbing the 4 digit year with that in mind.

    # Google Search: 'sqlite select part of string'
    # Google Result: https://www.sqlitetutorial.net/sqlite-functions/sqlite-substr/
    
    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 1) - Using Only SQL:
    query = '''
    select 
    substr(crash_date, -4, 4) YEAR,
    sum(person_injury = "Injured") INJURY_COUNT,
    sum(person_injury = "Killed") DEATHS_COUNT,
    sum(person_injury = "Unspecified") UNSPECIFIED_COUNT
    from person 
    group by 1
    '''
    return pd.read_sql(query, conn)

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 2) - Using Only SQL (Case):
    # https://www.sqlitetutorial.net/sqlite-case/
    query_v2 = '''
    select 
    SUBSTR(crash_date, -4, 4) YEAR,
    sum(
        CASE 
        when person_injury = "Injured" then 1
        else 0
        end
    ) INJURY_COUNT,
    sum(
        CASE 
        when person_injury = "Killed" then 1
        else 0
        end
    ) DEATHS_COUNT,
    sum(
        CASE 
        when person_injury = "Unspecified" then 1
        else 0
        end
    ) UNSPECIFIED_COUNT
    from person 
    group by 1
    '''
    return pd.read_sql(query_v2, conn)

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 3) - Using Pandas (Boolean Masks + Merge)
    # Note: Return .head() of any data frames until finished or use LIMIT in query until end
    query_v3 = '''
    select 
    crash_date,
    person_injury
    from person 
    '''
    df = pd.read_sql(query_v3, conn)

    # Google Search: 'pandas get part of string in column'
    # Google Result: https://www.geeksforgeeks.org/get-the-substring-of-the-column-in-pandas-python/
    df['CRASH_DATE'] = df['CRASH_DATE'].str[-4:]
    df = df.rename(columns={'CRASH_DATE': 'YEAR'})
    
    # Find count for each type (injured, killed, unspecified) separately. Store in separate data frames and then merge together at the end:
    df_injured = df[df['PERSON_INJURY']=='Injured']
    # Google Search: 'pandas groupby column find count'
    # Google Result: https://stackoverflow.com/questions/19384532/get-statistics-for-each-group-such-as-count-mean-etc-using-pandas-groupby
    df_injured = df_injured.groupby(['YEAR']).size().reset_index(name='INJURY_COUNT')
    
    df_killed = df[df['PERSON_INJURY']=='Killed']
    df_killed = df_killed.groupby(['YEAR']).size().reset_index(name='DEATHS_COUNT')
    
    df_unspecified = df[df['PERSON_INJURY']=='Unspecified']
    df_unspecified = df_unspecified.groupby(['YEAR']).size().reset_index(name='UNSPECIFIED_COUNT')
    
    df_final = df_injured.merge(df_killed, on='YEAR', how='outer')
    df_final = df_final.merge(df_unspecified, on='YEAR', how='outer')
    # Fill in any missing values with 0:
    df_final = df_final.fillna(0)
    
    # Convert COUNT columns to integer data type if necessary:
    df_final = df_final.astype({'INJURY_COUNT': 'int64', 'DEATHS_COUNT': 'int64', 'UNSPECIFIED_COUNT': 'int64'})
    
    return df_final

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 4) - Using Pandas (Value Counts)
    # Note: Return .head() of any data frames until finished or use LIMIT in query until end
    query_v4 = '''
    select 
    crash_date,
    person_injury
    from person 
    '''
    df = pd.read_sql(query_v4, conn)

    # Google Search: 'pandas get part of string in column'
    # Google Result: https://www.geeksforgeeks.org/get-the-substring-of-the-column-in-pandas-python/
    df['CRASH_DATE'] = df['CRASH_DATE'].str[-4:]
    df = df.rename(columns={'CRASH_DATE': 'YEAR'})
    
    # Filter PERSON_INJURY column to values we care about (just in case other values exist)
    df = df[df['PERSON_INJURY'].isin(['Injured','Killed','Unspecified'])]
    
    # Google Search: 'pandas groupby pivot'
    # Google Result: https://stackoverflow.com/questions/30679467/pivot-tables-of-counts-in-pandas-dataframe
    df = df[['YEAR','PERSON_INJURY']].value_counts().rename_axis([None, None]).unstack(fill_value=0)
    df = df.reset_index().rename(columns={'index': 'YEAR', 'Injured': 'INJURY_COUNT', 'Killed': 'DEATHS_COUNT', 'Unspecified': 'UNSPECIFIED_COUNT'})
    
    return df

    # ------------------------------------------------------------------------------------

    # SOLUTION (VERSION 5) - Using SQL + Pandas (I think this one is the easiest to understand):
    query_injured = '''
    select substr(crash_date, -4, 4) YEAR,
    count(person_injury) INJURY_COUNT
    from person 
    where person_injury = "Injured"
    group by 1
    '''
    
    query_killed = '''
    select substr(crash_date, -4, 4) YEAR,
    count(person_injury) DEATHS_COUNT
    from person 
    where person_injury = "Killed"
    group by 1
    '''
    
    query_unspecified = '''
    select substr(crash_date, -4, 4) YEAR,
    count(person_injury) UNSPECIFIED_COUNT
    from person 
    where person_injury = "Unspecified"
    group by 1
    '''
    
    df_injured = pd.read_sql(query_injured, conn)
    df_killed = pd.read_sql(query_killed, conn)
    df_unspecified = pd.read_sql(query_unspecified, conn)
    
    final_df = df_injured.merge(df_killed, how='outer', on='YEAR')
    final_df = final_df.merge(df_unspecified, how='outer', on='YEAR')
    final_df = final_df.fillna(0)
    final_df = final_df.astype({'INJURY_COUNT': 'int64', 'DEATHS_COUNT': 'int64', 'UNSPECIFIED_COUNT': 'int64'})
    
    return final_df
    

### Demo function call
demo_person_summary_df = summarize_person(conn)
display(demo_person_summary_df)

### Test Cell - Exercise 2  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['summarize_person']['config']

ex_conf['func'] = summarize_person

tester = Tester(ex_conf, key=b'RyeS0CX3AlR5W2hkDjq59hAtdzYRhkzP7eAhq88uBj0=', path='resource/asnlib/publicdata/')
for _ in range(5):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 3  

def summarize_crashes(conn):
    # GOAL: Return data frame with summary columns from CRASHES table
    # 1. YEAR: from "CRASH DATE"
    # 2. CRASH_COUNT: count of rows by year
    # 3. DEATHS: sum("NUMBER OF PERSONS KILLED") by year
    # 4. PEDESTRIAN_DEATHS: sum("NUMBER OF PEDESTRIANS KILLED") by year
    # 5. PEDESTRIAN_DEATH_SHARE: sum("NUMBER OF PEDESTRIANS KILLED") / sum("NUMBER OF PERSONS KILLED") by year
    # 6. DEATHS_PER_CRASH: sum("NUMBER OF PERSONS KILLED") / count of rows by year
    
    # INPUT: conn is a DB connection
    
    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 1) - Using Only SQL:
    query = '''
    select
    substr("CRASH DATE", -4, 4) YEAR,
    count(*) CRASH_COUNT,
    sum("NUMBER OF PERSONS KILLED") DEATHS,
    sum("NUMBER OF PEDESTRIANS KILLED") PEDESTRIAN_DEATHS,
    sum("NUMBER OF PEDESTRIANS KILLED")/sum("NUMBER OF PERSONS KILLED") PEDESTRIAN_DEATH_SHARE,
    sum("NUMBER OF PERSONS KILLED")/count(*) DEATHS_PER_CRASH
    from CRASHES
    group by 1
    '''
    return pd.read_sql(query, conn)

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 2) - Using Mostly Pandas:
    # Note: Limit output until finished using either .head() or LIMIT in our query
    
    # Select only the columns that we need from CRASHES in our query:
    query_v2 = '''
    select
    "CRASH DATE",
    "NUMBER OF PERSONS KILLED",
    "NUMBER OF PEDESTRIANS KILLED"
    from CRASHES
    '''
    df = pd.read_sql(query_v2, conn)
    
    # Create a YEAR column using last 4 characters of "CRASH DATE" column:
    df["CRASH DATE"] = df["CRASH DATE"].str[-4:]
    df = df.rename(columns={"CRASH DATE": 'YEAR'})
    
    # Get count of crashes by year:
    df_crashes = df.groupby(['YEAR']).size().reset_index(name='CRASH_COUNT')
    
    # Get sum of deaths by year:
    # Google Search: 'pandas groupby column find sum'
    # Google Result: https://stackoverflow.com/questions/39922986/how-do-i-pandas-group-by-to-get-sum
    df_num_persons_killed = df.groupby(['YEAR'])["NUMBER OF PERSONS KILLED"].sum().reset_index()
    df_num_persons_killed = df_num_persons_killed.rename(columns={"NUMBER OF PERSONS KILLED": 'DEATHS'})
    
    # Get sum of pedestrian deaths by year:
    df_peds_killed = df.groupby(['YEAR'])["NUMBER OF PEDESTRIANS KILLED"].sum().reset_index()
    df_peds_killed = df_peds_killed.rename(columns={"NUMBER OF PEDESTRIANS KILLED": 'PEDESTRIAN_DEATHS'})
    
    # Merge my data frames together using outer merge:
    df_final = df_crashes.merge(df_num_persons_killed, on='YEAR', how='outer')
    df_final = df_final.merge(df_peds_killed, on='YEAR', how='outer')
    
    # Add the last two columns (calculated from other columns):
    df_final['PEDESTRIAN_DEATH_SHARE'] = df_final['PEDESTRIAN_DEATHS'] / df_final['DEATHS']
    df_final['DEATHS_PER_CRASH'] = df_final['DEATHS'] / df_final['CRASH_COUNT']
    
    return df_final

### Demo function call
demo_crashes_summary = summarize_crashes(conn)
display(demo_crashes_summary)

### Test Cell - Exercise 3  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['summarize_crashes']['config']

ex_conf['func'] = summarize_crashes

tester = Tester(ex_conf, key=b'LzLE2EBzHIODexIqa8grPVUkAbrllL3Y6PD6REvlZGo=', path='resource/asnlib/publicdata/')
for _ in range(100):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 4  

def summarize_vehicles(conn):
    # GOAL: return summary data frame using info from VEHICLES and CRASHES tables
    # INPUT: conn is a DB connection
    
    # STRATEGY: 
    # 1. VEHICLE_TYPE: Get upper case of VEHICLE_TYPE from vehicles (exclude null values)
    # 2. COUNT: Count of rows grouped by vehicle_type
    # 3. OUT_OF_STATE: number of upper case state registration not equal to NY or NULL grouped by vehicle_type
    # 4. DEATHS: sum of "NUMBER OF PERSONS KILLED" in CRASHES (join VEHICLES to CRASHES using COLLISION_ID)
    
    # Sort by COUNT desc, then VEHICLE_TYPE asc
    # Limit to 30 rows or less
    # COLLISION_ID must be in VEHICLES and CRASHES tables (so inner join)
    
    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 1) - SQL Only:
    
    # Google Search: 'sqlite join'
    # Google Result: https://www.sqlitetutorial.net/sqlite-join/
    query = '''
    select
    upper(vehicle_type) VEHICLE_TYPE,
    count(*) COUNT,
    sum(upper(state_registration) <> "NY" and state_registration is not null) OUT_OF_STATE,
    sum("NUMBER OF PERSONS KILLED") DEATHS
    from vehicles
    INNER JOIN crashes 
    USING(collision_id)
    where vehicle_type is not null
    group by upper(vehicle_type)
    order by 2 desc, 1
    limit 30
    '''
    return pd.read_sql(query, conn)
    
    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 2) - Mostly Pandas:
    # Select columns we care about from VEHICLES, CRASHES (perform inner join on collision_id):
    query_v2 = '''
    select 
    vehicle_type,
    state_registration,
    "NUMBER OF PERSONS KILLED"
    from vehicles
    INNER JOIN crashes
    USING(collision_id)
    '''
    df = pd.read_sql(query_v2, conn)
    
    # Make vehicle_type and state_registration upper case:
    df['VEHICLE_TYPE'] = df['VEHICLE_TYPE'].str.upper()
    df['STATE_REGISTRATION'] = df['STATE_REGISTRATION'].str.upper()
    
    # Filter out null values from VEHICLE_TYPE:
    # Google Search: 'pandas filter out nan'
    # Google Result: https://stackoverflow.com/questions/22551403/python-pandas-filtering-out-nan-from-a-data-selection-of-a-column-of-strings
    df = df[df['VEHICLE_TYPE'].notnull()]
    
    # Find COUNT of rows grouped by VEHICLE_TYPE:
    df_count = df.groupby(['VEHICLE_TYPE']).size().reset_index(name='COUNT')
    
    # Find OUT_OF_STATE:
    df_out_of_state = df[df['STATE_REGISTRATION'] != 'NY']
    df_out_of_state = df_out_of_state[df_out_of_state['STATE_REGISTRATION'].notnull()]
    df_out_of_state = df_out_of_state.groupby(['VEHICLE_TYPE']).size().reset_index(name='OUT_OF_STATE')
    
    # Find DEATHS:
    df_deaths = df.groupby(['VEHICLE_TYPE'])["NUMBER OF PERSONS KILLED"].sum().reset_index()
    df_deaths = df_deaths.rename(columns={"NUMBER OF PERSONS KILLED": 'DEATHS'})
    
    # Merge everything back together:
    df_final = df_count.merge(df_out_of_state, on='VEHICLE_TYPE', how='outer')
    df_final = df_final.merge(df_deaths, on='VEHICLE_TYPE', how='outer')
    
    # Order by COUNT descending, then VEHICLE_TYPE ascending:
    # Google Search: 'pandas sort one column ascending other descending'
    # Google Result: https://stackoverflow.com/questions/17141558/how-to-sort-a-pandas-dataframe-by-two-or-more-columns
    df_final = df_final.sort_values(['COUNT', 'VEHICLE_TYPE'], ascending=[False, True])
    
    # Reset my index so it starts at 0, 1, 2.. 
    df_final = df_final.reset_index(drop=True)
    
    # Fill in any empty values with 0, set data type of aggregate columns to be an integer:
    df_final = df_final.fillna(0)
    df_final = df_final.astype({'COUNT': 'int64', 'OUT_OF_STATE': 'int64', 'DEATHS': 'int64'})

    # Return up to top 30 rows:
    return df_final.head(30)

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 3) - SQL + Pandas:
    # Pull all information, except for OUT_OF_STATE stuff (because that requires complex STATE_REGISTRATION filtering)
    query1 = '''
    select
    upper(vehicle_type) VEHICLE_TYPE,
    count(*) COUNT,
    sum("NUMBER OF PERSONS KILLED") DEATHS
    from vehicles
    INNER JOIN crashes 
    USING(collision_id)
    where vehicle_type is not NULL
    group by 1
    order by 2 desc, 1
    LIMIT 30
    '''
    # Now pull OUT_OF_STATE information separately (with filtering out NY and NULL values)
    query2 = '''
    select
    upper(vehicle_type) VEHICLE_TYPE,
    count(state_registration) OUT_OF_STATE
    from vehicles
    where state_registration <> 'NY'
    and state_registration is not NULL
    group by 1
    '''

    df_main = pd.read_sql(query1, conn)
    df_out_of_state = pd.read_sql(query2, conn)
    
    # Merge the two dataframes back together using left merge 
    # Why? Because df_main is limited to top 30 results already and contains all data we need except for the OUT_OF_STATE column
    df_final = df_main.merge(df_out_of_state, on='VEHICLE_TYPE', how='left')
    
    # Fill in any empty values with 0 and return
    return df_final.fillna(0)

    # ------------------------------------------------------------------------------------
    
    # SOLUTION (VERSION 4) - Only SQL (Using Case):
    query_v4 = '''
    select
    upper(v.vehicle_type) VEHICLE_TYPE,
    count(*) COUNT,
    sum(
        CASE 
        WHEN upper(v.state_registration) = 'NY' then 0
        WHEN v.state_registration is NULL then 0
        ELSE 1
        END
    ) OUT_OF_STATE,
    sum(c."NUMBER OF PERSONS KILLED") DEATHS
    from vehicles v, crashes c
    where v.collision_id = c.collision_id
    and v.vehicle_type is not NULL
    group by 1
    order by 2 desc, 1
    LIMIT 30
    '''
    return pd.read_sql(query_v4, conn)


### Demo function call
demo_vehicle_summary = summarize_vehicles(conn)
display(demo_vehicle_summary)

### Test Cell - Exercise 4  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['summarize_vehicles']['config']

ex_conf['func'] = summarize_vehicles

tester = Tester(ex_conf, key=b'abnTJj1xha26W0V1GiFPY9_H9c3tllHziFuhZXXc66A=', path='resource/asnlib/publicdata/')
for _ in range(5):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 5  

def geo_filter_crashes(qry, conn, params):
    # GOAL: run qry using paramas. Convert 'CRASH_DATE' column to be datetime
    
    # INPUT:
    # 1. qry is a SQL query
    # 2. conn is a DB connection
    # 3. params is some parameters we want to pass into qry
    
    # Google Search: 'pandas pass parameters into sql query'
    # Google Result: https://stackoverflow.com/questions/48629413/how-to-pass-variable-values-dynamically-in-pandas-sql-query
    # https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html
    df = pd.read_sql(qry, conn, params=params)

    # Google Search: 'pandas convert column to datetime64'
    # Google Result: https://www.geeksforgeeks.org/convert-the-column-type-from-string-to-datetime-format-in-pandas-dataframe/
    # https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html
    df['CRASH_DATE'] = pd.to_datetime(df['CRASH_DATE'])
    return df



### Demo function call
demo_qry = '''
        select
            "CRASH DATE" AS CRASH_DATE,
            LATITUDE,
            LONGITUDE,
            "NUMBER OF PERSONS KILLED" AS PERSONS_KILLED
        from
            crashes
        where
            latitude is not null and latitude between ? and ?
        and
            longitude is not null and longitude between ? and ?
        and
            PERSONS_KILLED is not null and PERSONS_KILLED > 0
    '''
demo_df = geo_filter_crashes(demo_qry, conn, [40.5, 40.95, -74.1, -73.65])
display(demo_df.head())

### Test Cell - Exercise 5  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['geo_filter_crashes']['config']

ex_conf['func'] = geo_filter_crashes

tester = Tester(ex_conf, key=b'Js0HmgMQRjHXmmjXi4nPGc7_U3iIQ1qUUSDFWDdOM_g=', path='resource/asnlib/publicdata/')
for _ in range(20):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')

### Solution - Exercise 6  

def density_grid(bounds, grid_size, estimator):
    # GOAL: return Numpy array with shape (grid_size, grid_size)
    
    # INPUT: 
    # bounds is a list: [xmin, xmax, ymin, ymax]
    # grid_size is an integer
    # estimator is a function
    
    # STRATEGY:
    # 1. Use np.linspace to build x arr of grid_size evenly spaced numbers between and including the min and max x coords
    # 2. Use np.linspace to build y arr of grid_size evenly spaced numbers between and including the min and max y coords
    # 3. np.meshgrid - look up how to use
    # 4. get flattened x array and flattened y array
    # 5. stack them using np.vstack
    # 6. Pass into estimator an array with shape (2, # of data pts) where first row x values and 2nd row y values
    # 7. Return array with shape (grid_size, grid_size)
    
    x_arr = np.linspace(bounds[0], bounds[1], grid_size)
    y_arr = np.linspace(bounds[2], bounds[3], grid_size)
    mesh = np.meshgrid(x_arr, y_arr)
    flat_x = mesh[0].flatten()
    flat_y = mesh[1].flatten()
    double_stack = np.vstack((flat_x, flat_y))
    return estimator(double_stack).reshape(grid_size, grid_size)


### Demo function call
demo_bounds = [0, 9, 0, 90]
demo_grid_size = 10
# estimator adds the x and y values
demo_estimator = lambda a: a[0] + a[1]
demo_grid = density_grid(demo_bounds, demo_grid_size, demo_estimator)
print(demo_grid)

### Test Cell - Exercise 6  

from cse6040_devkit.tester_fw.testers import Tester
from yaml import safe_load

with open('resource/asnlib/publicdata/assignment_config.yaml') as f:
    ex_conf = safe_load(f)['exercises']['density_grid']['config']

ex_conf['func'] = density_grid

tester = Tester(ex_conf, key=b'OUm-rIOTtKW4RMUlmslRWQrXyW9IoJbDwb3UcOkhuCg=', path='resource/asnlib/publicdata/')
for _ in range(20):
    try:
        tester.run_test()
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
    except:
        (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars()
        raise

###
### AUTOGRADER TEST - DO NOT REMOVE
###

print('Passed! Please submit.')