Sponsored Content
Full Discussion: Command for vlookup function
Top Forums UNIX for Dummies Questions & Answers Command for vlookup function Post 302928638 by makelifeeasier on Saturday 13th of December 2014 02:30:01 PM
Old 12-13-2014
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 appreciated.

<File 1>
Code:
contig-100_1 5
contig-100_30 10
contig-100_50 20
contig-100_40 15
contig-100_60 25

<File 2>
Code:
contig-100_1 "Bacteria"
contig-100_50 "Bacteria A"
contig-100_60 "Bacteria A, Fungi B"

<Expected output>
Code:
contig-100_1 5 Bacteria
contig-100_30 10 NA
contig-100_50 20 Bacteria A
contig-100_40 15 NA
contig-100_60 25 Bacteria A, Fungi B


Last edited by Don Cragun; 12-13-2014 at 04:11 PM.. Reason: Add CODE tags.
 

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

Vlookup functionality in Bash

Hi please help in the below request Input File 1: a,1 b,2 c,3 d,4 e,5 f,6 Input File 2: 2 5 3 1 Output Required: b 2 e 5 c 3 (3 Replies)
Discussion started by: ravin
3 Replies

3. Shell Programming and Scripting

vlookup files

hi frnds i have 2 files. 1st is dddd and 2nd is ssss ==> dddd <==: 1,charit 2,gilhotra ==> ssss <==: 1,sajan 2,doda 3,hello and i want o/p ...mean join and vlookup both files sajan,charit (4 Replies)
Discussion started by: dodasajan
4 Replies

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

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

Excel vlookup function like value mapping with awk

I have two files File1 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... (1 Reply)
Discussion started by: sammy777888
1 Replies

7. Shell Programming and Scripting

Vlookup in Linux

Hello Everybody I am looking for vlookup like functionality in Linux since two files I have are very big(1000MB each) and its not opening completely in excel. Here the requirement file1 11,12,13 16,14,12 28,21,22 22,23,24 file 3 18,16,16 14,12,12 23,22,24 16,11,13 here... (8 Replies)
Discussion started by: shanul karim
8 Replies

8. Shell Programming and Scripting

Conditional Vlookup

Hi everyone, I need to replace values of column 2 array1 with values of column 2 array2 based on a lookup of column 4 value, but only return a value IF the values in column 1 of BOTH array1 and array2 match, otherwise keep original value in column 2 of array1. Both files are tab delimited... (2 Replies)
Discussion started by: Geneanalyst
2 Replies

9. UNIX for Beginners Questions & Answers

Vlookup on 2 files - inserting vlookup command on another command

Hello, i am trying to print group name column(etc/group) on script (etc/passwd) since group name is not listed on etc/passwd columns. Im trying to do a vlookup. but i cant figure out how i can insert the vlookup command FNR==NR inside the print out command or the output. I also tried exporting... (2 Replies)
Discussion started by: joonisio
2 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
Bio::Assembly::IO(3pm)					User Contributed Perl Documentation				    Bio::Assembly::IO(3pm)

NAME
Bio::Assembly::IO - Handler for Assembly::IO Formats SYNOPSIS
use Bio::Assembly::IO; $in = Bio::Assembly::IO->new(-file=>"<inputfilename", -format=>'phrap'); $out = Bio::Assembly::IO->new(-file=>">outputfilename", -format=>'phrap'); while ( my $scaffold = $in->next_assembly() ) { # do something with Bio::Assembly::Scaffold instance # ... $out->write_assembly(-scaffold => $scaffold); } $in->close; $out->close; DESCRIPTION
Bio::Assembly::IO is a handler module for formats in the Assembly::IO set (e.g. Bio::Assembly::IO::phrap). FEEDBACK
Mailing Lists User feedback is an integral part of the evolution of this and other Bioperl modules. Send your comments and suggestions preferably to the Bioperl mailing lists Your participation is much appreciated. bioperl-l@bioperl.org - General discussion http://bioperl.org/wiki/Mailing_lists - About the mailing lists Support Please direct usage questions or support issues to the mailing list: bioperl-l@bioperl.org rather than to the module maintainer directly. Many experienced and reponsive experts will be able look at the problem and quickly address it. Please include a thorough description of the problem with code and data examples if at all possible. Reporting Bugs Report bugs to the Bioperl bug tracking system to help us keep track the bugs and their resolution. Bug reports can be submitted via the web: https://redmine.open-bio.org/projects/bioperl/ AUTHOR
Robson Francisco de Souza E-mail: rfsouza@citri.iq.usp.br CONTRIBUTORS
# APPENDIX
The rest of the documentation details each of the object methods. Internal methods are usually preceded with a _ new Title : new Usage : $stream = Bio::Assembly::IO->new( -file => $filename, -format =>'format' ) Function: Returns a new assembly stream Returns : A Bio::Assembly::IO::Handler initialised with the appropriate format Args : -file => $filename -format => format next_assembly Title : next_assembly Usage : $scaffold = $stream->next_assembly() Function: Reads the next assembly object from the stream and returns it. Returns : a Bio::Assembly::ScaffoldI compliant object Args : none next_contig Title : next_contig Usage : $contig = $stream->next_contig() Function: Reads the next contig or singlet from the stream and returns it. Returns : a Bio::Assembly::Contig or Bio::Contig::Assembly::Singlet Args : none write_assembly Title : write_assembly Usage : $stream->write_assembly($assembly) Function: Write the assembly object in desired format. This method calls write_header(), write_contigs() and write_footer() internally. Returns : 1 on success, 0 for error Args : A Bio::Assembly::Scaffold object write_header Title : write_header Usage : $stream->write_header($assembly) Function: Write the start of the assembly file. It can be called at any time, not when starting to write the assembly file. Returns : 1 on success, 0 for error Args : A Bio::Assembly::Scaffold object or ... (check the specific format driver for more details) write_contig Title : write_contig Usage : $stream->write_contig($contig) Function: Write a contig object in the desired format. Returns : 1 on success, 0 for error Args : A Bio::Assembly::Contig object write_footer Title : write_footer Usage : $stream->write_footer($assembly) Function: Write the start of the assembly file. Returns : 1 on success, 0 for error Args : A Bio::Assembly::Scaffold object or ... (check the specific format driver for more details) _load_format_module Title : _load_format_module Usage : *INTERNAL Assembly::IO stuff* Function: Loads up (like use) a module at run time on demand Example : Returns : Args : _guess_format Title : _guess_format Usage : $obj->_guess_format($filename) Function: guess format based on file suffix Example : Returns : guessed format of filename (lower case) Args : Notes : formats that _filehandle() will guess includes ace, phrap and tigr at the moment _sort Title : _sort Usage : @sorted_values = $ass_io->_sort(@values) Function: Sort a list of values naturally if Sort::Naturally is installed (nicer), lexically otherwise (not as nice, but safe) Returns : array of sorted values Args : array of values to sort perl v5.14.2 2012-03-02 Bio::Assembly::IO(3pm)
All times are GMT -4. The time now is 02:26 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy