how can I replace NaT from a dataframe with a date/variable that was created (setup) before?
I got the following dataframe:
Date Name CF
0 NaT Peter -10
0 2017-12-14 Peter -20
1 NaT Tomas -5.5
1 2017-12-15 Peter -25
2 NaT Tomas -3
2 2017-12-14 Tomas -5
3 NaT Walker -4.6
3 2017-12-15 Tomas 88
4 2017-12-15 Tomas -30
5 2017-12-15 Walker 15
I would like to replace NaT with a variable that was setup at the beginning of my Project: anEnd variable like this:
format_date = "%d-%m-%Y"
end = datetime.date(2017, 12, 15)
I tried two approaches: First, I used np.where from Pandas, but it does not recognize the NaT. Second, I tried this
test = table_final2.Date.astype(object).where(table_final2.Date.notnull(), end)
but this results in a mix of formats for the column Date
Date Name CF
0 2017-12-15 Peter -10
0 2017-12-14 00:00:00 Peter -20
1 2017-12-15 Tomas -5.5
1 2017-12-15 00:00:00 Peter -25
2 2017-12-15 Tomas -3
2 2017-12-14 00:00:00 Tomas -5
3 2017-12-15 Walker -4.6
3 2017-12-15 00:00:00 Tomas 88
4 2017-12-15 00:00:00 Tomas -30
5 2017-12-15 00:00:00 Walker 15
So, how can I replace NaT accurately? Thanks in advance!
PD: when test.dtypes shows Date as an object.
IIUC you can use Series.fillna() method:
In [138]: end = pd.to_datetime('2017-12-15')
In [139]: df['Date'] = df['Date'].fillna(end)
In [140]: df
Out[140]:
Date Name CF
0 2017-12-15 Peter -10.0
0 2017-12-14 Peter -20.0
1 2017-12-15 Tomas -5.5
1 2017-12-15 Peter -25.0
2 2017-12-15 Tomas -3.0
2 2017-12-14 Tomas -5.0
3 2017-12-15 Walker -4.6
3 2017-12-15 Tomas 88.0
4 2017-12-15 Tomas -30.0
5 2017-12-15 Walker 15.0