arcgis-python-api
arcgis-python-api copied to clipboard
Esri
Table query fails with "TypeError: Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead."
Describe the bug
Querying a table will unexpectedly throw the following error, when there is no result to return and editor tracking is enabled for the table:
TypeError: Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead.
To Reproduce
- Create an empty table in a hosted feature layer (e.g., from the New Item in ArcGIS Online Content)
- Add a field in which to enter some values for which to query.
- Add some data to the table.
- Enable editor tracking on the hosted feature layer.
- Use query from arcgis.features.Table to query for a value you did not enter, and the following error message will be returned:
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<timed exec> in <module>
/opt/conda/lib/python3.9/site-packages/arcgis/features/layer.py in query(self, where, out_fields, time_filter, return_count_only, return_ids_only, return_distinct_values, group_by_fields_for_statistics, statistic_filter, result_offset, result_record_count, object_ids, gdb_version, order_by_fields, out_statistics, return_all_records, historic_moment, sql_format, return_exceeded_limit_features, as_df, having, **kwargs)
4241 ):
4242 columns["SHAPE"] = object
-> 4243 df = pd.DataFrame([], columns=columns.keys()).astype(columns, True)
4244 if "SHAPE" in df.columns:
4245 df["SHAPE"] = GeoArray([])
/opt/conda/lib/python3.9/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors)
6303 else:
6304 try:
-> 6305 res_col = col.astype(dtype=cdt, copy=copy, errors=errors)
6306 except ValueError as ex:
6307 ex.args = (
/opt/conda/lib/python3.9/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors)
6322 else:
6323 # else, only a single dtype is given
-> 6324 new_data = self._mgr.astype(dtype=dtype, copy=copy, errors=errors)
6325 return self._constructor(new_data).__finalize__(self, method="astype")
6326
/opt/conda/lib/python3.9/site-packages/pandas/core/internals/managers.py in astype(self, dtype, copy, errors)
449 copy = False
450
--> 451 return self.apply(
452 "astype",
453 dtype=dtype,
/opt/conda/lib/python3.9/site-packages/pandas/core/internals/managers.py in apply(self, f, align_keys, **kwargs)
350 applied = b.apply(f, **kwargs)
351 else:
--> 352 applied = getattr(b, f)(**kwargs)
353 result_blocks = extend_blocks(applied, result_blocks)
354
/opt/conda/lib/python3.9/site-packages/pandas/core/internals/blocks.py in astype(self, dtype, copy, errors, using_cow)
509 values = self.values
510
--> 511 new_values = astype_array_safe(values, dtype, copy=copy, errors=errors)
512
513 new_values = maybe_coerce_values(new_values)
/opt/conda/lib/python3.9/site-packages/pandas/core/dtypes/astype.py in astype_array_safe(values, dtype, copy, errors)
240
241 try:
--> 242 new_values = astype_array(values, dtype, copy=copy)
243 except (ValueError, TypeError):
244 # e.g. _astype_nansafe can fail on object-dtype of strings
/opt/conda/lib/python3.9/site-packages/pandas/core/dtypes/astype.py in astype_array(values, dtype, copy)
185
186 else:
--> 187 values = _astype_nansafe(values, dtype, copy=copy)
188
189 # in pandas we don't store numpy str dtypes, so convert to object
/opt/conda/lib/python3.9/site-packages/pandas/core/dtypes/astype.py in _astype_nansafe(arr, dtype, copy, skipna)
114 dti = to_datetime(arr.ravel())
115 dta = dti._data.reshape(arr.shape)
--> 116 return dta.astype(dtype, copy=False)._ndarray
117
118 elif is_timedelta64_dtype(dtype):
/opt/conda/lib/python3.9/site-packages/pandas/core/arrays/datetimes.py in astype(self, dtype, copy)
692 and is_unitless(dtype)
693 ):
--> 694 raise TypeError(
695 "Casting to unit-less dtype 'datetime64' is not supported. "
696 "Pass e.g. 'datetime64[ns]' instead."
TypeError: Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead.
Expected behavior
An empty result should be returned when there is no match.
Platform (please complete the following information):
ArcGIS Online Notebooks (ArcGIS Notebook Python 3 Standard - 9.0)
@nanaeaubry whichever version Esri has packaged in ArcGIS Online Notebooks (ArcGIS Notebook Python 3 Standard - 9.0)
@knoopum I followed the steps to reproduce using the ArcGIS Notebook Python 3 Standard - 9.0 runtime in ArcGIS Online Notebooks without issue. Can you post a code snippet?
Sidenote: You can check the arcgis versions in your ArcGIS Online Notebook by running
import arcgis
arcgis.__version__
or navigating to Info > Runtime in the Notebook UI
It should be
2.2.0.1.
@tyhranac Sorry! I left off an important part of the instructions to reproduce. In step 5, when you query the table, set as_df = True.
Querying for all the records works:
Querying for a value that exists in the data also works:
Querying for a value that does not exist in the data generates the error:
I've been running into this issue too. I have a feature layer collection that has empty data but query with as_df against Feature Layer types works and queries against tables results in the same error. This is on arcgis.gis verions '2.2.0.1'.
I think the issue lies in that the "Fields" array that details all the different fields and their data types when returning just a featureset is included in for Feature Layer types but this isn't the case for Tables. So when you have date fields in a table it autos unit-less dtype.
Interestingly if you query to make it a featureset and then use the sdf method on the featureset to return a dataframe it works but returns essentially an empty dataframe with no columns or dtypes.
My work around is basically capturing the "Fields" array from the properties of the table, making a new dictionary that mimics the table featureset structure and injects the Fields array into it. Then when I create a Dataframe from this updated featureset it understands that the datetime fields should be 'datetime64[ns].
flc_master = WF_Data_All_Item.layers[0].container
wf_data = {}
for lyr in flc_master.layers + flc_master.tables:
_obj = {}
_id = lyr.properties.id
_name = lyr.properties.name
_recCount = lyr.query(where='1=1', out_fields='*',return_count_only=True)
_type = lyr.properties.type
_obj['id'] = _id
_obj['name'] = _name
_obj['record count'] = _recCount
_obj['lyr'] = lyr
_obj['type'] = _type
if _recCount == 0:
if _type == 'Feature Layer':
df = lyr.query(where='1=1', out_fields='*',as_df=True)
_obj['df'] = df
if _type == 'Table':
try:
df = lyr.query(where='1=1', out_fields='*',as_df=True)
print(df.info())
print(df)
except Exception as e:
print(e)
else:
print("Query as_df worked")
finally:
try:
df = lyr.query(where='1=1', out_fields='*').sdf
print(df.info())
print(df)
except Exception as e:
print(e)
else:
print("FeatureSet as sdf worked")
finally:
pass
fields = lyr.properties.fields
fs = lyr.query(where='1=1', out_fields='*').to_dict()
new_dict = {}
for key,value in fs.items():
if key == 'fields':
new_dict[key] = fields
else:
new_dict[key] = value
fs_new = FeatureSet.from_dict(new_dict)
df = fs_new.sdf
print(df.info())
print(df)
This outputs:
Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead.
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 0 entries
Empty DataFrame
None
Empty DataFrame
Columns: []
Index: []
FeatureSet as sdf worked
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 0 entries
Data columns (total 12 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 name 0 non-null string
1 contactnumber 0 non-null string
2 userid 0 non-null string
3 GlobalID 0 non-null string
4 wfprivileges 0 non-null string
5 CreationDate 0 non-null datetime64[us]
6 Creator 0 non-null string
7 EditDate 0 non-null datetime64[us]
8 Editor 0 non-null string
9 Project 0 non-null string
10 OG 0 non-null string
11 OBJECTID 0 non-null Int64
dtypes: Int64(1), datetime64[us](2), string(9)
memory usage: 124.0 bytes
None
Empty DataFrame
Columns: [name, contactnumber, userid, GlobalID, wfprivileges, CreationDate, Creator, EditDate, Editor, Project, OG, OBJECTID]
Index: []
Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead.
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 0 entries
Empty DataFrame
None
Empty DataFrame
Columns: []
Index: []
FeatureSet as sdf worked
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 0 entries
Data columns (total 10 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 description 0 non-null string
1 GlobalID 0 non-null string
2 CreationDate 0 non-null datetime64[us]
3 Creator 0 non-null string
4 EditDate 0 non-null datetime64[us]
5 Editor 0 non-null string
6 Sequence 0 non-null Int32
7 Project 0 non-null string
8 OG 0 non-null string
9 OBJECTID 0 non-null Int64
dtypes: Int32(1), Int64(1), datetime64[us](2), string(6)
memory usage: 124.0 bytes
None
Empty DataFrame
Columns: [description, GlobalID, CreationDate, Creator, EditDate, Editor, Sequence, Project, OG, OBJECTID]
Index: []
@knoopum can you please export your table to a FGDB and share it? or share the URL?
We put in a fix that has been released. Closing due to this being addressed. Feel free to comment or reopen if needed