0

I have 4 columns in my tab delimited data, I need to perform 2 steps.

  1. Group the data according to combinations of first 2 columns.
  2. Every time a new combination of values appear in the concatenated values (col 4) for a particular group (col2), I need to assign a new category for that row both alphabetically and numerically. If that combination has been encountered earlier, then the corresponding category is assigned.

Input file looks like

Line    Group   Name    Value
L1  G1  AX27    A
L1  G1  AX25    T
L1  G1  AX356   G
L1  G2  1X87    C
L1  G2  AX78    A
L1  G2  AX987   A
L1  G2  AX001B  A
L1  G3  A2XTR1  A
L1  G3  A2XTR2  T
L2  G1  AX27    A
L2  G1  AX25    T
L2  G1  AX356   C
L2  G2  1X87    G
L2  G2  AX78    T
L2  G2  AX987   A
L2  G2  AX001B  A
L2  G3  A2XTR1  A
L2  G3  A2XTR2  T
L3  G1  AX25    T
L3  G1  AX356   G
L3  G2  AX987   A
L3  G2  AX001B  A
L3  G3  A2XTR1  A
L3  G3  A2XTR2  C
L4  G1  AX27    A
L4  G1  AX25    T
L4  G1  AX356   G
L4  G3  A2XTR1  A
L4  G3  A2XTR2  C
L5  G3  A2XTR1  A
L5  G3  A2XTR2  T

The intermediate output file looks like

Line    Group   Collapsednames  Collapsedvalues
L1  G1  AX27 AX25 AX356 A T G 
L2  G1  AX27 AX25 AX356 A T C
L3  G1  AX25 AX356  T G
L4  G1  AX27 AX25 AX356 A T G 
L1  G2  1X87 AX78 AX987 AX001B  C A A A
L2  G2  1X87 AX78 AX987 AX001B  G T A A 
L3  G2  AX987 AX001B    A A
L1  G3  A2XTR1 A2XTR2   A T
L2  G3  A2XTR1 A2XTR2   A T
L3  G3  A2XTR1 A2XTR2   A C
L4  G3  A2XTR1 A2XTR2   A C
L5  G3  A2XTR1 A2XTR2   A T

For all the lines (L1 through L4) , G1 has 3 distinct combinations (A T G , A T C, TG ) where the combination (A T G) is duplicated. So we can assign the alphabetic categories A,B nad C to each of these combinations where A appears twice.

So the final output will look like

Line    Group   Collapsednames  Collapsedvalues Alpha_Category  Number_Category
L1  G1  AX27 AX25 AX356 A T G   A   1
L2  G1  AX27 AX25 AX356 A T C   B   2
L3  G1  AX25 AX356  T G C   3
L4  G1  AX27 AX25 AX356 A T G   A   1
L1  G2  1X87 AX78 AX987 AX001B  C A A A A   1
L2  G2  1X87 AX78 AX987 AX001B  G T A A     B   2
L3  G2  AX987 AX001B    A A C   3
L1  G3  A2XTR1 A2XTR2   A T A   1
L2  G3  A2XTR1 A2XTR2   A T A   1
L3  G3  A2XTR1 A2XTR2   A C B   2
L4  G3  A2XTR1 A2XTR2   A C B   2
L5  G3  A2XTR1 A2XTR2   A T A   1

Here is what I have tried for step 1, but not working. I have little idea how to proceed to step 2 , once I figure out step 1.

awk -F"\t" '{if(a[$1$2]){a[$1$2]=a[$1$2]" "$3" "$4} else { a[$1$2]=$3"\t$4}} END {for (i in a) {print i"|"a[i]}}' file
  • To make 1st step awk 'NR != 1{a[$1" "$2]=a[$1" "$2]" "$3;b[$1" "$2]=b[$1" "$2]" "$4}END{for (idx in a) print idx,a[idx],b[idx]}' | sort -k2,2 but I didn't clear understand what we need to do with 2nd step – Costas Oct 31 '14 at 21:59
  • With sorting awk 'NR != 1{SUBSEP=" "; a[$1,$2]=a[$1,$2]" "$3;b[$1,$2]=b[$1,$2]" "$4}END{for (idx in a) print idx a[idx] b[idx]}'| sort -k2,2 -k1,1 – Costas Oct 31 '14 at 22:27
1

Not as easy as it seems. Perl solution, I tried to be a bit more verbose to make the code understandable. Basic knowldedge of nested data structures (hashes of hashes, hashes of arrays) is needed.

#!/usr/bin/perl
use warnings;
use strict;
use feature qw{ say };

my (%collapsed, %groups);

<>;  # Skip header.
while (<>) {
    my ($line, $group, $name, $value) = split;
    push @{ $collapsed{$line}{$group}{names}  }, $name;
    push @{ $collapsed{$line}{$group}{values} }, $value;
    undef $groups{$group};
}

for my $group (sort keys %groups) {
    for my $line (sort keys %collapsed) {
        next unless $collapsed{$line}{$group};
        my $value = join q(), @{ $collapsed{$line}{$group}{values} };
        $groups{$group}{$value} ||= keys %{ $groups{$group} };
    }
}

for my $group (sort keys %groups) {
    for my $line (sort keys %collapsed) {
        next unless $collapsed{$line}{$group};
        my $value = join q(), @{ $collapsed{$line}{$group}{values} };
        say join "\t", $line, $group,
            join(' ', @{ $collapsed{$line}{$group}{names}  }),
            join(' ', @{ $collapsed{$line}{$group}{values} }),
            chr $groups{$group}{$value} - 1 + ord 'A',
            $groups{$group}{$value},
    }
}
  • thank you,,this works perfect, i`m doing some testing , will get to you if issues come up – Hia Sen Nov 3 '14 at 4:57
1

Awk solutions (many assotiative arrays) but:

#!/usr/bin/awk

BEGIN {
SUBSEP=" "
split("A1^B2^C3^D4^E5",c,"^")
}

NR != 1 {
L[$1]=1
G[$2]=1
a[$1,$2]=a[$1,$2]" "$3
b[$1,$2]=b[$1,$2]" "$4
}

END {
for (g in G)
  {
  i=1
  for (l in L)
    {
    idx=b[l,g]
    if(d[idx]=="")
      d[idx]=c[i++]
    }
  } 
for (k in a)
  print k a[k] b[k],d[b[k]]
}

And do the above script:

awk -f script.awk tab.data | sort -k2,2 -k1,1

If some explanation needed please ask in comments.

  • thank you ! my actual L and G names are generic and do not look like L1,G2 etc, can this be accommodated? – Hia Sen Nov 3 '14 at 4:56
  • @HiaSen I have edit the script. I didn't do such before to avoid additional 2 arrays, but in your case it is reasonable to add ones. – Costas Nov 3 '14 at 9:58

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.