In [1]:
import datetime
import pandas as pd
%matplotlib inline
import matplotlib.pyplot as pyplot

In [2]:
parser = lambda x: datetime.strptime(x, '%Y-%m-%d %H:%M:%S')

In [3]:
df = pd.read_excel('/home/copelco/Desktop/MD/2013.xlsx',
                   parse_dates={'datetime': ['Date of Stop']})

In [4]:
list(df.columns.values)


Out[4]:
['datetime',
 'Location',
 'Agency',
 'Time of Stop',
 'Gender',
 'DOB',
 'Age at the time of stop',
 'Unnamed: 7',
 'Race',
 'State of Residence',
 'Registration (tag)',
 'State of Registration',
 'County of Residence',
 'Stop Reason (Abbreviated)',
 'Stop Reason',
 'Search',
 'Search Reason',
 'Disposition',
 'Outcome',
 'Arrest Reason',
 'Arrest Made',
 'Search Conducted',
 'Duration of Search (in minutes)',
 'Duration of Stop      (in minutes)',
 'Reason for Search or Stop Article',
 'Reason for Search  or Stop Section',
 'Reason for Search or Stop Sub-Section',
 'Reason for Search or Stop Paragraph',
 'Crime Charged Article',
 'Crime Charged Section',
 'Crime Charged Sub-Section',
 'Crime Charged Paragraph']

In [65]:
len(df.index)


Out[65]:
760613

In [4]:
df.Agency.value_counts()


Out[4]:
Maryland State Police                                           208301
Baltimore Police Department                                      80308
Baltimore County Police Department                               72651
Montgomery County Police Department                              54647
Maryland Transportation Authority Police                         52497
Prince George's County Police Department                         40956
Anne Arundel County Police Department                            38590
Howard County Police Department                                  27433
Frederick Police Department                                      19840
Ocean City Police Department                                     13703
Charles County Sheriff's Office                                   9246
Harford County Sheriff's Office                                   9240
University of Maryland College Park Police                        8194
Greenbelt Police Department                                       7024
Carroll County Sheriff's Office                                   6083
Calvert County Sheriff's Office                                   5397
Laurel Police Department                                          5198
Gaithersburg Police Department                                    4828
Washington County Sheriff's Office                                4567
Maryland National Capital Park Police Montgomery County           4483
Annapolis Police Department                                       4249
Rockville Police Department                                       4145
Westminster Police Department                                     4048
Saint Mary's County Sheriff's Office                              3828
Bowie Police Department                                           3459
Maryland National Capital Park Police Prince George's County      3100
Wicomico County Sheriff's Office                                  3032
Fruitland Police Department                                       2812
Cecil County Sheriff's Office                                     2772
Easton Police Department                                          2687
                                                                 ...  
Hampstead Police Department                                        210
Delmar Police Department                                           186
Montgomery County Sheriff's Office                                 183
Department of General Services                                     183
Upper Marlboro Police Department                                   163
St. Michaels Police Department                                     152
University Park Police                                             142
Allegany County Sheriff's Office                                   141
Bladensburg Police Department                                      137
Comptroller of Maryland                                            133
Capitol Heights Police Department                                  133
Baltimore City School Police                                       129
University of Maryland Eastern Shore Police                        113
District Heights Police Department                                 104
Smithsburg Police Department                                       102
Greensboro Police Department                                       100
University of Baltimore Police Department                           96
Rock Hall Police Department                                         81
Colmar Police Department                                            64
Anne Arundel County Sheriff's Office                                50
Maryland Motor Vehicle Administration                               39
Oxford Police Department                                            35
Salisbury University Police Department                              26
Howard County Sheriff's Office                                      13
Manchester Police Department                                        10
Bowie State University Police Department                             7
Trappe Police Department                                             5
Department of Health and Mental Hygiene                              4
Oakland Police Department                                            2
Brunswick Police Department                                          1
Name: Agency, dtype: int64

In [25]:
is_search = df['Search'].notnull()
len(df[is_search].index)


Out[25]:
23079

In [49]:
df[is_search].groupby(df.Agency)['Agency'].count().sort_values(ascending=False)


Out[49]:
Agency
Montgomery County Police Department                             3053
Baltimore County Police Department                              2893
Maryland State Police                                           2720
Howard County Police Department                                 1920
Prince George's County Police Department                        1698
Anne Arundel County Police Department                           1667
Ocean City Police Department                                    1038
Maryland Transportation Authority Police                         777
Frederick Police Department                                      661
Charles County Sheriff's Office                                  580
Harford County Sheriff's Office                                  461
Cecil County Sheriff's Office                                    315
Annapolis Police Department                                      315
Laurel Police Department                                         308
Calvert County Sheriff's Office                                  277
University of Maryland College Park Police                       250
Baltimore Police Department                                      244
Greenbelt Police Department                                      221
Cambridge Police Department                                      212
Westminster Police Department                                    211
Fruitland Police Department                                      181
Wicomico County Sheriff's Office                                 180
Gaithersburg Police Department                                   179
Maryland National Capital Park Police Montgomery County          144
Washington County Sheriff's Office                               134
Elkton Police Department                                         131
Maryland National Capital Park Police Prince George's County     106
Riverdale Police Department                                       99
Cumberland Police Department                                      94
Rockville Police Department                                       90
                                                                ... 
