Comparing across rows in Perl

Question

I have a file:

Name v1 v2 
Type1 ABC 32
Type1 DEF 44
Type1 XXX 45
Type2 ABC 78 
Type2 XXX 23 
Type3 DEF 22 
Type3 XXX 12 
Type4 ABC 55 
Type4 DEF 78 
Type5 ABC 99 
Type6 DEF 00

I'm trying to print only portions of this file with certain conditions which are:

• For a given name, say Type1, if there is XXX in column v1, I want to skip printing all occurrences of Type1 in that file.
• For a given name say Type4, if there is ABC and DEF in column v1, I want to print only that row which has a smaller numeric value for v2.
• For a given name say Type5 or Type6 which has only ABC or DEF, I want to print them.

How do I go about it? I can read the file into an array, but I do not know how to search across multiples rows for a particular column.

Answer 1

The tool you need for this is hashes - which are perl's way of storing key-value pairs. Specifically - we need to preprocess your data into a hash, so we can 'look up' places where lowest values or XXX is present.

Fortunately - your third condition looks like a subset of your second - if you're just printing the lowest value, the lowest value when there's only one, is the same.

So I'd probably do it something like this:

#!/usr/bin/env perl
use strict;
use warnings;
use Data::Dumper;

#read header line, because we don't want to process it; 
#note - diamond operators are 'magic' file handles. 
#they read either piped input on STDIN, or 
#open/read files specified on command line. 
#this is almost exactly like how sed/grep work. 
my $header_line = <>;
#turn the rest of our intput into an array of arrays, split on whitespace/linefeeds. 
my @lines = map { [split] } <>;

#print for diag
print Dumper \@lines;

#this hash tracks if we've 'seen' an XXX
my %skip_type;
#this hash tracks the lowest V2 value. 
my %lowest_v2_for;
foreach my $record (@lines) {
    #we could work with $record ->[0], etc.
    #this is because I think it's more readable this way. 
    my ( $type, $v1, $v2 ) = @$record;

    #find all the lines with "XXX" - store in a hash.
    if ( $v1 eq "XXX" ) {
        $skip_type{$type}++;
    }

    #check if this v2 is the lowest for this particular type. 
    #make a note if it is. 
    if ( not defined $lowest_v2_for{$type}
        or $lowest_v2_for{$type} > $v2 )
    {
        $lowest_v2_for{$type} = $v2;
    }
}

#print for diag - things we are skipping. 
print Dumper \%skip_type;


print $header_line;

#run through our list again, testing the various conditions:
foreach my $record (@lines) {
    my ( $type, $v1, $v2 ) = @$record;

    #skip if it's got an XXX. 
    next if $skip_type{$type};
    #skip if it isn't the lowest value
    next if $lowest_v2_for{$type} < $v2;
    #print otherwise.
    print join( " ", @$record ), "\n";
}

This gives (less some diagnostic output from Dumper which can be discarded freely if you don't want it):

Name v1 v2 
Type4 ABC 55
Type5 ABC 99
Type6 DEF 00

Answer 2

My take:

perl -wE ' 
    # read the data 
    chomp( my $header = <> ); 
    my %data; 
    while (<>) { 
        chomp; 
        my @F = split; 
        $data{$F[0]}{$F[1]} = $F[2]; 
    } 

    # requirement 1 
    delete $data{Type1} if exists $data{Type1}{XXX}; 

    # requirement 2 
    if (exists $data{Type4}{ABC} and exists $data{Type4}{DEF}) { 
        if ($data{Type4}{ABC} <= $data{Type4}{DEF}) { 
            delete $data{Type4}{DEF}; 
        } 
        else { 
            delete $data{Type4}{ABC}; 
        } 
    } 

    # requirement 3 
    for my $name (qw/Type5 Type6/) { 
        delete $data{$name} unless ( 
            scalar keys %{$data{$name}} == 1 
            and (exists $data{$name}{ABC} or exists $data{$name}{DEF}) 
        ); 
    } 

    $, = " "; 
    say $header; 
    for my $name (sort keys %data) { 
        for my $v1 (sort keys %{$data{$name}}) { 
            say $name, $v1, $data{$name}{$v1}; 
        } 
    } 
' file 

outputs

Name v1 v2 
Type2 ABC 78
Type2 XXX 23
Type3 DEF 22
Type3 XXX 12
Type4 ABC 55
Type5 ABC 99
Type6 DEF 00

There are no requirements about Type2 and Type3

Answer 3

There is three different tasks. All can be done by awk:

1. Skip printing after XXX

   $1 == "Type1" {if($2 == "XXX")f=1;if(! f)print}

2. Minimum value for Type4

   $1 == "Type4" {if(min > $3 || ! min)min = $3} END{print min}

3. Choice lines for printing

   $1$2 ~ "^(Type5|Type6)(ABC|DEF)$"