Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Excel vlookup function like value mapping with awk
Top Forums Shell Programming and Scripting Excel vlookup function like value mapping with awk Post 303000948 by durden_tyler on Monday 24th of July 2017 09:16:08 AM
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
 

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

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

LEARN ABOUT DEBIAN

mencal

mencal(1)								 1								 mencal(1)

NAME

       mencal - menstruation calendar

SYNOPSIS

       mencal [options] [file1 file2 ... -c CONF1 -c CONF2 ...]

DESCRIPTION

   Display options:
       -m, --monday
	      draw monday as first weekday (sunday is default)

       -1     current month (default)

       -3     previous, current and next month

       -y [YYYY]
	      all-year calendar (default YYYY is current year)

       -q, --quiet
	      no top information will be printed

       -n, --nocolor
	      noncolored output

       -i, --icolor COLOR
	      intersection color (default red)

	      available colors: red, green, blue, yellow, violet, cyan, shiny, bold

   mencal configuration:
       -c, --config
	      s=[YYYY]MMDD,l=LL,d=DD,n=NAME,f=FILE,c=COLOR

	      The  second argument is a comma separated list of options. No spaces are allowed in this list. If no name is specified, 'Unknown' is
	      used.  Various -c options or filenames can be set.

       s,start=[YYYY]MMDD
	      start day of period (default current day)

       l,length=LL
	      length of period in days (default 28)

       d,duration=D
	      duration of menstruation in days (default 4)

       n,name=NAME
	      name of subject

       f,file=FILE
	      filename to save configuration to (see section FILES)

	      only menstruation related variables will be saved

       c,color=COLOR
	      color used for menstruation days

	      available colors: red, green, blue, yellow, violet, cyan, shiny, bold default color is red, with	'-n'  switch  color  settings  are
	      ignored

   Info options:
       -h, --help
	      print help

       -V, --version
	      print version information

FILES
:
       $HOME/.mencalrc - the default configuration file that is automatically loaded

AUTHOR
:
       (C) 2002 C. McCohy
       e-mail: <mccohy@kyberdigi.cz>
       Word-Wide-Web: http://www.kyberdigi.cz/projects/mencal/english

       This  manual  page  was	written for the Debian GNU/Linux distribution because the original program does not have a manual page (but may be
       used by others).

HISTORY

       22 March 2002 - Originally written by Amaya Rodrigo <amaya@debian.org>.
       24 Apr 2002 - New manpage contributed by Pablo S. Torralba <psanchez@skyrealms.org>.
       Amaya Rodrigo <amaya@debian.org> is now responsible for developing and maintaining this manual page. Comments and suggestions  are  greatly
       wellcome.

mencal							       Menstruation calendar							 mencal(1)
All times are GMT -4. The time now is 02:17 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy