how to combine presence only data sets to one presence/absence table

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

how to combine presence only data sets to one presence/absence table

Patrick Zimmermann
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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: how to combine presence only data sets to one presence/absence table

Chuck Cleland
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
>
> ______________________________________________
> [hidden email] 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: how to combine presence only data sets to one presence/absence table

Stephen Tucker
In reply to this post by Patrick Zimmermann
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 <[hidden email]> 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
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: how to combine presence only data sets to one presence/absence table

antu
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


Ananta Acharya
Graduate Student