If Else Condition to write JSON values to NumPy array to feature

GeoffreyWest · ‎02-19-2015

have Python script that parses a JSON web-service and writes to a geodatabase. I would like to be able to create an if statement for fields that are in the web-service so if they contain values, then they are written to my table, if not, no value is added, but the field is still there, i.e. if there is no bulky item a bulkyitem field is added with no value or bulkyitemqty no value is added, and if values are there the fields are written and values are added. My script works now because I know which fields and values are available in the test web-service. Now, I am writing the first electronic waste object to my database. Ideally, I would like to iterate through most of the fields shown in the JSON response(not all) and write those to my table as well.

Script:

import json
import jsonpickle
import requests
import arcpy
import numpy

fc = "C:\MYLATesting.gdb\MYLA311"
if arcpy.Exists(fc):
  arcpy.Delete_management(fc)

f = open('C:\Users\Administrator\Desktop\myla311.json', 'r')

data = jsonpickle.encode( jsonpickle.decode(f.read()) )

url = "myURL"
headers = {'Content-type': 'text/plain', 'Accept': '/'}

r = requests.post(url, data=data, headers=headers)
sr = arcpy.SpatialReference(4326)

decoded = json.loads(r.text)

SRAddress = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['SRAddress']
latitude = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['Latitude']
longitude = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['Longitude']

CommodityType = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][0]['Type']
ItemType = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][0]['ElectronicWestType']
ItemCount_1 = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][0]['ItemCount']


CommodityType1 = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][1]['Type']
ItemType1 = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][1]['ElectronicWestType']
ItemCount1 = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['ListOfLa311ElectronicWaste']['La311ElectronicWaste'][1]['ItemCount']






print SRAddress
print latitude
print longitude

print CommodityType
print ItemType
print ItemCount_1






print CommodityType1
print ItemType1
print ItemCount1


#Items and Keys should maintain same order
item ={'SRAddress': SRAddress,  'CommodityType': CommodityType, 'ItemType': ItemType, 'ItemCount_1': ItemCount_1, 'longitude': longitude, 'latitude': latitude}
import numpy as np  #NOTE THIS
keys = ['SRAddress','CommodityType', 'ItemType','ItemCount_1','longitude','latitude']
k1,k2,k3, k4, k5, k6 = keys
# data_line ={'SRAddress': SRAddress, 'Longitude': longitude, 'Latitude': latitude, 'CommodityType': CommodityType, 'ItemCount': ItemCount}
frmt = '\nStraight dictionary output\n Address: {} Long: {} Lat: {}'
print(frmt.format(item[k1],item[k2],item[k3], item[k4],item[k5], item[k6]))
print '\noption 1: List comprehension with unicode'
a =  tuple([unicode(item[key]) for key in keys])  # list comprehension with unicode
print('{}'.format(a))
dt = np.dtype([('SRAddress','U40'),('CommodityType','U40'), ('ItemType','U40'), ('ItemCount_1','U40'),('longitude','<f8'),('latitude','<f8')])
arr = np.array(a,dtype=dt)

# print'\narray unicode\n',arr
# print'dtype',arr.dtype
# print '\noption 2:List comprehension without unicode'
# b = tuple([item[key] for key in keys])
# print('{}'.format(b))
# dt = np.dtype([('SRAddress','U40'),('CommodityType','S4'), ('ItemCount','U40'),('longitude','<f8'),('latitude','<f8')])
# arr = np.array(b,dtype=dt)
# print'\narray without unicode\n',arr
# print'dtype',arr.dtype

arcpy.da.NumPyArrayToFeatureClass(arr, fc, ['longitude', 'latitude'], sr)

Output:

'5810 N WILLIS AVE, 91411
34.176277
-118.455249
Electronic Waste
Microwaves
3
Electronic Waste
Televisions (Any Size)
6

Straight dictionary output
 Address: 5810 N WILLIS AVE, 91411 Long: Electronic Waste Lat: Microwaves

