Hi guys,
Thanks for having me. I have 2 df. 1 comes from the worksheet where a list of projects with a ref number, a date & the employees ID is entered. This in internally maintained. These projects get submitted to our vendor portal which sends us emails about the acceptability of said projects. It includes our ref number in emails. The other df is the email lists that I’ve been able to get in pd with the sender ID, the date sent, the body which contains our ref numbers along with the approval or rejection phrase.
I want to be able to search for each ref number from the first df within the body column of the second df and give me whether the body says the words, approved / rejected / under consideration.
I only need the status of these ref from the latest mail received, as there are multiple mails received that acknowledge the project being submitted, the project being under consideration etc., but I only need the status that is posted in the last email received. I then want to add the status in the first df against the ref numbers.
So far, I’ve been able to read the excel using pd and get the emails from outlook to the pd.df that have the recivers mail, the mailbody, the date and have been able to filter the df based on if the mailbody contains the ref number from the excel df. I now need to further filter the df for each ref based on if the mailbody contains the keywords rejected, consideration or approved. I then want to update the first df with addional column that contain the updates for the ref as well as the date when the mail was recieved.
sample table with date, ref and the employee ID
|Column 1 | Column 2 | Column 3|
|— | — | —|
|Date | Ref. No. | EMPLOYEE|
|31/Jan/2023 | H-DM-38100 | ARVA|
|09/Feb/2023 | H-DM-38127 | EMAD|
|28/Mar/2023 | H-DM-38203 | MEENA|
|27/Apr/2023 | H-DM-38261 | ARVA|
Some ideas that I have is that I can shorten the loop by only reading the latest mail’s body for the ref and the keyword, but how do I go about looking for 2 matches .