nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Appending from pandas dataframe directly to hosted table

HanliePetoors

Hi,

I have a hosted feature layer in AGOL that contains 209 layers of asset types.

I want to export the assets to an asset register that resides in a hosted table in AGOL.

To do this I loop through the asset layers and append them to a pandas dataframe containing a subset of the common fields in the asset layers. All the layers' data are then in one dataframe.

I then want to append this dataframe to the hosted table. The table must be truncated and appended each time the script is run. I already have the code to truncate the table but now I'm struggling with the append part.

The intention is for the script to be run as an AGOL notebook. Most of the examples I have found require a local CSV version of the dataframe that then gets re-published to AGOL.

1. Is it possible to bypass the local CSV and just append the dataframe directly to the hosted table? Perhaps with an insert cursor?

2. If not, how does the local file work if one is running the notebook from inside AGOL? How do you specify where the local file is supposed to be?

Thanks

Hanlie

Find more posts tagged with

Accepted answers

jcarlson

Ah, I see. Well, you could start your notebook with a command to install geopandas, but it's probably not the right approach.

Does your data have any spatial information in it?

As for the append method, when I'm in a situation where I have a dataframe or other data source that I want to append to my layer, the process, in my mind, is very simple:

source → function → destination

But the append method requires you to export your dataframe to a file, upload the file to your portal, get its itemid, run append and refer to the itemid, plus any additional info like field mappings, etc. It seems like a lot of extra steps when I already have the data in my python workspace. Creating and uploading files is just a waste of time and storage space.

Also, the append method assumes that your data is complete, which is not always the case for what I'm doing. I may have a dataframe of a particular subset of columns that I want to update. As long as I have the matching objectid, FeatureLayer.edit_features can handle that just fine. If you know what you're doing, you can be a lot more precise and efficient than the append method permits.

All comments

maya_fromstein

Hi!

Wow your suggestions are amazing and really saved the day with the issues I was having updating point feature layers. Thank you so much!

Do you happen to have any sample code/resources for your selective feature editing? I am trying to edit features that are really huge polygons (ie., Northern Europe). When I truncate the whole feature layer I lose the geometry, but the geometry/shape is too big to be added back in without getting timeout errors and it crashing. It would be *super* handy to:

* identify which features even need editing (though it will probably be nearly all of them, there are only 20 and they are regional summaries)

* identify which attributes need editing

* then specifically only modify those attributes! without removing and reassembling (that can't be the right term, sorry) the impossibly large geometries.
(basically everything you mentioned in your workflow! 🙌)

The examples in the edit_features documentation all involve (to my understanding) updating the geometry too. I am still not that good in python, and all the playing around I've done with the feature attribute dictionaries always come back down to that issue.

Any help or guidance into the right direction would be hugely helpful and appreciated!

Thank you so much.

HanliePetoors

Hi Josh,

Thanks, I'll give the edit_features method a go.

The data from which I construct the dataframe is spatial, but I don't bring the geometries into the dataframe because my output is to a hosted table. Basically an asset register for the financial people.

Regards

Hanlie

jcarlson

Ah, I see. Well, you could start your notebook with a command to install geopandas, but it's probably not the right approach.

Does your data have any spatial information in it?

As for the append method, when I'm in a situation where I have a dataframe or other data source that I want to append to my layer, the process, in my mind, is very simple:

source → function → destination

HanliePetoors

Hi Josh,

Thanks for the reply.

I'm struggling with your suggestion though because I'm using a standard pandas dataframe and I'm not sure about using Geopandas. If the script is going to run from a Notebook server in AGOL, then the administrator of the organisation will need to to add that module, correct? I'm not sure if that's an option.

On top of that Geopandas requires an address or coordinates that I don't have. I could put in fake ones, but it seems like a very roundabout way of doing things.

Why do you say that the append method in the API is far more trouble than it's worth?

Thanks

Hanlie

jcarlson

In the ArcGIS Python API, there is actually a method for converting you GeoPandas frame to Esri's "GeoAccessor".

https://developers.arcgis.com/python/api-reference/arcgis.features.toc.html#geoaccessor

Once your data is in a GeoAccessor, you can use GeoAccessor.spatial.to_featureset() to generate a featureset that can be applied directly to a table.

The "append" method in the API is far more trouble than it's worth, so we can just use "edit_features" on the feature layer object.

from arcgis.features import GeoAccessor

# your current process goes here where you build your geodataframe
# let's assume your geodataframe is called "gdf"
# you also truncated the layer already, so let's assume that featurelayer objected is called "fl"

# convert to geoaccessor
sdf = GeoAccessor.from_geodataframe(gdf)

# convert to featureset
fs = sdf.spatial.to_featureset()

# apply edits
fl.edit_features(adds=fs)

The documentation suggests doing your edits in batches if there is a very large number of features.

Also, if you're certain that the data all needs to be refreshed, truncating and appending is fine. But if some of the features don't actually change, consider using the pandas method compare. You'd have to query the hosted layer to its own dataframe, but once you have it, you can compare the two frames to identify which specific rows and columns have changed.

We've moved many of our scripts to that model. Our parcels layer, for instance, has about 60k features in it, but on a given day, only about 300 parcels are edited, and those only in a few columns like owner name. By isolating the specific attributes edited, our actual edits are kept to a minimum, and the layer is never empty.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.