Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Joining the files with comparing the first column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Joining the files with comparing the first column
# 1  
Old 05-18-2013
Joining the files with comparing the first column
Hi,
I have two files in the following format. I am trying to compare the first column of both the files and if the values match the rows in file tst6 should be replaced in tst1.

File tst1
Code:
S00823295|MIDDL|0|MR|019221521A||RL|STD|0|0||E
S00862481|ESSEX|0|MR|018163650A||R|STD|0|0||E
S00754609|PLYMO|0|MR|024266906A||VR|STD|0|0||E
S00645160|MIDDL|0|MR|018099911D||P|STD|0|0||E
S00732105|NORFO|0|MR|010225966A||P|STD|0|0||E
S00812038|MIDDL|0|MR|014143292A||P|STD|0|0||E
S00827283|MIDDL|0|MR|020105100A||P3|STD|0|0||E
S00815555|MIDDL|0|MR|021203506A||P|STD|0|0||E
S00827600|MIDDL|0|MR|010261774A||P|STD|0|0||E
S00827601|MIDDL|0|MR|020206784A||P|STD|0|0||E
S00640208|MIDDL|0|MR|025206271A||P|STD|0|0||E
S00843952|MIDDL|0|MR|030243897A||P|STD|0|0||E
S00543110|MIDDL|0|MR|029071547A||P|STD|0|0||E
S00858097|MIDDL|0|MR|027120380D||R3|STD|0|0||E
S00001493|ESSEX|0|MR|028205925D||RK|STD|0|0||E
S00001525|MIDDL|0|MR|032148569D||RK|STD|0|0||E
S00001541|NORFO|0|MR|030184451A||P|STD|0|0||E
S00001573|ESSEX|0|MR|033092125A||BC|STD|0|0||E
S00001589|MIDDL|0|MR|004360465D||P|STD|0|0||E
S00001606|MIDDL|0|MR|030229776A||RK|STD|0|0||E
S00001641|ESSEX|0|MR|074289965A||R|STD|0|0||E
S00001658|NORFO|0|MR|210142567A||R|STD|0|0||E
S00001723|WORCE|0|MR|028161062A||W|STD|0|0||E
S00001727|NORFO|0|MR|023162782A||P|STD|0|0||E
S00001737|MIDDL|0|MR|031204620A||N|STD|0|0||E

File tst6
Code:
S00823295|MIDDL|0002.20|MR|001187912D||R|STD|201305|001187912D||E
S00862481|1062D|0013.40|PDP|001241743A||P|EG PDP3|201305|001241743A||E
S00754609|MIDDL|0010.60|MR|001244187A||P|STD|201305|001244187A||E
S00645160|SUFFO|0001.90|MR|001246274A||R|STD|201305|001246274A||E
S00732105|ESSEX|0003.40|MR|001263767A||BC|STD|201305|001263767A||E
S00812038|MIDDL|0009.00|MR|001341347B||BC|STD|201305|001341347B||E
S00827283|NORFO|0002.20|MR|001344485A||BC|STD|201305|001344485A||E
S00815555|ESSEX|0003.40|MR|001445431A||BC|STD|201305|001445431A||E
S00827600|ESSEX|0017.10|MR|001641484A||BC|STD|201305|001641484A||E
S00827601|ESSEX|0017.10|MR|001642345A||BC|STD|201305|001642345A||E
S00640208|MIDDL|0002.20|MR|002146794A||R|STD|201305|002146794A||E
S00843952|MIDDL|0001.60|MR|002189080A||R|STD|201305|002189080A||E
S00543110|BARNS|0024.60|MR|002207034D||A|STD SAVER|201305|002207034D||E
S00858097|BRIST|0005.90|MR|002241152A||BC|STD|201305|002241152A||E
S00837608|ESSEX|0001.20|MR|002264406A||BC|STD|201305|002264406A||E
S00662488|PLYMO|0024.60|MR|002285080A||A|STD SAVER|201305|002285080A||E
S00820966|NORFO|0022.40|MR|002289133A||A|STD SAVER|201305|002289133A||E

Desired output :
Code:
S00823295|MIDDL|0002.20|MR|001187912D||R|STD|201305|001187912D||E
S00862481|1062D|0013.40|PDP|001241743A||P|EG PDP3|201305|001241743A||E
S00754609|MIDDL|0010.60|MR|001244187A||P|STD|201305|001244187A||E
S00645160|SUFFO|0001.90|MR|001246274A||R|STD|201305|001246274A||E
S00732105|ESSEX|0003.40|MR|001263767A||BC|STD|201305|001263767A||E
S00812038|MIDDL|0009.00|MR|001341347B||BC|STD|201305|001341347B||E
S00827283|NORFO|0002.20|MR|001344485A||BC|STD|201305|001344485A||E
S00815555|ESSEX|0003.40|MR|001445431A||BC|STD|201305|001445431A||E
S00827600|ESSEX|0017.10|MR|001641484A||BC|STD|201305|001641484A||E
S00827601|ESSEX|0017.10|MR|001642345A||BC|STD|201305|001642345A||E
S00640208|MIDDL|0002.20|MR|002146794A||R|STD|201305|002146794A||E
S00843952|MIDDL|0001.60|MR|002189080A||R|STD|201305|002189080A||E
S00543110|BARNS|0024.60|MR|002207034D||A|STD SAVER|201305|002207034D||E
S00858097|BRIST|0005.90|MR|002241152A||BC|STD|201305|002241152A||E
S00001493|ESSEX|0|MR|028205925D||RK|STD|0|0||E
S00001525|MIDDL|0|MR|032148569D||RK|STD|0|0||E
S00001541|NORFO|0|MR|030184451A||P|STD|0|0||E
S00001573|ESSEX|0|MR|033092125A||BC|STD|0|0||E
S00001589|MIDDL|0|MR|004360465D||P|STD|0|0||E
S00001606|MIDDL|0|MR|030229776A||RK|STD|0|0||E
S00001641|ESSEX|0|MR|074289965A||R|STD|0|0||E
S00001658|NORFO|0|MR|210142567A||R|STD|0|0||E
S00001723|WORCE|0|MR|028161062A||W|STD|0|0||E
S00001727|NORFO|0|MR|023162782A||P|STD|0|0||E
S00001737|MIDDL|0|MR|031204620A||N|STD|0|0||E

