Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Vlookup multiple file and awk
Top Forums Shell Programming and Scripting Vlookup multiple file and awk Post 303027365 by vgersh99 on Friday 14th of December 2018 09:22:33 AM
Old 12-14-2018
Quote:
Originally Posted by baris35
Dear Vgersh99,
One note:
I do not understand why output is not in comply with the list of order in mainfile when I change newfile1 and newfile2 as follows:


mainfile
Code:
AA_11
BB_22
CC_33
DD_44
EE_55
FF_66
GG_77
HH_88
II_99
JJ_100
KK_101
LL_102

newfile1
Code:
AA_11 MONDAY24
BB_22 WEDNESDAY42
CC_33 THURSDAY52
DD_44 FRIDAY62
EE_55 TUESDAY72
FF_66 FRIDAY99
GG_51 WHAT01
HH_77 SATURDAY22
II_88 SUNDAY22

newfile2
Code:
AA_11 MONDAY
BB_22 WEDNESDAY
CC_33 THURSDAY
DD_44 FRIDAY
EE_55 TUESDAY
FF_66 FRIDAY
GG_51 WHAT
HH_77 SATURDAY
II_88 SUNDAY
JJ_101 MONTAG
KK_109 DIENSTAG
LL115 MITTWOCH
MM_55 DONNERSTAG

This way, output is:
Code:
AA_11  MONDAY24 MONDAY
II_99
KK_101
GG_77
HH_88
FF_66  FRIDAY99 FRIDAY
DD_44  FRIDAY62 FRIDAY
EE_55  TUESDAY72 TUESDAY
BB_22  WEDNESDAY42 WEDNESDAY
JJ_100
CC_33  THURSDAY52 THURSDAY
LL_102

I was expecting:
Code:
AA_11  MONDAY24 MONDAY
BB_22  WEDNESDAY42 WEDNESDAY
CC_33  THURSDAY52 THURSDAY
DD_44  FRIDAY62 FRIDAY
EE_55  TUESDAY72 TUESDAY
FF_66  FRIDAY99 FRIDAY
GG_77
HH_88
II_99
JJ_100
KK_101
LL_102

Thank you
Boris

--- Post updated at 08:04 AM ---

Hello,
Have just sorted out.

Code:
awk 'FNR == NR { lineno[$1] = NR; next} {print lineno[$1], $0;}' mainfile outputfile | sort -k 1,1n | cut -d' ' -f2-

Many thanks
Boris
The order of iteration over the array indicies is not defined/guaranteed.
From gawk manual:
Quote:
By default, when a for loop traverses an array, the order is undefined, meaning that the awk implementation determines the order in which the array is traversed. This order is usually based on the internal implementation of arrays and will vary from one version of awk to the next.
Sorting the output, assumes that the entries in the mainfile is sorted as well, which might always be true.
A small change to the script guarantees the output in the same order as in he mainfile.
Code:
FNR==NR { a[$1];ord[FNR]=$1;next}
$1 in a {a[$1]=a[$1] OFS $2}
END {
  for (i=1;i in ord;i++)
    print ord[i],a[ord[i]]
}

Last edited by vgersh99; 12-14-2018 at 11:17 AM..
This User Gave Thanks to vgersh99 For This Post:
baris35
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Hi, Input 7488 7389 chr1.fa chr1.fa 3546 9887 chr5.fa chr9.fa 7387 7898 chrX.fa chr3.fa 7488 7389 chr21.fa chr3.fa 7488 7389 chr1.fa chr1.fa 3546 9887 chr9.fa chr5.fa 7898 7387 chrX.fa chr3.fa Desired Output 7488 7389 chr1.fa chr1.fa 2 3546 9887 chr5.fa chr9.fa 2... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

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

3. Shell Programming and Scripting

awk script to perform an action similar to vlookup between two csv files in UNIX

Hi, I am new to awk/unix and am trying to put together an awk script to perform an action similar to vlookup between the two csv files. Here are the contents of the two files: File 1: Date,ParentID,Number,Area,Volume,Dimensions 2014-01-01,ABC,247,83430.33,857.84,8110.76... (9 Replies)
Discussion started by: Prit Siv
9 Replies

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

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 using awk without exact match for two colum input

Source file 1 335 R1-snapfound 0098F RDFType:R1 R2-Dev R2-snapfound ,010C0 RemoteSymmetrixID:345 335 R1-snapfound 00990 RDFType:R1 R2-Dev R2-snapfound ,010C1 RemoteSymmetrixID:345 335 R1-snapfound 009C0 RDFType:R1 R2-Dev R2-snapfound ,009C1 RemoteSymmetrixID:345 335 R1-snapfound 009C1... (5 Replies)
Discussion started by: ranjancom2000
5 Replies

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

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

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
All times are GMT -4. The time now is 03:46 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy