Hi All,
Getting not the expected results.
The below code should check the Timesheet Date lies between Date Start and Date Expiry and then say its Qualified or learner or Not Qualified
import pandas as pd
import numpy as np
import os
import datetime
data = pd.read_excel(r'C:\Users\sbhadra\AppData\Local\Programs\Python Scripts\Excel\data.xlsx')
qualification = pd.read_excel(r'C:\Users\sbhadra\AppData\Local\Programs\Python Scripts\Excel\Q.xlsx')
## Clearing up qualification table
qualification = qualification[['Employee Code','Qualification ID','Date Start','Date End','Date Expiry','Comments']]
qualification['Qualification ID']=qualification['Qualification ID'].fillna(-1)
qualification['Qualification ID']=qualification['Qualification ID'].astype(int)
## Copy start date value to end date when end date is not present
for i in qualification.index:
if(pd.isnull(qualification.at[i, 'Date End'])):
qualification.at[i, 'Date End'] = qualification.at[i, 'Date Start']
######
## Clearing the time part from timesheet datetime
data["Timesheet Date"] = pd.to_datetime(data["Timesheet Date"], errors='coerce',utc=False)
#print(data)
######
## Loop for each skill for one employee and check the qualification table for valid skill
for i in data.index:
empCode = data.at[i, 'Employee Code']
skillCode = data.at[i, 'Skill Code']
timesheetDate = data.at[i, 'Timesheet Date']
derivedQual = 'Not Qualified'
#print("empCode" + str(empCode) + "::::::" + "skillCode" + str(skillCode))
## Lookup all entries in qualification table for this empCode and skillCode
qual = qualification.loc[(qualification['Employee Code'] == empCode) & (qualification['Qualification ID'] == skillCode)]
#qual['Date End'] = qual.index
qual = qual.sort_values('Date End', ascending=True)
for j in qual.index:
startDate = qual.at[j, 'Date Start']
endDate = qual.at[j, 'Date End']
expiryDate = qual.at[j, 'Date Expiry']
# Added to check if expiry date null then put timesheet date
if(pd.isnull(expiryDate)):
expiryDate = timesheetDate
if(derivedQual != 'Qualified' and pd.to_datetime(timesheetDate) > pd.to_datetime(endDate)):
derivedQual = 'Learner'
elif(derivedQual != 'Qualified' and pd.to_datetime(timesheetDate) > pd.to_datetime(expiryDate)):
derivedQual = 'Not Qualified'
elif(derivedQual != 'Qualified' and pd.to_datetime(timesheetDate) >= pd.to_datetime(endDate) and pd.to_datetime(timesheetDate) <= pd.to_datetime(expiryDate)):
derivedQual = 'Qualified'
break
print(pd.to_datetime(endDate))
print("timesheetDate: " + str(timesheetDate) + " Date Start: " +str(pd.to_datetime(startDate))+ " Date End: " + str(pd.to_datetime(endDate)) + " Date Expiry: " + str(pd.to_datetime(expiryDate)))
data.at[i, 'Test'] = derivedQual
#print(qual)
print(data)
Getting some warnings and even the result is fulfilling the third option - but still the variable is not displaying Not Qualified instead of ‘Qualified’.
c:\Users\sbhadra\AppData\Local\Programs\Python Scripts\Final.py:65: UserWarning: Parsing dates in %d/%m/%Y format when dayfirst=False (the default) was specified. Pass `dayfirst=True` or specify a format to silence this warning.
print(pd.to_datetime(endDate))
2024-04-29 00:00:00
c:\Users\sbhadra\AppData\Local\Programs\Python Scripts\Final.py:66: UserWarning: Parsing dates in %d/%m/%Y format when dayfirst=False (the default) was specified. Pass `dayfirst=True` or specify a format to silence this warning.
print("timesheetDate: " + str(timesheetDate) + " Date Start: " +str(pd.to_datetime(startDate))+ " Date End: " + str(pd.to_datetime(endDate)) + " Date Expiry: " + str(pd.to_datetime(expiryDate)))
timesheetDate: 2024-01-05 00:00:00 Date Start: 2024-04-29 00:00:00 Date End: 2024-04-29 00:00:00 Date Expiry: 2024-01-05 00:00:00
Employee Code Skill Code Skill Description Timesheet Date Employee Hours Key Qualified Test
0 OBRIJA 1 Expanda Profile Handler-1 2024-01-05 9 OBRIJA-1 NaN Not Qualified
Any suggestion