Creating a Feature Layer from an API

[LindaSlattery]

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?

[jcarlson]

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?

[LindaSlattery]

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!

[jcarlson]

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.

[LindaSlattery]

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]

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.

[LindaSlattery]

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

[jcarlson]

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')

[jcarlson]

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.

[LindaSlattery]

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?