col_types = "str"
df = pd.read_csv("mini_violations.csv", na_values={'Vehicle Year':"0", 'Date First Observed': "0", 'Vehicle Expiration Date': "0"}, low_memory=False, dtype=col_types, parse_dates=["Issue Date"])
#giving parse_time also "Vehicle Expiration Date" as a element, it did not convert it, thus left out and dealt with with .apply later

df.columns

df.info()

print(df['Issue Date'][0])

df['Violation County'].value_counts()

df.dtypes

"""
Explanation: 1. I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
2. I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
3. I want the dates to be dates! Read the read_csv documentation to find out how to make pandas automatically parse dates.
End of explanation
"""


df["Date First Observed"].head(40)
#format "Date First Observed": 20130719
type(df["Date First Observed"])

import dateutil
from dateutil import parser
#https://dateutil.readthedocs.io/en/stable/parser.html
#dateutil.parser.parse(timestr, parserinfo=None)

#WHY IS IT A FLOAT? I DEFINED ALL COLUMNS TO BE STR!?
type(df["Date First Observed"][0])

def date_str_to_date(timestr):
    convert_date_str = str(timestr)
    try:
        return dateutil.parser.parse(convert_date_str)
    except: 
        return convert_date_str

df['date_first_observed'] = df["Date First Observed"].apply(date_str_to_date)

#format "Vehicle Expiration Date": 20140831

df['vehicle_expiration'] = df["Vehicle Expiration Date"].apply(date_str_to_date)

"""
Explanation: 4. "Date first observed" is a pretty weird column, but it seems like it has a date hiding inside. Using a function with .apply, transform the string (e.g. "20140324") into a Python date.
--> Already done in the first step: "Make the 0's show up as NaN"
As "Vehicle Expiration Date" has the same format as "Date First observed" will also convert it with the same method
End of explanation
"""


import time

def changing_time(messy_time):
    a = messy_time[0:2]
    b = messy_time[-3:-1]
    c = messy_time[-1:]
    d = a +":"+ b + c + "M"
    #return d
    try: 
        stripped_time = dateutil.parser.parse(d).time()
        return stripped_time
    except:
        return d

df['violation_time'] = df["Violation Time"].astype(str).apply(changing_time)

type(df['violation_time'][0])

"""
Explanation: 5. "Violation time" is... not a time. Make it a time.
End of explanation
"""


#df["Vehicle Color"].value_counts() 
df['vehicle_color'] = df["Vehicle Color"].replace(["WH", "WT", "WHT", "WHI"], "WHITE").replace(["BK", "BLK"], "BLACK").replace(["GY", "GRY", "GRAY"], "GREY").replace(["BL", "BLU"], "BLUE").replace("RD", "RED").replace(["OR","ORANG"], "ORANGE").replace(["GRN", "GR", "GN"], "GREEN").replace(["BR", "BRN", "BN", "BRWN"], "BROWN").replace(["SILVE", "SILVR", "SIL", "SL"],"SILVER").replace("GL", "GOLD").replace(["YELLO","YW"],"YELLOW").replace("TN", "TAN")

#TRIED FUNCTION AND LIST COMPREHENSION AND FOR LOOP, ALL THREW ERRORS AT SOME POINT

#def change_color(wrongspell, rightspell):
#    newdf = df[df["Vehicle Color"].replace(wrongspell, rightspell)]
#    return newdf
#change_color("WH", "WHITE")
#change_color("WHT", "WHITE")
#change_color("WT", "WHITE")

#wrong_spellings = ["WH", "WT", "WHT"]
#for spelling in wrong_spellings:
#    newdf["Vehicle Color"] = df["Vehicle Color"].replace(spelling, "WHITE")

#[df["Vehicle Color"].replace(spelling, "WHITE") for spelling in wrong_spellings]  

df.columns

"""
Explanation: 6. There sure are a lot of colors of cars, too bad so many of them are the same. Make "BLK" and "BLACK", "WT" and "WHITE", and any other combinations that you notice.
End of explanation
"""


# selecting the columns needed to answer the remaining questions 
# above manipulated columns vehicle_color and date_first_observed not included, as not needed
selection= df[['Summons Number', 'Plate ID', 'Registration State', 'Violation County', 'Issue Date', 'violation_time', 'Violation Code']]

