Merging two files based on first column and return

Question

I am fairly new to Perl so hopefully this has a quick solution.

I have been trying to combine two files based on a key. The problem is there are multiple values instead of the one it is returning. Is there a way to loop through the hash to get the 1-10 more values it could be getting?

Example:

File Input 1:

12345|AA|BB|CC
23456|DD|EE|FF

File Input2:

12345|A|B|C
12345|D|E|F
12345|G|H|I
23456|J|K|L
23456|M|N|O
32342|P|Q|R

The reason I put those last one in is because the second file has a lot of values I don’t want but file 1 I want all values. The result I want is something like this:

WANTED OUTPUT:

12345|AA|BB|CC|A|B|C
12345|AA|BB|CC|D|E|F
12345|AA|BB|CC|G|H|I
23456|DD|EE|FF|J|K|L
23456|DD|EE|FF|M|N|O

Attached is the code I am currently using. It gives an output like so:

OUTPUT I AM GETTING:

12345|AA|BB|CC|A|B|C
23456|DD|EE|FF|J|K|L

My code so far:

#use strict; 
#use warnings; 

open file1, "<FILE1.txt"; 
open file2, "<FILE2.txt"; 

while(<file2>){

    my($line) = $_; 
    chomp $line; 
    my($key, $value1, $value2, $value3) = $line =~ /(.+)\|(.+)\|(.+)\|(.+)/; 
    $value4 = "$value1|$value2|$value3"; 
    $file2Hash{$key} = $value4; 
} 

while(<file1>){ 

    my ($line) = $_; 
    chomp $line; 
    my($key, $value1, $value2, $value3) = $line =~/(.+)\|(.+)\|(.+)\|(.+)/; 

    if (exists $file2Hash{$key}) {

        print $line."|".$file2Hash{$key}."\n";
    } 
    else {
        print $line."\n";
    } 
} 

Thank you for any help you may provide,

Answer 1

I assume that each key in FILE1.txt is unique and that each unique key has at least one corresponding line in FILE2.txt.

Your approach is then quite close to what you need, you should just use FILE1.txt to create the hash from (as already mentioned here).

The following should work:

#!/usr/bin/perl

use strict;
use warnings;

my %file1hash;

open file1, "<", "FILE1.txt" or die "$!\n";
while (<file1>) {
    my ($key, $rest) = split /\|/, $_, 2;
    chomp $rest;
    $file1hash{$key} = $rest;
}
close file1;

open file2, "<", "FILE2.txt" or die "$!\n";
while (<file2>) {
    my ($key, $rest) = split /\|/, $_, 2;
    if (exists $file1hash{$key}) {
        chomp $rest;
        printf "%s|%s|%s\n", $key, $file1hash{$key}, $rest;
    }
}
close file2;

exit 0;

Answer 2

Your overall idea is sound. However in file2, if you encounter a key you have already defined, you overwrite it with a new value. To work around that, we store an array(-ref) inside our hash.

So in your first loop, we do:

push @{$file2Hash{$key}}, $value4; 

The @{...} is just array dereferencing syntax.

In your second loop, we do:

if (exists $file2Hash{$key}){
  foreach my $second_value (@{$file2Hash{$key}}) {
    print "$line|$second_value\n";
  }
} else {
  print $line."\n";
}

Beyond that, you might want to declare %file2Hash with my so you can reactivate strict.

Answer 3

Keys in a hash must be unique. If keys in file1 are unique, use file1 to create the hash. If keys are not unique in either file, you have to use a more complicated data structure: hash of arrays, i.e. store several values at each unique key.