CODEWITHSUNDEEP
python-2.7pandasdataframe
Haipeng Su
Haipeng Su

Reputation: 2541

Pandas DataFrame Replace NaT with None

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

I have a simple DataFrame as shown,

enter image description here

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

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

enter image description here

I support that NaT is also classified as 'Null' because the following, enter image description here

However, NaT is not replaced with None.

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

Thank you in advance.

Upvotes: 52

Views: 122284

Answers (11)

Mohamed Emad
Mohamed Emad

Reputation: 21

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

Upvotes: 0

bwan1011
bwan1011

Reputation: 75

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})

`

Upvotes: 2

Dmitry
Dmitry

Reputation: 41

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

Upvotes: 0

Rexovas
Rexovas

Reputation: 478

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

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

Upvotes: 0

Manish Jindal
Manish Jindal

Reputation: 129

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

Upvotes: 0

Jane Kathambi
Jane Kathambi

Reputation: 935

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)

Upvotes: 8

Giorgos Myrianthous
Giorgos Myrianthous

Reputation: 39850

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])

Upvotes: -1

dshefman
dshefman

Reputation: 1007

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:

enter image description here

Upvotes: 35

Snake Verde
Snake Verde

Reputation: 634

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
                         )

Upvotes: 2

Neeraj Yadav
Neeraj Yadav

Reputation: 99

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)

Upvotes: 5

piRSquared
piRSquared

Reputation: 294358

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

Upvotes: 52

Related Questions