Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Join with awk

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Join with awk
# 1  
Old 07-20-2015
Join with awk
I have file 1 (f1)
Code:
531902021342|610231416030043
531902021421|610231415853764
531902021460|610231406819593
531902021477|610231410151075
531902021481|610230208405255
531902021484|0
531902021557|610231407330625
531902021558|610230208106654
531902021564|610230204101407

file 2(f2)
Code:
610231416030043|USA 1
610230000021247|USA 1
610230000021375|USA 1
610230000021376|USA 1
610230000021377|USA 1
610230000021415|ENG 1
610230000021416|ENG 1
610230000021417|ENG 1
610230000021421|ENG 1


I need to join f2 (second column) to f1 when column 2 in f1 equals with column 1 in f2. Expected Output should be
Code:
531902021342|610231416030043|USA 1
531902021421|610231415853764|
531902021460|610231406819593|
531902021477|610231410151075|
531902021481|610230208405255|
531902021484|0|
531902021557|610231407330625|
531902021558|610230208106654|
531902021564|610230204101407|

I did this
Code:
awk 'NR==FNR {h[$2] = $1; next} {print $1,$2,$3,h[$2]}' f2 f1

but it doesnt work..please help
# 2  
Old 07-20-2015
Hello radius,

Good try from you, and thanks for showing us what you have tried so far. You can use following for your requirement. Let me know if you have any queries on same.
Code:
 awk -F"|" 'FNR==NR{A[$1]=$NF;next} ($NF in A){print $0 OFS A[$NF];} !($NF in A){print}' OFS="|" f2 f1

Output will be as follows.
Code:
531902021342|610231416030043|USA 1
531902021421|610231415853764
531902021460|610231406819593
531902021477|610231410151075
531902021481|610230208405255
531902021484|0
531902021557|610231407330625
531902021558|610230208106654
531902021564|610230204101407

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
radius
# 3  
Old 07-20-2015
if I modify my file
f1

Code:
531902021342|610231416030043|FEMALE
531902021421|610231415853764|FEMALE
531902021460|610231406819593|FEMALE
531902021477|610230000021377|FEMALE
531902021481|610230208405255|FEMALE
531902021484|0|FEMALE
531902021557|610231407330625|FEMALE
531902021558|610230208106654|FEMALE
531902021564|610230204101407|FEMALE

and f2
Code:
610231416030043|USA1|FEMALE
610230000021247|USA1|MALE
610230000021375|USA1|FEMALE
610230000021376|USA1|FEMALE
610230000021377|USA1|FEMALE
610230000021415|ENG1|FEMALE
610230000021416|ENG1|FEMALE
610230000021417|ENG1|FEMALE
610230000021421|ENG1|FEMALE

and I expect
Code:
531902021342|610231416030043|FEMALE|USA1
531902021421|610231415853764|FEMALE|
531902021460|610230000021247|FEMALE|
531902021477|610230000021377|FEMALE|USA1
531902021481|610230208405255|FEMALE|
531902021484|0|FEMALE|
531902021557|610231407330625|FEMALE|
531902021558|610230208106654|FEMALE|
531902021564|610230204101407|FEMALE|

how about this?
# 4  
Old 07-20-2015
Hello radius,

Could you please try following and let me know if this helps you.
Code:
 awk -F"|" 'FNR==NR{A[$1]=$2;next} ($2 in A){print $0 OFS A[$2];next} !($2 in A){print $0 OFS}' OFS="|" f2 f1

Output will be as follows.
Code:
531902021342|610231416030043|FEMALE|USA1
531902021421|610231415853764|FEMALE|
531902021460|610231406819593|FEMALE|
531902021477|610230000021377|FEMALE|USA1
531902021481|610230208405255|FEMALE|
531902021484|0|FEMALE|
531902021557|610231407330625|FEMALE|
531902021558|610230208106654|FEMALE|
531902021564|610230204101407|FEMALE|

Thanks,
R. Singh
# 5  
Old 07-20-2015
can you explain the code mr ravinder? I really appreciate it
# 6  
Old 07-20-2015
How about this abbreviated version:
Code:
awk -F"|" 'FNR==NR{A[$1]=$2;next} {print $0 OFS A[$2]}' OFS="|" file2 file1

It collects the file2's second fields into the A array, and then prints file1's lines adding A's contents is exists or nothing if A[$2] is empty/does not exist.
# 7  
Old 07-20-2015
Quote:
Originally Posted by radius
can you explain the code mr ravinder? I really appreciate it
Hello Radius,

Following may help you in same.
Code:
 awk -F"|"               #### Taking pipe | as a delimiter
