import pandas as pd
dates=['Issue Date', 'Vehicle Expiration Date'] #Importing dates as datetime
col_types={'Plate ID': 'str','Date First Observed':'str'} #Importing Plate ID and the Date First Observed as a string, because it has to be made into a time by a function. 

df=pd.read_csv("small-violations.csv",dtype=col_types,parse_dates=dates,na_values={'Date First Observed'==0,'Vehicle Expiration Date'==88888888,88888888.0,88880088},infer_datetime_format=True)

df.dtypes #Finding out if our datatype import has worked or not.

df.columns #Identifying all the columns of the dataframe.

"""
Explanation: Homework 11: PARKING TICKETS
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
"""


## WRITING A FUNCTION TO CONVERT STRING INTO A DATE

def string_to_date(string):
    from dateutil import parser
    if pd.isnull(string):
        return None
    else:
        dt = parser.parse(string)
        return dt.date()

string_to_date('20160808') #Testing it out. 

df['Date First Observed']= df['Date First Observed'].apply(string_to_date) #Applying it to the dataframe column

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


def string_to_time(string): #CONVERTING THE VIOLATION TIME STRING INTO TIME
    from dateutil import parser
    import re
    if pd.isnull(string):
        return None
    if string =='Nan' or string =='nan' or string =='0':
        return None
    if string[0:4].isnumeric()==True:
        if int(string[0:2]) <=12 and int(string[2:4])<=59:
            regex=re.search(r"\d\d\d\d[AaPp]",string)
            if regex:
                time=string[0:2]+":"+string[2:4]+" "+string[4]+"M"
                return parser.parse(time).time()
            else:
                return None

def float_to_int(float): #THE VIOLATION CODE COLUMN IS ACTUALLY A FLOAT, SO WE'RE CONVERTING INTO AN INTEGER FIRST. 
    if pd.isnull(float):
        return None
    else:
         return int(float)

df['Violation Code']=df['Violation Code'].apply(float_to_int)

df['Violation Time']=df['Violation Time'].apply(string_to_time)

df['Violation Time'].head() #TESTING IF IT WORKED

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


df['Vehicle Color'].value_counts() #LOOKING AT COLORS IN THE ORIGINAL DATASET

df['Vehicle Color'] = df['Vehicle Color'].replace(['WHT', 'WH','WT','WHI'], 'WHITE')

df['Vehicle Color'] = df['Vehicle Color'].replace(['GRAY', 'GY','GRY'], 'GREY')

df['Vehicle Color'] = df['Vehicle Color'].replace(['BLK', 'BK'], 'BLACK')

df['Vehicle Color'] = df['Vehicle Color'].replace('BL', 'BLUE')

df['Vehicle Color'] = df['Vehicle Color'].replace(['BR', 'BN','BRWN'], 'BROWN')

df['Vehicle Color'] = df['Vehicle Color'].replace('RD', 'RED')

df['Vehicle Color'] = df['Vehicle Color'].replace(['GR', 'GN','GRN'], 'GREEN')

df['Vehicle Color'] = df['Vehicle Color'].replace('TN', 'TAN')

df['Vehicle Color'] = df['Vehicle Color'].replace('GL', 'GOLD')

df['Vehicle Color'] = df['Vehicle Color'].replace('BRN', 'BROWN')

df['Vehicle Color'] = df['Vehicle Color'].replace(['YW', 'YELLO','YELL'], 'YELLOW')

df['Vehicle Color'] = df['Vehicle Color'].replace(['BL', 'BLU'], 'BLUE')

df['Vehicle Color'] = df['Vehicle Color'].replace('MR', 'MAROON')

df['Vehicle Color'] = df['Vehicle Color'].replace(['SIL', 'SILVR','SILVE','SILV'], 'SILVER')

df['Vehicle Color'] = df['Vehicle Color'].replace(['OR', 'ORANG'], 'ORANGE')

df['Vehicle Color'].value_counts() #LOOKING AT THE COLORS AFTER CLEANING UP, MUCH BETTER!

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


#READING PARKING VIOLATION CODES FROM NYC DATASET
codesdf=pd.read_csv("DOF_Parking_Violation_Codes.csv",dtype={'Manhattan\xa0 96th St. & below':int, 'All Other Areas': int}) 

codesdf.columns

codesdf['Manhattan\xa0 96th St. & below'].value_counts() #CHECKING IF THE IMPORT WORKED WELL

newdf=df.join(codesdf, on='Violation Code', how='left') #MERGING THE TWO DATAFRAMES INTO A NEW DATAFRAME Unimaginatively CALLED NEWDF

newdf.head() #DID IT WORK? SEEMS TO HAVE, THE NANS IN THE END OF THIS FRAME ARE THERE IN THE ORIGINAL ONE TOO. 

newdf.columns #THE NEW COLUMNS SEEM TO HAVE BEEN ADDED

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


newdf['Violation County'].value_counts() #LET US SEE THE VIOLATION COUNTY DATA, THIS NEEDS TO BE CLEANED UP A BIT

newdf['Violation County'] = newdf['Violation County'].replace('BX', 'BRONX')

newdf['Violation County'] = newdf['Violation County'].replace(['R', 'RICH'], 'RICHMOND')

newdf['Violation County'] = newdf['Violation County'].replace('K', 'KINGS')

newdf['Violation County'].value_counts() #ALL CLEANED

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


manhattanviolations=newdf[newdf['Violation County']=='NY']

#FINES COLLECTED IN MANHATTAN
manhattanviolations['Manhattan\xa0 96th St. & below'].sum() 