Code currently used :
Code:
join -t"|" -1 1 -2 1 -o 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 tst1 tst6 > nua7

Any help would be really appreciated.
# 2  
Old 05-18-2013
Code:
awk -F\| 'NR==FNR{A[$1]=$0;next}$1 in A{$0=A[$1]}1' tst6 tst1

This User Gave Thanks to Yoda For This Post:
nua7
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparing same column from two files, printing whole row with matching values

First I'd like to apologize if I opened a thread which is already open somewhere. I did a bit of searching but could quite find what I was looking for, so I will try to explaing what I need. I'm writing a script on our server, got to a point where I have two files with results. Example: File1... (6 Replies)
Discussion started by: mitabrev83
6 Replies

2. UNIX for Advanced & Expert Users

Comparing column from two different files

I have two files with different size and formal and want to compare all the values of one of the column in file 1 compared with values of one of the column in file 2 and return the common values as output file. can someone please help if the file size and format are same i am able to get... (4 Replies)
Discussion started by: neuroGen
4 Replies

3. Shell Programming and Scripting

Joining two files second column in first file and first column in second file

Dear all I am having two list files , i want join these two file according second column in first file and first column in second file 1st file 1 A 2 R 3 B 4 s 5 S 2 nd file A m S n B i s L R f resultent output should... (2 Replies)
Discussion started by: suryanarayana
2 Replies

4. UNIX for Dummies Questions & Answers

Comparing two text files by a column and printing values that do not match

I have two text files where the first three columns are exactly the same. I want to compare the fourth column of the text files and if the values are different, print that row into a new output file. How do I go about doing that? File 1: 100 rs3794811 0.01 0.3434 100 rs8066551 0.01... (8 Replies)
Discussion started by: evelibertine
8 Replies

5. Shell Programming and Scripting

comparing column of two different files and print the column from in order of 2nd file

Hi friends, My file is like: Second file is : I need to print the rows present in file one, but in order present in second file....I used while read gh;do awk ' $1=="' $gh'" {print >> FILENAME"output"} ' cat listoffirstfile done < secondfile but the output I am... (14 Replies)
Discussion started by: CAch
14 Replies

6. UNIX for Dummies Questions & Answers

Comparing the 2nd column in two different files and printing corresponding 9th columns in new file

Dear Gurus, I am very new to UNIX. I appreciate your help to manage my files. I have 16 files with equal number of columns in it. Each file has 9 columns separated by space. I need to compare the values in the second column of first file and obtain the corresponding value in the 9th column... (12 Replies)
Discussion started by: Unilearn
12 Replies

7. Shell Programming and Scripting

Joining multiple files based on one column with different and similar values (shell or perl)

Hi, I have nine files looking similar to file1 & file2 below. File1: 1 ABCA1 1 ABCC8 1 ABR:N 1 ACACB 1 ACAP2 1 ACOT1 1 ACSBG 1 ACTR1 1 ACTRT 1 ADAMT 1 AEN:N 1 AKAP1File2: 1 A4GAL 1 ACTBL 1 ACTL7 (4 Replies)
Discussion started by: seqbiologist
4 Replies

8. Shell Programming and Scripting

Comparing two files and printing 2nd column if match found

Hi guys, I'm rather new at using UNIX based systems, and when it comes to scripting etc I'm even newer. I have two files which i need to compare. file1: (some random ID's) 451245 451288 136588 784522 file2: (random ID's + e-mail assigned to ID) 123888 xc@xc.com 451245 ... (21 Replies)
Discussion started by: spirm8
21 Replies

9. Shell Programming and Scripting

Joining paragraph and comparing the difference

Hi Power User,:) I have this tipe of file: file1: HEAD HAIR FINGER NAIL THUMB HEAD HAIR NOSE HEAD LEG ARM (0 Replies)
Discussion started by: anjas
0 Replies

10. Shell Programming and Scripting

joining files based on key column

Hi I have to join two files based on 1st column where 4th column of a2.txt=at and take 2nd column of a1.txt and 3rd column of a2.txt and check against source files ,if matches list those source file names. a1.txt a1|20090809|20090810 a2|20090907|20090908 a2.txt a1|d|file1.txt|at... (9 Replies)
Discussion started by: akil
9 Replies
Login or Register to Ask a Question