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
– Haipeng Su, Commented Mar 15, 2017 at 18:52
@Cleb Hi, Thx for the reference. Please see the answer below. — Haipeng Su
– Haipeng Su, Commented Mar 17, 2017 at 20:15
stackoverflow.com/a/42818550/8770481 This one saved my life. It worked well for me — namnd_briangryffindor
– namnd_briangryffindor, Commented Mar 26, 2020 at 8:57

piRSquared · Accepted Answer · 2017-03-15 19:02:23Z

52

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, 2017 at 19:02

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Haipeng Su Over a year ago

Amazing!!! Thank you so so much. Totally make sense, the Timestamp type gives default NaT for Null. Thx again.

renny Over a year ago

I don't wanna change the type of object and want it only in datetime format so this solution isn't working for me.

Aakash Basu Over a year ago

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?

piRSquared Over a year ago

Assign the result back to the dataframe

LucyDrops · Accepted Answer · 2021-08-10 18:42:18Z

36

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, 2021 at 18:42

LucyDrops

6097 silver badges15 bronze badges

answered Jan 17, 2020 at 19:51

dshefman

1,03711 silver badges20 bronze badges

2 Comments

Snake Verde Over a year ago

Note that pd.np was removed in Pandas 1.x.x

B. Bogart Over a year ago

This also changes the column type to object

Jane Kathambi · Accepted Answer · 2022-06-21 09:07:04Z

9

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, 2022 at 9:07

Jane Kathambi

96510 silver badges10 bronze badges

2 Comments

jshrimp29 Over a year ago

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.

Ali Hasan Over a year ago

I was mad why not working for me, I was using pandas version 1.4. Upgraded to 1.5 or latest it worked!

Rahul Agarwal · Accepted Answer · 2019-06-17 09:44:59Z

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, 2019 at 9:44

Rahul Agarwal

4,1168 gold badges33 silver badges56 bronze badges

answered Apr 7, 2019 at 12:32

Neeraj Yadav

991 silver badge3 bronze badges

Comments

Snake Verde · Accepted Answer · 2020-11-11 10:21:32Z

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, 2020 at 10:21

Snake Verde

6344 silver badges13 bronze badges

1 Comment

knl Over a year ago

This will change the column type to str, which in some cases we don't want that to happen

bwan1011 · Accepted Answer · 2023-09-29 11:46:40Z

2

So I'm a little late to the party, but this result also worked for me.

`

import pandas as pd

df["column"] = df["column"].replace({pd.NaT: None})

`

edited Sep 29, 2023 at 11:46

answered Sep 29, 2023 at 11:37

bwan1011

7511 bronze badges

Comments

Manish Jindal · Accepted Answer · 2022-10-08 15:46:38Z

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 8, 2022 at 15:46

Manish Jindal

1291 silver badge10 bronze badges

1 Comment

AlexK Over a year ago

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 and this Github issue comment), which means forward-filling last non-null (and non-0 in your case).

Rexovas · Accepted Answer · 2023-05-14 06:54:12Z

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, 2023 at 6:54

Rexovas

4782 silver badges9 bronze badges

Comments

Dmitry · Accepted Answer · 2023-06-30 17:28:25Z

0

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

answered Jun 30, 2023 at 17:28

Dmitry

513 bronze badges

Comments

Mohamed Emad · Accepted Answer · 2024-04-23 20:29:35Z

0

df = df.astype(object).mask(df.isna(), None)

answered Apr 23, 2024 at 20:29

Mohamed Emad

213 bronze badges

Comments

Giorgos Myrianthous · Accepted Answer · 2022-05-28 11:15:42Z

-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, 2022 at 11:15

Giorgos Myrianthous

40.4k21 gold badges156 silver badges175 bronze badges

2 Comments

Joel Aufrecht Over a year ago

df.fillna(np.nan) does not replace NaT with nan.

Rexovas Over a year ago

why not just df.replace([pd.NaT], [None])

Collectives™ on Stack Overflow

Pandas DataFrame Replace NaT with None

11 Answers 11

4 Comments

2 Comments

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

4 Comments

2 Comments

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related