Cannot compare dates between date variable and pandas dataframe

user_12048390 0
Viewed 91

I have a frustrating issue while comparing variable date with pandas dataset of dates. No matter what formatting options I try, I just cannot get these in line. May you guys please help, I basically only need to compare the dates in the pandas dataset with todays date + 6 months.

My code:

SourceData_Workbook = R"G:\AR\REPORTS\Automation Files\Credit Risk\test1.xlsx"

SourceInPandas = pd.read_excel(SourceData_Workbook, skiprows=33,header=0,index=False)

# Creating date variable + 6 months
six_months = date.today() + relativedelta(months=+6)


# Formatting sourced data to date format
SourceInPandas['Req.dlv.dt']=SourceInPandas['Req.dlv.dt'].apply(lambda x:datetime.strptime(x,'%d.%m.%Y'))

# Fails on this line
SourceInPandas.loc[(SourceInPandas['Req.dlv.dt']<= six_months) & (SourceInPandas['OpIt'] != "15 Overdue account")& (SourceInPandas['OpIt'] != "16 Prepayment required")& (SourceInPandas['OpIt'] != "17 Approval required"),"OpIt"]="Future delivery"

Stack trace:

TypeError: Invalid comparison between dtype=datetime64[ns] and date
pandas formatting excel date python
1 Answers

You can use Timestamp with Timestamp.floor and addded 6 months by DateOffset:

six_months = pd.Timestamp('today').floor('d') + pd.DateOffset(months=6)
print (six_months)
2021-06-10 00:00:00

SourceInPandas['Req.dlv.dt']=pd.to_datetime(SourceInPandas['Req.dlv.dt'], dayfirst=True)
user_29010020
Related