CODEWITHSUNDEEP
unixawk
Ken
Ken

Reputation: 61

How to merge two files based on one column and print both matching and non-matching?

I am trying to merge two files, match them on $1,and display the match from file 2 into a new column $3. If they don't match, the value should display 0.

For instance i have the below two files

tmptest1.txt

aaa 231
bbb 132
ccc 111

tmptest2.txt

aaa 222
ddd 132

I'd like the output file to be:

aaa 231 222
bbb 132 0
ccc 111 0
ddd 0 132

I tried the below code, while it does append the matching number into $3, it omits non matching ones as my print statement only looks at one file. How can i consider a combination of both files? 

awk 'FNR==NR{a[$1]=$2;next} {print $1,$2,a[$1]}' tmptest1.txt tmptest2.txt | sort | awk '!$3{ $3="0"1}

Result

aaa 231 222
bbb 132 0
ccc 111 0

Upvotes: 0

Views: 482

Answers (2)

Jotne
Jotne

Reputation: 41446

All in one awk. This works, but may be shorten some 

awk 'FNR==NR {a[$1]=$2;b[++j]=$1;next} 
    {if (a[$1]) 
        a[$1]=$1FS a[$1]FS$2; 
    else {
        a[$1]=$1FS"0"FS$2;b[++j]=$1}} 
END {for (i=1;i<=j;i++) 
        if(a[b[i]]~/^[a-zA-Z]/)
            print a[b[i]];
        else print b[i],a[b[i]],"0"
    }
' tmptest1.txt tmptest2.txt
aaa 231 222
bbb 132 0
ccc 111 0
ddd 0 132

Upvotes: 0

Shawn
Shawn

Reputation: 52334

Assuming your real files are sorted like your samples are:

$ join -o 0,1.2,2.2 -e0 -a1 -a2 tmptest1.txt tmptest2.txt
aaa 231 222
bbb 132 0
ccc 111 0
ddd 0 132

If not sorted and using bash, zsh, ksh93 or another shell that understands <(command) redirection:

join -o 0,1.2,2.2 -e0 -a1 -a2 <(sort temptest1.txt) <(sort tmptest2.txt)

Upvotes: 2

Related Questions