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


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Comparing the 2nd column in two different files and printing corresponding 9th columns in new file
# 1  
Old 06-03-2011
Data 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 (LAST COLUMN) of a second file..and make a new file containing only the second column of first file and 9th column of a first file and second file.

Ex. file 1
1 ab1 987 0.98 234 876 456 837 0.009
1 ab2 866 0.87 654 186 578 975 0.0007

file 2
1 ab1 957 0.68 244 576 456 877 0.019
1 ab2 866 0.97 654 586 558 975 0.0037

I need a file like,
1 ab1 0.009 0.019
1 ab2 0.0007 0.0037

I appreciate your help with the UNIX script and explanation to learn!

Thanks,
# 2  
Old 06-03-2011
Code:
awk 'NR==FNR{ A[$2]=$NF} { A[$2]=A[$2]" "$NF } END{ for (i in A) {print i A[i] }' file1 file2

# 3  
Old 06-03-2011
hi Kumaran,

Thanks for the quick reply. However, I have a problem in executing this command. Because, I am getting this error message stating awk:syntax error unexpected new line or end of string.

Can it be done using other scripts?
Thanks in advance!
# 4  
Old 06-03-2011
are you gonna do the same thing on all 15 (2nd-16th) files or only file1 and file2?
in file1, are there lines with same value in the 2nd column? how to handle it if yes?
# 5  
Old 06-03-2011
Hi sk1418,

Yes I want to combine the 9th columns of all 16 files in one single file. Because the column 2 is common in all the files. So, I want to compare the values in the 2nd column between two files and extract the corresponding value in the 9th column to a new file.

Is it OK?
# 6  
Old 06-03-2011
Assume that you have 16 files which name in pattern f1-f16, the following command should do the job:

Code:
cat f{2..16}|awk 'NR==FNR{a[$2]=$NF} NR>FNR{if($2 in a)print $2" "a[$2]" "$NF}' f1 -

note that the 1st column in result is the 2nd column in f1 (ab1, ab2...), not (1). since I don't know how does the (1) come to the result. you didn't explain the rule of it.
# 7  
Old 06-03-2011
Columns will be in the order of how the file are given.

Code:
 
perl -lane '$hash{$F[1]}.=" $F[8]";END{print "$_ $hash{$_}" for keys %hash}' file1 file2

And the output is

Code:
ab2  0.0007 0.0037
ab1  0.009 0.019

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

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparing two columns in two files and printing a third based on a match

Hello all, First post here. I did not notice a previous post to help me down the right path. I am looking to compare a column in a CSV file against another file (which is not a column match one for one) but more or less when a match is made, I would like to append a third column that contains a... (17 Replies)
Discussion started by: dis0wned
17 Replies

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

3. Shell Programming and Scripting

Difference between 2 files, one with 1 column and 2nd file with multiple columns

Hi, I need to find the difference between 2 files in unix and write the result in the new file File1: A B File2: X 123 hajkd Y 345 adjfka A 123 djafjhd B 678 dsndjks Output file: X 123 hajkd Y 345 adjfka Thanks. (6 Replies)
Discussion started by: nani1984
6 Replies

4. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

5. UNIX for Dummies Questions & Answers

Taking the average of two columns and printing it on a new column

Hi, I have a space delimited text file that looks like the following: Aa 100 200 Bb 300 100 Cc X 500 Dd 600 X Basically, I want to take the average of columns 2 and 3 and print it in column 4. However if there is an X in either column 2 or 3, I want to print the non-X value. Therefore... (11 Replies)
Discussion started by: evelibertine
11 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

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

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

Comparing 2 files with awk and updating 2nd file

file1: (unique files) 1 /pub/atomicbk/catalog/catalog.gif 693 2 /pub/atomicbk/catalog/home.gif 813 3 /pub/atomicbk/catalog/logo2.gif 12871 4 /pub/atomicbk/catalog/sleazbk.html 18338 file2: (duplicate filenames allowed) 28/Aug/1995:00:00:38 1 /pub/atomicbk/catalog/home.gif 813... (2 Replies)
Discussion started by: jontjioe
2 Replies

10. Shell Programming and Scripting

Comparing Columns and printing the difference from a particular file

Gurus, I have one file which is having multiple columns and also this file is not always contain the exact columns; sometimes it contains 5 columns or 12 columns. Now, I need to find the difference from that particular file. Here is the sample file: param1 | 10 | 20 | 30 | param2 | 10 |... (6 Replies)
Discussion started by: buzzusa
6 Replies
Login or Register to Ask a Question