Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Excel vlookup function like value mapping with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Excel vlookup function like value mapping with awk
# 1  
Old 07-24-2017
Excel vlookup function like value mapping with awk
I have two files

File1
Code:
175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545
179967 st_001256606.1 rs93516.2 rs86923.1 st_501841.1 177879 179967_177879

File2
Code:
179967_177879 0.75
179912_175545 0.5

Desired output File3
Code:
175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 NA
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 0.5
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 0.5
179967 st_001256606.1 rs93516.2 rs86923.1 st_501841.1 177879 179967_177879 0.75


I want to match the 7th column of File1 with 1st column of File2 and map the corresponding values at 8th cloumn. If any id of 7th column is not avalible (such as 175552_174841 here) in File 2 it should print NA. Here is what I did

Code:
awk 'NR == FNR{a[$7] = $1;next}; {print $0, $7 in a?a[$1]: "NA"}' File2 File1

The current output
Code:
175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 NA
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 NA
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 NA
179967 st_001256606.1 rs93516.2 rs86923.1 st_501841.1 177879 179967_177879 NA

Could someone please help to correct the code and explain it a bit. Thanks
# 2  
Old 07-24-2017
You're almost there.
Just keep in mind that awk splits each line of file1 into 7 tokens ($1 through $7) and file2 into 2 ($1 and $2).

Code:
$
$ awk 'NR == FNR{a[$1] = $2;next}; {print $0, $7 in a?a[$7]: "NA"}' file2 file1
175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 NA
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 0.5
179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 0.5
179967 st_001256606.1 rs93516.2 rs86923.1 st_501841.1 177879 179967_177879 0.75
$
$

This User Gave Thanks to durden_tyler For This Post:
sammy777888
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Vlookup not using awk

Hi I just want again to ask for help on what command to use to vlookup f1 group name in "/etc/group" matching f3 of it to "/etc/passwd" f4. I do need to display group name in the output of /etc/passwd without using awk or NR==FNR command. thank you while IFS=: read -r f1 f2 f3 f4 f5 f6 f7... (4 Replies)
Discussion started by: joonisio
4 Replies

2. Shell Programming and Scripting

Vlookup multiple file and awk

Hello Folks, What I wish to do is: If first column matches in main and new file, then paste $COL2 into output file. Something like vlookup. Please see also bold text in expected output. mainfile 11 22 33 44 55 66 77 88 99 100 101 102 (4 Replies)
Discussion started by: baris35
4 Replies

3. Shell Programming and Scripting

Vlookup using awk non similar files

I need to vlookup and check the server not found. Source file 1 server1 server2 server3 server4 server5_root server6_silver server7 server7-test server7-temp Source file 2 server1_bronze (6 Replies)
Discussion started by: ranjancom2000
6 Replies

4. Shell Programming and Scripting

Vlookup using awk without exact match

Code used to find the server from cloum 3 and update needtotakesnap Output came from above command awk 'NR==FNR{A;next}$3 in A{$3 = "needtotakesnap " $3}1' /home/Others/active-server.txt /home/Others/all-server |grep server1 879 dummy server1_217_silver dummy 00870 TDEV 2071575 831 Tier1... (3 Replies)
Discussion started by: ranjancom2000
3 Replies

5. Shell Programming and Scripting

Vlookup using awk

Hi folks, awk 'NR==FNR {m=$0; next} $1 in m{$0=m} {print}' file2 file1 Works a charm for a vlookup type query, sourced from https://www.unix.com/shell-programming-and-scripting/215998-vlookup-using-awk.html However my column content has white spaces and numbers. Example file1 The Man... (6 Replies)
Discussion started by: pshields1984
6 Replies

6. Shell Programming and Scripting

awk cmd for vlookup in Mysql

Hi, Is there possible to do vlookup in Mysql one table from another table based on one column values and placed the data in same table? if it is possible in mysql itself pls share links for reference. Here is the ex: i need to vlookup the cus.id in table to and place the cus.name in 4th... (3 Replies)
Discussion started by: Shenbaga.d
3 Replies

7. UNIX for Dummies Questions & Answers

Command for vlookup function

Hello experts, I have large text files that need to be arranged using a function like excel's vlookup. I have been playing with awk command but didn't really come up with a solution. Could anyone please help me out? Below are my datasets and expected output. Any help would be greatly... (8 Replies)
Discussion started by: makelifeeasier
8 Replies

8. Shell Programming and Scripting

Vlookup using awk

Hello, I am trying to use vlookup (Excel function) using awk but there is some problem :( --file1-- ABC123 101F X1 A $P=Z X2 A $P=X X3 B $P=F X4 C $P=G MNK180 END --file2-- X1 A_t $P=Z X2 A_t $P=X X3 B_u $P=F X4 C_o $P=G (2 Replies)
Discussion started by: young
2 Replies

9. Shell Programming and Scripting

Help require for vLookup Utility same as in EXCEL

Hi guys, can someone please help me with a vlookup utility in shell script that does the work similar to Excel. Ex: If File1.txt is 1 2 11 4 12 and File2.txt is 1 tab1 2 tab2 3 tab3 4 tab4 5 tab5 then the utility should create File3.txt with the below mentioned output: (24 Replies)
Discussion started by: viveklgupta007
24 Replies

10. Shell Programming and Scripting

Want to implement VLOOKUP (Excel function) in Unix

Dear All, i want to implement vookup function which is there in excel into Unix. Suppose i have 2 files. The files are given below. File1: MSC Cell SDCA Patna-1 12 Bihar Patna-2 45 Ranchi Bhopal-1 85 Raigarh Bhopal-2 ... (8 Replies)
Discussion started by: pravani1
8 Replies
Login or Register to Ask a Question