# make table column names look neater/having the same structure (no blank spaces within name!!!)
selection.columns = ['summons_number', 'plate_id', 'registration_state', 'violation_county', 'issue_date', 'violation_time', 'violation_code']

selection

# in atom I replaced all the $ by nothing -- seemed more time efficient than doing it in pandas ;) 

vc_set = pd.read_csv("DOF_Parking_Violation_Codes.csv", dtype="str")

vc_set.columns = ["code", "definition", "manhattan_below_96", "all_other_areas"]
vc_set

combined_df = selection.merge (vc_set, left_on='violation_code', right_on='code')

combined_df.columns

combined_df

"""
Explanation: 7. Join the data with the Parking Violations Code dataset from the NYC Open Data site.
End of explanation
"""


all_fines = combined_df["manhattan_below_96"]
all_list = all_fines.sum().split(" ")

cleaned_fines =[]
for fine in all_list:
    try:
        int_fine = int(fine)
        cleaned_fines.append(int_fine)
    except:
        continue

money_nyc = sum(cleaned_fines)
print("NYC makes at least", money_nyc, " US-Dollar off parking violations.")

"""
Explanation: 8. How much money did NYC make off of parking violations?
End of explanation
"""


#most lucrative:
combined_df["manhattan_below_96"].value_counts()

combined_df["all_other_areas"].value_counts()

combined_df[combined_df["all_other_areas"] == "200 (Heavy Tow, plus violation fine)"]

"""
Explanation: 9. What's the most lucrative kind of parking violation? The most frequent?
End of explanation
"""


#to find out row index of Violation code 46:
vc_set['code'].head(40)

#find out the definition of 
vc_set.loc[31]["definition"]

#most frequent:
combined_df['violation_code'].describe()

"""
Explanation: The highest fine of 200 Dollar is given outside of Manhattan and is associated with the "Violation Code" of 46
End of explanation
"""


#to find out row index of Violation code 21:
#vc_set['code']

#find out the definition of 
vc_set.loc[10]["definition"]

"""
Explanation: Most freqent is "Violation code" 21, occurring with a frequency of 387
End of explanation
"""


all_non_ny = combined_df[combined_df["registration_state"] != 'NY']
fines_non_ny = all_non_ny['all_other_areas']
fine_list = []
for fine in fines_non_ny:
    clean_fine = fine.replace(' (Heavy Tow, plus violation fine)', '').replace('vary','')
    try:
        fine_list.append(int(clean_fine))
    except:
        continue

sum_fines = sum(fine_list)
print("NYC makes at least", sum_fines, "US-Dollar from vehicles that are not registered in NY")

"""
Explanation: 10. New Jersey has bad drivers, but does it have bad parkers, too? How much money does NYC make off of all non-New York vehicles?
Thoughts: 1) Group by Registration State 2) sum up fare for all_other_areas
End of explanation
"""


def cleaning(value):
    if "(" in value:
        return 100
    if "vary" in value:
        return 2
    else:
        return int(value)

combined_df['manhattan_cleaned_fines'] = combined_df['manhattan_below_96'].apply(cleaning)

combined_df.groupby(by='registration_state')['manhattan_cleaned_fines'].sum().sort_values().plot(kind='barh', figsize=(20,10))

"""
Explanation: 11. Make a chart of the top few.
End of explanation
"""


type(combined_df['violation_time'][0])

print(combined_df['violation_time'][0])

combined_df['violation_time'][0].hour

def time_to_hour(value):
    try:
        return value.hour
    except:
        pass

combined_df["violation_hour"] = combined_df['violation_time'].apply(time_to_hour)
combined_df.head(3)

combined_df.groupby('violation_hour').count()['summons_number'].plot(kind="bar", figsize=(20,10))

combined_df['violation_hour'].hist(bins=4)

"""
Explanation: 12. What time of day do people usually get their tickets? You can break the day up into several blocks - for example 12am-6am, 6am-12pm, 12pm-6pm, 6pm-12am.
End of explanation
"""


number_of_all_tickets = combined_df['manhattan_below_96'].count()
number_of_all_tickets

# taking the total money NYC makes from question 8  -- stored in variable "money_nyc"
average_ticket_cost = money_nyc/number_of_all_tickets
print("The average ticket cost in NYC is", round(average_ticket_cost), "US-dollar.")

"""
Explanation: 13. What's the average ticket cost in NYC?
End of explanation
"""


