title: Data analysis for inconsistent string formatting
body: <p>I have this task that I've been working on, but am having extreme misgivings about my methodology.</p>
  
  <p>So the problem is that I have a ton of excel files that are formatted strangely (and not consistently) and I need to extract certain fields for each entry. An example data set is</p>
  
  <p><img src="https://i.stack.imgur.com/G5ZvE.png" alt="a look at the data"></p>
  
  <p>My original approach was this:</p>
  
  <ol>
  <li>Export to csv</li>
  <li>Separate into counties</li>
  <li>Separate into districts</li>
  <li>Analyze each district individually, pull out values</li>
  <li>write to output.csv</li>
  </ol>
  
  <p>The problem I've run into is that the format (seemingly well organized) is almost random across files. Each line contains the same fields, but in a different order, spacing, and wording. I wrote a script to correctly process one file, but it doesn't work on any other files.</p>
  
  <p>So my question is, is there a more robust method of approaching this problem rather than simple string processing? What I had in mind was more of a fuzzy logic approach for trying to pin which field an item was, which could handle the inputs being a little arbitrary. How would you approach this problem?</p>
  
  <p>If it helps clear up the problem, here is the script I wrote:</p>
  
  <pre><code># This file takes a tax CSV file as input
  # and separates it into counties
  # then appends each county's entries onto
  # the end of the master out.csv
  # which will contain everything including
  # taxes, bonds, etc from all years
  
  #import the data csv
  import sys
  import re
  import csv
  
  def cleancommas(x):
    toggle=False
    for i,j in enumerate(x):
      if j=="\"":
        toggle=not toggle
      if toggle==True:
        if j==",":
          x=x[:i]+" "+x[i+1:]
    return x
  
  def districtatize(x):
    #list indexes of entries starting with "for" or "to" of length &gt;5
    indices=[1]
    for i,j in enumerate(x):
      if len(j)&gt;2:
        if j[:2]=="to":
          indices.append(i)
      if len(j)&gt;3:
        if j[:3]==" to" or j[:3]=="for":
          indices.append(i)
      if len(j)&gt;5:
        if j[:5]==" \"for" or j[:5]==" \'for":
          indices.append(i)
      if len(j)&gt;4:
        if j[:4]==" \"to" or j[:4]==" \'to" or j[:4]==" for":
          indices.append(i)
    if len(indices)==1:
      return [x[0],x[1:len(x)-1]]
    new=[x[0],x[1:indices[1]+1]]
    z=1
    while z&lt;len(indices)-1:
      new.append(x[indices[z]+1:indices[z+1]+1])
      z+=1
    return new
    #should return a list of lists. First entry will be county
    #each successive element in list will be list by district
  
  def splitforstos(string):
    for itemind,item in enumerate(string):      # take all exception cases that didn't get processed
      splitfor=re.split('(?&lt;=\d)\s\s(?=for)',item)  # correctly and split them up so that the for begins
      splitto=re.split('(?&lt;=\d)\s\s(?=to)',item)    # a cell
      if len(splitfor)&gt;1:
        print "\n\n\nfor detected\n\n"
        string.remove(item)
        string.insert(itemind,splitfor[0])
        string.insert(itemind+1,splitfor[1])
      elif len(splitto)&gt;1:
        print "\n\n\nto detected\n\n"
        string.remove(item)
        string.insert(itemind,splitto[0])
        string.insert(itemind+1,splitto[1])
  
  def analyze(x):
    #input should be a string of content
    #target values are nomills,levytype,term,yearcom,yeardue
    clean=cleancommas(x)
    countylist=clean.split(',')
    emptystrip=filter(lambda a: a != '',countylist)
    empt2strip=filter(lambda a: a != ' ', emptystrip)
    singstrip=filter(lambda a: a != '\' \'',empt2strip)
    quotestrip=filter(lambda a: a !='\" \"',singstrip)
    splitforstos(quotestrip)
    distd=districtatize(quotestrip)
    print '\n\ndistrictized\n\n',distd
    county = distd[0]
    for x in distd[1:]:
      if len(x)&gt;8:
        district=x[0]
        vote1=x[1]
        votemil=x[2]
        spaceindex=[m.start() for m in re.finditer(' ', votemil)][-1]
        vote2=votemil[:spaceindex]
        mills=votemil[spaceindex+1:]
        votetype=x[4]
        numyears=x[6]
        yearcom=x[8]
        yeardue=x[10]
        reason=x[11]
        data = [filename,county,district, vote1, vote2, mills, votetype, numyears, yearcom, yeardue, reason]
        print "data",data
      else:
        print "x\n\n",x
        district=x[0]
        vote1=x[1]
        votemil=x[2]
        spaceindex=[m.start() for m in re.finditer(' ', votemil)][-1]
        vote2=votemil[:spaceindex]
        mills=votemil[spaceindex+1:]
        votetype=x[4]
        special=x[5]
        splitspec=special.split(' ')
        try:
          forind=[i for i,j in enumerate(splitspec) if j=='for'][0]
          numyears=splitspec[forind+1]
          yearcom=splitspec[forind+6]
        except:
          forind=[i for i,j in enumerate(splitspec) if j=='commencing'][0]
          numyears=None
          yearcom=splitspec[forind+2]
        yeardue=str(x[6])[-4:]
        reason=x[7]
        data = [filename,county,district,vote1,vote2,mills,votetype,numyears,yearcom,yeardue,reason]
        print "data other", data
      openfile=csv.writer(open('out.csv','a'),delimiter=',', quotechar='|',quoting=csv.QUOTE_MINIMAL)
      openfile.writerow(data)
  
  # call the file like so: python tax.py 2007May8Tax.csv
  filename = sys.argv[1] #the file is the first argument
  f=open(filename,'r')
  contents=f.read() #entire csv as string
  #find index of every instance of the word county
  separators=[m.start() for m in re.finditer('\w+\sCOUNTY',contents)] #alternative implementation in regex
  
  # split contents into sections by county
  # analyze each section and append to out.csv
  for x,y in enumerate(separators):
    try:
      data = contents[y:separators[x+1]]
    except:
      data = contents[y:]
    analyze(data)
  </code></pre>