In [1]:
import urllib, urllib2
param = {'where':'1=1','outFields':'*','f':'json'}
url = 'http://coagisweb.cabq.gov/arcgis/rest/services/public/APD_Incidents/MapServer/0/query?' + urllib.urlencode(param)
rawreply = urllib2.urlopen(url).read()
In [2]:
rawreply[0]
Out[2]:
'{'
In [3]:
type(rawreply)
Out[3]:
str
In [4]:
import simplejson
reply = simplejson.loads(rawreply)
print reply["features"][0]["attributes"]["date"]
print reply["features"][0]["attributes"]["CVINC_TYPE"]
1405468800000
LARCENY ALL OTHER
In [5]:
print len(reply["features"])
#reply["features"][:3]
reply["features"][2:5]
24440
Out[5]:
[{u'attributes': {u'CVINC_TYPE': u'THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE',
   u'CV_BLOCK_ADD': u'4300 BLOCK ZUNI RD SE',
   u'OBJECTID': 1396553,
   u'date': 1405555200000L},
  u'geometry': {u'x': -11866271.569699999, u'y': 4173822.9486000016}},
 {u'attributes': {u'CVINC_TYPE': u'BURGLARY/BREAKING AND ENTERING',
   u'CV_BLOCK_ADD': u'1200 BLOCK DICKERSON DR SE',
   u'OBJECTID': 1396554,
   u'date': 1405468800000L},
  u'geometry': {u'x': -11868874.981800001, u'y': 4173046.724200003}},
 {u'attributes': {u'CVINC_TYPE': u'THEFT FROM A BUILDING',
   u'CV_BLOCK_ADD': u'1300 BLOCK VALLEY VIEW DR SW',
   u'OBJECTID': 1396555,
   u'date': 1405555200000L},
  u'geometry': {u'x': -11882344.039, u'y': 4170566.8716999963}}]
In [6]:
count = 0
myformateddata=[]
while (count < len(reply["features"])):
    mydict={}
    for key, value in reply["features"][count]["attributes"].iteritems():
        mydict[key]= value
    myformateddata.append(mydict)
    count = count + 1
