CoCalc Shared Filesprocess_spokane_allegations.sagewsOpen in CoCalc with one click!
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())