Feature Layer to Geodataframe: DateTime Field Issues

[RainaMonaghan2]

Hello,

I am currently working with the API to pull data from a feature layer, transform it to a geodataframe with geopandas, and push it to a PostgreSQL database with PostGIS. (<-- just explaining the need  to turn the layer into a gdf, since geopandas has a nifty .to_postgis method)

Unfortunately, this is changing the DateTime field into a format I can't understand or parse. Below is my code, and the resulting DT field. The original field is in the routine Esri DateTime format. 

gis = GIS()
url_2000 = 'https://services3.arcgis.com/T4QMspbfLg3qTGWY/arcgis/rest/services/Historic_Geomac_Perimeters_All_Years_2000_2018/FeatureServer/0'
layer_2000 = FeatureLayer(url_2000)
fset_2000 = layer_2000.query()
gjson_string_2000 = fset_2000.to_geojson
gjson_dict_2000 = json.loads(gjson_string_2000)
gdf_2000 = gpd.GeoDataFrame.from_features(gjson_dict_2000['features'])

Any help figuring this out would be much appreciated, thanks in advance!

[Jan-Tschada · Accepted solution]

This is the UNIX timestamp/epoch in milliseconds. If you want to use the FeatureSet as a dataframe the easiest way is the spatially enabled dataframe.

from arcgis import GIS
from arcgis.features import FeatureLayer, FeatureSet
from datetime import datetime
import json
import geopandas as gpd

gis = GIS()
url_2000 = 'https://services3.arcgis.com/T4QMspbfLg3qTGWY/arcgis/rest/services/Historic_Geomac_Perimeters_All_Years_2000_2018/FeatureServer/0'
layer_2000 = FeatureLayer(url_2000)
fset_2000 = layer_2000.query()
fset_2000.sdf

The conversion from UTC timestamp to the datetime object is done under the hood. 

If you still want to use the GeoDataFrame from the geopandas module, you have to write your own conversion implementation. The to_geojson property of the FeatureSet is going to reconvert the datetime object back into an UTC timestamp. This seems a little bit awkward. The following function is converting the UTC timestamp into the ISO format string, because your code reloads the GeoJSON into a python object again. Furthermore, the to_geojson also encodes your strings into unicode escaped characters ('\uxxx') - I love our German Umlauts. I would suggest to stay away from the to_geojson approach.

The following function directly converts the UTC timestamps into the ISO string format.

def convert_timestamps(feature_set: FeatureSet, field_names, use_iso=True):
    """Converts timestamps (UNIX epoch in [ms]) into datetime objects by modifying the feature set directly."""
    for feature in feature_set.features:
        for attribute_name in feature.attributes:
            if attribute_name in field_names:
                if use_iso:
                    feature.attributes[attribute_name] = datetime.utcfromtimestamp(feature.attributes[attribute_name] * 1e-3).isoformat()
                else:
                    feature.attributes[attribute_name] = datetime.utcfromtimestamp(feature.attributes[attribute_name] * 1e-3)

So that, you just need to call it by using:

convert_timestamps(fset_2000, ['perimeterdatetime'])
gjson_string_2000 = fset_2000.to_geojson
gjson_dict_2000 = json.loads(gjson_string_2000)
gdf_2000 = gpd.GeoDataFrame.from_features(gjson_dict_2000['features'])

The geopandas dataframe contains the correct ISO string. You have to reconvert it into a datetime object, again. This is only needed because of the to_geojson property behavior.

Hope it helps.