I am having two dataframe which has been load from excel and here is the sample:
df1:
EmpName End date amount hours total quantity
emp1 date1 10 2 20
emp2 date2 20 3 60
emp3 date3 30 4 120
emp4 date4 40 5 200
emp5 date5 50 6 300
emp6 date6 60 7 420
emp7 date7 20 8 160
emp8 date8 30 1 30
emp9 date9 10 2 20
emp10 date10 40 4 160
df2:
EmpName End date amount hours total quantity
emp2 date7 10 2 20
emp2 date4 20 3 60
emp1 date6 30 4 120
emp7 date1 40 5 200
emp4 date8 50 6 300
emp6 date3 60 7 420
emp5 date4 20 8 160
emp8 date5 30 1 30
emp9 date9 10 2 20
From the above two table i need to find if the df1.empname isin df2.empname and df2.to_date isin df2.to_date if both the above condition are true then fetch the respective values of df2-date, df2-amount and df2-hours.
I was able to get the condition to true but was not able to fetch the same. code is here:
df1['Status'] = np.where(df1['EmpName'].isin (df2['EmpName']) & df1['End date'].isin (df2['End Date']), 'Found', 'Not Found')
df1.head()
I cannot merge both the dataset as each dataset has around 27k of rows. and if I merge using inner or outer the total row count is comming around 674502 rows × 50 columns and also after the above code is executed with some modification it is giving me wrong dataset information with the same rows and columns
