Dear all,
how can I, with R, transform a presence-absence (0/1) matrix of species
occurrences into a presence-only table (3 columns) with the names of the
species (1st column), the lat information of the sites (2nd column) and
the lon information of the sites (3rd column), as given in the below
example?
Thanks a lot for your help!
Christian
my dataframe:
site lat lon spec1 spec2 spec3 spec4
site1 10 11 1 0 1 0
site2 20 21 1 1 1 0
site3 30 31 0 1 1 1
my desired new dataframe:
species lat lon
spec1 10 11
spec1 20 21
spec2 20 21
spec2 30 31
spec3 10 11
spec3 20 21
spec3 30 31
spec4 30 31
--
Christian Hof, PhD student
Center for Macroecology & Evolution
University of Copenhagen
www.macroecology.ku.dk
&
Biodiversity & Global Change Lab
Museo Nacional de Ciencias Naturales, Madrid
www.biochange-lab.eu
mobile ES .. +34 697 508 519
mobile DE .. +49 176 205 189 27
mail .. chof at bio.ku.dk
mail2 .. vogelwart at gmx.de
blog .. www.vogelwart.de
Christian, You need to use reshape to convert to the 'long' format. Check the help page ?reshape for details.>dat <- read.table('clipboard', header=TRUE) >datsite lat lon spec1 spec2 spec3 spec4 1 site1 10 11 1 0 1 0 2 site2 20 21 1 1 1 0 3 site3 30 31 0 1 1 1> dat.long <- reshape(dat, varying = list(names(dat)[4:7]),timevar="species", times=names(dat)[4:7], direction="long")> dat.longsite lat lon species spec1 id 1.spec1 site1 10 11 spec1 1 1 2.spec1 site2 20 21 spec1 1 2 3.spec1 site3 30 31 spec1 0 3 1.spec2 site1 10 11 spec2 0 1 2.spec2 site2 20 21 spec2 1 2 3.spec2 site3 30 31 spec2 1 3 1.spec3 site1 10 11 spec3 1 1 2.spec3 site2 20 21 spec3 1 2 3.spec3 site3 30 31 spec3 1 3 1.spec4 site1 10 11 spec4 0 1 2.spec4 site2 20 21 spec4 0 2 3.spec4 site3 30 31 spec4 1 3> dat.long[dat.long$spec1 == 1, ]site lat lon species spec1 id 1.spec1 site1 10 11 spec1 1 1 2.spec1 site2 20 21 spec1 1 2 2.spec2 site2 20 21 spec2 1 2 3.spec2 site3 30 31 spec2 1 3 1.spec3 site1 10 11 spec3 1 1 2.spec3 site2 20 21 spec3 1 2 3.spec3 site3 30 31 spec3 1 3 3.spec4 site3 30 31 spec4 1 3 -Christos> -----Original Message----- > From: r-help-bounces at r-project.org > [mailto:r-help-bounces at r-project.org] On Behalf Of Christian Hof > Sent: Friday, May 02, 2008 5:28 PM > To: r-help at r-project.org > Subject: [R] data transformation > > Dear all, > how can I, with R, transform a presence-absence (0/1) matrix > of species occurrences into a presence-only table (3 columns) > with the names of the species (1st column), the lat > information of the sites (2nd column) and the lon information > of the sites (3rd column), as given in the below example? > Thanks a lot for your help! > Christian > > > my dataframe: > > site lat lon spec1 spec2 spec3 spec4 > site1 10 11 1 0 1 0 > site2 20 21 1 1 1 0 > site3 30 31 0 1 1 1 > > > my desired new dataframe: > > species lat lon > spec1 10 11 > spec1 20 21 > spec2 20 21 > spec2 30 31 > spec3 10 11 > spec3 20 21 > spec3 30 31 > spec4 30 31 > > > > -- > Christian Hof, PhD student > > Center for Macroecology & Evolution > University of Copenhagen > www.macroecology.ku.dk > & > Biodiversity & Global Change Lab > Museo Nacional de Ciencias Naturales, Madrid www.biochange-lab.eu > > mobile ES .. +34 697 508 519 > mobile DE .. +49 176 205 189 27 > mail .. chof at bio.ku.dk > mail2 .. vogelwart at gmx.de > blog .. www.vogelwart.de > > ______________________________________________ > 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. > >
Hi Christian, Here's a way using the reshape package:> dfrsite lat lon spec1 spec2 spec3 spec4 1 site1 10 11 1 0 1 0 2 site2 20 21 1 1 1 0 3 site3 30 31 0 1 1 1> library(reshape) > dfr <- melt(dfr[, -1], id=1:2, variable_name='species') > dfr <- dfr[dfr$value>0,] > dfrlat lon species value 1 10 11 spec1 1 2 20 21 spec1 1 5 20 21 spec2 1 6 30 31 spec2 1 7 10 11 spec3 1 8 20 21 spec3 1 9 30 31 spec3 1 12 30 31 spec4 1 The 'value', variable is not interesting here, but if you had counts rather than presence/absence it could be. best, Kingsford Jones On Fri, May 2, 2008 at 2:27 PM, Christian Hof <chof at bio.ku.dk> wrote:> Dear all, > how can I, with R, transform a presence-absence (0/1) matrix of species > occurrences into a presence-only table (3 columns) with the names of the > species (1st column), the lat information of the sites (2nd column) and the > lon information of the sites (3rd column), as given in the below example? > Thanks a lot for your help! > Christian > > > my dataframe: > > site lat lon spec1 spec2 spec3 spec4 > site1 10 11 1 0 1 0 > site2 20 21 1 1 1 0 > site3 30 31 0 1 1 1 > > > my desired new dataframe: > > species lat lon > spec1 10 11 > spec1 20 21 > spec2 20 21 > spec2 30 31 > spec3 10 11 > spec3 20 21 > spec3 30 31 > spec4 30 31 > > > > -- > Christian Hof, PhD student > > Center for Macroecology & Evolution > University of Copenhagen > www.macroecology.ku.dk > & > Biodiversity & Global Change Lab > Museo Nacional de Ciencias Naturales, Madrid > www.biochange-lab.eu > > mobile ES .. +34 697 508 519 > mobile DE .. +49 176 205 189 27 > mail .. chof at bio.ku.dk > mail2 .. vogelwart at gmx.de > blog .. www.vogelwart.de > > ______________________________________________ > 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. >
Try this:
newx <- with(x, cbind(stack(x, select = grep("spec", names(x))),
lat, lon))
newx[newx$values > 0, -1]
On 5/2/08, Christian Hof <chof@bio.ku.dk> wrote:>
> Dear all,
> how can I, with R, transform a presence-absence (0/1) matrix of species
> occurrences into a presence-only table (3 columns) with the names of the
> species (1st column), the lat information of the sites (2nd column) and the
> lon information of the sites (3rd column), as given in the below example?
> Thanks a lot for your help!
> Christian
>
>
> my dataframe:
>
> site lat lon spec1 spec2 spec3 spec4
> site1 10 11 1 0 1 0
> site2 20 21 1 1 1 0
> site3 30 31 0 1 1 1
>
>
> my desired new dataframe:
>
> species lat lon
> spec1 10 11
> spec1 20 21
> spec2 20 21
> spec2 30 31
> spec3 10 11
> spec3 20 21
> spec3 30 31
> spec4 30 31
>
>
>
> --
> Christian Hof, PhD student
>
> Center for Macroecology & Evolution
> University of Copenhagen
> www.macroecology.ku.dk
> &
> Biodiversity & Global Change Lab
> Museo Nacional de Ciencias Naturales, Madrid
> www.biochange-lab.eu
>
> mobile ES .. +34 697 508 519
> mobile DE .. +49 176 205 189 27
> mail .. chof@bio.ku.dk
> mail2 .. vogelwart@gmx.de
> blog .. www.vogelwart.de
>
> ______________________________________________
> R-help@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.
>
--
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O
[[alternative HTML version deleted]]