Thank you for your reply! I have a look at your profile, and found, wow, I have introduced several of your paper in my lab journal club about Verrucomicrobia!
Hi all,
I have two files (a.txt; b.txt), columns were separate by tab,
a.txt:
id value1 value2 value3
g1 v1 v2 good
g2 v3 v4 better
b.txt:
id value1 value2
g1 v1 v2
g3 v3 v4
I want to use id as the keywords on use perl to search the same id in the two files,
if id in a==id in b, like g1
then append value3 of g1 in a.txt to the end of line g1 in b.txt
I want to use this method to deal with my GO-gene association file and gff files, and I am just beginning to use Perl. I know it is not a good manner to ask for scripts, so just me some hints on where to begin. Any suggestions are appreciated!
4 answers
if your files (sets) are not huge, you can handle this easily using R, without resorting to Perl:
# read data in
a = read.table("~/tmp/a.txt",header=T)
b = read.table("~/tmp/b.txt",header=T)
# merge datasets
merge(a,b,by="id")
id value1.x value2.x value3 value1.y value2.y
1 g1 v1 v2 good v1 v2
# extract results
c = merge(a,b,by="id")
res=c[,c(1,6,5,4)]
res
id value2.y value1.y value3
1 g1 v2 v1 good
# produce output
write.table(res, file="~/tmp/c.txt",quote=F)
R is more interactive - easier to re-run and take care about other things (comparison constraints, etc), plus it allows to run script in batch mode. But Perl is more streamlined and command-line oriented if you after that.
thank you, those are not exactly mine, but our team effort, I just performed some data processing
Merging the two files and then parsing the results for this task is not a good solution--in any language. Also, your comments about Perl are rather puzzling.
Let me step back a bit. I think that Intersection) is one of the standard (basic) operations from sets theory, and can be handled in a variety of ways in any language. Not handling this, is the "suicide" for any language. R is interactive, Perl is not. edit: biostars doesnt handle that wikipedia link, sorry.
Add only what you need to add--and nothing more. Adding the informational 'noise' by merging everything subsequently required removing that 'noise.' I still have no idea what you mean by "R is interactive, Perl is not."
By default, R starts interactively, while Perl is not really designed that way. I might be wrong, but merging (joining) two sets in a deterministic way requires some sort of indexing or sorting and is computationally expensive probably not better than O(nlogn), unless you are ok with some errors. In my solution I read everything in the memory, but merge operation took care about sorting and selection.
There are many ways to do this join task. To avoid small bugs from writing your own custom code, and the probable inefficiencies of a naive implementation, you could use the join utility on a Linux system. It should do what you want.
$ join -j 1 -t " " a.txt b.txt
id value1 value2 value3 value1 value2
g1 v1 v2 good v1 v2
good solution. you may want to add an awk call to shuffle columns in a way OP wants.
You can use dplyr in R as well.
a = read.table("~/tmp/a.txt",header=T)
b = read.table("~/tmp/b.txt",header=T)
library(dplyr)
inner_join(a, b)
This can be done in two steps: 1) split each line of a.txt and build a hash, where the id is a key and value3 is the key's associated value, and 2) split each line of b.txt and print the line plus value3 if the current id exists as a key in the hash:
use strict;
use warnings;
my ( @cols, %hash );
while (<>) {
$hash{ $cols[0] } = $cols[-1] if @cols = split;
last if eof;
}
while (<>) {
print +( join "\t", @cols, $hash{ $cols[0] } ), "\n"
if @cols = split and exists $hash{ $cols[0] };
}
Usage: perl script.pl a.txt b.txt [>outFile.txt]
The last, optional parameter directs output to a file.
The last if eof; is placed within the first while since both files would be completely read without it. The + after the print in the second while helps perl (the interpreter) disambiguate what to do.
This one-liner will produce the same results:
perl -ane '$h{$F[0]}=$F[-1];last if eof;END{while(<>){@F=split; s/\K$/\t$h{$F[0]}/ and print if$h{$F[0]}}}' a.txt b.txt [>outFile.txt]
Hope this helps!
I am astonished by all the discussions and suggestiones you brought. For a beginner, I really learned a lot from this. I have tried the merge way in R, use M=merge(a, b, all=TRUE), a, b is my gene list and DE list, they have a common id column. there about 3000 lines in a and b. after this merge, I think they will merge all things of a and b by the id column, and thing all indentical in a or b would be set NA, as the ?merge said. but the results showed that before 1095 lines all things were what I wanted, but after that all were messed! I do not know whether merge has limitation for lines or file size.
By the way, I think this link illustrate merge very nice http://dss.princeton.edu/training/Merge101R.pdf
after use Rstudio to import things without using command, I found it worked:
import dataset-->import from txt-->heading, yes; tab, period, none
command showed as following:
> HZ254_phzH_Deseq.out <- read.delim("~/HZ254_phzH_Deseq out.txt", quote="")
> View(HZ254_phzH_Deseq.out)
> HZ254_genedata_ensembl <- read.delim("~/HZ254_genedata_ensembl.txt", quote="")
> View(HZ254_genedata_ensembl)
> M=merge(HZ254_phzH_Deseq.out,HZ254_genedata_ensembl,all=TRUE)
> write.table(M,file='HZ254_DEfull1.xls',row.names=F,quote=F,sep='\t')
command causing mess I mentioned above as following:
a= read.table("HZ254_phzH_Deseq out.txt",header=T,sep='\t')
b= read.table("HZ254_genedata_ensembl.txt",header=T,sep='\t')
M=merge(a,b,all=TRUE)
and I also use the command derived from Rstudio, it also worked.
So, I think the problem is the way of data import, what is difference between the them?
Thanks!
Hi liupfskygre.
My apologies for responding so late, but just saw your replies.
Am glad you've found the discussions here beneficial. I've seen so much expertise and have been quite impressed. Am not familiar with Rstudio, so I can't comment about the data import issue.
The best to you.
Log in to answer this question.