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; } } }

Convert data frame to stand alone table

LCS123

Hello, we are exploring the use of ArcGIS Notebook within ArcGIS Pro and was hoping this community could help with a problem we have run into. Our ultimate goal is to retreive data from a Google Sheet we use and join that data to a feature layer (polygon) within ArcGIS Pro, which we will then look to automate web publishing to one of our ArcGIS Online maps. I have had someone develop some python that retreives our Google Sheet data (see attached). We are trying to figure out how to convert this data frame to a stand alone table so that we can join with our feature layer. We wanted to see if this is possible? If not, we were thinking that we need to some how execute the join between the feature layer and the Google Sheet (now in our data frame) all through ArcGIS Notebook, if possible. Please note both the Google Sheet data and feature layer will be update frequently so we are trying to automate this process. Any advise is greatly appreciated!

Capture.PNG

Find more posts tagged with

Accepted answers

HannesZiegler

Glad to help! I hope all is well, Ida battered quite a few places..

Regarding your error, you've got a bunch of 'O' (object) data types in the structured array, and you need to set those to the appropriate data type - see my response on an old thread here.

HannesZiegler

Is this a pandas dataframe?

You can probably use to_records (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_records.html) to create a numpy structured (aka record) array, then use arcpy.NumPyArrayToTable (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/numpyarraytotable.htm). I'll let you guys work out the details, since this might need some massaging!

Hope this gets you unstuck!

All comments

MatthewLeonard

@BenCapell_NWTF10 This solved my problem, thanks!

I had converted an Excel sheet to pandas dataframe, made some tweaks, and then wanted to convert the dataframe to an ArcGIS standalone table. Even though I had set the column data types to "str" when converting from Excel to pandas, apparently the string fields were still "object" data types. So this fix allowed me to convert to ArcGIS table with appropriate field data types!

HannesZiegler

Hey all, you can also use apache arrow to potentially convert from Pandas DataFrame to GDB Table more easily, see Leverage Apache Arrow in ArcGIS Pro (esri.com)

BenCapell_NWTF10

I couldn't seem to edit my previous comment, so to follow with a script edit. I actually needed to enter a the desired string field length on line 4 below.

dtypes = {}
for col,type in df.dtypes.items():
    if str(type) == 'object':
        dtypes[col] = f'U255'
    else:
        dtypes[col] = type
array = df.to_records(column_dtypes=dtypes)

BenCapell_NWTF10

Thank you for this contribution. I am utilizing this concept of converting pd.DataFrame to GDB Table as part of a larger workflow that moves queried database records into a .gdb as a table participating in a relationship class with a feature class.
To accomplish the pd.DataFrame to GDB Table step: first the pd.DataFrame must become a numpy.array with df.to_records(). Then the array is converted to GDB Table with arcpy.da.NumPyArrayToTable(array, table_path). The snag, as noted, is that the data type 'object'/'O' can't be inferred by GDB Table fields when reading the numpy.array. I declared the 'object' data type as string for each column using the process below to convert columns with the 'O' type into String fields. Note that if some other field type should be applied for a field that is typed as 'O' then the code will need adjusting. Hope this helps the next viewer of this thread!

numpy data types ref: https://numpy.org/doc/stable/user/basics.types.html#data-types-for-strings-and-bytes

```

dtypes = {}
for col,type in df.dtypes.items():
    if str(type) == 'object':
        dtypes[col] = f'U{len(col)}'
    else:
        dtypes[col] = type
array = df.to_records(column_dtypes=dtypes)

LCS123

Thank you, HannesZiegler! It worked!

HannesZiegler

Glad to help! I hope all is well, Ida battered quite a few places..

Regarding your error, you've got a bunch of 'O' (object) data types in the structured array, and you need to set those to the appropriate data type - see my response on an old thread here.

LCS123

HannesZiegler, first off, thank you for taking a look at this! We've had some delays here in Louisiana given the Ida impacts but I am actually trying to troubleshoot myself (to help build my own Python literacy). I've converted our Dataframe to a rec.array using the first step. Still having trouble running NumPyArraytoTable as seen in the attached. I'll have our python expert take a look at this once he is back up in running. Will keep you posted. Thanks again!

Capture.PNG

HannesZiegler

Is this a pandas dataframe?

Hope this gets you unstuck!

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.