Matching fields between two files, repeated records


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Matching fields between two files, repeated records
# 1  
Old 01-08-2018
Matching fields between two files, repeated records

In two previous posts (here) and (here), I received help from forum members comparing multiple fields across two files and selectively printing portions of each as output based upon would-be matches using [g]awk. I had been fairly comfortable populating awk arrays with fields and using awk's special "in" statement to find records either matching or non-matching, but had difficulty getting these one-liners transformed into shell scripts and also I encountered an impasse when I realized that awk's behavior is to give precedence to the last line number in an array when one of its elements is repeated (AWK Man page; I hope I stated that correctly).

In order to disambiguate repeated records in arrays I attempted inserting an additional field into a file composed of a discrete numbers. However, I learned in a response to a previous post (thank you Scrutinizer) that awk's pre-increment feature can adequately handle these repeats and thus the need to introduce an additional, discrete, disambiguating field is superfluous.

I am now back to square one trying to put this all together. I apologize in advance for the bevy of similar posts, but I'm hoping that my groping in the dark will prove useful to other users.

I have two files such as:

File1
Code:
PS012,007 M>MR                0   2 -1 -1 -1  1 -1   -1 -1  0  2     1   2   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 H                   0   7 -1 -1 -1 -1 -1   -1  3  1  2    -1   7   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 D                  -1   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 MRJ>                0   3 -1 -1 -1  1 -1   -1 -1  0  0     2   3   2   2      -1      -1      -1    0  502     0
PS012,007 M>MR                0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   2   2      -1      -1      -1    0  521     0
PS012,007 HW=                 0   8 -1 -1 -1 -1 -1   -1 -1  1  2    -1   8   8   2      -1      -1      -1    0  502     0
PS012,007 DKJ                 0   1  0 17  1  1 -1   62 -1  0  0     3  13  13   2      -1      -1      -1    0  521     0
PS012,007 S>M                 0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 GBJ                 0   1  0 17  1  1 -1   62 -1  0  0     3  13   2  -1      -1      -1      -1    0  502     0
PS012,007 D                  -1   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  519     0
PS012,007 BXR                 0   1  0 17  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0
PS012,007 B                   0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 >R<                 0   2 -1 -1 -1  1 -1   -1 -1  0  1     3   2   5   2      -1      -1      -1    0  505     0
PS012,007 W                   0   6 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  509     0
PS012,007 DKJ                 0   1  6 18  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0
PS012,007 XD                 -1   2 -1 -1 -1  1 -1   -1 -1  0  0     2   2   2   1      -1      -1      -1    0  506     0
PS012,007 B                   0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 CB<                -1   2 -1 -1 -1  1 -1   -1 -1  0  0     3   2   5   2      -1      -1      -1    0  506     0

File2
Code:
PS012,007 >MRH       M>MR
PS012,007 ?          H
PS012,007 ?          D
PS012,007 JHWH       MRJ>
PS012,007 >MRH       M>MR
PS012,007 VHR        ?
PS012,007 ?          HW=
PS012,007 ?          DKJ
PS012,007 KSP        S>M
PS012,007 ?          GBJ
PS012,007 ?          D
PS012,007 YRP        BXR
PS012,007 B          B
PS012,007 <LJL       >R<
PS012,007 L          ?
PS012,007 H          ?
PS012,007 >RY        ?
PS012,007 ?          W
PS012,007 ZQQ        DKJ
PS012,007 CB<=       ?
PS012,007 ?          XD
PS012,007 ?          B
PS012,007 ?          CB<

I would like to accomplish the following: When $1 and $3 of File2 match $1 and $2 of File1, then print $1-$3 of File2 followed by $3-$23 of File1, and if there is a "?" in $3 of File2, then write $1-$3 followed by 21x "-" (dash) after as $4-$24 in the output. However, in my data files there are places where records are repeated in $3 of File2, such as DKJ in the lines
Code:
PS012,007 ?          DKJ
PS012,007 ZQQ        DKJ

in the sample provided for File2. The only feature different between $1 and $3 in these two lines is the order in which the appear in the file.

The output should look something like this:

