Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Search data file2 for entries in data file1

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Search data file2 for entries in data file1
# 1  
Old 03-27-2017
Search data file2 for entries in data file1
Hello,
I have two data files:

file1:
Code:
9780205646999   28.31   20      Cengage
9780205647040   51.94   20      Cengage
9780205660568   49.11   20      Cengage
9780205696758   51.75   20      Cengage
9780205727643   41.63   20      Cengage

file2:
Code:
9780020080954   9780020080954    2       23.3729         11      100     a       n
9780205647040   9780205647040    2       23.3729         11      100     a       n
9780023001406   9780023001406    2       46.4325         11      100     a       n
9780205727643   9780205727643    2       299.378         11      100     a       n

I am looking to search file2 for every entry in column 1 of file1. If the entry is found in file2, the output should be the entire corresponding line of file 1 with the entry in column 4 of file2 appended as such:

Code:
9780205647040   51.94   20      Cengage	23.3729
9780205727643   41.63   20      Cengage	299.378

I know how to do this with grep -f, but it is extremely cumbersome. Thanks very much!
# 2  
Old 03-27-2017
It seems like this should be doable with a fairly simple awk script. Please show us the code using grep -f that is working for you.

What operating system and shell are you using?
# 3  
Old 03-27-2017
I'm using XQuartz on a Mac OS (El Capitan) with a bash shell. What I think would work would be something like:

Code:
awk '{print $1}' file1 > temp
grep -f temp file2

but there may be up to 10k lines in file1, so this would take a very long time. Additionally, I don't know how to append the column from file2.

Thanks again!
# 4  
Old 03-27-2017
Try something like:
Code:
awk '
FNR == NR {
	v[$1] = $4
	next
}
$1 in v {
	print $0, v[$1]
}' OFS="\t" file2 file1

I don't know about XQuartz, but it works fine in a Terminal window.
This User Gave Thanks to Don Cragun For This Post:
palex
# 5  
Old 03-30-2017
One followup question... If I wanted to include an additional column from file2 to the output, how would I modify that code? For example, including column 7 as such:


Code:
9780205647040   51.94   20      Cengage	23.3729  a
9780205727643   41.63   20      Cengage	299.378  a

Thanks!
# 6  
Old 03-30-2017
Quote:
Originally Posted by palex
One followup question... If I wanted to include an additional column from file2 to the output, how would I modify that code? For example, including column 7 as such:


Code:
9780205647040   51.94   20      Cengage	23.3729  a
9780205727643   41.63   20      Cengage	299.378  a

Thanks!
Given the code shown in post #4, what do you think would need to be changed to get the output you want now?

Can't you just add one line to gather the additional field from file2 and change the print command to output the additional field you gathered from file2 when you find a matching record in file1?
# 7  
Old 03-30-2017
I did play around with the code, but I couldn't figure out the syntax of the array (I looked on several pages also). Something like this came to mind, but is clearly wrong:

Code:
awk -F"\t" '
FNR == NR {
        v[$1,$2] = $4,$7
        next
}
$1 in v {
        print $0, v[$1], v[$2]
}' OFS="\t" file2 file1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

2. UNIX for Dummies Questions & Answers

Compare file1 and file2, print matching lines in same order as file1

I want to print only the lines in file2 that match file1, in the same order as they appear in file 1 file1 file2 desired output: I'm getting the lines to match awk 'FNR==NR {a++}; FNR!=NR && a' file1 file2 but they are in sorted order, which is not what I want: Can anyone... (4 Replies)
Discussion started by: pathunkathunk
4 Replies

3. Shell Programming and Scripting

Search within file1 numbers from list in file2

Hello to all, I hope somebody could help me with this: I have this File1 (real has 5 million of lines): Number Category --------------- -------------------------------------- 8734060355 3 8734060356 ... (6 Replies)
Discussion started by: Ophiuchus
6 Replies

4. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

5. Shell Programming and Scripting

search from file1 and replace into file2

I have 2 files: file1.txt: 1|15|XXXXXX||9630716||0096000||30/04/2012|E|O|X||||20120525135617-30.04.2012|PAT66OLM|STA||||00001|STA_0096000_YYYPPPXTMEX00_20120525135617_02_P.pdf|... (2 Replies)
Discussion started by: pparthiv
2 Replies

6. Shell Programming and Scripting

Search & replace fields from file1 to file2

hi, I have two xml files with the name source.xml and tobe_replaced.xml. Sample data: source.xml contains: <?xml version="1.0"?> <product description="prod1" product_info="some/info"> <product description="prod2" product_info="xyz/allinfo"> <product description="abc/partialinfo"... (2 Replies)
Discussion started by: dragon.1431
2 Replies

7. Shell Programming and Scripting

Search, replace string in file1 with string from (lookup table) file2?

Hello: I have another question. Please consider the following two sample, tab-delimited files: File_1: Abf1 YKL112w Abf1 YAL054c Abf1 YGL234w Ace2 YKL150w Ace2 YNL328c Cup9 YDR441c Cup9 YDR442w Cup9 YEL040w ... File 2: ... ABF1 YKL112W ACE2 YLR131C (9 Replies)
Discussion started by: gstuart
9 Replies

8. Shell Programming and Scripting

Read each word from File1 and search each file in file2

file1: has all words to be searched. 100007 200999 299997 File2: has all file names to be searched. C:\search1.txt C:\search2.txt C:\search3.txt C:\search4.txt Outfile: should have all found lines. Logic: Read each word in file1 and search each file in the list of File2; if the... (8 Replies)
Discussion started by: clem2610
8 Replies

9. Shell Programming and Scripting

Search values between ranges in File1 within File2

Hi people, I have 2 files, one with a list of non consecutive ranges (File1.txt), where each range begins with the value in column 1 and finishes with the value in column 2 in the same line, as can be seen above. 215312581156279 215312581166279 215312582342558 215312582357758... (4 Replies)
Discussion started by: cgkmal
4 Replies

10. Shell Programming and Scripting

awk/sed search lines in file1 matching columns in file2

Hi All, as you can see I'm pretty new to this board. :D I'm struggling around with small script to search a few fields in another file. Basically I have file1 looking like this: 15:38:28 sz:10001 pr:14.16 15:38:28 sz:10002 pr:18.41 15:38:29 sz:10003 pr:19.28 15:38:30 sz:10004... (1 Reply)
Discussion started by: floripoint
1 Replies
Login or Register to Ask a Question