|
|
Sponsored Content
Top Forums
Shell Programming and Scripting
match two key columns in two files and print output (awk)
Post 302705619 by pelhabuan on Tuesday 25th of September 2012 09:19:08 AM
09-25-2012
match two key columns in two files and print output (awk)
I have two files... file1 and file2.
Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2
Many thanks if you know how to do this....
file1
31-101 106 0 92
31-101 106 29 92
31-101 106 85 12
31-101 106 66 11
31-101 106 71 33
31-101 146 0 14
31-101 146 45 14
31-101 146 94 14
31-101 146 86 15
31-101 146 93 15
31-101 146 72 15
31-101 186 10 14
31-101 186 58 14
31-101 186 48 14
31-101 186 39 15
31-101 186 54 15
31-101 186 68 15
31-102 106 0 144
31-102 106 128 194
31-102 106 167 198
31-102 106 244 106
31-102 106 240 132
31-102 106 255 139
31-102 106 294 262
file2
31-101 106 396333 9256246
31-101 146 396764 9256784
31-101 186 396676 9256357
31-102 106 397346 9256457
Desired output
All of file one columns 3 and 4 of file two
31-101 106 0 92 396333 9256246
31-101 106 29 92 396333 9256246
31-101 106 85 12 396333 9256246
31-101 106 66 11 396333 9256246
31-101 106 71 33 396333 9256246
31-101 146 0 14 396764 9256784
31-101 146 45 14 396764 9256784
31-101 146 94 14 396764 9256784
31-101 146 86 15 396764 9256784
31-101 146 93 15 396764 9256784
31-101 146 72 15 396764 9256784
31-101 186 10 14 396676 9256357
31-101 186 58 14 396676 9256357
31-101 186 48 14 396676 9256357
31-101 186 39 15 396676 9256357
31-101 186 54 15 396676 9256357
31-101 186 68 15 396676 9256357
31-102 106 0 144 397346 9256457
31-102 106 128 194 397346 9256457
31-102 106 167 198 397346 9256457
31-102 106 244 106 397346 9256457
31-102 106 240 132 397346 9256457
31-102 106 255 139 397346 9256457
31-102 106 294 262 397346 9256457
Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2
Many thanks if you know how to do this.... file1
31-101 106 0 92
31-101 106 29 92
31-101 106 85 12
31-101 106 66 11
31-101 106 71 33
31-101 146 0 14
31-101 146 45 14
31-101 146 94 14
31-101 146 86 15
31-101 146 93 15
31-101 146 72 15
31-101 186 10 14
31-101 186 58 14
31-101 186 48 14
31-101 186 39 15
31-101 186 54 15
31-101 186 68 15
31-102 106 0 144
31-102 106 128 194
31-102 106 167 198
31-102 106 244 106
31-102 106 240 132
31-102 106 255 139
31-102 106 294 262
file2
31-101 106 396333 9256246
31-101 146 396764 9256784
31-101 186 396676 9256357
31-102 106 397346 9256457
Desired output
All of file one columns 3 and 4 of file two
31-101 106 0 92 396333 9256246
31-101 106 29 92 396333 9256246
31-101 106 85 12 396333 9256246
31-101 106 66 11 396333 9256246
31-101 106 71 33 396333 9256246
31-101 146 0 14 396764 9256784
31-101 146 45 14 396764 9256784
31-101 146 94 14 396764 9256784
31-101 146 86 15 396764 9256784
31-101 146 93 15 396764 9256784
31-101 146 72 15 396764 9256784
31-101 186 10 14 396676 9256357
31-101 186 58 14 396676 9256357
31-101 186 48 14 396676 9256357
31-101 186 39 15 396676 9256357
31-101 186 54 15 396676 9256357
31-101 186 68 15 396676 9256357
31-102 106 0 144 397346 9256457
31-102 106 128 194 397346 9256457
31-102 106 167 198 397346 9256457
31-102 106 244 106 397346 9256457
31-102 106 240 132 397346 9256457
31-102 106 255 139 397346 9256457
31-102 106 294 262 397346 9256457
|
|
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi guys,
I need some help to come out with a solution . I have seven such files but I am showing only three for convenience.
filea
a5 20
a8 16
fileb
a3 42
a7 14
filec
a5 23
a3 07
The output file shoud contain the data in table form showing first field of... (7 Replies)
Discussion started by: smriti_shridhar
7 Replies
2. Shell Programming and Scripting
Dear Shell experts,
I have 2 files with structure:
File 1: ID and count
head test_GI_count1.txt
1000094 2
10039307 1
10039641 1
10047177 11
10047359 1
1008555 2
10120302 1
10120672 13
10121776 1
10121865 32
And 2nd file:
head Protein_gi_GeneID_symbol.txt
protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies
3. Shell Programming and Scripting
Hello,
I have two files which are of the following format
File 1 which has two columns
Protein_ID Substitution
NP_997239 T53R
NP_060668 V267M
NP_058515 P856A
NP_001206 T55M
NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies
4. Shell Programming and Scripting
Hi,
I want to print last column of 3 input files with awk.
cat file1.txt file2.txt file3.txt
file1.txt
1 ad 200
2 ss 300
file2.txt
1 mm 444
2 ee 555
file3.txt
1 kk 999
2 jj 555
My o/p should be :-
1 200 444 999
2 300 555 555 (3 Replies)
Discussion started by: satishmallidi
3 Replies
5. Shell Programming and Scripting
Hi,
I have multiple files that each contain one column of strings:
File1:
123abc
456def
789ghi
File2:
123abc
456def
891jkl
File3:
234mno
123abc
456def
In total I have 25 of these type of file. (5 Replies)
Discussion started by: owwow14
5 Replies
6. Shell Programming and Scripting
I would like to join two files when two columns in each file matches with each other and then produce an output when taking multiple columns.
Like I have file A
1234,ABCD,23,JOHN,NJ,USA
2345,ABCD,24,SAM,NY,USA
5678,GHIJ,24,TOM,NY,USA
5678,WXYZ,27,MAT,NJ,USA
and file B
... (2 Replies)
Discussion started by: mady135
2 Replies
7. UNIX for Dummies Questions & Answers
Hi Help,
I have two files namely a.txt and b.txt
a.txt looks like
a.txt
1 2 2
1 3 3
2 4 4
4 5 6
6 7 7
b.txt looks like,
b.txt
1 2 1
1 3 2
2 4 3
3 4 4
4 5 5 (2 Replies)
Discussion started by: Indra2011
2 Replies
8. Shell Programming and Scripting
Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
Discussion started by: cmccabe
6 Replies
9. UNIX for Beginners Questions & Answers
I have two files and desire to use the strings from $1 of file 1 (file1.txt) as search criteria to find matches in $2 of file 2 (file2.txt). If matches are found I want to output the entire line of file 2 (file2.txt) followed by fields $2-$11 of file 1 (file1.txt). I can find the matches, I cannot... (7 Replies)
Discussion started by: jvoot
7 Replies
10. UNIX for Beginners Questions & Answers
I've written an awk script to compare two fields in two different files and then print portions of each file on the same line when matched. It works reasonably well, but every now and again, I notice some errors and cannot seem to figure out what the issue may be and am turning to you for help.
... (2 Replies)
Discussion started by: jvoot
2 Replies
LEARN ABOUT OSF1
comm
comm(1) General Commands Manual comm(1) NAME
comm - Compares two sorted files. SYNOPSIS
comm [-123] file1 file2 STANDARDS
Interfaces documented on this reference page conform to industry standards as follows: command: XCU5.0 Refer to the standards(5) reference page for more information about industry standards and associated tags. OPTIONS
Suppresses output of the first column (lines in file1 only). Suppresses output of the second column (lines in file2 only). Suppresses output of the third column (lines common to file1 and file2). The command comm -123 produces no output. OPERANDS
A pathname of the first file to be compared. If file1 is a hyphen (-), the standard input is used. A pathname of the second file to be compared. If file2 is a hyphen (-), the standard input is used. If both file1 and file2 refer to standard input or to the same FIFO special, block special or character special file, the results are unde- fined. DESCRIPTION
The comm command reads file1 and file2 and writes three columns to standard output, showing which lines are common to the files and which are unique to each. The leftmost column of standard output includes lines that are in file1 only. The middle column includes lines that are in file2 only. The rightmost column includes lines that are in both file1 and file2. If you specify a hyphen (-) in place of one of the file names, comm reads standard input. Generally, file1 and file2 should be sorted according to the collating sequence specified by the LC_COLLATE environment variable. (See sort(1).) If the input files are not sorted properly, the output of comm might not be useful. EXIT STATUS
Successful completion. Error occurred. EXAMPLES
In the following examples, file1 contains the following sorted list of North American cities: Anaheim Baltimore Boston Chicago Cleveland Dallas Detroit Kansas City Milwaukee Minneapolis New York Oakland Seattle Toronto The second file, file2, contains this sorted list: Atlanta Chicago Cincinnati Houston Los Angeles Montreal New York Philadelphia Pittsburgh San Diego San Francisco St. Louis To display the lines unique to each file and common to the two files, enter: comm file1 file2 This command results in the following output: Anaheim Atlanta Baltimore Boston Chicago Cincinnati Cleveland Dal- las Detroit Houston Kansas City Los Angeles Milwaukee Minneapolis Montreal New York Oakland Philadel- phia Pittsburgh San Diego San Francisco Seattle St. Louis Toronto The leftmost column contains lines in file1 only, the middle column contains lines in file2 only, and the rightmost column contains lines common to both files. To display any one or two of the three output columns, include the appropriate flags to suppress the columns you do not want. For example, the following command displays columns 1 and 2 only: comm -3 file1 file2 Anaheim Atlanta Baltimore Boston Cincinnati Cleveland Dallas Detroit Houston Kansas City Los Angeles Milwaukee Minneapolis Montreal Oakland Philadelphia Pittsburgh San Diego San Francisco Seattle St. Louis Toronto The following command displays output from only the second column: comm -13 file1 file2 Atlanta Cincinnati Houston Los Angeles Montreal Philadelphia Pittsburgh San Diego San Francisco St. Louis The following command displays output from only the third column: comm -12 file1 file2 Chicago New York SEE ALSO
Commands: cmp(1), diff(1), sdiff(1), sort(1), uniq(1) comm(1)