Home Man
Search
Today's Posts
Register

This forum is closed for new posts. Please post beginner questions to learn unix and learn linux in the UNIX for Beginners Questions & Answers forum.

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

Tags
beginners

 

 
Thread Tools Search this Thread
# 1  
Old 07-05-2012
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 resulting output file, the first column is the common column that is present in all 20 files and the following sets of two columns after that are the last two columns of each text file (i.e. columns 2 and 3 are columns 2 and 3 of GSM1.txt, columns 4 and 5 are columns 2 and 3 of GSM 2.txt and so on...)

How do I go about doing that? Thanks!
# 2  
Old 07-05-2012
join file1 file2 file3?
The Following User Says Thank You to Corona688 For This Useful Post:
evelibertine (07-05-2012)
# 3  
Old 07-05-2012
Actually I get the error message

join: extra operand `3.txt'


When I try
Code:
join 1.txt 2.txt 3.txt > output.txt

# 4  
Old 07-05-2012
Check join man page; join can take only 2 files at a time. You'll need it run it in a loop or through xargs.
# 5  
Old 07-05-2012
Thanks, I didn't realize that.

Okay then:

Code:
awk -F"\t" -v OFS="\t" 'F!=FILENAME { FNUM++; F=FILENAME }

{       COL[$1]++;        C=$1; $1="";        A[C, FNUM]=$0 }

END {
        for(X in COL)
        {
                printf("%s", X);
                for(N=1; N<=FNUM; N++) printf("%s", A[X, N]);
                printf("\n");
        }
}' file1 file2 file3 file4 ...

The Following User Says Thank You to Corona688 For This Useful Post:
evelibertine (07-05-2012)
# 6  
Old 07-05-2012
extend this to the number of files you have
Code:
join GSN1.txt GSN2.txt > tmp.tmp     
for f in GSN3.txt GSN4.txt GSN5.txt  
do                                   
    join tmp.tmp $f > tmpf           
    mv tmpf tmp.tmp                  
done                                 
mv tmp.tmp GSN_ALL.txt               
cat GSN_ALL.txt

The Following User Says Thank You to jim mcnamara For This Useful Post:
evelibertine (07-05-2012)
 

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Join columns across multiple lines in a Text based on common column using BASH nv186000 Shell Programming and Scripting 6 03-06-2018 08:14 AM
Join 2nd column of multiple files paolo.kunder Shell Programming and Scripting 5 10-28-2015 05:40 AM
Join common patterns in multiple lines into one line Harrisham Shell Programming and Scripting 4 02-04-2014 07:53 AM
How to join 2 .txt files based on a common column? alisrpp UNIX for Dummies Questions & Answers 1 05-18-2013 12:10 AM
how to join two files using "Join" command with one common field in this problem? mindfreak UNIX for Dummies Questions & Answers 2 04-13-2012 05:55 AM
Perl join two files by "common" column yifangt Web Programming 5 02-07-2011 08:30 AM
Join multiple files based on 1 common column quincyjones Shell Programming and Scripting 9 12-17-2010 01:17 AM
Join multiple files by column with awk macsx82 Shell Programming and Scripting 10 09-18-2010 04:56 PM
Join 2 files with multiple columns: awk/grep/join? InfoSeeker UNIX for Dummies Questions & Answers 3 12-01-2009 07:45 PM
Command line tool to join multiple .wmv files? karman OS X (Apple) 2 09-23-2007 01:52 AM


All times are GMT -4. The time now is 11:17 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password