CODEWITHSUNDEEP
bashawkmerge
user3666956
user3666956

Reputation: 69

awk merge two columns by key, joining values

These are my two imput files:

file1.txt
1   34
2   55
3   44
6   77


file2.txt
1   12
2   7
5   32

And I wish my output to be:

1   34   12
2   55   0
3   44   0
5   0   32
6   77   0

I need to do this in awk and although I was able to merge files, I do not know how to do it without losing info...

awk -F"\t" 'NR==FNR {h[$1] = $2; next }{print $1,$2,h[$2]}' file1.txt file2.txt > try.txt
awk  '{ if ($3 !="") print $1,$2,$3; else print $1,$2,"0";}' try.txt > output.txt

And the output is:

1   34   12
2   55   7
3   44   0
6   77   0

Sorry, I know this must be very easy, but I am quite new in this world! Please I need help!!! Thanks in advance!!

Upvotes: 1

Views: 2518

Answers (2)

jaypal singh
jaypal singh

Reputation: 77185

Here is another way using join and awk: 

join -a1 -a2 -o1.1 2.1 1.2 2.2 -e0 file1 file2 | awk '{print ($1?$1:$2),$3,$4}' OFS='\t'
1   34  12
2   55  7
3   44  0
5   0   32
6   77  0
  • -a switch allows to join on un-pairable lines.
  • -o builds our output format
  • -e allows to specify what should be printed for values that do not exist
  • awk just completes the final formatting.

Upvotes: 1

Kent
Kent

Reputation: 195289

this command gives you the desired output:

awk 'NR==FNR{a[$1]=$2;next}
    {if($1 in a){print $0,a[$1];delete a[$1]}
        else print $0,"0"}
    END{for(x in a)print x,"0",a[x]}' file2 file1|sort -n|column -t

note that I used sort and column to sort & format the output.

output: (note I guess the 2 55 0 was a typo in your expected output)

1  34  12
2  55  7
3  44  0
5  0   32
6  77  0

Upvotes: 1

Related Questions