Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Matching the rows in 2 files

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Matching the rows in 2 files
# 1  
Old 12-10-2013
Matching the rows in 2 files
I have a file like this

Code:
AFF3    BCL2
AGTRAP    BRAF
AHRR    NCOA2
AKAP9    BRAF

And second input file like this

Code:
chromosome    start    end    gene
chr1    38177326    38664955    AFF3
chr4    148077060    148088064    AGTRAP
chr13    74211117    74292309    AHRR
chr5    3928185    4080204    AKAP9
chr1    106538177    106714290    GLI1
chr6    39603236    39725463    BCL2
chr1    13139158    13374083    BRAF
chr6    39603236    39725463    NCOA2
chr6    39603236    39725463    BRAF

what i want to get is something like below.

output file
Code:
AFF3    BCL2    chr1    38177326    38664955 chr6    39603236    39725463    
AGTRAP    BRAF    chr4    148077060    148088064   chr1    13139158    13374083
AHRR    NCOA2    chr13    74211117    74292309 chr6    39603236    39725463

Last edited by raj_k; 12-10-2013 at 05:29 AM.. Reason: wrongly typed the output
# 2  
Old 12-10-2013
Could you review your output spec, do you really want the previous row to the join key appended ?
# 3  
Old 12-10-2013
Hey sorry its a mistake i edited it
# 4  
Old 12-10-2013
Code:
awk '
        NR == FNR {
                A[$1 OFS $2]
                next
        }
        /^chr[0-9]/ {
                for ( k in A )
                {
                        split ( k, V )
                        if ( ( $NF == V[1] ) || ( $NF == V[2] ) )
                        {
                                r = $0
                                sub ( /[ ][A-Z0-9]*$/, x, r )
                                R[k] = R[k] ? R[k] OFS r : k OFS r
                        }
                }
        }
        END {
                for ( k in R )
                        print R[k]
        }
' OFS='\t' file1 file2

This User Gave Thanks to Yoda For This Post:
raj_k
# 5  
Old 12-10-2013
Try also:
Code:
awk 'NR==FNR{T[$4]=$1 FS $2 FS $3; next} {print $0, T[$1], T[$2]}'  file2 file1
AFF3    BCL2 chr1 38177326 38664955 chr6 39603236 39725463
AGTRAP    BRAF chr4 148077060 148088064 chr6 39603236 39725463
AHRR    NCOA2 chr13 74211117 74292309 chr6 39603236 39725463
AKAP9    BRAF chr5 3928185 4080204 chr6 39603236 39725463

Why did line 4 not appear in your desired output?
This User Gave Thanks to RudiC For This Post:
raj_k
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

(hard one) awk, copy matching 2 col & 10-12 rows, is it possible?

Hey guys, my first post here. I'm trying to use awk to copy all matching paragraphs from one file The file looks like this : Test Case Number 990990003099 Card Type CCCC Transaction Type Sale Entry Mode Keyed Account Number 4099562299219923 Transaction Amount 78.88 Description lorem... (2 Replies)
Discussion started by: awkawed
2 Replies

2. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

3. Shell Programming and Scripting

Merge matching rows

Hello, I need this output. thank you very much. input: Code: ***table***wood ***snack***top ***table***garfield ***big***zen ***table***cars output: Code: ***table***wood2345garfield2345cars ***snack***top ***big***zen (7 Replies)
Discussion started by: tara123
7 Replies

4. Shell Programming and Scripting

Merging rows after matching a pattern

Hi All, I have the below file where I want the lines to merged based on a pattern. AFTER CMMILAOJ CMMILAAJ AFTER CMDROPEJ CMMIMVIJ CMMIRNTJ CMMIRNRJ CMMIRNWJ CMMIRNAJ CMMIRNDJ AFTER CMMIRNTJ CMMIRNRJ CMMIRNWJ (4 Replies)
Discussion started by: varun22486
4 Replies

5. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

6. Shell Programming and Scripting

Deleting specific rows in large files having rows greater than 100000

Hi Guys, I need help in modifying a large text file containing more than 1-2 lakh rows of data using unix commands. I am quite new to the unix language the text file contains data in a pipe delimited format sdfsdfs sdfsdfsd START_ROW sdfsd|sdfsdfsd|sdfsdfasdf|sdfsadf|sdfasdf... (9 Replies)
Discussion started by: manish2009
9 Replies

7. Shell Programming and Scripting

count numbers of matching rows and replace its value in another file

Hello all, can you help me in this problem, assume We have two txt file (file_1 and file_3) one is file_1 contains the data: a 0 b 1 c 3 a 7 b 4 c 5 b 8 d 6 . . . . and I need to count the lines with the matching data (a,b,..) and print in new file called file_2 such as the... (4 Replies)
Discussion started by: GoldenFalcon10
4 Replies

8. Shell Programming and Scripting

SED: delete matching row and 4 next rows?

Hi, Tried to look for solution, and found something similar but could not adapt the solution for my needs.. I'm trying to match a pattern (in this case "ProcessType")in a logfile, then delete that line and the 4 following lines. The logfile looks as follows: ProcessType: PROCESS_A... (5 Replies)
Discussion started by: Juha
5 Replies

9. Shell Programming and Scripting

Matching fields of rows and then operating

Hi All, I was seaching for script for solaris 5.10 environmet to get a output file from Input file like this. INPUT FILE---------------- 1000KE,MINE,74748 1000KE,YOUR,123998 200KE,MINE,886049 50KE,MINE,474176 50KE,YOUR,379998 100KE,YOUR,999994 50KE,MINE,9601 50KE,YOUR,990393... (3 Replies)
Discussion started by: ashis.tewari
3 Replies

10. UNIX for Dummies Questions & Answers

sum of all matching rows using awk

I have file "1","x1897"," 89.10" "1","x2232"," -12.12" "1","x1897"," 389.10" "1","x2232"," 212.12" "1","x1897"," 19.10" "1","x2232"," 2.12" i want to add all 3 rd column rows (they have spaces also)for x1 and sum of 3rd column rows for x2 separately. I am very... (8 Replies)
Discussion started by: i.scientist
8 Replies
Login or Register to Ask a Question