[GIS] Parsing/converting ISO 8601 datetime fields using pandas

arcgis-prodatetimeenterprise-geodatabasepandaspython

I have a datetime field in a Pandas dataframe that becomes problematic when using ArcGIS Pro to load data from a csv to a table on ArcSDE. The field in Pandas looks like this:

2007-02-01T05:00:00.0000000+00:00

but the output in field in ArcGIS Pro reads the datetime column as 12/30/1899 throughout the entire dataset.

I tried using a pandas script to parse the field:

df['Time'] = pd.to_datetime(['Time'], 
                        format="%Y-%m-%dT%H:%M:%S.%f")

but I'm receiving:

ValueError: time data Time doesn't match format specified

I simply want the field in an acceptable format to load into an ArcSDE table.

Update:

The suggested change worked:

df['Time'] = pd.to_datetime(df['Time'], format="%Y-%m-%dT%H:%M:%S.%f", errors = 'coerce')

The result in Pandas is this:

Time    
2007-02-01 05:00:00+00:00

but after using the append tool in ArcGIS Pro, the time field defaults to:

12/30/1899

Best Answer

A working sample

import pandas as pd
 
data = [{'mydate': '2007-02-01T05:00:00.0000000+00:00', 'b': 2, 'c':3},
        {'mydate': '2007-02-01T05:00:00.0000000+00:00', 'b': 20, 'c': 30}]
 
# Creates DataFrame.
df = pd.DataFrame(data)

# Below line commented to adopt simpler alternative
# df['mydate'] = pd.to_datetime(df['mydate'], format="%Y-%m-%dT%H:%M:%S.%f")
# Simplified version due to Mike T comment
df['mydate'] = pd.to_datetime(df['mydate'])

Your issue is that you use

df['Time'] = pd.to_datetime(['Time'], 
                        format="%Y-%m-%dT%H:%M:%S.%f")

instead of

df['Time'] = pd.to_datetime(df['Time'], 
                        format="%Y-%m-%dT%H:%M:%S.%f")

The difference is in pd.to_datetime(['Time'] vs pd.to_datetime(df['Time']

As stated by Mike T in comment, a simplified version can be df['Time'] = pd.to_datetime(df['Time'])

Although you are using ArcGIS Pro, the issue is unrelated to geospatial. In this case, it's better to post on http://stackoverflow.com instead.

Related Question