GeoAccessor pd.DataFrame.spatial.to_table()/from_table() errors with nulls/NaNs

TylerT — Wed, 30 Oct 2024 17:11:32 GMT

Greetings,

I've been struggling with both pd.DataFrame.spatial.from_table()/to_table() methods particularly when NaNs (nulls) are in integer columns.

First issue: spatial.to_table() will not write int64 types. Downcasting the offending fields to int32 will fix the issue but only if you don't have NaNs which aren't allowed for int32. Passing the offending columns as float64 will also fix the issue but introduces the second issue below. Note: Line 9 below is not necessary because pandas reads the integer column as float64, however showing to be explicit.

Note: Passing float64 interestingly ends up as integer datatype in EDGB. This image below shows EGDB table metadata.

Second issue: If the float64 approach above is taken then spatial_from_table() throws an error when NaNs are present in integer columns, unless skip_nulls is True (default). Leaving skip_nulls=True is not desirable because valid rows are discarded.

Changing the null_value argument to np.NaN results in same error.

Any troubleshooting advice would be greatly appreciated. Here is the test DataFrame constructor for your use.

import pandas as pd import numpy as np from arcgis.features import GeoAccessor, GeoSeriesAccessor data = { 'string_column': ['A', 'B', 'C', 'D'], 'integer_column': [1, np.NaN, 3, 4], 'float_column': [1.23, 4.56, np.NaN, 10.11], 'datetime_column': pd.to_datetime(['2024-11-22', '2024-11-23' , '2024-11-24', '2024-11-25']) } df = pd.DataFrame.from_dict(data) df['integer_column'] = df['integer_column'].astype('float64') display(df) display(df.info()) df.spatial.to_table(location=sde / tbl)

Thank you.

Tyler
ArcGIS Pro 3.3
Enterprise 11.2 (MSSQL Server)

Re: GeoAccessor pd.DataFrame.spatial.to_table()/from_table() errors with nulls/NaNs

CMV_Erik — Mon, 09 Dec 2024 17:37:54 GMT

Have you tried casting the integer column to pd.Int32Dtype()?

df[integercol] = df[integercol].astype(pd.Int32Dtype())

I've also had better luck with None than NaNs

df = df.replace({np.nan:None})

FYI I've been working around this for a while in Pro 2.8.8; I'm hoping it will be improved in Pro 3. My bigest complaint is that both to_table() and to_featureclass() have issues with exporting date/number datatypes that the dataframes themselves support. But for some reason, to_table() is more limited than to_featureclass(). I just had a problem where to_table failed on an integer column with empty values. So I threw on a dummy shape column, saved that using to_featureclass(), and then used ArcPy Table to Table to get the table I wanted. Very much hoping this issue was noticed and fixed long ago.

topic GeoAccessor pd.DataFrame.spatial.to_table()/from_table() errors with nulls/NaNs in ArcGIS API for Python Questions

GeoAccessor pd.DataFrame.spatial.to_table()/from_table() errors with nulls/NaNs

Re: GeoAccessor pd.DataFrame.spatial.to_table()/from_table() errors with nulls/NaNs