[FIXED] Write a code in Python that will help compare two timestamps "dd-mm-yyyy hh:mm"

Issue

How to write a program in Python that will compare two timestamps "dd-mm-yyyy hh:mm", by using operators like > and <, which will return 0 and 1.

As you can see in the above Excel sheet from which I am trying to import I want to compare A2 with C1. For example, if A2>C1 print 1 else 0 - for all rows and print it in C2. But it seems whenever I am trying to achieve it its either printing all 0s or 1s or NaN which are wrong.

Following is the code I have tried:

import pandas as pd
import numpy as np

# Read the Excel sheet into a pandas DataFrame
df = pd.read_excel('MAT_TEST3.xlsx')

# Convert date and time columns to datetime objects using positional index
df.iloc[:, 0] = pd.to_datetime(df.iloc[:, 0], format='%d-%m-%Y %H:%M', errors='coerce')
df.iloc[:, 1] = pd.to_datetime(df.iloc[:, 1], format='%d-%m-%Y %H:%M', errors='coerce')
df.iloc[:, 2] = pd.to_datetime(df.iloc[:, 2], format='%d-%m-%Y %H:%M', errors='coerce')

# Compare dates and times and create a new column at the last position
df.iloc[:, 2] = (df.iloc[:, 0] > df.iloc[:, 2]).astype(int)

print(df)

# Save the modified DataFrame back to Excel
#df.to_excel('MAT_TEST3_OP.xlsx', index=False)

Output -

DEP_DATE_TIME       ARR_DATE_TIME  2024-01-05 03:59:59.545000  \
0 2024-01-05 03:13:00 2024-01-05 11:23:00                           0   
1 2024-01-06 02:35:00 2024-01-06 11:30:00                           0   
2 2024-01-07 02:35:00 2024-01-07 11:30:00                           0   
3 2024-01-05 14:30:00 2024-01-06 00:25:00                           0   
4 2024-01-06 14:30:00 2024-01-07 00:25:00                           0   
5 2024-01-07 14:30:00 2024-01-08 00:25:00                           0   

   2024-01-05 04:59:59.580000  2024-01-05 05:59:59.615000  \
0                         NaN                         NaN   
1                         NaN                         NaN   
2                         NaN                         NaN   
3                         NaN                         NaN   
4                         NaN                         NaN   
5                         NaN                         NaN   

   2024-01-05 06:59:59.650000  
0                         NaN  
1                         NaN  
2                         NaN  
3                         NaN  
4                         NaN  
5                         NaN 

Solution

There's no need to fiddle with .iloc, you can use the column headers directly

Selectively iterating over them, you could do something like this

# convert contents of columns to dates
for col_name in ["DEP_DATE_TIME", "ARR_DATE_TIME"]:
    df[col_name] = pd.to_datetime(df[col_name])

# now slice and iterate over later columns
for col_name in df.columns[2:]:
    # convert column name to datetime
    date_base = pd.to_datetime(col_name)
    df[col_name] = (df["DEP_DATE_TIME"] > date_base).astype(int)

Answered By - ti7