Skip to content

Presence/ absence data from matrix to single column

14 messages · arun, John Kane, Andrea Goijman +3 more

#
I've been trying to reshape this database but haven't succeed at it. I tried
using loops but can't get it right. I just want to reshape my database from
this matrix, to the one below, with only one column of data. 

Year	Route	Point	Sp1	Sp2	Sp3
2004	123	123-1	0	1	0
2004	123	123-2	0	1	1
2004	123	123-10	1	1	0

What I want:

Year	Route	Point			
2004	123	123-1	Sp1	0	
2004	123	123-2	Sp1	0	
2004	123	123-10	Sp1	1	
2004	123	123-1	Sp2	1	
2004	123	123-2	Sp2	1	
2004	123	123-10	Sp2	1	
2004	123	123-1	Sp3	0	
2004	123	123-2	Sp3	1	
2004	123	123-10	Sp3	0	




--
View this message in context: http://r.789695.n4.nabble.com/Presence-absence-data-from-matrix-to-single-column-tp4645271.html
Sent from the R help mailing list archive at Nabble.com.
#
Hi,
Try this:
dat1<-read.table(text="
Year??? Route??? Point??? Sp1??? Sp2??? Sp3
2004??? 123??? 123-1??? 0??? 1??? 0
2004??? 123??? 123-2??? 0??? 1??? 1
2004??? 123??? 123-10??? 1??? 1??? 0
",header=TRUE,sep="",stringsAsFactors=FALSE)

library(reshape)
melt(dat1,id=c("Year","Route","Point"))
? Year Route? Point variable value
1 2004?? 123? 123-1????? Sp1???? 0
2 2004?? 123? 123-2????? Sp1???? 0
3 2004?? 123 123-10????? Sp1???? 1
4 2004?? 123? 123-1????? Sp2???? 1
5 2004?? 123? 123-2????? Sp2???? 1
6 2004?? 123 123-10????? Sp2???? 1
7 2004?? 123? 123-1????? Sp3???? 0
8 2004?? 123? 123-2????? Sp3???? 1
9 2004?? 123 123-10????? Sp3???? 0
A.K. 





----- Original Message -----
From: agoijman <agoijman at cnia.inta.gov.ar>
To: r-help at r-project.org
Cc: 
Sent: Saturday, October 6, 2012 11:03 AM
Subject: [R] Presence/ absence data from matrix to single column

I've been trying to reshape this database but haven't succeed at it. I tried
using loops but can't get it right. I just want to reshape my database from
this matrix, to the one below, with only one column of data. 

Year??? Route??? Point??? Sp1??? Sp2??? Sp3
2004??? 123??? 123-1??? 0??? 1??? 0
2004??? 123??? 123-2??? 0??? 1??? 1
2004??? 123??? 123-10??? 1??? 1??? 0

What I want:

Year??? Route??? Point??? ??? ??? 
2004??? 123??? 123-1??? Sp1??? 0??? 
2004??? 123??? 123-2??? Sp1??? 0??? 
2004??? 123??? 123-10??? Sp1??? 1??? 
2004??? 123??? 123-1??? Sp2??? 1??? 
2004??? 123??? 123-2??? Sp2??? 1??? 
2004??? 123??? 123-10??? Sp2??? 1??? 
2004??? 123??? 123-1??? Sp3??? 0??? 
2004??? 123??? 123-2??? Sp3??? 1??? 
2004??? 123??? 123-10??? Sp3??? 0??? 




--
View this message in context: http://r.789695.n4.nabble.com/Presence-absence-data-from-matrix-to-single-column-tp4645271.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
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 the reshape2 package. You will probablly have to install the package.  install.packages("reshape2)

with your data as xx :
library(reshape2)
melt(xx, id =c("Year", "Route", "Point"))

seems to do what you want.

John Kane
Kingston ON Canada
____________________________________________________________
Share photos & screenshots in seconds...
TRY FREE IM TOOLPACK at http://www.imtoolpack.com/default.aspx?rc=if1
Works in all emails, instant messengers, blogs, forums and social networks.
#
On 10/07/2012 01:03 AM, agoijman wrote:
Hi agoijman,
You can do this using the rep_n_stack function.

adat<-data.frame(Year=rep(2004,3),Route=rep(123,3),
  Point=c("123-1","123-2","123-10"),Sp1=c(0,0,1),
  Sp2=c(1,1,1),Sp3=c(0,1,0))
library(prettyR)
rep_n_stack(adat,c("Sp1","Sp2","Sp3"),
  stack.names=c("Sp-names","Sp-values"))

Jim
#
Also reshape() will work:

adat<-data.frame(Year=rep(2004,3),Route=rep(123,3),
  Point=c("123-1","123-2","123-10"),Sp1=c(0,0,1),
  Sp2=c(1,1,1),Sp3=c(0,1,0))
reshape(adat, varying=4:6, v.name="Sp-value", 
  times=c("Sp1", "Sp2", "Sp3"), idvar="Point", 
  timevar="Sp-name", direction="long")

----------------------------------------------
David L Carlson
Associate Professor of Anthropology
Texas A&M University
College Station, TX 77843-4352
#
The problem with that, is that I just wrote an example of my database, but I
have around 250 species and more than 500 sites. In the approach you show
me, it looks like I have to enter every species name and sites individually,
right?



--
View this message in context: http://r.789695.n4.nabble.com/Presence-absence-data-from-matrix-to-single-column-tp4645271p4645331.html
Sent from the R help mailing list archive at Nabble.com.
#
Hello,

I haven't been following this thread but apparently the answer to your 
worries is no.
You can use a combination of names() and grep() to sort it out. 
something like

#nms <- names(adat)
nms <- c("Year", "Route", "Point", paste0("Sp", 1:250))

pattern <- "^Sp[[:digit:]]+$"
whichCols <- grep(pattern, nms)
whichNames <- nms[whichCols]

reshape(..., varying = whichCols, times = whichNames, ...)


Hope this helps,

Rui Barradas
Em 07-10-2012 15:35, agoijman escreveu:
#
Hi,
I guess you are not talking about the melt() method.
dat1<-read.table(text="
Year??? Route??? Point??? Sp1??? Sp2??? Sp3
2004??? 123??? 123-1??? 0??? 1??? 0
2004??? 123??? 123-2??? 0??? 1??? 1
2004??? 123??? 123-10??? 1??? 1??? 0
",header=TRUE,sep="",stringsAsFactors=FALSE)


#If all the Sp columns are located next to another as shown in your example dataset, then you can also try this:
name1<-unlist(strsplit(paste(colnames(dat1)[4:6],collapse=" ")," "))
reshape(dat1,varying=4:6,v.name="Sp-value",times=name1,timevar="Sp-name",idvar=c("Year","Route","Point"),direction="long")

A.K.






----- Original Message -----
From: Rui Barradas <ruipbarradas at sapo.pt>
To: agoijman <agoijman at cnia.inta.gov.ar>
Cc: r-help at r-project.org
Sent: Sunday, October 7, 2012 2:32 PM
Subject: Re: [R] Presence/ absence data from matrix to single column

Hello,

I haven't been following this thread but apparently the answer to your 
worries is no.
You can use a combination of names() and grep() to sort it out. 
something like

#nms <- names(adat)
nms <- c("Year", "Route", "Point", paste0("Sp", 1:250))

pattern <- "^Sp[[:digit:]]+$"
whichCols <- grep(pattern, nms)
whichNames <- nms[whichCols]

reshape(..., varying = whichCols, times = whichNames, ...)


Hope this helps,

Rui Barradas
Em 07-10-2012 15:35, agoijman escreveu:
______________________________________________
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.
#
You should keep the context of your thread intact for those of us using
email instead of Nabble.

The locations were not listed individually anywhere in the reshape()
command. I listed Point as the id variable so reshape would use that to
create row names, but if you delete idvar="Point", reshape will give each
row a consecutive number followed by the species name or number.

I did list the species names individually in the times= argument to make
things a bit clearer since reshape() can be a confusing command at first.
But this would work as well:

reshape(adat, varying=4:6, v.name="Sp-value", 
  times=names(adat)[4:6], idvar="Point", 
  timevar="Sp-name", direction="long")

----------------------------------------------
David L Carlson
Associate Professor of Anthropology
Texas A&M University
College Station, TX 77843-4352
I
individually,
http://r.789695.n4.nabble.com/Presence-absence-data-from-matrix-to-single-co
lumn-tp4645271p4645331.html
http://www.R-project.org/posting-guide.html
#
Hello,

If all your species have different names, you can allways try one of
1. if the non-species follow a pattern, negate those columns and you'll 
have the species columns.
2. are the species the last columns? Use positional referencing.

Rui Barradas
Em 08-10-2012 00:16, Andrea Goijman escreveu:
#
HI,

Sorry, I complicated a code where it was not required at all.
Just using colnames(dat1)[4:6] or names(dat1)[4:6] should work if the species columns are adjacent to each other.


reshape(dat1,varying=4:6,v.name="Sp-value",times=colnames(dat1)[4:6],timevar="Sp-name",idvar=c("Year","Route","Point"),direction="long")
#or
reshape(dat1,varying=4:6,v.name="Sp-value",times=names(dat1)[4:6],timevar="Sp-name",idvar=c("Year","Route","Point"),direction="long")
A.K.