Pandas replace invalid datetime values with values from other columns

user_12281892 0
Viewed 114

I have the following dataset in which, for some reason, some of the datetimes show up as random ints:

import numpy as np
import pandas as pd

df_show = pd.DataFrame({
    "date": ['11/05/2020', '12/02/2020', '12/02/2020'],
    "time": ['19:14:28', '19:14:28', '19:16:28'],
    "datetime": [np.datetime64('2020-11-05T19:14:28.000000000'), np.datetime64('2020-12-02T19:14:28.000000000'), 12]
})

My goal is to specifically replace those values with a datetime value by combining the date and time cols. I have two approaches, but both fail.

  1. Find the specific values based on the cell type and replaces them with concatenated and converted to datetime type:
df_show.loc[pd.to_datetime(df_show['datetime'], errors='coerce').isnull(), ["datetime"]] = pd.to_datetime(
df_show.loc[pd.to_datetime(df_show['datetime'], errors='coerce').isnull()]["date"].astype(str) + ' ' \
' '+df_show.loc[pd.to_datetime(df_show['datetime'], errors='coerce').isnull()]["time"].astype(str)
    )

However, this returns an int again, which corresponds to the datetime but is int. Why is it happening? Why don't I get the normal datetime[ns] type? And how can I convert it into datetime? Doing

 pd.to_datetime(df_show['datetime'], errors='coerce')

doesn't work since it converts the int into NaT.

  1. First convert the col into datetime, then replace the NaT.
df_show['datetime'] = pd.to_datetime(df_show['datetime'], errors='coerce')
df_show.loc[df_show['datetime'].isnull(), ["datetime"]] = pd.to_datetime(
df_show.loc[df_show['datetime'].isnull()]["date"].astype(str) + ' ' \
' '+df_show.loc[df_show['datetime'].isnull()]["time"].astype(str)
    )

for which I'm getting:

TypeError: cannot astype a datetimelike from [datetime64[ns]] to [float64]

and I don't understand why. Both sides have the same type datetime64[ns] so why this error message? And how do I fix it?

Thanks.

pandas dataframe datetime python
1 Answers

Your second approach is the right direction. To fix it, use .loc[rows, col] instead of .loc[rows, [col]]:

# you want to reuse this
invalid_dates = df_show['datetime'].isnull()

df_show.loc[invalid_dates, "datetime"] = pd.to_datetime(
    df_show.loc[invalid_dates, "date"].astype(str) + ' ' \
    + df_show.loc[invalid_dates, "time"].astype(str)
    )

However, you can just use fillna:

s = pd.to_datetime(df_show[['date','time']].agg(' '.join, axis=1))
df_show['datetime'] = pd.to_datetime(df_show['datetime'], errors='coerce').fillna(s)
user_42384080
Related