large data transformation in python
<p>I have a large data set (ten 12gb csv files)  that have 25 columns and would want to transform it to a dataset with 6 columns. the first 3 columns remains the same whereas the 4th one would be the variable names and the rest contains data. Below is my input:</p>

<pre><code>#RIC    Date[L] Time[L] Type    L1-BidPrice L1-BidSize  L1-AskPrice L1-AskSize  L2-BidPrice L2-BidSize  L2-AskPrice L2-AskSize  L3-BidPrice L3-BidSize  L3-AskPrice L3-AskSize  L4-BidPrice L4-BidSize  L4-AskPrice L4-AskSize  L5-BidPrice L5-BidSize  L5-AskPrice L5-AskSize
HOU.ALP 20150901    30:10.8 Market Depth    5.29    50000   5.3 32000   5.28    50000   5.31    50000   5.27    50000   5.32    50000   5.26    50000   5.33    50000           5.34    50000
HOU.ALP 20150901    30:10.8 Market Depth    5.29    50000   5.3 44000   5.28    50000   5.31    50000   5.27    50000   5.32    50000   5.26    50000   5.33    50000           5.34    50000
HOU.ALP 20150901    30:12.1 Market Depth    5.29    50000   5.3 32000   5.28    50000   5.31    50000   5.27    50000   5.32    50000   5.26    50000   5.33    50000           5.34    50000
HOU.ALP 20150901    30:12.1 Market Depth    5.29    50000   5.3 38000   5.28    50000   5.31    50000   5.27    50000   5.32    50000   5.26    50000   5.33    50000           5.34    50000
</code></pre>

<p>and I would transform it to:</p>

<pre><code>#RIC    Date[L] Time[L] level   Bid_price   bid_volume  Ask_price   Ask_volume
HOU.ALP 20150901    30:10.8 L1  5.29    50000   5.3 50000
HOU.ALP 20150901    30:10.8 L2  5.28    50000   5.31    50000
HOU.ALP 20150901    30:12.1 L3  5.27    50000   5.32    50000
HOU.ALP 20150901    30:12.1 L4  5.26    50000   5.33    50000
HOU.ALP 20150901    30:12.1 L5              
HOU.ALP 20150901    30:12.1 L1  5.29    50000   5.3 50000
HOU.ALP 20150901    30:12.1 L2  5.28    44000   5.31    50000
HOU.ALP 20150901    30:12.1 L3  5.27    48000   5.32    50000
HOU.ALP 20150901    30:12.1 L4  5.26    50000   5.33    50000
</code></pre>

<p>Here is my attempt with the coding. I think I would have to use dictionary to write to a csv file</p>

<pre><code>def depth_data_transformation(input_file_list, output_file):

for file in input_file_list:
    file_to_open = '%s.csv' %file
    with open(file_to_open) as f, open(output_file, "w") as out:
        next(f) # skip header
        cols = ["#RIC", "Date[L]", "Time[L]", "level", "Bid_price", "bid_volume", "Ask_price", "Ask_volume"]
        wr = csv.writer(out)
        wr.writerow(cols)
        for row in csv.reader(f):
            # get all but first three cols
            it = row[4:]
            # zip_longest(*[iter(it)] * 4, fillvalue="") -&gt; group into 4's, add empty string for missing values
            for ind, t in enumerate(izip_longest(*[iter(it)] * 4, fillvalue=""), 1):
               # first 3 cols, level and group all in one row/list.
                wr.writerow(row[:3]+ ["l{}".format(ind)] + list(t))                                 
</code></pre>

<p>You need to group the levels, i.e <code>L1-BidPrice L1-BidSize  L1-AskPrice L1-AskSize</code> and write each to a new row :</p>

<pre><code>import csv  
from itertools import zip_longest # izip_longest python2


with open("infile.csv") as f, open("out.csv", "w") as out:
    next(f) # skip header
    cols = ["#RIC", "Date[L]", "Time[L]", "level", "Bid_price", "bid_volume", "Ask_price", "Ask_volume"]
    wr = csv.writer(out)
    wr.writerow(cols)
    for row in csv.reader(f):
        # get all but first three cols.
        it = row[4:]
        # zip_longest(*[iter(it)] * 4, fillvalue="") -&gt; group into 4's, add empty string for missing values
        for ind, t in enumerate(zip_longest(*[iter(it)] * 4, fillvalue=""), 1):
           # first 3 cols, level and group all in one row/list.
            wr.writerow(row[:3]+ ["l{}".format(ind)] + list(t))
</code></pre>

<p>Which would give you:</p>

<pre><code>#RIC,Date[L],Time[L],level,Bid_price,bid_volume,Ask_price,Ask_volume
HOU.ALP,20150901,30:10.8,l1,5.29,50000,5.3,32000
HOU.ALP,20150901,30:10.8,l2,5.28,50000,5.31,50000
HOU.ALP,20150901,30:10.8,l3,5.27,50000,5.32,50000
HOU.ALP,20150901,30:10.8,l4,5.26,50000,5.33,50000
HOU.ALP,20150901,30:10.8,l5,5.34,50000,,
HOU.ALP,20150901,30:10.8,l1,5.29,50000,5.3,44000
HOU.ALP,20150901,30:10.8,l2,5.28,50000,5.31,50000
HOU.ALP,20150901,30:10.8,l3,5.27,50000,5.32,50000
HOU.ALP,20150901,30:10.8,l4,5.26,50000,5.33,50000
HOU.ALP,20150901,30:10.8,l5,5.34,50000,,
HOU.ALP,20150901,30:12.1,l1,5.29,50000,5.3,32000
HOU.ALP,20150901,30:12.1,l2,5.28,50000,5.31,50000
HOU.ALP,20150901,30:12.1,l3,5.27,50000,5.32,50000
HOU.ALP,20150901,30:12.1,l4,5.26,50000,5.33,50000
HOU.ALP,20150901,30:12.1,l5,5.34,50000,,
HOU.ALP,20150901,30:12.1,l1,5.29,50000,5.3,38000
HOU.ALP,20150901,30:12.1,l2,5.28,50000,5.31,50000
HOU.ALP,20150901,30:12.1,l3,5.27,50000,5.32,50000
HOU.ALP,20150901,30:12.1,l4,5.26,50000,5.33,50000
HOU.ALP,20150901,30:12.1,l5,5.34,50000,,
</code></pre>

<p>In <code>for ind, t in enumerate(zip_longest(*[iter(it)] * 4, fillvalue=""), 1)</code>, <em><code>enumerate</code></em> with a start index of 1 is keeping track of which <em>group/level</em> we are at.</p>

<p><em><code>zip_longest(*[iter(it)] * 4, fillvalue="")</code></em> groups the cols into sections i.e <code>L1-BidPrice,L1-BidSize,L1-AskPrice,L1-AskSize</code>, <code>L2-BidPrice,L2-BidSize,L2-AskPrice,L2-AskSize</code> etc.. all the way to <code>Ln-..</code> </p>

<p>You have <code>HOU.ALP 20150901    30:10.8 L1  5.29    50000   5.3 50000</code> in your expected output but 32000 is the value in your input for <code>L1-AskSize</code>, each row has 5 levels and you also have 8 columns so I presume your expected output is wrong.</p>

python csv