CODEWITHSUNDEEP
pythonpython-3.xpandasdataframetimestamp
user14073111
user14073111

Reputation: 621

Compare two columns that contains timestamps in pandas

Lets say I have a dataframe like this one:

  Col0       Col1                    Col2                   Col3                   Col4
   1.txt  2021-06-23 15:04:30   2021-06-23 14:10:30   2021-06-23 14:15:30   2021-06-23 14:20:30
   2.txt  2021-06-23 14:25:30   2021-06-23 15:30:30   2021-06-23 14:35:30   2021-06-23 14:40:30

I want to compare if the timestamp in Col1 is greater than in Col2 and if that is true I want to remove the timestamps from the other columns (Col2, Col3, Col4). I also want to check if timestamp in Col2 is greater than in Col3 and if that is true I want to remove timestamp from other columns Col3, Col4).

I tried this one:

df['Col1'] = pd.to_datetime(df['Col1'])
df['Col2'] = pd.to_datetime(df['Col2'])
df['Col3'] = pd.to_datetime(df['Col3'])
k= (df['Col1'] > df['Col2']).astype(int)
p=(df['Col2'] > df['Col3']).astype(int)

if k>0:
    df['Col2']=np.nan
    df['Col3']=np.nan
    df['Col4']=np.nan
elif p>0:
    df['Col3']=np.nan
    df['Col4']=np.nan

But it is showing me this error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

My desirable output would look like this:

  Col0       Col1                    Col2               Col3                   Col4
   1.txt  2021-06-23 15:04:30        NaN                 NaN                    NaN
   2.txt  2021-06-23 14:25:30   2021-06-23 15:30:30      NaN                    NaN

EDITED: Added Col0

Upvotes: 0

Views: 811

Answers (2)

user14073111
user14073111

Reputation: 621

I tried this one and got the output that i wanted (when a dataframe contains also other columns with 'str' and 'float'):

df['Col1'] = pd.to_datetime(df['Col1'])
df['Col2'] = pd.to_datetime(df['Col2'])
df['Col3'] = pd.to_datetime(df['Col3'])
df.loc[df['Col1'] > df['Col2'], 'Col2'] = np.nan
df.loc[df['Col1'] > df['Col2'], 'Col3'] = np.nan
df.loc[df['Col1'] > df['Col2'], 'Col4'] = np.nan


df.loc[df['Col2'] > df['Col3'], 'Col3'] = np.nan
df.loc[df['Col2'] > df['Col3'], 'Col4'] = np.nan

Upvotes: 0

Corralien
Corralien

Reputation: 120559

A straightforward way with boolean mask:

dt = df.select_dtypes('datetime')
dt = dt.mask(dt.lt(dt.shift(axis=1)).cumsum(axis=1).astype(bool))

df.loc[:, dt.columns.tolist()] = dt

>>> df
    Col0                Col1                Col2 Col3 Col4
0  1.txt 2021-06-23 15:04:30                 NaT  NaT  NaT
1  2.txt 2021-06-23 14:25:30 2021-06-23 15:30:30  NaT  NaT

Upvotes: 1

Related Questions