Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Total of columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Total of columns
# 1  
Old 01-12-2012
Total of columns
Input file

Code:
 
cll_0014_spp,84,uranus,disk33,45
 
cll_0014_spp,84,pluto,disk3,10
cll_0014_spp,450,pluto,disk4,1
cll_0014_spp,453,pluto,disk46,19
 
cll_0014_spp,84,neptune,disk34,60
cll_0014_spp,450,neptune,disk4,1
cll_0014_spp,68,neptune,disk54,59
 
cll_1234_spp,66,satrun,1000
cll_1234_spp,28,satrun,1024
cll_1234_spp,49,satrun,786
 
cll_3456_spp,1614,pluto,58
cll_3456_spp,1656,pluto,120


Output needed

Code:
cll_0014_spp,uranus,45
cll_0014_spp,pluto,11
cll_0014_spp,neptune,119
cll_1234_spp,satrun,2810
cll_1234_spp,pluto,178

Basically i need to total up column 5 based on unique combination sets of (column 1 and 3)

Thanks
Last edited by greycells; 01-13-2012 at 09:01 AM..
# 2  
Old 01-12-2012
Code:
awk -F, '!/^ *$/{a[$1OFS$3]+=$NF} END{for(i in a){print i OFS a[i]}}' OFS=, infile

--ahamed
This User Gave Thanks to ahamed101 For This Post:
greycells
# 3  
Old 01-13-2012
Hi ... can i add another scenario to this ...

if Column 1 and 2 are also same ... with the above logic .. can i get a output like this ...in which add the column 5 seprately for the ones with same $1 and $2 and append "(shared)" to it
Code:
cll_0014_spp,uranus,45
cll_0014_spp,pluto,10(shared)
cll_0014_spp,pluto,20
cll_0014_spp,neptune,61(shared)
cll_0014_spp,neptune,59
cll_1234_spp,satrun,2810
cll_1234_spp,pluto,178

Last edited by Franklin52; 01-15-2012 at 03:43 PM.. Reason: Please use code tags for data and code samples, thank you
# 4  
Old 01-13-2012
Quote:
Originally Posted by greycells
Hi ... can i add another scenario to this ...

if Column 1 and 2 are also same ... with the above logic .. can i get a output like this ...in which add the column 5 seprately for the ones with same $1 and $2 and append "(shared)" to it

cll_0014_spp,uranus,45
cll_0014_spp,pluto,10(shared)
cll_0014_spp,pluto,20
cll_0014_spp,neptune,61(shared)
cll_0014_spp,neptune,59
cll_1234_spp,satrun,2810
cll_1234_spp,pluto,178
maybe try this Smilie
Code:
# awk -F',' '{if($0!~/^ *$/)if(x==0){f=$1FS$2};{
> x++;a[x]=$1FS$2;b[x]=$NF;c[x]=$3;}}
> END{for(i=1;i<=x;i++)if(f==a[i]){
> sub(",.*","",a[i]);print a[i],c[i],b[i]"(shared)"}
> else{sub(",.*","",a[i]);if(a[i]~/^ * $/&&ra!=0){cc=1;}
> else{ra+=b[i];w=a[i];ww=c[i];cc=0};if(cc==1){print w,ww,ra;cc=0;ra=0}}
> print a[x],c[x],ra}' OFS=',' infile
cll_0014_spp,uranus,45(shared)
cll_0014_spp,pluto,10(shared)
cll_0014_spp,pluto,20
cll_0014_spp,neptune,60(shared)
cll_0014_spp,neptune,60
cll_1234_spp,satrun,2810
cll_3456_spp,pluto,178

regards
ygemici
# 5  
Old 01-13-2012
Thank ! but not quite working ... if u look at my last output thats what i need ...

for example

Code:
 
cll_0014_spp,84,uranus,disk33,45
 
cll_0014_spp,84,pluto,disk3,10
cll_0014_spp,450,pluto,disk4,1
cll_0014_spp,453,pluto,disk46,19
 
cll_0014_spp,84,neptune,disk34,60
cll_0014_spp,450,neptune,disk4,1
cll_0014_spp,68,neptune,disk54,59

