Process spokane allegations

Authors: Tim Clemans SMC, William A. Stein

Views : 8

︠55752784-7e49-4d7b-956f-ee9c98a0edbb︠
import csv
import sys

f = open('spokane_police_iapro_allegations.csv', 'rt')
rows = []
try:
    reader = csv.reader(f)
    headers = next(reader, None)
    for row in reader:
        d = dict(zip(headers,row))
        d['Agency ID'] = '60734d23-91e9-4f75-bd04-19de5184f210'
        d['id'] = '%s %s %s %s' % (d['Agency ID'], d['IA No'], d['Badge/ID number'], d['Allegation'])
        d['id'] = d['id'].lower().replace(' ', '_')
        rows.append(d)
finally:
    f.close()
print rows[:5]
r.db('public').table('police_internal_affairs_allegations').insert(rows).run()

[{'Incident type': 'Citizen Complaint', 'Occurred date': '12/31/2014', 'IA No': 'C15-002', 'Title': 'Officer', 'Incident No': '14-435989', 'Badge/ID number': '359', 'Action taken': '', 'Directive': '', 'Date hired': '2/23/1993', 'id': '60734d23-91e9-4f75-bd04-19de5184f210_c15-002_359_inadequate_response', 'Last name': 'Pavlischak', 'First name': 'Charles', 'Allegation': 'Inadequate Response', 'Finding': '', 'Agency ID': '60734d23-91e9-4f75-bd04-19de5184f210', 'Finding date': ''}, {'Incident type': 'Citizen Complaint', 'Occurred date': '12/31/2014', 'IA No': 'C15-002', 'Title': 'Officer', 'Incident No': '14-435989', 'Badge/ID number': '359', 'Action taken': '', 'Directive': '', 'Date hired': '2/23/1993', 'id': '60734d23-91e9-4f75-bd04-19de5184f210_c15-002_359_failure_to_report_dv', 'Last name': 'Pavlischak', 'First name': 'Charles', 'Allegation': 'Failure to Report DV', 'Finding': '', 'Agency ID': '60734d23-91e9-4f75-bd04-19de5184f210', 'Finding date': ''}, {'Incident type': 'Citizen Complaint', 'Occurred date': '4/3/2014', 'IA No': 'C15-007', 'Title': '', 'Incident No': '14-139943', 'Badge/ID number': '', 'Action taken': '', 'Directive': '', 'Date hired': '', 'id': '60734d23-91e9-4f75-bd04-19de5184f210_c15-007__harassment', 'Last name': '', 'First name': '', 'Allegation': 'Harassment', 'Finding': '', 'Agency ID': '60734d23-91e9-4f75-bd04-19de5184f210', 'Finding date': ''}, {'Incident type': 'Citizen Complaint', 'Occurred date': '2/16/2015', 'IA No': 'C15-008', 'Title': 'Officer', 'Incident No': '15-53074', 'Badge/ID number': '1201', 'Action taken': '', 'Directive': '', 'Date hired': '3/3/2014', 'id': '60734d23-91e9-4f75-bd04-19de5184f210_c15-008_1201_demeanor', 'Last name': 'Killian', 'First name': 'Seth', 'Allegation': 'Demeanor', 'Finding': 'Exonerated', 'Agency ID': '60734d23-91e9-4f75-bd04-19de5184f210', 'Finding date': '4/21/2015'}, {'Incident type': 'Citizen Complaint', 'Occurred date': '2/16/2015', 'IA No': 'C15-008', 'Title': 'Officer', 'Incident No': '15-53074', 'Badge/ID number': '1201', 'Action taken': '', 'Directive': '', 'Date hired': '3/3/2014', 'id': '60734d23-91e9-4f75-bd04-19de5184f210_c15-008_1201_lack_of/inadequate_response', 'Last name': 'Killian', 'First name': 'Seth', 'Allegation': 'Lack of/Inadequate Response', 'Finding': 'Training/Policy Failure', 'Agency ID': '60734d23-91e9-4f75-bd04-19de5184f210', 'Finding date': '4/21/2015'}]
{u'first_error': u'Duplicate primary key `id`:\n{\n\t"Action taken":\t"",\n\t"Agency ID":\t"60734d23-91e9-4f75-bd04-19de5184f210",\n\t"Allegation":\t"Lack of/Inadequate Response",\n\t"Badge/ID number":\t"",\n\t"Date hired":\t"",\n\t"Directive":\t"",\n\t"Finding":\t"",\n\t"Finding date":\t"",\n\t"First name":\t"",\n\t"IA No":\t"I14-038",\n\t"Incident No":\t"",\n\t"Incident type":\t"Inquiry",\n\t"Last name":\t"",\n\t"Occurred date":\t"7/1/2014",\n\t"Title":\t"",\n\t"id":\t"60734d23-91e9-4f75-bd04-19de5184f210_i14-038__lack_of/inadequate_response"\n}\n{\n\t"Action taken":\t"",\n\t"Agency ID":\t"60734d23-91e9-4f75-bd04-19de5184f210",\n\t"Allegation":\t"Lack of/Inadequate Response",\n\t"Badge/ID number":\t"",\n\t"Date hired":\t"",\n\t"Directive":\t"",\n\t"Finding":\t"",\n\t"Finding date":\t"",\n\t"First name":\t"",\n\t"IA No":\t"I14-038",\n\t"Incident No":\t"",\n\t"Incident type":\t"Inquiry",\n\t"Last name":\t"",\n\t"Occurred date":\t"7/1/2014",\n\t"Title":\t"",\n\t"id":\t"60734d23-91e9-4f75-bd04-19de5184f210_i14-038__lack_of/inadequate_response"\n}', u'errors': 22, u'deleted': 0, u'unchanged': 0, u'skipped': 0, u'replaced': 0, u'inserted': 831}

