Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

column merge same value

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting column merge same value
# 1  
Old 09-04-2012
column merge same value
Dear All,
I have the following file:

file1
Code:
"1"	189218400	189221399	3000	"0041 ENSMUSG00000000031"	"0041"	"+"	"ENSMUSG00000000031"	189039418	189175306	"downstream"	178982	43094	"NearestStart"
"1"	197067200	197068353	1154	"0057 ENSMUSG00000080633"	"0057"	"+"	"ENSMUSG00000080633"	197054472	197054592	"downstream"	12728	12608	"NearestStart"
"1"

file2
Code:
"ENSMUSG00000000031"	"H19"
"ENSMUSG00000000078"	"Klf6"
"ENSMUSG00000000088"	"Cox5a"

I would like to write a file that match the 8th column in the first filke with the 1st column in the second file, like this
Code:
"1"	189218400	189221399	3000	"0041 ENSMUSG00000039210"	"0041"	"+"	"ENSMUSG00000000031"	189039418	189175306	"downstream"	178982	43094	"NearestStart" "H19"

any suggestion?
thanks again,
apolo

---------- Post updated at 03:48 AM ---------- Previous update was at 03:43 AM ----------

I've tried
Code:
join -1 8 -2 1 file1 file2

but nothing happens...
Last edited by Franklin52; 09-04-2012 at 06:11 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 09-04-2012
Hi

Try 9, not 8:

Code:
$ join -1 9 -2 1 file1 file2

But, not a good solution though. This will not work if the 6th field contains more than 2 words within quotes.

Guru.
# 3  
Old 09-04-2012
Not very elegant:
Code:
awk -F'\t' 'FNR==NR{gsub(/"/,"",$1);a[$1]=$2;next}
{t=$8;gsub(/"/,"",t);n=split(t,b,/[ \t]*/);if(b[n] in a) $(NF+1)=a[b[n]]}1' file2 file1

# 4  
Old 09-04-2012
awk
Hi,

Try this one if your file1 is fixed field length,

Code:
awk 'FNR==NR{a[$1]=$2;next;}a[$(NF-6)]{print $0 FS a[$(NF-6)];}' file2 file1

Cheers,
Ranga Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge csvs with column headers

hello gurus, Somebody must have done this before, I couldn't find anything. Please redirect me if this was solved before, and if not please help. To the problem now, I have multiple csv files (about 1000) which I need to concatenate by column header. The final file should have a superset... (4 Replies)
Discussion started by: abh.kumar
4 Replies

2. Shell Programming and Scripting

Merge column file

Hi All, I have on file1 and file2 some, $cat file1 aaa bbb ccc ddd eee fff ggg hhh iii jjj with line blank, and (12 Replies)
Discussion started by: aav1307
12 Replies

3. Shell Programming and Scripting

Multiple file merge by column

Hello all, I am quite new in linux shell scripting and I have this issue. I ve got some files including measurements taken every 10minutes for a whole day. File name format is: 00.00, 00.10, 00.20,....23.50 File structure is: x | y | temperature x and y is the same in all files (same... (12 Replies)
Discussion started by: atzounis
12 Replies

4. Shell Programming and Scripting

Merge column headers and transpose

Hello Everyone! I am new on this forum and this is my first post. I wish to apologize for my, not canonical, English. I would like to solve this problem but I have no clue of how do it!I will be grateful if someone could help me! I have a table like this: gene TF1 TF2 TF3 TF4 gene1 1 2 3 4... (5 Replies)
Discussion started by: giuliangiuseppe
5 Replies

5. Shell Programming and Scripting

Merge with common column

hi i have two files and i wanted to join them using common column. try to do this using "join" command but that did not help. File 1: 123 9a.vcf hy92.vcf hy90.vcf Index Ref Alt Ref Alt Ref Alt 315 14 0 7 4 ... (6 Replies)
Discussion started by: empyrean
6 Replies

6. Shell Programming and Scripting

Count and merge using common column

I have the following records from multiple files. 415 A G 415 A G 415 A T 415 A . 415 A . 421 G A 421 G A,C 421 G A 421 G A 421 G A,C 421 G . 427 A C 427 A ... (3 Replies)
Discussion started by: empyrean
3 Replies

7. Shell Programming and Scripting

merge same pattern of same column in one line

Hello, I have some problem in the modified shell script. I would like to merge the same word in column 1 in one line. Example : A1 B1 2 A2 B1 4 A3 B1 7 A1 B2 1 A2 B2 10 A3 B2 8 Expected output : A1 B1 B2 2 1 A2 B1 B2 4 10 A3 ... (6 Replies)
Discussion started by: awil
6 Replies

8. UNIX for Advanced & Expert Users

merge two column multiple files into one

Hi I have multiple files each with two columns and I need to combine all those file into a tab delimited file. (multiple entry with same name separated by a comma) The content of the files are as follows: --- file1.txt: name var1 aaa xx aaa gg bbb yy ddd zz --- file2.txt ... (8 Replies)
Discussion started by: mary271
8 Replies

9. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ... (10 Replies)
Discussion started by: apjneeraj
10 Replies

10. Shell Programming and Scripting

Column Merge?

Hi all, I need to join two columns from two different files file1.txt and file2.txt and append in a new file. Example : $cat file1.txt ABCD.ksh:010141 ABCD.ksh:010511 ABCD.ksh:010815 ABCD.ksh:011114 ABCD.ksh:011415 ABCD.ksh:011720 ABCD.ksh:012022 ABCD.ksh:012830 ABCD.ksh:014432... (5 Replies)
Discussion started by: sabyasm
5 Replies
Login or Register to Ask a Question