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

set_color

set_color(1)							       fish							      set_color(1)

NAME

       set_color - set_color - set the terminal color

set_color - set the terminal color
   Synopsis
       set_color [-v --version] [-h --help] [-b --background COLOR] [COLOR]

   Description
       Change the foreground and/or background color of the terminal. COLOR is one of black, red, green, brown, yellow, blue, magenta, purple,
       cyan, white and normal.

       o -b, --background Set the background color

       o -c, --print-colors Prints a list of all valid color names

       o -h, --help Display help message and exit

       o -o, --bold Set bold or extra bright mode

       o -u, --underline Set underlined mode

       o -v, --version Display version and exit

       Calling set_color normal will set the terminal color to whatever is the default color of the terminal.

       Some terminals use the --bold escape sequence to switch to a brighter color set. On such terminals, set_color white will result in a grey
       font color, while set_color --bold white will result in a white font color.

       Not all terminal emulators support all these features. This is not a bug in set_color but a missing feature in the terminal emulator.

       set_color uses the terminfo database to look up how to change terminal colors on whatever terminal is in use. Some systems have old and
       incomplete terminfo databases, and may lack color information for terminals that support it. Download and install the latest version of
       ncurses and recompile fish against it in order to fix this issue.

Version 1.23.1							  Sun Jan 8 2012						      set_color(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