In [7]:
from pandas import DataFrame as df
myFrame = df(data=myformateddata)
len(myFrame)
Out[7]:
24440
In [8]:
myFrame
Out[8]:
CVINC_TYPE CV_BLOCK_ADD OBJECTID date
0 LARCENY ALL OTHER 7800 BLOCK CENTRAL AVE E 1396551 1.405469e+12
1 SHOPLIFTING 7800 BLOCK CENTRAL AV E 1396552 1.405555e+12
2 THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE 4300 BLOCK ZUNI RD SE 1396553 1.405555e+12
3 BURGLARY/BREAKING AND ENTERING 1200 BLOCK DICKERSON DR SE 1396554 1.405469e+12
4 THEFT FROM A BUILDING 1300 BLOCK VALLEY VIEW DR SW 1396555 1.405555e+12
5 AUTO BURGLARY 1500 BLOCK GIBSON BLVD SE 1396556 1.405469e+12
6 BURGLARY/BREAKING AND ENTERING 2000 BLOCK SOUTHERN AVE SE 1396557 1.405469e+12
7 VAGRANCY, BEGGING OR LOITERING 2100 BLOCK CENTRAL AV E 1396558 1.405555e+12
8 COMMERCIAL BURGLARY 1400 BLOCK BROADWAY BL SE 1396559 1.405555e+12
9 DRIVING WHILE INTOXICATED 7600 BLOCK MONTGOMERY BL NE 1396560 1.405642e+12
10 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 7000 BLOCK NATALIE JANAE LN NE 1396561 1.405642e+12
11 AUTO BURGLARY 4800 BLOCK SPRING VALE RD NW 1396562 1.405469e+12
12 THEFT FROM A BUILDING 9500 BLOCK CANDELARIA RD NE 1396563 1.405469e+12
13 THEFT FROM A MOTOR VEHICLE UNIT BLOCK HOTEL CI NE 1396564 1.405555e+12
14 SIMPLE ASSAULT/BATTERY 13100 BLOCK CENTRAL AV E 1396565 1.405555e+12
15 SHOPLIFTING 3500 BLOCK COORS BLVD SW 1396566 1.405642e+12
16 AGGRAVATED ASSAULT 3300 BLOCK RIO PLATA DR SW 1396567 1.405555e+12
17 THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE 3900 BLOCK 11TH ST NW 1396568 1.405555e+12
18 DRIVING WHILE INTOXICATED 800 BLOCK RIO GRANDE BLVD NW 1396569 1.405555e+12
19 DISORDERLY CONDUCT 200 BLOCK LOMAS BLVD NW 1396570 1.405469e+12
20 AUTO BURGLARY 1300 BLOCK OJO SARCO ST SW 1396571 1.405642e+12
21 SHOPLIFTING 1400 BLOCK RENAISSANCE BL NE 1396572 1.405555e+12
22 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 100 BLOCK UNION SQUARE ST SE 1396573 1.405642e+12
23 BURGLARY/BREAKING AND ENTERING 1000 BLOCK MOUNTAIN RD NW 1396574 1.405555e+12
24 DISORDERLY CONDUCT 3800 BLOCK CENTRAL AV E 1396575 1.405555e+12
25 AUTO BURGLARY 900 BLOCK LOUISIANA BL NE 1396576 1.405555e+12
26 AUTO BURGLARY 2000 BLOCK MENAUL BLVD NE 1396577 1.405642e+12
27 AUTO THEFT 500 BLOCK ORTIZ DR SE 1396578 1.405469e+12
28 THEFT FROM A BUILDING 9100 BLOCK COPPER AV NE 1396579 1.405469e+12
29 AUTO THEFT 3900 BLOCK MONTGOMERY BLVD NE 1396580 1.405469e+12
... ... ... ... ...
24410 CREDIT CARD/ATM FRAUD 6100 BLOCK FLOR DE RIO NW 1420961 1.420502e+12
24411 AUTO THEFT 5300 BLOCK MONTGOMERY BLVD NE 1420962 1.420502e+12
24412 THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE 1200 BLOCK SILVER AVE SW 1420963 1.420502e+12
24413 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 3000 BLOCK QUAIL POINTE DR NW 1420964 1.420502e+12
24414 FRAUD 2800 BLOCK MADISON ST NE 1420965 1.420502e+12
24415 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 1200 BLOCK SILVER AVE SW 1420966 1.420502e+12
24416 AUTO BURGLARY 6000 BLOCK TOPKE PL NE 1420967 1.420502e+12
24417 AUTO THEFT 500 BLOCK 4TH ST NW 1420968 1.420502e+12
24418 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 4200 BLOCK COE DR NE 1420969 1.420502e+12
24419 AUTO THEFT 3000 BLOCK EUBANK BLVD NE 1420970 1.420502e+12
24420 WEAPONS VIOLATION 100 BLOCK DEPUTY DEAN MIERA DR SW 1420971 1.418774e+12
24421 EMBEZZLEMENT, MISAPPROPRIATION 1600 BLOCK VALLEY RD SW 1420972 1.414454e+12
24422 AUTO THEFT 2000 BLOCK SHIPMAN RD SW 1420973 1.416787e+12
24423 LARCENY ALL OTHER 9900 BLOCK CLEARWATER ST NW 1420974 1.417565e+12
24424 DISORDERLY CONDUCT 4500 BLOCK BROADWAY BLVD SE 1420975 1.417565e+12
24425 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 200 BLOCK SUNNYSLOPE ST SW 1420976 1.417565e+12
24426 COMMERCIAL BURGLARY 8800 BLOCK 2ND ST NW 1420977 1.417565e+12
24427 DRUG/NARCOTICS VIOLATIONS 200 BLOCK WILLOW RD NW 1420978 1.418429e+12
24428 LARCENY ALL OTHER 2200 BLOCK CAMPANA CT SW 1420979 1.418342e+12
24429 SHOPLIFTING 5600 BLOCK EDITH BLVD NE 1420980 1.418342e+12
24430 DRUG/NARCOTICS VIOLATIONS 1000 BLOCK LA VEGA DR SW 1420981 1.417997e+12
24431 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 6000 BLOCK ISLETA BLVD SW 1420982 1.417997e+12
24432 POSSESSION OF DRUG PARAPHERNALIA N/A 1420983 1.417133e+12
24433 POSSESSION OF DRUG PARAPHERNALIA 2600 BLOCK COORS BLVD NW 1420984 1.418602e+12
24434 SIMPLE ASSAULT/BATTERY 500 BLOCK MUSCATEL AVE NE 1420985 1.417910e+12
24435 EMBEZZLEMENT, MISAPPROPRIATION 600 BLOCK VINEYARD RD NE 1420986 1.419034e+12
24436 AUTO THEFT 3200 BLOCK ISLETA BLVD SW 1420987 1.418947e+12
24437 AUTO THEFT 300 BLOCK DEL AKER RD NW 1420988 1.419034e+12
24438 AUTO THEFT 300 BLOCK EL PUEBLO RD NW 1420989 1.418774e+12
24439 LARCENY ALL OTHER 6000 BLOCK BECK RD SW 1420990 1.410912e+12

24440 rows × 4 columns

