4

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

1 Answer 1

5

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.

3
  • 2
    furthermore ISO formats are automatically detected, so pd.to_datetime(df['Time']) works fine.
    – Mike T
    Oct 4, 2021 at 21:27
  • Edited my answer
    – ThomasG77
    Oct 4, 2021 at 22:24
  • @ThomasG77 thank you, that did work in parsing the field but the field still read 12/30/1899 after appending the data to a table Oct 5, 2021 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.