Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Summing up rows data regarding 1st column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Summing up rows data regarding 1st column
# 1  
Old 04-10-2012
Summing up rows data regarding 1st column
Dear all,

I have one file like
Code:
   
  LABEL   A   B   C   D   E   F   G   H   I   J   K   L   M   N      
 G02100   64651.3   25630.7   8225.21   51238   267324   268005   234001   52410.9   18598.2   10611   10754.7   122535   267170   36631.4     
  G02100   12030.3   8260.15   8569.91   7901.76   6730.83   3060.29   4991.3   10213.1   5043.9   4889.69   3838.4   3652.25   3742.68   5945   
    G02100   10909.1   222.446   0   0   0   0   0   295.779   19.884   0   0   0   0   0      
 G02780   8287.85   8067.36   5764.61   7754.75   4428.79   1653.26   4524.88   6942.11   6309.85   6228.8   8280.75   3437.41   3310.83   4884.93     
  G02780   7780.28   19497.7   10218.1   10975.8   3154.82   191.512   64.78   5298.8   16604.7   12996.6   3010.33   751.934   143.949   9.5502       
G09200   6974.73   7790.54   3865.08   7108.96   3778.3   1625.9   4294.91   4277.2   4259.59   4709.11   7341.28   3574.67   3382.8   4967.7      
 G37770   6924.22   6072.77   1353.14   708.795   140.226   125.069   437.265   5715.5   2883.18   2494.85   5448.92   698.224   503.822   100.277   
    G74270   10681.8   11908.3   3564.38   10432.4   4826.1   2020.65   7799.51   8969.07   5302.4   4989.71   22158   7662.56   6693.63   4412.19   
    G74270   10060.6   6502.83   297.032   115.507   397.306   261.864   1399.25   3488.46   2995.85   1472.46   2026.57   537.095   935.669   606.438    
   G74270   9492.4   4574.97   506.054   288.768   327.193   527.636   861.574   6589.79   2372.82   1589.15   1395.78   769.837   1151.59   615.988      
 G74270   9318.15   20012.7   10330.1   267.767   0   0   0   12057.4   11963.5   17414   0   0   0   0       
G74270   9265.12   11326.2   1430.15   0   0   33.2215   53.4435   4762.92   9749.78   7251.19   0   17.9032   215.924   630.313  
     G35720   7575.73   11826.7   8415.9   6011.63   1834.62   582.353   1230.82   7020.41   13918.8   13757.8   1207.89   1432.25   359.873   816.542

i need to have an output file (summing up the values in rows according to the first column) like
Code:
       
  LABEL   A   B   C   D   E   F   G   H   I   J   K   L   M   N     
  G02100   87590.7   34113.3   16795.12   59139.76   274054.8   271065.3   238992.3   62919.78   23661.98   15500.69   14593.1   126187.3   270912.7   42576.4    
   G02780   16068.13   27565.06   15982.71   18730.55   7583.61   1844.772   4589.66   12240.91   22914.55   19225.4   11291.08   4189.344   3454.779   4894.48    
   G09200   6974.73   7790.54   3865.08   7108.96   3778.3   1625.9   4294.91   4277.2   4259.59   4709.11   7341.28   3574.67   3382.8   4967.7      
 G74270   48818.07   54325   16127.72   11104.44   5550.599   2843.372   10113.78   35867.64   32384.35   32716.51   25580.35   8987.395   8996.813   6264.929     
  G35720   7575.73   11826.7   8415.9   6011.63   1834.62   582.353   1230.82   7020.41   13918.8   13757.8   1207.89   1432.25   359.873   816.542

Please help me guys

Thanks
Last edited by AAWT; 04-10-2012 at 08:07 AM..
# 2  
Old 04-10-2012
Hi, try this:
Code:
awk 'NR==1{n=NF;print;next} {A[$1];for(i=2;i<=n;i++) S[$1,i]+=$i} END{for(label in A){p=label; for (i=2;i<=n;i++) p=p FS S[label,i]; print p}}' infile

# 3  
Old 04-10-2012
Thanks Scrutinizer,
it did the summing up but problem is, all the summing results are in the columns (Label; A; B; C ;D;E;F;G;H;I;J;K;L;M;N) its only in first column with all the readings.
its a tab delimited file with 15 columns and >20, 000 rows.
So ? Smilie


regards
# 4  
Old 04-10-2012
Could you elaborate, I don't know what you mean. Do you mean output should be TAB-separated? :
Code:
awk 'NR==1{n=NF;$1=$1;print;next} {A[$1];for(i=2;i<=n;i++) S[$1,i]+=$i} END{for(label in A){p=label; for (i=2;i<=n;i++) p=p OFS S[label,i]; print p}}' OFS='\t' infile


