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
File2
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
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
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:
The output of this line of code is:
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
TestScript2.awk
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.
Except for the formatting, how far would this get you:
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.
Except for the formatting, how far would this get you:
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.
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.
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)
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)
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 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)
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)
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)
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)
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)
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)