Join files on multiple fields Post: 302950784

Sponsored Content

Top Forums Shell Programming and Scripting Join files on multiple fields Post 302950784 by Aia on Tuesday 28th of July 2015 03:31:51 PM

07-28-2015

Registered User

See if this is of any use.

Code:

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

# two files must be given at command line
my $first_file = shift or die;
my $second_file = shift or die;

my %data;
my $f;

# process lines from first file
open $f, "<", $first_file or die "$!\n";
while(<$f>) {
    # split line into fields
    my @fields = split;
    # create a key based on first two fields separated by tab
    my $key = join "\t", @fields[0..1];
    # add to data structure and append a list as place holder
    # for second file data
    $data{$key} = [@fields[2..$#fields], ("X23", 0, 0, 0, 0)];
}
close $f;

# process all lines from second file
open $f, "<", $second_file or die "$!\n";
while(<$f>) {
    my @fields = split;
    my $key = join "\t", @fields[0..1];
    # the same key exist in first and second file
    # remove the place holder data
    if(exists $data{$key}){
        $data{$key} = [@{$data{$key}}[0..4], @fields[2..$#fields]];
    # key only exist in second file. Add padding in front.
    }else{
        $data{$key} = [("XX2720", 0, 0, 0, 0), @fields[2..$#fields]]
    }
}
close $f;

for my $k (keys %data) {
    print join "\t", ($k, @{$data{$k}});
    print "\n";
}

Save: mergex.pl
Run: perl mergex.pl file1 file2

This User Gave Thanks to Aia For This Post:

Aia

View Public Profile for Aia

Find all posts by Aia

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

join on multiple fields

Is it possible to do a join on multiple fields of two files? I am trying to do something like join -t, -1 2,3 -2 2,3 -o 2.1,2.2,2.3,1.3 filea fileb I want the join to be on columns 2 and 3 of filea and columns 2 and 3 of fileb. What is hapenning is that the second file that I want to do the join...

2. Shell Programming and Scripting

join on a file with multiple lines, fields

I've looked at the join command which is able to perform what I need on two rows with a common field, however if I have more than two rows I need to join all of them. Thus I have one file with multiple rows to be joined on an index number: 1 randomtext1 2 rtext2 2 rtext3 3 rtext4 3 rtext5...

3. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7...

4. Shell Programming and Scripting

How to join multiple files?

I am trying to join a few hundred files using join. Is there a way to use while read or something else to automate this. My problem is the following. Day 1 City Temp ABC 20 DEF 30 HIJ 15 Day 2 City Temp ABC 22 DEF 29 KLM 5 Day 3

5. UNIX for Dummies Questions & Answers

Need help with Join on multiple fields

Hi, I need help with the join command I have 2 files that I want to join on multiple fields. I want to return all records from file 1 I also want empty fields in my joined file if there isn't a match in file 2 I have already sorted them so I know they are in the same order. file1 ...

6. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the...

7. Shell Programming and Scripting

awk program to join 2 fields of different files

Hello Friends, I just need a small help, I need an awk program which can join 2 fields of different files which are having one common field into one file. File - 1 FileName~Size File- 2 FileName~Date I need the output file in the following way O/P- File FileName~Date~Size For...

8. Shell Programming and Scripting

Join fields from files with duplicate lines

I have two files, file1.txt: 1 abc 2 def 2 dgh 3 ijk 4 lmn file2.txt 1 opq 2 rst 3 uvw My desired output is: 1 abc opq 2 def rst 2 dgh rst 3 ijk uvw

9. Shell Programming and Scripting

Join fields comparing 4 fields using awk

Hi All, I am looking for an awk script to do the following Join the fields together only if the first 4 fields are same. Can it be done with join function in awk?? a,b,c,d,8,,, a,b,c,d,,7,, a,b,c,d,,,9, a,b,p,e,8,,, a.b,p,e,,9,, a,b,p,z,,,,9 a,b,p,z,,8,, desired output: ...

10. Shell Programming and Scripting

Join multiple files

Hi there, I am trying to join 24 files (i showed example of 3 files below). They all have 2 columns. The first columns is common to all. The files are tab delimited eg file 1 rs0001 100e-34 rs0003 2.8e-01 rs008 1.9e-90 file 2 rs0001 1.98e-22 rs0004 3.77e-10...

LEARN ABOUT DEBIAN

gchem3d

GCHEM3D(1)						       gnome-chemistry-utils							GCHEM3D(1)

NAME

       gchem3d - a small chemical viewer application

SYNOPSIS

       gchem3d [OPTION(S)...] [FILE...]

DESCRIPTION

       gchem3d is a small chemical viewer application, which can show several chemical file formats.

OPTIONS

       The following options are accepted:

       -b COLOR, --bgcolor=COLOR
	   Use the given color as background color.  COLOR can be one of "black" (default), "white", "#rrggbb" (don't forget to escape the "#"
	   character in the shell).

       -d MODEL, --display3d=MODEL
	   Choose how molecules are displayed.	MODEL can be one of "BallnStick" (default), "SpaceFill".

       -?, --help
	   Show application help options.

       --help-all, --help-*
	   Print all or just a group of help options. These options are not documented here. Instead see gtk-options(7) and gnome-options(7).

       -v, --version
	   Print gchem3d version information.

SEE ALSO

       gnome-options(7), gtk-options(7)

AUTHORS

       Jean Brefort <jean.brefort@normalesup.org>
	   Program author.

       Daniel Leidert <daniel.leidert@wgdd.de>
	   Manpage author.

COPYRIGHT

       Copyright (C) 2002-2007 Jean Brefort
       Copyright (C) 2004-2007 Daniel Leidert

       Permission is granted to copy, distribute and/or modify this document under the terms of the GNU General Public License, Version 2 or any
       later version published by the Free Software Foundation.

gcu 0.12				      $Date: 2009-03-19 10:53:47 +0100 (jeu. 19 mars 2009) $					GCHEM3D(1)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

join on multiple fields

Discussion started by: reggiej

2. Shell Programming and Scripting

join on a file with multiple lines, fields

Discussion started by: crimper

3. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Discussion started by: InfoSeeker

4. Shell Programming and Scripting

How to join multiple files?

Discussion started by: theFinn