R help - Jul 2007 - how to combine presence only data sets to one presence/absence table

Problem: I have a Set of samples each with a list of observed species
(presence only).
Data is stored in a excel spreadsheet and the columns (spl) have
different numbers of observations (spcs).
Now I want to organize the data in a species by sample matrix with
presence/absence style in R.

data style (in excel):

spl_A	spl_B	spl_C
spcs1	spcs1	spcs2
spcs2	spcs3	spcs3
spcs4		spcs5
spcs5

desired style:

	spl_A	spl_B	spl_C
spcs1	1	1	0
spcs2	1	0	1
spcs3	0	1	1
.
.
.

How and in which form do I import the data to R?
(read.table() seems not to be appropriate, as data is not organized as a table)

How can I create the species by sample matrix?

Thanks for any help,
Patrick Zimmermann

Patrick Zimmermann wrote:
> Problem: I have a Set of samples each with a list of observed species
> (presence only).
> Data is stored in a excel spreadsheet and the columns (spl) have
> different numbers of observations (spcs).
> Now I want to organize the data in a species by sample matrix with
> presence/absence style in R.
> 
> data style (in excel):
> 
> spl_A	spl_B	spl_C
> spcs1	spcs1	spcs2
> spcs2	spcs3	spcs3
> spcs4		spcs5
> spcs5
> 
> desired style:
> 
> 	spl_A	spl_B	spl_C
> spcs1	1	1	0
> spcs2	1	0	1
> spcs3	0	1	1
> .
> .
> .
> 
> How and in which form do I import the data to R?
> (read.table() seems not to be appropriate, as data is not organized as a
table)
> 
> How can I create the species by sample matrix?
  I'm not going to tackle how to read in the Excel data, but assuming
you had several vectors of species names gather together in a list, you
could construct a presence/absence data frame or matrix as follows:

spl_A <-
c("spcs1","spcs2","spcs4","spcs5")
spl_B <- c("spcs1","spcs3")
spl_C <- c("spcs2","spcs3","spcs5")

mylist <- list(spl_A = spl_A, spl_B = spl_B, spl_C = spl_C)

mymat <- sapply(mylist,
          function(x){as.numeric(sort(unique(unlist(mylist))) %in% x)})

rownames(mymat) <- sort(unique(unlist(mylist)))

mymat
      spl_A spl_B spl_C
spcs1     1     1     0
spcs2     1     0     1
spcs3     0     1     1
spcs4     1     0     0
spcs5     1     0     1
> Thanks for any help,
> Patrick Zimmermann
> 
> ______________________________________________
> R-help at stat.math.ethz.ch 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. 
-- 
Chuck Cleland, Ph.D.
NDRI, Inc.
71 West 23rd Street, 8th floor
New York, NY 10010
tel: (212) 845-4495 (Tu, Th)
tel: (732) 512-0171 (M, W, F)
fax: (917) 438-0894

I think you can still read as a table, just use argument fill=TRUE.

Reading from Excel in general: you can save data as 'csv' or
tab-delimited
file and then use read.csv or read.delim, respectively, or use one of the
packages listed in the following post (for some reason lines breaks are
messed up but hope you can extract the content):
http://tolstoy.newcastle.edu.au/R/e2/help/07/06/19925.html

## read in data
x <- 
read.table(textConnection(
"spl_A	spl_B	spl_C
spcs1	spcs1	spcs2
spcs2	spcs3	spcs3
spcs4		spcs5
spcs5"
),fill=TRUE,header=TRUE,na.string="")

Then,

## 1. find unique
spcs <- sort(na.omit(unique(unlist(x)))) 
## 2. create matrix of zeros
mat <- matrix(0,ncol=ncol(x),nrow=length(spcs),
              dimnames=list(spcs,names(x))) 
## 3. assign zeros to matches
for( i in 1:ncol(mat) ) mat[match(x[,i],rownames(mat)),i] <- 1

Alternatively,
## find unique
spcs <- sort(na.omit(unique(unlist(x)))) 
## return the matrix you want (combine steps 2 and 3 from above)
sapply(x,function(.x,spcs)
       "names<-"(ifelse(!is.na(match(spcs,.x)),1,0),spcs),spcs)

Hope this helps.

ST

--- Patrick Zimmermann <brassnotdead at googlemail.com> wrote:
> Problem: I have a Set of samples each with a list of observed species
> (presence only).
> Data is stored in a excel spreadsheet and the columns (spl) have
> different numbers of observations (spcs).
> Now I want to organize the data in a species by sample matrix with
> presence/absence style in R.
> 
> data style (in excel):
> 
> spl_A	spl_B	spl_C
> spcs1	spcs1	spcs2
> spcs2	spcs3	spcs3
> spcs4		spcs5
> spcs5
> 
> desired style:
> 
> 	spl_A	spl_B	spl_C
> spcs1	1	1	0
> spcs2	1	0	1
> spcs3	0	1	1
> .
> .
> .
> 
> How and in which form do I import the data to R?
> (read.table() seems not to be appropriate, as data is not organized as a
> table)
> 
> How can I create the species by sample matrix?
> 
> Thanks for any help,
> Patrick Zimmermann
> 
> ______________________________________________
> R-help at stat.math.ethz.ch 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.
>

What about the opposite of this, This has been very helpful for me, but at
the same time, I needed the opposite of this to..

ie, this to

        spl_A	spl_B	spl_C 
spcs1	1	1	0 
spcs2	1	0	1 
spcs3	0	1	1

this

spl_A	  spl_B	spl_C 
spcs1  spcs1	spcs2 
spcs2	 spcs3	spcs3 
spcs4	 spcs5 
spcs5


Thank you

--
View this message in context:
http://r.789695.n4.nabble.com/how-to-combine-presence-only-data-sets-to-one-presence-absence-table-tp830140p3465121.html
Sent from the R help mailing list archive at Nabble.com.