Desired Output
Code:
PS012,007 >MRH       M>MR      0   2 -1 -1 -1  1 -1   -1 -1  0  2     1   2   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 ?          H         0   7 -1 -1 -1 -1 -1   -1  3  1  2    -1   7   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 ?          D        -1   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 JHWH       MRJ>      0   3 -1 -1 -1  1 -1   -1 -1  0  0     2   3   2   2      -1      -1      -1    0  502     0
PS012,007 >MRH       M>MR      0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   2   2      -1      -1      -1    0  521     0
PS012,007 VHR        ?         -   -  -  -  -  -  -    -  -  -  -     -   -   -   -       -       -       -    -    -     -
PS012,007 ?          HW=       0   8 -1 -1 -1 -1 -1   -1 -1  1  2    -1   8   8   2      -1      -1      -1    0  502     0
PS012,007 ?          DKJ       0   1  0 17  1  1 -1   62 -1  0  0     3  13  13   2      -1      -1      -1    0  521     0
PS012,007 KSP        S>M       0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 ?          GBJ       0   1  0 17  1  1 -1   62 -1  0  0     3  13   2  -1      -1      -1      -1    0  502     0
PS012,007 ?          D        -1   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  519     0
PS012,007 YRP        BXR       0   1  0 17  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0  
PS012,007 B          B         0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 <LJL       >R<       0   2 -1 -1 -1  1 -1   -1 -1  0  1     3   2   5   2      -1      -1      -1    0  505     0 
PS012,007 L          ?         -   -  -  -  -  -  -    -  -  -  -     -   -   -   -       -       -       -    -    -     -
PS012,007 H          ?         -   -  -  -  -  -  -    -  -  -  -     -   -   -   -       -       -       -    -    -     -
PS012,007 >RY        ?         -   -  -  -  -  -  -    -  -  -  -     -   -   -   -       -       -       -    -    -     -
PS012,007 ?          W         0   6 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  509     0
PS012,007 ZQQ        DKJ       0   1  6 18  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0
PS012,007 CB<=       ?         -   -  -  -  -  -  -    -  -  -  -     -   -   -   -       -       -       -    -    -     -
PS012,007 ?          XD       -1   2 -1 -1 -1  1 -1   -1 -1  0  0     2   2   2   1      -1      -1      -1    0  506     0
PS012,007 ?          B         0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1  
PS012,007 ?          CB<      -1   2 -1 -1 -1  1 -1   -1 -1  0  0     3   2   5   2      -1      -1      -1    0  506     0

The code that I am currently attempting as the basis for what I ultimately hope will be a shell script is the following one-liner:
Code:
awk 'NR==FNR {A[$1,$2,++C1[$1,$2]]=$0; next}$3 !~/\?/ && ($1,$3,++C2[$1,$3]) in A{print $0,A[$1,$3,C2[$1,$3]]}' File1 File2

The output of this line of code is:
Code:
PS012,007 JHWH       MRJ>        PS012,007 MRJ>                0   3 -1 -1 -1  1 -1   -1 -1  0  0     2   3   2   2      -1      -1      -1    0  502     0
PS012,007 >MRH       M>MR        PS012,007 M>MR                0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   2   2      -1      -1      -1    0  521     0
PS012,007 ?          HW=         PS012,007 HW=                 0   8 -1 -1 -1 -1 -1   -1 -1  1  2    -1   8   8   2      -1      -1      -1    0  502     0
PS012,007 ?          DKJ         PS012,007 DKJ                 0   1  0 17  1  1 -1   62 -1  0  0     3  13  13   2      -1      -1      -1    0  521     0
PS012,007 KSP        S>M         PS012,007 S>M                 0   2 -1 -1 -1  1 -1   -1 -1  0  2     3   2   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 ?          GBJ         PS012,007 GBJ                 0   1  0 17  1  1 -1   62 -1  0  0     3  13   2  -1      -1      -1      -1    0  502     0
PS012,007 ?          D           PS012,007 D                  -1   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  519     0
PS012,007 YRP        BXR         PS012,007 BXR                 0   1  0 17  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0
PS012,007 B          B           PS012,007 B                   0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 <LJL       >R<         PS012,007 >R<                 0   2 -1 -1 -1  1 -1   -1 -1  0  1     3   2   5   2      -1      -1      -1    0  505     0
PS012,007 ?          W           PS012,007 W                   0   6 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   6   6  -1      -1      -1      -1    0  509     0
PS012,007 ZQQ        DKJ         PS012,007 DKJ                 0   1  6 18  1  1 -1   62 -1  0  0     2   1   1  -1      -1      -1      -1    0  521     0
PS012,007 ?          XD          PS012,007 XD                 -1   2 -1 -1 -1  1 -1   -1 -1  0  0     2   2   2   1      -1      -1      -1    0  506     0
PS012,007 ?          B           PS012,007 B                   0   5 -1 -1 -1 -1 -1   -1 -1 -1 -1    -1   5   0  -1      -1      -1      -1   -1   -1    -1
PS012,007 ?          CB<         PS012,007 CB<                -1   2 -1 -1 -1  1 -1   -1 -1  0  0     3   2   5   2      -1      -1      -1    0  506     0