1)I need to calculate $5 total based on if ($1 $3) are same ..
2) also if ($1 $2 )for those are not unique mark them as "shared" but also total up "shared" numbers for which ( $1 $3) are same

like
Code:
 
cll_0014_spp,uranus,45 ( shared)  --> ($1 $3)  appears only once and had a non unique ( $1 $2) so shared 
cll_0014_spp,pluto,11(shared) --> ( $1 $3 ) appears 3 times and in 2 of those ( $1 $2 ) is not unique so its shared and gets added up
cll_0014_spp,pluto,19 --> the rest of ($1 $3) with unique ($1 $2) gets added up
cll_0014_spp,neptune,61(shared) --> ($1 $3) appear 3 times with 2 of them have non unique ($1 $2) and get added up to 61
cll_0014_spp,neptune,59 --> the remaining ( $1 $3) with unique ($1 $2)

and so on ..

thx
Image
Last edited by greycells; 01-13-2012 at 07:08 PM..
# 6  
Old 01-14-2012
Try this...
Code:
awk -F, '!/^ *$/{a[$0];b[$1$2]++}
END{
  for(i in a)
  {
    l=split(i,arr,",")
    if(b[arr[1]arr[2]] > 1)
      s[arr[1]","arr[3]]+=arr[l]
    else
      o[arr[1]","arr[3]]+=arr[l]
  }
  for(i in s) print i","s[i]" (shared)"
  for(i in o) print i","o[i]
}' infile

--ahamed
This User Gave Thanks to ahamed101 For This Post:
greycells
# 7  
Old 01-14-2012
Quote:
Originally Posted by greycells
Thank ! but not quite working ... if u look at my last output thats what i need ...

for example

Code:
 
cll_0014_spp,84,uranus,disk33,45
 
cll_0014_spp,84,pluto,disk3,10
cll_0014_spp,450,pluto,disk4,1
cll_0014_spp,453,pluto,disk46,19
 
cll_0014_spp,84,neptune,disk34,60
cll_0014_spp,450,neptune,disk4,1
cll_0014_spp,68,neptune,disk54,59

1)I need to calculate $5 total based on if ($1 $3) are same ..
2) also if ($1 $2 )for those are not unique mark them as "shared" but also total up "shared" numbers for which ( $1 $3) are same

like
Code:
 
cll_0014_spp,uranus,45 ( shared)  --> ($1 $3)  appears only once and had a non unique ( $1 $2) so shared 
cll_0014_spp,pluto,11(shared) --> ( $1 $3 ) appears 3 times and in 2 of those ( $1 $2 ) is not unique so its shared and gets added up
cll_0014_spp,pluto,19 --> the rest of ($1 $3) with unique ($1 $2) gets added up
cll_0014_spp,neptune,61(shared) --> ($1 $3) appear 3 times with 2 of them have non unique ($1 $2) and get added up to 61
cll_0014_spp,neptune,59 --> the remaining ( $1 $3) with unique ($1 $2)

and so on ..

thx
Image
i dont understand exactly but i try some code for your request ,
i hope this help you Smilie

for example your data is
Code:
# cat yourdata
cll_0014_spp,84,uranus,disk33,45

cll_0014_spp,84,pluto,disk3,10
cll_0014_spp,450,pluto,disk4,1
cll_0014_spp,453,pluto,disk46,19
cll_0014_spp,453,pluto,disk46,1900000000
cll_0014_spp,453,pluto,disk46,3000900000000

cll_0014_spp,84,neptune,disk34,60
cll_0014_spp,450,neptune,disk4,1
cll_0014_spp,68,neptune,disk54,59
cll_0014_spp,68,neptune,disk54,6000000
cll_0014_spp,68,neptune,disk54,1000000000

cll_1234_spp,66,satrun,1000
cll_1234_spp,28,satrun,1024
cll_1234_spp,49,satrun,786

cll_3456_spp,1614,pluto,58
cll_3456_spp,1656,pluto,120

