Awk: Modifying columns based on comparison


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk: Modifying columns based on comparison
# 8  
Old 04-10-2014
This code worked for both the samples you have provided and genereted the expected output you have provided

Code:
awk 'BEGIN {FS = OFS = ","}
  {if ($1 in a)
    {if (a11[$1] == $13)
      {split(a0[$1], b, ","); print $1,($2 + b[2]), ($3 + b[3]), ($4 + b[4]), ($5 + b[5]), ($6 + b[6]), ($7 + b[7]), ($8 + b[8]), ($9 + b[9]), ($10 + b[10]), ($11), ($12 + b[12]), (b[13]), $14}
    else if (a13[$1] == $11)
      {split(a0[$1], b, ","); print $1,($2 + b[2]), ($3 + b[3]), ($4 + b[4]), ($5 + b[5]), ($6 + b[6]), ($7 + b[7]), ($8 + b[8]), ($9 + b[9]), ($10 + b[10]), (b[11]), ($12 + b[12]), ($13), $14}}
  else
    {a[$1]++; a0[$1] = $0; a11[$1] = $11; a13[$1] = $13}
  }' file


Last edited by SriniShoo; 04-10-2014 at 05:46 AM.. Reason: comments
# 9  
Old 04-10-2014
Thanks. Yes it is near but when i ran this on my actual input it didn't. Saw some additional lines which were not having same $1. Can you please suggest

InputFile
Code:
SSCHPD01S201404010924210318.PDSN,0,0,16,0,906,20,7,619,523,19,476,2571,20140401092421
SSCHPD01S201404011225380559.PDSN,0,0,3,0,823,19,4,615,632,4,483,2583,20140401122538
SSCHPD01S201404011225380559.PDSN,1,,,,,,,,1,2,,4,20140401122538
SSCHPD01S201404010924210317.PDSN,,,,,,,,,,4,,4,20140401092421
SSCHPD01S201404010924210317.PDSN,0,0,16,0,906,20,7,619,523,4,476,2571,20140401092421
SSCHPD01S201404011225380579.PDSN,0,0,3,0,823,19,4,615,632,4,483,2583,20140401122538
SSCHPD01S201404011225380579.PDSN,2,,,,,,,,,2,,4,20140401122538
SSCHPD01S201404010924210318.PDSN,1,1,1,1,1,1,1,1,1,10,,19,20140401092421
SSCHPD01S201404010924210319.PDSN,10,10,10,10,10,10,10,10,10,10,20,120,20140401092421


Last edited by siramitsharma; 04-10-2014 at 12:51 PM.. Reason: Comments
# 10  
Old 04-10-2014
Below is the code modified per your requirements. Also, the below code works better with larger files than previous code as we are freeing the memory after usage.
Code:
awk 'BEGIN {FS = OFS = ","}
  {if ($1 in a1)
    {if (a11[$1] == $13)
      {split(a[$1], b, ","); print $1,($2 + b[2]), ($3 + b[3]), ($4 + b[4]), ($5 + b[5]), ($6 + b[6]), ($7 + b[7]), ($8 + b[8]), ($9 + b[9]), ($10 + b[10]), ($11), ($12 + b[12]), (b[13]), $14;
	    delete a[$1]; delete a1[$1]; delete a11[$1]; delete a13[$1]; delete b}
    else if (a13[$1] == $11)
      {split(a[$1], b, ","); print $1,($2 + b[2]), ($3 + b[3]), ($4 + b[4]), ($5 + b[5]), ($6 + b[6]), ($7 + b[7]), ($8 + b[8]), ($9 + b[9]), ($10 + b[10]), (b[11]), ($12 + b[12]), ($13), $14;
	    delete a[$1]; delete a1[$1]; delete a11[$1]; delete a13[$1]; delete b}}
  else
    {a1[$1]; a[$1] = $0; a11[$1] = $11; a13[$1] = $13}
  } END {for(x in a) {print a[x]}}' file

# 11  
Old 04-14-2014
Thanks
# 12  
Old 04-14-2014
Another awk:
Code:
awk '
  {
    split (A[$1],F)
    if(F[11]==$13) {
      $11=$13=0
      for(i=2; i<NF; i++) $i+=F[i]
    }
    A[$1]=$0
  }
  END{
    for(i in A) print A[i]
  }
' FS=, OFS=, file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

2. Shell Programming and Scripting

Merging two file based on comparison of first columns

Respected Members. Hello. This is my first post in the forum. I will try to follow all the rules as prescribed by the forum. In case of non-compliance, I request you to kindly give me some more time to understand and abide by them. I am working on two files. I wish to merge the two files... (6 Replies)
Discussion started by: manojmalhotra
6 Replies

3. Shell Programming and Scripting

Merging two file based on comparison of first columns

Respected Members. Hello. This is my first post in the forum. I will try to follow all the rules as prescribed by the forum. In case of non-compliance, I request you to kindly give me some more time to understand and abide by them. I am working on two files. I wish to merge the two files... (1 Reply)
Discussion started by: manojmalhotra
1 Replies

4. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

5. Shell Programming and Scripting

How to add columns based on a pattern using awk?

Hi, I have a file with more than 1000 lines with ~14 columns. I need to find all the lines with matching value in column 14 and then add column 6 in all the lines before printing them out.. e.g if this is the input file: abc test input 10 for process 2345 abc test input 15 for process 2348... (1 Reply)
Discussion started by: xkdasari
1 Replies

6. Shell Programming and Scripting

Help with awk replacing identical columns based on another file

Hello, I am using Awk in UBUNTU 12.04. I have a file like following with three fields and 44706 rows. F1 A A F2 G G F3 A T I have another file like this: AL_1 F1 A A AL_2 F1 A T AL_3 F1 A A AL_1 F2 G G AL_2 F2 G A AL_3 F2 G G BO_1 F1 A A BO_2 F1 A T... (6 Replies)
Discussion started by: Homa
6 Replies

7. Shell Programming and Scripting

awk based script to ignore all columns from a file which contains character strings

Hello All, I have a .CSV file where I expect all numeric data in all the columns other than column headers. But sometimes I get the files (result of statistics computation by other persons) like below( sample data) SNO,Data1,Data2,Data3 1,2,3,4 2,3,4,SOME STRING 3,4,Inf,5 4,5,4,4 I... (9 Replies)
Discussion started by: ks_reddy
9 Replies

8. Shell Programming and Scripting

Please Help!!!! Awk for summing columns based on selected column value

a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff,gg,hh,ii a thru ii are digits and strings.... The awk needed....if coloumn 9 == i (coloumn 9 is string ), output the sum of x's(coloumn 22 ) in all records and sum of y's (coloumn 23 ) in all records in a file (records.txt).... (6 Replies)
Discussion started by: BrownBob
6 Replies

9. Shell Programming and Scripting

awk : extracting unique lines based on columns

Hi, snp.txt CHR_A SNP_A BP_A_st BP_A_End CHR_B BP_B SNP_B R2 p-SNP_A p-SNP_B 5 rs1988728 74904317 74904318 5 74960646 rs1427924 0.377333 0.000740085 0.013930081 5 ... (12 Replies)
Discussion started by: genehunter
12 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