Pandas Replace 0 values with NaT

Question

I have a dateframe with a column named "date" which contains date in format :year month date. Some of the months and dates are with zero value, meaning that these dates are not valid, so I need to replace data for these values with NaT (not a time". I tried the following :

df["date"] = df["date"].replace(0, np.nan), also tried: df["date"] = df["date"].replace({'0':np.nan, 0:np.nan}) also : df["date"] = df["date"].replace(['0', 0], np.nan)

But none of the above is working. still have data like : 1970 0 0 1970 1 0 etc...

Answer 1

Use pd.to_datetime with option errors='coerce'.

Sample series s:

Out[31]:
0    1970 0 0
1    1970 1 1
2    1970 1 0
dtype: object

s_out = pd.to_datetime(s, errors='coerce')

In [33]: s_out
Out[33]:
0          NaT
1   1970-01-01
2          NaT
dtype: datetime64[ns]