In [1]:
import xlrd

def worksheet_to_list_of_dicts( worksheet ):
    list_of_dicts = []
    data = [] #make a data store
    for i in xrange(worksheet.nrows):
        data.append(worksheet.row_values(i))
    
    header = data[0]

    for i in data[1:]:
        x = dict( zip( header, i ) )
    
        list_of_dicts.append( x )
    return list_of_dicts

def get_seriousness_levels( convictions ):
    seriousness_levels = []
    for i in convictions:
        x = i['Offense Seriousness Level']
        if '(' in str( x ):
            level = int( x[1] )
        else:
            level = int( x )
            
        seriousness_levels.append( level )
        
    return seriousness_levels

In [2]:
workbook = xlrd.open_workbook('../data/mastercrimelist.xls' )
print workbook.sheet_names()
by_title_worksheet = workbook.sheet_by_name('By Title')
by_mgl_worksheet = workbook.sheet_by_name('By MGL')


[u'By MGL', u'By Title', u'References', u'Notes and Glossary']

In [3]:
by_mgl_list_of_dicts = worksheet_to_list_of_dicts( by_mgl_worksheet )
by_title_list_of_dicts = worksheet_to_list_of_dicts( by_title_worksheet )

offense_list_of_dicts = by_title_list_of_dicts
print offense_list_of_dicts[0]


{u'Min H/C': '', u'Mand. Time': '', u'Offense Reference': u'c. 265 s. 13A(a)', u'Notes': '', u'Offense Penalty Reference': u'', u'Max H/C': u'2 1/2 years', u'Grid': u'Yes', u'Staircase Factor': '', u'Offense': u'A&B c. 265 s. 13A(a)', u'Penalty Type ': u'Misd.', u'Min Prison': '', u'Max Prison': '', u'Offense Seriousness Level': 3.0}

In [4]:
import json
x = json.dumps( offense_list_of_dicts )

with open( '../../courtcal/data/offense_list.json', 'w' ) as f:
    f.write( x )

In [8]:
# generate 1000 criminal records

import random

random_convictions_database = []

for i in range( 1000 ):

    num_convictions = random.randint( 0, 10 )
    #print num_convictions
    conviction_indices = random.sample(range(len( offense_list_of_dicts )), num_convictions)
    convictions = []

    for idx in conviction_indices:
        convictions.append( offense_list_of_dicts[idx] )
    
    random_convictions_database.append( convictions )

In [9]:
print [ len(i) for i in random_convictions_database ]


