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()
rawreply[0]
'{'
type(rawreply)
str
import simplejson
reply = simplejson.loads(rawreply)
print reply["features"][0]["attributes"]["date"]
print reply["features"][0]["attributes"]["CVINC_TYPE"]
1405468800000 LARCENY ALL OTHER
print len(reply["features"])
#reply["features"][:3]
reply["features"][2:5]
24440
[{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}}]
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
from pandas import DataFrame as df
myFrame = df(data=myformateddata)
len(myFrame)
24440
myFrame
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
import os
#os.getcwd()
from pandas import ExcelWriter
writer = ExcelWriter('CrimeIncidents.xlsx')
myFrame.to_excel(writer,'Sheet1',index=False)
writer.save()
myFrame["CVINC_TYPE"].value_counts()
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
import matplotlib.pyplot as plt
incidentType=myFrame["CVINC_TYPE"].value_counts()[:5]
incidentType.plot(kind='barh',rot=0)
<matplotlib.axes._subplots.AxesSubplot at 0xe04a630>
#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
myFrame[(myFrame.date>milli)]
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 |