0

I'm trying to convert a Pandas DataFrame column from UNIX to Datetime, but I either get a mismatch error or the new dates are all 1970-01-01.

Here is tail sample of the list:

ds y

86 1625616000000 34149.989815

87 1625702400000 33932.254638

88 1625788800000 32933.578199

89 1625875200000 33971.297750

90 1625884385000 33868.766626

When I look at how my UNIX looks like in datetime:

mydatetime = datetime.fromtimestamp(1618185600000 // 1000, tz=tzutc())
print('mydatetime',mydatetime)

I get:

mydatetime 2021-04-12 00:00:00+00:00

So when I use the conversion function:

df2 = pd.to_datetime(df1['ds'].astype(str), format='%Y-%m-%d %H:%M:%S+%f:%Z')

I get:

ValueError: time data '1618185600000' does not match format '%Y-%m-%d %H:%M:%S+%f:%Z' (match)

But, when I use the lazy road:

df2 = pd.to_datetime(df1['ds'], unit='ns')

The results are:

86 1970-01-01 00:27:05.616000

87 1970-01-01 00:27:05.702400

88 1970-01-01 00:27:05.788800

89 1970-01-01 00:27:05.875200

90 1970-01-01 00:27:05.886197

Name: ds, type: datetime64[ns]

HumanBot
  • 25
  • 1
  • 7
  • 1
    Did you consider: `unit='ms'`? This converts to seconds: `1618185600000 // 1000` and gives you the correct answer with `fromtimestamp`, which suggests you are starting with milliseconds. – Mark Jul 10 '21 at 05:10
  • Does this answer your question? [Convert unix time to readable date in pandas dataframe](https://stackoverflow.com/questions/19231871/convert-unix-time-to-readable-date-in-pandas-dataframe) – Henry Ecker Jul 10 '21 at 05:15

1 Answers1

2

Use pd.Timestamp to convert to datetime:

>>> df['ds'].mul(1e6).apply(pd.Timestamp)

0   2021-07-07 00:00:00
1   2021-07-08 00:00:00
2   2021-07-09 00:00:00
3   2021-07-10 00:00:00
4   2021-07-10 02:33:05
Name: ds, dtype: datetime64[ns]

Or suggested by @HenryEcker:

>>> pd.to_datetime(df['ds'], unit='ms')

0   2021-07-07 00:00:00
1   2021-07-08 00:00:00
2   2021-07-09 00:00:00
3   2021-07-10 00:00:00
4   2021-07-10 02:33:05
Name: ds, dtype: datetime64[ns]
Corralien
  • 109,409
  • 8
  • 28
  • 52