<jupyter_start><jupyter_text>Loading Parking Violation Data and cleaning operations<jupyter_code>import glob
import pandas as pd
pd.set_option('display.max_columns', 500)
files = glob.glob('../vz-hackathon/**')
parking_violations = [f for f in files if 'Parking_Violations_in' in f]<jupyter_output><empty_output><jupyter_text>Combine all csvs into one data frame<jupyter_code>list_ = []
for file_ in parking_violations[:]:
    df = pd.read_csv(file_,index_col=None, header=0)
    
    filename = file_[len('../vz-hackathon/'):]
    
    df['filename'] = filename
       
    list_.append(df)
    
frame = pd.concat(list_)

# TESTs
assert frame.filename.nunique() == len(parking_violations)
frame.columns = [col.lower() for col in frame.columns]
frame = frame.reset_index(drop=True)
df = frame.copy()<jupyter_output><empty_output><jupyter_text>Construct datetime object from issue_time raw string<jupyter_code>def mil_to_time(x):
    "Convert messy issue_time to datetime object based upon length of issue_time string"
    if x == 'nan':
        return '00:00:00.000Z'
        
    x = x.split('.')[0]
    lg = len(x)
    
    if lg == 4:
        t = x[:2] + ':' + x[2:] + ':00.000Z'
      
    elif lg == 3:
        t = '0' + x[0] + ':' + x[1:] + ':00.000Z'
    
    elif lg == 2:
        t = '0' + '0' + ':' + x + ':00.000Z'
    
    elif lg == 1:
        t = '0' + '0' + ':' + '0' + x + ':00.000Z'
    
    else:
        t = '00:00.000Z'
    
    # correction for timedate if one element is greater than 5.
    # double check this
    if int(t[3]) > 5:
        t = t[:2]+ ':' + '5' + t[4:]
        
    return t
df['issue_time_military'] = df.issue_time.apply(str).apply(mil_to_time)
dates = df.ticket_issue_date.str[:10] + 'T' #+ 
df['ticket_issue_datetime'] = dates + df.issue_time_military<jupyter_output><empty_output><jupyter_text>Optional Test that datetime contruction is correct<jupyter_code>## Testing datetime format to ensure that everything is actually datetime
# for i, t in enumerate(df.ticket_issue_datetime):
#     try:
#         pd.to_datetime(t)
#     except:
#         print i, t<jupyter_output><empty_output><jupyter_text>Holiday value to Boolean<jupyter_code>df['holiday'] = df.holiday != 0<jupyter_output><empty_output><jupyter_text>Delete redundant columns<jupyter_code>del df['day_of_week']
del df['month_of_year']
del df['week_of_year']
del df['issue_time']
del df['issue_time_military']
del df['ticket_issue_date']<jupyter_output><empty_output><jupyter_text>Drop Duplicates and Fill in Empy Rows<jupyter_code>df.drop_duplicates(subset='rowid_', inplace=True)
df.streetsegid.fillna(0, inplace=True)
df.to_pickle('parking_eda.pkle')<jupyter_output><empty_output><jupyter_text>Export to CSV<jupyter_code>df.to_csv('../cleaned_data/clean_parking_violations.tsv', sep='\t', index=False)
df.sample(10000).to_csv('../sampled_data/clean_parking_violations_10k_sample.tsv', sep='\t', index=False)
df.sample(100000).to_csv('../sampled_data/clean_parking_violations_100k_sample.tsv', sep='\t', index=False)
df.sample(1000000).to_csv('../sampled_data/clean_parking_violations_1million_sample.tsv', sep='\t', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14971572962728996, 'fraction_numerical': 0.028111181301326595, 'mean_word_length': 4.40443686006826, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '296782', 'license': 'MIT', 'n_tokens_mistral': 1272, 'n_tokens_neox': 1187, 'n_words': 221, 'path': 'notebooks/old/Parking violations - Data Cleaning.ipynb', 'repo_name': 'ndanielsen/dc_parking_violations_data', 'size': '3166'}