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
2
  • 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, 2014 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, 2014 at 22:27

2 Answers 2

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},
    }
}
1
  • thank you,,this works perfect, i`m doing some testing , will get to you if issues come up
    – Hia Sen
    Nov 3, 2014 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.

2
  • 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, 2014 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, 2014 at 9:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .