Perl script help please - combining files

[AmyEllison]

I have a tab-delimited text file like this (GOTERMS):

contig11 GO:100 other columns of data
contig11 GO:289 other columns of data
contig11 GO:113 other columns of data
contig22 GO:388 other columns of data
contig22 GO:101 other columns of data

And another like this (SNPS):

contig11 3 N
contig11 1 Y
contig22 1 Y
contig22 2 N

I need to combine them so that each 'multiple' entry of one of the files is duplicated and populated with its data in the other, so that I get:

contig11 3 N GO:100 other columns of data
contig11 3 N GO:289 other columns of data
contig11 3 N GO:113 other columns of data
contig11 1 Y GO:100 other columns of data
contig11 1 Y GO:289 other columns of data
contig11 1 Y GO:113 other columns of data
contig22 1 Y GO:388 other columns of data
contig22 1 Y GO:101 other columns of data
contig22 2 N GO:388 other columns of data
contig22 2 N GO:101 other columns of data

It was suggested I store the rows of the first file in a hash indexed by the row's id:

#!/usr/bin/env/perl

use warnings;

open (GOTERMS, "$ARGV[0]") or die "Error opening the input file with GO terms";
open (SNPS, "$ARGV[1]") or die "Error opening the input file with SNPs";

my %goterm;

while (<GOTERMS>)
{
    my($id, $rest) = /^(\S++)(,*)/s;
    push @{$goterm{$id}}, $rest;
}

while (my $row2 = <SNPS>)
{
    chomp($row2);
    my ($id) = $row2 =~ /^(\S+)/;
    for my $rest (@{ $goterm{$id} })
    {
        print("$row2$rest\n");
    }
}

close GOTERMS;
close SNPS;

But I am not getting the GO column onwards in the final file, just duplicated lines of SNPS file - can anyone suggest where I'm going wrong with this script or suggest another solution?

[mastal]

Hi,

It looks to me like what you really want is to make your data into two SQL tables and perform a table join, one that gives you the union of both tables.

[mastal]

Perl script help please - combining files

Try modifying the perl script as follows:

Add

use strict;

before the 'use warnings;' line.

change

    my($id, $rest) = /^(\S++)(,*)/s;

to

   my($id, $rest) =~ /^(\S+)(.*)/;

I think the line above is the main reason the script wasn't printing the columns with the GO data.

change

   for my $rest (@{ $goterm{$id} })
    {
        print("$row2$rest\n");
    }

to

   foreach my $rest (@{$goterm{$id}})
    {
        print "$row2\t$rest");
    }

[swbarnes2]

I don't think this is right:

 my($id, $rest) = /^(\S++)(,*)/s;

I bet you want

my($id, $rest) = /^(\S+)(.*)/;

[id0]

Does this have to be done in perl?

There is a UNIX join command that can do this in one line (if I am understanding the question correctly):

http://www.albany.edu/~ig4895/join.htm

[AmyEllison]

Wooops - yep changing the comma to fullstop did the trick - what a silly typo! Thanks for pointing it out!!

[syfo]

I have a tab-delimited text file like this (GOTERMS):

contig11 GO:100 other columns of data
contig11 GO:289 other columns of data
contig11 GO:113 other columns of data
contig22 GO:388 other columns of data
contig22 GO:101 other columns of data

And another like this (SNPS):

contig11 3 N
contig11 1 Y
contig22 1 Y
contig22 2 N

I need to combine them so that each 'multiple' entry of one of the files is duplicated and populated with its data in the other, so that I get:

contig11 3 N GO:100 other columns of data
contig11 3 N GO:289 other columns of data
contig11 3 N GO:113 other columns of data
contig11 1 Y GO:100 other columns of data
contig11 1 Y GO:289 other columns of data
contig11 1 Y GO:113 other columns of data
contig22 1 Y GO:388 other columns of data
contig22 1 Y GO:101 other columns of data
contig22 2 N GO:388 other columns of data
contig22 2 N GO:101 other columns of data

It was suggested I store the rows of the first file in a hash indexed by the row's id:

#!/usr/bin/env/perl

use warnings;

open (GOTERMS, "$ARGV[0]") or die "Error opening the input file with GO terms";
open (SNPS, "$ARGV[1]") or die "Error opening the input file with SNPs";

my %goterm;

while (<GOTERMS>)
{
    my($id, $rest) = /^(\S++)(,*)/s;
    push @{$goterm{$id}}, $rest;
}

while (my $row2 = <SNPS>)
{
    chomp($row2);
    my ($id) = $row2 =~ /^(\S+)/;
    for my $rest (@{ $goterm{$id} })
    {
        print("$row2$rest\n");
    }
}

close GOTERMS;
close SNPS;

But I am not getting the GO column onwards in the final file, just duplicated lines of SNPS file - can anyone suggest where I'm going wrong with this script or suggest another solution?

Yes:

join file2 file1

[syfo]

Does this have to be done in perl?

There is a UNIX join command that can do this in one line (if I am understanding the question correctly):
http://www.albany.edu/~ig4895/join.htm

Exactly (I didn't see your post, sorry).