The UNIX and Linux Forums  

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
Column Merge? Column Merge?
Google UNIX.COM
Forums Portal Register Forum Rules FAQContribute Members List Arcade Search Today's Posts Mark Forums Read


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
compare the column from 3 files and merge that line ganesh_mak Shell Programming and Scripting 8 04-14-2008 04:56 AM
how to read the column and print the values under that column gemini106 Shell Programming and Scripting 6 03-28-2008 04:05 AM
How to check Null values in a file column by column if columns are Not NULLs Mandab Shell Programming and Scripting 7 03-15-2008 06:57 AM
extracting/copy a column into a new column folashandy UNIX for Advanced & Expert Users 1 02-21-2008 10:24 AM
Replace 10th column with a new column--- Terriblly hurry ahmedwaseem2000 Shell Programming and Scripting 2 09-05-2005 10:10 PM

Reply
 
Submit Tools LinkBack Thread Tools Search this Thread Display Modes
  #1  
Old 10-22-2005
Registered User
  

Join Date: Sep 2005
Posts: 27
Cool Column Merge?
Hi all,

I need to join two columns from two different files file1.txt and file2.txt and append in a new file. Example :
$cat file1.txt
ABCD.ksh:010141
ABCD.ksh:010511
ABCD.ksh:010815
ABCD.ksh:011114
ABCD.ksh:011415
ABCD.ksh:011720
ABCD.ksh:012022
ABCD.ksh:012830
ABCD.ksh:014432
ABCD.ksh:020532

$cat file2.txt
ABCD.ksh:010148
ABCD.ksh:010522
ABCD.ksh:010849
ABCD.ksh:011124
ABCD.ksh:011421
ABCD.ksh:011731
ABCD.ksh:012511
ABCD.ksh:014012
ABCD.ksh:015512
ABCD.ksh:021010

I need to get the output of something like this (say with : separator)

ABCD.ksh 10141 10148
ABCD.ksh 10511 10522
ABCD.ksh 10815 10849
ABCD.ksh 11114 11124
ABCD.ksh 11415 11421
ABCD.ksh 11720 11731
ABCD.ksh 12022 12511
ABCD.ksh 12830 14012
ABCD.ksh 14432 15512
ABCD.ksh 20532 21010

A simple output of :

10141 10148
10511 10522
10815 10849
11114 11124
11415 11421
11720 11731
12022 12511
12830 14012
14432 15512
20532 21010

May also help.

I am trying $ join -t: file1.txt file2.txt
But it results the Cartesian product (10x10=100) rows.

Any help ??

~
Reply With Quote
Forum Sponsor
  #2  
Old 10-25-2005
Registered User
  

Join Date: Oct 2005
Posts: 22
These both work for me...but maybe I am not understanding you correctly.

$join -t: file1.txt file2.txt | more
ABCD.ksh:010141:010148
ABCD.ksh:010141:010522
ABCD.ksh:010141:010849
ABCD.ksh:010141:011124
ABCD.ksh:010141:011421
ABCD.ksh:010141:011731
ABCD.ksh:010141:012511
ABCD.ksh:010141:014012
ABCD.ksh:010141:015512
ABCD.ksh:010141:021010
ABCD.ksh:010511:010148
ABCD.ksh:010511:010522
ABCD.ksh:010511:010849
ABCD.ksh:010511:011124
ABCD.ksh:010511:011421
ABCD.ksh:010511:011731
ABCD.ksh:010511:012511
ABCD.ksh:010511:014012
ABCD.ksh:010511:015512
ABCD.ksh:010511:021010
ABCD.ksh:010815:010148
ABCD.ksh:010815:010522

$join -t: file1.txt file2.txt | cut -d: -f2,3 | more
010141:010148
010141:010522
010141:010849
010141:011124
010141:011421
010141:011731
010141:012511
010141:014012
010141:015512
010141:021010
010511:010148
010511:010522
010511:010849
010511:011124
010511:011421
010511:011731
010511:012511
010511:014012
010511:015512
010511:021010
010815:010148
010815:010522
Reply With Quote
  #3  
Old 10-25-2005
Registered User
  

Join Date: Sep 2005
Posts: 27
Thanks. But..

while I am usine
$join -t: file1.txt file2.txt

I am getting the cartesian product - that is the all possible join combination 10x10 = 100 rows.

/home/mukher2> join -t: file1 file2|wc -l
100
/home/mukher2> wc -l file*
10 file1
10 file2
20 total
/home/mukher2>

I am using HP-UX B.11.11 and /usr/bin/join.


But the problem resolved when I used :

/home/mukher2> pr -tm file1 file2
ABCD.ksh:010141 ABCD.ksh:010148
ABCD.ksh:010511 ABCD.ksh:010522
ABCD.ksh:010815 ABCD.ksh:010849
ABCD.ksh:011114 ABCD.ksh:011124
ABCD.ksh:011415 ABCD.ksh:011421
ABCD.ksh:011720 ABCD.ksh:011731
ABCD.ksh:012022 ABCD.ksh:012511
ABCD.ksh:012830 ABCD.ksh:014012
ABCD.ksh:014432 ABCD.ksh:015512
ABCD.ksh:020532 ABCD.ksh:021010
/home/mukher2>

The desired output can be obtained by :

pr -tm file1 file2 |awk '{print $1":"$2}'
Reply With Quote
  #4  
Old 10-25-2005
Registered User
  

Join Date: Oct 2005
Posts: 22
sabyasm,

thanks for the reply, I understand what you are saying and glad that you have found the solution...this is another variation which makes it more pretty for you (there is probably a better way of doing it using awk BUT this is just faster for me):

$pr -tm file1.txt file2.txt | awk '{print $1":"$2}' | cut -d: -f2,4
010141:010148
010511:010522
010815:010849
011114:011124
011415:011421
011720:011731
012022:012511
012830:014012
014432:015512
020532:021010

OR

$pr -tm file1.txt file2.txt | awk '{print $1":"$2}' | cut -d: -f2,4 | sed s/\:/\ /g
010141 010148
010511 010522
010815 010849
011114 011124
011415 011421
011720 011731
012022 012511
012830 014012
014432 015512
020532 021010

Reply With Quote
  #5  
Old 10-25-2005
Registered User
  

Join Date: Sep 2005
Posts: 27
thanks a lot!
Reply With Quote
  #6  
Old 10-25-2005
vgersh99's Avatar
Moderator
  

Join Date: Feb 2005
Location: Boston, MA
Posts: 3,019
nawk -f syb.awk file1.txt file2.txt

syb.awk:
Code:
BEGIN {
  FS=":"
  OFS=" "
}

FNR==NR {
  arr[FNR] = $1 OFS $2
  next
}
{
   print arr[FNR], $2
}
Reply With Quote
Google The UNIX and Linux Forums
Reply

« Transfer script. | PERL question »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode




All times are GMT -7. The time now is 01:00 PM.

The UNIX and Linux Forums RSS Feeds - Contact Us - The UNIX and Linux Forums - Archive - Top

Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited.
The UNIX and Linux Forums Content Copyright ©1993-2008. All Rights Reserved.Ad Management by RedTyger Visit The Complex Event Processing Blog

Content Relevant URLs by vBSEO 3.2.0