I think it is appropriate because a lot of biological data is in excel, biologists and computational biologists use excel, they will probably never stop using excel, and many bioinformatics people trying to do computation on data stuck in excel files may not realize it is possible or easy to do what @sanju is asking. By reading this question, they will discover that it is. He should edit the question title to be more specific however.
How To Create A Matrix In R?
Hi all
I have 300 sheets in an excel file. I need only Sequence identity data from this file. This data is in 9th column. I imported all sequence identity values from 300 sheets in to R using the following code.
library(gdata) myfile<-NULL; for (i in 1:300)
{ myfile[[i]]<-read.xls("C://Users//Desktop//mydata.xls",sheet=i,head=F)[,9] } .
Next I have to apply distance formula and to create a matrix. The formula is
distance= 100-sequenceidentity.
How to apply this formula in R and how can I create a matrix ?
2 answers
Have a look here and the documentation
HTH!
I think its basic R and not quite appropriate but anyway.
So it seems you have the upper? lower? triangle of your identity matrix in a list (but why 230 rows for 300 sheets? There is something amiss). You could do something similar to this, but look out for the order of the indices, and if you have the identity with itself in your data etc...!
li <- lapply(5:1, function(x){ 1:x }) #li == myfile
maxl <- max(sapply(li, length))
mat <- diag(maxl)
mat[lower.tri(mat,diag=TRUE)] <- unlist(li)
dist.mat <- 100 - mat
All the better. So someone watching him struggle could learn two things. As long as he's struggling to apply computation to a biological problem, it's instructive to others. Masters in the art may see that his problem is really just some language ignorance, but for the majority of us, it may be confounding. Half the questions on this board could probably be reduced to computational ignorance (nothing really to do with biology). e.g. "Want to extract sequences from UCSC? Don't bother us, go look at an SQL forum." As long as it's in a biological context, I think it's ok to ask here.
I agree. I would be happy to see more R, Bioconductor, etc. questions being addressed here. My previous experience with R forums have been mostly negative. The documentation is sometimes confusing, and the existing forums are full of people just telling users to rtfm...
• 0 views
•
link
written
by
Malachi Griffith
Log in to answer this question.
More posts like this
-
Scatterplots Showing Correlation Between Gene Pairs
written by spaul8505 •
-
Apply Functions In R
written by sundarrayalu •
-
Fold Change Problem In Rankproduct Bioconductor Package
written by James •
-
Getting Pairwise Sequence Alignment Score With Biopython
written by Lakshmi •
-
Format For Distance Matrix For Running Hclust On R
written by H2P •
-
Hierarchical Clustering In R
written by Sanju •
-
Multiple Sequence Alignment
written by Sanju •
-
Calculating Genetic Distances Between Protein Sequences
written by Sanju •
-
How To Read Multiple Excel Sheets In R Programming?
written by Sanju •
-
Hierarchial Clustering
written by Sanju •
You did not tell us how many rows there are on each sheet. Is it only one, multiple values per sheet and if multiple values, always the same number?
Sanju, what options did you already try?
@ Ido tamir first sheet contains 230 rows and 10 columns. second sheet contains 229 rows. 3rd sheet contains 228 rows and so on. But no:of columns(10 columns) are same in all sheets.
@Egon, I tried dist.alignment function to generate a distance matrix. But I got error because my file is not an aligned file. It is an excel file. My aim is to generte distance matrix using the following formula. Distance = 100-sequenceidentity. My sequence identity values are in 9th column.