Sponsored Content
Top Forums Shell Programming and Scripting Sum of a column as new column based on header in a script Post 303026037 by mkathi on Sunday 18th of November 2018 09:13:38 PM
Old 11-18-2018
Quote:
Originally Posted by RudiC
How about


Code:
awk -F, '
NR == 1         {for (i=1; i<=NF; i++) if ("," MCOL "," ~ "," $i ",") COL[++CNT] = i
                 print $0
                }
FNR == 1        {next
                }

NR == FNR       {for (i=1; i<=CNT; i++) SUM[i] += $(COL[i])
                 next
                }
                {for (i=1; i<=CNT; i++) $(NF+1) = SUM[i]
                 print
                }
' OFS="," MCOL="c1,c2" file file

Yes, you read correctly, supply the input file twice - once to determine the values, once to print them.
Nice thanks ,this is a very interesting way to write code but here is the output I am getting when I execute this code

Code:
c1,c2,c3,c4,c5
c1,c2,c3,c4,c5,30,50
10,20,30,40,50,30,50
20,30,40,50,60,30,50

i tried to say

Code:
awk -F, '
NR == 1         {for (i=1; i<=NF; i++) if ("," MCOL "," ~ "," $i ",") COL[++CNT] = i
                 print $0,MCOL[i]
                }
FNR == 1        {next
                }

NR == FNR       {for (i=1; i<=CNT; i++) SUM[i] += $(COL[i])
                 next
                }
                {for (i=1; i<=CNT; i++) $(NF+1) = SUM[i]
                 print
                }
' OFS="," MCOL="c1,c2" file file

some thing like this but it is printing the whole line ideally this is how i want my out put

Code:
c1,c2,c3,c4,c5,sum_c1,sum_c3
10,20,30,40,50,30,50
20,30,40,50,60,30,50

is this possible. thanks for the input

------ Post updated at 02:13 AM ------

I am also trying something like this but I am pretty sure i am writing the code wrongly as i am getting syntax error. I am trying out the code on ideone so it doesnt print the error out unfortunately.

Please can you point out what is wrong with my approach or syntax here

Code:
#!/usr/bin/sh
col="c1,c3"   
idx=   
sum=0         
{
IFS=, read -ra values <<< "$col"
for v in "${values[@]}"
do
  for i in ${v//,/ } do
     while IFS=, read -r -a line; do
     sum=$(( sum + ${line[$i]} 
	 echo $sum
	 done
  done
done
}<testfile


Thanks.
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How do I sum one column based on another column?

Hi, I am new to this forum and new to awk. I have a file that contains 2 columns. Heres an example of what it looks like: 10 + 20 + 40 + 50 - 70 - So the file is tab-delimited. What I want to do is add 10 to column 1 whenever column 2 is + and substract 10 from column 1... (1 Reply)
Discussion started by: phil_heath
1 Replies

2. Shell Programming and Scripting

Sum a column value based on multiple keys

Hi, I have below as i/p file: 5ABC 36488989 K 000010000ASB BYTRES 5PQR 45757754 K 000200005KPC HGTRET 5ABC 36488989 K 000045000ASB HGTRET 5GTH 36488989 K 000200200ASB BYTRES 5FTU ... (2 Replies)
Discussion started by: nirnkv
2 Replies

3. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

4. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

5. Shell Programming and Scripting

Sum Of Column Based On Column Condition

I have a following inputfile MT,AP,CDM,TTML,MUM,GS,SUCC,3 MT,AP,CDM,TTSL,AP,GS,FAIL,9 MT,AP,CDM,RCom,MAH,GS,SUCC,3 MT,AP,CDM,RTL,HP,GS,SUCC,1 MT,AP,CDM,Uni,UPE,GS,SUCC,2 MT,AP,CDM,Uni,MUM,GS,SUCC,2 TTSL,AP,GS,MT,MAH,CDM,SUCC,20 TTML,AP,GS,MT,MAH,CDM,FAIL,10... (2 Replies)
Discussion started by: siramitsharma
2 Replies

6. UNIX for Dummies Questions & Answers

Sum based on column 1

i have file input aaa ccc,45567,rterw,1 bbb dcs,564543,hjghgh,1 aaa ccc,454,rterw,6 i want to sum based on column 1 expected output aaa ccc,7 bbb dcs,1 (4 Replies)
Discussion started by: radius
4 Replies

7. UNIX for Dummies Questions & Answers

Sum based on certain column

I have file 1 1/1/2013 A 553.0763397 96 16582 X1 X3 X5 X7 X9 1/1/2013 B 600.8333588 195 11992 X2 X3 X6 X7 X9 1/1/2013 B 459.8333588 195 11992 X1 X3 X6 X7 X9 1/2/2013 A 844.2973022 306 19555 X1 ... (12 Replies)
Discussion started by: radius
12 Replies

8. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

9. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

10. UNIX for Beginners Questions & Answers

Sum in file based column

Hi All, I have a file as below and want to sum based on the id in the first column Input 10264;ATE; 12 10265;SES;11 10266AUT;50 10264;ATE;10 10265;SES;13 10266AUT;89 10264;ATE;1 10265;SES;15 10266AUT;78 Output 10264;ATE; 23 10265;SES;39 10266AUT;139 (6 Replies)
Discussion started by: arunkumar_mca
6 Replies
All times are GMT -4. The time now is 11:16 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy