pandas conert date failed - pd.to_datetime(df['xxx'], format='%Y-%m-%d').dt.date

Question

I am facing one little problem. I am storing some date time data and the data is

#secCode,secName,announcementTitle,announcementId,announcementTime

003816,xxx name,2024report,1222913141,1743004800000

the date time column is clearly string - 1743004800000, so when I try to convert it

df['announcementTime'] = pd.to_datetime(df['announcementTime'], format='%Y-%m-%d').dt.date

I got this error

  File "/root/miniconda3/lib/python3.12/site-packages/pandas/core/tools/datetimes.py", line 1072, in to_datetime
    values = convert_listlike(arg._values, format)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/miniconda3/lib/python3.12/site-packages/pandas/core/tools/datetimes.py", line 435, in _convert_listlike_datetimes
    return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ValueError: time data "1743004800000" doesn't match format "%Y-%m-%d", at position 0. You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.
    df['announcementTime'] = pd.to_datetime(df['announcementTime'], format='%Y-%m-%d')
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/miniconda3/lib/python3.12/site-packages/pandas/core/tools/datetimes.py", line 1072, in to_datetime

Panda Kim · Accepted Answer · 2025-08-25 00:06:46Z

2

Example

example for reproducing

import pandas as pd
df = pd.DataFrame({
    "announcementTime": [1743004800000, 1753004800000]
})

df

   announcementTime
0     1743004800000
1     1753004800000

Answer

Think of your input timestamp data as a number in ms, not '%Y-%m-%d' format

# use unit='ms' because the timestamp is in milliseconds
df["announcementTime"] = pd.to_datetime(df["announcementTime"], unit="ms")

# If you just want the date, use .dt.date
df["announcementDate"] = df["announcementTime"].dt.date

df

     announcementTime announcementDate
0 2025-03-26 16:00:00       2025-03-26
1 2025-07-20 09:46:40       2025-07-20

If it's not in ms, modify it to the appropriate unit.

edited Aug 25 at 0:06

answered Aug 25 at 0:00

Panda Kim

13.7k2 gold badges8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas conert date failed - pd.to_datetime(df['xxx'], format='%Y-%m-%d').dt.date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related