combined_df.groupby('issue_date').count()
combined_df.groupby('issue_date').count()['summons_number']
plt.style.use('ggplot')
combined_df.groupby('issue_date').count()['summons_number'].plot(kind='bar', figsize=(20,10))

"""
Explanation: 14. Make a graph of the number of tickets per day.
End of explanation
"""


#groupby date, sum revenue

copy_df = combined_df
copy_df.index = copy_df['issue_date']

copy_df.groupby("issue_date")['manhattan_cleaned_fines'].sum().plot(kind='bar', figsize=(20,10))

type(dates_frame['issue_date'][0])

dates_frame

dates_frame = combined_df[combined_df['issue_date'] == combined_df['issue_date']]
rashida = dates_frame.groupby('issue_date')
rashida.head()

dates_frame = combined_df[combined_df['issue_date'] == combined_df['issue_date']]
by_date = dates_frame.groupby('issue_date')
type(by_date)
[date[0] for date in by_date["issue_date"]]
# [date for date in dates_frame["issue_date"]]

# group by date, sum fines per day

dates_frame = combined_df[combined_df['issue_date'] == combined_df['issue_date']]
by_date = dates_frame.groupby('issue_date')

by_dates_list = []
for value in by_date['manhattan_below_96'].sum():
    cleaned_value_list = value.replace("(Regular Tow, plus violation fine)", "").replace("vary", "").split()
    revenue_int_list = [int(value) for value in cleaned_value_list]
    revenue_sum = sum(revenue_int_list)
    individual_date = [date[0] for date in by_date["issue_date"]]
    date_dict={'date': "individual_date", 'revenue': revenue_sum}
    by_dates_list.append(date_dict)

by_dates_list

by_dates_df = pd.DataFrame(by_dates_list)
by_dates_df.plot(kind='bar', figsize=(20,10))

"""
Explanation: 15. Make a graph of the amount of revenue collected per day.
End of explanation
"""


licensed_drivers = pd.read_csv("licensed_drivers.csv")
selection_licensed = licensed_drivers.tail(5)[["County", "Total"]]
selection_licensed

abbreviation = [{'state': "Staten Island", 'abbreviation': "K"}, {'state': "Brooklyn", 'abbreviation': "R"}, {'state':"Queens", 'abbreviation':"Q"}, {'state':"Bronx", 'abbreviation': "BX"}, {'state':"New York", 'abbreviation':"NY"}]
abbrev_table = pd.DataFrame(abbreviation)
abbrev_table

joined_table = abbrev_table.merge(selection_licensed, left_on="state", right_on="County")
licensed_df = joined_table[['County', 'abbreviation', 'Total']]
licensed_df.columns = ("borough name", "abbreviation", "number of licensed drivers")
licensed_df

"""
Explanation: 16. Manually construct a dataframe out of https://dmv.ny.gov/statistic/2015licinforce-web.pdf (only NYC boroughts - bronx, queens, manhattan, staten island, brooklyn), having columns for borough name, abbreviation, and number of licensed drivers.
End of explanation
"""


#1) Get total violation cost per borough
#2) Divide it by number of licensed drivers

df_17 = combined_df[['summons_number', 'violation_county', 'manhattan_below_96', 'all_other_areas']]
df_17

fine_dict_list = []
def get_fines(a,b):
    viol = df_17[df_17['violation_county'] == a]
    fines = viol[b]

    fines_list = []
    for fine in fines:
        try:
            fines_list.append(int(fine))
        except:
            continue
    fine_per_borough = sum(fines_list)
    dict_fines = {'state': a, 'total_fines_in_borough': fine_per_borough}
    fine_dict_list.append(dict_fines)

fines_per_borough = fine_dict_list

get_fines('NY', 'manhattan_below_96')
get_fines('K', 'all_other_areas')
get_fines('BX', 'all_other_areas')
get_fines('R', 'all_other_areas')
get_fines('Q', 'all_other_areas')

fines_per_borough

borough_fines = pd.DataFrame(fines_per_borough)

joined_df = licensed_df.merge(borough_fines, left_on="abbreviation", right_on="state")
joined_df

joined_df['parking-ticket-cost-per-licensed-driver'] = joined_df['total_fines_in_borough'] / joined_df['number of licensed drivers']

joined_df

"""
Explanation: 17. What's the parking-ticket-cost-per-licensed-driver in each borough of NYC? Do this with pandas and the dataframe you just made, not with your head!
End of explanation
"""


