Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Command for vlookup function

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Command for vlookup function
# 8  
Old 12-13-2014
Quote:
Originally Posted by makelifeeasier
Thanks Don Cragun!

I am working on Mac OS. I have executed your command with bash shell. But the third column in the output file is all "NA"...
I'm also working on Mac OS X and when I run the script (using either bash or ksh) I get exactly the output you said you wanted.

Please show us the output from the command:
Code:
head -5 File[12] | od -bc

so we can figure out why my suggestion doesn't work with your data.
# 9  
Old 12-15-2014
Dear Don Cragun,

Sorry for my late reply. I am not very sure about what you meant by File[12]. Please see below.

Code:
head -5 File1[12] | od -bc
0000000 143 157 156 164 151 147 055 061 060 060 137 063 063 061 065 066
          c   o   n   t   i   g   -   1   0   0   _   3   3   1   5   6
0000020 061 040 066 012 143 157 156 164 151 147 055 061 060 060 137 063
          1       6  \n   c   o   n   t   i   g   -   1   0   0   _   3
0000040 070 064 061 062 061 040 066 012 143 157 156 164 151 147 055 061
          8   4   1   2   1       6  \n   c   o   n   t   i   g   -   1
0000060 060 060 137 063 067 066 070 066 071 040 071 067 012 143 157 156
          0   0   _   3   7   6   8   6   9       9   7  \n   c   o   n
0000100 164 151 147 055 061 060 060 137 064 063 070 061 067 061 040 061
          t   i   g   -   1   0   0   _   4   3   8   1   7   1       1
0000120 063 012 143 157 156 164 151 147 055 061 060 060 137 062 066 071
          3  \n   c   o   n   t   i   g   -   1   0   0   _   2   6   9
0000140 066 061 067 040 062 066 062 012
          6   1   7       2   6   2  \n

head -5 File2[12] | od -bc
0000000 143 157 156 164 151 147 055 061 060 060 137 062 060 062 066 011
          c   o   n   t   i   g   -   1   0   0   _   2   0   2   6  \t
0000020 042 162 157 157 164 042 012 143 157 156 164 151 147 055 061 060
          "   r   o   o   t   "  \n   c   o   n   t   i   g   -   1   0
0000040 060 137 063 063 060 064 011 042 162 157 157 164 042 012 143 157
          0   _   3   3   0   4  \t   "   r   o   o   t   "  \n   c   o
0000060 156 164 151 147 055 061 060 060 137 065 061 064 065 011 042 162
          n   t   i   g   -   1   0   0   _   5   1   4   5  \t   "   r
0000100 157 157 164 042 012 143 157 156 164 151 147 055 061 060 060 137
          o   o   t   "  \n   c   o   n   t   i   g   -   1   0   0   _
0000120 070 060 065 060 011 042 162 157 157 164 042 012 143 157 156 164
          8   0   5   0  \t   "   r   o   o   t   "  \n   c   o   n   t
0000140 151 147 055 061 060 060 137 070 064 066 071 011 042 162 157 157
          i   g   -   1   0   0   _   8   4   6   9  \t   "   r   o   o
0000160 164 042 012
          t   "  \n

head -5 output[12] | od -bc
0000000 143 157 156 164 151 147 055 061 060 060 137 063 063 061 065 066
          c   o   n   t   i   g   -   1   0   0   _   3   3   1   5   6
0000020 061 040 066 040 116 101 012 143 157 156 164 151 147 055 061 060
          1       6       N   A  \n   c   o   n   t   i   g   -   1   0
0000040 060 137 063 070 064 061 062 061 040 066 040 116 101 012 143 157
          0   _   3   8   4   1   2   1       6       N   A  \n   c   o
0000060 156 164 151 147 055 061 060 060 137 063 067 066 070 066 071 040
          n   t   i   g   -   1   0   0   _   3   7   6   8   6   9    
0000100 071 067 040 116 101 012 143 157 156 164 151 147 055 061 060 060
          9   7       N   A  \n   c   o   n   t   i   g   -   1   0   0
0000120 137 064 063 070 061 067 061 040 061 063 040 116 101 012 143 157
          _   4   3   8   1   7   1       1   3       N   A  \n   c   o
0000140 156 164 151 147 055 061 060 060 137 062 066 071 066 061 067 040
          n   t   i   g   -   1   0   0   _   2   6   9   6   1   7    
0000160 062 066 062 040 116 101 012
          2   6   2       N   A  \n

---------- Post updated at 10:29 AM ---------- Previous update was at 10:26 AM ----------

Dear RudiC,

Your command worked great like I said. But when I tried your script with small sized datasets (1-5 M), it worked fine. But it didn't work well on the bigger sized datasets (6 - 10 M). Do you have any suggestion on this? Any advice would be greatly appreciated.
Last edited by Don Cragun; 12-15-2014 at 01:14 PM.. Reason: Add CODE tags again!
 
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. 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

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

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

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

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

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

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

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

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