0

I'm still learning programming and I've tried many things but just cannot get the correct format. I have a tab delimited file with 17 columns and many (around 50.000) rows. The file is already sorted by first column. I want to merge rows that have the same first column (A), but all other 16 columns are different and I want to keep all the information in one row, preferably in the same column with semicolon ; as a delimiter between them. I want to keep tab as a delimiter in the output file. Thank you so much for the answers and if you could also explain the answer where I went wrong that would be even better :).

I've tried so far:

awk -F'\t' 'NF>1{a[$1] = a[$1]";"$2}END{for(i in a){print i""a[i]}}' filename.txt

perl -F',' -anle 'next if /^$/;$h{$F[0]} = $h{$F[0]}.", ".$F[1];
END{print $_,$h{$_},"\n" for sort keys %h}' filename.txt

FILE FORMAT (other 15 columns have the same format as column B)

A     B     C    
123   fvv   ggg
123   kjf   ggg
123   ccd   att
567   abc   gst
567   abc   hgt
879   ttt   tyt

The output I want (I need all 17 columns and for columns 2-16 I need the same output as in column B and C). All cases of B should be under B and all cases of C should be under C and all cases of D should be under D etc. So the output has 17 columns just like the input and instead of 50.000 rows, it now should have around 20.000, because there are many repeats for column 1 (for this particular file):

A     B                C
123   fvv;kjf;ccd      ggg;ggg;att
567   abc;abc          gst;hgt
879   ttt              lll
  • sed ':1;$!N;s/^\(\(\S\+\s\+\).*\)\n\2/\1;/;t1;P;D' filename.txt – Costas Feb 18 '16 at 12:44
  • This doesn't do exactly what I want. It gives output in such a way that it repeats all 16 columns in this way (but for 16): 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 and since i have for some cases 50 rows, with the same 1st row, this makes it hard to read as well as give an error due to so many columns (16 x 50 = 800 !!) . – Fluorine Feb 18 '16 at 12:55
  • Hang on, what output do you want for the multiple columns? Should all different cases of "B" be under "B"? Or should they be concatenated? Please edit your question and add a more representative example of your input and desired output so we can see how you want us to deal with the multiple columns. – terdon Feb 18 '16 at 13:23
  • Yes all cases of B should be under B and all cases of C should be under C and all cases of D should be under D etc. So the output has 17 columns just like the input and instead of 50.000 rows, it now should have around 20.000 (for this particular file). – Fluorine Feb 18 '16 at 14:48
  • If you don't mind having the values separated by commas instead of semicolons you could try with gnu datamash e.g. assuming infile is sorted by 1st column you could run { head -n 1; datamash -g 1 $(printf 'collapse %s ' {2..15}); } <infile | column -t – don_crissti Feb 18 '16 at 15:32
1
awk '{
      if(NR!=1){a[$1]=$2";"a[$1]}
      else print $0}
    END{
      n = asorti(a, b);
      for (n in b) {
      print b[n],a[b[n]]
      }
    }'
0

A perl solution:

$ perl -F"\t" -anle 'if($.==1){print; next} push @{$k{$F[0]}},@F[1..$#F]; 
  END{print "$_\t" . join(";",@{$k{$_}}) for sort keys(%k)}' file 
A   B   
123 fvv;kjf;ccd
567 abc;abc
879 ttt

This can work on an arbitrary number of fields. It does, however, require loading quite a few things into memory and that might be a problem if your file is large.


As for where you went wrong, we can't tell you unless you explain what actually happened but, off the top of my head, you perl attempt would fail because:

  • You are using -F, which sets the field separator to a comma when your input has tabs.
  • You are using -l and print "foo\n". The -l already adds a newline to each print call, so you'll have multiple, blank lines.
  • You are using $h{$F[0]}.", ".$F[1]; to append, so the first time that is run and $h{$F[0]} is not defined, you will add an extra , at the beginning of your stored value.
  • You are only looking at the second field, ignoring all others.

Similarly, your awk will fail because:

  • You are printing foo""bar which will concatenate the output with no space between each field. You want print foo,bar and you also want OFS="\t" for tab-separated output.
  • You are only looking at the second field, ignoring all others.
0

apologies for this one-liner, but here it goes --

awk 'BEGIN{FS="\t"} {for(i=2; i<=NF; i++) { if (!a[$1]) a[$1]=$1FS$i ;else a[$1]=a[$1]";"$i};if ($1 != old) b[j++] = a[old];old=$1 } END{for (i=0; i<j; i++) print b[i] }' 1

123 fvv ;kjf;ccd
567 abc;abc
879 ttt
0

