I have an API and I am trying to use the data in ArcGIS Online. Does anyone know of a python script that I can use to convert the response into a feature layer? Or some other way to do it?
@LindaSlattery here is the updated code that will first perform a truncate of the hosted table, and then update the table with all records.
import requests, json, arcpy from arcgis import GIS # Variables username = "jskinner_CountySandbox" # AGOL Username password = "********" # AGOL Password itemID = 'f214df9b8f5440149a20ccd5d452a95e' # AGOL Table Item ID dataURL = 'https://eagle-i.doe.gov/api/outagesummary/countymax24hoursummary?state=OH&eiApiKey=' # Disable warnings requests.packages.urllib3.disable_warnings() # Connect to AGOL print("Connecting to AGOL") gis = GIS('https://www.arcgis.com', username, password) # Get Table print("Get Hosted Table") fLayer = gis.content.get(itemID) editTable = fLayer.tables[0] # Truncate Table print("Truncate table") editTable.manager.truncate() # Get Data print("Retrieving Data") r = requests.get(dataURL, verify=False) response = json.loads(r.content) data = response['data'] # Create Dictionary of attributes and update table print("Updating hosted table") for attr in data: addFeatures = { "attributes" : { "currentOutage" : attr['currentOutage'], "currentOutageRunStartTime" : attr['currentOutageRunStartTime'], "maxOutage1" : attr['maxOutage1'], "maxOutage1RunStartTime": attr['maxOutage1RunStartTime'], "maxOutage24": attr['maxOutage24'], "maxOutage24RunStartTime": attr['maxOutage24RunStartTime'], "totalCustomers": attr['totalCustomers'], "currentOutageHasOverrideattr":attr['currentOutageHasOverrideData'], "maxOutage24HasOverrideData": attr['maxOutage24HasOverrideData'], "maxOutage1HasOverrideData": attr['maxOutage1HasOverrideData'], "countyName": attr['countyName'], "stateId": attr['stateId'], "stateName": attr['stateName'], "countyFIPSCode": attr['countyFIPSCode'] } } # Update Table editTable.edit_features(adds=[addFeatures]) print("Finished")
Would you mind sharing some of your code? And when you say "feature layer", do you mean a layer in ArcGIS Online, or a file-based copy of the data?
Hi Josh,
Here's the URL I use (my API key is all zeroes for security):
https://eagle-i.doe.gov/api/outagesummary/countymax24hoursummary?state=OH&county=Adams&eiApiKey=00000000-0000-0000-0000-000000000000
And here is the response from the API call:
{"metadata":{"source":"DOE/CESER/ISER EAGLE-I™ Project","timestamp":"2021.09.10.16.52.01","url":"https://eagle-i.doe.gov","termsOfUse":"Terms of Use | The EAGLE-I™ real-time or historical electric outage data will not be repackaged, published, or distributed outside of the federal or state government emergency response community. | Allowed publishing or distribution is allowed for data summaries or derived data as long as the attribution noted above is preserved. | Similar data publishing and distribution limitations are required for non-DOE users of EAGLE-I data."},"data":[{"currentOutage":0,"currentOutageRunStartTime":"2021-09-10T16:30:00Z","maxOutage1":0,"maxOutage1RunStartTime":"2021-09-10T16:30:00Z","maxOutage24":1,"maxOutage24RunStartTime":"2021-09-10T15:30:00Z","totalCustomers":16247,"currentOutageHasOverrideData":false,"maxOutage24HasOverrideData":false,"maxOutage1HasOverrideData":false,"countyName":"Adams","stateId":19,"stateName":"OH","countyFIPSCode":"39001"}],"resolution":"MINUTES"}
I don't have any other code because I am not sure where to start. And yes, I am talking about creating a feature layer to use in ArcGIS Online. Although now that I say this, I would need it to produce a table that I can join to a feature layer, since the result does not have geometry.
Thanks for your help!
I see. Well, if you're comfortable using some Python, you can probably do this with Pandas and the ArcGIS Python API.
In particular, Pandas has a number of ways to get data in from other formats. Once you bring the JSON into a dataframe, you can do any other reshaping of the data there.
import pandas as pd j = { "metadata":{ "source":"DOE/CESER/ISER EAGLE-I™ Project", "timestamp":"2021.09.10.16.52.01", "url":"https://eagle-i.doe.gov", "termsOfUse":"Terms of Use | The EAGLE-I™ real-time or historical electric outage data will not be repackaged, published, or distributed outside of the federal or state government emergency response community. | Allowed publishing or distribution is allowed for data summaries or derived data as long as the attribution noted above is preserved. | Similar data publishing and distribution limitations are required for non-DOE users of EAGLE-I data." }, "data":[ {"currentOutage":0,"currentOutageRunStartTime":"2021-09-10T16:30:00Z","maxOutage1":0,"maxOutage1RunStartTime":"2021-09-10T16:30:00Z","maxOutage24":1,"maxOutage24RunStartTime":"2021-09-10T15:30:00Z","totalCustomers":16247,"currentOutageHasOverrideData":False,"maxOutage24HasOverrideData":False,"maxOutage1HasOverrideData":False,"countyName":"Adams","stateId":19,"stateName":"OH","countyFIPSCode":"39001"} ], "resolution":"MINUTES" } df = pd.DataFrame(data=j['data'])
And here's the dataframe:
Not sure how many calls you'll be making to the API and how consistently the output is formatted, but if you read through the Pandas docs, there are ways to easily append multiple frames into one. Or else, you can extract the "data" object from each call and merge those into a single list before creating the dataframe.
Then perform whatever reshaping you need to, if any.
After that, you can use the spatial submodule of the dataframe that comes from the ArcGIS API, to publish directly to a feature layer.
reshaped_df.spatial.to_featurelayer('my-layer-name')
EDIT: I should add, if you bring in the requests library, you can make your API calls right in the same script as the rest of this process.
Thanks, Josh. I have some comfort with python, although I've never used pandas. I am going to work with this today and let you know if i have any other questions and how it all turns out.
Thanks so much!
Linda
@LindaSlattery here is an example how to update a hosted table in AGOL using Python and the ArcGIS for Python API with the data example you provided. The hosted table's field names match the same as the Data object in you sample. You should just have to update the parameters:
import requests, json from arcgis import GIS # Variables username = "jskinner_CountySandbox" # AGOL Username password = "********" # AGOL Password itemID = 'f214df9b8f5440149a20ccd5d452a95e' # AGOL Table Item ID dataURL = 'https://eagle-i.doe.gov/api/outagesummary/countymax24hoursummary?state=OH&county=Adams&eiApiKey=0000...' # Disable warnings requests.packages.urllib3.disable_warnings() # Connect to AGOL gis = GIS('https://www.arcgis.com', username, password) # Get Table fLayer = gis.content.get(itemID) editTable = fLayer.tables[0] # Get Data params = {'f': 'pjson'} r = requests.post(dataURL, data = params, verify=False) response = json.loads(r.content) data = response['data'] print(data) # Create Dictionary of attributes addFeatures = { "attributes" : { "currentOutage" : data[0]['currentOutage'], "currentOutageRunStartTime" : data[0]['currentOutageRunStartTime'], "maxOutage1" : data[0]['maxOutage1'], "maxOutage1RunStartTime": data[0]['maxOutage1RunStartTime'], "maxOutage24": data[0]['maxOutage24'], "maxOutage24RunStartTime": data[0]['maxOutage24RunStartTime'], "totalCustomers": data[0]['totalCustomers'], "currentOutageHasOverrideData":data[0]['currentOutageHasOverrideData'], "maxOutage24HasOverrideData": data[0]['maxOutage24HasOverrideData'], "maxOutage1HasOverrideData": data[0]['maxOutage1HasOverrideData'], "countyName": data[0]['countyName'], "stateId": data[0]['stateId'], "stateName": data[0]['stateName'], "countyFIPSCode": data[0]['countyFIPSCode'] } } # Update Table editTable.edit_features(adds=[addFeatures]) print("Finished")
Thanks, Jake! and i hate to sound like an idiot, but when you say I will have to update the parameters, is that in the "params = " row? How would that be different from the addFeatures section?
Thanks so much for your help, this is very confusing to me 🙂
@LindaSlattery you will just need to update the section at the top of the script under # Variables:
username = ArcGIS Online Username
password = ArcGIS Online Password
itemID = item ID of the ArcGIS Online hosted table. This can be found in the URL when you view the item details
dataURL = the URL to retrieve data from the API you're querying
After that, you should be able to execute the script.
OK, I did all of that and got the following errors. That's why I thought I needed to add something else in teh params row.
--------------------------------------------------------------------------- JSONDecodeError Traceback (most recent call last) <ipython-input-1-8a00425e9f5e> in <module> 21 params = {'f': 'pjson'} 22 r = requests.post(dataURL, data = params, verify=False) ---> 23 response = json.loads(r.content) 24 data = response['data'] 25 print(data) /opt/conda/lib/python3.7/json/__init__.py in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw) 346 parse_int is None and parse_float is None and 347 parse_constant is None and object_pairs_hook is None and not kw): --> 348 return _default_decoder.decode(s) 349 if cls is None: 350 cls = JSONDecoder /opt/conda/lib/python3.7/json/decoder.py in decode(self, s, _w) 335 336 """ --> 337 obj, end = self.raw_decode(s, idx=_w(s, 0).end()) 338 end = _w(s, end).end() 339 if end != len(s): /opt/conda/lib/python3.7/json/decoder.py in raw_decode(self, s, idx) 353 obj, end = self.scan_once(s, idx) 354 except StopIteration as err: --> 355 raise JSONDecodeError("Expecting value", s, err.value) from None 356 return obj, end JSONDecodeError: Expecting value: line 1 column 1 (char 0)
OK, I did all of that and got the following errors.
OK, so I did that and got the following error. Sorry for the screen shot, but whenever I copied and pasted I got an error in here. Just my lucky day with errors 🙂
@LindaSlattery did you update the dataURL variable with the correct URL including the token?
Yes, with the API key. I can put the URL into a browser window and the data gets returned, so the URL is correct.
Outside of my username, password and URL, the only other thing that gets changed is the item ID. I created a blank table in Pro, added one row, and shared up to my AGOL. I made sure it was editable, and even made it public, just in case that was an issue. Could there be something up with the table? Here is the URL, if you want to look at it.
https://services6.arcgis.com/zxOMWqh0yAD6mMsJ/arcgis/rest/services/power_outages_county/FeatureServer
@LindaSlattery I found the issue. The request needs to be a get rather than a post.
Here is the updated code:
import requests, json from arcgis import GIS # Variables username = "jskinner_CountySandbox" # AGOL Username password = "********" # AGOL Password itemID = 'f214df9b8f5440149a20ccd5d452a95e' # AGOL Table Item ID dataURL = 'https://eagle-i.doe.gov/api/outagesummary/countymax24hoursummary?state=OH&county=Adams&eiApiKey=0000...' # Disable warnings requests.packages.urllib3.disable_warnings() # Connect to AGOL gis = GIS('https://www.arcgis.com', username, password) # Get Table fLayer = gis.content.get(itemID) editTable = fLayer.tables[0] # Get Data r = requests.get(dataURL, data = params, verify=False) response = json.loads(r.content) data = response['data'] print(data) # Create Dictionary of attributes addFeatures = { "attributes" : { "currentOutage" : data[0]['currentOutage'], "currentOutageRunStartTime" : data[0]['currentOutageRunStartTime'], "maxOutage1" : data[0]['maxOutage1'], "maxOutage1RunStartTime": data[0]['maxOutage1RunStartTime'], "maxOutage24": data[0]['maxOutage24'], "maxOutage24RunStartTime": data[0]['maxOutage24RunStartTime'], "totalCustomers": data[0]['totalCustomers'], "currentOutageHasOverrideData":data[0]['currentOutageHasOverrideData'], "maxOutage24HasOverrideData": data[0]['maxOutage24HasOverrideData'], "maxOutage1HasOverrideData": data[0]['maxOutage1HasOverrideData'], "countyName": data[0]['countyName'], "stateId": data[0]['stateId'], "stateName": data[0]['stateName'], "countyFIPSCode": data[0]['countyFIPSCode'] } } # Update Table editTable.edit_features(adds=[addFeatures]) print("Finished")
Very, very close now. I got an error but realized it was missing the params = {'f': 'pjson'} code under Get Data. It runs now but does not update the table. All of the data prints in the Notebook.
@LindaSlattery you should not need the params = {'f':'pjson'}. The issue appears to be the type of field for totalCustomers. This is set to Small Integer:
This data type is limited to only a small range of values. I would recommend converting all fields from Small Integer to Long Integer, then republish the feature service.
I really appreciate you giving me all of this help. If I remove the params = {'f':'pjson'} line, I get the following error:
22 # Get Data---> 23 r = requests.get(dataURL, data = params, verify=False)24 response = json.loads(r.content)25 data = response['data']
NameError: name 'params' is not defined
I added the line back in and changed the field type and it runs now, but only updates the first row of data. All of the rows appear in the notebook from the print command, but only the first record appends in the table. The response gives a row for each county.
Thank you so much, Jake! You are the best . However, I did get an error with the arcpy in the first row, but it worked perfectly when I took out arcpy. I set up my scheduler and will be testing it out throughout the day before we deploy it for the watch office. Your help is much appreciated!
Hi,I have approached the same problem while building a web app that uses python requests to query a 3rd party API to get the building parcel polygon when the app user types his address, but I got 3 issues:
1- when I use something like the following line, I get the error " data doesn't have an address" and it doesn't cause the 'shape column contains a polygon.
df.spatial.to_featureclass('block_groups_19_kendall')
2- if I am successful to transform the data frame into a feature class which functions to use to plot it on the map?
3- which function to use to link the user address typed in the search bar widget as an attribute in my requests query?
or what function or module in python API equals the following line in python:
user_address = str(SearchBarWidget.Input_value)
I know this is a lot to answer I am kinda new to ArcGIS, and you can just direct me where to search or mention the function name and I will do my best,
Thank you in advance
@LindaSlattery
Thank you Linda for your example. I have a question for you. I used your example and receive this error. Would you happen to know why?
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