[6, 9, 0, 2, 6, 0, 9, 1, 1, 2, 3, 2, 10, 9, 8, 8, 10, 0, 3, 7, 1, 4, 3, 8, 8, 2, 7, 2, 0, 2, 3, 3, 9, 1, 6, 3, 2, 9, 3, 10, 9, 8, 6, 6, 2, 3, 3, 2, 4, 1, 3, 3, 4, 5, 8, 0, 5, 2, 6, 9, 8, 1, 3, 6, 8, 0, 7, 2, 8, 7, 8, 5, 1, 9, 4, 2, 2, 6, 10, 4, 9, 7, 7, 1, 4, 7, 8, 9, 3, 1, 1, 2, 8, 5, 1, 4, 3, 10, 5, 10, 5, 5, 3, 9, 1, 4, 8, 2, 6, 9, 0, 0, 6, 8, 4, 8, 0, 4, 8, 0, 2, 5, 0, 8, 0, 7, 3, 3, 9, 2, 6, 10, 5, 9, 9, 7, 2, 8, 4, 10, 7, 0, 5, 1, 4, 9, 2, 5, 10, 7, 3, 10, 9, 0, 8, 10, 2, 6, 0, 3, 5, 6, 0, 10, 3, 7, 4, 1, 2, 6, 9, 1, 8, 9, 10, 1, 1, 10, 6, 10, 6, 8, 3, 5, 8, 0, 8, 9, 5, 2, 10, 6, 9, 8, 0, 5, 8, 0, 2, 9, 6, 9, 10, 1, 9, 7, 7, 1, 5, 7, 3, 0, 7, 10, 7, 4, 10, 3, 9, 2, 1, 6, 5, 7, 4, 1, 9, 10, 9, 8, 0, 4, 0, 3, 7, 0, 8, 4, 9, 1, 3, 6, 10, 6, 9, 8, 8, 5, 9, 1, 5, 3, 3, 0, 5, 2, 9, 10, 10, 7, 1, 6, 0, 6, 10, 2, 2, 4, 3, 3, 6, 2, 5, 1, 3, 4, 9, 9, 3, 8, 6, 4, 8, 1, 2, 8, 5, 1, 9, 10, 9, 10, 8, 2, 2, 7, 6, 8, 7, 2, 1, 6, 6, 1, 2, 5, 1, 6, 10, 4, 2, 6, 10, 3, 0, 5, 8, 7, 9, 1, 3, 9, 3, 8, 6, 7, 4, 1, 0, 5, 10, 6, 5, 0, 0, 4, 1, 9, 7, 0, 6, 9, 6, 5, 8, 9, 8, 10, 8, 5, 8, 10, 8, 5, 4, 0, 4, 5, 5, 9, 10, 1, 2, 8, 10, 9, 0, 8, 10, 7, 0, 1, 3, 5, 9, 6, 8, 4, 10, 9, 7, 10, 3, 6, 9, 8, 5, 0, 5, 0, 4, 6, 4, 6, 6, 9, 7, 5, 10, 5, 3, 3, 0, 10, 1, 0, 2, 4, 9, 2, 3, 1, 7, 7, 6, 3, 8, 5, 5, 7, 0, 8, 1, 5, 5, 7, 0, 6, 2, 3, 3, 8, 7, 0, 1, 10, 10, 7, 10, 2, 8, 6, 1, 6, 8, 2, 8, 3, 10, 0, 4, 0, 3, 3, 5, 0, 2, 8, 10, 1, 5, 8, 6, 3, 9, 2, 4, 1, 3, 8, 3, 4, 9, 2, 1, 5, 0, 9, 1, 10, 1, 0, 3, 2, 3, 8, 5, 7, 9, 1, 6, 0, 9, 0, 3, 9, 7, 9, 3, 10, 7, 6, 4, 9, 3, 1, 2, 9, 10, 0, 5, 10, 4, 9, 10, 8, 7, 9, 0, 7, 4, 8, 5, 7, 4, 5, 5, 5, 7, 8, 3, 8, 4, 7, 3, 0, 9, 10, 4, 6, 6, 3, 2, 7, 7, 0, 1, 5, 9, 5, 9, 2, 6, 0, 8, 9, 10, 4, 5, 4, 8, 0, 3, 3, 5, 4, 0, 3, 5, 5, 5, 0, 10, 0, 4, 8, 8, 6, 6, 10, 8, 7, 6, 10, 8, 1, 10, 8, 1, 0, 0, 1, 8, 0, 3, 0, 6, 4, 6, 4, 7, 9, 3, 2, 3, 2, 2, 9, 9, 0, 9, 9, 10, 10, 4, 8, 1, 6, 1, 0, 1, 3, 3, 3, 1, 8, 8, 10, 9, 2, 4, 1, 8, 9, 9, 9, 1, 0, 2, 8, 9, 4, 2, 0, 3, 3, 4, 2, 9, 3, 2, 2, 4, 3, 10, 2, 8, 2, 10, 3, 8, 4, 2, 0, 6, 0, 2, 2, 0, 3, 4, 5, 5, 6, 9, 0, 7, 8, 10, 2, 0, 5, 0, 3, 9, 8, 1, 10, 9, 8, 10, 4, 10, 4, 0, 7, 3, 4, 5, 5, 7, 8, 9, 8, 10, 9, 2, 0, 6, 0, 10, 4, 2, 5, 10, 6, 0, 0, 1, 8, 4, 6, 5, 5, 8, 0, 7, 9, 0, 10, 1, 4, 9, 1, 5, 3, 9, 7, 3, 8, 2, 0, 6, 1, 7, 6, 9, 5, 1, 3, 8, 2, 3, 5, 6, 10, 8, 6, 5, 3, 4, 7, 8, 9, 4, 0, 8, 4, 0, 6, 9, 6, 3, 4, 5, 3, 2, 7, 9, 5, 1, 6, 2, 1, 7, 9, 2, 3, 8, 4, 3, 6, 3, 9, 4, 10, 8, 4, 3, 8, 5, 2, 9, 2, 8, 8, 1, 10, 4, 3, 8, 2, 0, 8, 4, 7, 1, 9, 5, 9, 0, 6, 10, 1, 7, 8, 3, 3, 10, 5, 2, 6, 3, 2, 3, 8, 10, 3, 6, 2, 1, 3, 9, 0, 1, 10, 0, 8, 0, 8, 6, 5, 5, 6, 3, 9, 8, 10, 1, 7, 4, 4, 0, 8, 5, 4, 5, 3, 1, 6, 6, 6, 1, 2, 9, 1, 3, 8, 6, 2, 0, 1, 2, 4, 6, 8, 5, 0, 8, 1, 9, 0, 8, 7, 4, 10, 1, 3, 1, 3, 2, 7, 2, 8, 3, 5, 2, 8, 2, 4, 6, 7, 4, 2, 9, 6, 2, 7, 5, 5, 1, 3, 2, 1, 4, 5, 10, 6, 6, 4, 8, 1, 4, 7, 7, 7, 6, 7, 9, 10, 3, 1, 1, 10, 0, 2, 6, 6, 2, 10, 0, 7, 9, 0, 9, 0, 10, 4, 9, 3, 0, 0, 3, 4, 1, 8, 5, 10, 8, 7, 8, 9, 1, 7, 10, 8, 0, 9, 4, 9, 2, 3, 6, 0, 2, 2, 2, 6, 5, 3, 4, 2, 7, 10, 2, 9, 5, 5, 3, 0]

