Merging two files by a column and add 0 when entry is missing

user_19408310 0
Viewed 65

I used this code to run a .txt with ~200 lines of text

awk '
BEGIN{ OFS="\t" }
FNR==NR{
  arr[$0]
  next
}
{
  print (($0 in arr)?$0 OFS $0:$0 OFS "0")
}
' file2 file1 | column -t -s $'\t'
  • here is the output from the code above
AMEX60DD102001000_6   AMEX60DD102001000_6
AMEX60DD102001000_7   AMEX60DD102001000_7
AMEX60DD102001000_8   0
AMEX60DD102001004_5   AMEX60DD102001004_5
AMEX60DD102001004_6   AMEX60DD102001004_6
AMEX60DD102010030_10  AMEX60DD102010030_10
AMEX60DD102010039_1   AMEX60DD102010039_1
AMEX60DD102010039_2   AMEX60DD102010039_2
AMEX60DD102010039_9   AMEX60DD102010039_9
AMEX60DD102010065_1   AMEX60DD102010065_1

The code works perfectly fine. it returns the results I wanted to see. However, when I run the same code with over 100,000 lines of text in a file, it returns to me this below!

Some lines on the right could be missing, but I have checked and I see that all lines that end with a single digit got replaced with 0 on the right. Only lines end with double digits stay matched.

AMEX60DD101005309_1                         0
AMEX60DD101005337_2                         0
AMEX60DD101005415_7                         0
AMEX60DD101005433_6                         0
AMEX60DD101005642_3                         0
AMEX60DD101005679_12  AMEX60DD101005679_12
AMEX60DD101005679_13  AMEX60DD101005679_13
AMEX60DD101005679_14  AMEX60DD101005679_14
AMEX60DD101005679_2                         0
AMEX60DD101005679_3                         0
AMEX60DD101005765_1                         0
AMEX60DD101005782_8                         0
AMEX60DD101005797_3                         0
AMEX60DD101005901_2                         0
AMEX60DD101005984_1                         0
unix
1 Answers

With your shown samples and attempts please try following awk solution. Using awk + column combination here. You need to pass file2 and file1 respectively files into it and we should be Good to go here. For lines missing in file1 are having >0 you can change it as per your requirement too as mentioned in your question.

awk '
BEGIN{ OFS="\t" }
FNR==NR{
  arrFile2[$0]
  next
}
{
  if($0 in arrFile2){
    $0=$0 OFS $0
  }
  else{
    $0=$0 OFS ">0"
    arrFile1[$0]
  }
  print
}
END{
    for(i in arrFile1){
       print ">0" OFS i
    }
}
' file2 file1 | column -t -s $'\t'
user_58665800
Related