Left join on files using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Left join on files using awk
# 1  
Old 05-27-2009
Left join on files using awk

Quote:
File_A:
NY
NJ
PA
CA
VA
TN
Quote:
File_B:
NY hello
NJ 3
CA 1
VA 5
Quote:
ouptut:
NY,Found
NJ,Found
PA,
CA,Found
VA,Found
TN,
Code:
nawk 'NR==FNR{a[$1];next} {if($1 in a) print $1,"Found" else print}'  OFS="," File_B File_A

The above code is not working help is appreciated
# 2  
Old 05-27-2009
well this was easy, you are missing on the syntax

nawk 'NR==FNR{a[$1];next} {if($1 in a) print $1,"Found";print}' OFS="," File_B File_A

-----Post Update-----

sample output

bash-3.00$ nawk 'NR==FNR{a[$1];next} {if($1 in a) print $1,"Found";print}' OFS="," File_B File_A
NY,Found
NY
NJ,Found
NJ
PA
CA,Found
CA
VA,Found
VA
TN
# 3  
Old 05-27-2009
Quote:
Originally Posted by tonan
well this was easy, you are missing on the syntax

nawk 'NR==FNR{a[$1];next} {if($1 in a) print $1,"Found";print}' OFS="," File_B File_A

-----Post Update-----

sample output

bash-3.00$ nawk 'NR==FNR{a[$1];next} {if($1 in a) print $1,"Found";print}' OFS="," File_B File_A
NY,Found
NY
NJ,Found
NJ
PA
CA,Found
CA
VA,Found
VA
TN

This is not the output i am looking for. please see the required output
# 4  
Old 05-27-2009
Code:
awk 'FNR==NR{a[$1]++;next}{if($1 in a)print $1,"Found"; else printf("%s%s\n", $1,OFS)}' OFS="," B A


-Devaraj Takhellambam
# 5  
Old 05-27-2009
Or (use gawk, nawk or /usr/xpg4/bin/awk on Solaris)

Code:
awk 'NR == FNR { _[$1]; next }
($2 = $1 in _ ? "Found" : x) || 1
' OFS=, file_b file_a


Last edited by radoulov; 05-27-2009 at 02:54 PM..
# 6  
Old 05-27-2009
if you have Python, an alternative
Code:
#!/usr/bin/env python
file2=[i.split()[0] for i in open("file2").read().split("\n")]
for line in open("file1"):
    line=line.strip().split()
    if line[0] in file2:
        print line[0]," found"
    else:
        print line[0],","

output
Code:
# ./test.py
NY  found
NJ  found
PA ,
CA  found
VA  found
TN ,

# 7  
Old 05-27-2009
Quote:
...
ouptut:
NY,Found
NJ,Found
PA,
CA,Found
VA,Found
TN,
...
And if you have perl, then:

Code:
perl -ne 'BEGIN{open(F,"fileb"); while(<F>){split;$found{$_[0]}="Found"} close(F)} {chomp; print "$_,$found{$_}\n"}' filea

Test:

Code:
$
$ cat filea
NY
NJ
PA
CA
VA
TN
$
$ cat fileb
NY hello
NJ 3
CA 1
VA 5
$
$ perl -ne 'BEGIN{open(F,"fileb"); while(<F>){split;$found{$_[0]}="Found"} close(F)} {chomp; print "$_,$found{$_}\n"}' filea
NY,Found
NJ,Found
PA,
CA,Found
VA,Found
TN,
$

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

5. Shell Programming and Scripting

Awk - join multiple files

Is it possible to join all the files with input1 based on 1st column? input1 a b c d e f input2 a b input3 a e input4 c (2 Replies)
Discussion started by: quincyjones
2 Replies

6. Shell Programming and Scripting

left join using awk

Hi guys, I need to use awk to join 2 files file_1 A 001 B 002 C 003 file_2 A XX1 B XX2 output desired A 001 XX1 B 002 missing C 003 XX2 thank you! (2 Replies)
Discussion started by: g1org1o
2 Replies

7. Programming

LEFT JOIN issue in Mysql

I have a data table as follows: mysql> select * from validations where source = "a03"; +------------+-------+--------+ | date | price | source | +------------+-------+--------+ | 2001-01-03 | 80 | a03 | | 2001-01-04 | 82 | a03 | | 2001-01-05 | 84 | a03 | | 2001-01-06... (2 Replies)
Discussion started by: figaro
2 Replies

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

9. Shell Programming and Scripting

Left Join in Unix based on Key?

So I have 2 files: File 1: 111,Mike,Stipe 222,Peter,Buck 333,Mike,Mills File 2: 222,Mr,Bono 444,Mr,Edge I want output to be below, where 222 records joined and all none joined records still in output 111,Mike,Stipe 222,Peter,Buck,Mr,Bono 333,Mike,Mills 444,Mr,Edge (4 Replies)
Discussion started by: stack
4 Replies

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