7

So I have a large file that contains count values in columns. A shorter version of the file is as follows:

Id,Sample1,Sample2,Sample3,Sample4,Sample5,Sample6,Sample7,Sample8,Sample9,Sample10,Sample11,Sample12,Sample13,Sample14,Sample15,Sample16,Sample17,Sample18,Sample19,Sample20,Sample21,Sample22,Sample23,Sample24
bar,80,167,1419,2973,16846,31523,257,466,4004,6662,13862,22205,116,284,786,1198,467,853,3333,6054,18122,34030,22,41
foo,29,71,582,1143,6382,11466,99,138,1388,2176,4337,7043,55,106,349,600,211,319,1468,2418,8661,15285,12,12
qaz,23,59,478,904,4919,8538,85,147,1553,2463,5061,8094,56,84,271,411,176,291,1268,2132,7219,12436,6,13
etc,27,43,464,970,4101,8092,90,111,1441,2174,4954,7940,41,85,249,378,130,234,1075,1856,5920,10854,9,18
zxc,15,34,332,609,2568,4652,53,82,989,1592,3219,5034,46,87,315,479,104,170,759,1297,4926,8171,2,9
qwe,9,20,146,310,2932,5391,48,94,842,1349,2823,4430,23,34,115,235,113,172,811,1362,4178,7576,8,10
asd,16,39,388,687,2796,5303,51,110,763,1216,2610,4165,50,79,351,603,113,156,824,1345,3337,6168,4,9
abc,6,16,91,192,385,654,21,32,326,496,7884,12266,10,19,56,89,41,62,251,367,1545,2657,,4
xyz,14,33,249,538,1634,2983,28,71,555,952,1697,2712,23,34,111,167,95,148,650,1131,3204,5814,4,12

What I would like to do is to sum consecutive columns (sample1 + sample2, sample3 + sample4, ...) such that:

bar,247,4392,...
foo,100,1725,...
qaz,82,1382,...
...

I've tried for loops over NF but it seems to print everything on separate lines, also the numbers aren't really accurate either...

> awk -F, 'NR > 1 {for(i=2; i<=NF; i=i+2){print ($i + $i+1)}}' short.csv 
161
2839
33693
515
8009
27725
233
1573
935
6667
36245
45
59
1165
...

Is it possible to do this in awk or similar light-weight, command line script-fu?

  • @Rahul The values under columns SampleN, tried to clarify it in the question body. – posdef Aug 11 '16 at 12:18
  • 3
    replace $i+1 by $(i+1). – meuh Aug 11 '16 at 12:18
  • @meuh That solves the addition problem, the values are still on separate rows however. I am also not sure how to get the first column in on the same line – posdef Aug 11 '16 at 12:20
  • 1
    use printf "%d,",... for print. – meuh Aug 11 '16 at 12:22
9

The problem you had with $i + $i+1 was effectively it was doing $i + $i + 1 which isn't what you wanted.

Instead something like

awk -F, 'NR > 1 { printf("%s",$1)
                  for(i=2;i<=NF;i+=2) { printf(",%d",$i+$(i+1)) }
                  print ""
                }
'

Results:

bar,247,4392,48369,723,10666,36067,400,1984,1320,9387,52152,63
foo,100,1725,17848,237,3564,11380,161,949,530,3886,23946,24
qaz,82,1382,13457,232,4016,13155,140,682,467,3400,19655,19
etc,70,1434,12193,201,3615,12894,126,627,364,2931,16774,27
zxc,49,941,7220,135,2581,8253,133,794,274,2056,13097,11
qwe,29,456,8323,142,2191,7253,57,350,285,2173,11754,18
asd,55,1075,8099,161,1979,6775,129,954,269,2169,9505,13
abc,22,283,1039,53,822,20150,29,145,103,618,4202,4
xyz,47,787,4617,99,1507,4409,57,278,243,1781,9018,16
  • Fantastic! Thank you.. I suppose it's in general nicer to put that script inside a separate file? I say that mostly since it's easier to manage the parentheses over multiple lines.. – posdef Aug 11 '16 at 12:31
  • 2
    That's a matter of taste. I tend to inline short awk scripts like this so there's less external file dependencies. awk doesn't care too much about newlines in this scenario so you could even put it all on one line with ; separators; awk -F, 'NR > 1 { printf("%s",$1) ; for(i=2;i<=NF;i+=2) { printf(",%d",$i+$(i+1)) } ; print "" }' – Stephen Harris Aug 11 '16 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.