︠cf89ad12-20df-4d93-803d-6a80040995d2︠
import rethinkdb as r
r.connect( "localhost", 28015).repl()

<rethinkdb.net.DefaultConnection object at 0x7f37093861d0>

︠9bc282ca-9471-4c6b-9af5-ff4f19819c6f︠
r.db_create('public').run()
r.db('public').table_create('agencies').run()
r.db('public').table_create('police_internal_affairs_allegations').run()
r.db('public').table('agencies').insert({'name': 'Spokane Police', 'city': 'Spokane', 'state': 'Washington'}).run()

{u'config_changes': [{u'old_val': None, u'new_val': {u'id': u'0baccc9d-8121-41ec-adbb-ba9c36b0e6bb', u'name': u'public'}}], u'dbs_created': 1}
{u'tables_created': 1, u'config_changes': [{u'old_val': None, u'new_val': {u'primary_key': u'id', u'durability': u'hard', u'db': u'public', u'shards': [{u'nonvoting_replicas': [], u'primary_replica': u'compute4_us_bfx', u'replicas': [u'compute4_us_bfx']}], u'indexes': [], u'write_acks': u'majority', u'id': u'590c5126-8ffe-4fe9-a7bc-5f5f587cd75d', u'name': u'agencies'}}]}
{u'tables_created': 1, u'config_changes': [{u'old_val': None, u'new_val': {u'primary_key': u'id', u'durability': u'hard', u'db': u'public', u'shards': [{u'nonvoting_replicas': [], u'primary_replica': u'compute4_us_bfx', u'replicas': [u'compute4_us_bfx']}], u'indexes': [], u'write_acks': u'majority', u'id': u'bb6ada7e-d054-4927-b63e-cda75683d322', u'name': u'police_internal_affairs_allegations'}}]}
{u'errors': 0, u'deleted': 0, u'generated_keys': [u'60734d23-91e9-4f75-bd04-19de5184f210'], u'unchanged': 0, u'skipped': 0, u'replaced': 0, u'inserted': 1}

'
db = r.db('public')
db.table('police_internal_affairs_allegations').count().run()

831

list(db.table('police_internal_affairs_allegations').filter({'Last name': 'Killian'}).pluck('Allegation').run())

[{u'Allegation': u'Demeanor'}, {u'Allegation': u'Lack of/Inadequate Response'}]


import rethinkdb as r
r.query("r.table('police_internal_affairs_allegations')")

Error in lines 2-2
Traceback (most recent call last):
  File "/projects/sage/sage-6.9/local/lib/python2.7/site-packages/smc_sagews/sage_server.py", line 905, in execute
    exec compile(block+'\n', '', 'single') in namespace, locals
  File "", line 1, in <module>
TypeError: 'module' object is not callable

import ast
ast.literal_eval('r.table("police_internal_affairs_allegations")')

Error in lines 2-2
Traceback (most recent call last):
  File "/projects/sage/sage-6.9/local/lib/python2.7/site-packages/smc_sagews/sage_server.py", line 905, in execute
    exec compile(block+'\n', '', 'single') in namespace, locals
  File "", line 1, in <module>
  File "/projects/sage/sage-6.9/local/lib/python/ast.py", line 80, in literal_eval
    return _convert(node_or_string)
  File "/projects/sage/sage-6.9/local/lib/python/ast.py", line 79, in _convert
    raise ValueError('malformed string')
ValueError: malformed string

def is_valid(query_string):
    if not query_string.startswith('table'):
        return False
    
eval('r.db_list().run()')

[u'public', u'rethinkdb', u'test']

allegations = list(r.db('public').table('police_internal_affairs_allegations').eq_join('Agency ID', r.db('public').table('agencies')).without({'right': "id"}).zip().run())