Code:
LABEL	A	B	C	D	E	F	G	H	I	J	K	L	M	N
G09200	6974.73	7790.54	3865.08	7108.96	3778.3	1625.9	4294.91	4277.2	4259.59	4709.11	7341.28	3574.67	3382.8	4967.7
G02100	87590.7	34113.3	16795.1	59139.8	274055	271065	238992	62919.8	23662	15500.7	14593.1	126187	270913	42576.4
G37770	6924.22	6072.77	1353.14	708.795	140.226	125.069	437.265	5715.5	2883.18	2494.85	5448.92	698.224	503.822	100.277
G74270	48818.1	54325	16127.7	11104.4	5550.6	2843.37	10113.8	35867.6	32384.3	32716.5	25580.3	8987.4	8996.81	6264.93
G35720	7575.73	11826.7	8415.9	6011.63	1834.62	582.353	1230.82	7020.41	13918.8	13757.8	1207.89	1432.25	359.873	816.542
G02780	16068.1	27565.1	15982.7	18730.5	7583.61	1844.77	4589.66	12240.9	22914.6	19225.4	11291.1	4189.34	3454.78	4894.48

# 5  
Old 04-10-2012
Smilie yes exactly this i need,

Thanks a lot

Regards
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to transpose pieces of data in a column to multiple rows?

Hello Everyone, I am very new to the world of regular expressions. I am trying to use grep/sed for the following: Input file is something like this and there are multiple such files: abc 1 2 3 4 5 ***END*** abc 6 7 8 9 ***END*** abc 10 (2 Replies)
Discussion started by: shellnewuser
2 Replies

2. Shell Programming and Scripting

How to separate rows of data into another column?

I have data such as below where the value in second field is the same as that in the row after. 123456,22222,John,0,xyz 234567,22222,John1,1,cde 43212,3333,Jean,3,pip 84324,3333,Abel,2,cat I'd like to rearrange the output like below to put such records beside each other and separated with... (5 Replies)
Discussion started by: james2009
5 Replies

3. Shell Programming and Scripting

Data rearranging from rows to column

Hello Everyone, I have a input file looks like -0.005-0.004-0.003-0.002-0.00100.0010.0020.0030.0040.005My desired output should look like -0.005 -0.004 -0.003 -0.002 -0.001 0 0.001 0.002 0.003 0.004 0.005I had some success in getting the desired output. But i face a problem when i... (15 Replies)
Discussion started by: dinesh.n
15 Replies

4. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

5. Shell Programming and Scripting

Summing up values of rows of numbers

data file contains failed=24 error=23 error=163 failed=36 error=903 i need to get a total count of each value above. i'm looking for the most efficient method to do this as the datafile i provided is just a sample. the actual data can be several hundred thousands of lines. so from... (3 Replies)
Discussion started by: SkySmart
3 Replies

6. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

7. Shell Programming and Scripting

[Solved] Compare column data in all the rows

Hi.. In the below sorted input file.. I am comparing the first 3 columns of data one by one row and it is a pipeline delimitter file.. AA|BB|CC|line1 AA|BB|CC|ine4 AA|BB|CC|line2 BB|CC|DD|line3 BB|CC|DD|line5 If first 3 columns of data matches with any record in the file the... (4 Replies)
Discussion started by: NareshN
4 Replies

8. Shell Programming and Scripting

Transpose Column of Data to Rows

I can no longer find my commands, but I use to be able to transpose data with common fields from a single column to rows using a command line. My data is separated as follows: NAME=BOB ADDRESS=COLORADO PET=CAT NAME=SUSAN ADDRESS=TEXAS PET=BIRD NAME=TOM ADDRESS=UTAH PET=DOG I would... (7 Replies)
Discussion started by: docdave78
7 Replies

9. Shell Programming and Scripting

column data to rows every n line

Hi every one, I am trying to organise an input text file like: input 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 into an output as following: output file 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 (5 Replies)
Discussion started by: nxp
5 Replies

10. UNIX for Dummies Questions & Answers

Find different column numbers among rows in data

I want to find the different column numbers among rows in a file. For example: A001 a b c d e ... N A002 a b c d e ... N A003 a b c d e ... N+1 A004 a b c d e ... N A005 a b c d e ... N+2 : : For most of the lines I will have N columns (say 1000 rows) in each line except the line 3... (5 Replies)
Discussion started by: AMBER
5 Replies
Login or Register to Ask a Question