1

I have a dataframe containing excel style date values like:

launched_at
1375134026  
1377313137

;;;;

I am unable to convert these using SO answers Convert Excel style date with pandas

I run into errors like:

OutOfBoundsDatetime                       Traceback (most recent call last)
<ipython-input-76-f255a40f213e> in <module>()
      1 import datetime as dt
      2 
----> 3 cf_success['launch date'] = pd.to_datetime(cf_success['launched_at'], unit='d', origin='1899-12-30')

1 frames
/usr/local/lib/python3.7/dist-packages/pandas/core/tools/datetimes.py in _convert_listlike_datetimes(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)
    338 
    339             result, tz_parsed = tslib.array_with_unit_to_datetime(
--> 340                 arg, unit, errors=errors
    341             )
    342 

pandas/_libs/tslib.pyx in pandas._libs.tslib.array_with_unit_to_datetime()

OutOfBoundsDatetime: cannot convert input with unit 'd'

My desired output is

2015-08-11 12:12:28
CallumDA
  • 12,025
  • 6
  • 30
  • 52
K C
  • 413
  • 4
  • 15
  • your example date "2015-08-11 12:12:28" would be ~42227.50866 in Excel, so a number like `1375134026` is definitely not an Excel serial date. – FObersteiner Nov 19 '21 at 13:11
  • 1
    checking UNIX time 1375134026, this would be e.g. 2013-07-29T21:40:26+00:00 (use `pd.to_datetime(cf_success['launched_at'], unit='s')` for that). – FObersteiner Nov 19 '21 at 13:12
  • Thanks very much, I am able to convert it now. Sorry for the confusion - I actually meant 'My desired output format is - '. An issue here is that - the date as is informed to me is from year 2019 or 2020, and I am getting 2017 by 's' and 1970 by 'ms'. Do you know how to adress this issue. – K C Nov 19 '21 at 13:58
  • 1
    ***IF*** it's UNIX time, ***then*** it's definitively seconds, not milliseconds, but two years off from what you expect? Hm... not sure, no. Problem with serial dates is that they might be nice in terms of bytes required to store them but they don't carry a lot of information so it's sometimes hard to make any sense of them later on (as you just experience...). – FObersteiner Nov 19 '21 at 14:07
  • 1
    Thanks a lot for this information and also the solution. – K C Nov 19 '21 at 14:10

0 Answers0