option 1: List comprehension with unicode
(u'5810 N WILLIS AVE, 91411', u'Electronic Waste', u'Microwaves', u'3', u'-118.455249', u'34.176277')`

Output JSON:

{ "status": { "code": 311, "message": "Service Request Successfully Queried.", "cause": "" }, "Response": { "NumOutputObjects": "1", "ListOfServiceRequest": { "ServiceRequest": [ { "AddressVerified": "Y", "SRNumber": "1-3580171", "SRType": "Electronic Waste", "CreatedDate": "02/17/2015 16:53:25", "UpdatedDate": "02/17/2015 16:53:25", "IntegrationId": "02172015165417667", "Status": "Open", "CreatedByUserLogin": "MYLATHREEONEONE", "UpdatedByUserLogin": "MYLATHREEONEONE", "Anonymous": "N", "Zipcode": "91411", "Latitude": "34.176277", "Longitude": "-118.455249", "CustomerAccessNumber": "", "LADWPAccountNo": "", "NewContactFirstName": "", "NewContactLastName": "", "NewContactPhone": "", "NewContactEmail": "", "ParentSRNumber": "", "Priority": "Normal", "Language": "", "ReasonCode": "", "ServiceDate": "02/19/2015 00:00:00", "Source": "", "Email": "mylathreeoneone@gmail.com", "FirstName": "Myla", "HomePhone": "2131234567", "LastName": "Threeoneone", "LoginUser": "", "ResolutionCode": "", "SRUnitNumber": "5810", "MobilOS": "", "SRAddress": "5810 N WILLIS AVE, 91411", "SRAddressName": "", "SRAreaPlanningCommission": "South Valley APC", "SRCommunityPoliceStation": "VALLEY BUREAU", "SRCouncilDistrictMember": "Tom LaBonge", "SRCouncilDistrictNo": "4", "SRDirection": "N", "SRNeighborhoodCouncilId": "20", "SRNeighborhoodCouncilName": "VAN NUYS NC", "SRStreetName": "WILLIS", "SRSuffix": "AVE", "SRTBColumn": "J", "SRTBMapGridPage": "561", "SRTBRow": "1", "SRXCoordinate": "6423983", "SRYCoordinate": "1886848", "AssignTo": "EV", "Assignee": "", "Owner": "BOS", "ParentSRStatus": "", "ParentSRType": "", "ParentSRLinkDate": "", "ParentSRLinkUser": "", "SRAreaPlanningCommissionId": "3", "SRCommunityPoliceStationAPREC": "VAN NUYS", "SRCommunityPoliceStationPREC": "9", "SRCrossStreet": "", "ActionTaken": "", "SRCity": "", "RescheduleCounter": "", "SRHouseNumber": "5810", "ListOfLa311BarricadeRemoval": {}, "ListOfLa311BulkyItem": {}, "ListOfLa311DeadAnimalRemoval": {}, "ListOfLa311GraffitiRemoval": {}, "ListOfLa311InformationOnly": {}, "ListOfLa311MultipleStreetlightIssue": {}, "ListOfLa311SingleStreetlightIssue": {}, "ListOfLa311SrPhotoId": { "La311SrPhotoId": [ { "Name": "021720151654176671", "PhotoId": "https://myla311.lacity.org/portal/docview?id=04b8ba678fe21d32b05673eb9ad7711b", "Type": "SR Photo ID", "LastUpdatedBy": "" } ] }, "ListOfLa311BusPadLanding": {}, "ListOfLa311CurbRepair": {}, "ListOfLa311Flooding": {}, "ListOfLa311GeneralStreetInspection": {}, "ListOfLa311GuardWarningRailMaintenance": {}, "ListOfLa311GutterRepair": {}, "ListOfLa311LandMudSlide": {}, "ListOfLa311Pothole": {}, "ListOfLa311Resurfacing": {}, "ListOfLa311SidewalkRepair": {}, "ListOfLa311StreetSweeping": {}, "ListOfLa311BeesOrBeehive": {}, "ListOfLa311MedianIslandMaintenance": {}, "ListOfLa311OvergrownVegetationPlants": {}, "ListOfLa311PalmFrondsDown": {}, "ListOfLa311StreetTreeInspection": {}, "ListOfLa311StreetTreeViolations": {}, "ListOfLa311TreeEmergency": {}, "ListOfLa311TreeObstruction": {}, "ListOfLa311TreePermits": {}, "ListOfLa311BrushItemsPickup": {}, "ListOfLa311Containers": {}, "ListOfLa311ElectronicWaste": { "La311ElectronicWaste": [ { "CollectionLocation": "Gated Community/Multifamily Dw", "DriverFirstName": "", "DriverLastName": "", "ElectronicWestType": "Microwaves", "GatedCommunityMultifamilyDwelling": "Curb", "IllegallyDumped": "N", "ItemCount": "3", "MobileHomeSpace": "", "OtherElectronicWestType": "", "ServiceDateRendered": "", "TruckNo": "", "Type": "Electronic Waste", "IllegalDumpCollectionLoc": "", "LastUpdatedBy": "", "Name": "021720151654176711" }, { "CollectionLocation": "Gated Community/Multifamily Dw", "DriverFirstName": "", "DriverLastName": "", "ElectronicWestType": "Televisions (Any Size)", "GatedCommunityMultifamilyDwelling": "Curb", "IllegallyDumped": "N", "ItemCount": "6", "MobileHomeSpace": "", "OtherElectronicWestType": "", "ServiceDateRendered": "", "TruckNo": "", "Type": "Electronic Waste", "IllegalDumpCollectionLoc": "", "LastUpdatedBy": "", "Name": "021720151654176722" }, { "CollectionLocation": "Gated Community/Multifamily Dw", "DriverFirstName": "", "DriverLastName": "", "ElectronicWestType": "VCR/DVD Players", "GatedCommunityMultifamilyDwelling": "Curb", "IllegallyDumped": "N", "ItemCount": "1", "MobileHomeSpace": "", "OtherElectronicWestType": "", "ServiceDateRendered": "", "TruckNo": "", "Type": "Electronic Waste", "IllegalDumpCollectionLoc": "", "LastUpdatedBy": "", "Name": "021720151654176723" } ] }, "ListOfLa311IllegalDumpingPickup": {}, "ListOfLa311ManualPickup": {}, "ListOfLa311MetalHouseholdAppliancesPickup": {}, "ListOfLa311MoveInMoveOut": {}, "ListOfLa311HomelessEncampment": {}, "ListOfLa311IllegalAutoRepair": {}, "ListOfLa311IllegalConstruction": {}, "ListOfLa311IllegalConstructionFence": {}, "ListOfLa311IllegalDischargeOfWater": {}, "ListOfLa311IllegalDumpingInProgress": {}, "ListOfLa311IllegalExcavation": {}, "ListOfLa311IllegalSignRemoval": {}, "ListOfLa311IllegalVending": {}, "ListOfLa311LeafBlowerViolation": {}, "ListOfLa311NewsRackViolation": {}, "ListOfLa311Obstructions": {}, "ListOfLa311TablesAndChairsObstructing": {}, "ListOfLa311GisLayer": { "La311GisLayer": [ { "A_Call_No": "", "Area": "0", "Day": "THURSDAY", "DirectionSuffix": "", "DistrictAbbr": "", "DistrictName": "EV", "DistrictNumber": "", "DistrictOffice": "", "Fraction": "", "R_Call_No": "", "SectionId": "", "ShortDay": "Thu", "StreetFrom": "", "StreetTo": "", "StreetLightId": "", "StreetLightStatus": "", "Type": "GIS", "Y_Call_No": "", "Name": "02172015165417667100", "CommunityPlanningArea": "", "LastUpdatedBy": "", "BOSRadioHolderName": "" } ] }, "ListOfLa311ServiceRequestNotes": { "La311ServiceRequestNotes": [ { "CreatedDate": "02/17/2015 16:53:26", "Comment": "Out on the sidewalk near the curb. Hopefully it is still there.", "CreatedByUser": "MYLATHREEONEONE", "IsSrNoAvailable": "", "CommentType": "Address Comments", "Notification": "N", "FeedbackSRType": "", "IntegrationId": "021720151654176661", "Date1": "", "Date2": "", "Date3": "", "Text1": "", "ListOfLa311SrNotesAuditTrail": {} }, { "CreatedDate": "02/17/2015 16:53:26", "Comment": "So glad to get rid of this old junk. Thanks.", "CreatedByUser": "MYLATHREEONEONE", "IsSrNoAvailable": "", "CommentType": "External", "Notification": "N", "FeedbackSRType": "", "IntegrationId": "021720151654176662", "Date1": "", "Date2": "", "Date3": "", "Text1": "", "ListOfLa311SrNotesAuditTrail": {} } ] }, "ListOfLa311SubscribeDuplicateSr": {}, "ListOfChildServiceRequest": {}, "ListOfLa311BillingCsscAdjustment": {}, "ListOfLa311BillingEccAdjustment": {}, "ListOfLa311BillingRsscAdjustment": {}, "ListOfLa311BillingRsscExemption": {}, "ListOfLa311SanitationBillingBif": {}, "ListOfLa311SanitationBillingCssc": {}, "ListOfLa311SanitationBillingEcc": {}, "ListOfLa311SanitationBillingInquiry": {}, "ListOfLa311SanitationBillingLifeline": {}, "ListOfLa311SanitationBillingRssc": {}, "ListOfLa311SanitationBillingSrf": {}, "ListOfLa311DocumentLog": {}, "ListOfAuditTrailItem2": {}, "ListOfLa311GenericBc": { "La311GenericBc": [ { "ATTRIB_08": "N", "NAME": "021720151654176711", "PAR_ROW_ID": "1-24QH7", "ROW_ID": "1-24QHR", "TYPE": "Electronic Waste", "ListOfLa311GenericbcAuditTrail": {} }, { "ATTRIB_08": "N", "NAME": "021720151654176722", "PAR_ROW_ID": "1-24QH7", "ROW_ID": "1-24QHS", "TYPE": "Electronic Waste", "ListOfLa311GenericbcAuditTrail": {} }, { "ATTRIB_08": "N", "NAME": "021720151654176723", "PAR_ROW_ID": "1-24QH7", "ROW_ID": "1-24QHT", "TYPE": "Electronic Waste", "ListOfLa311GenericbcAuditTrail": {} }, { "ATTRIB_08": "", "NAME": "02172015165417667100", "PAR_ROW_ID": "1-24QH7", "ROW_ID": "1-24QHU", "TYPE": "GIS", "ListOfLa311GenericbcAuditTrail": {} }, { "ATTRIB_08": "", "NAME": "021720151654176671", "PAR_ROW_ID": "1-24QH7", "ROW_ID": "1-24QHQ", "TYPE": "SR Photo ID", "ListOfLa311GenericbcAuditTrail": {} } ] }, "ListOfLa311ServiceNotComplete": {}, "ListOfLa311Other": {}, "ListOfLa311WeedAbatementForPrivateParcels": {} } ] } } }

JoshuaBixby · ‎02-26-2015

Can you re-post your current code with the loop now integrated into it?

GeoffreyWest · ‎02-26-2015

f2 = open('C:\Users\Administrator\Desktop\DetailView.json', 'r')


data2 = jsonpickle.encode( jsonpickle.decode(f2.read()) )


url2 = "https://myla311.lacity.org/myla311router/mylasrbe/1/QuerySR"
headers2 = {'Content-type': 'text/plain', 'Accept': '/'}


r2 = requests.post(url2, data=data2, headers=headers2)


decoded2 = json.loads(r2.text)


for sr in decoded2['Response']['ListOfServiceRequest']['ServiceRequest']:
    SRAddress = sr['SRAddress']
    latitude = sr['Latitude']
    longitude = sr['Longitude']


    for ew in sr["ListOfLa311ElectronicWaste"][u"La311ElectronicWaste"]:
        CommodityType = ew['Type']
        ItemType = ew['ElectronicWestType']
        ItemCount = ew['ItemCount']


 print ew['ItemCount']
  print ew['Type']

item ={'SRAddress':  sr['SRAddress'],  'latitude': sr['Latitude'], 'longitude': sr['Longitude'], 'Type': ew['Type'], 'ElectronicWestType': ew['ElectronicWestType'], 'ItemCount': ew['ItemCount']}
import numpy as np     #NOTE THIS
keys = [sr['SRAddress'], sr['Latitude'], sr['Longitude'],ew['Type'],ew['ElectronicWestType'], ew['ItemCount']]
k1,k2,k3, k4, k5, k6 = keys
# data_line ={'SRAddress': SRAddress, 'Longitude': longitude, 'Latitude': latitude, 'CommodityType': CommodityType, 'ItemCount': ItemCount}
frmt = '\nStraight dictionary output\n SRAddress: {} Long: {} Lat: {}  Type: {} WasteType: {} ItemCount {}'
print(frmt.format(item[k1], item[k2], item[k3], item[k4], item[k5], item[k6]))
print '\noption 1:  List comprehension with unicode'
a = tuple([unicode(item[key]) for key in keys])  # list comprehension with unicode
print('{}'.format(a))
dt = np.dtype([('SRAddress','U40'),('latitude','<f8'), ('longitude','<f8'), ('Type','U40'),('ElectronicWestType','U40'),('ItemCount','U40')])
arr = np.array(a,dtype=dt)



sr = arcpy.SpatialReference(4326)






arcpy.da.NumPyArrayToFeatureClass(arr, fc, ['longitude', 'latitude'], sr )

JoshuaBixby · ‎02-26-2015

Python dictionaries are implemented using a hash table. They are effectively an unordered collection of items, which means you shouldn't rely on the order of the elements. I mention this because it seems to me you may be trying to do just that. Instead of an item dictionary, I think an item list of tuples would work better and be more straightforward.

f2 = open('C:\Users\Administrator\Desktop\DetailView.json', 'r')
data2 = jsonpickle.encode( jsonpickle.decode(f2.read()) )

url2 = "https://myla311.lacity.org/myla311router/mylasrbe/1/QuerySR"
headers2 = {'Content-type': 'text/plain', 'Accept': '/'}

r2 = requests.post(url2, data=data2, headers=headers2)
decoded2 = json.loads(r2.text)

items = []
for sr in decoded2['Response']['ListOfServiceRequest']['ServiceRequest']:
    SRAddress = sr['SRAddress']
    latitude = sr['Latitude']
    longitude = sr['Longitude']

    for ew in sr["ListOfLa311ElectronicWaste"][u"La311ElectronicWaste"]:
        CommodityType = ew['Type']
        ItemType = ew['ElectronicWestType']
        ItemCount = ew['ItemCount']
        
        items.append((SRAddress,
                      latitude,
                      longitude,
                      CommodityType,
                      ItemType,
                      ItemCount))

import numpy as np    #NOTE THIS
dt = np.dtype([('SRAddress', 'U40'),
                ('latitude', '<f8'),
                ('longitude', '<f8'),
                ('Type', 'U40'),
                ('ElectronicWestType', 'U40'),
                ('ItemCount', 'U40')])
arr = np.array(items,dtype=dt)
sr = arcpy.SpatialReference(4326)
arcpy.da.NumPyArrayToFeatureClass(arr, fc, ['longitude', 'latitude'], sr )

GeoffreyWest · ‎02-26-2015

This works Joshua, I am SO appreciative for your assistance with this. This is a 1-M relationship between the SRAddress Types and Items. Is there a function or process that I can use to map out, ie Type 1, ElectronicWasteType1, ItemCount1, Type 2, EletronicWasteType2, ItemCount2? So I guess my question is how would I loop through only the types and counts and append those? I presume the initial loop can be removed and added programmatically since it is only referenced once.

SRAddress = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['SRAddress']

latitude = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['Latitude']

longitude = decoded['Response']['ListOfServiceRequest']['ServiceRequest'][0]['Longitude']

JoshuaBixby · ‎02-26-2015

Are you looking for only a single record/point per SRAddress? I recall you mentioning there may be up to 10 types or collections. Do you want a table that has [Type1, EWT1, ItemCount1, ...., Type10, EWT10, ItemCount10] columns? A table with at least 30 columns, most of which will be empty?

Or, are you wanting a separate table that has [SRAddress, Type, EWT, ItemCOunt] that you can use to relate back to a point feature class that has the point locations for each SRAddress?

I guess I don't understand what you want the final data structure to look like in terms of how many tables and what those tables look like.

GeoffreyWest · ‎02-26-2015

Either instance would be ideal.

However the point feature class should follow the latter, where the

SRAddress will have 1-10 for Type and Count, to clarify other collection

types are white goods, which are metals, so a white goods feature could

have SRAddress 123 Fake Street and the one commodity column for the feature

would be white goods, and types 1-10 and count 1-10 types for white goods

would be oven, refrigerator, etc. So when the program is executed it would

iterate over all commodity types as you have done with e-waste items and

for each SRAdrress there is one SRNumber but 10 item types and item

counts. So given the current structure of the data it would be SRAdress,

CommdityType, ItemType1, Count1, ItemType2, Count2.....10.

So for the example that is provided the table would read ItemType1:

Microwaves, ItemCount 1: 3, ItemType 2:Televisions (Any Size), ItemCount2:

6, etc.