Join files on multiple fields Post: 302950831

Sponsored Content

Top Forums Shell Programming and Scripting Join files on multiple fields Post 302950831 by Scrutinizer on Wednesday 29th of July 2015 07:35:14 AM

07-29-2015

Moderator

Note that this is technically not a join operation since you are also merging on fields that the files do not have in common, so the join command would not work anyway.

Also, these are TAB separated files and you are leaving out the last 5 empty fields in each file.

I cut off the last 5 fields is to assume there are no spaces in the fields and there are no other empty fields and use the default FS instead of \t plus use $1=$1 so the empty fields are discarded. If that is OK than this could work:

Code:

awk '
  BEGIN {
    OFS="\t"
  }
  FNR==1{
    X[++c]=$3                             # save the third fied; X[1] of the first file X[2] of the second one.
  }
  {
    $1=$1                                 # Discard last 5 trailing fields because FS is default
    i=$1 OFS $2                           # set i to the first two fields, separated by OFS
  } 
  NR==FNR {                               # process first file
    A[i]=$0                               # Put record from first file into array A with first two fields as index
    next
  }
                                          # process second file
  i in A {                                # if the record exist in A, print joined record
    print A[i],$3,$4,$5,$6,$7
    delete A[i]                           # delete record since index was matched
    next
  }
  {                                       # if the record does not exist in A
    print i,X[1],0,0,0,0,$3,$4,$5,$6,$7   # print it with the remaing fields zeroed and the saved 3rd field of file 1
  } 
  END {
    for(i in A)                           # For the remaining records in file 1 that were not matched
      print A[i],X[2],0,0,0,0             # print them with the remaing fields zeroed and the saved 3rd field of file 2
  }
' file1 file2                             # process file1 first and then file 2

If that is not OK then the fields need to be assigned differently (or the trailing fields need to be discardedd in a different way) and FS should be set to \t

Last edited by Scrutinizer; 07-29-2015 at 08:54 AM..

This User Gave Thanks to Scrutinizer For This Post:

Scrutinizer

View Public Profile for Scrutinizer

Find all posts by Scrutinizer

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 V7

join

JOIN(1) 						      General Commands Manual							   JOIN(1)

NAME

       join - relational database operator

SYNOPSIS

       join [ options ] file1 file2

DESCRIPTION

       Join  forms,  on the standard output, a join of the two relations specified by the lines of file1 and file2.  If file1 is `-', the standard
       input is used.

       File1 and file2 must be sorted in increasing ASCII collating sequence on the fields on which they are to be joined, normally the  first	in
       each line.

       There  is  one line in the output for each pair of lines in file1 and file2 that have identical join fields.  The output line normally con-
       sists of the common field, then the rest of the line from file1, then the rest of the line from file2.

       Fields are normally separated by blank, tab or newline.	In this case, multiple separators count as one, and leading  separators  are  dis-
       carded.

       These options are recognized:

       -an    In addition to the normal output, produce a line for each unpairable line in file n, where n is 1 or 2.

       -e s   Replace empty output fields by string s.

       -jn m  Join on the mth field of file n.	If n is missing, use the mth field in each file.

       -o list
	      Each  output line comprises the fields specifed in list, each element of which has the form n.m, where n is a file number and m is a
	      field number.

       -tc    Use character c as a separator (tab character).  Every appearance of c in a line is significant.

SEE ALSO

       sort(1), comm(1), awk(1)

BUGS

       With default field separation, the collating sequence is that of sort -b; with -t, the sequence is that of a plain sort.

       The conventions of join, sort, comm, uniq, look and awk(1) are wildly incongruous.

																	   JOIN(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