CODEWITHSUNDEEP
pythonpandas
some_programmer
some_programmer

Reputation: 3538

How to replace NaTs in a date column with dates in a pandas series?

I have a df as follows:

dates   values
2020-12-31 23:45:00 75.6
2021-01-01 00:00:00 74.7
 NaT                73.7
 NaT                72.3
 NaT                70.5
 NaT                68.2

I want to replace the NaT values with a series I made as follows:

oct_two_am = datetime.datetime(2020, 10, 25, 2, 0)
oct_three_am = datetime.datetime(2020, 10, 25, 3, 0)

# generates 15mins data
oct_dls = pd.date_range(start=oct_two_am, end=oct_three_am, freq='15min', closed='left')
oct_dls = oct_dls.to_series().reset_index(drop=True)

This gave me the following series oct_dls:

oct_dls
Out[26]: 
0   2020-10-25 02:00:00
1   2020-10-25 02:15:00
2   2020-10-25 02:30:00
3   2020-10-25 02:45:00
dtype: datetime64[ns]

Now, I want to replace the NaTs in my dataframe df, with the values in the series oct_dls

I tried:

df['dates'] = df['dates'].fillna(value=oct_dls)

But it doesn't work.

Upvotes: 2

Views: 429

Answers (2)

jezrael
jezrael

Reputation: 862791

If length of missing values match number of new values of DatetimeIndex use DataFrame.loc with filtering by Series.isna, then solution is simplify:

oct_two_am = datetime.datetime(2020, 10, 25, 2, 0)
oct_three_am = datetime.datetime(2020, 10, 25, 3, 0)

# generates 15mins data
oct_dls = pd.date_range(start=oct_two_am, end=oct_three_am, freq='15min', closed='left')

df.loc[df['dates'].isna(), 'dates'] = oct_dls
print (df)
                dates  values
0 2020-12-31 23:45:00    75.6
1 2021-01-01 00:00:00    74.7
2 2020-10-25 02:00:00    73.7
3 2020-10-25 02:15:00    72.3
4 2020-10-25 02:30:00    70.5
5 2020-10-25 02:45:00    68.2

Upvotes: 4

BENY
BENY

Reputation: 323306

Since your index dose not match , that is why not work to fill the null value.

index match is one of the hidden key in pandas assignment. 

oct_dls.index=df.index[df['dates'].isnull()]
df['dates'] = df['dates'].fillna(value=oct_dls)

Upvotes: 5

Related Questions