Python pandas - Replace NaN values of column by mean of two datetime64[ns] columns

user_16432438 0
Viewed 108

I'm facing problem while calculating the mean of 2 datetime64[ns] columns.

The dataframe looks like:

data ={
        'time1' :['2019-05-21 08:29:55','2019-10-07 17:43:09','2020-12-13 21:53:00','2018-04-17 16:51:23','2016-08-31 17:40:49'],
        'time2':['2019-05-21 09:29:40', '2019-10-07 19:42:50', '2020-12-13 22:44:00', '2018-04-17 17:50:46', '2016-08-31 18:10:49'],
        'Avg_time[(time1+time2)/2]':[np.NaN,np.NaN,np.NaN,np.NaN,np.NaN]
      }
df =pd.DataFrame(data)
df

Output:

          time1                time2            Avg_time[(time1+time2)/2]
0   2019-05-21 08:29:55  2019-05-21 09:29:40         NaN
1   2019-10-07 17:43:09  2019-10-07 19:42:50         NaN
2   2020-12-13 21:53:00  2020-12-13 22:44:00         NaN
3   2018-04-17 16:51:23  2018-04-17 17:50:46         NaN
4   2016-08-31 17:40:49  2016-08-31 18:10:49         NaN

I want the NaN values of column Avg_time[(time1+time2)/2] to be replaced by the mean of the columns time1 and time2.

Note: The type of columns time1 and time2 is datetime64[ns] (can be converted using to_datetime()).

pandas nan timestamp datetime python
1 Answers

You can convert datetimes to native format ns by converting by DataFrame.to_numpy and casting to np.int64, then converting to mean and last back to datetimes and replace missing values by Series.fillna:

df['time1'] = pd.to_datetime(df['time1'])
df['time2'] = pd.to_datetime(df['time2'])

arr = df[['time1','time2']].to_numpy().astype(np.int64).mean(axis=1)
df['Avg_time'] = df['Avg_time'].fillna(pd.Series(pd.to_datetime(arr), index=df.index))
print (df)
                time1               time2                Avg_time
0 2019-05-21 08:29:55 2019-05-21 09:29:40 2019-05-21 08:59:47.500
1 2019-10-07 17:43:09 2019-10-07 19:42:50 2019-10-07 18:42:59.500
2 2020-12-13 21:53:00 2020-12-13 22:44:00 2020-12-13 22:18:30.000
3 2018-04-17 16:51:23 2018-04-17 17:50:46 2018-04-17 17:21:04.500
4 2016-08-31 17:40:49 2016-08-31 18:10:49 2016-08-31 17:55:49.000

Alternative:

df['time1'] = pd.to_datetime(df['time1'])
df['time2'] = pd.to_datetime(df['time2'])

t1 = df['time1'].to_numpy().astype(np.int64)
t2 = df['time2'].to_numpy().astype(np.int64)
df['Avg_time'] = df['Avg_time'].fillna(pd.Series((t1 + t2) / 2, index=df.index))
user_29010020
Related