awk command to compare files by column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers awk command to compare files by column
# 1  
Old 06-30-2015
awk command to compare files by column

So I have this issue. I have 4 files. the first one is the master file who has all possible combinations:

file 1
Code:
-      a
-      b
-      c
-      d
-      e

the other three have some of the letters and a number instead of - for example

file 2
Code:
34   a
5     c

file 3
Code:
10   b
12   c

what I want to do is to create a new file that uses the file 1, searches the other file(s) and creates the following table
Code:
a    -     34 
b    -       0 
c    -       5 
d    -       0 
e    -       0

if file 2 has the letter it should spit out the number
if file 2 does not have the letter it should spit out zero "0"

I thought I could do it with awk but I'm just struggling here. This is the command I'm trying to use

Code:
awk 'NR==FNR{a[$2];next} {if ($2 in a) print a[$2],$1; else print a[$2],"0"}' sample1.txt sample2.txt  > new.txt

This should be to compare two samples but it doesn't work so I need to figure out what am I doing wrong. Also I'm not sure if I can implement the third file in there.

Last edited by Don Cragun; 06-30-2015 at 03:38 PM.. Reason: Add CODE tags.
# 2  
Old 06-30-2015
Please use code tags as required by forum rules!

Why does b have a 0 in your output when it has a 10 in file2? Which value should accompany c ?

However, try
Code:
awk 'NR==FNR{a[$2];next} $2 in a { print $2, "-", $1; delete a[$2]} END {for (i in a) print i, "-", 0}' file[1-3]
a - 34
c - 5
b - 10
d - 0
e - 0

# 3  
Old 06-30-2015
I apologize for not reading the posting rules first... I will do that for my next thread.

b has 0 for file 2. if we use on the script all files then the table should look like this

Code:
a  34   0
b    0  10
c    5   12
d   0     0
e    0    0


so it makes a new column for each additional file.

BTW thank you for the fast reply!

Last edited by Don Cragun; 06-30-2015 at 03:39 PM.. Reason: Add CODE tags.
# 4  
Old 06-30-2015
Try:
Code:
awk '
  FNR==1{
    c++
  }
  {
    A[$2]
    C[$2,c]=$1
  }
  END {
    for(i in A) {
      $1=i
      for(j=2; j<=c; j++)
        $j=C[i,j]+0
      print
    }
  }
' file1 file2 file3 | sort

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Compare two files column values using awk

Judi # cat File1 judi /export/home 76 judi /usr 83 judi # judi # cat File2 judi /export/home 79 judi /usr 82 judi # if COLUMN3 of File2 is greater that COLUMN3 of File1, then print File2's lines juid /export/home 79 Code tags please (2 Replies)
Discussion started by: judi
2 Replies

3. Shell Programming and Scripting

Compare two files using awk command recursively

I want to compare two files, 1) Compare Each query result. 2) Compare Only first row of the Query output 3) Compare Time (3rd column), First file time is lesser than 2nd file then print the PO_NUM else do nothing. File1: C:\script>call transaction 1OPOP C:\script>Select ID, PO_ID, TIME, DES... (3 Replies)
Discussion started by: Ragu14
3 Replies

4. Shell Programming and Scripting

Compare files & extract column awk

I have two tab delimited files as given below: File_1: PV16 E1 865 2814 1950 PV16 E2 2756 3853 1098 PV16 E4 3333 3620 288 PV16 E5 3850 4101 252 PV16 E6 83 559 477 PV16 E7 562 858 297 PV16 L2 4237 5658 ... (10 Replies)
Discussion started by: vaibhavvsk
10 Replies

5. Shell Programming and Scripting

Compare & subtract lines in files by column using awk.

I have two files with similar column pattern as given below : 2 sample lines from file1 are given below. 18 12630 . G T 49.97 . AC=2;AF=1.00;AN=2;DP=3;Dels=0.00;FS=0.000;HRun=0;HaplotypeScore=0.0000;MQ=60.00;MQ0=0;NDA=1;QD=16.66;SB=-0.01 GT:AD:DP:GQ:PL ... (2 Replies)
Discussion started by: vaibhavvsk
2 Replies

6. Shell Programming and Scripting

awk command to compare a file with set of files in a directory using 'awk'

Hi, I have a situation to compare one file, say file1.txt with a set of files in directory.The directory contains more than 100 files. To be more precise, the requirement is to compare the first field of file1.txt with the first field in all the files in the directory.The files in the... (10 Replies)
Discussion started by: anandek
10 Replies

7. Shell Programming and Scripting

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (3 Replies)
Discussion started by: yerruhari
3 Replies

8. UNIX for Dummies Questions & Answers

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

9. UNIX for Advanced & Expert Users

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

10. Shell Programming and Scripting

awk compare column between 2 files

Hi, I would like to compare file1 and file2 file1 1 2 3 file2 1 a 2 b 3 c 4 d The result should only print out "d" in file 2. Thanks (3 Replies)
Discussion started by: phamp008
3 Replies
Login or Register to Ask a Question