Perl can do this handily, using a hash:

#!/usr/bin/env perl

use strict;
use warnings;

my %stuff;
my @header = split ' ', <>;

#read in the data to "stuff"
while ( <> ) { 
   my ( $key, $value ) = split; 
   push ( @{$stuff{$key}}, $value ); 
}

print join ("\t", @header ),"\n"; 
foreach my $key ( sort keys %stuff ) {
   print $key, "\t", join ";", @{$stuff{$key}},"\n";
}

Output:

A   B
123 fvv;kjf;ccd;
567 abc;abc;
879 ttt;

Where you went wrong? Honestly, I would suggest it's in trying to compress it all into a one liner. That is - in my opinion - really bad practice. At best, it promotes inscrutable code that's hard to follow.

The above could be condensed down, but it really pays to do it longhand first.

In order to support multiple columns, then you start to hit a slight bit of a nuisance with column widths.

This works, but produces output that indentation isn't neatly aligned:

#!/usr/bin/env perl

use strict;
use warnings;

my %stuff; 

my ( $id, @header ) = split ' ', <>;

while ( <> ) { 
   my ( $key, @values ) = split; 
   my %row;
   @row{@header} = @values; 
   push ( @{$stuff{$key}{$_}}, $row{$_} ) for keys %row;
}

print join ( "\t", $id, @header),"\n";
foreach my $key ( sort keys %stuff ) {
   print join ("\t", $key, map { join ";", @{$stuff{$key}{$_}}} @header), "\n";
}

Output of:

A   B   C
123 fvv;kjf;ccd ggg;ggg;att
567 abc;abc gst;hgt
879 ttt tyt

If tab separation isn't suitable for your needs, you can use sprintf to do the formatting:

my $format = '%12s';
print map { sprintf($format, $_) } ( $id, @header),"\n";
foreach my $key ( sort keys %stuff ) {   
   print map { sprintf($format, $_) } ( $key, map { join ";", @{$stuff{$key}{$_}}} @header),"\n";
}

We make some use of map here, which I appreciate isn't exactly an obvious thing.

What it does is take a list, and apply a transformation to each element. So - in the above example:

print join ("\y", map { join ";", @$_ } ([1,2,3],[4,5,6],[7,8,9]) )

Will generate:

1;2;3   4;5;6   7;8;9

The map operation is saying 'join each subarray on ;' and then return that as a list... that we can then join with a tab. That's basically what the above is doing.

  • I rolled back my edit (which, ironically enough, added a one-liner and was written before your warning against them) because I noticed that your solution only works for 2 fields. The OP has multiple fields and has only included 2 as an example. – terdon Feb 18 '16 at 13:17
  • Good point - I'll amend my example. (And might try and one-liner-it, but might not :)) – Sobrique Feb 18 '16 at 13:20
  • This script gives me only column 1 and 2 and for columns 3-17 only column name, rest is empty. – Fluorine Feb 18 '16 at 14:43
  • Yes, sorry, still rejigging it with the new sample input. – Sobrique Feb 18 '16 at 14:43
  • Amended. Works with sample data now. – Sobrique Feb 18 '16 at 14:52
0
awk '
    function p(n,A){
        s = n
        for(i=2;i<=NF;i++){
            s = s "\t" A[i]
            A[i] = $i
        }
        if(n)
            print s
    }
    NR==1{
        print
        next
    }
    $1==n{
        for(i=2;i<=NR;i++)
            A[i] = A[i] ";" $i
        next
    }
    {
        p(n,A)
        n = $1
    }
    END{
        p(n,A)
    }
    ' file
0

I want to merge rows that have the same first column (A), but all other 16 columns are different and I want to keep all the information in one row

You got some fine answers, but I want to point out to you that what you've described in those 30 words is known in SQL as a join. If you import your 50,000 lines into two tables in SQLite, you get the effect you want with

select * from R join S on r1 = s1

where r1 and s1 are column names that you determine.

There are a lot of advantages to using SQL for something like this, especially as the join and selection criteria grow more complex. It's one of the reasons SQL was invented.

  • I'm fairly certain that you misunderstood the problem. In SQL parlance, the problem most resembles a GROUP BY where the aggregating functions concatenate strings. – Barefoot IO Feb 18 '16 at 23:22
  • Not a misunderstanding, just a simplification. The JOIN is the hard part (and it is a join). The concatenation is easy. It could be done in the caller, or in SQL with SELECT r1, r2 || ';' || s2, r3 || ';' s3, etc. – James K. Lowden Feb 18 '16 at 23:37

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.