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-25-2015

The following functional code snippet will loop through all the service requests and then loop through all of the electronic waste items for each service request. Is this sort of what you are after:

for sr in decoded['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']

View solution in original post

JoshuaBixby · ‎02-20-2015

It would be helpful if you could elaborate a bit more on the final data structure you are looking at creating. The sample JSON object is large and sparse, which would create a large and sparse table if you want to load it all into a single table.

I am unclear whether you are interested in all items in the list or certain subsets. You seem to be working with electronic waste, is that the subset you want to extract? When you say you want to "iterate through most of the fields shown in the JSON response(not all)," what is your criteria for not iterating over something?

GeoffreyWest · ‎02-25-2015

The final data structure that I am looking at creating is a feature class with the attributes and values which are stored in the JSON file. However, I do not necessairily want to bring in all of the data that is in the JSON. Please see the example below. It is not guaranteed that there will always be 3 requests for e-waste, but at least one. If there are 10 (maximum) I would like to write them to a feature class in a file geodatabase but if there are three then I would like to write them to the db. I am able execute this process now because I am aware of the data structure.

So ideally the script would loop over "La311ElectronicWaste" and write all of the designated values to a feature class.

"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" } ] },

JoshuaBixby · ‎02-25-2015

The following functional code snippet will loop through all the service requests and then loop through all of the electronic waste items for each service request. Is this sort of what you are after:

for sr in decoded['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']

GeoffreyWest · ‎02-25-2015

I think this is what I am aiming for. To extend on this application, I have another JSON web-service which actually queries service request numbers. I am using numpyarraytoTable to retrieve all Service Request numbers and joining that table to what is called a Detail view which contains the longitude, latitude, request type, location, etc. The output below shows the SRs that I would create in numpyarraytoTable then join to my numpyarraytoFeature by SRNumber. I will use the same method/logic to grab the SRNumbers from the output below as well.

 "Response": {
        "LastUpdateDate": "02/17/2015", 
        "ListOfServiceRequest": {
            "ServiceRequest": [
                {
                    "CreatedDate": "02/17/2015 16:53:25", 
                    "SRAddress": "5810 N WILLIS AVE, 91411", 
                    "SRNumber": "1-3580171", 
                    "SRType": "Electronic Waste", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 16:53:25", 
                    "imageURL": 
                }, 
                {
                    "CreatedDate": "02/17/2015 16:58:45", 
                    "SRAddress": "HOLLYWOOD BLVD AT VINE ST, 90028", 
                    "SRNumber": "1-3567421", 
                    "SRType": "Illegal Dumping Pickup", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 16:58:45", 
                    "imageURL":
                }, 
                {
                    "CreatedDate": "02/17/2015 16:46:36", 
                    "SRAddress": "14410 W SYLVAN ST, 91401", 
                    "SRNumber": "1-3557011", 
                    "SRType": "Dead Animal Removal", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 16:46:36", 
                    "imageURL": 
                }, 
                {
                    "CreatedDate": "02/17/2015 16:50:38", 
                    "SRAddress": "1204 S BURNSIDE AVE, 90019", 
                    "SRNumber": "1-3567371", 
                    "SRType": "Bulky Items", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 16:50:38", 
                    "imageURL": 
                }, 
                {
                    "CreatedDate": "02/17/2015 16:56:20", 
                    "SRAddress": "14526 W VANOWEN ST, 91405", 
                    "SRNumber": "1-3580221", 
                    "SRType": "Barricade Removal", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 16:56:20", 
                    "imageURL": 
                }, 
                {
                    "CreatedDate": "02/17/2015 17:01:06", 
                    "SRAddress": "SUNSET BLVD AT VERMONT AVE, 90027", 
                    "SRNumber": "1-3557071", 
                    "SRType": "Service Not Complete", 
                    "Status": "Open", 
                    "UpdatedDate": "02/17/2015 17:01:06", 
                    "imageURL":
                }
            ]
        }

GeoffreyWest · ‎02-25-2015

I think the problem with this is that I need to loop through the indices.

When using the snippet that you provided, I receive a key error. I am

hoping to loop to avoid having to identify indices with each object.

GeoffreyWest · ‎02-25-2015

Joshua I was able to implement this to some degree, however my keys are behaving strangely and when I print ItemCount for instance, it returns one instead of 3,6,1. Your help is much appreciated.

JoshuaBixby · ‎02-26-2015

ItemCount is holding the item count for a single type, not all of the item counts for all of the type of electronic waste. In the code I provided, the last loop over the electronic waste items grabs the VCR/DVD Players, which has an item count of 1. After the loops are finished, and since I didn't delete ItemCount, it stills exists and has the last value of 1 stored in it.

GeoffreyWest · ‎02-26-2015

The error that I am receiving now is File "C:/MYLAScripts/MYLAJSONtesting.py", line 193, in print(frmt.format(item[k1], item[k2], item[k3], item[k4], item[k5], item[k6])) KeyError: u'5810 N WILLIS AVE, 91411' What could cause this?

JoshuaBixby · ‎02-26-2015

You are passing the string, '5810 N WILLIS AVE, 91411', as a key name (either for k1, ..., or k6), and the error message is telling you there is not key named that address string.