1

I would like to merge 2 files based on column 1, 2, 3. I have tried the awk command below but is not working.

awk 'NR==FNR {h[$1FS$2FS$3]=$4; next}{k=$1FS$2FS$3; if (k in h) print $1,$2,$3,$4,h[k] ;else print $1,$2,$3,$4,"NA"}1' FS=\| OFS=\| file2.txt 

file1.txt:

Student1|Class 1A|27|20140804 08:16:54
Student2|Class 1B|15|20140804 10:10:10
Student3|Class 1C|17|20140804 15:02:14
Student4|Class 1D|20|20140804 18:02:14
Student5|Class 2D|10|20140804 20:02:14

file2.txt:

Student1|Class 1A|27|20140805 08:16:54
Student2|Class 1B|15|20140805 10:10:10
Student4|Class 1D|20|20140805 18:02:14
Student5|Class 2D|10|20140805 20:02:14

Expected result:

Student1|Class 1A|27|20140804 08:16:54|20140805 08:16:54
Student2|Class 1B|15|20140804 10:10:10|20140805 10:10:10
Student3|Class 1C|17|20140804 15:02:14|NA
Student4|Class 1D|20|20140804 18:02:14|20140805 18:02:14
Student5|Class 2D|10|20140804 20:02:14|20140805 20:02:14
  • 1
    Drop the stray 1 at the end of the awk program, and give it file2.txt as the first file argument and file1.txt as the second file argument. Personally I'd also assume a nonancient awk and put -vFS=\| -vOFS=\| or just -F\| -vOFS=\| before the program instead of the vars-among-files method, and consider $0 in place of $1,$2,$3,$4. – dave_thompson_085 Aug 4 '15 at 16:31
0

Try this. Builds an array as with your example and just prints merged fields via END { ... }.

$ awk -F\| '{ k=$1 FS $2 FS $3; h[k] = (k in h) ? h[k]=h[k] FS $4 : $0 } END { for(x in h){printf "%s%s\n",h[x],(length(h[x])>38) ? "" : "|NA"}}' file1.txt file2.txt|sort
Student1|Class 1A|27|20140804 08:16:54|20140805 08:16:54
Student2|Class 1B|15|20140804 10:10:10|20140805 10:10:10
Student3|Class 1C|17|20140804 15:02:14|NA
Student4|Class 1D|20|20140804 18:02:14|20140805 18:02:14
Student5|Class 2D|10|20140804 20:02:14|20140805 20:02:14
$
0

If it's safe to assume that the first file contains the full list of keys, i.e. students, you can simply do the appending for each record first. Then, during END, you'll need to sort the keys, trim the appended values and back-fill the missing values with "NA":

$ awk -F\| '{k=$1 FS $2 FS $3;r[k]=r[k] FS $4;c[k]++}
    END{n=asorti(r,s);
        for(i=1;i<=n;i++){
            print s[i] substr(r[s[i]],1) (++c[s[i]] == ARGC ? "" : FS "NA")
        }
    }' file1.txt file2.txt
Student1|Class 1A|27|20140804 08:16:54|20140805 08:16:54
Student2|Class 1B|15|20140804 10:10:10|20140805 10:10:10
Student3|Class 1C|17|20140804 15:02:14|NA
Student4|Class 1D|20|20140804 18:02:14|20140805 18:02:14
Student5|Class 2D|10|20140804 20:02:14|20140805 20:02:14

I'm using ++c[s[i]] == ARGC, where ARGC tells use the number of files + 1 (being the awk command itself), to do the back-fill comparison.

0

I tried this and it is working

/usr/xpg4/bin/awk 'NR==FNR {h[$1,$2,$3]=$4; next}{print $0,($1,$2,$3) in h?h[$1,$2,$3]:"NA"}' FS=\| OFS=\| file2.txt file1.txt

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.