Pandas - Conditional Logic with Date Objects

Question

It seems that despite the fact when I check my pandas columns and it returns a dtype of dtype: datetime64[ns], I cannot get the two columns below in my code to compare during a conditional statement (Completed Date < Original_Due_Date).Mind, the Completed_Date column was used earlier in the code to compare against a date and it worked there.

The error I get stems from the line 4 lines up from the bottom: copied below

finaldf.loc[(finaldf['Original_Due_Date' >= 'Completed_Date'),'On_Time_Units'] = 'Order_Qty'

Error:

 ValueError: could not convert string to Timestamp

Full code below (cannot post data set because it is private)

if day_of_week !=0:
    finaldf['Completed_Date'] = pd.to_datetime(finaldf['Completed_Date'], format="%m/%d/%Y")
    finaldf['Due_Date'] = pd.to_datetime(finaldf['Due_Date'], format="%m/%d/%y") # making it lower case y made it work
    current_week_flags = (finaldf.Completed_Date >= last_monday) & (finaldf.Completed_Date <= today)
    finaldf.loc[(finaldf['Completed_Date'] >= last_monday) & (finaldf['Completed_Date'] <= today) & (finaldf['Due_Date'] < last_monday), 'Due_Date'] = last_monday
    #appears to be working great as of 4.17
    finaldf = finaldf.merge(origdue, on='Work_Order', how= 'left') #vlookup, puts column on outer right 
    finaldf = finaldf.merge(rcode, on='Work_Order', how= 'left')

    #above was working on 4.17
    test = (finaldf.Due_Date >= last_monday) & (finaldf.Due_Date < today)
    finaldf = finaldf[test]
    #above we filtered for the date range, mind the test is  boolean, that called it back in if the value is true

    finaldf = finaldf[finaldf.WO_Stat.str.contains('Complete', na=False)] #make df only contain complete orders
    #the above appears to work great as of 4.18

    #newcolumns = ['Days_Late', 'New_Days_Late', 'Status', 'Day', 'On_Time/Late', 'Cust_PO_#&_WO']
    #finaldf = finaldf.reindex(columns = newcolumns)
    finaldf = finaldf.assign(Days_Late = "", New_Days_Late="", Status="", Day="", On_Time_or_Late="", Cust_PO_WO="", On_Time_Units="", On_Time_Orders="")
    finaldf = finaldf[['column1,column2,Original_Due_Date,column3']]
    #finaldf['Completed_Date'] = pd.to_datetime(finaldf['Completed_Date'], format="%m/%d/%Y").dt.date()
    #finaldf['Orginal_Due_Date'] = pd.to_datetime(finaldf['Original_Due_Date'], format="%m/%d/%Y").dt.date()
    finaldf.loc[(finaldf['Original_Due_Date']>= 'Completed_Date'),'On_Time_Units'] = 'Order_Qty'
    writer = pd.ExcelWriter('currentweek.xlsx', engine='xlsxwriter')
    finaldf.to_excel(writer, index=False, sheet_name='Sheet1')    
    writer.save()

Possible duplicate of Compare two date columns and validate if DateA is earlier than DateB — dee cue
– dee cue, Commented Apr 19, 2019 at 3:35

Ruben Helsloot · Accepted Answer · 2020-10-25 16:58:47Z

1

What does this mean?

finaldf['Original_Due_Date' >= 'Completed_Date'

It's grammatically incorrect, should be

finaldf['Original_Due_Date'] >= 'Completed_Date'

Even then, you are comparing Timestamp (finaldf['Original_Due_Date']) to str ('Completed_Date'), hence the error. I guess what you meant is:

finaldf['Original_Due_Date'] >= finaldf['Completed_Date']

PS: Edit for updated questions:

flags = (finaldf['Original_Due_Date'] >= finaldf['Completed_Date'])

finaldf.loc[flags, 'On_Time_Units'] = finaldf.loc[flags, 'Order_Qty']

That's it, I ain't answer no more question.

finaldf.loc[(finaldf['Original_Due_Date']>= finaldf['Completed_Date']),'On_Time_Units'] = finaldf.Order_Qty

This also works :)

edited Oct 25, 2020 at 16:58

Ruben Helsloot

13.2k6 gold badges33 silver badges56 bronze badges

answered Apr 19, 2019 at 3:45

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

0004 Over a year ago

thank you !!! but I have one more questions for now on that - because the whole reason is to have the condition pass in the Order_Qty, and you see I edited the code to df[columnname], but before 'order_qty' just passed it in as a sttring? If you want I can put in another question?

0004 Over a year ago

thank you @Quang Hoang , I found another way, I should have waited to ask you I am so sorry, but thank you so much I have learned a lot working with you on this project!

0004 Over a year ago

finaldf.loc[(finaldf['Original_Due_Date']>= finaldf['Completed_Date']),'On_Time_Units'] = finaldf.Order_Qty

Collectives™ on Stack Overflow

Pandas - Conditional Logic with Date Objects

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related