Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Join 2 file using column 2 from each file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join 2 file using column 2 from each file
# 1  
Old 02-22-2012
Bug Join 2 file using column 2 from each file
I have data on file A
Code:
ABCDE3189	ABCDE3189131	-23
ABCDE3189	ABCDE3189128	20.7
ABCDE3255	ABCDE3255126	17.5
ABCDE3255	ABCDE3255130	19.1
ABCDE3255	ABCDE3255131	17.2
ABCDE3255	ABCDE3255132	20.3
ABCDE3255	ABCDE3255124	24.1


I have file B
Code:
ABCDE3189	ABCDE3189131	-105.7	-105.7 	0
ABCDE3189	ABCDE3189128	-105.8	-104.7	1.1
ABCDE3255	ABCDE3255126	-104.6	-105.2	0.7
ABCDE3255	ABCDE3255130	-105.4	-1	        0
ABCDE3255	ABCDE3255131	-105.6	-1	        0
ABCDE3255	ABCDE3255132	-106.2	-106	        0.2
ABCDE3255	ABCDE3255128	-105.6	-105.7	0.1


Now i need output form combination of two file as pre column2. If any column dont have any data i need N/I as that space....

My Final Output on File C....
Code:
ABCDE3189	ABCDE3189131	-105.7	-105.7	0	23
ABCDE3189	ABCDE3189128	-105.8	-104.7	1.1	20.7
ABCDE3255	ABCDE3255126	-104.6	-105.2	0.7	17.5
ABCDE3255	ABCDE3255130	-105.4	-1	        0	19.1
ABCDE3255	ABCDE3255131	-105.6	-1	        0	17.2
ABCDE3255	ABCDE3255132	-106.2	-106	        0.2	20.3
ABCDE3255	ABCDE3255128	-105.6	-105.7	0.1	N/I
ABCDE3255	ABCDE3255124	  N/I	          N/I	        N/I	24.1


Thanks a lot in Advance.....
Last edited by Franklin52; 02-23-2012 at 04:20 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 02-22-2012
For some reason, sort and join come to mind. Command join cannot do pipes, so sort each of the two files on the key column (with LC_ALL=C, somebody made sort default not binary) and then use join to make a Cartesian product file from them that you can process with 'while read v1 v2 v3 v4 v5 v6 v7 v8 ....' if necessary. The join output can be specified or will contain column 2 and all the other columns of file a and then file b. Check man join and man sort.
# 3  
Old 02-22-2012
I have below command but it's not give me output for all file data and it give out put as per file 2 data.
Code:
nawk 'NR==FNR{a[$2]=$3" "$4" "$5;next}{$4=a[$2]?a[$2]:"N/A"}1' file1 file2


If any one can help on this......
Last edited by Franklin52; 02-24-2012 at 03:26 AM.. Reason: Please use code tags for code and data samples, thank you
# 4  
Old 02-23-2012
To join, either you must store one file in a string addressable array and then look up the values in the second file, or sort the files and then merge the sorted data, still potentially Cartesian for duplicate keys, but your examples are keyed unique. For two unique files with all keys present in both, the sorted key merge is trivial.

Since you want to do a full outer join, in SQL-speak, use the -a option in join, and then deal with the odd lines by field count in post-processing, or a field marker field added in pre-processing (proprocess file a to be 'key a fest_of_fields' and file b to ve 'key b rest_of_fields'. The sort and join method is the most robust and versatile. It looks like your have a two column key, which can be merged by preprocessing so it is easy to restore in postprocessing. Commnds like sed or awk are great for both.

Another robust approach is to use SQL with a file to table load or a jdbc jar or odbc driver for text or csv, and do a literal SQL full outer join:
Code:
select
  case when a.f1 IS NULL then b.f1 else a.f1 end f1,
  case when a.f2 IS NULL then b.f2 else a.f2 end f2,
  b.f3,
  b.f4,
  b.f5,
  a.f3
 from
  file1 a
   full join file2 b
    on a.f2=b.f2
 order by
  case when a.f1 IS NULL then b.f1 else a.f1 end,
  case when a.f2 IS NULL then b.f2 else a.f2 end

Last edited by DGPickett; 02-24-2012 at 03:01 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. Shell Programming and Scripting

Match column 8 in file 1 with column 2 in file 2 and replace..

I am looking at the NR==FNR posts and trying to use them to achieve the following but I am not getting it. I have 2 files. I want to match column 8 in file 1 with column 2 in file 2. When they match I want to replace column 9 in file 1 with column 1 in file 2. This is and extract from file 1 ... (5 Replies)
Discussion started by: kieranfoley
5 Replies

3. Shell Programming and Scripting

Compare 2 text file with 1 column in each file and write mismatch data to 3rd file

Hi, I need to compare 2 text files with around 60000 rows and 1 column. I need to compare these and write the mismatch data to 3rd file. File1 - file2 = file3 wc -l file1.txt 58112 wc -l file2.txt 55260 head -5 file1.txt 101214200123 101214700300 101250030067 101214100500... (10 Replies)
Discussion started by: Divya Nochiyil
10 Replies

4. Shell Programming and Scripting

[Solved] Sorting a column in a file based on a column in a second file

Hello, I have two files as the following: File1: F0100020 A G F0100030 A T F0100040 A G File2: F0100040 A G BTA-28763-no-rs 77.2692 F0100030 A T BTA-29334-no-rs 11.4989 F0100020 A G BTA-29515-no-rs 127.006 I want to sort the second file based on the... (6 Replies)
Discussion started by: Homa
6 Replies

5. Shell Programming and Scripting

Awk: Need help replacing a specific column in a file by part of a column in another file

Hi, I have two input files as File1 : ABC:client1:project1 XYZ:client2-aa:project2 DEF:client4:proj File2 : client1:W-170:xx client2-aa:WT-04:yy client4:L-005A:zz Also, array of valid values can be hardcoded like Output : ABC:W:project1 XYZ:WT:project2 (1 Reply)
Discussion started by: aa2601
1 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

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

8. Shell Programming and Scripting

Join 3 files using key column in a mapping file

I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it? 1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all... (7 Replies)
Discussion started by: bigsmile
7 Replies

9. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies

10. Shell Programming and Scripting

To cut entire column from a file and apend it to another file as another column

file1.txt : india pakistan bangladesh japan canada africa USA srilanka Nepal file2.txt Delhi Tokyo washington I have to cut the first column of file1.txt and apend it with file2.txt as another column like this Delhi india Tokyo japan washington USA ... (4 Replies)
Discussion started by: sakthifire
4 Replies
Login or Register to Ask a Question