5

I have two files, each of them have several columns and not equal in line number , I need to read the third column in both files and make sure are identical then compare the forth column in both files and get the highest number and print whole line in a third file. and the for the line which not found in the other file take it also as highest number

File A

a b c 10
d e f 11
g h i 15
j k l 15
p l m 35

Flie B

d e f 15
j k l 20
w x z 40

Required Output

File C

a b c 10
d e f 15
g h i 15
j k l 20
p l m 35
w x z 40
  • 1
    If you know awk, this is a simple task. – FloHe Jan 20 '17 at 10:50
  • I am beginner I know awk but I do not how to do it if can help me much appreciate you – Ali Jan 20 '17 at 11:32
  • What about column 1 and 2, do you ignore them (show in example). – ctrl-alt-delor Jan 20 '17 at 15:44
  • yes ignore them – Ali Jan 20 '17 at 16:03
  • I am sorry for this confusion, I wish it is clear now – Ali Jan 20 '17 at 23:34
0
$ cat fileA fileB | sort -k3,3 -k4,4nr | sort -k3,3 -u
a b c 10
d e f 15
g h i 15
j k l 20
p l m 35
w x z 40

This is a pipeline with three parts:

  1. Concatenate fileA with fileB.
  2. Sort the concatenated file in decreasing numerical order based on the fourth column for each unique value in the third column. The result of this step is

    a b c 10
    d e f 15
    d e f 11
    g h i 15
    j k l 20
    j k l 15
    p l m 35
    w x z 40
    
  3. Sort this again, but remove duplicates and only use the third column as the sort key. Since this will leave the line with the first found instance of the sort key but discard the lines with duplicated sort keys (and with lower values in the fourth column, thanks to the first sort), it will give us the wanted result.

This approach disregards the contents of the first two columns completely.

  • much appreciate you it is working as I want – Ali Jan 22 '17 at 7:56
  • 2
    @Ali If one of these answers solved your issue, please take a moment and accept it by clicking on the check mark to the left. That will mark the question as answered and is the way thanks are expressed on the Stack Exchange sites. – terdon Jan 22 '17 at 13:15
1

Like this:

awk '$4>n[$3]{n[$3]=$4;l[$3]=$0}END{for(i in l)print l[i]}' file_a file_b > file_c

Explanation: if 4th column is greater than remembered maximum for the same 3rd column (in n array), then remember the new 4th column and the whole line (in l array) for that 3rd column. At the end print all remembered lines.

  • many thanks for you. it is working as I want – Ali Jan 22 '17 at 7:56
0

If your awk interpreter resides in /usr/bin/:

cat doit.awk

#!/usr/bin/awk

NF!=4 {

    print "Field number must be 4" > "/dev/stderr";
    exit 1;
}

FILENAME=="file_a" {

    i++;
    numval_a[i]=$4;
    letters_a[i]=$1FS$2FS$3;
}

FILENAME=="file_b" {

    k++;
    numval_b[k]=$4;
    letters_b[k]=$1FS$2FS$3;
}

END {

    for(j=1; j<=i; j++) {

        if(letters_a[j]!=letters_b[j]) {

            print "Line missmatch!" > "/dev/stderr";
            exit 2;
        }

        if(numval_a[j]>numval_b[j])
            max=numval_a[j];
        else
            max=numval_b[j];

        print letters_a[j], max;
    }
}

awk -f doit.awk file_a file_b

a b c 10
d e f 15
g h i 20
j k l 25
  • I did not check it is complicated for me. thanks for your time I really appreciate you – Ali Jan 20 '17 at 13:39
  • hardcoding filenames in the script limits its usability a lot – mik Jan 22 '17 at 23:27

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.