Merge columns on different files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge columns on different files
# 1  
Old 10-09-2013
Merge columns on different files

Hello, I have two files that have this format:

file 1
Code:
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
Code:
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 this:
Code:
86.82 0.00  86.82 43.61 86.82 0.22
86.84 0.00  86.84 43.61 86.84 0.22
86.86 0.00  86.86 43.61 86.86 0.22
86.88 0.00  86.88 43.61 86.88 0.22

Can someone please help?
# 2  
Old 10-09-2013
Assumption files are sorted and missing columns should get 0.00:

Code:
join -o 1.1,1.2,1.3,1.4,2.1,2.2 -e "0.00" file1 file2

# 3  
Old 10-10-2013
An awk approach may try

Code:
$ cat f2
86.82 0.22
86.84 0.22
86.86 0.22
86.88 0.22

Code:
$ cat f1
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

Code:
$ awk 'FNR==NR{_[$1]=$0;next}{printf($1 in _)?$0 FS _[$1] RS:""}' f2 f1

Resulting
Code:
86.82 0.00  86.82 43.61 86.82 0.22
86.84 0.00  86.84 43.61 86.84 0.22
86.86 0.00  86.86 43.61 86.86 0.22
86.88 0.00  86.88 43.61 86.88 0.22

# 4  
Old 10-10-2013
@Akshay Hegde
Using _ as a variable name is not a good idea. It makes beginners wonder what is going on. Its valid, but use some thing else, like this:
Code:
awk 'FNR==NR{arr[$1]=$0;next}{printf($1 in arr)?$0 FS arr[$1] RS:""}' f2 f1

When you see some like this example, its not easy to see what is going on Smilie
awk '_ && _--;/test/{_=3}' file
# 5  
Old 10-10-2013
Thank you Jotne.. what you said is true it will be difficult for beginners to understand
# 6  
Old 10-10-2013
Code:
sort file1 file2 | paste -d"\t\n" -s 
86.82 0.00  86.82 43.61    86.82 0.22
86.84 0.00  86.84 43.61    86.84 0.22
86.86 0.00  86.86 43.61    86.86 0.22
86.88 0.00  86.88 43.61    86.88 0.22


Last edited by RudiC; 10-10-2013 at 08:59 AM.. Reason: forgot code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Merge files based on columns

011111123444 1234 1 20000 011111123444 1235 1 30000 011111123446 1234 3 40000 011111123447 1234 4 50000 011111123448 1234 3 50000 File2: 011111123444,Rsttponrfgtrgtrkrfrgtrgrer 011111123446,Rsttponrfgtrgtr 011111123447,Rsttponrfgtrguii 011111123448,Rsttponrfgtrgtjiiu I have 2 files... (4 Replies)
Discussion started by: vinus
4 Replies

3. Shell Programming and Scripting

Merge columns from multiple files

Hello and Good day I have a lot of files with same number of rows and columns.$2 and $3 are the same in all files . I need to merge $2,$3,$6 from first file and $6 from another files. File1: $1 $2 $3 $4 $5 $6... (8 Replies)
Discussion started by: ali.seifaddini
8 Replies

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

5. UNIX for Dummies Questions & Answers

Merge columns from multiple files

Hi all, I've searched the web for a long time trying to figure out how to merge columns from multiple files. I know paste will append columns like so: paste file1 file2 file3 file4 file5 ... But this becomes inconvenient when you want to append a large number of files into a single file. ... (2 Replies)
Discussion started by: torchij
2 Replies

6. Shell Programming and Scripting

Merge two files matching columns

Hi! I need to merge two files when col1 (x:x:x) matching and adds second column from file1.txt. # cat 1.txt aaa;a12 bbb;b13 ccc;c33 ddd;d55 eee;e11 # cat 2.txt bbb;b55;34444;d55 aaa;a15;35666;a44 I try with this awk and I get succesfully first column from 1.txt: # awk -F";"... (2 Replies)
Discussion started by: fhluque
2 Replies

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

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

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. UNIX for Dummies Questions & Answers

compare columns from 2 files and merge

Dear all, Being new to Unix i have a problem. I have 2 files: File 1: 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: 118,1,BFGL-NGS-109695,3610326,0,18,1,0.556,0.389,0.056,0.25,0.8183... (2 Replies)
Discussion started by: samwilkinson
2 Replies
Login or Register to Ask a Question