<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
I want the dates to be dates! Read the read_csv documentation to find out how to make pandas automatically parse dates.
"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.
"Violation time" is... not a time. Make it a time.
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.
Join the data with the Parking Violations Code dataset from the NYC Open Data site.
How much money did NYC make off of parking violations?
What's the most lucrative kind of parking violation? The most frequent?
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?
Make a chart of the top few.
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.
What's the average ticket cost in NYC?
Make a graph of the number of tickets per day.
Make a graph of the amount of revenue collected per day.
Manually construct a dataframe out of https
Step1:  1. I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
Step2:  2.  I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
Step3:  3. I want the dates to be dates! Read the read_csv documentation to find out how to make pandas automatically parse dates.
Step4:  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.
Step5:  5. "Violation time" is... not a time. Make it a time
Step6:  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.
Step7:  7. Join the data with the Parking Violations Code dataset from the NYC Open Data site.
Step8:  8. How much money did NYC make off of parking violations?
Step9:  9. What's the most lucrative kind of parking violation? The most frequent?
Step10:  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?
Step11:  11. Make a chart of the top few.
Step12:  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.
Step13:  13. What's the average ticket cost in NYC?
Step14:  14. Make a graph of the number of tickets per day.
Step15:  15. Make a graph of the amount of revenue collected per day.
Step16:  16. Manually construct a dataframe out of https
Step17:  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!
<ASSISTANT_TASK:>
Python Code:
import dateutils
import dateutil.parser
import pandas as pd
parking_df = pd.read_csv("small-violations.csv")
parking_df
parking_df.dtypes
import datetime
parking_df.head()['Issue Date'].astype(datetime.datetime)
import pandas as pd
parking_df = pd.read_csv("small-violations.csv")
parking_df
Explanation: I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
I want the dates to be dates! Read the read_csv documentation to find out how to make pandas automatically parse dates.
"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.
"Violation time" is... not a time. Make it a time.
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.
Join the data with the Parking Violations Code dataset from the NYC Open Data site.
How much money did NYC make off of parking violations?
What's the most lucrative kind of parking violation? The most frequent?
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?
Make a chart of the top few.
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.
What's the average ticket cost in NYC?
Make a graph of the number of tickets per day.
Make a graph of the amount of revenue collected per day.
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.
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
col_plateid = { 'Plate ID': 'str',  }
violations_df = pd.read_csv("small-violations.csv", dtype=col_plateid)
violations_df.head(20)
print("The data type is",(type(violations_df['Plate ID'][0])))
Explanation: 1. I want to make sure my Plate ID is a string. Can't lose the leading zeroes!
End of explanation
type(parking_df['Vehicle Year'][0])
# DISCOVERY - pass value as [0] rather than 0
col_types = { 'Vehicle Year': [0] }
test_df = pd.read_csv("violations.csv", na_values=col_types, nrows=10)
test_df.head(10)
violations_df['Vehicle Year'] = violations_df['Vehicle Year'].replace("0","NaN")
violations_df.head(10)
Explanation: 2.  I don't think anyone's car was built in 0AD. Discard the '0's as NaN.
End of explanation
type(violations_df['Issue Date'][0])
violate_df = pd.read_csv("small-violations.csv", parse_dates=True, infer_datetime_format=True, keep_date_col=True, date_parser=True, dayfirst=True, nrows=10)
#violate_df['Vehicle Year'] = test1_df['Vehicle Year'].replace("0","NaN")
violate_df.head()
yourdate = dateutil.parser.parse(violate_df['Issue Date'][0])
yourdate
violate_df.head()['Issue Date'].astype(datetime.datetime)
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
violate_df.columns
# changing it to string because it later needs to be converted into Python time.
col_observ = { 'Date First Observed': 'str',  }
test2_df = pd.read_csv("violations.csv", dtype=col_observ, nrows=10)
test2_df.head()
# defining conversion into python time
def to_date(num):
      
    if num == "0":
        return num.replace("0","NaN")
    else:
        yourdate = dateutil.parser.parse(num)
        date_in_py = yourdate.strftime("%Y %B %d")
        return date_in_py
