Read and write python file using X and Y fields in the data.

[jaykapalczynski]

I have a json string that is being read from a word press API URL.... it looks like the attached.

I need to get this to a feature class.  In addition to all the fields, it only has an X and Y field which I have to convert to point geometry.  Any thoughts on how I can achieve this via Python?

So I need to read the URL for the json string then create a FC from it.

Python to Table and then Table to FC?  Any help would be appreciated.

[jaykapalczynski]

@Anonymous User thanks again.,,,Last question as I think this is now working great...  You mentioned I can modify the field type and lengths etc.  

Is that done in the def as seen below for each Field?  What would the syntax be to set a text field with length vs Int vs double etc

def extract_p_tag(invalue):
    result = p_tag.search(invalue)
    newVal = result.group(1)
    print 'Fixing p Tag ' + result + ' to ' + newVal
    return newVal

[Anonymous User]

This has been a journey, glad its working.  Depending on what you want to do with the field, you can use either alterField (for just renaming, or increasing length<maybe- sometimes it doesn't work>), or addField (type conversion, field length decrease, nullables, etc.).

I'd suggest putting all the field parameters into a dictionary so you can iterate over it to add the fields.  For the example, I converted the long/lat and added a new field with short. You don't need all of the field parameters, I just included them for demo.

Move the flds list comprehension to after the new fields are added so they are included for the cursor.

# old field is used for the key for the new field details.
fld_dict = {"site_longitude": {'field_name': 'longitude', 'field_type': "LONG"},
            "site_latitude": {'field_name': 'latitude', 'field_type': "DOUBLE"},
            "new_field": {'field_name': 'new_field', 'field_type': 'SHORT', 'field_precision': None, 'field_scale': None, 'field_length': None,
                          'field_alias': 'new alias', 'field_is_nullable': True, 'field_is_required': False, 'field_domain': None}}

for fld, val in fld_dict.items():
    # .get returns the value in the dictionary or None if it doesn't exist.
    arcpy.AddMessage(f'Adding field: {val.get("field_name")}')
    arcpy.AddField_management(output_fc, val.get('field_name'),  val.get('field_type'), val.get('field_precision'),  val.get('field_scale'),  val.get('field_length'),
                           val.get('field_alias'),  val.get('field_is_nullable'),  val.get('field_is_required'),  val.get('field_domain'))

flds = [f.name for f in arcpy.ListFields(output_fc)]

with arcpy.da.UpdateCursor(output_fc, flds) as uCur:
    int_example = 0
    for row in uCur:
        # new field- migrate the previous info into the new field
        row[flds.index('longitude')] = float(extract_p_tag(row[flds.index('site_longitude')]))
        row[flds.index('latitude')] = float(extract_p_tag(row[flds.index('site_latitude')]))
        row[flds.index('new_field')] = int_example

        row[flds.index('site_description')] = extract_p_tag(row[flds.index('site_description')])
        row[flds.index('site_website')] = fix_website(row[flds.index('site_website')])
        row[flds.index('site_ebird_url')] = fix_website(row[flds.index('site_ebird_url')])
        row[flds.index('site_facilities')] = fix_facilities(row[flds.index('site_facilities')])
        row[flds.index('seabirds')] = fix_na(row[flds.index('seabirds')])

        uCur.updateRow(row)
        int_example =+ 1

for fld, val in fld_dict.items():
    # use the fld dict again to delete the 'old' fields no longer needed, but save the new fields by putting the name in the list:
    if fld not in ['new_field']:
        arcpy.DeleteField_management(output_fc, fld)

[jaykapalczynski]

@Anonymous User man this is it... THANK YOU very much for your help...I learned a ton here and will pay it forward.....I didnt know which one to mark as the ANSWERED so I picked to one that directly addressed my initial question.

Here is my Final Script if anyone has any questions or comments please feel free to ask.

import arcpy
import json
import uuid
import os
import re

arcpy.env.workspace = 'C:/Users/t/Desktop/JSONSamples/'
dataInput = r"C:/Users/t/Desktop/JSONSamples/JSON_New3.json"
dataOutput = r"C:/Users/t/Desktop/JSONSamples/JSON_New4.json"

f = open(dataInput)
data = json.load(f)

# Added this So I could use it in the For loop
data1 = {"BirdTrailLocations":  data }

print data1
print ""

converted_list = []

for i in data1['BirdTrailLocations']:
    feat_dict = {} 
    feat_dict["geometry"] = {"x": i["site_longitude"], "y": i["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    i["site_facilities"] = str(i["site_facilities"])
    feat_dict["attributes"] = i
    converted_list.append(feat_dict)

print converted_list
print ""

# Closing file
f.close()

new_dict = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
            'features': converted_list}

print(new_dict)

with open(dataOutput, 'w') as f:
    json.dump(new_dict, f)

output_fc = r'C:\\Users\\t\\Desktop\\JSONSamples\\outgdb.gdb\test'
arcpy.env.workspace = r"C:\\Users\\t\\Desktop\\JSONSamples\\outgdb.gdb"
cws = arcpy.env.workspace

fc_Delete = ["test"]

for fc in fc_Delete:
  fc_path = os.path.join(cws, fc)
  if arcpy.Exists(fc_path):
    arcpy.Delete_management(fc_path)

arcpy.JSONToFeatures_conversion(dataOutput, output_fc)

# old field is used for the key for the new field details.
fld_dict = {"site_longitude": {'field_name': 'longitude', 'field_type': "LONG"},
            "site_latitude": {'field_name': 'latitude', 'field_type': "DOUBLE"},
            "new_field": {'field_name': 'new_field', 'field_type': 'SHORT', 'field_precision': None, 'field_scale': None, 'field_length': None,
                          'field_alias': 'new alias', 'field_is_nullable': True, 'field_is_required': False, 'field_domain': None},
            "new_fieldText": {'field_name': 'new_fieldText', 'field_type': 'TEXT', 'field_precision': None, 'field_scale': None, 'field_length': 50,
                          'field_alias': 'new Text', 'field_is_nullable': True, 'field_is_required': False, 'field_domain': None}}

for fld, val in fld_dict.items():
    value = str({val.get("field_name")})
    print "adding Field: " + value
    arcpy.AddField_management(output_fc, val.get('field_name'),  val.get('field_type'), val.get('field_precision'),  val.get('field_scale'),  val.get('field_length'),
                           val.get('field_alias'),  val.get('field_is_nullable'),  val.get('field_is_required'),  val.get('field_domain'))


#####  Clean up the fields.
flds = [f.name for f in arcpy.ListFields(output_fc)]
p_tag = re.compile(r'.*?>(.*)<')

print ""
print "--- Fixing Data ---"
print ""

def extract_p_tag_descriptions(invalue):
    result = p_tag.search(invalue)
    if result:
        newVal = result.group(1)
    else:
        newVal = invalue

    print 'Fixing p Tag ' + newVal + ' to ' + newVal
    print ""
    return newVal

def extract_p_tag_directions(invalue):
    result = p_tag.search(invalue)
    if result:
        newVal = result.group(1)
    else:
        newVal = invalue

    print 'Fixing p Tag Directions' + newVal + ' to ' + newVal
    print ""
    return newVal

def fix_website(invalue):
    newVal = re.sub(r'/', r'\\', invalue)
    newVal = newVal.replace('\\\\', '\\')
    #arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    print 'Fixing website ' + invalue + ' to ' + newVal
    print ""
    return str(newVal)

def fix_facilities(invalue):
    newVal = invalue.replace("u'", "'").replace('[', '').replace(']', '').replace(r"'", r'')
    #arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    print 'Fixing facilities ' + invalue + ' to ' + newVal
    print ""
    return newVal

def fix_na(invalue):
    newVal = invalue.replace('\/', '\\')
    #arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    print 'Fixing NA' + invalue + ' to ' + newVal
    print ""
    return str(newVal)

with arcpy.da.UpdateCursor(output_fc, flds) as uCur:
    int_example = 0
    text_example = "new text"
    for row in uCur:
        # new field- migrate the previous info into the new field
        row[flds.index('longitude')] = float(extract_p_tag_descriptions(row[flds.index('site_longitude')]))
        row[flds.index('latitude')] = float(extract_p_tag_descriptions(row[flds.index('site_latitude')]))
        row[flds.index('new_field')] = int_example
        row[flds.index('new_fieldText')] = text_example
        
        row[flds.index('site_description')] = extract_p_tag_descriptions(row[flds.index('site_description')])
        row[flds.index('site_website')] = fix_website(row[flds.index('site_website')])
        row[flds.index('site_ebird_url')] = fix_website(row[flds.index('site_ebird_url')])
        row[flds.index('site_facilities')] = fix_facilities(row[flds.index('site_facilities')])
        row[flds.index('seabirds')] = fix_na(row[flds.index('seabirds')])

        uCur.updateRow(row)
        int_example =+ 1

for fld, val in fld_dict.items():
    # use the fld dict again to delete the 'old' fields no longer needed, but save the new fields by putting the name in the list:
    if fld not in ['new_field', 'new_fieldText']:
        arcpy.DeleteField_management(output_fc, fld)