import pandas as pd

df = pd.read_csv('violations.csv', dtype = 'str', parse_dates = True)

"""
Explanation: Homework 11: PARKING TICKETS
Data is here: https://www.dropbox.com/s/4cbpwdcd802av1z/11-homework-data.zip?dl=0 [ www.dropbox.com/s/4cbpwdcd802av1z/11-homework-data.zip?dl=0 ]
<p>Read this: http://iquantny.tumblr.com/post/144197004989/the-nypd-was-systematically-ticketing-legally

<p>And the then we can take a peek at some data on parking. After you've cleaned up the data as instructed below, feel free to browse in other directions than the ones I say. When fixing up the data, you'll probably making a lot of use of date parsing and .apply.
End of explanation
"""


# test to make sure we haven't lost leading zeroes
# type(df['Plate ID'][0])

# test to make sure we haven't lost leading zeroes
# df['Plate ID'][0][0]

# test to make sure we haven't lost leading zeroes
# number_of_plates = range(len(df['Plate ID']))
# for number in number_of_plates:
#     if str(df['Plate ID'][number])[0] == '0': #used str as a workaround for the nan value at 2743
#         print(df['Plate ID'][number])

# # troubleshooting bc 2743 is na
# type(df['Plate ID'][2743])
# # if df['Plate ID'][2743]:
# #     print(df['Plate ID'][2743])

"""
Explanation: 1. I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
End of explanation
"""


# df['Vehicle Year'].head(10)

# Turns "0"s into the string "NaN"
# df['Vehicle Year'] = ['NaN' if item == '0' else item for item in df['Vehicle Year']]

# Turns "0"s into NoneType
df['Vehicle Year'] = df['Vehicle Year'].where(df['Vehicle Year'] != '0', None)

"""
Explanation: 2. I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
End of explanation
"""


# seeing if parse_dates = True worked:
# df['Issue Date'].head()

