Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Vlookup multiple file and awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup multiple file and awk
# 1  
Old 12-11-2018
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
Code:
11
22
33
44
55
66
77
88
99
100
101
102

newfile1
Code:
11 MONDAY24
22 WEDNESDAY42
33 THURSDAY52
44 FRIDAY62
55 TUESDAY72
66 FRIDAY99
51 WHAT01
77 SATURDAY22
88 SUNDAY22

newfile2
Code:
11 MONDAY
22 WEDNESDAY
33 THURSDAY
44 FRIDAY
55 TUESDAY
66 FRIDAY
51 WHAT
77 SATURDAY
88 SUNDAY
101 MONTAG
109 DIENSTAG
115 MITTWOCH
55 DONNERSTAG

Expected output:
Code:
11 MONDAY24 MONDAY
22 WEDNESDAY42 WEDNESDAY
33 THURSDAY52 THURSDAY
44 FRIDAY62 FRIDAY
55 TUESDAY72 TUESDAY DONNERSTAG
66 FRIDAY99 FRIDAY
77 SATURDAY22 SATURDAY
88 SUNDAY22 SUNDAY
99
100
101 MONTAG
102


I run awk code with two steps:
Code:
awk 'NR==FNR {m[$1]=$1"\t"$2; next} {$1=m[$1]; print}' mainfile newfile1 > output
awk 'NR==FNR {m[$1]=$1"\t"$2; next} {$1=m[$1]; print}' mainfile newfile2 >> output

This one does not works as expected.
Could you please let me know how to solve this?
Awk is not a must. Easily understandable solutions are welcome.

Thanks
Boris
Last edited by baris35; 12-13-2018 at 10:12 PM..
# 2  
Old 12-11-2018
awk -f baris.awk mainfile newfile1 newfile2 where baris.awk is:
Code:
FNR==NR { a[$1];next}
$1 in a {a[$1]=a[$1] OFS $2}
END {
  for (i in a)
    print i,a[i]
}

This User Gave Thanks to vgersh99 For This Post:
baris35
# 3  
Old 12-11-2018
Thanks Vgersh99,
Gives expected output.


Kind regards
Boris
# 4  
Old 12-14-2018
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
# 5  
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
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 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

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

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

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

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

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

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

10. 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
Login or Register to Ask a Question