Table query fails with "TypeError: Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead."

[knoopum]

Table query fails with "TypeError: Casting to unit-less dtype 'datetime64' is not supported. Pass e.g. 'datetime64[ns]' instead."

[nanaeaubry]

@knoopum What version of the python api are you using?

[knoopum]

@nanaeaubry whichever version Esri has packaged in ArcGIS Online Notebooks (ArcGIS Notebook Python 3 Standard - 9.0)

[tyhranac]

@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.

[knoopum]

@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:

[VFRoderick]

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: []

[achapkowski]

@knoopum can you please export your table to a FGDB and share it? or share the URL?