title: How to extract last date from string within panda dataframe
body: <p>I need to find the last date preceding specific text within a string for each row in a panda dataframe. The text could be anywhere after the date within the string.</p>
  
  <p>I have a csv file that consists of two columns. The first is a reference number (unique on each row), the other is a string that can be split on commas. Similar to this:</p>
  
  <pre><code>Ref No  Audit
  abc123  "text1, text2, text3"
  abc124  "text1, text2, text3"
  </code></pre>
  
  <p>The Ref No refers to an item in a database, the Audit is an audit of all changes made to the item. Each comma separated item within the text is a single change (eg text1 is a change, text2 is a change etc). Only when a change is made on a new date is a date appended to the start of the part of text. Each item in the Audit column looks something like this:</p>
  
  <p>"01/01/2019 09:15:12 task 1 completed, task 2 completed, task 3 completed, 02/01/2019 15:26:03 task 4 completed, task 5 completed".</p>
  
  <p>I am trying to find a way to identify when a specific task is completed. In the example above, if I were wanting to know when task 5 was completed, this would be 02/01/2019.</p>
  
  <p>Beyond getting the data into a panda dataframe and putting the Audit text in a list, I'm lost about how to do this.</p>
  
  <pre><code>import pandas as pd
  
  audit = pd.read_csv('audit_export1.csv', encoding='latin1')
  
  audit['Audit'].apply(pd.Series)
  audit['Audit'] = audit['Audit'].str.split(',')
  </code></pre>
  
  <p>I'm hoping to get a dataframe containing 2 columns; the reference and the date of change searched for.</p>
  
  <p>Sorry for any mistakes or glaring errors in how I've worded the issue, I'm very new to python so would really appreciate any help you could give. I had a look at previously asked questions but couldn't find anything that answered this issue, but that may be my lack of knowledge being unable to generalise guidance.</p>
  
  <h2>Edit</h2>
  
  <p>27/03/2019 09:34:41 John Smith The field " Handler" has been changed from Old value "" to new value "John Doe",  The field "Workflow Status" has been changed from Old value "Awaiting allocation" to new value "With Handler",27/03/2019 22:24:19 Jane Doe The field "Handler" has been changed from Old value "Jane Doe" to new value "Bill Smith",10/04/2019 04:58:43 Jill Smith The field "Workflow Status" has been changed from Old value "With Handler" to new value "Passed to Clearing Desk",10/04/2019 06:31:09 Sam Jones The field "Workflow Status" has been changed from Old value "Passed to Clearing Desk" to new value "QC",13/04/2019 08:30:49 Dave Jones The field "QC Handler" has been changed from Old value "" to new value "Dave Jones",  The field "Workflow Status" has been changed from Old value "QC" to new value "QC - With Handler",13/04/2019 09:06:36 Dave Jones The field "Workflow Status" has been changed from Old value "QC - With Handler" to new value "Passed back for re-work",17/04/2019 23:52:11 Sally Smith The field "Workflow Status" has been changed from Old value "Passed back for re-work" to new value "QC - Passed to Clearing Desk",18/04/2019 2:45:30 Bill Jones The field "Workflow Status" has been changed from Old value "QC - Passed to Clearing Desk" to new value "Complaint Handling"</p>
  
  <h3>Edit 2</h3>
  
  <pre><code>import pandas as pd
  import re
  a = pd.read_csv('audit_export1.csv')
  to_find = "Workflow Status"
  
  for cases in a:
      date_list = re.findall(r'\d+/\d+/\d+ \d+:\d+:\d+ ', a['Audit Trail'])
  
      s = re.split(r'\d+/\d+/\d+ \d+:\d+:\d+ ', a['Audit Trail'])
      task_list = [i for i in s if i != '']
  
      for  i, item in enumerate(task_list):
          if to_find in item:
              a['Audit Date'] = date_list[i]
  </code></pre>
  
  <h3>Edit 3</h3>
  
  <pre><code>import pandas as pd
  from datetime import datetime
  
  to_find = 'Workflow'
  a = pd.read_csv('audit_export2.csv')
  
  a.Audit = a.Audit.str.split(',')
  
  overall_list = []
  for tmplist in a.Audit:
      dl = []
      for i in tmplist:
          i = i.strip()
          try: 
              sdate = datetime.strptime(i[:10],'%d/%m/%Y')
              dl.append(str(sdate) + i[10:])
          except ValueError: # if its NOT a date, adding the date got from the try block to the string
              dl.append(str(sdate) + i)
          dl = dl[::-1]
      overall_list.append(dl)
  
  a['Audit_date'] = pd.Series(overall_list)
  </code></pre>