如何使用python列表在文本文件的缺失位置填充NA

在这里,我尝试遵循一种通用方法,即不必在代码中预先编程列跨距。要返回数据帧,可以使用pd。用stringio阅读csv。请根据您的文件位置修改路径。这段代码是从你的代码中扩展出来的,让你更容易理解,否则还有更有效的方法来编写同样的逻辑

    import re
import pandas as pd
import StringIO
path = "/home/clik/clik/demo.txt"
EndStr = "------------------------------"
FilterStr = "=================="
FindStr = "empcode   Emnname"


def match(sp1, sp2):
    disjunct = max(sp1[0] - sp2[1], sp2[0] - sp1[1])
    if disjunct >= 0:
        return -abs((sp1[0]+sp1[1])/2.0 - (sp2[0]+sp2[1])/2.0)
    return float(disjunct) / min(sp1[0] - sp2[1], sp2[0] - sp1[1])


def PrepareList():
    with open(path) as f:
        out = []
        for i, line in enumerate(f):
            print line.rstrip()
            if line.rstrip().startswith(FindStr):
                print(line)
                tmp = []
                col_spans = [m.span() for m in re.finditer("[^\s][^\s]+", line)]
                tmp.append(re.sub("\s+", ",", line.strip()))
                # print(tmp)
                for line in f:
                    if line.rstrip().startswith(EndStr):
                        out.append(tmp)
                        break
                    row = [None] * len(col_spans)
                    for m in re.finditer("[^\s][^\s]+", line):
                        colmatches = [match(m.span(), cspan) for cspan in col_spans]
                        max_index = max(enumerate(colmatches), key=lambda e: e[1])[0]
                        row[max_index] = m.group() if row[max_index] is None else (row[max_index] + ' ' + m.group())
                    tmp.append(','.join(['NA' if e is None else e for e in row]))
                    #tmp.append(re.sub("\s+", ",", line.strip()))
                #for pandas dataframe
                #return pd.read_csv(StringIO.StringIO('\n'.join(tmp)))

                #for returning list of tuples
                return map(tuple, tmp)
                #for returning list of list
                #return tmp
    f.close()


LstEmp = PrepareList()

要将元组列表转换为pyspark dataframe,下面是一个教程 http://bigdataplaybook.blogspot.in/2017/01/create-dataframe-from-list-of-tuples.html

从数据集中可以看出,字段中的文本长度是可变的,字段本身以固定位置开始和结束。这通常发生在制表符分隔的字段中。

==============================================
empcode   Emnname   Date       DESC
12d      sf        2018-02-06      dghsjf  
asf2     asdfw2    2018-02-16      fsfsfg  
dsf21    sdf2      2016-02-06      sdgfsgf

如果是这种情况,则应采取以下措施:

for line in f:
    if line.rstrip()==Findstr:
        tmp=[]
        tmp.append(re.sub("\t",",",line.strip()))
        #print(tmp)
        for line in f:
            if line.rstrip()==EndStr:
                out.append(tmp)
                break

            tmp.append(re.sub("\t",",",line.strip()))
        return (tmp)

我换了 \s 在代码中使用 \t 并移除 + . 在python正则表达式中, + 符号展开以匹配其前面的一个或多个正则表达式。在这种情况下是这样的 \s 从第一个字段的末尾扩展到下一个字段。

或者,如果输入文件不是以制表符分隔的,则可以根据固定长度字段提取字段值,然后执行 strip()

fields = [ (0,10),
           (10, 20),
           (20,36),
           (36,100) # Assuming last field will not cross this length
         ]
field_values = [ line[ x[0]:x[1] ].strip() for x in fields ]