Merge multiple lines in same file based on column 1

Question

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

Answer 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]]
      }
    }'

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.