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>You can do this more cleanly completely in Pandas.</p>

<p>Suppose you have two independent data frames with only one overlapping column:</p>

<pre><code>&gt;&gt;&gt; df1
   A  B
0  1  2
&gt;&gt;&gt; df2
   B  C
1  3  4
</code></pre>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/merging.html#concatenating-objects" rel="nofollow">.concat</a> to concatenate them together:</p>

<pre><code>&gt;&gt;&gt; pd.concat([df1, df2])
    A  B   C
0   1  2 NaN
1 NaN  3   4
</code></pre>

<p>You can see <code>NaN</code> is created for row values that do not exist. </p>

<p>This can easily be applied to your example data without preprocessing at all:</p>

<pre><code>import pandas as pd
df=pd.DataFrame()
with open(fn) as f_in:
    for i, line in enumerate(f_in):
        line_data=pd.DataFrame({k.strip():v.strip() 
                  for k,_,v in (e.partition(':') 
                        for e in line.split('\t'))}, index=[i])
        df=pd.concat([df, line_data])

&gt;&gt;&gt; df
  Col_01 Col_20 Col_21  Col_22  Col_23 Col_24  Col_25
0     14     25  23432  639142     NaN    NaN     NaN
1      8     25    NaN   25134  243344    NaN     NaN
2     17    NaN     75       5   79876  73453  634534
3     19     25  32425     NaN  989423    NaN     NaN
4     12     25  23424  342421       7  13424      67
5      3     95  32121     NaN     NaN    NaN  111231
</code></pre>

<hr>

<p>Alternatively, if your main issue is establishing the desired order of the columns in a multi chunk add of columns, just read all the column value first (not tested):</p>

<pre><code># based on the alpha numeric sort of the example of:
# [ALPHA]_[NUM]
headers=set()
with open(fn) as f:
    for line in f:
        for record in line.split('\t'):
            head,_,datum=record.partition(":")
            headers.add(head)
# sort as you wish:             
cols=sorted(headers, key=lambda e: int(e.partition('_')[2])) 
</code></pre>

<p>Pandas will use the order of the list for the column order if given in the initial creation of the DataFrame.</p>

python pandas awk sed dataframe