4

I would like to compare 2 similar files on a common column. The files will have identical headers.

file1.txt

mem_id      Date    Time    Building    
aa          bb      cc      dd
ee          ff      gg      hh
ii          jj      kk      ll

file2.txt

mem_id      Date    Time    Building    
aa          bb      cc      dd
ee          ff      2g      hh
ii          jj      kk      2l

Command

awk 'NR==FNR{for(i=1;i<=NF;i++){A[i,NR]=$i}next} {for(i=1;i<=NF;i++){if(A[i,FNR]!=$i)\ 
{print "ID#-"$1": Column",i"- File1.txt value=",A[i,FNR]" / File2.txt value= "$i}}}'\ 
file1.txt file2.txt

Current Output

ID#-ee: Column 3- File1.txt value= gg / File2.txt value= 2g
ID#-ii: Column 4- File1.txt value= ll / File2.txt value= 2l

Desired Output

mem_id#-ee: Time- file1.txt value= gg / file2.txt value= 2g
mem_id#-ii: Building- file1.txt value= ll / file2.txt value= 2l 

I am very close. But I would like help with a few improvements.

1- I would like to replace the “Column 3” and “Column 4” with the actual column header (Time, Building, whatever)

2- I would like to dynamically gather the file names in the output and not have to add it as part of the command (to make it universal)

3- I would like this scriptable.

Any help would be appreciated.

  • 1
    1- Read the contents of the first row to an array, then use that array later on. – muru Sep 4 '14 at 19:32
  • cat file1.txt |awk {'print $1"\t"$2"\t"$4"\t"$3'} > newfile.txt ### replaced – PersianGulf Sep 4 '14 at 20:26
  • but i don't understand #2 and #3 please help me.... – PersianGulf Sep 4 '14 at 20:28
2

Using awk:

awk '
NR==1 { 
  for (i=1; i<=NF; i++)
    header[i] = $i
}
NR==FNR {
  for (i=1; i<=NF; i++) {
    A[i,NR] = $i
  }
  next
}
{
  for (i=1; i<=NF; i++)
    if (A[i,FNR] != $i)
      print "ID#-" $1 ": " header[i] "- " ARGV[1] " value= ", A[i,FNR]" / " ARGV[2] " value= "$i
}' file1.txt file2.txt

Output:

ID#-ee: Time- file1.txt value=  gg / file2.txt value= 2g
ID#-ii: Building- file1.txt value=  ll / file2.txt value= 2l
  • This is very helpful. Thank you. One quick question... can you suggest a way to dynamically grab the header of column 1 (as to read "mem_id")? – cmart2112 Sep 5 '14 at 14:47
  • 1
    @cmart2112 You're welcome. The header array has those values. If you print header[1] you can refer back to column1. – jaypal singh Sep 5 '14 at 14:50
2

Here's a script that I think does the job:

#! /bin/bash

FILE1=$1
FILE2=$2

[[ -z $FILE1 || -z $FILE2 ]] && echo "USAGE: $0 FILE1 FILE2" && exit 1

join -j 1 $FILE1 $FILE2 | 
awk '
NR == 1 { for (i = 1; i <= NF; i++) { header[i] = $i; }; NCOLS = (NF - 1)/2; } 
NR > 1 { for (i = 2; i <= NCOLS + 1; i++) { if ($i != $(i+NCOLS)) {print header[1]"#-"$1": "header[i]"- '$FILE1' value=",$i" / '$FILE2' value= "$(i+NCOLS) } } }
'

I applied a join on the files so that I can read them in one go.

With NR == 1, I'm reading in the first row to an array (so that I can reuse them later.

Since I used join, if both files had n columns, the combined output has 2n-1 columns (the columns used for joining is merged). So I save the number of columns (discounting the common column) to NCOLS.

  • Thanks for the input. This script gets me very close, but it doen't not appear to eliminate the rows that are not common in column 1. – cmart2112 Sep 5 '14 at 14:49
  • @cmart2112 are you sure? Can you give an example, because join discards lines without a common join column. – muru Sep 5 '14 at 14:51

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.