Pandas DataFrame Replace NaT with None

Question

I have been struggling with this question for a long while, and I tried different methods.

I have a simple DataFrame as shown,

I can use code to replace NaN with None (Not String "None"),

[![dfTest2 = dfTest.where(pd.notnull(dfTest), None)][2]][2]

I support that NaT is also classified as 'Null' because the following,

However, NaT is not replaced with None.

I have been searching for answers but got no luck. Anyone could Help?

Thank you in advance.

@Cleb, tried but not won't work. Exception as `ValueError: must specify a fill method or value`. Seems that `None` is either an value or method. — Haipeng Su, Mar 15 '17 at 18:52
[This might be of help](http://stackoverflow.com/questions/17097236/how-to-replace-values-with-none-in-pandas-data-frame-in-python). — Cleb, Mar 15 '17 at 19:02
@Cleb Hi, Thx for the reference. Please see the answer below. — Haipeng Su, Mar 17 '17 at 20:15
https://stackoverflow.com/a/42818550/8770481 This one saved my life. It worked well for me — Nguyễn Danh Nam, Mar 26 '20 at 08:57

score 43 · Accepted Answer · answered Mar 15 '17 at 19:02

43

Make the dtype object

dfTest2 = pd.DataFrame(dict(InvoiceDate=pd.to_datetime(['2017-06-01', pd.NaT])))

dfTest2.InvoiceDate.astype(object).where(dfTest2.InvoiceDate.notnull(), None)

0    2017-06-01 00:00:00
1                   None
Name: InvoiceDate, dtype: object

answered Mar 15 '17 at 19:02

piRSquared

265,629
48
427
571

1

Amazing!!! Thank you so so much. Totally make sense, the Timestamp type gives default NaT for Null. Thx again. – Haipeng Su Mar 15 '17 at 21:12
4

I don't wanna change the type of object and want it only in datetime format so this solution isn't working for me. – renny May 06 '19 at 06:16
Your code gives me only the column affected as a result. But I want this to happen in in the DF itself and the original column replaced by this new one with None's. How to do that? – Aakash Basu Apr 30 '20 at 07:09
Assign the result back to the dataframe – piRSquared Apr 30 '20 at 07:31

score 25 · Answer 2 · edited Aug 10 '21 at 18:42

25

The simplest solution I found that worked for me is...

Input:

import pandas as pd
import numpy as np
dfTest = pd.DataFrame(dict(InvoiceDate=pd.to_datetime(['2017-06-01', pd.NaT]), CorpId=[2997373, np.nan], TestName=[1,1]))
dfTest.replace({np.nan: None}, inplace = True)

Output of dfTest:

edited Aug 10 '21 at 18:42

LucyDrops

389
3
11

answered Jan 17 '20 at 19:51

dshefman

777
8
17

Note that `pd.np` was removed in Pandas 1.x.x – Snake Verde Nov 11 '20 at 10:22
1

This also changes the column type to object – B. Bogart Jun 25 '21 at 13:35

score 4 · Answer 3 · edited Jun 17 '19 at 09:44

4

Make the column type as str first

 dfTest2.InvoiceDate =  dfTest2.InvoiceDate.astype(str)

then compare it directly with "NaT" and replace with None

dfTest2.InvoiceDate = dfTest2.InvoiceDate.apply(lambda x : None if x=="NaT" else x)

edited Jun 17 '19 at 09:44

Rahul Agarwal

3,940
7
26
47

answered Apr 07 '19 at 12:32

Neeraj Yadav

79
3

score 2 · Answer 4 · answered Nov 11 '20 at 10:21

2

Similar approach as suggested by @neerajYadav but without the apply:

dfTest2['InvoiceDate'] = (dfTest2['InvoiceDate']
                          .astype(str) # <- cast to string to simplify
                                       #    .replace() in newer versions
                          .replace({'NaT': None} # <- replace with None
                         )

answered Nov 11 '20 at 10:21

Snake Verde

545
2
10

This will change the column type to str, which in some cases we don't want that to happen – knl Apr 18 '22 at 07:10

score 0 · Answer 5 · answered May 28 '22 at 11:15

0

If you don't want to change the type of the column, then another alternative is to to replace all missing values (pd.NaT) first with np.nan and then replace the latter with None:

import numpy as np

df = df.fillna(np.nan).replace([np.nan], [None])

answered May 28 '22 at 11:15

Giorgos Myrianthous

30,279
17
114
133

Pandas DataFrame Replace NaT with None

5 Answers5

Linked