Hurlock Police Department                                         16
Frostburg State University                                        14
Talbot County Sheriff's Office                                    13
Snow Hill Police Department                                       11
Coppin State University Police                                    10
Maryland Transit Administration                                    9
Chevy Chase Village Police Department                              8
Bel Air Police Department                                          7
Montgomery County Sheriff's Office                                 7
Allegany County Sheriff's Office                                   6
Hampstead Police Department                                        6
Ridgley Police Department                                          6
St. Michaels Police Department                                     5
Capitol Heights Police Department                                  5
Colmar Police Department                                           5
District Heights Police Department                                 5
Anne Arundel County Sheriff's Office                               5
Boonsboro Police Department                                        5
Berwyn Heights Police Department                                   4
University of Maryland Baltimore Police                            4
Department of General Services                                     3
Crofton Police Department                                          3
Ocean Pines Police Department                                      3
Frostburg City Police Department                                   2
University Park Police                                             1
Manchester Police Department                                       1
Rock Hall Police Department                                        1
University of Maryland Eastern Shore Police                        1
Upper Marlboro Police Department                                   1
Greensboro Police Department                                       1
Name: Agency, dtype: int64

In [50]:
len(df[is_search].index)/len(df.index)


Out[50]:
0.03034263153535372

In [6]:
df.Race.unique()


Out[6]:
array(['h', 'a', 'w', 'b', 'u', 'o', nan], dtype=object)

In [7]:
df.Outcome.unique()


Out[7]:
array(['sero', 'warn', 'cit', 'arr', nan], dtype=object)

In [8]:
df.Search.unique()


Out[8]:
array([nan, 'both', 'prop', 'pers', '   '], dtype=object)

In [13]:
df.Disposition.unique()


Out[13]:
array([nan, 'none', 'contra', 'both', 'prop'], dtype=object)

In [5]:
df.Gender.unique()


Out[5]:
array(['m', 'f', nan, 'm '], dtype=object)

In [12]:
df['State of Registration'].unique()


Out[12]:
array(['i', 'o', nan], dtype=object)

In [13]:
df['State of Residence'].unique()


Out[13]:
array(['i', 'o', nan], dtype=object)

In [14]:
df['County of Residence'].unique()


Out[14]:
array(['Harford County', 'Baltimore City', nan, 'Cecil County',
       "Prince George's County", 'Carroll County', 'Howard County',
       'Baltimore County', 'Calvert County', 'Washington County',
       'Allegany County', 'Charles County', 'Anne Arundel County',
       'Montgomery County', "Queen Anne's County", 'Garrett County',
       'Dorchester County', 'Talbot County', 'Wicomico County',
       'Worcester County', 'Caroline County', "Saint Mary's County",
       'Frederick County', 'Kent County', 'Somerset County', ' '], dtype=object)

In [15]:
df['Stop Reason'].unique()


Out[15]:
array(['22-206(a)', '13-411(f)', '21-201(a1)', ..., '22-402 (a)',
       '16-303(b)', '13-704(b)'], dtype=object)

In [16]:
df['Search Reason'].unique()


Out[16]:
array([nan, 'incarrest', 'cons', 'other', 'prob', 'k9', 'exigent'], dtype=object)

In [17]:
df['Search Conducted'].unique()


Out[17]:
array(['n', 'y', nan], dtype=object)

In [45]:
df[df['Agency'].str.contains('Montgomery')]['Agency'].unique()


Out[45]:
array(['Maryland National Capital Park Police Montgomery County',
       'Montgomery County Police Department',
       "Montgomery County Sheriff's Office"], dtype=object)

In [6]:
in_montgomery = df['Agency'] == 'Montgomery County Police Department'
missing_race = df['Race'].isnull()
did_search = df.Search.notnull()
valid_time_value = df.datetime.dt.time > datetime.time(0, 0)

In [67]:
len(df[in_montgomery].index)


Out[67]:
54647

In [38]:
len(df[in_montgomery & missing_race].index)


Out[38]:
13

In [39]:
df[in_montgomery].Race.value_counts()


Out[39]:
w    21795
b    16802
h     9455
a     3617
o     2944
u       21
Name: Race, dtype: int64

In [40]:
df[in_montgomery].Race.value_counts().sum()


Out[40]:
54634

In [41]:
df[in_montgomery].Search.count()


Out[41]:
3053

In [42]:
df[in_montgomery].Search.count()/df[in_montgomery].Race.value_counts().sum()


Out[42]:
0.055880953252553357

In [43]:
df[in_montgomery].Search.value_counts()


Out[43]:
both    2141
prop     540
pers     372
Name: Search, dtype: int64

In [44]:
df[in_montgomery & did_search].Race.value_counts()


Out[44]:
b    1204
w    1042
h     651
a      86
o      70
Name: Race, dtype: int64

In [51]:
df[in_montgomery & did_search].Race.value_counts().sum()


Out[51]:
3053

In [52]:
p = df[in_montgomery & valid_time_value]['datetime'].groupby(df.datetime.dt.hour).count().plot(kind='bar',
                                                                                               title="Montgomery Hour of Day Stops")
p.set_xlabel("Hour of the Day")
p.set_ylabel("Stops")


Out[52]:
<matplotlib.text.Text at 0x7fc62a307e48>

In [53]:
p = df[in_montgomery & valid_time_value].groupby(pd.Grouper(key='datetime', freq='1M')).count()['Location'].plot(kind='bar',
                                                                                                                 title="Montgomery Stops By Month")
p.set_xlabel("Month")
p.set_ylabel("Stops")


Out[53]:
<matplotlib.text.Text at 0x7fc629b6b2e8>

In [65]:
p = df[in_montgomery & valid_time_value].groupby([pd.Grouper(key='datetime', freq='1M'), 'Race'])['Race'].count()

In [ ]: