Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

compare columns from 2 files and merge

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers compare columns from 2 files and merge
# 1  
Old 08-25-2009
compare columns from 2 files and merge
Dear all,

Being new to Unix i have a problem. I have 2 files:
File 1:

Code:
118,1,0,2,3,0,5,0.3,0,0.3,0.6,1
118,2,1,2,2,0,5,0.4,0,0.4,0.4,1
118,4,2,0,3,0,5,0.7,0,0.3,0.6,1
118,6,4,1,0,0,5,0.8,0,0.2,0,1

File 2:
Code:
118,1,BFGL-NGS-109695,3610326,0,18,1,0.556,0.389,0.056,0.25,0.8183
118,2,BFGL-NGS-109696,6200739,0,18,1,0.222,0.5,0.278,0.472,0.824,0
118,3,BFGL-NGS-109701,4560156,0,18,1,0,0.167,0.833,0.083,0.8567,0.
118,4,BFGL-NGS-109702,1990730,0,18,1,0,0,1,0,0.5299,0.3408,0.3408,
118,5,BFGL-NGS-109705,5050020,0,18,1,0.056,0.667,0.278,0.389,0.777
118,6,BFGL-NGS-109707,2000561,0,18,1,0.056,0.167,0.778,0.139,0.796
118,7,BFGL-NGS-109711,3140750,0,18,1,0.333,0.444,0.222,0.444,0.865

File 2 is longer than File 1. I wish to compare the contents of the two files by column 2. Where there is a match I wish to extract the line from File 2 and place it after corresponding line from File 1 thus creating a new file.

e.g.
Code:
118,1,0,2,3,0,5,0.3,0,0.3,0.6,1 118,1,BFGL-NGS-109695,3610326,0,18,1...

I have tried using awk for this
Code:
awk -F, 'BEGIN {while ((getline < "File2.txt") > 0) a[$1] = $2}

but i get lost afterwards on how to merge the corresponding extracted lines together. Any help would be much appreciated

Samantha
Last edited by vgersh99; 08-25-2009 at 01:58 PM.. Reason: code tags, PLEASE!
# 2  
Old 08-25-2009
Quote:
Originally Posted by samwilkinson
...
File 2 is longer than File 1. I wish to compare the contents of the two files by column 2. Where there is a match I wish to extract the line from File 2 and place it after corresponding line from File 1 thus creating a new file.

e.g.
118,1,0,2,3,0,5,0.3,0,0.3,0.6,1 118,1,BFGL-NGS-109695,3610326,0,18,1...

Code:
awk -F, 'BEGIN { while (( getline < "file_2" )>0) a[$2]=$0 } 
         a[$2] { print $0 , a[$2] }'   file_1  >  newfile


or in a more traditional ( NR==FNR ) way,

Code:
awk -F, 'NR==FNR { a[$2]=$0; next  }
           a[$2] { print $0, a[$2] }
        ' file_2 file_1 > newfile

Note that the file order is important, also use getline with care.
# 3  
Old 08-25-2009
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare and merge two big CSV files

Hi all, i need help. I have two csv files with a huge amount of data. I need the first column of the first file, to be compared with the data of the second, to have at the end a file with the data not present in the second file. Example File1: (only one column) profile_id 57036226... (11 Replies)
Discussion started by: SirMannu
11 Replies

2. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

3. Shell Programming and Scripting

Compare two files and merge into third

Hello: Newbie with Awk. Trying to compare two files and merge data based on CID. Please see the input file format and desired output. Any help is appreciated. TIA Input File1 CID1 --- TYP1 --- DCN1 --- INDATE1 --- IN-DATA1 CID2 --- TYP2 --- DCN2 --- INDATE2 --- IN-DATA2 CID3 ---... (6 Replies)
Discussion started by: wincrazy
6 Replies

4. Shell Programming and Scripting

Merge columns on different files

Hello, I have two files that have this format: file 1 86.82 0.00 86.82 43.61 86.84 0.00 86.84 43.61 86.86 0.00 86.86 43.61 86.88 0.00 86.88 43.61 file 2 86.82 0.22 86.84 0.22 86.86 0.22 86.88 0.22 I would like to merge these two files such that the final file looks like... (5 Replies)
Discussion started by: kayak
5 Replies

5. Shell Programming and Scripting

Compare and Merge files

Hi All, I have two different files as shown below separated by a "|". I need to compare the first column from both the files and if they match merge both the columns. File 1 "S00172012"|"CHRONIC RENAL FAILURE"|""|"I" "S00159962"|"SUBENDO INFRC-INIT EPISD"|""|"I" "S00255303"|"BENIGN... (6 Replies)
Discussion started by: nua7
6 Replies

6. Shell Programming and Scripting

Merge columns of different files

Hi, I have tab limited file 1 and tab limited file 2 The output should contain common first column vales and corresponding 2nd column values; AND also unique first column value with corresponding 2nd column value of the file that contains it and 0 for the second file. the output should... (10 Replies)
Discussion started by: polsum
10 Replies

7. UNIX for Dummies Questions & Answers

Merge two files with two columns being similar

Hi everyone. How can I merge two files, where each file has 2 columns and the first columns in both files are similar? I want all in a file of 4 columns; join command removes the duplicate columns. 1 Dave 2 Mark 3 Paul 1 Apple 2 Orange 3 Grapes to get it like this in the 3rd file:... (9 Replies)
Discussion started by: Atrisa
9 Replies

8. Shell Programming and Scripting

merge the two files which has contain columns

Hi may i ask how to accomplish this task: I have 2 files which has multiple columns first file 1 a 2 b 3 c 4 d second file 14 a 9 .... 13 b 10.... 12 c 11... 11 d 12... I want to merge the second file to first file that will looks like this ... (2 Replies)
Discussion started by: jao_madn
2 Replies

9. Shell Programming and Scripting

compare the column from 3 files and merge that line

I have 3 file, each of has got 80000 records. file1.txt ----------------------- ABC001;active;modify;accept; ABC002;notactive;modify;accept; ABC003;notactive;no-modify;accept; ABC004;active;modify;accept; ABC005;active;no-modify;accept; file2.txt ---------------------------... (8 Replies)
Discussion started by: ganesh_mak
8 Replies

10. Shell Programming and Scripting

Compare two files and merge columns in a third

Hi, I'm working with snmp, with a little script I'm able to obtain from a switch a list with a couple of values with this format Port Mac 1 00:0A:0B:0C:0D:0E .... (hundred of entries) Now with a simple arp on a router I am able to obtain another list 00:0A:0B:0C:0D:0E... (20 Replies)
Discussion started by: CM64
20 Replies
Login or Register to Ask a Question