Prepare A Crime Database For Geocoding

• Tweet
• 

        // I used this to prepare a crime database for geocoding

import csv
import os

## move into the correct directory

os.chdir('DIRECTORY WHERE DATA IS STORED')
)
cwd = os.getcwd()

## open the spreadsheet

reader = csv.reader(open('crimes.csv', 'r'), dialect='excel')
c = csv.writer(open("september crime.csv", "wb"))

## define fields

for row in reader:
    street_number = row[9]
    street_name = row[3]
    date = row[1]
    init_time = row[2]
    city = row[4]
    state = row[5]
    incident = row[10]

## make changes to existing fields, defining the fields for our new spreadsheet

    address = (street_number + ' ' + street_name)

    if city[0:2] == 'CH':
        city = 'CHAPEL HILL'
    if city[0:2] == 'OC':
        city = 'ORANGE COUNTY'
    if city[0:2] == 'CA':
        city = 'CARRBORO'
    

## format time

## for times that are four digits
    if len(init_time) == 4:
        minutes = init_time[2:4]
        if int(init_time[0:2]) > 12:
            hours = abs(12 - int(init_time[0:2]))
            time = (str(hours) + ':' + minutes + ' PM')
        else:
            hours = init_time[0:2]
            time = (str(hours) + ':' + minutes + ' AM')
        
## for times that are three digits

    elif len(init_time) == 3:
        minutes = init_time[1:3]
        time = (init_time[0] + ':' + minutes + ' AM')

    else:
        time = ' '
        
    ## create categories
    ## set the default to misc
    category = 'Miscellaneous'

    ## assault categories
    if incident.find('ASSAULT') != -1:
        category = 'Assault'

    if incident.find('ASLT') != -1:
        category = 'Assault'

    if incident.find('RAPE') != -1:
        category = 'Assault'

    if incident.find('FIGHTING') != -1:
        category = 'Assault'

    ## automobile categories
    if incident.find('AUTO') != -1:
        category = 'Automobile related'

    if incident.find('VEHICLE') != -1:
        category = 'Automobile related'

    if incident.find('LICENSE PLATE') != -1:
        category = 'Automobile related'

    if incident.find('B&E VEH') != -1:
        category = 'Automobile related'

    if incident.find('B&E-VEH') != -1:
        category = 'Automobile related'

    ## burglary categories

    if incident.find('BURGLARY') != -1:
        category = 'Burglary'

    ## drug categories

    if incident.find('DRUG') != -1:
        category = 'Drug crimes'

    if incident.find('NARCOTIC S') != -1:
        category = 'Drug crimes'

    if incident.find('MARIJUANA') != -1:
        category = 'Drug crimes'

    if incident.find('COCAINE') != -1:
        category = 'Drug crimes'

    if incident.find('SIMPLE POSSESSION') != -1:
        category = 'Drug crimes'

    if incident.find('MAINTAIN') != -1:
        category = 'Drug crimes'

    ## homicide

    if incident.find('HOMICIDE') != -1:
        category = 'Homicide'

    ## robbery categories

    if incident.find('ROBBERY') != -1:
        category = 'Robbery'

    ## theft

    if incident.find('LARCENY') != -1:
        category = 'Theft'

    if incident.find('BUSINESS') != -1:
        category = 'Theft'

    if incident.find('RESIDENCE') != -1:
        category = 'Theft'

    if incident.find('BREAKING') != -1:
        category = 'Theft'

    ## alcohol crimes

    if incident.find('ALCOHOL') != -1:
        category = 'Alcohol'

    if incident.find('CONSUM') != -1:
        category = 'Alcohol'

    if incident.find('IMPAIRED') != -1:
        category = 'Alcohol'

    if incident.find('DRUNK') != -1:
        category = 'Alcohol'

    if incident.find('FRAUDULENT') != -1:
        category = 'Alcohol'

    if incident.find('MALT') != -1:
        category = 'Alcohol'

    if incident.find('OPEN') != -1:
        category = 'Alcohol'

    if incident.find('LIQUOR') != -1:
        category = 'Alcohol'

    if incident.find('UNDERAGE') != -1:
        category = 'Alcohol'

    ## traffic

    if incident.find('TRAFFIC') != -1:
        category = 'Traffic'

    if incident.find('ROAD') != -1:
        category = 'Traffic'

    if incident.find('IMPROPER') != -1:
        category = 'Traffic'

    if incident.find('HIT') != -1:
        category = 'Traffic'


    ## here are the exceptions

    if incident.find('ASSAULTIVE') != -1:
        category = 'Miscellaneous'
    if incident.find('NO ASSAULT') != -1:
        category = 'Miscellaneous'
    if incident.find('STOLEN AUTO') != -1:
        category = 'Miscellaneous'
    if incident.find('MAINTAIN') != -1:
        category = 'Drug crimes'
    if incident.find('B&E, LARCENY F/VEH') != -1:
        category = 'Automobile related'
    if incident.find('LARCENY FROM A') != -1:
        category = 'Automobile related'
    if incident.find('LARCENY OF LICENSE') != -1:
        category = 'Automobile related'
    if incident.find('B&E , LARCENY F/V') != -1:
        category = 'Automobile related'
    if incident.find('B&E, LARCENY F/V') != -1:
        category = 'Automobile related'
    
   # print category
    
    #write the new spreadsheet
    c.writerow([date, time, address, city, state, incident, category])

print 'The conversion is complete.'

Tags:
• Crime
• Database
• geocoding
• Python