Merge text files with 1st column and difference in output file

Question

File 1: (text1.txt)

Row_Added_Ts    Count   
01/01/14        2022448     
02/01/14        8345155   
03/01/14        8663852  
04/01/14        6785739  
05/01/14        5279913  

File 2: (text2.txt)

Row_Added_Ts    Count  
01/01/14    211      
02/01/14    1598    
03/01/14    1710    
05/01/14    5279913  
06/01/14    7953261 

Output File:

Row_Added_Ts    Count       Row_Added_Ts    Count   Difference  
01/01/14        2022448     01/01/14    211         2022237  
02/01/14        8345155     02/01/14    1598        8343557  
03/01/14        8663852     03/01/14    1710        8662142  
04/01/14        6785739     04/01/14    0           6785739  
05/01/14        5279913     05/01/14    5279913     0  
06/01/14        0           06/01/14    7953261     -7951831  

The requirement is to combine 2 files and fill the rows with 0 where there is no match. Also do the difference of Count in the final Output File.

I had tried to combine with join and awk commands, but didn't get the desired output.

join -j 2 -o 1.1 1.2 2.1 2.2 text1.txt text2.txt  

awk 'BEGIN {FS = OFS = "/t"}
NR == FNR {f[$1] = $0 next}
  {print f[$1]   $0}' text2.txt text1.txt

Also join with sort is not working in sh, ksh or bash.

Answer 1

Use join and awk together:

join -t $'\t' -j 1 -a 1 -a 2 -e 0 -o 0,1.2,0,2.2 file1 file2 | \
  awk 'OFS="\t"{$5=$2-$4}NR==1{$5="Difference"}1'

Where:

• -t sets the input and output delimiter
• -j defines the join field in both files
• -a forces join to print unpairable lines from both files
• -e specifies how to fill the empty fields
• and -o is the output format
• awk first sets the output field separator OFS to tab
• the 5th field $5 is calculated from the 2nd and 4th
• and if it's the first line in the file, format the 5th field as header