Creating a Feature Layer from an API

2583
26
Jump to solution
09-10-2021 09:04 AM
Labels (1)
LindaSlattery
New Contributor III

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?

0 Kudos
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

@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")

 

View solution in original post

0 Kudos
26 Replies
jcarlson
MVP Notable Contributor

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?

- Josh Carlson
Kendall County GIS
0 Kudos
LindaSlattery
New Contributor III

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=0000...

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!

0 Kudos
jcarlson
MVP Notable Contributor

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:

jcarlson_0-1631293924518.png

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.

- Josh Carlson
Kendall County GIS
LindaSlattery
New Contributor III

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

jcarlson
MVP Notable Contributor

Good luck, and you're welcome! I have a couple of scripts that do something similar, so if you need a specific example, I'm sure I can dig one up.

- Josh Carlson
Kendall County GIS
0 Kudos
LindaSlattery
New Contributor III

That would be awesome if you could find an example. Thanks!

0 Kudos
jcarlson
MVP Notable Contributor

So, here's a section of a script that pulled data from the US Census API, simplified and commented to make it relevant to what you're doing.

The API you're working with is largely going to determine what the details look like, but throwing together a request will look about the same.

# Import modules
from arcgis import GIS
import pandas as pd
import requests
import json

# Census variables to grab
cvar = {
    'GEO_ID':'GEOID',
    'B01001_001E':'total',
    'NAME':'name'
}

# Request parameters
payload = {
    'get' : ','.join(list(cvar.keys())),
    'for' : 'block group:*',
    'in' : ['state:17', 'county:093'],
    'APIkey' : 'my_api_key'
}

# Submit request to API
response = requests.get(f'https://api.census.gov/data/2019/acs/acs5', params=payload)

# Read response as JSON into dataframe
# First row is column names, skipped for data import
df = pd.DataFrame(response.json()[1:])

# Apply column names from first row
cols = response.json()[0]
column_dict = {}
for c in cols:
    column_dict.update({cols.index(c):c})

df.rename(columns=column_dict, inplace=True)

# Other reshaping happens here, drop a few columns, etc

# Explicitly state data types
df_dtypes = {
    'GEOID':'string',
    'name':'string',
    'total':'int32'
}

# Apply data types
df = df[list[df_dtypes.keys())].astype[df_dtypes]

# Export to layer
df.spatial.to_featureclass('block_groups_19_kendall')

 

- Josh Carlson
Kendall County GIS
0 Kudos
jcarlson
MVP Notable Contributor

Oh and by the way! If the end goal is to join these with other geometry, the Census's ArcGIS server is a great place to start! You can perform the join right there in your Python, too.

# TIGER county layer
geom_url = 'https://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/State_County/MapServer/1'

# Filter Illinois (17) and Kendall County (093) from larger dataset
kendall_filter = 'STATE = 17 and COUNTY = 093'

# Define fields you want to keep
cnty_fields = ['BASENAME', 'GEOID']

# Query service for county geometry
geom_df = arcgis.features.FeatureLayer(geom_url).query(where=kendall_filter, out_field=cnty_fields, as_df=True)

In my preceding example of Block Groups, I could query the Census's GIS layer, then join the shape field into my original dataframe using the GEOID column.

If there's a reliable way to get your output dataframe to have a column that matches a Census field, or perhaps something from the Living Atlas, you may be able to do your join all in one go.

- Josh Carlson
Kendall County GIS
0 Kudos
LindaSlattery
New Contributor III

I'm getting hung up on the following line: I've tried replacing the 1: with the variable name and even taking it out altogether, but then I get different errors. Any idea how to get past that?

LindaSlattery_0-1631300446659.png

 

0 Kudos