pandas df.to_json

07-19-2019 11:02 AM
MVP Frequent Contributor

I am converting a Feature class to a numpy array, to a pandas dataframe in order to peform some grouping and basic statistics (to determine percent of totals on Shape_Area).  This is all working as expected and generating the desired result.  

I need to publish this out as a GP service and for some reason it fails (no errors) to convert the grouped dataframe to_json(). It seems to "print" correctly in the python IDE,

[{"Landuse":"Dikes and Levees","pctOfTotal":0.84,"acreTotal":17.88},{"Landuse":"Fallow Cropland","pctOfTotal":98.5,"acreTotal":2094.91},{"Landuse":"Mixed Wetland Hardwoods","pctOfTotal":0.66,"acreTotal":14.01}]

but when output to arcpy.AddMessage it seems to print the dataframe instead of the JSON,

"                   Landuse  pctOfTotal  acreTotal\n0         Dikes and Levees        0.84      17.88\n1          Fallow Cropland       98.50    2094.91\n2  Mixed Wetland Hardwoods        0.66      14.01"

Full implementation:

narr = arcpy.da.FeatureClassToNumPyArray(clip_fc, flds)
df = pd.DataFrame(narr, columns=['Landuse','Shape_Area'])
grouped = df.groupby(['Landuse'])[['Shape_Area']].sum().reset_index()
grouped['pctOfTotal'] = (grouped['Shape_Area'] / grouped['Shape_Area'].sum()) * 100
grouped['acreTotal'] = grouped['Shape_Area'] / 43560
outJson = grouped.to_json(orient='records', default_handler = str)

print outJson #correctly formats as desired JSON
arcpy.AddMessage(outJson) #incorrectly displays it as a dataframe, not JSON
arcpy.SetParameter(1, outJson) #incorrectly outputs it as a dataframe, not JSON
0 Kudos
2 Replies
Occasional Contributor III

It looks like outJson  is a list - maybe this is being misinterpreted by addMessage which expects a string? What happens if you create a string from outJson? Or I guess you see what happens if you just pass in the JSON string item in the list that the method creates and see what that does (i.e. outJson[0])

MVP Frequent Contributor

String would just output the df structure as a string.

I ended up converting to csv and then csv-to-json.  

0 Kudos