Comparing date column of the csv file to today date

Question

I need to compare my large data's date with today date to calculate another column value if the date is more than or less than today.

My code is as below however I'm keep getting error!

I have tried Timestamp.now() but my python 3.6 can not recognise it.

import datetime
def midspread_calcs():
    for row in df_midspread:
        if df_midspread['Date']<datetime.datetime.now():
           df_midspread['Midspread']= df_midspread.Oldrate*df_midspread.Value
        else:
           df_midspread['Midspread']= df_midspread.Newrate*df_midspread.Value 

result = midspread_calcs()

Please provide the error that you are getting. Also please check if db_midspread['Date'] is in date format. — Anupam Chaplot, Feb 05 '20 at 06:37
this is the latest Error that I'm getting : ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). — Z.A, Feb 05 '20 at 11:29

score 0 · Answer 1 · answered Feb 05 '20 at 06:42

0

Check if db_midspread['Date'] is in the correct date time format. your logic is correct

if its not use the following code : The easiest way is to use to_datetime:

db_midspread['Date'] = pd.to_datetime(db_midspread['Date'])```

answered Feb 05 '20 at 06:42

Thivya Thogesan

206
2
12

Thank you for the comment however i have already tried this one ! didn't work ! – Z.A Feb 05 '20 at 11:15

score 0 · Answer 2 · answered Feb 05 '20 at 06:45

0

Some of mighty problems in the first sight:

1- Your for loop is misnaming

    for row in df_midspread:
        if row['Date']<datetime.datetime.now():
           row['Midspread']= row.Oldrate*row.Value
        else:
           row['Midspread']= row.Newrate*row.Value

2- You called your function and assign it to result but your function does not have a return.

3- You should make sure df_midspread['Date'] is a datetime.

answered Feb 05 '20 at 06:45

Iron Hand Odin

410
4
10

Many thanks for pointing to all issues ! I took the function and return off and there's still the same issue ! it sounds my data frame date column is still recognised as string even though i have used the below : df_midspread['Date'] = pd.to_datetime(df_midspread['Date], format='%Y-%m-%d %H:%M:%S') – Z.A Feb 05 '20 at 11:18
and this is the Error :ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). Any Thought ? – Z.A Feb 05 '20 at 11:30
can you provide a sample data so i can reproduce the errors. – Iron Hand Odin Feb 05 '20 at 12:38
I have provided an image of the dummy data if it helps ! thanks for your time – Z.A Feb 05 '20 at 22:43
thanks for your notes! inspired me to solve the issue :) – Z.A Feb 05 '20 at 23:05

score 0 · Accepted Answer · answered Feb 05 '20 at 23:04

Thanks for your advice,It works now :) for row in df_midspread['Date']:

    if row<datetime.datetime.now():
       df_midspread['Midspread']= df_midspread.Oldrate*df_midspread.Value
    else:
       df_midspread['Midspread']= df_midspread.Newrate*df_midspread.Value

Comparing date column of the csv file to today date

3 Answers3