[R] Read a similarity matrix from excel

[George Kakarontzas]

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

Select your table in Excel
Press Ctrl-C
In R do
test<-read.delim("clipboard")

you shall get something like that

> test
  X 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)
> mat
  a  b c
a 1 23 3
b 4  5 6
c 7  8 9
> row.names(mat)<-test$X
> mat
  a  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 the
similarity
> 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 guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.