match value2 in 2 files if value 1 is exact match

Question

I have 2 files containing list. Column 1 is userIds & column 2 is associated values

# cat file1
e3001 75
n5244 30
w1453 500
#cat file2
d1128 30
w1453 515
n5244 30
e3001 55

Things to consider.

1. userIds may not be sorted exactly in both files
2. Number of userIds may vary in files

REQUIRED

• firstly, userId from file1:column1 must match UserId in file2:column1
• next compare their values in file1:column2 with file2:column2
• print where values has variance. also extra userIds if any

OUTPUT:

e3001 has differnece, file1 value: 75 & file2 value: 55
w1453 has differnece, file1 value: 500 & file2 value: 515
d1128 is only present in filename: file1|file2

solution with 1liner-awk or bash loop is welcome

I'm trying to loop, but it's spitting garbage, guess there's some mislogic

#!/usr/bin/env bash
VARIABLES
FILE1=file1
FILE2=file2
USERID1=(awk -F'\t' '{ print $1 }' ${FILE1})
USERID2=(awk -F'\t' '{ print $1 }' ${FILE2})
USERDON1=(awk -F'\t' '{ print $2 }' ${FILE1})
USERDON2=(awk -F'\t' '{ print $2 }' ${FILE2})
for user in ${USERID1[@]}
do
    for (( i = 0; i < "${#USERID2[@]}"; i++ ))
    #for user in ${USERID2[@]}
    do
        if [[ ${USERID1[$user]} == ${USERID2[i]} ]]
        then
            echo ${USERID1[$user]} MATCHES BALANCE FROM ${FILE1}: ${USERDON1[$i]} WITH BALANCE FROM ${FILE2}: ${USERDON2[$i]}
        else
            echo ${USERID1[$user]} 
        fi
    done
done

Below is copied file right from linux box. It's tab separated, but awk works with tab also, as far as I know.

#cat file1
e3001   55
n5244   30
w1453   515

Answer 1

Hmmm - your script takes the scenic route, so to speak. How about a simple awk approach? Like

awk '
NR==FNR         {ARR[$1] = $2
                 F1      = FILENAME
                 next
                }
($1 in ARR)     {if ($2 != ARR[$1]) print $1 " has difference," \
                                          F1 " value: " ARR[$1] \
                                          " & " FILENAME " value: " $2 
                 delete ARR[$1]
                 next
                }
                {print $1 " is only present in filename: " FILENAME
                }
END             {for (a in ARR) print a " is only present in filename: " F1
                }
' file[12]
d1128 is only present in filename: file2
w1453 has difference, file1 value: 500 & file2 value: 515
e3001 has difference, file1 value: 75 & file2 value: 55

It reads all of file1 into an array, then, with every line in file2, checks $1 against the array indices, and, if present, prints the difference (or doesn't print if none), and deletes the array element (that delete may be missing in some awk implementations, BTW). If not present, print accordingly. In the END section, all remaining array elements are printed as they exist only in file1.

Answer 2

The shell is a horrible tool for this sort of thing. Also, as a general rule, you should avoid CAPS for your shell variables in your shell scripts. Since, by convention, global environment shell variables are capitalized, this can lead to naming collisions and hard to debug issues. Finally, your script requires reading the file 4 separate times(!) and then processing the data.

With that said, here's another awk approach (frankly, RudiC's is better, but I'd already written this so I'm posting anyway):

$ awk '{
  if(NR==FNR) {
    fn1=FILENAME;
    f1[$1]=$2;
    next
  }
  f2[$1]=$2;
  if($1 in f1){
    if($2 != f1[$1]){
      printf "%s is different; %s value: %s & %s value: %s\n", \
             $1,fn1,$2,FILENAME,f1[$1]
    }
  }
  else{
    print $1,"is only present in filename:", FILENAME
  }
}
END{
  for(id in f1){
    if( !(id in f2) ){print id,"is only present in afilename:",fn1}
  }
}' file1 file2
d1128 is only present in filename: file2
w1453 is different; file1 value: 515 & file2 value: 500
e3001 is different; file1 value: 55 & file2 value: 75

Answer 3

Comment are self explanatory :

awk '
    BEGIN {file1 = ARGV[1]; file2 = ARGV[2]}
# Load all file1 contents
NR == FNR {map[$1] = $2; next}

# If $1 is not in m then this key is unique to file2
!($1 in map) {uniq[$1]; next}

# If $1 is in m and the value differs there are delta
# between the two files. Save it.
$1 in map && map[$1] != $2 {diff[$1] = $2; next}

# The two files have all the same data.
{delete map[$1]}

END {
    # Anything is in diff are in both files but
    # with different values
    for ( i in diff )
        print i, "has difference,", file1, "value:", map[i], "&", file2, "value:", diff[i]

    # Anything is still in m is only in file 1
    for ( i in map )
        if (!(i in diff))
            print i, "is only present in filename :", file1

    # Anything is in uniq is unique to file2
    for ( i in uniq )
        print i, "is only present in filename :", file2
}

' file1 file2

Answer 4

awk 'function printUniq(Id, fName){
         printf("%s is only present in filename: %s\n", Id, fName)
}

{ fileName[nxtinput+0]=FILENAME }
!nxtinput{ Ids[$1]=$2; next }

($1 in Ids){ if($2!=Ids[$1])
                 printf ("%s has difference, %s value: %s & %s value: %s\n",\
                 $1, fileName[0], Ids[$1], fileName[1], $2);
             delete Ids[$1];
             next
}
{ printUniq($1, fileName[1]) }
END{ for(id in Ids) printUniq(id, fileName[0]) }' file1 nxtinput=1 file2

Answer 5

Essentially the same solution as posted by RudiC but without the all upper case variable names and with a couple of other minor improvements to clarity:

$ cat tst.awk
NR==FNR {
    file1[$1] = $2
    next
}
$1 in file1 {
    if ( $2 != file1[$1] ) {
        printf "%s has difference, %s value: %s & value: %s\n", $1, ARGV[1], file1[$1], FILENAME, $2
    }
    delete file1[$1]
    next
}
{
    print $1, "is only present in filename:", FILENAME
}
END {
    for ( id in file1 ) {
        print id, "is only present in filename:", ARGV[1]
    }
}

$ awk -f tst.awk file1 file2
d1128 is only present in filename: file2
w1453 has difference, file1 value: 500 & value: file2
e3001 has difference, file1 value: 75 & value: file2