0

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
4
$ 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.

3

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
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.