In [33]:

    

from django.db.models import Q, Count, Sum
from django.db import connections
from nc.models import Agency, Stop, Search


    

In [2]:

    

Agency.objects.count()


    

    
Out[2]:





311

In [3]:

    

sheriff = Agency.objects.filter(name__icontains='Sheriff')
sheriff.count()


    

    
Out[3]:





99

In [4]:

    

police = Agency.objects.filter(name__icontains='Police')
police.count()


    

    
Out[4]:





201

In [5]:

    

list(Agency.objects.exclude(id__in=police).exclude(id__in=sheriff))


    

    
Out[5]:





[<Agency: Brunswick Community College>,
 <Agency: Butner Public Safety>,
 <Agency: Fairmont Department of Public Safety>,
 <Agency: Guilford Technical Community College>,
 <Agency: NC Alcohol Law Enforcement>,
 <Agency: NC Division of Motor Vehicles, License and Theft Bureau>,
 <Agency: NC State Bureau of Investigation>,
 <Agency: NC State Highway Patrol>,
 <Agency: NC State Parks>,
 <Agency: NC Wildlife Enforcement>,
 <Agency: SHP - Motor Carrier Enforcement Section>]

In [15]:

    

list(Agency.objects.filter(Q(name__icontains='College') | Q(name__icontains='University')))


    

    
Out[15]:





[<Agency: Appalachian State University Police Department>,
 <Agency: Brunswick Community College>,
 <Agency: East Carolina University Police Department>,
 <Agency: Elizabeth City State University Police Department>,
 <Agency: Fayetteville State University Police Department>,
 <Agency: Guilford Technical Community College>,
 <Agency: NC A&T University Police Department>,
 <Agency: NC Central University Police Department>,
 <Agency: NC State University Police Department>,
 <Agency: UNC Asheville University Police Department>,
 <Agency: UNC Chapel Hill University Police Department>,
 <Agency: UNC Charlotte University Police Department>,
 <Agency: UNC Greensboro University Police Department>,
 <Agency: UNC Pembroke University Police Department>,
 <Agency: Western Carolina University Police Department>,
 <Agency: Winston-Salem State University Police Department>]

In [10]:

    

Stop.objects.count()


    

    
Out[10]:





18819973

In [22]:

    

top_ten_agencies = Agency.objects.annotate(total_stops=Count('stops')).values_list('name', 'total_stops').order_by('-total_stops')[:10]
list(top_ten_agencies)


    

    
Out[22]:





[('NC State Highway Patrol', 8827911),
 ('Charlotte-Mecklenburg Police Department', 1463331),
 ('Raleigh Police Department', 786806),
 ('Greensboro Police Department', 525818),
 ('Fayetteville Police Department', 426245),
 ('Winston-Salem Police Department', 424051),
 ('High Point Police Department', 262248),
 ('Durham Police Department', 254197),
 ('Cary Police Department', 204872),
 ('Wilmington Police Department', 162884)]

In [23]:

    

top_ten_agencies.aggregate(Sum('total_stops'))


    

    
Out[23]:





{'total_stops__sum': 13338363}

In [1]:

    

top_ten_percent = 13338363/18819973
"{0:.4f}%".format(top_ten_percent*100)


    

    
Out[1]:





'70.8734%'

In [2]:

    

state_highway_patrol = Agency.objects.get(name='NC State Highway Patrol')
state_highway_patrol.stops.count()


    

    
Out[2]:





8827911

In [2]:

    

"{0:.4f}%".format(8827911/18819973*100)


    

    
Out[2]:





'46.9071%'

In [32]:

    

year = connections[Stop.objects.db].ops.date_trunc_sql('year', 'date')
qs = Stop.objects.extra(select={'year': year})
qs = qs.values('year').annotate(total_stops=Count('date')).order_by('-year')
summary = [(row['year'].year, row['total_stops']) for row in list(qs)]
summary


    

    
Out[32]:





[(2014, 1429490),
 (2013, 1534460),
 (2012, 1606434),
 (2011, 1729708),
 (2010, 1723909),
 (2009, 1436964),
 (2008, 1370693),
 (2007, 1435327),
 (2006, 953488),
 (2005, 961000),
 (2004, 1010310),
 (2003, 1089837),
 (2002, 1314200),
 (2001, 588960),
 (2000, 635193)]

In [34]:

    

Search.objects.count()


    

    
Out[34]:





590720

In [42]:

    

state_search_rate = Search.objects.count()/Stop.objects.count()
"{0:.4f}%".format(state_search_rate*100)


    

    
Out[42]:





'3.1388%'

In [ ]: