Compare two lines in a file and delete those if pattern matches

Question

I have a file like this.

12345 X678GHR 0 ADD
23445 HGT6787 1 ADD
12345 X678GHR 0 REM
67894 OIY5678 0 ADD
12345 OIY5678 0 ADD
12345 X678GHR 1 ADD

I have to compare the lines in a file to delete the lines that were added and removed later. So the output should look like this:

23445 HGT6787 1 ADD
67894 OIY5678 0 ADD
12345 OIY5678 0 ADD
12345 X678GHR 1 ADD

Cleaned the records added and deleted later from the file.

Update: I also have to make sure that columns 2 and 3 also match between deleting records. In my original file, delimiter is not space. It is a closed bracket ")"

Please help. I'm very new to UNIX

Answer 1

If you don't need to guarantee the order of entries, then given

$ cat file
12345)X678GHR)0)ADD
23445)HGT6787)1)ADD
12345)X678GHR)0)REM
67894)OIY5678)0)ADD
12345)OIY5678)0)ADD
12345)X678GHR)1)ADD

the following awk

$ awk -F ')' '
    $NF == "ADD" {lines[$1 FS $2 FS $3] = $0} 
    $NF == "REM" {delete lines[$1 FS $2 FS $3]} 
    END {for(i in lines) print lines[i]}
' file
12345)X678GHR)1)ADD
67894)OIY5678)0)ADD
23445)HGT6787)1)ADD
12345)OIY5678)0)ADD

---

If you do need to preserve order, then you can do so by making two passes over the file:

$ awk -F ')' '
    NR == FNR {if($NF == "REM") rem[$1 FS $2 FS $3]; next} 
    !($1 FS $2 FS $3 in rem)
' file file
23445)HGT6787)1)ADD
67894)OIY5678)0)ADD
12345)OIY5678)0)ADD
12345)X678GHR)1)ADD

Answer 2

If preserving the order of the input lines is important, then a single associative array is not enough (because associative arrays are inherently unordered in most languages, including awk and perl), so you need two arrays.

1. an array with numeric indexes containing the text of the input lines
2. an associative array containing the line number of the first array that matches the hash.

This is much easier to do in perl than in awk, so that's what I'll use. I'll use @lines for the first array, and %keys for the second.

@F is the name of the automatically-created array containing the auto-split fields when the -F option is used - similar to awk's $1, $2, $3 etc, except that it's $F[0], $F[1], $F[2], etc. and $F[-1] is the last element of @F, roughly equivalent to awk's $NF. Note that perl arrays start from 0, not 1.

perl -F'\)' -l -e '
  $key = join(")",@F[0..$#F-1]);
  if ($F[-1] eq "ADD") {
    $lines[$.] = $_;      # $. is the line number of the current file
    $keys{$key} = $.;
  } elsif ($F[-1] eq "REM") {
    delete($lines[$keys{$key}]);
    delete($keys{$key});
  }

  if (eof) {
    foreach $l (@lines) { print $l if $l; };
    @lines = ();
    %keys = ();
  };' inputfile

Output:

23445)HGT6787)1)ADD 
67894)OIY5678)0)ADD
12345)OIY5678)0)ADD
12345)X678GHR)1)ADD

Note that this perl version works with any number of fields in the input, it isn't hard-coded to use fields 1-3 for the data and field 4 for the "ADD" or "REM" instruction. Instead, it uses all but the last field for the data and the last field for the instruction. This is possible to do with awk too, but you'll need to write a join() function or at least a simple loop to concatenate all but the last field.

This perl version is also capable of processing multiple input files, by detecting the end of each file (using the eof() function), and clearing both arrays after printing the lines which haven't been deleted. i.e. it resets everything at the end of each input file. I could have just used an END {} block as in @steeldriver's awk answer, but this seemed appropriate because you never know when you're going to want to re-use a script in a slightly different way...and it's always a good idea to ask yourself "what if?" and "how could this fail?" type questions.

Answer 3

Given

$ cat input
12345)X678GHR)0)ADD
23445)HGT6787)1)ADD
12345)X678GHR)0)REM
67894)OIY5678)0)ADD
12345)OIY5678)0)ADD
12345)X678GHR)1)ADD

... then

$ tac input | perl -n -l -e \
        's/REM$// ? $hit{"${_}ADD"}=1 : print unless delete $hit{$_}' \
    | tac

... produces

23445)HGT6787)1)ADD
67894)OIY5678)0)ADD
12345)OIY5678)0)ADD
12345)X678GHR)1)ADD

Explanation:

1. The tac command reorders the file from last line to first line; this gets the REM lines to appear before the matching ADD lines. Another tac is used at the end to restore the original order.
2. The perl command switches could be combined to -nle, but were left separate for clarity:
   • -n = don't automatically print each line
   • -l = strip the newline character from the end of each input line, and add it back for any print commands
   • -e = specifies ("enters") a line of the Perl script
3. The Perl code is doing quite a lot:
   • The ?: operator is an if/then/else: condition?expr1:expr2 - if the condition is true, then run expr1; otherwise run expr2
   • If we can successfully strip "REM" from the end of the line (and thus end up with something like "12345)X678GHR)0)", then we'll append an "ADD" to it & stick it onto a "hit list"
   • Otherwise (meaning we have an ADD), we print the record unless it's on the "hit list"; whether or not it was there, we remove the record from the "hit list"

"Hit list" is a colloquial term for things to kill/eliminate. In this Perl code, it's implemented not as a list but as the keys to an (unordered) hash a.k.a. associative array, so that we can do quick lookups. The value tied to each key does not (much) matter; here we use the number 1. (We could've used any value except 0 or undef.)

Assumptions:

1. You're comparing the entire line (all the columns) except the ADD/REM at the end.
2. It's acceptable to never print REM lines (even if there's no corresponding ADD line).
3. Nested cancellations will not occur. E.g., an ADD/ADD/REM/REM - in which the 3rd line cancels the 2nd & the 4th line cancels the 1st - will not occur.