Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sum of multiple columns based on few conditions

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum of multiple columns based on few conditions
# 1  
Old 06-19-2014
Sum of multiple columns based on few conditions
Input file:
Code:
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003200,0000111
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00001,0003400,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00007,0003100,0000200
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003000,0000100
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003100,0000100
0013,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100

i need to sum of multiple columns(8th,9th,10th) based on few conditions on the above file.
1) Sort by column 1
2)Sum of columns(8th,9th,10th) based on unique column 1 and column 5.
3)print the original record and print the output in a separate line(second column value should be T
and 6th column should be blank in the output line)

Output:
Code:
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003000,0000100
0012,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003100,0000100
0012,T,2020,K,047225000000,                        ,B,00002,0006100,0000200
0013,A,2020,K,047225000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0013,T,2020,K,047225000000,                       ,B,00001,0003300,0000100
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003200,0000111
0014,A,2020,K,011115000000,xyxxxxxxxxxxxxxx ,B,00001,0003300,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00001,0003400,0000100
0014,A,2020,K,011116000000,xyxxxxxxxxxxxxxx ,B,00007,0003100,0000200
0014,T,2020,K,011115000000,                        ,B,00002,0006500,0000211
0014,T,2020,K,011116000000,                        ,B,00008,0006500,0000300

I tried the below command, the total is coming correct. The formatting is not correct and need the output mentioned above
Code:
awk -F',' '{a[$1"."$5]+=$8;b[$1"."$5]+=$9;c[$1"."$5]+=$10;f[$1"."$5]=$2;
g[$1"."$5]=$1;h[$1"."$5]=$3;
j[$1"."$5]=$4;k[$1"."$5]=$5;
r[$1"."$5]=$7
l[$1"."$5]=$6;m[$1"."$5]=$7}END{for(i in a)print g[i]",",f[i]",",h[i]",",j[i]",",k[i]",",l[i]",",r[i]",",a[i]",",b[i]",",c[i] |"sort -t ',' -n -k1,1"}' file.txt

Can anyone please assist me?
Last edited by bartus11; 06-19-2014 at 12:19 PM.. Reason: Please use [code][/code] tags.
# 2  
Old 06-19-2014
assuming you have GNU awk (gawk - preferably the latest 4.1.1): gawk -f vin.awk myFile where vin.awk is:
Code:
#!/bin/gawk -f
BEGIN {
  FS=OFS=","
  sc="8,9,10"
  sn=split(sc,scA,FS)
}
{
  idx=($1 SUBSEP $5)
  block[idx]=(idx in block)?block[idx] ORS $0:$0
  line[idx]=$0
  for(i=1; i in scA;i++)
     sumA[idx][scA[i]]=sprintf("%07d", sumA[idx][scA[i]]+$scA[i])
}
END {
  for(i in block) {
    print block[i]
    lineN=split(line[i],lineA,FS)
    lineA[2]="T"
    lineA[6]=""
    for(j=1; j in scA;j++)
       lineA[scA[j]]=sumA[i][scA[j]]
    for(j=1;j<=lineN;j++)
       printf("%s%s%s", (j==1)?"":OFS, lineA[j],(j==lineN)?ORS:"")
  }
}

Last edited by vgersh99; 06-19-2014 at 01:46 PM..
# 3  
Old 06-19-2014
Sum of multiple columns based on few conditions
Thanks for the response.
gawk is not installed on our server , so this code is not working.
Can you try using awk ?
# 4  
Old 06-19-2014
ok, try this with awk (use 'nawk' if on Solaris):
Code:
#!/bin/awk -f
BEGIN {
  FS=OFS=","
  sc="8,9,10"
  sn=split(sc,scA,FS)
}
{
  idx=($1 SUBSEP $5)
  block[idx]=(idx in block)?block[idx] ORS $0:$0
  line[idx]=$0
  for(i=1; i in scA;i++)
     sumA[idx,scA[i]]=sprintf("%07d", sumA[idx,scA[i]]+$scA[i])
}
END {
  for(i in block) {
    print block[i]
    lineN=split(line[i],lineA,FS)
    lineA[2]="T"
    lineA[6]=""
    for(j=1; j in scA;j++)
       lineA[scA[j]]=sumA[i,scA[j]]
    for(j=1;j<=lineN;j++)
       printf("%s%s%s", (j==1)?"":OFS, lineA[j],(j==lineN)?ORS:"")
  }
}

