ShanZhengYang ShanZhengYang - 2 months ago 10
Python Question

How to preprocess and load a "big data" tsv file into a python dataframe?

I am currently trying to import the following large tab-delimited file into a dataframe-like structure within Python---naturally I am using

pandas
dataframe, though I am open to other options.

This file is several GB in size, and is not a standard
tsv
file---it is broken, i.e. the rows have a different number of columns. One row may have 25 columns, another has 21.

Here is an example of the data:

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


As you can see, some of these columns are not in the correct order...

Now, I think the correct way to import this file into a dataframe is to preprocess the data such that you can output a dataframe with
NaN
values, e.g.

Col_01 .... Col_20 Col_21 Col22 Col23 Col24 Col25
8 .... 25 NaN 25134 243344 NaN NaN
17 .... NaN 75 2 79876 73453 634534
19 .... 25 32425 NaN 989423 NaN NaN
12 .... 25 23424 342421 7 13424 67
3 .... 95 32121 NaN NaN NaN 111231


To make this even more complicated, this is a very large file, several GB in size.

Normally, I try to process the data in chunks, e.g.

import pandas as pd

for chunk in pd.read_table(FILE_PATH, header=None, sep='\t', chunksize=10**6):
# place chunks into a dataframe or HDF


However, I see no way to "preprocess" the data first in chunks, and then use chunks to read the data into
pandas.read_table()
. How would you do this? What sort of preprocessing tools are available---perhaps
sed
?
awk
?

This is a challenging problem, due to the size of the data and the formatting that must be done before loading into a dataframe. Any help appreciated.

Answer
$ 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 ""
}

Data sample (pandas.txt):

Col_01 Col_20 Col_21 Col_22 Col_23 Col_25
Col_01: 14  Col_20: 25    Col_21: 23432    Col_22: 639142
Col_01: 8   Col_20: 25    Col_22: 25134    Col_23: 243344
Col_01: 17  Col_21: 75    Col_23: 79876    Col_25: 634534    Col_22: 5    Col_24: 73453
Col_01: 19  Col_20: 25    Col_21: 32425    Col_23: 989423
Col_01: 12  Col_20: 25    Col_21: 23424    Col_22: 342421    Col_23: 7    Col_24: 13424    Col_25: 67
Col_01: 3   Col_20: 95    Col_21: 32121    Col_25: 111231

$ awk -f pandas.awk -pandas.txt
Col_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 

All needed cols should be in the data file header. It's probably not a big job to collect the headers while processing, just keep the data in arrays and print in the end, maybe in version 3.

If you read the headers from a different file (cols.txt) than the data file (pandas.txt), execute the script (pandas.awk):

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