Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Awk: matching multiple fields between 2 files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Awk: matching multiple fields between 2 files
# 1  
Old 03-17-2017
Awk: matching multiple fields between 2 files
Hi,

I have 2 tab-delimited input files as follows.
file1.tab:
Code:
green	A	apple
red	B	apple

file2.tab:
Code:
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 get:
Code:
green


The following code returns a blank output, since it seems to retain only the last iteration of file1.
Code:
gawk '
BEGIN{FS=OFS="\t"}
NR==FNR{
   letter[$3]=$2
   color[$3]=$1
   next
}
{
   a = split($3, b, ";")

   for(i=1; i<=a; i++){
      if($1 in letter && letter[$1] ~ b[i]){
         print color[$1]
      }
   }
}' file1.tab file2.tab

# 2  
Old 03-17-2017
How about
Code:
awk '
BEGIN           {FS = OFS = "\t"
                }

NR==FNR         {for (n = split ($3, T, ";"); n>0; n--) CMP[$1, T[n]]
                 next
                }

($3,$2) in CMP  {print $1
                }
' file2 file1
green

# 3  
Old 03-17-2017
Thanks RudiC,

However I should have insisted on the second criteria ("$3 of file2 is present in $2 of file1"). Meaning that I am not looking for an exact match.

For example if file1.tab is:
Code:
green	A;foo	apple
red	B;bar	apple

# 4  
Old 03-17-2017
Weren't that a brilliant exercise for the reader to implement?
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. UNIX for Beginners Questions & Answers

Continued trouble matching fields in different files and selective field printing ([g]awk)

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)
Discussion started by: jvoot
7 Replies

3. Shell Programming and Scripting

awk to print fields that match using conditions and a default value for non-matching in two files

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)
Discussion started by: cmccabe
6 Replies

4. Shell Programming and Scripting

awk script issue redirecting to multiple files after matching pattern

Hi All I am having one awk and sed requirement for the below problem. I tried multiple options in my sed or awk and right output is not coming out. Problem Description ############################################################### I am having a big file say file having repeated... (4 Replies)
Discussion started by: kshitij
4 Replies

5. Shell Programming and Scripting

Awk: adding fields after matching $1

Dear AWK-experts! I did get stuck in the task of combining files after matching fields, so I'm still awkward with learning AWK. There are 2 files: one containing 3 columns with ID, coding status, and score for long noncoding RNAs: file1 (1.txt) (>5000 lines) ... (12 Replies)
Discussion started by: kben
12 Replies

6. UNIX for Advanced & Expert Users

awk print all fields except matching regex

grep -v will exclude matching lines, but I want something that will print all lines but exclude a matching field. The pattern that I want excluded is '/mnt/svn' If there is a better solution than awk I am happy to hear about it, but I would like to see this done in awk as well. I know I can... (11 Replies)
Discussion started by: glev2005
11 Replies

7. UNIX for Dummies Questions & Answers

Comparing multiple fields from 2 files uing awk

Hi I have 2 files as below File 1 Chr Start End chr1 120 130 chr1 140 150 chr2 130 140 File2 Chr Start End Value chr1 121 128 ABC chr1 144 149 XYZ chr2 120 129 PQR I would like to compare these files using awk; specifically if column 1 of file1 is equal to column 1 of file2... (7 Replies)
Discussion started by: sshetty
7 Replies

8. 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

9. 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

10. Shell Programming and Scripting

awk print fields to multiple files?

I am trying to print the output of a command to two separate files. Is it possible to use awk to print $1 to one file and $2 to another file? Thanks in advance! (1 Reply)
Discussion started by: TheCrunge
1 Replies
Login or Register to Ask a Question