Diff on datetime64 column evoking "ValueError: Could not convert object to NumPy datetime"

[jorisvandenbossche]

With pandas 0.10.1 and numpy 1.7.0 (Anaconda 1.4.0), I get a ValueError when trying to use the diff method on a datetime64 column or series.

Eg:

In [3]: t = np.arange(start='2012-01-01 00:00:01', stop='2012-01-01 00:00:10', d
type='datetime64')

In [4]: df = pd.DataFrame(np.arange(len(t)))

In [5]: df['time'] = t

Using the diff method gives an error:

In [6]: df['time'].diff()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-6-83fe0aa0c1ed> in <module>()
----> 1 df['time'].diff()

C:\Anaconda\lib\site-packages\pandas\core\series.pyc in diff(self, periods)
   1736         diffed : Series
   1737         """
-> 1738         result = com.diff(self.values, periods)
   1739         return Series(result, self.index, name=self.name)
   1740

C:\Anaconda\lib\site-packages\pandas\core\common.pyc in diff(arr, n, axis)
    475     na_indexer = [slice(None)] * arr.ndim
    476     na_indexer[axis] = slice(None, n) if n >= 0 else slice(n, None)
--> 477     out_arr[tuple(na_indexer)] = np.nan
    478
    479     if arr.ndim == 2 and arr.dtype.name in _diff_special:

ValueError: Could not convert object to NumPy datetime

while the numpy diff works as expected:

In [7]: np.diff(df['time'].values)
Out[7]:
array([1000000000, 1000000000, 1000000000, 1000000000, 1000000000,
       1000000000, 1000000000, 1000000000], dtype='timedelta64[ns]')

The problems seems to be that np.nan cannot be converted to a numpy datetime64.

My code also did work with a previously version of numpy and/or pandas (but am not certain wich version. Before I updated my scientific stack with anaconda).

[jreback]

this will work in 0.11, see http://pandas.pydata.org/pandas-docs/dev/timeseries.html#time-deltas
note - diff itself is a bug right now, but you can do: s-s.shift(1)

[jorisvandenbossche]

Thanks for the quick reply.

However, in 0.10 the shift approach invokes the same error of trying to set np.nan to a numpy datetime64:

df['time'] - df['time'].shift(1)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-38-11de14805d34> in <module>()
----> 1 df['time'] - df['time'].shift(1)

C:\Anaconda\lib\site-packages\pandas\core\series.pyc in shift(self, periods, freq, copy, **kwds)
   2810             if periods > 0:
   2811                 new_values[periods:] = self.values[:-periods]
-> 2812                 new_values[:periods] = nan
   2813             elif periods < 0:
   2814                 new_values[:periods] = self.values[-periods:]

ValueError: Could not convert object to NumPy datetime

So I will have to use the numpy workaround until 0.11 is released.

[jreback]

this is broken in 0.10.1, try out 0.11-dev

you MIGHT be able to do this in 0.10.1

s = Series(df['time'],dtype=object) s.diff()

[jreback]

This works in 0.10.1

In [17]: df = pd.DataFrame(dict(time = pd.date_range('20130102',periods=5)))

In [19]: df['time'].astype('object').diff()
Out[19]: 
0               NaN
1    1 day, 0:00:00
2    1 day, 0:00:00
3    1 day, 0:00:00
4    1 day, 0:00:00
Name: time

In [20]: df['time'].astype('object').diff().dtype
Out[20]: dtype('object')

In [21]: df['time'].astype('object').diff()[1]
Out[21]: datetime.timedelta(1)

[jreback]

is this still an issue?

[jorisvandenbossche]

Sorry, no. Since you say it will work in 0.11, I suppose it is already solved.
For now (0.10) the numpy or astype(object) workaround will do it.

[vfilimonov]

It is still an issue on pandas 0.11.0 and NumPy 1.7.1

However s-s.shift(1) instead of s.diff() works well

[jreback]

was fixed for 0.11.1 in #3100