"""
Explanation: 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'][30: 35]

# 33 is a date
# Did it for the whole dataframe instead of for each row
# def year(row):
#     return df['Date First Observed'][row][:4]
# def month(row):
#     return df['Date First Observed'][row][4:6]
# def day(row):
#     return df['Date First Observed'][row][-2:]
# def date(row):
#     return {
#         'year': year(row),
#         'month': month(row),
#         'day': day(row)
#         }

def year(row):
    return row[:4]
def month(row):
    return row[4:6]
def day(row):
    return row[-2:]
def date(row):
    if len(row) == 8:
        dictionary = {
            'year': year(row),
            'month': month(row),
            'day': day(row)
            }
        return '{year}-{month}-{day}'.format(**dictionary)
    else:
        return None

df['Date First Observed Formatted'] = df['Date First Observed'].apply(date)

df['Date First Observed Formatted'][30: 35]

"""
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. Make the 0's show up as NaN.
End of explanation
"""


df['Violation Time'].head()

def hour(row):
    if row[0] == '.':
        return 'ERROR'
    elif row[-1] == 'P' and row[:2] != '12':
        try:
            return str(int(row[:2]) + 12)
        except:
            return 'ERROR'
    else:
        return row[:2]
    
def minute(row):
    return row[2:4]

def time(row):
    if pd.isnull(row):
        return None
    elif len(row) == 5:
        dictionary = {
            'hour': hour(row),
            'minute': minute(row)
            }
        return '{hour}:{minute}'.format(**dictionary)

number = -1
for item in df['Violation Time']:
    number += 1
    if isinstance(item, float):
        pass
    elif item.startswith('.6'):
        print(item)
        print(number)

df['Violation Time Formatted'] = df['Violation Time'].apply(time)

df['Violation Time Formatted'][424306]

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


df['Vehicle Color'].value_counts()

def color(row):
    # white
    if row == 'WH' or row == 'WHT' or row == 'WT' or row == 'WHI' or row == 'W':
        return 'WHITE'     
    # gray
    elif row == 'GY' or row == 'GREY' or row == 'GRY' or row == 'GREW':
        return 'GRAY'
    # black
    elif row == 'BK' or row == 'BLK' or row == 'BCK':
        return 'BLACK'
    # blue. I'm guessing BL is BLUE since there are two-letter codes and BK is BLACK
    elif row == 'NAVYB' or row == 'BL' or row == 'B LU' or row == 'BLU':
        return 'BLUE'
     # silver
    elif row == 'SILVE' or row == 'SLVIR' or row == 'SL' or row == 'SIL' or row == 'SILVR' or row == 'SILV' or row == 'SILIV':
        return 'SILVER'
    # red
    elif row == 'RD' or row == 'REDD':
        return 'RED'
    # green
    elif row == 'GR' or row == 'GRN' or row == 'GN':
        return 'GREEN'
    # tan
    elif row == 'TN' or row == 'BEIGE':
        return 'TAN'
    # brown
    elif row == 'BR' or row == 'BRN' or row == 'BN' or row == 'BRWN' or row == 'BRW' or row == 'BRO':
        return 'BROWN'
    # yellow
    elif row =='YW' or row == 'YELLO' or row == 'YE' or row == 'YELL' or row == 'YEL':
        return 'YELLOW'
    # gold
    elif row == 'GL' or row == 'GD' or row == 'GLD':
        return 'GOLD'
    # orange
    elif row == 'ORANG' or row == 'ORNAG' or row == 'OR':
        return 'ORANGE'
    # purple
    elif row == 'PURPL' or row == 'LAVEN':
        return 'PURPLE'
    # turquoise
    elif row == 'TQ' or row == 'TURQ':
        return 'TURQUOISE'
    # maroon
    elif row == 'MAROO' or row == 'BRGND' or row == 'BURG':
        return 'MAROON'
    else:
        return row

df['Vehicle Color Formatted'] = df['Vehicle Color'].apply(color)
df['Vehicle Color Formatted'].value_counts()

"""
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
"""


df_codes = pd.read_csv('Uniform_Violation_Codes.csv', dtype = str)

df_merged = df.merge(df_codes, how = 'left', left_on = 'Violation Code', right_on = 'CODE')

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


def fix_counties(county_name):
    if county_name == 'RICH':
        return 'R'
    elif county_name == 'BRONX':
        return 'BX'
    elif county_name == 'KINGS':
        return 'K'
    else:
        return county_name

df_merged['Violation County'] = df_merged['Violation County'].apply(fix_counties)

"""
Explanation: 8. How much money did NYC make off of parking violations?
Aside: Let's Fix the Counties and Integers
'Violation County' included 'RICH', 'BRONX', and 'KINGS':
End of explanation
"""


def int_dollar(str_dollar):
    try:
        if pd.isnull(str_dollar):
            return None
        else:
            return int(str_dollar)
    except:
        return None

df_merged['All Other Areas Int'] = df_merged['All Other Areas'].apply(int_dollar)
df_merged['Manhattan\xa096th st and below Int'] = df_merged['Manhattan\xa096th st and below'].apply(int_dollar)

df_merged.columns

"""
Explanation: 'All Other Areas' and 'Manhattan 96th st and below' were not integers:
End of explanation
"""


precincts = df_merged['Violation Precinct'].unique()

"""
Explanation: Okay, Back to the Question
But since there's a different fee for violations in Manhattan below 96th St, first we need to know:
Is It Below 96th St?
End of explanation
"""


precincts_below_96 = ["22", "20", "19", "18", "17", "13", "10", "9", "7", "6", "5", "1"]
# row_numbers = range(len(df_merged['Violation Precinct']))

# Make below_96_list = [
#       {'Violation Precinct': '22', 'Below 96th St?': True},
#       {'Violation Precinct': '20', 'Below 96th St?': True},
#       ...
#      ]

below_96_list = []
below_96_dict = {}

for precinct in precincts:
    below_96_dict['Violation Precinct'] = precinct
    if precinct in precincts_below_96:
        below_96_dict['Below 96th St?'] = True
    else:
        below_96_dict['Below 96th St?'] = False
    below_96_list.append(below_96_dict)
    below_96_dict = {}

# Make below_96_list a DataFrame and then merge it with df_merged

df_below_96 = pd.DataFrame(below_96_list)

df_merged = df_below_96.merge(df_merged, how = 'left', on = 'Violation Precinct')

"""
Explanation: One precinct straddles 96th St, but for simplicity, I'm only going to county precincts entirely below 96th.
End of explanation
"""


# make it so
# if Below 96th St? == True, Fee for This Precinct = Manhattan 96th st and below Int
# else, Fee for This Precinct = 'All Other Areas Int'

row_numbers = range(len(df_merged['Below 96th St?']))
fees_list = []
fees_dict = {}

for row in row_numbers:
    fees_dict['Summons Number'] = df_merged['Summons Number'][row]
    if df_merged['Below 96th St?'][row] == True:
        fees_dict['Fee for This Precinct'] = df_merged['Manhattan\xa096th st and below Int'][row]
    else:
        fees_dict['Fee for This Precinct'] = df_merged['All Other Areas Int'][row]
    fees_list.append(fees_dict)
    fees_dict = {}

df_fees = pd.DataFrame(fees_list)

"""
Explanation: So How Much Is Each Violation Fee?
End of explanation
"""


df_fees['Fee for This Precinct'].unique()

"""
Explanation: Aside: There's a Problem With Not Every Fee Having a Value
Some 'Fee For This Precinct' values are nan.
End of explanation
"""


df_merged['Manhattan\xa096th st and below'].unique()

"""
Explanation: That's because some fees in the columns 'Manhattan 96th st and below' and 'All Other Areas' are nan
End of explanation
"""


df_merged[df_merged['Manhattan\xa096th st and below'].isnull()]['Violation Code'].unique()

"""
Explanation: That's because there are violation codes that I now see were not in the DOF_Parking_Violation_Codes.csv we got:
    ['41', '9', '8', '6', '95', '15', '88', '54', '76', '4', '30', '1',
           '87', '5', '7', '90', '3', '36', '0', '2', '29']