to_date("20140324")
# confirming its string.
type(test2_df['Date First Observed'][0])
test2_df['Date First Observed'].apply(to_date)
#replacing Date First Observed with Date First Observed column as already there are so many columns.
test2_df['Date First Observed'] = test2_df['Date First Observed'].apply(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. Make the 0's show up as NaN.
End of explanation
violate_df['Violation Time'].head(5)
type(violate_df['Violation Time'][0])
# am replacing A and P with AM and PM to 
def str_to_time(time_str):
    s = time_str.replace("P"," PM").replace("A"," AM")
    x = x = s[:2] + ":" + s[2:]
    return x
str_to_time("1239P")
test2_df['Violation Time'] = test2_df['Violation Time'].apply(str_to_time)
def vio_date(time_str):
    parsed_date = dateutil.parser.parse(time_str)
    date_vio = parsed_date.strftime("%H:%M %p")
    return date_vio
    #return parsed_date.hour
print(vio_date("12:32 PM"))
test2_df['Violation Time'].apply(vio_date)
#replacing Violation Time with Date Violation Time column as already there are so many columns.
test2_df['Violation Time'] = test2_df['Violation Time'].apply(vio_date)
test2_df['Violation Time']
Explanation: 5. "Violation time" is... not a time. Make it a time
End of explanation
#violate_df['Vehicle Color'].count_values()
violate_df.groupby('Vehicle Color').describe()
def to_color(color_str):
    if color_str == "WH":
        return str(color_str.replace("WH","White"))
    if color_str == "WHT":
        return str(color_str.replace("WHT","White"))
    if color_str == "RD":
        return str(color_str.replace("RD","Red"))
    if color_str == "BLK":
        return str(color_str.replace("BLK","BLACK"))
    if color_str == "BK":
        return str(color_str.replace("BK","BLACK"))
    if color_str == "BR":
        return str(color_str.replace("BR","Brown"))
    if color_str == "BRW":
        return str(color_str.replace("BRW","Brown"))
    if color_str == "GN":
        return str(color_str.replace("GN","Green"))
    if color_str == "GRY":
        return str(color_str.replace("GRY","Gray"))
    if color_str == "GY":
        return str(color_str.replace("GY","Gray"))
    if color_str == "BL":
        return str(color_str.replace("BL","Blue"))
    if color_str == "SILVR":
        return str(color_str.replace("SILVR","Silver"))
    if color_str == "SILVE":
        return str(color_str.replace("SILVE","Silver"))
    if color_str == "MAROO":
        return str(color_str.replace("MAROO","Maroon"))
to_color("WHT")    
test2_df['Vehicle Color'].apply(to_color)
#replacing Vehicle Color with Vehicle Color column as already there are so many columns.
test2_df['Vehicle Color'] = test2_df['Vehicle Color'].apply(to_color)
test2_df['Vehicle Color'].head()
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_code = pd.read_csv("DOF_Parking_Violation_Codes.csv")
df_code.head(10)
violate_df['Violation Legal Code'].head()
test2_df.join(df_code, on='Violation Code', how='left')
Explanation: 7. Join the data with the Parking Violations Code dataset from the NYC Open Data site.
End of explanation
for ammount in df_code["All Other Areas"]:
    try:
        money_to_int(ammount)
    except:
        print(ammount)
        print(type(ammount))
def money_to_int(money_str):
    return int(money_str.replace("$","").replace(",",""))
print(money_to_int("$115"))
import re
ammount_list = []
other_area = df_code["All Other Areas"]
for ammount in other_area:
    try:
        x = money_to_int(ammount)
        ammount_list.append(x)
        #print(amount_list)
    except:
        print("made it to except")
        if isinstance(ammount,str):
            print("is a string!")
            clean = re.findall(r"\d{3}", ammount)
            z = [int(i) for i in clean]
            #print(type(z[0]))
            #print(clean)
            if len(z) > 1:
                print("z is greater than 1")
                avg = int(sum(z) / len(z))
                print(type(avg))
                #print(avg)
                ammount_list.append(avg)
            elif len(z) == 1:
                print("only one item in list!")
                print("Let's append", str(z[0]))
                ammount_list.append(z[0])
                #print(amount_list)
            else:
                ammount_list.append(None)
        else: 
            ammount_list.append(None)
len(ammount_list) 
df_code['new_areas'] = ammount_list
df_code
#df_code['new_areas'].sum()
#since I am unable to read the entire data set using the subset to calculate the sum.
test3_df = pd.read_csv("small-violations.csv", dtype=col_observ)
test3_df
test3_df.join(df_code, on='Violation Code', how='left')
# joining with the violation dataset
new_data = test3_df.join(df_code, on='Violation Code', how='left')
new_data['new_areas'].sum()
Explanation: 8. How much money did NYC make off of parking violations?
End of explanation
new_data.columns
new_data['Violation Code'].value_counts()
new_data[new_data['Violation Code'] == 21]
most_frequent = new_data[new_data['Violation Code'] == 21].head(1)
print("The most frequent violation is", most_frequent['DEFINITION'])
columns_to_show = ['Violation Code','new_areas']
new_data[columns_to_show]
lucrative_df = new_data[columns_to_show]
freq_df = new_data
#df.sort_values('length', ascending=False).head(3)
lucrative_df.groupby('Violation Code')['new_areas'].sum().sort_values(ascending=False)
new_data[new_data['Violation Code'] == 14].head(1)
most_lucrative = new_data[new_data['Violation Code'] == 14].head(1)
print("The most lucrative is Violation Code 14 which corresponds to", most_frequent['DEFINITION'])
Explanation: 9. What's the most lucrative kind of parking violation? The most frequent?
End of explanation
columns_to_show = ['Registration State','new_areas']
new_data[columns_to_show]
df_reg = new_data[columns_to_show]
df_reg[df_reg['Registration State'] != "NY"]
df_nonNY = df_reg[df_reg['Registration State'] != "NY"]
print("The total money that NYC make off of all non-New York vehicles is", df_nonNY['new_areas'].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_nonNY.groupby('Registration State')['new_areas'].sum().sort_values(ascending=False).head(10)
import matplotlib.pyplot as plt
%matplotlib inline
df_nonNY.groupby('Registration State')['new_areas'].sum().sort_values(ascending=False).head(10).plot(kind='bar',x='Registration State', color='green')
Explanation: 11. Make a chart of the top few.
End of explanation
new_data.columns
test2_df['Violation Time'].head()
#new_data['Violation Time'] = test2_df['Violation Time']
violate_df['Violation Time']
type(new_data['Violation Time'][0])
v_time = new_data['Violation Time']
v_time.head(10)
def vio_date(time_str):
    parsed_date = dateutil.parser.parse(time_str)
    date_vio = parsed_date.strftime("%H:%M %p")
    return date_vio
    #return parsed_date.hour
print(vio_date("12:32 PM"))
# 12am-6am, 6am-12pm, 12pm-6pm, 6pm-12am
count1 = []
count2 = []
count3 = []
count4 = []
count5 = []
z = [i for i in v_time]
#print(z)
for i in z:
    if i != None:
        #print(i)
        try:
            vio_date(i)
            #print(type(i))
            #print("finished printing i")
        except:
            pass
        
#print(type(z[0]))
for item in z:
    item = str(item)
    #print(type(item))
    
    if item < "06.00 AM":
        count1.append(item)
        #print(len(count1))
    if item < "12.00 PM":
        count2.append(item)
        
    if item < "06.00 PM":
        count3.append(item)
        
    if item < "12.00 AM":
        count4.append(item)
        
    #else:
        #count5.append(item)
#print(len(count5))
print(len(count4))
print(len(count3))
print(len(count2))
print(len(count1))
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
#gives the Registration State wise ticket cost (new_areas) 
df_reg
df_reg.describe()
Explanation: 13. What's the average ticket cost in NYC?
End of explanation
new_data.columns
# parsing to the daytime format. did earlier (dateutil.parser.parse(violate_df['Issue Date'][0])
new_data['Issue Date'] = test3_df['Issue Date']
new_data['Issue Date'].value_counts().head(10)
new_data['Issue Date'].value_counts().head(10).plot(kind='bar',x='Issue Date', color='orange')
Explanation: 14. Make a graph of the number of tickets per day.
End of explanation
# since new data issue date is showing so many nan. i am going back to old data
new_data['Issue Date'] = test3_df['Issue Date']
new_data['Issue Date']
columns_to_show = ['Issue Date','new_areas']
new_data[columns_to_show].head()
new_data.groupby('Issue Date')['new_areas'].sum().sort_values(ascending=False).head(10).plot(kind='bar',x='Issue Date', color='green')
Explanation: 15. Make a graph of the amount of revenue collected per day.
End of explanation
df = pd.read_csv("borough.csv")
df
# bronx, queens, manhattan, staten island, brooklyn o
df[58: 63]
NYC = df[58: 63]
NYC['code'] = ["BX", "K", "NYC", "Q", "R"]
NYC
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
columns_to_show = ['Violation County','new_areas']
new_data[columns_to_show]
columns_to_show = ['Violation County','new_areas']
new_data[columns_to_show]
county = new_data[columns_to_show]
county.groupby('Violation County')['new_areas'].sum().sort_values(ascending=False).head(10)
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
<END_TASK>