pandas.to_datetime() automatically converting to <M8[ns] and unable to use numpy.isnat()

user_8809728 0
Viewed 6645

I have a dataframe that was read as a string containing a date in the format "YYYY-MM-DD". I had converted the column to datetime using pd.to_datetime (with coerce) and I'm intending to search the column for NaTs using numpy.isnat(). 

defaultDate = datetime.datetime(2020, 12, 31)
df['dates'] = pd.to_datetime(df['dates'], errors = 'coerce')
df['newDates'] = [x if ~np.isnat(x) else defaultDate for x in df['dates']]

When I tried to run the code, I get the error: 

**TypeError**: ufunc 'isnat' is only defined for datetime and timedelta.

I later found out that the dtype of the column had been converted to <M8[ns]. Is there a way to properly to convert to datetime, or is there some way to get around this? I have numpy version 1.16.4.

pandas dataframe numpy python
2 Answers

<M8[ns] is a synonym for datetime64[ns]. Also, you don't need np.isnat if you are dealing with pandas datetime:

defaultDate = pd.to_datetime('2020-12-31')
df['newDates'] = [x if ~np.isnat(x) else defaultDate for x in df['dates']]
df['newDates'] = df['dates'].fillna(defaultDate)
user_42384080

Looks like isnat is meant to test an array like:

In [47]: np.array([0,1,'NaT'], 'datetime64[D]')                                 
Out[47]: array(['1970-01-01', '1970-01-02',        'NaT'], dtype='datetime64[D]')
In [48]: np.isnat(_)                                                            
Out[48]: array([False, False,  True])

I had to experiment to find out how to generate a NaT element. There may be other ways.

Can you give a dataframe or Series that has sample values, both valid dates and non-dates. That will make it easier to explore ways of filtering. I believe pandas has some sort of of not-a-time element, but I don't know if it's compatible with the numpy one. Keep in mind, also, that pandas readily switches to object dtype when Series elements include strings and None.

Testing a Series:

In [50]: ds = pd.Series(_47)                                                    
In [51]: ds                                                                     
Out[51]: 
0   1970-01-01
1   1970-01-02
2          NaT
dtype: datetime64[ns]
In [52]: ds.isna()                                                              
Out[52]: 
0    False
1    False
2     True
dtype: bool
In [54]: ds.isnull()                                                            
Out[54]: 
0    False
1    False
2     True
dtype: bool

Change an element of the Series:

In [58]: ds[2]=12                                                               
In [59]: ds                                                                     
Out[59]: 
0    1970-01-01 00:00:00
1    1970-01-02 00:00:00
2                     12
dtype: object

that changes the dtype

In [60]: ds.values                                                              
Out[60]: 
array([Timestamp('1970-01-01 00:00:00'), Timestamp('1970-01-02 00:00:00'),
       12], dtype=object)
In [61]: np.isnat(_)                                                            
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-61-47ce91c66a51> in <module>
----> 1 np.isnat(_)

TypeError: ufunc 'isnat' is only defined for datetime and timedelta.

A possible conversion sequence:

A series with a mix of dates and something else, object dtype:

In [118]: ds                                                                    
Out[118]: 
0    1970-01-01 00:00:00
1    1970-01-02 00:00:00
2                     12
dtype: object
In [119]: ds1=pd.to_datetime(ds,errors='coerce')                                
In [120]: ds1                                                                   
Out[120]: 
0   1970-01-01
1   1970-01-02
2          NaT
dtype: datetime64[ns]

conversion with coerce produces a NaT:

In [121]: idx = np.isnat(ds1)                                                   
In [122]: idx                                                                   
Out[122]: 
0    False
1    False
2     True
dtype: bool
In [123]: ds1[idx]                                                              
Out[123]: 
2   NaT
dtype: datetime64[ns]

define the correct default; its dtype is important, since pandas changes the dtype readily (numpy does not):

In [124]: default= np.array('2020-12-31','datetime64[ns]')[()]                  
In [125]: default                                                               
Out[125]: numpy.datetime64('2020-12-31T00:00:00.000000000')
In [126]: ds1[idx]=default                                                      
In [127]: ds1                                                                   
Out[127]: 
0   1970-01-01
1   1970-01-02
2   2020-12-31
dtype: datetime64[ns]
user_9019250
Related