[R] how to combine presence only data sets to one presence/absence table

[Stephen Tucker]

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