awk "intelligently" paste two files together.

Shell Programming and Scripting
1

Hi everyone,

I am trying to join two files together based on a field present in both files but not necessarily in the same order.

File 1:

123 user1
245 user2
559 user3

File 2:

123 user1
246 user4
544 user2

Also, sometimes a user may not even be in both files. What I want awk to do is look at field 2 and, when it finds the user in both files, add up the numbers in column one and print the username and the total. So desired output

246 user1
789 user2

I have seen a couple of similar examples here on the unix forums. However, nothing is exactly what I am trying to do and I just don't understand awk well enough to adapt for this purpose. Any help is greatly appreciated. Thanks so much.

2

If it doesn't have to be awk, then this would probably do the job:

#!/bin/sh
while read file1line
do
  value=`echo "$file1line" | cut -d ' ' -f 1`
  user=`echo "$file1line" | cut -d ' ' -f 2`
  # Depending on your os, you might need egrep here instead of grep -e
  file2value=`grep -e " $user\$" $2 | tail -1 | cut -d ' ' -f 1`
  if [ -n "$file2value" ]
  then
    # We've found one
    value=`expr $value + $file2value`
    echo "$value $user"
  fi
done < $1

(Untested)

Usage:

  ./yourscript file1 file2 > outputfile

This will have the following potential issues:
If a user appears twice in the first file, it will be output twice as though it were two different users (use another grep over the first file to locate additional identical users to fix this if it matters).
If a user appears twice in the second file, only the last detected occurance of the user will ever be used (use a while loop on the second file to fix this if it matters).

1 Like
3 
awk '{sum[$2]+=$1}END{for(i in sum) print sum,i}' file1 file2
1 Like
4

Thanks so much for the replies. Both work well. I thought that making tweaks would be a simple matter, but I am having a bit of trouble making a small change. I would also like to be able to return the difference between the numbers in the two files as well as the sum. I am having trouble altering the awk one liner solution for this purpose.

Smiling Dragon, your solution works great. Also, I was able to tailor it to what I need. Man, sometimes with AWK I just can't tell what's going on.