This gets me close to what I am after, however, it appears that $1 and $2 of File1 (as $4 and $5 in output) is intervening between $4 and $6 when it should not. Issue (1) is that I cannot adjust my print command correctly to get rid of those fields so as to match the "desired output."

The second two issues I am experiencing are related. I cannot seem to get this one-liner to work as a script. Secondly, and most obviously, there are no "dashes" present for I did not supply an "if/else if" statement. To do so, I have attempted the following TestScript1.awk and TestScript2.awk as follows:

TestScript1.awk
Code:
FNR==NR {
	q=$1 SUBSEP $2 SUBSEP ++C1[$1,$2]
	A[q]=$0
	next
}
{
if($3 !~/\?/ && ($1,$3,++C2[$1,$3]) in A)
	print $0,A[$1,$3,C2[$1,$3]]
else if($3 ~/\?/)
	print $0, " - - - - - - - - - - - - - - - - - - - - - "
}

TestScript2.awk
Code:
FNR==NR {
	A[$1,$2,++C1[$1,$2]]=$0
	next
}
{
if($3 !~/\?/ && ($1,$3,++C2[$1,$3]) in A)
	print $0,A[$1,$3,C2[$1,$3]]
else if($3 ~/\?/)
	print $0, " - - - - - - - - - - - - - - - - - - - - - "
}

My hope is to learn inductively by working backwards from a representative, correct code so as to save the Desired Output as a file and then repeat the search and print method with $1 and $2 of File2 (rather than $1 and $3) with a different File1.

Thank you all so much and hopefully this results in codes and methods than can be beneficial to others as well.
# 2  
Old 01-09-2018
Except for the formatting, how far would this get you:
Code:
awk '
NR == FNR       {q = $1 "," $2
                 $1 = $2 = ""
                 T[q "," ++C[q]] = $0
                 next
                }
                {q = $1 "," $3
                 X = q "," ++D[q]
                 printf "%s\t",  $0
                 if (X in T)    print T[X]
                 if ($3 == "?") print "  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -"
                }
' file1 file2

Please note, that
Quote:
awk's behavior is to give precedence to the last line number in an array when one of its elements is repeated
does not quite accurately describe the situation - array elements are just overwritten when indices are encountered another time(s), and awk does this as any other programming language.
This User Gave Thanks to RudiC For This Post:
# 3  
Old 01-09-2018
Quote:
Originally Posted by RudiC
Except for the formatting, how far would this get you:
Code:
awk '
NR == FNR       {q = $1 "," $2
                 $1 = $2 = ""
                 T[q "," ++C[q]] = $0
                 next
                }
                {q = $1 "," $3
                 X = q "," ++D[q]
                 printf "%s\t",  $0
                 if (X in T)    print T[X]
                 if ($3 == "?") print "  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -"
                }
' file1 file2

Please note, that does not quite accurately describe the situation - array elements are just overwritten when indices are encountered another time(s), and awk does this as any other programming language.
Thank you so much for this RudiC. After a quick test, initial indications are that that did the trick and I can run it through a printf to take care of the formatting.

