Datetime conversion - convert only date for rows not containing time-CodePudding

I have the dataframe

df = pd.DataFrame(
    {
            'ID': ['AB01', 'AB02', 'AB03', 'AB04','AB05', 'AB06'],
            'l_date': ["1/4/2021","1/4/2021",'1/5/2021','1/5/2021','1/8/2021', np.nan],
            'l_time': ["17:05",
                      "6:00","13:43:10","00:00",np.nan,np.nan]
    }
    )

And I want to create a new column which combines l_date and l_time into a datetime column, l_datetime.

My code is this

cols = ['l_date','l_time']
df['d_datetime'] = df[cols].astype(str).agg(' '.join, axis=1)
df['d_datetime'] = df['d_datetime'].replace({'nan':''},  regex=True) 
df['d_datetime'] = pd.to_datetime(df['d_datetime'], errors="coerce").dt.strftime("%d/%m/%Y %H:%M")

Now, this generates time for AB05 as 00:00 and creates the datetime. But for the ones which doesn't time in column l_time, I want the d_datetime to only have the date. How can I achieve this?

Intially I tried

df['d_datetime'] = df['d_datetime'].replace({' 00:00':''},  regex=True)

But this removes the time for AB04 too and I don't want that. How can I achieve the end result looks like below?

CodePudding user response：

Here is the solution

import pandas as pd import numpy as np

df = pd.DataFrame(
    {
            'ID': ['AB01', 'AB02', 'AB03', 'AB04','AB05', 'AB06'],
            'l_date': ["1/4/2021","1/4/2021",'1/5/2021','1/5/2021','1/8/2021', np.nan],
            'l_time': ["17:05",
                      "6:00","13:43:10","00:00",np.nan,np.nan]
    }
    )
df.l_time = df.l_time.fillna('')
df['d_datetime']= df['l_date'].astype(str) df['l_time'].astype(str)
print(df)

CodePudding user response：

Use:

df['d_datetime'] = (pd.to_datetime(df['l_date']).dt.strftime("%d/%m/%Y")   ' '   df['l_time'].replace(np.nan, '').str[0:5]).str.strip()

OUTPUT:

     ID    l_date    l_time        d_datetime
0  AB01  1/4/2021     17:05  04/01/2021 17:05
1  AB02  1/4/2021      6:00   04/01/2021 6:00
2  AB03  1/5/2021  13:43:10  05/01/2021 13:43
3  AB04  1/5/2021     00:00  05/01/2021 00:00
4  AB05  1/8/2021       NaN        08/01/2021
5  AB06       NaN       NaN               NaN