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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparing same column from two files, printing whole row with matching values
# 1  
Old 11-11-2016
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 (2 columns):
Code:
 23 DEST_LOCN:35191
 29 DEST_LOCN:36709
138 DEST_LOCN:38269
323 DEST_LOCN:38591
 25 DEST_LOCN:38595
289 DEST_LOCN:39349
 31 DEST_LOCN:42060
453 DEST_LOCN:43664


File2 (2 columns):
Code:
191 DEST_LOCN:38269
388 DEST_LOCN:38591
 25 DEST_LOCN:38598
167 DEST_LOCN:39349
545 DEST_LOCN:43664



If a value in column2 is the same in both files, i'd like to print results like column1.file1, column1.file2, column2.file(1 or 2). Example:
Code:
138 191 DEST_LOCN:38269
323 388 DEST_LOCN:38591
289 167 DEST_LOCN:39349
453 545 DEST_LOCN:43664


Can you help me or point to correct thread?

Thank you!
M


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 11-11-2016 at 09:27 AM.. Reason: Added CODE tags.
# 2  
Old 11-11-2016
Code:
join -j 2 -o 1.1,2.1,1.2 file1 file2

# 3  
Old 11-11-2016
Thank you!

Is it possible to be that simple Smilie

---------- Post updated at 02:15 PM ---------- Previous update was at 02:01 PM ----------

I'd like to add one more question of I can.

After the join I also added a math function:
Code:
awk '{print ($1/$2)}'


I'd like to limit the output to two decimals. Trying somehow with printf but not successful yet.

Thanks in advance!


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 11-11-2016 at 09:29 AM.. Reason: Added CODE tags.
# 4  
Old 11-11-2016
It's the correct approach. How would your attempt "somehow with printf" look like? Which format string did you specify?



And, please format your posts correctly with code tags for code and data.
# 5  
Old 11-11-2016
faked it a little, like that

Code:
awk '{print substr(($1/$2),0,4)'

but that's not the correct roundup
# 6  
Old 11-11-2016
Try:
Code:
awk '{print $1/$2}' OFMT="%.2f" file

or
Code:
awk '{printf "%.2f\n",$1/$2}' file

--
And combined with the join suggested earlier (assumes sorted files)
Code:
join -12 -22 -o 1.1,2.1,0 file1 file2 | awk '{print $1/$2, $3}' OFMT="%.2f"

or
Code:
join -12 -22 -o 1.1,2.1,0 file1 file2 | awk '{printf "%.2f %s\n", $1/$2, $3}'

or combined in one awk (sorted order not required)
Code:
awk 'NR==FNR{A[$2]=$1; next} $2 in A{print A[$2]/$1, $2}' OFMT="%.2f"  file1 file2


Last edited by Scrutinizer; 11-11-2016 at 10:44 AM..
# 7  
Old 11-11-2016
Do you know the difference between printf (your comment in post#3) and print?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Concatenate column values when header is Matching from multiple files

there can be n number of columns but the number of columns and header name will remain same in all 3 files. Files are tab Delimited. a.txt Name 9/1 9/2 X 1 7 y 2 8 z 3 9 a 4 10 b 5 11 c 6 12 b.xt Name 9/1 9/2 X 13 19 y 14 20 z 15 21 a 16 22 b 17 23 c 18 24 c.txt Name 9/1 9/2... (14 Replies)
Discussion started by: Nina2910
14 Replies

2. Shell Programming and Scripting

Awk: Comparing arguments with in line values of file and printing the result

I need to develop a script where I will take two date arguments as parameter date1 and date2 which will in format YYYYMM. Below is the input file say sample.txt. sample.txt will have certain blocks starting with P1. Each block will have a value 118,1:TIMESTAMP. I need to compare the... (7 Replies)
Discussion started by: garvit184
7 Replies

3. Shell Programming and Scripting

Comparing two files on row by row and send the report

Comparing two files on row by row File1 ecount~100 dcount~200 ccount~300 zxcscount~5000 and so on. File2 ecount~100 dcount~203 ccount~300 zxcscount~5000 and so on. If i use diff command (1 Reply)
Discussion started by: onesuri
1 Replies

4. Shell Programming and Scripting

Print every 5 4th column values as separate row with different first column

Hi, I have the following file, chr1 100 200 20 chr1 201 300 22 chr1 220 345 23 chr1 230 456 33.5 chr1 243 567 90 chr1 345 600 20 chr1 430 619 21.78 chr1 870 910 112.3 chr1 914 920 12 chr1 930 999 13 My output would be peak1 20 22 23 33.5 90 peak2 20 21.78 112.3 12 13 Here the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

5. UNIX for Dummies Questions & Answers

Comparing two test files and printing out the values that do not match

Hi, I have two text files with matching first columns. Some of the values in the second column do not match. I want to write a script to print out the rows (only the first column) where the values in the second column do not match. Example: Input 1 A 1 B 2 C 3 D 4 Input 2 A 2 B 2... (6 Replies)
Discussion started by: evelibertine
6 Replies

6. 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

7. Shell Programming and Scripting

Printing entire field, if at least one row is matching by AWK

Dear all, I have been trying to print an entire field, if the first line of the field is matching. For example, my input looks something like this. aaa ddd zzz 123 987 126 24 0.650 985 354 9864 0.32 0.333 4324 000 I am looking for a pattern,... (5 Replies)
Discussion started by: Chulamakuri
5 Replies

8. 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

9. 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

10. Shell Programming and Scripting

regarding about printing row to column

Hello, I got data like that, =111 A= alpha B= 1 C= qq D= 45 F= ss G= 334 =1234 A= B= 2w C= D= 443 F= G= =3434 A= B= e3e (5 Replies)
Discussion started by: davidkhan
5 Replies
Login or Register to Ask a Question