The problems with:
* '41' was not in the document
* '9' was '09' in the document
* '8' was '08' 
* '6' was '06'
* '95' was not in the document 
* '15' was not in the document
* '88' was not in the document
* '54' was not in the document
* '76' was not in the document
* '4' was '04'
* '30' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . It was probably left out bc it's dealing with intercity buses.
* '1' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . It was probably left out bc it's dealing with intercity buses.
* '87' was not in the document
* '5' was '05'
* '7' was '07
* '90' was not in the document 
* '3' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . It was probably left out bc it's dealing with intercity buses.
* '36' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . I'm going to add it because it deals with driving in school zones.
* '0' was not in the document and only happened twice. Maybe an accident? 
* '2' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . It was probably left out bc it's dealing with intercity buses.
* '29' was not in the document, but was online: https://www1.nyc.gov/site/finance/vehicles/services-violation-codes.page . It was probably left out bc it's dealing with intercity buses.
<p>The ones dealing with intercity buses, I'm not going to add, since this is more about individuals.
<p>The other ones in the document, I can't find online.
<p>I can fix the ones that started with '0' and code 36.
End of explanation
"""


df_merged = df_fees.merge(df_merged)

df_merged.head()

"""
Explanation: End Aside. The Fees Are Fixed, So We Can Add Them to Our df_merged
End of explanation
"""


total_fees = 0
for fee in df_merged['Fee for This Precinct']:
    try:
        total_fees += int(fee)
    except:
        pass
total_fees

"""
Explanation: Total Money Made
End of explanation
"""


# total_fees = 0
# for fee in df_merged[df_merged['Below 96th St?'] == False]['Fee for This Precinct']:
#     try:
#         total_fees += int(fee)
#     except:
#         pass
# total_fees

# df_non_Manhattan = df_merged[df_merged['Violation County'] != 'NY']
# non_manhattan_sum = df_non_Manhattan['All Other Areas Int'].sum()

"""
Explanation: How I Tried to Do It Before I Made 'Fee for This Precinct'
Non-Manhattan Money
End of explanation
"""


# total_fees = 0
# for fee in df_merged[df_merged['Below 96th St?'] == True]['Fee for This Precinct']:
#     try:
#         total_fees += int(fee)
#     except:
#         pass
# total_fees

# Midtown North is 18
# Midtwon South is 22
# part of 24 is also below 96, but I'm not counting it
# precincts_below_96 = ["22", "20", "19", "18", "17", "13", "10", "9", "7", "6", "5", "1"]

# # go through rows in precincts. If row is in precinct list, save row because we want to get the sum of what was charged for those rows.
# row_numbers = range(len(df_merged['Violation Precinct']))
# row_numbers_for_violations_occuring_below_96 = []

# for row_number in row_numbers:
#     precinct = df_merged['Violation Precinct'][row_number]
#     if precinct in precincts_below_96:
#         row_numbers_for_violations_occuring_below_96.append(row_number)

# # fines_occuring_below_96 = [df_merged['Manhattan\xa096th st and below Int'][row_number] for row_number in row_numbers_for_rows_occuring_below_96]

# def fines_occuring_below_96(row_number):
#     try:
#         return int(df_merged['Manhattan\xa096th st and below Int'][row_number])
#     except:
#         return None

# below_96_fee_sum = 0
# for row_number in row_numbers_for_violations_occuring_below_96:
#     fine = fines_occuring_below_96(row_number)
#     if pd.isnull(fine):
#         pass
#     else:
#         below_96_fee_sum += fine
# below_96_fee_sum

"""
Explanation: Manhattan in Precincts Entirely Below 96th St Money
End of explanation
"""


# # If County is NY but the precincts are not the ones listed above

# # we're getting precincts that aren't listed above
# row_numbers_for_violations_occuring_above_96 = []

# for row_number in row_numbers:
#     precinct = df_merged['Violation Precinct'][row_number]
#     if not precinct in precincts_below_96:
#         row_numbers_for_violations_occuring_above_96.append(row_number)

# # Only counting the ones in NY
# df_Manhattan = df_merged[df_merged['Violation County'] == 'NY']

# def fines_occuring_above_96(row_number):
#     try:
#         return int(df_Manhattan['All Other Areas Int'][row_number])
#     except:
#         return None

# above_96_fee_sum = 0
# for row_number in row_numbers_for_violations_occuring_above_96:
#     fine = fines_occuring_above_96(row_number)
#     if pd.isnull(fine):
#         pass
#     else:
#         above_96_fee_sum += fine
# above_96_fee_sum

"""
Explanation: The Rest of Manhattan Money
End of explanation
"""


# total_sum = non_manhattan_sum + below_96_fee_sum + above_96_fee_sum
# print('At least $' + str(total_sum))

"""
Explanation: Total Amount Made by NYC:
End of explanation
"""


df_merged['DEFINITION'].value_counts()

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


df_merged[['DEFINITION', 'Fee for This Precinct']].groupby('DEFINITION').sum().sort_values('Fee for This Precinct', ascending = False)

"""
Explanation: Most Lucrative
End of explanation
"""


# def_list = df_merged['DEFINITION'].unique()

# def first_fee_result(partial_df):
#     number = 0
#     for row in partial_df:
#         number += 1
#         if number == 1:
#             return row

# lucrative_list = []
# lucrative_dict = {}
# for definition in def_list:
#     all_rows_in_df_with_def = df_merged[df_merged['DEFINITION'] == definition]
#     how_many_rows = all_rows_in_df_with_def['DEFINITION'].count()
#     fee = first_fee_result(all_rows_in_df_with_def['All Other Areas Int'])
#     if how_many_rows > 0:
#         lucrative_dict['DEFINITION'] = definition
#         lucrative_dict['freq'] = how_many_rows
#         lucrative_dict['All Other Areas Int'] = fee
#         lucrative_dict['How Lucrative'] = how_many_rows * fee
#         lucrative_list.append(lucrative_dict)
#         lucrative_dict = {}

# df_lucrative = pd.DataFrame(lucrative_list)

# df_lucrative.sort_values('How Lucrative', ascending=False).head()

"""
Explanation: How I Tried to Do It Before I Made 'Fee for This Precinct'
I want:
    [
    {'DEFINITION': 'Street Cleaning...', 'freq': 140329, 'All Other Areas Int': 45},
    {'DEFINITION': 'Failing to show a receipt', 'freq': 125716, 'All Other Areas Int': 35},
    ...
    ]