Criminal History Groups Massachusetts Sentencing Guidelines

E

• Serious Violent Record • Two or more prior convictions in any combination for offenses in level 7 through 9

D

• Violent or Repetitive Record • Six or more prior convictions in any combination for offenses in levels three, four, five, or six; or • Two or more prior convictions in any combination for offenses in levels five or six; or • One prior conviction for offenses in levels seven through nine.

C

• Serious Record • Three to five prior convictions in any combination for offenses in levels three or four; or • One prior conviction for offenses in levels five or six.

B

• Moderate Record • Six or more prior convictions in any combination for offenses in levels one or two; or • One or two prior convictions in any combination for offenses levels three or four.

A

• No/Minor Record • No prior convictions of any kind; or • One to five prior convictions in any combination for offenses in levels one or two.


In [10]:
# LOGIC
import collections

all_convictions = random_convictions_database[:10]
for test_convictions in all_convictions:
    seriousness_levels = get_seriousness_levels( test_convictions )

    conviction_counts = collections.Counter( seriousness_levels )
    to_print = zip( seriousness_levels, [ i['Offense'] for i in test_convictions ] )
    print "Number of Crimes:", len( test_convictions )
    print "Crimes:"
    for i in to_print:
        print i
    print conviction_counts

    criminal_history_group = None

    # E
    if conviction_counts[7] + conviction_counts[8] + conviction_counts[9] >= 2:
        criminal_history_group = "E"

    # D 
    elif conviction_counts[7] + conviction_counts[8] + conviction_counts[9] >= 1:
        criminal_history_group = "D"    
    elif conviction_counts[5] + conviction_counts[6] >= 2:
        criminal_history_group = "D"
    elif conviction_counts[3] + conviction_counts[4] + conviction_counts[5] + conviction_counts[6] >= 6:
        criminal_history_group = "D"

    # C
    elif conviction_counts[5] + conviction_counts[6] >= 1:
        criminal_history_group = "C"
    elif conviction_counts[3] + conviction_counts[4] >= 3:
        criminal_history_group = "C"

    # B
    elif conviction_counts[3] + conviction_counts[4] >= 1:
        criminal_history_group = "C"
    elif conviction_counts[1] + conviction_counts[2] >= 6:
        criminal_history_group = "C"


    # A
    elif conviction_counts[1] + conviction_counts[2] >= 1:
        criminal_history_group = "A"
    elif len( convictions ) == 0:
        criminal_history_group = "A"


    
    print "Criminal History Group:", criminal_history_group
    print "\n"


Number of Crimes: 6
Crimes:
(3, u'OBSCENE MATTER TO MINOR, 2ND AND SUBSQ. OFF. c. 272 s. 28')
(1, u'HOISTING MACHINERY, OPERATION VIOLATION c. 146 s. 53')
(4, u'A&B ON PERSON WITH INTELLECTUAL DISABILITY c. 265 s. 13F')
(2, u'GOVERNMENT AGENCY OR STATE FAIR, FALSELY HOLD OUT AS c. 110 s. 4B')
(1, u'ADVERTISING RESEMBLING CURRENCY c. 267 s. 29 ')
(2, u'FOREIGN MONEY DEPOSITS VIOLATION c. 169 s. 1 through 16')
Counter({1: 2, 2: 2, 3: 1, 4: 1})
Criminal History Group: C