allegations[0]

{u'city': u'Spokane', u'Incident type': u'Citizen Complaint', u'Incident No': u'', u'IA No': u'C15-017', u'Directive': u'', u'Action taken': u'', u'Date hired': u'', u'Badge/ID number': u'', u'Title': u'', u'id': u'60734d23-91e9-4f75-bd04-19de5184f210_c15-017__negligent_driving', u'First name': u'', u'state': u'Washington', u'Allegation': u'Negligent Driving', u'Finding': u'', u'Agency ID': u'60734d23-91e9-4f75-bd04-19de5184f210', u'name': u'Spokane Police', u'Finding date': u'', u'Occurred date': u'3/1/2015', u'Last name': u''}

import rethinkdb as r
r.connect( "localhost", 28015).repl()
def download_socrata_dataset(url):
    import requests
    table_name = url.strip('.json').strip('https://').replace('/', '_').replace('-', '_').replace('.', '_')
    r.db('public').table_create(table_name).run()
    data = requests.get(url).json()
    r.db('public').table(table_name).insert(data).run()
    #print url.strip('.json').strip('https://')
    #return requests.get(url).json()
download_socrata_dataset('https://data.seattle.gov/resource/qew5-gawh.json')
download_socrata_dataset('https://data.seattle.gov/resource/pafy-bfmu.json')

<rethinkdb.net.DefaultConnection object at 0x7f3708216f10>

# ssh -4 -i ~/aws -L 24455:localhost:28015 [email protected]
#r.connect( "localhost", 24455).repl()

import rethinkdb as r
r.connect( "localhost", 28015).repl()

r.connect( "localhost", 28015).repl()

<rethinkdb.net.DefaultConnection object at 0x7f370990c8d0>
<rethinkdb.net.DefaultConnection object at 0x7f370990c9d0>

r.db('public').table_list().run()

[u'agencies', u'data_seattle_gov_resource_pafy_bfmu', u'data_seattle_gov_resource_qew5_gaw', u'police_internal_affairs_allegations']

list(r.db('public').table('data_seattle_gov_resource_pafy_bfmu').run())[0]

{u'opa_case_number': u'2015OPA-0622', u'completed_date_2': u'10/27/15', u'external_case_number': u'2014-222942', u'due_date_2': u'10/26/15', u'task_creation_date_2': u'10/13/15', u'currentstatus': u'Done', u'status_description': u'OPA Close Case', u'id': u'01086ca6-5ea1-4a23-8911-801cdcf474ee'}

list(r.db('public').table('data_seattle_gov_resource_qew5_gaw').run())[0]

{u'complaint_status': u"The complaint is being reviewed to determine whether it should be handled by the employee's supervisor (Supervisor Action) or if a full misconduct investigation is required.", u'id': u'1760e348-473a-4ea2-9a12-536975f82b23', u'data_field': u'OPA Classification'}

inner_join = r.db('public').table("data_seattle_gov_resource_pafy_bfmu").inner_join(
    r.db('public').table("data_seattle_gov_resource_qew5_gaw"),
    lambda post, user:
        post["status_description"] == user["data_field"]
).zip().run()

list(inner_join)[0]

{u'opa_case_number': u'2015OPA-0622', u'data_field': u'OPA Close Case', u'completed_date_2': u'10/27/15', u'complaint_status': u'Complete\nThe case has been fully investigated, reviewed and a determination has been made regarding whether the allegations associated with the complaint are Sustained or Not Sustained. You will receive a letter in the mail with details regarding the outcome of the investigation.  \nThe employee has thirty days from the date the case was closed to appeal the findings of the investigation. ', u'external_case_number': u'2014-222942', u'due_date_2': u'10/26/15', u'task_creation_date_2': u'10/13/15', u'currentstatus': u'Done', u'status_description': u'OPA Close Case', u'id': u'806c619b-bc21-4288-9c82-56ce692fd7ce'}

combine_tables(table1, table2, col_from_t1, col_from_t2, columns_to_bring_over):
    rows = list(r.db('public').table(table1).run())
    for i, row in enumerate(rows):

Error in lines 1-3
Traceback (most recent call last):
  File "/projects/sage/sage-6.9/local/lib/python2.7/site-packages/smc_sagews/sage_server.py", line 905, in execute
    exec compile(block+'\n', '', 'single') in namespace, locals
  File "<string>", line 1
    combine_tables(table1, table2, col_from_t1, col_from_t2, columns_to_bring_over):
                                                                                   ^
SyntaxError: invalid syntax

allegations = list(r.db('public').table('police_internal_affairs_allegations').eq_join('Agency ID', r.db('public').table('agencies')).without({'right': "id"}).zip().run())