Re: [gentoo-user] [OT] - command line read *.csv & create new file

[Mark Knecht <markknecht@gmail.com>]

On Mon, Feb 23, 2009 at 2:18 PM, Etaoin Shrdlu <shrdlu@unlimitedmail.org> wrote:
> On Monday 23 February 2009, 17:05, Mark Knecht wrote:
>
>> I'm attaching a small (100 line) data file out of TradeStation. Zipped
>> it's about 2K. It should expand to about 10K. When I run the command
>> to get 10 lines put together it works correctly and gives me a file
>> with 91 lines and about 100K in size. (I.e. - 10x on my disk.)
>>
>> awk -v n=10 -f awkScript1.awk awkDataIn.csv >awkDataOut.csv
>>
>> No mangling of the first line - that must have been something earlier
>> I guess. Sorry for the confusion on that front.
>>
>> One other item has come up as I start to play with this farther down
>> the tool chain. I want to use this data in either R or RapidMiner to
>> data mine for patterns. Both of those tools are easier to use if the
>> first line in the file has column titles. I had originally asked
>> TradeStation not to output the column titles but if I do then for the
>> first line of our new file I should actually copy the first line of
>> the input file N times. Something like
>>
>> For i=1; read line, write N times, write \n
>>
>> and then
>>
>> for i>=2 do what we're doing right now.
>
> That is actually accomplished just by adding a bit of code:
>
> BEGIN {FS=OFS=","}
>
> NR==1{for(i=1;i<=n;i++){printf "%s%s", sep, $0;sep=OFS};print""} # header
> NR>=2{
>   r=$NF;NF--
>   for(i=1;i<n;i++){
>     s[i]=s[i+1]
>     dt[i]=dt[i+1]
>     if((NR>=n+1)&&(i==1))printf "%s%s",dt[1],OFS
>     if(NR>=n+1)printf "%s%s",s[i],OFS
>   }
>   sep=dt[n]="";for(i=1;i<=dropcol;i++){dt[n]=dt[n] sep $i;sep=OFS}
>   sub("^([^,]*,){"dropcol"}","")
>   s[n]=$0
>   if(NR>=n+1)printf "%s,%s\n", s[n],r
> }
>
> Note that no column is dropped from the header. If you need to do that,
> just tell us how you want to do that.
>

thanks. that's a good add.

If I drop columns - and I do need to - then something like how cut
works would be good, but it needs to repeat across all the rows being
used. For instance, if I'm dropping columns 6 & 12 from a 20 column
wide data set, then I'm dropping 6 & 12 from all N lines. This is
where using cut after the line is built is difficult as I'm forced to
figure out a list like 6,12,26,32,46,52, etc. Easy to make a mistake
doing that. If I could say something like "Drop 6 & 12 from all rows,
and 1 & 2 from all rows higher than the first that make up this new
line" then that would be great. That's a lot to ask though.

D1,T1,A1,B1,C1,D1,
D2,T2,A2,B2,C2,D2,
D3,T3,A3,B3,C3,D3,
D4,T4,A4,B4,C4,D4,
D5,T5,A5,B5,C5,D5,

In the data above if I drop column A, then I drop it for all rows.
(For instance, A contains 0 and isn't necessary, etc.)  Assuming 3
wide I'd get

D1,T1,B1,C1,D1,B2,C2,D2,B3,C3,D3
D2,T2,B2,C2,D2,B3,C3,D3,B4,C4,D4
D3,T3,B3,C3,D3,B4,C4,D4,B5,C5,D5

Making that completely flexible - where I can drop 4 or 5 random
columns - is probably a bit too much work. On the other hand maybe
sending it to cut as part of the whole process, line by lone or
something, is more reasonable? I don't know.

I found a web site to study awk so I'm starting to see more or less
how your example works when I have the code in front of me. Creating
the code out of thin air might be a bit of a stretch for me at this
point though.

thanks for your help!

Cheers,
Mark