'FNR==NR                 #### putting a condition where FNR==NR which will be TRUE only while reading the first file, as we all know FNR will set each time
                              it will read the new file, opposite of it NRR's value will be keep on increaing till it reads all the files.
{A[$1]=$2;next}          #### creating an array name A whose index is $1's value and array's value is $2, when it stores this I am using next and asking awk to
                              not to execute next statments after this statment now.
($2 in A)                #### Now this wil lbe read only when f1 is getting read. So I am checking if $2 is present as any of aray A's index.
{print $0 OFS A[$2];     #### If above condiiton is true and any lines' $2 is present in array named A then print operation should perform to print $0 with OFS
                              (output field seperator) and value of array A's current element whose index is $2 in it.
next}                    #### ASAP above condiiton is TRUE and print action has been done then I am requesting awk to leave all other statments now by using next.
!($2 in A)               #### Now I am using !($2 in A) means $2's which are NOT present in array A means non matching columns of files f2 and f1
{print $0 OFS}'          #### printing the current line with OFS (output field seprator)
OFS="|" f2 f1            #### setting output field seprator as Pipe "|" and putting filenames f2 and f1

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
radius
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Shell Programming and Scripting

Join two files using awk

Hello All; I have two files: File1: abc def pqr File2: abc,123 mno,456 def,989 pqr,787 ghj,678 (6 Replies)
Discussion started by: mystition
6 Replies

3. Shell Programming and Scripting

awk join 2 files

Hello All, file1 A1;B1;C1;D1;E1;F1;G1;H1;III1;J1 A2;B2;C2;D2;E2;F2;G2;H2;III2;J2 A3;B3;C3;D3;E3;F3;G3;H3;III3;J3 A4;B4;C4;D4;E4;F4;G4;H4;III4;J4file2 III1 ZZ1 S1 Y 1 P1 None NA III2 ZZ2 S2 Y 3 P2 None NA III3 ZZ3 S2 Y 5 ... (2 Replies)
Discussion started by: vikus
2 Replies

4. UNIX for Dummies Questions & Answers

Join with awk different column

hi guys, i need help I need to join file2 to file1 when column 3 in my file1 and column 1 in my file2 in the same string file1 AA|RR|ESKIM RE|DD|RED WE|WW|SUPSS file2 ESKIM|ES SUPSS|SS Output AA|RR|ESKIM|ES RE|DD|RED| WE|WW|SUPSS|SS (3 Replies)
Discussion started by: radius
3 Replies

5. UNIX for Dummies Questions & Answers

Join or Awk

Is is possible to join on unsorted files by a string? Don't want to sort because there is other text that is already in a good format. I want to replace or join RBOSK-374 in file 2 with NB2781 FGH in file 1. Any help would be appreciated. a.log: RBOSK-374 AAA-B04-D16-K01 a.log: 0 XXX 602... (1 Reply)
Discussion started by: jimmyf
1 Replies

6. Shell Programming and Scripting

left join using awk

Hi guys, I need AWK to merge the following 2 files: file1 1 a 1 1 2 b 2 2 3 c 3 3 4 d 4 4 file2 a a/a c/c a/c c/c a/a c/t c c/t c/c a/t g/g c/c c/t desired output: 1 a 1 1 a/a c/c a/c c/c a/a c/t 2 b 2 2 x x x x x x 3 c 3 3 c/t c/c a/t g/g c/c c/t 4 d 4 4 x x x x x x (2 Replies)
Discussion started by: g1org1o
2 Replies

7. Shell Programming and Scripting

how to join two files with awk.

Hi, Unix Gurus, I need to compare two file based on key value and load result to different files. requirement as following: file1 1, abc 2, bcd 4, cdefile2 1, aaaaa 2, bbbbb 5, ccccckey value is first column for both file. I need generate following files; records_in_1_not_2.txt 4,... (6 Replies)
Discussion started by: ken002
6 Replies

8. Shell Programming and Scripting

Join using awk

Hi - I want to join 2 files on matching keys ( column 1 in both files ) and check for unmatched columns, If for each record print the first unmatched column from both files and then proceed to next record and do the same. File 1: 123|Roy|jj|20/07/3000|25.48 125|Victor|kk|30/07/2009|34.56... (7 Replies)
Discussion started by: nbethy1
7 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

10. Shell Programming and Scripting

awk for join

Is it possible to do this with awk command?? sort1.txt a 10 b 20 sort2.txt a b c d join command join sort1.txt sort2.txt && join -v1 sort2.txt sort1.txt output a 10 b 20 (1 Reply)
Discussion started by: repinementer
1 Replies
Login or Register to Ask a Question