In [9]:
import os
#os.getcwd()
In [10]:
from pandas import ExcelWriter
writer = ExcelWriter('CrimeIncidents.xlsx')
myFrame.to_excel(writer,'Sheet1',index=False)
writer.save()
In [30]:
myFrame["CVINC_TYPE"].value_counts()
Out[30]:
AUTO BURGLARY                                 3879
VANDALISM, MALICIOUS MISCHIEF, GRAFFITI       2601
SHOPLIFTING                                   2557
BURGLARY/BREAKING AND ENTERING                2539
AUTO THEFT                                    1985
LARCENY ALL OTHER                             1920
DRIVING WHILE INTOXICATED                     1463
FRAUD                                         1162
THEFT FROM A BUILDING                          794
THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE     745
DRUG/NARCOTICS VIOLATIONS                      691
COMMERCIAL BURGLARY                            627
AGGRAVATED ASSAULT                             565
EMBEZZLEMENT, MISAPPROPRIATION                 323
CREDIT CARD/ATM FRAUD                          314
DISORDERLY CONDUCT                             312
ROBBERY                                        250
COUNTERFEITING/FORGERY                         241
DISTURBANCE                                    233
SIMPLE ASSAULT/BATTERY                         232
THEFT FROM A MOTOR VEHICLE                     199
INTIMINDATION                                  154
LIQ LAWS-SELL, TRANS, POSS, MANUF, FURNISH     123
POSSESSION OF DRUG PARAPHERNALIA               118
WEAPONS VIOLATION                               86
PROSTITUTION                                    75
VAGRANCY, BEGGING OR LOITERING                  58
SHOOTING                                        49
THEFT FROM COIN OPERATED MACHINE OR DEVICE      38
ARSON                                           27
MURDER AND NONNEGLIGENT MANSLAUGHTER            20
WORTHLESS CHECKS                                11
DRUNKENNESS                                     10
PURSE SNATCHING                                 10
EXTORTION/BLACKMAIL                              8
PICK POCKETING                                   6
WIRE FRAUD                                       6
LOUD PARTY                                       5
IMPERSONATION                                    4
dtype: int64
In [11]:
import matplotlib.pyplot as plt
incidentType=myFrame["CVINC_TYPE"].value_counts()[:5]
incidentType.plot(kind='barh',rot=0)
Out[11]:
<matplotlib.axes._subplots.AxesSubplot at 0xe04a630>
In [14]:
#print myFrame[(myFrame.date>1420930000000)]
import datetime
import time
myDate=datetime.datetime(2015,1,10)
#myDate.isoformat()
milli=time.mktime(myDate.timetuple()) * 1000+ myDate.microsecond / 1000
print len(myFrame[(myFrame.date>milli)])
print milli
20
1.4208732e+12
In [15]:
myFrame[(myFrame.date>milli)]
Out[15]:
CVINC_TYPE CV_BLOCK_ADD OBJECTID date
17579 DRIVING WHILE INTOXICATED 9000 BLOCK CENTRAL SW 1414130 1.420934e+12
17585 AUTO BURGLARY 4400 BLOCK THE 25 WAY NE 1414136 1.420934e+12
17605 EMBEZZLEMENT, MISAPPROPRIATION 4300 BLOCK NANDINA CT NW 1414156 1.420934e+12
17620 DISTURBANCE 900 BLOCK SAN PEDRO DR NE 1414171 1.420934e+12
17621 AUTO THEFT 3300 BLOCK WELLESLEY CT NE 1414172 1.420934e+12
17624 AUTO BURGLARY 3300 BLOCK WELLESLEY NE 1414175 1.420934e+12
17636 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 1200 BLOCK CHELWOOD PARK BL NE 1414187 1.420934e+12
17637 AUTO BURGLARY 1600 BLOCK PENNSYLVANIA ST NE 1414188 1.420934e+12
17643 AUTO THEFT 5800 BLOCK HARPER DR NE 1414194 1.420934e+12
17747 DISTURBANCE 3100 BLOCK CENTRAL AVE E 1414298 1.420934e+12
17748 SHOPLIFTING 2200 BLOCK Q ST NE 1414299 1.420934e+12
17749 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 8500 BLOCK VISTA CLARA LN SW 1414300 1.420934e+12
17750 DISORDERLY CONDUCT 900 BLOCK LOUISIANA BL NE 1414301 1.420934e+12
17759 THEFT OF MOTOR VEHICLE PARTS OR ACCESSORIE 600 BLOCK CHAMA ST SE 1414310 1.420934e+12
17760 COMMERCIAL BURGLARY 3300 BLOCK COLUMBIA DR. NE 1414311 1.420934e+12
17761 DISORDERLY CONDUCT 1000 BLOCK LOUISIANA BLVD SE 1414312 1.420934e+12
17765 SHOPLIFTING 6600 BLOCK MENAUL BL NE 1414316 1.420934e+12
17766 DRUG/NARCOTICS VIOLATIONS 6600 BLOCK MENAUL BL NE 1414317 1.420934e+12
17778 VANDALISM, MALICIOUS MISCHIEF, GRAFFITI 1800 BLOCK ZENA LONA CT NE 1414329 1.420934e+12
17779 AGGRAVATED ASSAULT 1100 BLOCK CENTRAL SE 1414330 1.420934e+12