I have 4 columns in my tab delimited data, I need to perform 2 steps.Group the data according to combinations of first 2 columns.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 likeLine    Group   Name    ValueL1  G1  AX27    AL1  G1  AX25    TL1  G1  AX356   GL1  G2  1X87    CL1  G2  AX78    AL1  G2  AX987   AL1  G2  AX001B  AL1  G3  A2XTR1  AL1  G3  A2XTR2  TL2  G1  AX27    AL2  G1  AX25    TL2  G1  AX356   CL2  G2  1X87    GL2  G2  AX78    TL2  G2  AX987   AL2  G2  AX001B  AL2  G3  A2XTR1  AL2  G3  A2XTR2  TL3  G1  AX25    TL3  G1  AX356   GL3  G2  AX987   AL3  G2  AX001B  AL3  G3  A2XTR1  AL3  G3  A2XTR2  CL4  G1  AX27    AL4  G1  AX25    TL4  G1  AX356   GL4  G3  A2XTR1  AL4  G3  A2XTR2  CL5  G3  A2XTR1  AL5  G3  A2XTR2  TThe intermediate output file looks likeLine    Group   Collapsednames  CollapsedvaluesL1  G1  AX27 AX25 AX356 A T G L2  G1  AX27 AX25 AX356 A T CL3  G1  AX25 AX356  T GL4  G1  AX27 AX25 AX356 A T G L1  G2  1X87 AX78 AX987 AX001B  C A A AL2  G2  1X87 AX78 AX987 AX001B  G T A A L3  G2  AX987 AX001B    A AL1  G3  A2XTR1 A2XTR2   A TL2  G3  A2XTR1 A2XTR2   A TL3  G3  A2XTR1 A2XTR2   A CL4  G3  A2XTR1 A2XTR2   A CL5  G3  A2XTR1 A2XTR2   A TFor 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 likeLine    Group   Collapsednames  Collapsedvalues Alpha_Category  Number_CategoryL1  G1  AX27 AX25 AX356 A T G   A   1L2  G1  AX27 AX25 AX356 A T C   B   2L3  G1  AX25 AX356  T G C   3L4  G1  AX27 AX25 AX356 A T G   A   1L1  G2  1X87 AX78 AX987 AX001B  C A A A A   1L2  G2  1X87 AX78 AX987 AX001B  G T A A     B   2L3  G2  AX987 AX001B    A A C   3L1  G3  A2XTR1 A2XTR2   A T A   1L2  G3  A2XTR1 A2XTR2   A T A   1L3  G3  A2XTR1 A2XTR2   A C B   2L4  G3  A2XTR1 A2XTR2   A C B   2L5  G3  A2XTR1 A2XTR2   A T A   1Here 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
Grouping data and assigning alphabet and numeric values for new categories
awk;perl
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/perluse 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},    }}