如何预处理“大数据” tsv文件并将其加载到python数据框中？

如何预处理“大数据” tsv文件并将其加载到python数据框中？

$ cat > pandas.awk
BEGIN {
PROCINFO[“sorted_in”]=”@ind_str_asc” # traversal order for for(i in a)
}
NR==1 { # the header cols is in the beginning of data file
# FORGET THIS: header cols from another file replace NR==1 with NR==FNR and see * below
split($0,a,” “) # mkheader a[1]=first_col …
for(i in a) { # replace with a[first_col]=”” …
a[a[i]]
printf “%6s%s”, a[i], OFS # output the header
delete a[i] # remove a[1], a[2], …
}
# next # FORGET THIS * next here if cols from another file UNTESTED
}
{
gsub(/: /,”=”) # replace key-value separator “: ” with “=”
split($0,b,FS) # split record from “,”
for(i in b) {
split(b[i],c,”=”) # split key=value to c[1]=key, c[2]=value
b[c[1]]=c[2] # b[key]=value
}
for(i in a)# go thru headers in a[] and printf from b[]
printf “%6s%s”, (i in b?b[i]:”NaN”), OFS; print “”
}

数据样本（

pandas.txt

）：

Col_01 Col_20 Col_21 Col_22 Col_23 Col_25Col_01: 14  Col_20: 25    Col_21: 23432    Col_22: 639142Col_01: 8   Col_20: 25    Col_22: 25134    Col_23: 243344Col_01: 17  Col_21: 75    Col_23: 79876    Col_25: 634534    Col_22: 5    Col_24: 73453Col_01: 19  Col_20: 25    Col_21: 32425    Col_23: 989423Col_01: 12  Col_20: 25    Col_21: 23424    Col_22: 342421    Col_23: 7    Col_24: 13424    Col_25: 67Col_01: 3   Col_20: 95    Col_21: 32121    Col_25: 111231$ awk -f pandas.awk -pandas.txtCol_01 Col_20 Col_21 Col_22 Col_23 Col_25    14     25  23432 639142    NaN    NaN      8     25    NaN  25134 243344    NaN     17    NaN     75      5  79876 634534     19     25  32425    NaN 989423    NaN     12     25  23424 342421      7     67      3     95  32121    NaN    NaN 111231

所有需要的列应该在数据文件头中。在处理过程中收集标头可能不是一件大事，只是将数据保留在数组中并最终打印（可能在版本3中）。

如果您从

cols.txt

与数据文件（

pandas.txt

）不同的文件（）中读取标头，请执行脚本（

pandas.awk

）：

$ awk -F pandas.awk cols.txt pandas.txt