mjhjmtu

Suppose I have 2 Files, ABC.txt & DEF.txt with the data shown below as an example:

ABC.txt:

abc 14
dka 1
def 51

DEF.txt:

def 12
ckd 41

I want to grep column 1& 2 from both files and write into a third file so that third file contain both file without any repeated column 1 and thire value put for two file and put zero for values that didn't exit in files. How can it be done?

My expected output is (output:

 ABC DEF
abc 14 0
ckd 0 41
def 51 12
dka 1 0

$ join -j 1 -a 1 -a 2 -o 0,1.2,2.2 -e 0 <(sort ABC.txt) <(sort DEF.txt)
abc 14 0
ckd 0 41
def 51 12
dka 1 0

The header and column spacing are left as an exercise.

Awk solution:

awk 'BEGIN 
 OFS="t"; print "", "ABC", "DEF" 
 
 NR==FNR a[$1]=$2; next 
 
 if ($1 in a) v=a[$1]; delete a[$1] 
 $3 = v+0 
 1; 
 END for(i in a) print i, 0, a[i] ' DEF.txt ABC.txt

The output:

 ABC DEF
abc 14 0
dka 1 0
def 51 12
ckd 0 41

GNU datamash has a cross-tabulation (pivot table) option that is quite nice for this sort of thing - although your data would need some pre-processing:

awk 'print $0, substr(FILENAME,1,length(FILENAME)-4)' ABC.txt DEF.txt | 
 datamash -Ws --filler='0' crosstab 1,3 unique 2
 ABC DEF
abc 14 0
ckd 0 41
def 51 12
dka 1 0