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 File 1 matches $1 of File 2, then print $1 and $2 of File 2, or alternatively, print $1 from File 1 with $2 of File 2 when $1 and $2 match between the files. The problem is that File 1 has repeated records in it. Thus when I apply awk 'FNR==NR{a[$1]; next} $1 in a' File 1 File 2 I can get all matches where $1 in File 1 matches $1 in File 2 and then output $1 && $2 in File 2, but without the repeated records. However, I need the order of the records in File 1 to be retained as well as all of the repeated records.
File 1
File 2
Desired Output:
NB: The records are much more varied and repeats much further spread out in the actual file than the simplified examples here.
I had a somewhat similar, albeit more involved, issue in the past that RudiC helped me with (see here), but I am having trouble applying his code to this simpler example.
I got it close with this:
While this attempt printed all of the repeated records of File 1, it only supplied $2 from File 2 along with $1 of File 1 on the first time it appears, but not every time, such as the following:
Could you please try following.
Output will be as follows. EDIT: After reading your question again, 1 question came. Is it you want to check $2 also from Input_file2 to Input_file1 comparison vice?
Thanks,
R. Singh
Last edited by RavinderSingh13; 11-17-2019 at 02:25 AM..
This User Gave Thanks to RavinderSingh13 For This Post:
Hi,
My input looks like that:
A|123|qwer
A|456|tyui
A|456|wsxe
B|789|dfgh
Using awk, I am trying to get:
A|123;456|qwer;tyui;wsxe
B|789|dfgh
For records with same $1, group all the $2 in a field (without replicates), and all the $3 in a field (without replicates).
What I have tried:... (6 Replies)
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 awk. I had been fairly comfortable populating awk arrays with fields and using awk's special... (3 Replies)
I apologize in advance, but I continue to have trouble searching for matches between two files and then printing portions of each to output in awk and would very much appreciate some help.
I have data as follows:
File1
PS012,002 PRQ 0 1 1 17 1 0 -1 3 2 1 2 -1 ... (7 Replies)
Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
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)
Hi,
input:
AA|BB|CC
DD|EE
FF
what I am trying to get:
AA|BB|CC
DD|EE|
FF||
I tried to create first an UDF for printing repeats, but I think I have an issue with my END section or my array:
function repeat(str, n, rep, i)
{
for(i=1 ;i<n;i++)
rep=rep str
return rep
}
... (6 Replies)
Hi,
I'm very new to these forums. I was wondering if someone could help an AWK beginner with a pattern matching
an actor to his appearance in movies, which would be stored as records. Let's say we have a database of 4 movies (each movie a record with name, studio + year, and actor fields with... (2 Replies)
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)
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)
Hello all,
Would appreciate if someone can help me out on the following requirement.
INPUT FILE:
--------------------------
TPS REPORT
abc def ghi
jkl mon pqr
stu vrs lll
END OF TPS REPORT
TPS REPORT
field1 field2 field3
field4 field5 field6 (8 Replies)