Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

join files based on a common field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting join files based on a common field
# 1  
Old 05-24-2011
join files based on a common field
Hi experts,

Would you please help me with this?
I have several files and I need to join the forth field of them based on the common first field.
here's an example...

first file:
Code:
280346 39.88 -75.08 547.8
280690 39.23 -74.83 538.7
280729 40.83 -75.08 499.2
280907 40.9 -74.4 507.8
281335 40.73 -74.35 504.8
281351 38.95 -74.93 543.8
281582 41.03 -74.42 465.2
282640 40.82 -74.28 502.2
283029 40.55 -74.87 515.2

second file:
Code:
280346 39.88 -75.08 556.6
280734 40.82 -75.08 503.1
280907 40.9 -74.4 516.8
281335 40.73 -74.35 518.0
281351 38.95 -74.93 552.8
281582 41.03 -74.42 489.3
282023 40.65 -74.3 536.5
282768 40.82 -74.28 501.0
283291 39.73 -75.08 547.7

third file:
Code:
280346 39.88 -75.08 549.3
280690 39.23 -74.83 533.8
280734 40.82 -75.08 494.7
280907 40.9 -74.4 505.2
281335 40.73 -74.35 509.8
281351 38.95 -74.93 537.1
281582 41.03 -74.42 480.8
282023 40.65 -74.3 530.1
282768 40.82 -74.28 503.3

And I'd like to have this:
Code:
280346 39.88 -75.08 547.8 556.6 549.3
280907 40.9 -74.4 507.8 516.8 505.2 
281335 40.73 -74.35 504.8 518.0 509.8
281351 38.95 -74.93 543.8 552.8 537.1 
281582 41.03 -74.42 465.2 480.8 480.8

Many thanks for your help in advance!
# 2  
Old 05-24-2011
Do you mean that you only wanna see line that have a first field match on the 3 files ?

---------- Post updated at 01:50 PM ---------- Previous update was at 01:41 PM ----------

If so here is an ugly solution that should do the job.
In my example the file names are file01, file02 and file03.
Code:
cat file?? | awk '{print $1}' | sort -u | while read i; do
   RF1="$(grep $i file01)"
   RF2="$(grep $i file02)"
   RF3="$(grep $i file03)"
   if [ -n "$RF1" -a -n "$RF2" -a -n "$RF3" ]; then
      echo "$RF1 $(echo $RF2 | awk '{print $4}') $(echo $RF3 | awk '{print $4}')"
   fi
done

# 3  
Old 05-24-2011
Thank you Chirel.
Yes I want to have the lines that matches on all the files.

The problem is that I have many files (184 files in TXT format). Is this possible to join them without being required to grep all the files one by one?
# 4  
Old 05-24-2011
If you look at the bottom of this thread, you'll find helpful hints from the related threads.
# 5  
Old 05-24-2011
Thank you vgersh99.

I looked into the related threads and I found this code helpful:
Code:
awk  '{i=$1;sub(i,x);A[i]=A[i]$0} FILENAME==ARGV[ARGC-1]{print i A[i]}' file*

I am newbie.
Could you please tell me what are ARGV and ARGC in the above code?
# 6  
Old 05-24-2011
from 'man awk':
Code:
       ARGC        The number of  command  line  arguments  (does  not
                   include options to gawk, or the program source).

       ARGV        Array of command  line  arguments.   The  array  is
                   indexed  from  0 to ARGC - 1.  Dynamically changing
                   the contents of ARGV can control the files used for
                   data.

This User Gave Thanks to vgersh99 For This Post:
GoldenFire
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies

2. UNIX for Dummies Questions & Answers

How to join 2 .txt files based on a common column?

Hi all, I'm trying to join two .txt file tab delimitated based on a common column. File 1 transcript_id gene_id length effective_length expected_count TPM FPKM IsoPct comp1000201_c0_seq1 comp1000201_c0 337 183.51 0.00 0.00 0.00 0.00 comp1000297_c0_seq1 ... (1 Reply)
Discussion started by: alisrpp
1 Replies

3. UNIX for Dummies Questions & Answers

join 2 lines based on 1st field

hi i have a file with the following lines 2303:13593:137135 16 abc1 26213806....... 1234:45675:123456 16 bbc1 9813806....... 2303:13593:137135 17 bna1 26566444.... 1234:45675:123456 18 nnb1 98123456....... i want to join the lines having common 1st field i,e., ... (1 Reply)
Discussion started by: anurupa777
1 Replies

4. 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

5. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2... (2 Replies)
Discussion started by: mindfreak
2 Replies

6. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

7. UNIX for Dummies Questions & Answers

compare two files based on common field in unix

I have two files in UNIX. 1st file is Entity and Second File is References. 1st File has only one column named Entity ID and 2nd file has two columns Entity ID | Person ID. I want to produce a output file where entity id's are matching in both the files. Entity File 624197 624252 624264... (4 Replies)
Discussion started by: PRS
4 Replies

8. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

9. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

10. Shell Programming and Scripting

Join file contents via common field

I have 2 files with a common parm - Jobname File 1 0507 1202 JOBA 0507 1302 JOBB 0507 1452 JOBC 0507 1552 JOBA 0507 1553 JOBA File2 JOBA abcdefg server4 JOBB defghij server22 JOBC vwxyz12 server55 I would like to take each line from File1 and match the jobname with the jobname... (8 Replies)
Discussion started by: Northerner
8 Replies
Login or Register to Ask a Question