Dear All
?
please provide your insigths on the following:
?
I have:
?
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
?
What I would like to do is to eliminate certain rows of this matrix based on the date column values. As you can see, in the second column my values (15 and 18) repeat 3 times each, so this column serves as an ID number if you will. Thus each ID numbers show up with 3 different date values in the first column. Now I would like to eliminate the rows with the earliest date per ID number. My result should look like this:
?
z <-x[-c(1,4),]
?
as allways, your help is greatly appreciated,
?
thanks,
?
Andras
selection based on dates
6 messages · Andras Farkas, Ben Tupper, Rui Barradas +2 more
Hi,
If the rows are already ordered:
x1<- as.data.frame(x)
x[with(x1,ave(seq_along(V2),V2,FUN=function(x) !x%in%min(x)))==1,]
#???? [,1]???? [,2] [,3]
#[1,] "1/5/13" "15" "25"
#[2,] "1/9/13" "15" "28"
#[3,] "2/5/13" "18" "35"
#[4,] "2/9/13" "18" "38"
#otherwise
?x[with(x1,unlist(tapply(as.Date(V1,"%m/%d/%y"),list(V2),function(x) x!=min(x)),use.names=FALSE)),]
#???? [,1]???? [,2] [,3]
#[1,] "1/5/13" "15" "25"
#[2,] "1/9/13" "15" "28"
#[3,] "2/5/13" "18" "35"
#[4,] "2/9/13" "18" "38"
A.K.
----- Original Message -----
From: Andras Farkas <motyocska at yahoo.com>
To: "r-help at r-project.org" <r-help at r-project.org>
Cc:
Sent: Tuesday, July 30, 2013 8:13 AM
Subject: [R] selection based on dates
Dear All
?
please provide your insigths on the following:
?
I have:
?
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
?
What I would like to do is to eliminate certain rows of this matrix based on the date column values. As you can see, in the second column my values (15 and 18) repeat 3 times each, so this column serves as an ID number if you will. Thus each ID numbers show up with 3 different date values in the first column. Now I would like to eliminate the rows with the earliest date per ID number. My result should look like this:
?
z <-x[-c(1,4),]
?
as allways, your help is greatly appreciated,
?
thanks,
?
Andras
______________________________________________
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.
On Jul 30, 2013, at 7:13 AM, Andras Farkas wrote:
Dear All
please provide your insigths on the following:
I have:
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
What I would like to do is to eliminate certain rows of this matrix based on the date column values. As you can see, in the second column my values (15 and 18) repeat 3 times each, so this column serves as an ID number if you will. Thus each ID numbers show up with 3 different date values in the first column. Now I would like to eliminate the rows with the earliest date per ID number. My result should look like this:
z <-x[-c(1,4),]
Hi,
It is not clear to me that the result you show in z is what you describe in words. On the other hand, I think the following will "eliminate the rows with the earliest date per ID number".
x <- structure(c("1/1/13", "1/5/13", "1/9/13", "2/1/13", "2/5/13",
"2/9/13", "15", "15", "15", "18", "18", "18", "20", "25", "28",
"30", "35", "38"), .Dim = c(6L, 3L))
ix <- duplicated(x[,2])
z <- x[ix,]
Is that what you are looking to achieve? By the way, the solution is not based upon selecting by date as your subject message suggests. Do you need to filter on dates instead? If that is so, then you will need a different solution.
Regards,
Ben
as allways, your help is greatly appreciated, thanks, Andras
______________________________________________ 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.
Ben Tupper Bigelow Laboratory for Ocean Sciences 60 Bigelow Drive, P.O. Box 380 East Boothbay, Maine 04544 http://www.bigelow.org
Hello,
Try the following.
idx <- which(c(TRUE, diff(as.integer(x[,2])) != 0))
x[-idx,]
Also, note that in constructs such as
a <-c("1/1/13",15,20)
both 15 and 20 are coerced to character. So your matrix is a matrix of
chars. For different types of data, use data.frames
Hope this helps,
Rui Barradas
Em 30-07-2013 13:13, Andras Farkas escreveu:
Dear All
please provide your insigths on the following:
I have:
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
What I would like to do is to eliminate certain rows of this matrix based on the date column values. As you can see, in the second column my values (15 and 18) repeat 3 times each, so this column serves as an ID number if you will. Thus each ID numbers show up with 3 different date values in the first column. Now I would like to eliminate the rows with the earliest date per ID number. My result should look like this:
z <-x[-c(1,4),]
as allways, your help is greatly appreciated,
thanks,
Andras
______________________________________________ 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.
I'm still a novice at R, so this may be a bit convoluted but it works:
colnames(x) = c("date", "id", "value")
do.call(rbind, (dlply(as.data.frame(x), .(id), function (y)
y[-c(which(as.Date(y$date, "%m/%d/%y") == min(as.Date(y$date,
"%m/%d/%y")), arr.ind=T)),])))
- FA
------ Original Message ------
From: "Andras Farkas" <motyocska at yahoo.com>
To: "r-help at r-project.org" <r-help at r-project.org>
Sent: 7/30/2013 8:13:21 AM
Subject: [R] selection based on dates
Dear All
please provide your insigths on the following:
I have:
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
What I would like to do is to eliminate certain rows of this matrix
based on the date column values. As you can see, in the second column
my values (15 and 18) repeat 3 times each, so this column serves as an
ID number if you will. Thus each ID numbers show up with 3 different
date values in the first column. Now I would like to eliminate the rows
with the earliest date per ID number. My result should look like this:
z <-x[-c(1,4),]
as allways, your help is greatly appreciated,
thanks,
Andras
______________________________________________ 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.
Just a note:
If the dataset is not ordered, this could result in:
?set.seed(24)
?xNew<-x[sample(1:nrow(x),6,replace=FALSE),]
idxN<-which(c(TRUE,diff(as.integer(xNew[,2]))!=0))
?xNew[-idxN,]
#[1] "2/1/13" "18"???? "30"?
xNew1<-xNew[order(xNew[,2],xNew[,1]),]
idx<-which(c(TRUE,diff(as.integer(xNew1[,2]))!=0))
?xNew1[-idx,]
#???? [,1]???? [,2] [,3]
#[1,] "1/5/13" "15" "25"
#[2,] "1/9/13" "15" "28"
#[3,] "2/5/13" "18" "35"
#[4,] "2/9/13" "18" "38"
#Same problem applies to my solution
? xNew2<- as.data.frame(xNew, stringsAsFactors=FALSE)
?xNew[with(xNew2,ave(as.numeric(as.Date(V1,"%m/%d/%y")),V2,FUN=function(x) !x%in% min(x)))!=0,] #keeps the original order of xNew
#???? [,1]???? [,2] [,3]
#[1,] "1/5/13" "15" "25"
#[2,] "2/9/13" "18" "38"
#[3,] "1/9/13" "15" "28"
#[4,] "2/5/13" "18" "35"
A.K.
----- Original Message -----
From: Rui Barradas <ruipbarradas at sapo.pt>
To: Andras Farkas <motyocska at yahoo.com>
Cc: "r-help at r-project.org" <r-help at r-project.org>
Sent: Tuesday, July 30, 2013 9:08 AM
Subject: Re: [R] selection based on dates
Hello,
Try the following.
idx <- which(c(TRUE, diff(as.integer(x[,2])) != 0))
x[-idx,]
Also, note that in constructs such as
a <-c("1/1/13",15,20)
both 15 and 20 are coerced to character. So your matrix is a matrix of
chars. For different types of data, use data.frames
Hope this helps,
Rui Barradas
Em 30-07-2013 13:13, Andras Farkas escreveu:
Dear All
please provide your insigths on the following:
I have:
a <-c("1/1/13",15,20)
b <-c("1/5/13",15,25)
c <-c("1/9/13",15,28)
d <-c("2/1/13",18,30)
e <-c("2/5/13",18,35)
f <-c("2/9/13",18,38)
x <-matrix(c(a,b,c,d,e,f),ncol=3,byrow=TRUE)
What I would like to do is to eliminate certain rows of this matrix based on the date column values. As you can see, in the second column my values (15 and 18) repeat 3 times each, so this column serves as an ID number if you will. Thus each ID numbers show up with 3 different date values in the first column. Now I would like to eliminate the rows with the earliest date per ID number. My result should look like this:
z <-x[-c(1,4),]
as allways, your help is greatly appreciated,
thanks,
Andras
______________________________________________ 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.
______________________________________________ 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.