CODEWITHSUNDEEP
pythonpandas
Stacey
Stacey

Reputation: 5107

Change NaT to blank in pandas dataframe

I have a dataframe (df) that looks like:

        DATES
0         NaT
1  01/08/2003
2         NaT
3         NaT
4  04/08/2003
5         NaT
6  30/06/2003
7  01/03/2004
8  18/05/2003
9         NaT
10        NaT
11 31/10/2003
12        NaT
13        NaT

I am struggling to find out how I transform the data-frame to remove the NaT values so the final output looks like

        DATES
0         
1  01/08/2003
2         
3         
4  04/08/2003
5         
6  30/06/2003
7  01/03/2004
8  18/05/2003
9         
10        
11 31/10/2003
12        
13

I have tried :

df["DATES"].fillna("", inplace = True)

but with no success.

For information the column is in a datatime format set with

df["DATES"] = pd.to_datetime(df["DATES"],errors='coerce').dt.strftime('%d/%m/%Y')

What can I do to resolve this?

Upvotes: 5

Views: 15171

Answers (4)

UserOnWeb
UserOnWeb

Reputation: 98

df.fillna() works on numpy.NaN values. Your "Nat" are probably strings. So you can do following,

if you want to use fillna()

df["DATES"].replace("NaT",np.NaN, inplace=True)
df.fillna("", inplace=True)

Else, you can just replace with your desired string

df["DATES"].replace("NaT","", inplace=True)

Upvotes: 1

Itamar Mushkin
Itamar Mushkin

Reputation: 2905

Your conversion to datetime did not work properly on the NaTs.
You can check this before calling the fillna by printing out df['DATES'][0] and seeing that you get a 'NaT' (string) and not NaT (your wanted format)

Instead, use (for example): df['DATES'] = df['DATES'].apply(pd.Timestamp)

This example worked for me as is, but notice that it's not datetime but rather pd.Timestamp (it's another time format, but it's an easy one to use). You do not need to specify your time format with this, your current format is understood by pd.Timestamp.

Upvotes: 0

Mdev
Mdev

Reputation: 463

Convert column to object and then use Series.where:

df['Dates'] = df['Dates'].astype(object).where(df['Date'].notnull(),np.nan)

Or whatever you want np.nan to be

Upvotes: 0

jezrael
jezrael

Reputation: 862901

There is problem NaT are strings, so need:

df["DATES"] = df["DATES"].replace('NaT', '')

Upvotes: 8

Related Questions