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 — namnd_briangryffindor, Mar 26 '20 at 08:57

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

49

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

285,575
57
475
624

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 33 · Answer 2 · edited Aug 10 '21 at 18:42

33

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

539
5
15

answered Jan 17 '20 at 19:51

dshefman

937
9
19

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 5 · Answer 3 · edited Jun 17 '19 at 09:44

5

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

4,034
7
27
51

answered Apr 07 '19 at 12:32

Neeraj Yadav

89
1
3

score 3 · Answer 4 · answered Jun 21 '22 at 09:07

3

df.fillna(None) only works for np.na but not pd.NaT. However doing df.replace({np.nan: None}) replaces both pd.NaT and np.na with None.

# Initalize a sample dataframe
df = pd.DataFrame({
                    'start_date': pd.to_datetime(['2017-06-01', pd.NaT]), 
                    'amount':[2997373, np.nan]
                   })
display(df)

# Then replace pd.NaT and np.na with None
df = df.replace({np.nan: None})
display(df)

answered Jun 21 '22 at 09:07

Jane Kathambi

695
6
8

1

Thank you, this is the one that finally did it for me! Interestingly, `df.replace(np.nan, None)` has the same effect as `df.fillna(None)`, but converting the `replace` call to a dict apparently makes all the difference. – jshrimp29 May 11 '23 at 23:21

score 2 · Answer 5 · 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

604
4
12

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 6 · answered Oct 08 '22 at 15:46

0

This looks strange but worked for me. Pandas version 14.1

import numpy as np

df = df.replace(np.NaN, 0).replace(0, None)

Before LastModifiedDate NaT

After LastModifiedDate None

answered Oct 08 '22 at 15:46

Manish Jindal

109
1
8

This cannot be correct. When `to_replace=` is a scalar and `value=` is `None` (as in your second `.replace()` call above), the function uses the `method=` parameter (see [docs](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.replace.html) and [this](https://github.com/pandas-dev/pandas/issues/17494#issuecomment-328966324) Github issue comment), which means forward-filling last non-null (and non-0 in your case). – AlexK Oct 13 '22 at 06:28

score 0 · Answer 7 · answered May 14 '23 at 06:54

0

I see a couple of other similar answers here, though none are as simple as this:

df.replace([pd.NaT], [None])

answered May 14 '23 at 06:54

Rexovas

469
2
9

score 0 · Answer 8 · answered Jun 30 '23 at 17:28

0

dfTest2.replace({pd.NaT:None},inplace=True)

answered Jun 30 '23 at 17:28

Dmitry

31
3

score -1 · Answer 9 · answered May 28 '22 at 11:15

-1

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

36,235
20
134
156

1

`df.fillna(np.nan)` does not replace `NaT` with `nan`. – Joel Aufrecht Oct 23 '22 at 23:11
why not just `df.replace([pd.NaT], [None])` – Rexovas May 14 '23 at 06:54

Pandas DataFrame Replace NaT with None

9 Answers9

Linked