Then I'll turn that to a DataFrame.
<p> _Then I'll multiply 'freq' and 'All Other Areas Int' to make a 'How Lucrative' column_
End of explanation
"""


df_non_NY = df_merged[df_merged['Registration State'] != 'NY']

df_non_NY['Fee for This Precinct'].sum()

"""
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?
End of explanation
"""


df_non_NY['Registration State'].value_counts()

top_out_of_state = ['NJ', 'PA', 'CT', 'FL', 'MA', 'VA', 'MD', '99', 'NC', 'IL']

out_of_state_list = []
out_of_state_dict = {}

for state in top_out_of_state:
    df_this_state = df_merged[df_merged['Registration State'] == state]
    fees = df_this_state['Fee for This Precinct'].sum()
    out_of_state_dict['Registration State'] = state
    out_of_state_dict['total fees'] = fees 
    out_of_state_list.append(out_of_state_dict)
    out_of_state_dict = {}

df_out_of_state = pd.DataFrame(out_of_state_list)

%matplotlib inline

ax = df_out_of_state.plot(kind = 'bar', x = 'Registration State', y = 'total fees', legend = None)
ax.set_title('Violation Fees Collected by NY from Non-NY Cars from 1973 to 2060')

"""
Explanation: 11. Make a chart of the top few.
Make a list of all of the non-NY registration states and grab the top 10.
<p> for each of the top 10, find the frequency
    `[
    {'Registration State': '99', 'total fees': '999' },
    {'Registration State': 'AB', 'total fees': '999' } ...
    ]`
