不一致字符串格式的数据分析

Question

我有一个任务正在进行中，但对我的方法有些怀疑。

问题是我有很多格式奇怪（而且不一致）的Excel文件，我需要从每个条目中提取某些字段。以下是一个示例数据集：

数据示例

我最初的做法是这样的：

导出为csv文件
按县分开
按区分开
单独分析每个区，提取值
写入output.csv

我遇到的问题是，这些文件的格式（看起来很有条理）几乎是随机的。每一行包含相同的字段，但顺序、间距和措辞都不同。我写了一个脚本来正确处理一个文件，但它在其他文件上却不管用。

所以我想问，有没有比简单的字符串处理更稳妥的方法来解决这个问题？我想的办法是使用模糊逻辑，试图确定某个项目属于哪个字段，这样可以处理一些不太确定的输入。你会怎么处理这个问题？

如果这能帮助你理解问题，这里是我写的脚本：

# 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 >5
  indices=[1]
  for i,j in enumerate(x):
    if len(j)>2:
      if j[:2]=="to":
        indices.append(i)
    if len(j)>3:
      if j[:3]==" to" or j[:3]=="for":
        indices.append(i)
    if len(j)>5:
      if j[:5]==" \"for" or j[:5]==" \'for":
        indices.append(i)
    if len(j)>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<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('(?<=\d)\s\s(?=for)',item)  # correctly and split them up so that the for begins
    splitto=re.split('(?<=\d)\s\s(?=to)',item)    # a cell
    if len(splitfor)>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)>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)>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)

字符串处理数据提取数据清洗数据分析 csv文件 excel文件模糊逻辑不一致数据

不一致字符串格式的数据分析

1 个回答

撰写回答