to_datetime returns NaT for epoch with errors='coerce'

[bhy]

xref #11760

Parsing unix epoch timestamps give NaT with errors='coerce' while they can be parsed correctly without it:

>>> pd.to_datetime(1420043460, errors='coerce', unit='s')
NaT
>>> pd.to_datetime(1420043460, unit='s')
Timestamp('2014-12-31 16:31:00')

pandas: 0.17.1
numpy: 1.10.1
Python 2.7.7

[jreback]

hmm, that does seem like a bug...

pull-requests welcome!

[engelmav]

I will take this one.

[jreback]

@engelmav want to do the PR?

[engelmav]

hey Jeff, sure. I don't have the fix yet. had to pause due to issues at
work.

On Sunday, January 24, 2016, Jeff Reback notifications@github.com wrote:

@engelmav https://github.com/engelmav want to do the PR?

—
Reply to this email directly or view it on GitHub
#11758 (comment).

[sumitbinnani]

Similarly, this is also not working as desired

>>> pd.to_datetime(11111111, unit='D', errors='ignore')
OverflowError: long too big to convert

[jorisvandenbossche]

@jreback I was looking at this, and some questions:

It seems this is deliberate, given the comment here: https://github.com/pydata/pandas/blob/084391126cc4edda35b41378d2905c788e5b573a/pandas/tslib.pyx#L2041 ("if we are coercing, dont' allow integers"). And I suppose this is to deal with the case of mixed types. For example, if you are parsing strings: pd.to_datetime(['2012-01-01', 1]), you want the integer to be coerced to NaT.
But, of course when having only integers, this behaviour is not correct.

But, I was also wondering, is there a reason we don't have a special path specifically for converting epochs (integers with unit specified), instead of using the general tslib.array_to_datetime where the types are checked for each element?
(simplified idea)

def to_datetime(arg, ...):

    ....
    elif com.is_integer(arg):
        _epoch_to_datetime(arg, unit)
    ....

def _epoch_to_datetime(vals, unit):
    return vals.astype('datetime[{}]'.format(unit))

[jreback]

@jorisvandenbossche so it could be in a routine. What you could do is multiply then feed it back to to_datetime with the same coerce option. you can directly astype if only if errors='raise'. I think ignore you might have to iterate as well.

[jreback]

further I suspect we can error out on the above example as unit doesn't make sense when you don't have integers/floats (though you can allow mixed things as long as you can astype them to ints/floats) eg.

In [1]: pd.to_numeric(['1.0',2,3.0])
Out[1]: array([ 1.,  2.,  3.])

in fact could be a pre-processing step assume we pass thru the errors, because that's the rub you have to potentially iterate here.

[jreback]

I think I can fix this shortly.