How to preprocess and load a "big data" tsv file into a python dataframe?
<p>I am currently trying to import the following large tab-delimited file into a dataframe-like structure within Python---naturally I am using <code>pandas</code> dataframe, though I am open to other options. </p>

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

<p>Here is an example of the data:</p>

<pre><code>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
</code></pre>

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

<p>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 <code>NaN</code> values, e.g. </p>

<pre><code>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
</code></pre>

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

<p>Normally, I try to process the data in chunks, e.g. </p>

<pre><code>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 
</code></pre>

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

<p>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. </p>

<p>Another version which takes a separate column file as parameter or uses the first record. Run either way:</p>

<pre><code>awk -f pandas2.awk pandas.txt # first record as header
awk -f pandas2.awk cols.txt pandas.txt # first record from cols.txt
awk -v cols="cols.txt" -f pandas2.awk pandas.txt # read cols from cols.txt
</code></pre>

<p>Or even:</p>

<pre><code>awk -v cols="pandas.txt" -f pandas2.awk pandas.txt # separates keys from pandas.txt for header
</code></pre>

<p>Code:</p>

<pre><code>$ cat &gt; pandas2.awk
BEGIN {
    PROCINFO["sorted_in"]="@ind_str_asc" # traversal order for for(i in a)
    if(cols) {                           # if -v cols="column_file.txt" or even "pandas.txt"
        while ((getline line&lt; cols)&gt;0) { # read it in line by line
            gsub(/: [^ ]+/,"",line)      # remove values from "key: value"
            split(line,a)                # split to temp array
            for(i in a)                  # collect keys to column array
                col[a[i]]
        }
        for(i in col)                    # output columns
            printf "%6s%s", i, OFS
        print ""
    }
}
NR==1 &amp;&amp; cols=="" {                      # if the header cols are in the beginning of data file
                                         # if not, -v cols="column_file.txt"
    split($0,a," +")                     # split header record by spaces
    for(i in a) {
        col[a[i]]                        # set them to array col
        printf "%6s%s", a[i], OFS        # output the header
    }
    print ""
}
NR==1 {
    next
}
{
    gsub(/: /,"=")                       # replace key-value separator ": " with "="
    split($0,b,FS)                       # split record from separator FS
    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 col)                        # go thru headers in col[] and printf from b[]
        printf "%6s%s", (i in b?b[i]:"NaN"), OFS; print ""
}
</code></pre>

python pandas awk sed dataframe