Combine Lines With Matching Keys

I have a text file with the following structure ID,operator,a,b,c,d,true WCBP12236,J1,75.7,80.6,65.9,83.2,82.1 WCBP12236,J2,76.3,79.6,61.7,81.9,82.1 WCBP12236,S1,77.2,81.5,69.4,84.

Solution 1:

You can use the Perl csv module Text::CSV to extract the fields, and then store them in a hash, where ID is the main key, the second field is the secondary key and all the fields are stored as the value. It should then be trivial to do whatever comparisons you want. If you want to retain the original order of your lines, you can use an array inside the first loop.

use strict;
use warnings;
use Text::CSV;

my %data;
my $csv = Text::CSV->new({
            binary =>1,      # safety precautioneol    => $/,     # important when using $csv->print()
    });
while ( my $row = $csv->getline(*ARGV) ) {
    my ($id, $J) = @$row;   # first two fields
    $data{$id}{$J} = $row;  # store line
}

Solution 2:

Python Way:

import os,sys, re, itertools
info=["WCBP12236,J1,75.7,80.6,65.9,83.2,82.1",
  "WCBP12236,J2,76.3,79.6,61.7,81.9,82.1",
  "WCBP12236,S1,77.2,81.5,69.4,84.1,82.1",
  "WCBP12236,S2,68.0,68.0,53.2,68.5,82.1",
  "WCBP12234,J1,63.7,67.7,72.2,71.6,75.3",
  "WCBP12234,J2,68.6,68.4,41.4,68.9,80.4",
  "WCBP12234,S1,81.8,82.7,67.0,87.5,75.3",
  "WCBP12234,S2,66.6,67.9,53.0,70.7,72.7",
  "WCBP12238,J1,78.6,79.0,56.2,82.1,82.1",
  "WCBP12239,J2,66.6,72.9,79.5,76.6,75.3",
  "WCBP12239,S1,86.6,87.8,23.0,23.0,82.1",
  "WCBP12239,S2,86.0,86.9,62.3,89.7,82.1",
  "WCBP12239,J1,70.9,71.3,66.0,73.7,75.3",
  "WCBP12238,J2,75.1,75.2,54.3,76.4,82.1",
  "WCBP12238,S1,65.9,66.0,40.2,66.5,80.4",
  "WCBP12238,S2,72.7,73.2,52.6,73.9,72.7" ]

defextract_data(operator_1, operator_2):
    operator_index=1
    id_index=0
    data={}
    result=[]
    ret=[]
    for line in info:
        conv_list=line.split(",")
        iflen(conv_list) > operator_index and ((operator_1.strip().upper() == conv_list[operator_index].strip().upper()) or (operator_2.strip().upper() == conv_list[operator_index].strip().upper()) ):
            if data.has_key(conv_list[id_index]):
                iters = [iter(conv_list[int(operator_index)+1:]), iter(data[conv_list[id_index]])]
                data[conv_list[id_index]]=list(it.next() for it in itertools.cycle(iters))
                continue
            data[conv_list[id_index]]=conv_list[int(operator_index)+1:]
    return data

ret=extract_data("j1", "s2")
print ret

O/P:

{'WCBP12239': ['70.9', '86.0', '71.3', '86.9', '66.0', '62.3', '73.7', '89.7', '75.3', '82.1'], 'WCBP12238': ['72.7', '78.6', '73.2', '79.0', '52.6', '56.2', '73.9', '82.1', '72.7', '82.1'], 'WCBP12234': ['66.6', '63.7', '67.9', '67.7', '53.0', '72.2', '70.7', '71.6', '72.7', '75.3'], 'WCBP12236': ['68.0', '75.7', '68.0', '80.6', '53.2', '65.9', '68.5', '83.2', '82.1', '82.1']}

Solution 3:

I didn't use Text::CSV like TLP did. If you needed to you could but for this example, I thought since there were no embedded commas in the fields, I did a simple split on ','. Also, the true fields from both operators are listed (instead of just 1) as I thought the special case of the last value complicates the solution.

#!/usr/bin/perluse strict;
use warnings;
use List::MoreUtils qw/ mesh /;

