Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Merge two files with two columns being similar

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Merge two files with two columns being similar
# 1  
Old 01-27-2011
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.

Code:
1 Dave
2 Mark
3 Paul

1 Apple
2 Orange
3 Grapes

to get it like this in the 3rd file:
Code:
1 Dave 1 Apple
2 Mark 2 Orange
3 Paul 3 Grapes

thanks.
# 2  
Old 01-27-2011
Code:
awk 'NR==FNR{a[$1]=$2; next}a[$1]{print a[$1], $0}' file1 file2

# 3  
Old 01-27-2011
But that removes the first column. I want all 4 columns. thanks.
# 4  
Old 01-27-2011
Quote:
Originally Posted by Atrisa
But that removes the first column. I want all 4 columns. thanks.
I'm sorry, this should work:
Code:
awk 'NR==FNR{a[$1]=$2; next}a[$1]{print $1, a[$1], $0}' file1 file2

Last edited by Franklin52; 01-27-2011 at 06:41 AM.. Reason: Adding end bracket
This User Gave Thanks to Franklin52 For This Post:
Atrisa
# 5  
Old 01-27-2011
Thanks a lot Franklin52. Ah, the end bracket '}' for print was missing, so you might want to edit itSmilie
# 6  
Old 01-27-2011
Quote:
Originally Posted by Franklin52
Code:
awk 'NR==FNR{a[$1]=$2; next}a[$1]{print $1, a[$1], $0}' file1 file2

Hiya, could you tell me what the "}a[$1]{" does in the middle ?

Thanks a lot...
# 7  
Old 01-27-2011
Quote:
Originally Posted by citaylor
Hiya, could you tell me what the "}a[$1]{" does in the middle ?

Thanks a lot...
The awk statement has the form:

condition {action}

You can read the command as:
Code:
awk '
NR==FNR{a[$1]=$2; next}		# if NR==FNR execute the commands between the brackets {}
a[$1]{print $1, a[$1], $0}	# if a[$1] is not empty execute the commands between the brackets {}
' file1 file2

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge columns from two files using awk

I have two csv files : say a.csv, b.csv a.csv looks like this : property1,property2,100 property3,property4,200 In a.csv, the combination of column1 and column2 will be unique b.csv looks like this property1,property2, 300, t1 property1,property2, 400,t2 property3, property4,800,t1... (2 Replies)
Discussion started by: Lakshmikumari
2 Replies

2. Shell Programming and Scripting

Merge specific columns of two files

Hello, I have two tab delimited text files. Both files have the same number of rows but not necessarily the same number of columns. The column headers look like, File 1: f0order CVorder Name f0 RI_9 E99 E199 E299 E399 E499 E599 E699 E799 E899 E999 File 2:... (9 Replies)
Discussion started by: LMHmedchem
9 Replies

3. Shell Programming and Scripting

Merge 2 files with one reference columns

Hi All Source1 servername1,patchid1 servername1,patchid2 servername1,patchid3 servername2,patchid1 servername2,patchid2 servername3,patchid4 servername3,patchid5 Source2 servername1,appname1 servername1,appname2 servername1,appname3 servername2,appname1 servername2,appname2... (13 Replies)
Discussion started by: mv_mv
13 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

Merging two columns from two files with similar names into a loop

I have two files like this: fileA.net A B C fileA.dat 1 2 3 and I want the output output_expected A 1 B 2 C 3 I know that the easier way is to do a paste fileA.net fileA.dat, but the problem is that I have 10,000 couple of files (fileB.net with fileB.dat; fileC.net with... (3 Replies)
Discussion started by: valente
3 Replies

6. Shell Programming and Scripting

Merge two files with similar column entries

Hi , I have few files which contains user name and data transfer rate in MBs and this data is collected for year and for each month report is saved in 12 different files I have to merge all the files to prepare the final report Files are as below Similarly I have 10 more files ... (5 Replies)
Discussion started by: pratapsingh
5 Replies

7. Shell Programming and Scripting

Computing the ratio of similar columns in the two files using awk script

Thanks Bartus11 for your help in the following code to compare the two files "t1" and "t2". awk 'NR==FNR{a=1;next}$2 in a{print $2}' t1 t2 First can anyone explain that what is the purpose of assigning a =1? Second, the current script is printing out the matched columns between the... (4 Replies)
Discussion started by: coder83
4 Replies

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

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

10. Shell Programming and Scripting

Comparing similar columns in two different files

Hi, I have two text files.The first and the 2nd file have data in the same format For e.g. The first file has table_name1 column1 sum(column1) max(column1) min(column1) table_name1 column2 sum(column2) max(column2) min(column2) table_name1 coulmn3 sum(column3) max(column3) min(column3) ... (13 Replies)
Discussion started by: ragavhere
13 Replies
Login or Register to Ask a Question