I have a data in which I need to filter and re-arrange columns and add a new column based on multiple conditions. Couldn't figure it out how to be done. Kindly support. I was able to sort two columns and then I'm stuck how to produce the code and logic. Alm id has multiple occurances. Now I need to re-arrange in such a way that those alm ids that has Action as "Insert" with Occur_time, should be consider as first_occur_time. Those alm ids whose action is Update with latest Occur_time should be consider as last_occur_time and those alm id with actions as delete with occur time, should be consider as Cleared_time (If delete is not available, then we should replace it with its first_occur_date 23:59:59 -- Colored below in light brown). Below is the df - almdata) and output required. Kindly share the logic and R code through which this can be achieved.
Table data (almdata):