title: What's the best Pandas apply / loop method in this case?
body: <p>I'm transforming some applicant transactional data, and I need to create a new flag column (labeled as "DESIRED FLAG" in my example). However, I can't figure out the right looping/apply method as there can be so many different variations in the logic below. </p>
  
  <p>In a perfect world, the sequential applicant process history would look like this, with all "Statuses" set to "Completed": </p>
  
  <ul>
  <li>On-Site Interview Kick Off --> Schedule Interviews --> Decision; OR</li>
  <li>Phone Interview Kick Off --> Schedule Interviews --> Decision</li>
  </ul>
  
  <p>And of course, applicants can go through many phone interviews and on-sites during their applicant process.</p>
  
  <p>As the example below shows, sometimes there are "Schedule Interviews" that are canceled. In those cases, I need to remove that step and the subsequent steps associated with that. These include "Schedule Interviews, "Decision", and "On-Site Interview Kick Off"OR"Phone Interview Kick Off". Also, sometimes there can be other "Events" like we see with the manually skipped one.</p>
  
  <p>I have other types of scenarios that I need to create flags for, so I need to keep the original dataframe with just the new column.</p>
  
  <pre><code>import pandas as pd
  
  data = {'Employee ID': ["100","100", "100", "100","100","100","100","100","100","100","200", "200", "200","200","200","200","200","300","300", "300", "300","300","300","300"],
          'Completed On Date': ["2009-01-01","2010-01-01","2011-06-05","2012-07-01","2013-01-01","2014-01-01","2015-01-01","2016-01-01","2017-01-01","2018-01-01","2010-01-01","2011-06-05","2012-07-01","2012-08-15","2013-01-01","2014-01-01","2015-01-01","2009-01-01","2010-01-01","2011-06-05","2012-07-01","2013-01-01","2014-01-01","2015-01-01"],
          'Event': ["Decision","On-Site Interview Kick Off","Schedule Interviews","Decision","On-Site Interview Kick Off","Schedule Interviews","Decision","Phone Interview Kick Off","Schedule Interviews","Decision","On-Site Interview Kick Off","Schedule Interviews","Decision","Decision","Phone Interview Kick Off","Schedule Interviews","Decision","Job Apply","Phone Interview Kick Off","Schedule Interviews","Decision","On-Site Interview Kick Off","Schedule Interviews","Decision"],
          'Event Status': ["Completed","Completed","CANCELED","Completed","Completed","Completed","Completed","Completed","Completed","Completed","Completed","CANCELED","Manually Skipped","Completed","Completed","Completed","Completed","Completed","Completed","CANCELED","Completed","Completed","Completed","Completed"],
          'DESIRED FLAG': ["Keep","Keep","Remove","Remove","Remove","Keep","Keep","Keep","Keep","Keep","Keep","Remove","Remove","Remove","Remove","Keep","Keep","Keep","Keep","Remove","Remove","Remove","Keep","Keep"]}
  df = pd.DataFrame(data, columns=['Employee ID','Completed On Date','Event','Event Status','DESIRED FLAG'])
  df = df.sort_values(by=(['Employee ID','Completed On Date']))
  
  df
  </code></pre>