Code:
# awk -F',' '/^ $/{empty++}{if(x==0){f=$1$2};{x++;a[x]=$0;b[x]=$NF}}
END{for(i=1;i<=x+empty;i++){
if(a[i]!~/^ *$/){split(a[i],aa);
if(f==aa[1]aa[2]){share++;shared[++c]=aa[1] FS aa[3] FS b[i]+b[i+1]"(shared)";}
else{if(share!=1){t+=b[i]}if(share==1&&a[i+1]!~/^ *$/){split(a[i+1],aa);notshared[++c]=aa[1] FS aa[3] FS b[i+1]}}}
else{share=0;if(t)notshared[++c]=aa[1] FS aa[3] FS t;t=0}}
for(j=1;j<=c;j++)print shared[j] notshared[j]}' yourdata

cll_0014_spp,uranus,45(shared)
cll_0014_spp,pluto,11(shared)
cll_0014_spp,pluto,19
cll_0014_spp,pluto,1900000000
cll_0014_spp,pluto,3000900000000
cll_0014_spp,neptune,61(shared)
cll_0014_spp,neptune,59
cll_0014_spp,neptune,6000000
cll_0014_spp,neptune,1000000000
cll_1234_spp,satrun,2810
cll_3456_spp,pluto,178

regards
ygemici
This User Gave Thanks to ygemici For This Post:
greycells
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All, I'm embedding SQL query in Script which gives following output: Assignee Group Total ABC Group1 17 PQR Group2 5 PQR Group3 6 XYZ Group1 10 XYZ Group3 5 I have saved the above output in a file. How do i sum up the contents of this output so as to get following output: ... (4 Replies)
Discussion started by: Khushbu
4 Replies

2. Shell Programming and Scripting

Sub TOTAL Columns

Input 435,BL_lmapm03,rrr,RDF1+TDEV,0cef,45,mask1 435,BL_lmapm03,rrr,TDEV,080a,50,mask2 435,BL_lmapm02,fff,RDF1+TDEV,0ceg,45,mask4 435,BL_lmapm02,fff,TDEV,080b,60,mask6 435,BL_lmapm06,hhh,TDEV,080f,60,mask9 Output 435,BL_lmapm03,rrr,RDF1+TDEV,0cef,45,mask1... (4 Replies)
Discussion started by: greycells
4 Replies

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

4. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

6. Shell Programming and Scripting

Help with sum total number of record and total number of record problem asking

Input file SFSQW 5192.56 HNRNPK 611.486 QEQW 1202.15 ASDR 568.627 QWET 6382.11 SFSQW 4386.3 HNRNPK 100 SFSQW 500 Desired output file SFSQW 10078.86 3 QWET 6382.11 1 QEQW 1202.15 1 HNRNPK 711.49 2 ASDR 568.63 1 The way I tried: (2 Replies)
Discussion started by: patrick87
2 Replies

7. Shell Programming and Scripting

Single command for add 2 columns and remove 2 columns in unix/performance tuning

Hi all, I have created a script which adding two columns and removing two columns for all files. Filename: Cust_information_1200_201010.txt Source Data: "1","Cust information","123","106001","street","1-203 high street" "1","Cust information","124","105001","street","1-203 high street" ... (0 Replies)
Discussion started by: onesuri
0 Replies

8. Shell Programming and Scripting

Calculate total space, total used space and total free space in filesystem names matching keyword

Good afternoon! Im new at scripting and Im trying to write a script to calculate total space, total used space and total free space in filesystem names matching a keyword (in this one we will use keyword virginia). Please dont be mean or harsh, like I said Im new and trying my best. Scripting... (4 Replies)
Discussion started by: bigben1220
4 Replies

9. UNIX for Dummies Questions & Answers

grep running total/ final total across multiple files

Ok, another fun hiccup in my UNIX learning curve. I am trying to count the number of occurrences of an IP address across multiple files named example.hits. I can extract the number of occurrences from the files individually but when you use grep -c with multiple files you get the output similar to... (5 Replies)
Discussion started by: MrAd
5 Replies
Login or Register to Ask a Question