Datatime out of a date, hour and minute column where NaNs are present (pandas). Is there a general solution to manage such data?

user_10750541 0
Viewed 316

I am having some trouble managing and combining columns in order to get one datetime column out of three columns containing the date, the hours and the minutes.

Assume the following df (copy and type df= = pd.read_clipboard() to reproduce) with the types as noted below:

>>>df
         date  hour  minute
0  2021-01-01   7.0    15.0
1  2021-01-02   3.0    30.0
2  2021-01-02   NaN     NaN
3  2021-01-03   9.0     0.0
4  2021-01-04   4.0    45.0

>>>df.dtypes
date       object
hour      float64
minute    float64
dtype: object

I want to replace the three columns with one called 'datetime' and I have tried a few things but I face the following problems:

  1. I first create a 'time' column df['time']= (pd.to_datetime(df['hour'], unit='h') + pd.to_timedelta(df['minute'], unit='m')).dt.time and then I try to concatenate it with the 'date' df['datetime']= df['date'] + ' ' + df['time'] (with the purpose of converting the 'datetime' column pd.to_datetime(df['datetime']). However, I get

    TypeError: can only concatenate str (not "datetime.time") to str

  2. If I convert 'hour' and 'minute' to str to concatenate the three columns to 'datetime', then I face the problem with the NaN values, which prevents me from converting the 'datetime' to the corresponding type.

  3. I have also tried to first convert the 'date' column df['date']= df['date'].astype('datetime64[ns]') and again create the 'time' column df['time']= (pd.to_datetime(df['hour'], unit='h') + pd.to_timedelta(df['minute'], unit='m')).dt.time to combine the two: df['datetime']= pd.datetime.combine(df['date'],df['time']) and it returns

    TypeError: combine() argument 1 must be datetime.date, not Series along with the warning

    FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime module instead.

Is there a generic solution to combine the three columns and ignore the NaN values (assume it could return 00:00:00).

What if I have a row with all NaN values? Would it possible to ignore all NaNs and 'datetime' be NaN for this row?

Thank you in advance, ^_^

pandas datetime python
2 Answers

First convert date to datetimes and then add hour and minutes timedeltas with replace missing values to 0 timedelta:

td = pd.Timedelta(0)
df['datetime'] = (pd.to_datetime(df['date']) + 
                  pd.to_timedelta(df['hour'], unit='h').fillna(td) + 
                  pd.to_timedelta(df['minute'], unit='m').fillna(td))

print (df)
         date  hour  minute            datetime
0  2021-01-01   7.0    15.0 2021-01-01 07:15:00
1  2021-01-02   3.0    30.0 2021-01-02 03:30:00
2  2021-01-02   NaN     NaN 2021-01-02 00:00:00
3  2021-01-03   9.0     0.0 2021-01-03 09:00:00
4  2021-01-04   4.0    45.0 2021-01-04 04:45:00

Or you can use Series.add with fill_value=0:

df['datetime'] = (pd.to_datetime(df['date'])
                    .add(pd.to_timedelta(df['hour'], unit='h'), fill_value=0) 
                    .add(pd.to_timedelta(df['minute'], unit='m'), fill_value=0))
user_29010020

I would recommend converting hour and minute columns to string and constructing the datetime string from the provided components.

Logically, you need to perform the following steps:

Step 1. Fill missing values for hour and minute with zeros.

df['hour'] = df['hour'].fillna(0)
df['minute'] = df['minute'].fillna(0)

Step 2. Convert float values for hour and minute into integer ones, because your final output should look like 2021-01-01 7:15, not 2021-01-01 7.0:15.0.

df['hour'] = df['hour'].astype(int)
df['minute'] = df['minute'].astype(int)

Step 3. Convert integer values for hour and minute to the string representation.

df['hour'] = df['hour'].astype(str)
df['minute'] = df['minute'].astype(str)

Step 4. Concatenate date, hour and minute into one column of the correct format.

df['result'] = df['date'].str.cat(df['hour'].str.cat(df['minute'], sep=':'), sep=' ')

Step 5. Convert your result column to datetime object.

pd.to_datetime(df['result'])

It is also possible to fullfill all of this steps in one command, though it will read a bit messy:

df['result'] = pd.to_datetime(df['date'].str.cat(df['hour'].fillna(0).astype(int).astype(str).str.cat(df['minute'].fillna(0).astype(int).astype(str), sep=':'), sep=' '))

Result:

   date         hour  minute              result
0  2020-01-01   7.0    15.0 2020-01-01 07:15:00
1  2020-01-02   3.0    30.0 2020-01-02 03:30:00
2  2020-01-02   NaN     NaN 2020-01-02 00:00:00
3  2020-01-03   9.0     0.0 2020-01-03 09:00:00
4  2020-01-04   4.0    45.0 2020-01-04 04:45:00
user_52245910
Related