|
|
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 REDHAT
ascii
ASCII(7) Linux Programmer's Manual ASCII(7) NAME
ascii - the ASCII character set encoded in octal, decimal, and hexadecimal DESCRIPTION
ASCII is the American Standard Code for Information Interchange. It is a 7-bit code. Many 8-bit codes (such as ISO 8859-1, the Linux default character set) contain ASCII as their lower half. The international counterpart of ASCII is known as ISO 646. The following table contains the 128 ASCII characters. C program 'X' escapes are noted. Oct Dec Hex Char Oct Dec Hex Char ------------------------------------------------------------ 000 0 00 NUL ' ' 100 64 40 @ 001 1 01 SOH 101 65 41 A 002 2 02 STX 102 66 42 B 003 3 03 ETX 103 67 43 C 004 4 04 EOT 104 68 44 D 005 5 05 ENQ 105 69 45 E 006 6 06 ACK 106 70 46 F 007 7 07 BEL 'a' 107 71 47 G 010 8 08 BS '' 110 72 48 H 011 9 09 HT ' ' 111 73 49 I 012 10 0A LF ' ' 112 74 4A J 013 11 0B VT 'v' 113 75 4B K 014 12 0C FF 'f' 114 76 4C L 015 13 0D CR ' ' 115 77 4D M 016 14 0E SO 116 78 4E N 017 15 0F SI 117 79 4F O 020 16 10 DLE 120 80 50 P 021 17 11 DC1 121 81 51 Q 022 18 12 DC2 122 82 52 R 023 19 13 DC3 123 83 53 S 024 20 14 DC4 124 84 54 T 025 21 15 NAK 125 85 55 U 026 22 16 SYN 126 86 56 V 027 23 17 ETB 127 87 57 W 030 24 18 CAN 130 88 58 X 031 25 19 EM 131 89 59 Y 032 26 1A SUB 132 90 5A Z 033 27 1B ESC 133 91 5B [ 034 28 1C FS 134 92 5C '\' 035 29 1D GS 135 93 5D ] 036 30 1E RS 136 94 5E ^ 037 31 1F US 137 95 5F _ 040 32 20 SPACE 140 96 60 ` 041 33 21 ! 141 97 61 a 042 34 22 " 142 98 62 b 043 35 23 # 143 99 63 c 044 36 24 $ 144 100 64 d 045 37 25 % 145 101 65 e 046 38 26 & 146 102 66 f 047 39 27 ' 147 103 67 g 050 40 28 ( 150 104 68 h 051 41 29 ) 151 105 69 i 052 42 2A * 152 106 6A j 053 43 2B + 153 107 6B k 054 44 2C , 154 108 6C l 055 45 2D - 155 109 6D m 056 46 2E . 156 110 6E n 057 47 2F / 157 111 6F o 060 48 30 0 160 112 70 p 061 49 31 1 161 113 71 q 062 50 32 2 162 114 72 r 063 51 33 3 163 115 73 s 064 52 34 4 164 116 74 t 065 53 35 5 165 117 75 u 066 54 36 6 166 118 76 v 067 55 37 7 167 119 77 w 070 56 38 8 170 120 78 x 071 57 39 9 171 121 79 y 072 58 3A : 172 122 7A z 073 59 3B ; 173 123 7B { 074 60 3C < 174 124 7C | 075 61 3D = 175 125 7D } 076 62 3E > 176 126 7E ~ 077 63 3F ? 177 127 7F DEL HISTORY
An ascii manual page appeared in Version 7 AT&T UNIX. On older terminals, the underscore code is displayed as a left arrow, called backarrow, the caret is displayed as an up-arrow and the ver- tical bar has a hole in the middle. Uppercase and lowercase characters differ by just one bit and the ASCII character 2 differs from the double quote by just one bit, too. That made it much easier to encode characters mechanically or with a non-microcontroller-based electronic keyboard and that pairing was found on old teletypes. The ASCII standard was published by the United States of America Standards Institute (USASI) in 1968. SEE ALSO
iso_8859_1(7), iso_8859_15(7), iso_8859_7(7) Linux 1999-08-08 ASCII(7)