#Load Packages
import pandas as pd
import numpy as np
from IPython.display import display, HTML
low_memory = False

local_path = "/Users/michaeldowd/"
#Load the csv's 
clion_lookup = pd.read_csv(local_path + "Google Drive/Dowd_Local/Data/1st_Sample/c_lion_node_lookup_sample.csv")
crashes =      pd.read_csv(local_path + "Google Drive/Dowd_Local/Data/1st_Sample/crashes_sample.csv")
vehicles =     pd.read_csv(local_path + "Google Drive/Dowd_Local/Data/1st_Sample/vehicle_sample.csv")
factors =      pd.read_csv(local_path + "Google Drive/Dowd_Local/Data/1st_Sample/factor_sample.csv")

print "done"

"""
Explanation: Sample Crash/etc Data Set - Received Dec. 7th 2015
All columns names and allowed values can be referenced in the CrashDataDictionary.pdf file. 
Crash Data
Load Data
End of explanation
"""


display(HTML(pd.DataFrame(list(crashes.columns)).to_html()))


"""
Explanation: Crashes Column List
End of explanation
"""


#How many nulls in each column (only show columns with more than zero nulls)
crash_nulls = crashes.isnull().sum()
crash_nulls = crash_nulls[crash_nulls > 0]

def getPercNull(df, master_df):
    #Function for calculation percent of records that are null
    percents = []
    for b in df.iterrows():
        try:
            percents.append(b[1].Count/float(len(master_df[b[0]])))
        except:
            percents.append("NA")
    
    df['perc_null'] = percents

crash_nulls = pd.DataFrame(crash_nulls, columns = ["Count"])
getPercNull(crash_nulls, crashes)

display(HTML(crash_nulls.to_html()))
print "Total Records : " , len(crashes)

"""
Explanation: Null/NaN Totals by Column
See total & percent of null values by each column (where null values are present)
End of explanation
"""


priority_cols_crashes = ['case_yr', "road_sys", "reportable", "accd_typ", "num_of_veh", "traf_cntl", "light_cond", \
    "weather", "road_char","road_surf_", "collision_","ped_loc",  \
                 "ped_actn", "ext_of_inj","regn_cnty_", "dmv_accd_c", "err_cde", \
                "highway_in", "intersect1" ]

def summarize(column_list, master_df, include_sums = True):
    """
    Cycles through the columns in the priority cols list and does a simple aggregate, 
    total number of cases for each column, the sum of fatalities, and the sum of injuries.
    """
    print 5*">", "START", 5 * "<"
    for col in column_list:
        print ">"*20
        print col.upper()
        
        if include_sums:
            out =  master_df[[col, 'crashid', "num_of_fat", "num_of_inj"]].groupby(col)
            aggout =  out.agg({'crashid':{'count' :  'count' },
                          'num_of_fat' : {'sum' : 'sum'}, 
                          'num_of_inj' : {'sum' : 'sum'}
                          })
        elif include_sums == "Vehicles":
            out =  master_df[[col, 'crashid', "num_of_fat", "num_of_inj"]].groupby(col)
            aggout =  out.agg({'crashid':{'count' :  'count' },
                                'num_of_fat' : {'sum' : lambda x: np.sum(x)}
              })
        else:
            out =  master_df[[col, 'crashid']].groupby(col)
            aggout =  out.agg({'crashid':{'count' :  'count' }
              })
        display(HTML(aggout.to_html()))
    print 5*">", "END", 5 * "<"
    return aggout


"""
Explanation: Determine Priority Columns
Priority columns are those columns that may be used as an explanatory variable
End of explanation
"""


print 5*">", "Crash Data", 5 * "<"
summarize(priority_cols_crashes, crashes)

"""
Explanation: Crash Data Summary of Priority Columns
Total number of indcidents, fatalities and injuries by each priority column.
End of explanation
"""


#without using column value type enforcement - some mixed type colums emerged, code below corrected those in roadsys
crashes.road_sys.loc[crashes[crashes.road_sys == 12].index] = '12'
crashes.road_sys.loc[crashes[crashes.road_sys == 9].index] = '9'

pd.unique(crashes['road_sys'].ravel())

"""
Explanation: Data Cleaning - INCOMPLETE - may be needed for other columns if we use Pandas for loading
End of explanation
"""


pd.DataFrame(vehicles.columns, columns = ["Cols"])

"""
Explanation: Vehicles Data
Vehicles Data Column List
End of explanation
"""


#How many nulls in each column (only show columns with more than zero nulls)
number_of_nulls = vehicles.isnull().sum()
number_of_nulls = number_of_nulls[number_of_nulls > 0]

nulls_df = pd.DataFrame(number_of_nulls, columns = ["Count"])
getPercNull(nulls_df, vehicles)

display(HTML(nulls_df.to_html()))
        
print "Total Records : " , len(vehicles)

"""
Explanation: Null/NaN Totals by Column
End of explanation
"""


priority_cols_vehicles = [
    'case_yr', 'rgst_typ', 'body_typ', 'veh_typ','pre_accd_actn', 'age', 'sex', 'rgst_wgt'
]

#Join Crashes(just injuries and Fatalities) & Vehicles
crash_short = crashes[['case_num','crashid', 'num_of_fat', 'num_of_inj']]
merge_Vehicles = pd.merge(vehicles, crash_short, on='crashid')
grouped_by_Case_merge_vehicles = merge_Vehicles.groupby('crashid')

test = merge_Vehicles[['crashid','num_of_fat','num_of_inj']].groupby('crashid').sum()
# test.head()
merge_Vehicles.head()

"""
Explanation: Determine Priority Columns
End of explanation
"""


summarize(priority_cols_vehicles, merge_Vehicles, include_sums=True)

"""
Explanation: Vehicles Dataset Summary
Note: There is double counting present in the sum results in tables below, as each row in the vehicles table represents a party in the accident (vehicles, ped or bike). Data is just to provide an idea of the distribution of value for different fields. Injuries/Fatalities are not mapped to specific people/vehicles. I.E. If a crash occured and we know there was a bike and a car and we know the age/sex/etc of the two people involved (driver / rider) we do not know which person was injured/killed.
End of explanation
"""


print factors.head()
len(factors)

vehicles['uid'] = vehicles.crashid.map(str) + "_" + vehicles.veh_seq_num.map(str)
factors['uid'] = factors.crashid.map(str) + "_" + factors.veh_seq_num.map(str)

vehicles.crashid

factor_out = summarize(["aprnt_fctr"], factors, include_sums = False)
factor_out.sort('')

print factor_out.columns.get_level_values(1)
test = factor_out.iloc[:, factor_out.columns.get_level_values(1)=='count']

pd.DataFrame(test['crashid']['count']).sort_values('count')

"""
Explanation: Factor Dataset
End of explanation
"""