Number of Crimes: 9
Crimes:
(4, u'FIREARM, CARRY W/O LICENSE c. 269 s. 10(a)')
(2, u'LARCENY UNDER $250 BY SINGLE SCHEME c. 266 s. 30(1)')
(1, u'PESTICIDE, IMPROPER USE, 1ST AND SUBSQ., c. 132B s. 6A')
(3, u'CREDIT +$250 BY FALSE FINANCIAL STATEMNT c. 266 s. 33(2)')
(1, u'BANKING CO FAIL REPORT RESERVES c. 172A s. 10')
(2, u'DRUG, FAIL REPORT DISPENSING c. 94C s. 24(a)')
(2, u'INNKEEPER, DEFRAUD, UNDER $100 c. 140 s. 12')
(2, u'CIGARETTES, LICENSE, VENDING MACHINE VIOLATIONS c. 64C s. 10')
(2, u'ESTATE TAX, EVADE c. 65C s. 26')
Counter({2: 5, 1: 2, 3: 1, 4: 1})
Criminal History Group: C


Number of Crimes: 0
Crimes:
Counter()
Criminal History Group: A


Number of Crimes: 2
Crimes:
(8, u'PERJURY, SUBORN IN TRIAL OF CAPITAL CASE c. 268 s. 2')
(2, u'CANDIDATE SOLICITED DONATION VIOLATION c. 55 s. 11')
Counter({8: 1, 2: 1})
Criminal History Group: D


Number of Crimes: 6
Crimes:
(1, u'AFFRAY Common Law')
(3, u'CHILD UNDER 10, ABANDON, WITH DEATH c. 119 s. 39')
(1, u'BAKERY FACILITY VIOLATION, 3RD AND SUBSQ. OFF. c. 94 s. 9A')
(3, u'DRUG, POSSESS CLASS B, SUBSQ. OFF. c. 94C s. 34')
(5, u'PHENMETRAZINE, TRAFFICKING IN c. 94C s. 32E(b)(1) - 18 to 36 g')
(2, u'ESTATE TAX, CONCEAL GOODS TO AVOID FELONY c. 65C s. 28')
Counter({1: 2, 3: 2, 2: 1, 5: 1})
Criminal History Group: C


Number of Crimes: 0
Crimes:
Counter()
Criminal History Group: A


Number of Crimes: 9
Crimes:
(2, u'TOXICS USE VIOLATION c. 21I s. 21')
(1, u'FIRE VIOLATION NOTICE, DAMAGE c. 148 s. 30A')
(1, u'FAILURE TO DELIVER CERTIFICATE OF TITLE OR SALVAGE TITLE TO TRANSFEREE OR REGISTRAR c. 90D s. 32(b)')
(3, u'TRADE SECRET, LARCENY OF c. 266 s. 30(4) ')
(2, u'MBOAT APPLICATION FOR CERTIFICATION OF NUMBER; FALSE STATEMENT c. 90B s. 8(e)(1)')
(7, u'ROBBERY, ARMED & MASKED c. 265 s. 17 ')
(2, u'LIQUOR, STORE/WAREHOUSE WITHOUT PERMIT c. 138 s. 20A')
(1, u'WATER SUPPLY, DRIVE ANIMAL ON FROZEN c. 111 s. 174 ')
(1, u'DISCLOSURE OF INFORMATION BY TAX PREPARER; VIOLATION c. 62C s. 74')
Counter({1: 4, 2: 3, 3: 1, 7: 1})
Criminal History Group: D


Number of Crimes: 1
Crimes:
(2, u'COUNTERFEIT DRUG, DISTRIBUTE OR POSSESS WITH INTENT c. 94C s. 32G')
Counter({2: 1})
Criminal History Group: A


Number of Crimes: 1
Crimes:
(2, u'BALLOT QUESTION COMMITTEE; CONTRIBUTION; VIOLATION c. 55 s. 6B')
Counter({2: 1})
Criminal History Group: A


Number of Crimes: 2
Crimes:
(3, u'COUNTERFEIT COIN, COMMON UTTERER OF c. 267 s. 19')
(2, u'INSURANCE INFO, GET ON FALSE PRETENSES c. 175I s. 22')
Counter({2: 1, 3: 1})
Criminal History Group: C