Good morning, I have an excel spreadsheet with similarities among objects. The format of the file is the following: 1st row: <empty cell>,<object-1-name>,<object-name-2>,...,<object-N> 2nd row: <object-name-1>,0,s<1,2>,s<1,3>,...,s<1,N> 3rd row: <object-name-2>,s<2,1>,0,s<2,3>,...,s<2,N> The table is symmetrical and contains the similarities among the objects (s<x,k> stands for similarity among x and k). The first row contains the name of the objects (strings of characters) in the same order that these same names are contained in the first column. The cell <1,1> is empty. How can I read this in R so that after reading it I can perform a hierarchical clustering. The table is 78x78 so it is relatively easy to manually change something if it makes it easier to read. I found many articles about reading a data table with variables and then constructing the distance matrix from within R, but I have the similarity matrix instead. Thank you for your help George
Hi r-help-bounces at r-project.org napsal dne 01.02.2011 10:20:48:> Good morning, > > I have an excel spreadsheet with similarities among objects. The formatof> the file is the following: > > 1st row: <empty cell>,<object-1-name>,<object-name-2>,...,<object-N> > 2nd row: <object-name-1>,0,s<1,2>,s<1,3>,...,s<1,N> > 3rd row: <object-name-2>,s<2,1>,0,s<2,3>,...,s<2,N> > > The table is symmetrical and contains the similarities among the objects > (s<x,k> stands for similarity among x and k). The first row contains the > name of the objects (strings of characters) in the same order that these > same names are contained in the first column. The cell <1,1> is empty. > > How can I read this in R so that after reading it I can perform a > hierarchical clustering. The table is 78x78 so it is relatively easy to > manually change something if it makes it easier to read.Select your table in Excel Press Ctrl-C In R do test<-read.delim("clipboard") you shall get something like that> testX a b c 1 a 1 23 3 2 b 4 5 6 3 c 7 8 9>you has to get rid of first column> mat<-as.matrix(test[,-1])and if you want you can put row.names tou your matrix> row.names(mat)<-colnames(mat) > mata b c a 1 23 3 b 4 5 6 c 7 8 9> row.names(mat)<-test$X > mata b c a 1 23 3 b 4 5 6 c 7 8 9>Regards Petr> > I found many articles about reading a data table with variables and then > constructing the distance matrix from within R, but I have thesimilarity> matrix instead. > > Thank you for your help > > George > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.
Dear Petr, It worked fine. Thanks for helping. George -----Original Message----- From: Petr PIKAL [mailto:petr.pikal at precheza.cz] Sent: Wednesday, February 02, 2011 1:18 PM To: George Kakarontzas Cc: r-help at r-project.org Subject: Odp: [R] Read a similarity matrix from excel Hi r-help-bounces at r-project.org napsal dne 01.02.2011 10:20:48:> Good morning, > > I have an excel spreadsheet with similarities among objects. The > formatof> the file is the following: > > 1st row: <empty cell>,<object-1-name>,<object-name-2>,...,<object-N> > 2nd row: <object-name-1>,0,s<1,2>,s<1,3>,...,s<1,N> > 3rd row: <object-name-2>,s<2,1>,0,s<2,3>,...,s<2,N> > > The table is symmetrical and contains the similarities among the > objects (s<x,k> stands for similarity among x and k). The first row > contains the name of the objects (strings of characters) in the same > order that these same names are contained in the first column. The cell<1,1> is empty.> > How can I read this in R so that after reading it I can perform a > hierarchical clustering. The table is 78x78 so it is relatively easy > to manually change something if it makes it easier to read.Select your table in Excel Press Ctrl-C In R do test<-read.delim("clipboard") you shall get something like that> testX a b c 1 a 1 23 3 2 b 4 5 6 3 c 7 8 9>you has to get rid of first column> mat<-as.matrix(test[,-1])and if you want you can put row.names tou your matrix> row.names(mat)<-colnames(mat) > mata b c a 1 23 3 b 4 5 6 c 7 8 9> row.names(mat)<-test$X > mata b c a 1 23 3 b 4 5 6 c 7 8 9>Regards Petr> > I found many articles about reading a data table with variables and > then constructing the distance matrix from within R, but I have thesimilarity> matrix instead. > > Thank you for your help > > George > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.