Merge columns of different files


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge columns of different files
# 1  
Merge columns of different files

Hi,

I have tab limited file 1

Quote:
a 455
b 443
c 6655
d 554
e 6
and tab limited file 2
Quote:
a 56
b 5
x 678
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 be:

Quote:
a 455 56
b 443 5
c 6655 0
d 554 0
e 6 0
x 0 678
thanks in advance.Smilie
# 2  
Code:
awk '{if($1 in a){sub("0" FS,"",a[$1]);a[$1]=a[$1] FS $2}else{a[$1]="0" FS $2}}END{for(i in a)print i,a[i]}' file1 file2

This User Gave Thanks to yinyuemi For This Post:
# 3  
@yinyuemi

thanks a lot. I actually have 4 files to merge...for simplicity sake I used 2 files here...when I applied this code to 4 files, I dont see all columns...How can I modify this code? thanks a lot again. Smilie
# 4  
if you have multiple files,please try this:
Code:
awk '{a[ARGIND" "$1]=$2;b[$1];t=ARGIND}END{for(i in b){printf i ;for(j=1;j<=t;j++)printf FS (length(a[j" "i])?a[j" "i]:"0");print ""}}' file1 file2 file1 file2
x 0 678 0 678
a 455 56 455 56
b 443 5 443 5
c 6655 0 6655 0
d 554 0 554 0
e 6 0 6 0

This User Gave Thanks to yinyuemi For This Post:
# 5  
Code:
cat f1.txt

a 455
b 443
c 6655
d 554
e 6

$ cat f2.txt

a 56
b 5
x 678

awk '$0 !~ /#/{arr[$1]=arr[$1] " " $2}END{for(i in arr)print i,arr[i]}' f1.txt f2.txt

x  678
a  455 56
b  443 5
c  6655
d  554
e  6

Moderator's Comments:
Mod Comment How to use code tags

Last edited by Franklin52; 12-19-2011 at 04:27 AM.. Reason: Please use code tags for code and data samples, thank you
This User Gave Thanks to uniqme For This Post:
# 6  
Quote:
Originally Posted by yinyuemi
if you have multiple files,please try this:
Code:
awk '{a[ARGIND" "$1]=$2;b[$1];t=ARGIND}END{for(i in b){printf i ;for(j=1;j<=t;j++)printf FS (length(a[j" "i])?a[j" "i]:"0");print ""}}' file1 file2 file1 file2
x 0 678 0 678
a 455 56 455 56
b 443 5 443 5
c 6655 0 6655 0
d 554 0 554 0
e 6 0 6 0

thank you very much. its working perfect. have a great weekendSmilie
# 7  
Quote:
Originally Posted by yinyuemi
if you have multiple files,please try this:
Code:
awk '{a[ARGIND" "$1]=$2;b[$1];t=ARGIND}END{for(i in b){printf i ;for(j=1;j<=t;j++)printf FS (length(a[j" "i])?a[j" "i]:"0");print ""}}' file1 file2 file1 file2
x 0 678 0 678
a 455 56 455 56
b 443 5 443 5
c 6655 0 6655 0
d 554 0 554 0
e 6 0 6 0

Hi, this code is working for the above mentioned example data, but its not working for the following tables. May I know why and how should I rectify the problem? thanks.

file 1

Quote:
Genes;cds_1110032A03Rik_plus_chr9_50572946_50572962 1
Genes;cds_1300001I01Rik_plus_chr11_74477563_74477580 1
Genes;cds_1700022I11Rik_minus_chr4_42983970_42983989 1
Genes;cds_1700024P16Rik_plus_chr4_104685961_104685986 2
file 2

Quote:
Genes;cds_1300001I01Rik_plus_chr11_74477563_74477580 1
Genes;cds_1700022I11Rik_minus_chr4_42983970_42983989 1
Genes;cds_1700024P16Rik_plus_chr4_104685961_104685986 2
Genes;cds_4931428F04Rik_plus_chr8_107805893_107805912 2
desired output is

Quote:
Genes;cds_1110032A03Rik_plus_chr9_50572946_50572962 1 0
Genes;cds_1300001I01Rik_plus_chr11_74477563_74477580 1 1
Genes;cds_1700022I11Rik_minus_chr4_42983970_42983989 1 1
Genes;cds_1700024P16Rik_plus_chr4_104685961_104685986 2 2
Genes;cds_4931428F04Rik_plus_chr8_107805893_107805912 0 2
But after using the code, I am getting only the first column, not the 2nd and third.

Quote:
Genes;cds_1110032A03Rik_plus_chr9_50572946_50572962
Genes;cds_1300001I01Rik_plus_chr11_74477563_74477580
Genes;cds_1700022I11Rik_minus_chr4_42983970_42983989
Genes;cds_1700024P16Rik_plus_chr4_104685961_104685986
Genes;cds_4931428F04Rik_plus_chr8_107805893_107805912
I am not sure what the problem is?

thanks in advanceSmilie
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #686
Difficulty: Medium
In reverse Polish notation, the operators follow their operands; for instance, to add 3 and 4, one would write 3 4 + rather than 3 + 4.
True or False?

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

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

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

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

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

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

Featured Tech Videos