2

I asked a similar question yesterday (Merging two tables including multiple ocurrence of column identifiers) but ran into a problem with unique lines.

I would like to merge two tables based on column 1:

File 1:

1 today
1 green
2 tomorrow
3 red

File 2:

1 a lot
1 sometimes
2 at work
2 at home
2 sometimes
3 new
4 a lot
5 sometimes
6 at work

Desired output (file 3):

1 today a lot
1 today sometimes
1 green a lot
1 green sometimes
2 tomorrow at work
2 tomorrow at home
2 tomorrow sometimes
3 red new

I came up with the following:

awk -F '[\t]' -v OFS='\t' '{i=$1;$1=x} NR==FNR{A[i]=$0;next} A[i]{print i,$0A[i]}' file2 file1 > file3

However, it gives me only:

1 today sometimes
2 tomorrow sometimes
3 red new

Please note that the solutions in the previous thread (join and awk) would give me a combination of the 2 files including all lines. I would like to have only the lines of file 1 (column 1 as the identifier) but report all matching occurrences in file 2.

Edit:

columns are tab separated

Real File 1: fig|395961.4.peg.2627 Bacteria Cyanobacteria unknown unknown 1795

(Column1: fig... Column2: Bacteria... Column3 1795)

Real File 2: fig|1000561.3.peg.1838 Cysteine desulfurase (EC 2.8.1.7) Test - Thiamin Cofactors, Vitamins, Prosthetic Groups, Pigments

(Column1: fig... Column2: Cysteine... Column3 Test...)

1

I would do this in Perl:

#!/usr/bin/env perl 
use strict;

my (%file1,%file2);

## Open the 1st file
open(A,"file1");
while(<A>){
    ## Remove trailing newlines
    chomp; 
    ## Split the current line on tabs into the @F array.
    my @F=split(/\t/); 
    ## This is the tricky part. It adds fields 2-last
    ## to the hash $file1. The value of this hash is an array
    ## and the keys are the 1st fields. This will result in a list
    ## of all 1st fields and all their associated columns.
    push @{$file1{$F[0]}},@F[1..$#F];
} 


## Open the 2nd file
open(B,"file2");
while(<B>){
    ## Remove trailing newlines
    chomp; 
    ## Split the current line on tabs into the @F array.
    my @F=split(/\t/); 

    ## If the current 1st field was found in file1
    if (defined($file1{$F[0]})) {
        ## For each of the columns associated with
        ## this 1st field in the 1st file.
        foreach my $col (@{$file1{$F[0]}}) {
            print "$F[0]\t$col\t@F[1..$#F]\n";
        }
    }
} 

You could golf it into a (long) one-liner:

$ perl -lane 'BEGIN{open(A,"file1"); while(<A>){chomp; @F=split(/\t/); 
                    push @{$k{$F[0]}},@F[1..$#F];}  } 
              $k{$F[0]} && print "$F[0]\t@{$k{$F[0]}}\t@F[1..$#F]"' file2
1   today green a lot
1   today green sometimes
2   tomorrow    at work
2   tomorrow    at home
2   tomorrow    sometimes
3   red new

If you're working with huge files, let it run a while.

  • elegant, fast with big files (few sec in GB range), and exactly what i was looking for. thank you! hope this helps other people too – BSP Sep 17 '14 at 8:29
2

Using awk

USING FUNCTIONS

Legible:

    awk 'function get(file,x,y) {
        while ( (getline < file) > 0) {if ($1==x)y,substr($0,index($0," ")+1)}
        close(file)
        }
        ARGV[1]==FILENAME{get(ARGV[2],$1,$0)}' file1 file2

Single Line:

awk 'function g(f,x,y){while((getline <f)>0)if($1==x){print y,substr($0,index($0," ")+1)}close(f)}NR==FNR{g(ARGV[2],$1,$0)}' file1 file2

.

USING AN ARRAY

awk 'FNR==NR{a[$0]=$1;next}{for(i in a)if(a[i]==$1)print i,substr($0,index($0," ")+1)}' file file2

.

RESULT

1 today a lot
1 today sometimes
1 green a lot
1 green sometimes
2 tomorrow at work
2 tomorrow at home
2 tomorrow sometimes
3 red new
  • Mark it as answered if it is what you wanted :) – user78605 Sep 16 '14 at 11:56
  • sorry to bother again..when I apply this to a file of several GB the process takes very long (actually it didnt end until now). do you have any idea what might slow down the process? other parsing commands (e.g. my presented code) only take a few seconds with the same files – BSP Sep 16 '14 at 13:23
  • @BSP Did you use the function one or the array ? – user78605 Sep 16 '14 at 13:27
  • >@Jidder it seems both commands won't finish their work :( is it possible the reason is my working files (see edit in original post) have multiple columns? – BSP Sep 16 '14 at 14:51
  • You're losing the second column of the output. I get 1 today lot for example instead of 1 today a lot. – terdon Sep 16 '14 at 22:15
0

Whats wrong with the join command ?

join file1 file2

Gives the required result

  • right ..but for Mac users you sometimes get weird results..so I always have to carefully check the outcome – BSP Sep 17 '14 at 8:32
  • Do you have an example that flakes ? This specific example (generated via copy/paste) worked fine on my mac. – Amitabh Khashnobish Sep 18 '14 at 18:09

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.