# 5  
Old 06-19-2014
Thanks a lot for ur help!
The code is working fine. One small issue is that the length of column 8 is 5 and its coming as 7 for column 8, column 9 and column 10 in the T record. How to fix this?
Code:
0012,T,2020,K,047225000000,                   ,B,0000002,0006100,0000200

It should be like this
Code:
0012,T,2020,K,047225000000,                   ,B,00002,0006100,0000200

Also can u please explain me the code?
Last edited by vgersh99; 06-19-2014 at 06:10 PM.. Reason: code tags, please!
# 6  
Old 06-19-2014
this should be a bit prettier:
Code:
#!/bin/awk -f
BEGIN {
  FS=OFS=","
  sc="8,9,10"
  sn=split(sc,scA,FS)
}
{
  w6=length($6)
  idx=($1 SUBSEP $5)
  block[idx]=(idx in block)?block[idx] ORS $0:$0
  line[idx]=$0
  for(i=1; i in scA;i++)
     sumA[idx,scA[i]]=sprintf("%0*d", length($scA[i]),sumA[idx,scA[i]]+$scA[i])
}
END {
  for(i in block) {
    print block[i]
    lineN=split(line[i],lineA,FS)
    lineA[2]="T"
    lineA[6]=sprintf("%*s", w6, "")
    for(j=1; j in scA;j++)
       lineA[scA[j]]=sumA[i,scA[j]]
    for(j=1;j<=lineN;j++)
       printf("%s%s%s", (j==1)?"":OFS, lineA[j],(j==lineN)?ORS:"")
  }
}

This User Gave Thanks to vgersh99 For This Post:
vinus
# 7  
Old 06-20-2014
Thanks you so much for ur help. The code is working fine.

If you have the time, can you please explain me the code.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (12 Replies)
Discussion started by: as7951
12 Replies

2. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

3. Shell Programming and Scripting

Merge records based on multiple columns

Hi, I have a file with 16 columns and out of these 16 columns 14 are key columns, 15 th is order column and 16th column is having information. I need to concate the 16th column based on value of 1-14th column as key in order of 15th column. Here are the example file Input File (multiple... (3 Replies)
Discussion started by: Ravi Agrawal
3 Replies

4. Shell Programming and Scripting

Sort based on Multiple Columns in UNIX

Hi, I would like to sort a list in different ways: 1> Unique based on Field 1 with highest Field 4 For Instance Input: 1678923450;11112222333344;11-1x;2_File.xml 1678923450;11112222333344;11-1x;5_File.xml 1234567890;11113333222244;11-1x;3_File.xml Output: ... (7 Replies)
Discussion started by: DevendraG
7 Replies

5. Shell Programming and Scripting

sort by based on multiple columns

Hi, Is there any way to sort a file in cshell by sort command, sorting it by multiple fields, like to sort it first by the second column and then by the first column. Thanks forhead (1 Reply)
Discussion started by: Takeeshe
1 Replies

6. 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

7. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

Sorting based on Multiple columns

Hi, I have a requirement whereby I have to sort a flat file based on Multiple Columns (similar to ORDER BY Clause of Oracle). I am getting 10 columns in the flat file and I want the file to be sorted on 1st, 3rd, 4th, 7th and 9th columns in ascending order. The flat file is pipe seperated. Any... (15 Replies)
Discussion started by: dharmesht
15 Replies

10. Shell Programming and Scripting

awk 3 files to one based on multiple columns

Hi all, I have three files, one is a navigation file, one is a depth file and one is a file containing the measured field of gravity. The formats of the files are; navigation file: 2006 320 17 39 0 0 *nav 21.31542 -157.887 2006 320 17 39 10 0 *nav 21.31542 -157.887 2006 320 17 39 20 0... (2 Replies)
Discussion started by: andrealphus
2 Replies
Login or Register to Ask a Question