I have not seen before the "," in your variable and then "T" array. I suspect this is giving an extra coordinate to the array to make it multi-dimensional but will do more research in AWK man. I really appreciate your help.
# 4  
Old 01-10-2018
In fact, the "," overrides the SUBSEP character in the array index creation (as can do any character, e.g. <TAB>). I use this sometimes if I print out the array with inidices for debug purposes.
This User Gave Thanks to RudiC For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk for matching fields between files with repeated records

Hello all, I am having trouble with what should be an easy task, but seem to be missing something fundamental. I have two files, with File 1 consisting of a single field of many thousands of records. I also have File 2 with two fields and many thousands of records. My goal is that when $1 of... (2 Replies)
Discussion started by: jvoot
2 Replies

2. Shell Programming and Scripting

Comparing two files by two matching fields

Long time listener first time poster. Hope someone can advise. I have two files, 1000+ lines in each, two fields in each file. After performing a sort, what is the best way to find exact matches where field $1 and $2 in file1 are also present in file2 on the same line, then output only those... (6 Replies)
Discussion started by: bstaff
6 Replies

3. UNIX for Beginners Questions & Answers

Awk: matching multiple fields between 2 files

Hi, I have 2 tab-delimited input files as follows. file1.tab: green A apple red B apple file2.tab: apple - A;Z Objective: Return $1 of file1 if, . $1 of file2 matches $3 of file1 and, . any single element (separated by ";") in $3 of file2 is present in $2 of file1 In order to... (3 Replies)
Discussion started by: beca123456
3 Replies

4. Shell Programming and Scripting

Print matching fields (if they exist) from two text files

Hi everyone, Given two files (test1 and test2) with the following contents: test1: 80263760,I71 80267369,M44 80274628,L77 80276793,I32 80277390,K05 80277391,I06 80279206,I43 80279859,K37 80279866,K35 80279867,J16 80280346,I14and test2: 80263760,PT18 80279867,PT01I need to do some... (3 Replies)
Discussion started by: gacanepa
3 Replies

5. Shell Programming and Scripting

How to merge two or more fields from two different files where there is non matching column?

Hi, Please excuse for often requesting queries and making R&D, I am trying to work out a possibility where i have two files field separated by pipe and another file containing only one field where there is no matching columns, Could you please advise how to merge two files. $more... (3 Replies)
Discussion started by: karthikram
3 Replies

6. Shell Programming and Scripting

Matching multiple fields from two files and then some?

Hi, I am working with two tab-delimited files with multiple columns, formatted as follows: File 1: >chrom 1 100 A G 20 …(10 columns) >chrom 1 104 G C 18 …(10 columns) >chrom 2 28 T C ... (4 Replies)
Discussion started by: mbp
4 Replies

7. Shell Programming and Scripting

comparing two files for matching fields

I am newbie to unix and would please like some help to solve the task below I have two files, file_a.text and file_b.text that I want to evaluate. file_a.text 1698.74 1711.88 6576.25 899.41 3205.63 4187.98 697.35 1551.83 ... (3 Replies)
Discussion started by: gameli
3 Replies

8. Shell Programming and Scripting

Creating a file with matching records from two other files

Hi All, I have 2 files (file1 & file2). File1 and File2 have m and n columns respectively I have to compare value in column1 of file1 with file2 and find line(s) from file2 matching column1 value. The value can be in any column in the matching lines of file2. The output should be... (10 Replies)
Discussion started by: Swagi
10 Replies

9. Shell Programming and Scripting

Averaging all fields while counting repeated records

Hi every one; I have a 31500-line text file upon which two following tasks are to be performed: 1: Rearranging the file 2: Taking the average of each column (considering number of zeros) and output the result into a new file This is the code I've come up with: awk '(NR%3150<3150)... (0 Replies)
Discussion started by: nxp
0 Replies

10. Shell Programming and Scripting

AWK Matching Fields and Combining Files

Hello! I am writing a program to run through two large lists of data (~300,000 rows), find where rows in one file match another, and combine them based on matching fields. Due to the large file sizes, I'm guessing AWK will be the most efficient way to do this. Overall, the input and output I'm... (5 Replies)
Discussion started by: Michelangelo
5 Replies
Login or Register to Ask a Question