Support or default to less detailed datetime64

[CarstVaartjes]

Hi,

I regularly run into issues where I have dates that fall outside of Pandas's datetime standards. Quite a few data sources have defaults such as "9999-12-31" and stuff like that, leading to issues in pandas.

This is because Pandas defaults to nanoseconds where the time span is quite limited.
See: http://docs.scipy.org/doc/numpy/reference/arrays.datetime.html
Code Meaning Time span (relative) Time span (absolute)
s second +/- 2.9e12 years [ 2.9e9 BC, 2.9e9 AD]
ms millisecond +/- 2.9e9 years [ 2.9e6 BC, 2.9e6 AD]
us microsecond +/- 2.9e6 years [290301 BC, 294241 AD]
ns nanosecond +/- 292 years [ 1678 AD, 2262 AD]

I first thought it was the unit='s' parameter in to_datetime would work (see: http://pandas.pydata.org/pandas-docs/version/0.14.0/generated/pandas.tseries.tools.to_datetime.html), but this is only for translating a different datetime to nano seconds (I think) and the "ns" detail level seems to be rather hard coded.

I cannot imagine the majority of the use cases needing nano seconds; even going to micro seconds extends the date range to something that in my experience should always work. The nanosecond 2262AD is really limited.

Imho, ideally one should be able to choose the detail level. Is this just me or is this a common issue?

[jreback]

http://pandas-docs.github.io/pandas-docs-travis/gotchas.html#timestamp-limitations

simply use periods and use NaT as appropriate for missing values

[ifmihai]

it's a more common issue than believed

I bump into this issue more often than not

my guess is that at least 99% of pandas users dont need nanoseconds

if this is true,
then it would be common sense to use microseconds as default
and then to have something like
df = P.DataFrame(index=nano_range(start,end))

nano seconds usage is a particular case, not the rule

I really don't understand the rationale behind all this nano planning

But I dont understand how hard it would be to change to microseconds, either

[jreback]

@ifmihai

In [84]: period_range('201-1-1 10:20','9000-1-1 12:00',freq='H')
Out[84]: 
<class 'pandas.tseries.period.PeriodIndex'>
[201-01-01 10:00, ..., 9000-01-01 12:00]
Length: 77130459, Freq: H

you can simply use a PeriodIndex to represent any time span you want. DatetimeIndex and PeriodIndex have different usecases. DatetimeIndex provides maximum resolution within the most common timespans and is thus pretty general. If you need more range but less resolution then use a PeriodIndex. You CAN have it both ways. No need to change the defaults and try to conform everyone to one representation.

[ifmihai]

@jreback
"no need to change the defaults and try to conform everyone to one representation

what about nanoseconds? isnt it the same?

i feel forced to conform to whoever considered nanoseconds is best (which is really not practical for most user cases)
I would surely what to hearthe opinion of him who decided nanosecond unit
listen to what he has to say
I doubt it will be convincing, except probably he was forced by numpy, but that's out of my league

anyway, nanosecond unit receives a big -1 from me
(i already started to hate nanoseconds btw :D)

[jreback]

@ifmihai this was decided quite a long time ago, by @wesm (and I think it was numpy that drove it, not really sure). As I said before, that was one reason Periods were created. Changing the default is a non-starter.

[CarstVaartjes]

But how can we read a csv file for instance and convert dates to periods? Sincere question, as I'm really struggling with this and the documentation is really unclear here. Because there is

1. a "to_datetime" where I can give a date format but it will refuse to convert out of nanosecond bound datetimes, so I cannot use it (I will lose data); the units do not work (as in: "unit='s'" does not solve the out of bound issue, while in numpy it does!)
2. a "to_timestamp", but that's not really anything with a string to date conversion
3. a "to_period", also not a string to date conversion but "Convert TimeSeries from DatetimeIndex to PeriodIndex"; however I cannot create a DatetimeIndex with my values in the first place because I lose all out of bound values

See also this example:

x_df = DataFrame([[20120101, 20121231], [20130101, 20131231], [20140101, 20141231], [20150101, 99991231]])
x_df.columns = ['date_from', 'date_to']
date_def = '%Y%m%d'
x_df['date_to_2'] = [datetime.datetime.strptime(str(date_val), date_def) for date_val in x_df['date_to']]
x_df['date_to_3'] = [np.datetime64(date_val, unit='s') for date_val in x_df['date_to_2']] #
# it's all objects, even though date_to_3 is a full np.datetime64
x_df.dtypes
list(x_df['date_to_3'])

i cannot make date_to_3 into a period (as far as i know) and while being a perfectly nice np.datetime64 (with unit='s' instead of unit='ns'), Pandas refuses to see it as such.
It's really a Pandas limitation afaik; this really is an issue and Periods do not solve it (in any way that I can see at the moment)

[jreback]

Read them in as ints (you don't really need to do anything special to do this), just don't specify 'parse_dates'

In [27]: x_df.to_csv('test.csv',mode='w')

In [28]: !cat test.csv
,date_from,date_to
0,20120101,20121231
1,20130101,20131231
2,20140101,20141231
3,20150101,99991231

In [29]: df = read_csv('test.csv',index_col=0)

In [30]: df
Out[30]: 
   date_from   date_to
0   20120101  20121231
1   20130101  20131231
2   20140101  20141231
3   20150101  99991231

In [31]: df.dtypes
Out[31]: 
date_from    int64
date_to      int64
dtype: object

Define a converter that creates a period from an int

In [32]: def conv(x):
    return Period(year=x/10000,month=x/100 % 100, day=x%100,freq='D')
   ....: 

In [33]: converted = df.applymap(conv)

In [34]: converted
Out[34]: 
    date_from     date_to
0  2012-01-01  2012-12-31
1  2013-01-01  2013-12-31
2  2014-01-01  2014-12-31
3  2015-01-01  9999-12-31

In [35]: converted.iloc[3,0]
Out[35]: Period('2015-01-01', 'D')

In [36]: converted.iloc[3,1]
Out[36]: Period('9999-12-31', 'D')

Of course this could be vectorized / more natural, e.g. a to_periods is prob a good idea.

Give it a go!

[CarstVaartjes]

Thanks! It works perfectly like this, I will test HDF5 saving & queries this week too.

I would really add this snippet to http://pandas.pydata.org/pandas-docs/stable/timeseries.html (and yes, to_periods would be a really great idea :)

[jreback]

ok, why don't you open a feature request for to_periods (with as complete doc-string as you can)

[CarstVaartjes]

I've put it on my to do list for later this week (and will unscrupulously copy from to_datetime with the additional Period frequency formats)!

[shoyer]

@jreback I know PeriodIndex is the suggested work around for dates that don't fit in ns precision, but there is a difference between periods and datetimes for meaning as well -- periods refer to a periods of time rather than a time point.

For what it's worth, I've played a bit with np.datetime64 and I don't think nanosecond decision was driven by numpy. Numpy seems to prefer us/microsecond precision (there are a few more bugs with ns precision).

I do recognize that this may be too late to change now but I do think this is something worth considering. I suspect there are a lot more users who would be happy with a fixed choice of "us" rather than "ns" precision. Of course, it would also be a lot of work to actually do the implementation (and it's not the top priority for me).