<p> then put them in a dataframe and use matplotlib
End of explanation
"""


def time_to_words(first_two_digits_are_your_hour):
    if not first_two_digits_are_your_hour:
        return None
    elif first_two_digits_are_your_hour.startswith('ERROR'):
        return None
    else:
        try:
            hour = int(first_two_digits_are_your_hour[:2])
            if hour < 6:
                return 'early_morning'
            elif hour < 12:
                return 'morning'
            elif hour < 18:
                return 'afternoon'
            elif hour < 24:
                return 'evening'
            else:
                return 'ERROR'
        except:
            return 'ERROR'

df_merged['Violation Time in Words'] = df_merged['Violation Time Formatted'].apply(time_to_words)

df_merged['Violation Time in Words'].value_counts()

"""
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
"""


total_fees/len(df)

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


df_merged['Issue Date'].value_counts().head()

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


issue_dates_list = [unique_date for unique_date in set(df_merged['Issue Date'])]
len(issue_dates_list)

import datetime
    
def to_datetime_version(str_date):
    datetime_version = datetime.datetime.strptime(str_date, "%m/%d/%Y")
    return datetime_version

def to_string_version(datetime_date):
    string_version = datetime.datetime.strftime(datetime_date, "%m/%d/%Y")
    return string_version

def list_to_sorted_datetime(str_list):
    datetime_version = [to_datetime_version(item) for item in str_list]
    sorted_datetime_version = sorted(datetime_version)
    sorted_string_version = [to_string_version(item) for item in sorted_datetime_version]
    return sorted_string_version

sorted_issue_dates_list = list_to_sorted_datetime(issue_dates_list)

value_counts_list = [value_count for value_count in df_merged['Issue Date'].value_counts()]
len(value_counts_list)

tix_per_diem = []
tix_dict = {}

for unique_date in sorted_issue_dates_list:
    tix_dict['Issue Date'] = unique_date
    tix_dict['tickets this day'] = len(df_merged[df_merged['Issue Date'] == unique_date])
    tix_per_diem.append(tix_dict)
    tix_dict = {}

df_tix_per_diem = pd.DataFrame(tix_per_diem)

df_tix_per_diem.plot(kind = 'line', x = 'Issue Date', y = 'tickets this day', legend = None)

"""
Explanation: Let's try this:
<p> We can turn the issue dates into a list
<p> We can also turn the value_counts into a list
<p> See if they are both the same length, and then match them up into a dictionary:
<p>
    `tix_per_diem = [
    { 'Issue Date': 07/30/2013 , 'tickets this day': 36500},
    { 'Issue Date': 08/06/2013 , 'tickets this day': 36080}, ...
    ]`
End of explanation
"""


df_revenue_per_day = df_merged[['Issue Date', 'Fee for This Precinct']].groupby('Issue Date').sum()

df_revenue_per_day

date_and_revenue_dict = df_revenue_per_day.to_dict()['Fee for This Precinct']

rev_today = []
rev_dict = {}
for date in sorted_issue_dates_list:
    rev_dict['Issue Date'] = date
    if date_and_revenue_dict[date]:
        rev_dict['Revenue Today'] = date_and_revenue_dict[date]
    else:
        rev_dict['Revenue Today'] = 0
    rev_today.append(rev_dict)
    rev_dict = {}

print(rev_today)

"""
Explanation: 15. Make a graph of the amount of revenue collected per day.
Let's try this:
<p>We already have the issue dates into a list
<p>We can also group the information by date and find the sum of that revenue. Let's do that and see where we can take it from there.
End of explanation
"""


df_revenue_per_day = pd.DataFrame(rev_today)
df_revenue_per_day

ax = df_revenue_per_day.plot(kind = 'line', x = 'Issue Date', y = 'Revenue Today', legend = None)
ax.set_title('Revenue for Traffic Violations Collected Per Day')

"""
Explanation: I want to get the dictionary underneath 'All Other Areas Int' and have move all of the keys and values into dictionaries.
<p> also want to replace all nan values for the revenue with 0
<p> so dictionary_name['All Other Areas Int'] ->
<p>
    `rev_today = [
    {'Issue Date': '01/04/2012', 'Revenue Today': 115.0},
    {'Issue Date': '01/05/2013', 'Revenue Today': 115.0},
    ...
    {'Issue Date': '01/14/2013', 'Revenue Today': 0},
    ...
    ]`
End of explanation
"""


df_licenses = pd.read_csv('driver_licenses.csv')
df_licenses

"""
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
"""


df_fees_per_county = df_merged[['Violation County', 'Fee for This Precinct']].groupby('Violation County').sum()

df_fees_per_county['Violation County'] = df_fees_per_county.index

df_fees_per_county

df_last_question = df_licenses.merge(df_fees_per_county, right_on = 'Violation County', left_on = 'abbreviation')

df_last_question

df_last_question['Fee for This Precinct'] / df_last_question['number of licensed drivers']

"""
Explanation: 17. What's the parking-ticket-$-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
"""