allotherviolations=newdf[newdf['Violation County']!= 'NY']

#FINES NOT COLLECTED IN MANHATTAN

allotherviolations['All Other Areas'].sum()

#FINES COLLECTED IN ALL OF NYC
manhattanviolations['Manhattan\xa0 96th St. & below'].sum()+allotherviolations['All Other Areas'].sum()

"""
Explanation: Now, we know the locations of the violation and from the merged dataset we know that there the fine rates vary upon the location of the violation, with certain rates for everything under 96th street in Manhattan and other rates for elsewhere. So using the Violation County numbers, and the assumption that NY county is everything under 96th - we calculate.
End of explanation
"""


newdf['Manhattan\xa0 96th St. & below'].value_counts()
#IT IS EVIDENT THAT THE 265 DOLLARS IS THE MOST LUCRATIVE FINE in terms of monetary amount, but 115 DOlLARS IS THE MOST COMMON

lucrative=newdf[newdf['Manhattan\xa0 96th St. & below']==265]

lucrative['DEFINITION'].value_counts()
#TRACTOR TRAILER PARKING SEEMS TO MAKE MONIES

frequent=newdf['DEFINITION'].value_counts()
#IDENTIFYING THE MOST FREQUENT SETS OF VIOLATIONS

frequent.head() #THE TOP FIVE SETS OF VIOLATIONS

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


nonnyviolations=newdf[newdf['Registration State']!= 'NY']

nonnymanhattanviolations=nonnyviolations[nonnyviolations['Violation County']=='NY']

nonnymanhattanviolations['Manhattan\xa0 96th St. & below'].sum()
#MONEY MADE BY NON NY VEHICLES IN MANHATTAN

nonnymanhattanviolations=nonnyviolations[nonnyviolations['Violation County']!='NY']

nonnymanhattanviolations['All Other Areas'].sum()
#MONEY MADE BY NON NY VEHICLES OUTSIDE MANHATTAN

nonnymanhattanviolations['Manhattan\xa0 96th St. & below'].sum()+nonnymanhattanviolations['All Other Areas'].sum()
#TOTAL MONEY MADE BY NON NY VEHICLES 

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


import matplotlib.pyplot as plt

%matplotlib inline

plt.style.use('ggplot')
toptenviolations=nonnyviolations['Violation Code'].value_counts()
toptenviolations.head(10).plot(kind='bar')
#I USE THE VIOLATION CODE INSTEAD OF ACTUAL VIOLATION NAME BECAUSE THE NAMES OF SOME OF THEM ARE TOO LONG. 

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


newdf['Violation Time'].value_counts().plot(figsize=(16, 8))

"""
Explanation: We can then inspect what 21,38,14 violation codes are. 
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
"""


newdf['Manhattan\xa0 96th St. & below'].describe()
#AVERAGE COST OF TICKETS IN MANHATTAN is 95 DOLALRS

newdf['All Other Areas'].describe()
#AVERAGE COST OF TICKET ELSEWHERE IS 85 DOLLARS

import numpy
a=[84.716996,94.962425]
numpy.mean(a)
#SO THE AVERAGE TICKET COST IN NEW YORK CITY IS ABOUT 90 DOLLARS

"""
Explanation: From the graph above, it is very evident that most people get tickets in the 6 AM to 12 PM ie the morning slot. More than any other segments of the day.
13. What's the average ticket cost in NYC?
End of explanation
"""


ticketsperday=newdf['Issue Date'].value_counts()

ticketsperday.head(100).plot(figsize=(16, 8))

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


driversdf=pd.read_csv("drivers.csv")

driversdf.head(6)

"""
Explanation: Looks like, the month of August is when the Police seem to have gone on a fining spree. 
15. Make a graph of the amount of revenue collected per day.
<< Not too sure on how to get this done>> 
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
"""


#MAKING DATAFRAMES FOR EACH OF THESE BOROUGHS/COUNTIES
bronxviolations=newdf[newdf['Violation County']=='BRONX']
brooklynviolations=newdf[newdf['Violation County']=='KINGS']
manhattanviolations=newdf[newdf['Violation County']=='NY']
queensviolations=newdf[newdf['Violation County']=='Q']
statenislandviolations=newdf[newdf['Violation County']=='RICHMOND']

#GETTING THE TOTAL NUMBER OF DRIVERS FROM THE NEW DATA FRAME
numberofdriversinbronx=driversdf.iloc[0]['Total']
numberofdriversinbrooklyn=driversdf.iloc[1]['Total']
numberofdriversinmanhattan=driversdf.iloc[2]['Total']
numberofdriversinqueens=driversdf.iloc[3]['Total']
numberofdriversinstatenisland=driversdf.iloc[4]['Total']


bronxviolations['All Other Areas'].sum()/numberofdriversinbronx #AVERAGE PARKING TICKET PER DRIVER IN BRONX

brooklynviolations['All Other Areas'].sum()/numberofdriversinbrooklyn #AVERAGE PARKING TICKET PER DRIVER IN BROOKLYN

manhattanviolations['Manhattan\xa0 96th St. & below'].sum()/numberofdriversinmanhattan #AVERAGE PARKING TICKET PER DRIVER IN MANHATTAN

queensviolations['All Other Areas'].sum()/numberofdriversinqueens #AVERAGE PARKING TICKET PER DRIVER IN QUEENS

statenislandviolations['All Other Areas'].sum()/numberofdriversinstatenisland #AVERAGE PARKING TICKET PER DRIVER IN STATEN ISLAND

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


