0

I have the following list of epoch timestamps in a column in my df which is df['DateTime'] (Note they dont have [] in the actual column:

 [1.59549158e+12]
 [1.59549322e+12]
 [1.59549322e+12]
 [1.59549322e+12]
 [1.59549322e+12]
 [1.59549522e+12]
 [1.59549685e+12]
 [1.59549685e+12]
 [1.59549685e+12]
 [1.59549784e+12]
 [1.59549784e+12]
 [1.59549871e+12]
 [1.59549969e+12]
 [1.59549969e+12]
 [1.59556182e+12]
 [1.59556182e+12]
 [1.59556661e+12]
 [1.59556661e+12]
 [1.59556661e+12]
 [1.59556661e+12]
 [1.59556833e+12]
 [1.59556939e+12]
 [1.59557079e+12]
 [1.59557079e+12]
 [1.59557246e+12]
 [1.59557246e+12]
 [1.59557335e+12]
 [1.59557335e+12]
 [1.59557335e+12]
 [1.59557564e+12]
 [1.59557564e+12]
 [1.59557564e+12]
 [1.59557603e+12]
 [1.59557603e+12]

Looking through the documentation and on StackOverflow I have tried the following code to convert those into a timestamp:

df['Date'] = pd.to_datetime(df['DateTime'],unit='s')

But I am getting an error saying

OverflowError: int too big to convert

and also

pandas._libs.tslibs.np_datetime.OutOfBoundsDatetime: cannot convert input 1595482694074.0 with the unit 's'

Does anyone know what these errors mean and how to get around it. It says int too big to convert but as a data type it is a float64. I am trying to convert to an Australian timezone ideally. If anyone has any ideas that would be great! Thanks very much!

SOK
  • 1,732
  • 2
  • 15
  • 33
  • 4
    Try `unit = 'ms'`, seems to work – dm2 Jul 24 '20 at 13:19
  • 4
    `pd.to_datetime(1595482694074,unit='ms')` – BENY Jul 24 '20 at 13:20
  • ah thats perfect -thanks heaps! – SOK Jul 24 '20 at 13:21
  • and then to convert to Australia time? – SOK Jul 24 '20 at 13:23
  • 1
    add `.tz_localize('UTC').tz_convert('Australia/Queensland')` at the end of your time conversion call (if original time is in UTC that is) – dm2 Jul 24 '20 at 13:31
  • thanks that worked - however now im getting the format `2020-07-23 16:06:39.420000+10:00` which seems to no be compatible with excel. Would this then be converting the datetime to a shortened format? Something like `2020-07-23 16:06:39` – SOK Jul 24 '20 at 13:36
  • 1
    check (skip out on `.dt` though, like with Australia conversion) https://stackoverflow.com/questions/34789888/convert-datetime-columns-to-a-different-timezone-pandas – dm2 Jul 24 '20 at 13:38
  • Thanks so much for the help. when i did the `.dt.tz_localize(None)` after it put it in the correct format. so in total the coded ended up being `df['Date'] = (pd.to_datetime(df['DateTime'],unit='ms').dt.tz_localize('utc').dt.tz_convert('Australia/Melbourne').dt.tz_localize(None))`. Thanks @dm2 for your help! – SOK Jul 24 '20 at 13:42

0 Answers0