Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Join files on multiple fields
Top Forums Shell Programming and Scripting Join files on multiple fields Post 302950750 by RavinderSingh13 on Tuesday 28th of July 2015 11:19:42 AM
Old 07-28-2015
Hello sheetalk,

I haven't tested in different scenarios but could you please check following if that helps you.
Code:
awk -F"\t" 'FNR==NR{A[$1]=$0;next} ($1 in A){Q=$1;$1="";gsub(/^[[:space:]]+/,X,$0);print A[Q] OFS $0;delete A[Q];next} !($1 in A){$3="XX2720" OFS 0 OFS 0 OFS 0 OFS 0 OFS "X23";print $0} END{for(i in A){gsub(/[[:space:]]+$/,X,A[i]);print A[i] OFS "X23" OFS 0 OFS 0 OFS 0 OFS 0}}' OFS="\t" file1 file2

Output will be as follows.
Code:
53039541 ST5    XX2720  1       0       1.5     1       X23     0       1       0       1
1267456  ST1    XX2720  0       0       0       0       X23     1       0       1.4     1
12658699 ST5    XX2720  0       1       0       1       X23     0       0       0       0

EDIT: Adding a non-one liner form of solution on same.
Code:
 awk -F"\t" 'FNR==NR{
                        A[$1]=$0;
                        next
                   }
                        ($1 in A){
                                        Q=$1;
                                        $1="";
                                        gsub(/^[[:space:]]+/,X,$0);
                                        print A[Q] OFS $0;
                                        delete A[Q];
                                        next
                                 }
                        !($1 in A){
                                        $3="XX2720" OFS 0 OFS 0 OFS 0 OFS 0 OFS "X23";
                                        print $0
                                  }
            END    {
                         for(i in A){
                                        gsub(/[[:space:]]+$/,X,A[i]);
                                        print A[i] OFS "X23" OFS 0 OFS 0 OFS 0 OFS 0
                                    }
                   }
           ' OFS="\t" file1 file2

Thanks,
R. Singh
Last edited by RavinderSingh13; 07-28-2015 at 12:42 PM.. Reason: Added a non-one liner form for solution too now
This User Gave Thanks to RavinderSingh13 For This Post:
sheetalk
 

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... (1 Reply)
Discussion started by: reggiej
1 Replies

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... (5 Replies)
Discussion started by: crimper
5 Replies

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... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

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 (3 Replies)
Discussion started by: theFinn
3 Replies

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 ... (0 Replies)
Discussion started by: shunter0810
0 Replies

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... (5 Replies)
Discussion started by: evelibertine
5 Replies

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... (4 Replies)
Discussion started by: abhisheksunkari
4 Replies

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 (2 Replies)
Discussion started by: xan.amini
2 Replies

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: ... (1 Reply)
Discussion started by: aksijain
1 Replies

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... (4 Replies)
Discussion started by: fat
4 Replies
All times are GMT -4. The time now is 03:31 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy