1

I want to create a file that contains columns from two input files.

File1 is like:

11111111        abc12345   Y
22222222        xyz23456   Y

File2:

11111111       abc12345    
33333333       kbc34567

The Output should be like :

11111111        abc12345   Y
22222222        xyz23456   Y
33333333        kbc34567

I have tried the following and was able to find the duplicate but I need the duplicate entry from first file and other records as well and with same formatting(same amount of space between 1st, 2nd and 3rd column) :

awk 'FNR==NR{a[$1$2]=$1$2;next} ($1$2 in a) {print a[$1$2],$3}' file2 file1
  • What are the requirements for them to be duplicate, first two fields match ? – 123 Jan 25 '16 at 8:50
  • you're right , it is the first two fields for identifying a duplicate . – Ayesha Jan 25 '16 at 9:18
1

The space is being changed because you are printing i) the 1st and 2nd field concatenated and ii) the third field. By default, awk uses a space as the output field separator (OFS), so that messes up your spacing. A simple solution is to save the line itself ($0) in the array instead of the fields:

a[$1$2]=$0;

However, your script doesn't do what you want anyway. It will only print lines from file1 that were present in file2, so anything that is only in file1 will be skipped. According to your desired output, you want to print all lines from both files and, if any line of file2 has the same 1st two fields as one in file1, print only the corresponding line from file1. You can do this in awk with:

awk 'FNR==NR{a[$1$2]=$0; print} !($1$2 in a) {print}' file1 file2 

That will save each line of file1 in the array and also print it. Then, when file2 is being processed, it will print any line whose 1st two fields aren't in a.


Note that you can also do this using sort:

$ sort -uk1,2 file1 file2 
11111111    abc12345    Y
22222222    xyz23456    Y
33333333    kbc34567

You just need to make sure the amount of whitespace in the two files is the same (which isn't the case in your example), or make it so with:

$ sed  's/  */\t/g' file1 file2 | sort -uk1,2 
11111111    abc12345    Y
22222222    xyz23456    Y
33333333    kbc34567
0

Depending on how large your files are, this may not be the most efficient method, but I think it will work for the specific case. It does not require that the files are in any particular order, but does require that you always prefer File1 over File2:

#!/bin/bash
# Make a list of the unique identifiers in each of the files, changing the whitespace in between into a comma.
awk '{print $1 "," $2}' File1 File2| sort | uniq |
# Loop through all the unique identifiers we just found
while read l; do
    # Create a regular expression for each identifier to use as
    #  a search term, changing the comma into "any number of whitespaces"
    searchterm=$(echo $l | sed 's/,/\\\s*/')
    # if this pattern exists in File1
    if $(grep -E "$searchterm" File1 >& /dev/null); then
        # print it out
        grep -E "$searchterm" File1
    else
        # otherwise, print it if it's in File2
        grep -E "$searchterm" File2
    fi
done 

If you want File3 you can save this as a script and send the output there

#copy to merge_uniq.sh
chmod +x merge_uniq.sh
merge_uniq.sh > File3
  • I made a few improvements to your script but this will still have to read each file once at the beginning and then more times per search term. Also, since you're using awk to print the comma, why not use it to print the \s* as well? Something like: awk '{print $1"\\\\s*"$2}' File1 File2. – terdon Jan 25 '16 at 9:59
  • Thanks for the improvements, but I think your pure awk solution is the way to go... Regardless, I will definitely start using that syntax for any applicable loops... as for awk printing the \s, I just didn't think of it. I originally wrote this as a one-liner with lots of semi-colons. – 22degrees Jan 25 '16 at 10:11
  • Heh, I know the feeling :). You might want to have a look at this page for an explanation of why the for loop was a bad idea. – terdon Jan 25 '16 at 10:15
0
awk 'BEGIN{i=0} {if (!($1$2  in a)) {a[$1$2]=$0; index_array[i] =$1$2; i++} } END{for (j=0; j<i; j++) print a[index_array[j]]}' 1 2

11111111        abc12345   Y

22222222        xyz23456   Y
33333333       kbc34567

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.