CODEWITHSUNDEEP
pythonpandasdataframenumpydatetime
Zakyl
Zakyl

Reputation: 63

Datetime conversion - convert only date for rows not containing time

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?

enter image description here

UPDATE
From the below result:

enter image description here

I want to check if l_time is NaN and if it is then, I want to apply replace({'00:00':''}) to that row. How can I achieve this?

Upvotes: 0

Views: 123

Answers (3)

Vivs
Vivs

Reputation: 475

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)

Upvotes: 0

Muhammad Hassan
Muhammad Hassan

Reputation: 4229

Use:

df['d_datetime'] = (pd.to_datetime(df['l_date']).dt.strftime("%d/%m/%Y") + ' ' +
                    pd.to_datetime(df['l_time']).dt.time.replace(np.nan, '').astype(str).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 06: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

Upvotes: 1

Zakyl
Zakyl

Reputation: 63

df.loc[df["l_time"].isnull(), "d_datetime"] = df["d_datetime"].replace(
    {"00:00": ""}, regex=True
)

Upvotes: 0

Related Questions