my %data;

while (<DATA>) {
    chomp;
    my ($id, $op, @vals) = split/,/;
    $data{$id}{$op} = \@vals;
}

my @ops = ([qw/J1 J2/], [qw/S1 S2/], [qw/J1 S1/]);

formy $id (sortkeys %data) {
    formy $comb (@ops) {
        openmy $fh, ">>", "@$comb.txt"ordie $!;
        my $a1 = $data{$id}{ $comb->[0] };
        my $a2 = $data{$id}{ $comb->[1] };
        print $fh join(",", $id, mesh(@$a1, @$a2)), "\n";
        close $fh ordie $!;
    }   
}

__DATA__
WCBP12236,J1,75.7,80.6,65.9,83.2,82.1
WCBP12236,J2,76.3,79.6,61.7,81.9,82.1
WCBP12236,S1,77.2,81.5,69.4,84.1,82.1
WCBP12236,S2,68.0,68.0,53.2,68.5,82.1
WCBP12234,J1,63.7,67.7,72.2,71.6,75.3
WCBP12234,J2,68.6,68.4,41.4,68.9,75.3
WCBP12234,S1,81.8,82.7,67.0,87.5,75.3
WCBP12234,S2,66.6,67.9,53.0,70.7,75.3
WCBP12239,J1,78.6,79.0,56.2,82.1,82.1
WCBP12239,J2,66.6,72.9,79.5,76.6,82.1
WCBP12239,S1,86.6,87.8,23.0,23.0,82.1
WCBP12239,S2,86.0,86.9,62.3,89.7,82.1
WCBP12238,J1,70.9,71.3,66.0,73.7,84.1
WCBP12238,J2,75.1,75.2,54.3,76.4,84.1
WCBP12238,S1,65.9,66.0,40.2,66.5,84.1
WCBP12238,S2,72.7,73.2,52.6,73.9,84.1

The output files produced are below

J1 J2.txt

WCBP12234,63.7,68.6,67.7,68.4,72.2,41.4,71.6,68.9,75.3,75.3
WCBP12236,75.7,76.3,80.6,79.6,65.9,61.7,83.2,81.9,82.1,82.1
WCBP12238,70.9,75.1,71.3,75.2,66.0,54.3,73.7,76.4,84.1,84.1
WCBP12239,78.6,66.6,79.0,72.9,56.2,79.5,82.1,76.6,82.1,82.1

S1 S2.txt

WCBP12234,81.8,66.6,82.7,67.9,67.0,53.0,87.5,70.7,75.3,75.3
WCBP12236,77.2,68.0,81.5,68.0,69.4,53.2,84.1,68.5,82.1,82.1
WCBP12238,65.9,72.7,66.0,73.2,40.2,52.6,66.5,73.9,84.1,84.1
WCBP12239,86.6,86.0,87.8,86.9,23.0,62.3,23.0,89.7,82.1,82.1

J1 S1.txt

WCBP12234,63.7,81.8,67.7,82.7,72.2,67.0,71.6,87.5,75.3,75.3
WCBP12236,75.7,77.2,80.6,81.5,65.9,69.4,83.2,84.1,82.1,82.1
WCBP12238,70.9,65.9,71.3,66.0,66.0,40.2,73.7,66.5,84.1,84.1
WCBP12239,78.6,86.6,79.0,87.8,56.2,23.0,82.1,23.0,82.1,82.1

Update: To get only 1 true value, the for loop could be written like this:

formy $id (sortkeys %data) {
    formy $comb (@ops) {
        local $" = '';
        openmy $fh, ">>", "@$comb.txt"ordie $!;
        my $a1 = $data{$id}{ $comb->[0] };
        my $a2 = $data{$id}{ $comb->[1] };
        pop @$a2;
        my @mesh = grepdefined, mesh(@$a1, @$a2);
        print $fh join(",", $id, @mesh), "\n";
        close $fh ordie $!;
    }   
}

Update: Added 'defined' for test in grep expr. as it is the proper way (instead of just testing '$_', which possibly could be 0 and wrongly excluded for the list by grep).