Skip to content

help on date dataset

7 messages · anoumou, arun, Rui Barradas

anoumou
# 
Hi everybody, 
I am beginer in R and I need your precious help.
I want to create a small function  in R as in sas to retrieve date.
I have a file with data that import in R.
 DATE                       PAYS         nb_pays.ILI.
1   24/04/2009                 usa            0
2   24/04/2009                 usa            0
3   24/04/2009             Mexique            0
4   24/04/2009             Mexique            0
5   26/04/2009                 usa           20
6   26/04/2009                 usa           20
7   26/04/2009                 usa           20
8   26/04/2009                 usa           20
9   26/04/2009                 usa           20
10  26/04/2009             Mexique           18
11  27/04/2009                 usa           40
12  27/04/2009             Mexique           26
13  27/04/2009              Canada            6
14  27/04/2009               Spain            1
15  28/04/2009             Canada             6

I want to create something like that:
?	When entering two dates date1,date2 in the fuction extraction.
The result must be: a  new subdata  with one line  per date , per PAYS,per 
nb_pays.ILI (by summing all the number in variable nb_pays.ILI per date,per
country)  and the date must be between date1 and date2.
I sart to do somethings like that
extraction=function(date1,date2)
  {date<-derdata[["DATE"]]
   date
   sort(date)
   PAYS<-derdata[["PAYS"]]
   nb_pays.ILI<-derdata[["nb_pays.ILI."]]
   test1<-as.character(date,"%d %m %y")
   test1
   #the first date
   date1<- "04 03 2009"
   date1 <- strptime(date1, "%d %m %Y")
   date1
   unlist(unclass(date1))
   date1 <- as.POSIXct(date1)
   date1
   attributes(date1)
   date1 <-unclass(date1)
   date1
   
   #the second date
   date2<- "04 12 2009"
   date2 <- strptime(date2, "%d %m %Y")
   date2
   unlist(unclass(date2))
   date2 <- as.POSIXct(date2)
   date2
   attributes(date2)
   date2 <-unclass(date2)
   date2
   B1<- as.POSIXct(test1)
   B1 <-unclass(B1)
   B1
   B4 <- B1[(B1>date1) & (B1<date2)]
   B4
  
}




--
View this message in context: http://r.789695.n4.nabble.com/help-on-date-dataset-tp4649175.html
Sent from the R help mailing list archive at Nabble.com.
arun
# 
HI,
May be this helps:
dat1<-read.table(text="
?DATE?????????????????????? PAYS???????? nb_pays.ILI.
1?? 24/04/2009???????????????? usa??????????? 0
2?? 24/04/2009???????????????? usa??????????? 0
3?? 24/04/2009???????????? Mexique??????????? 0
4?? 24/04/2009???????????? Mexique??????????? 0
5?? 26/04/2009???????????????? usa?????????? 20
6?? 26/04/2009???????????????? usa?????????? 20
7?? 26/04/2009???????????????? usa?????????? 20
8?? 26/04/2009???????????????? usa?????????? 20
9?? 26/04/2009???????????????? usa?????????? 20
10? 26/04/2009???????????? Mexique?????????? 18
11? 27/04/2009???????????????? usa?????????? 40
12? 27/04/2009???????????? Mexique?????????? 26
13? 27/04/2009????????????? Canada??????????? 6
14? 27/04/2009?????????????? Spain??????????? 1
15? 27/04/2009?????????????? Spain??????????? 18
16? 28/04/2009???????????? Canada???????????? 6 
",sep="",header=TRUE,stringsAsFactors=FALSE)

fun1<-function(dat,date1,date2){
date1new<-as.Date(date1,format="%d/%m/%Y")
date2new<-as.Date(date2,format="%d/%m/%Y")
dat[,1]<-as.Date(dat$DATE,format="%d/%m/%Y")
res1<-with(dat,aggregate(nb_pays.ILI.,by=list(DATE,PAYS),sum))
names(res1)<-names(dat)
res2<-res1[res1[,1]>=date1new & res1[,1] <=date2new,]
res2<-res2[order(res2[,1],res2[,2]),]
#res2[,1]<-as.POSIXct(res2[,1])??? #if you want to convert to as.POSIXct()
rownames(res2)<-1:nrow(res2)
res2}
date1="24/04/2009"
?date2="27/04/2009"



?fun1(dat1,date1,date2)
#??????? DATE??? PAYS nb_pays.ILI.
#1 2009-04-24 Mexique??????????? 0
#2 2009-04-24???? usa??????????? 0
#3 2009-04-26 Mexique?????????? 18
#4 2009-04-26???? usa????????? 100
#5 2009-04-27? Canada??????????? 6
#6 2009-04-27 Mexique?????????? 26
#7 2009-04-27?? Spain?????????? 19
#8 2009-04-27???? usa?????????? 40


fun1(dat1,"27/04/2009","28/04/2009")
#??????? DATE??? PAYS nb_pays.ILI.
#1 2009-04-27? Canada??????????? 6
#2 2009-04-27 Mexique?????????? 26
#3 2009-04-27?? Spain?????????? 19
#4 2009-04-27???? usa?????????? 40
#5 2009-04-28? Canada??????????? 6


A.K.



----- Original Message -----
From: anoumou <teko_maurice at yahoo.fr>
To: r-help at r-project.org
Cc: 
Sent: Saturday, November 10, 2012 8:21 AM
Subject: [R] help on date dataset

Hi everybody, 
I am beginer in R and I need your precious help.
I want to create a small function? in R as in sas to retrieve date.
I have a file with data that import in R.
DATE? ? ? ? ? ? ? ? ? ? ?  PAYS? ? ? ?  nb_pays.ILI.
1?  24/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ? ? 0
2?  24/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ? ? 0
3?  24/04/2009? ? ? ? ? ?  Mexique? ? ? ? ? ? 0
4?  24/04/2009? ? ? ? ? ?  Mexique? ? ? ? ? ? 0
5?  26/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  20
6?  26/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  20
7?  26/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  20
8?  26/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  20
9?  26/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  20
10? 26/04/2009? ? ? ? ? ?  Mexique? ? ? ? ?  18
11? 27/04/2009? ? ? ? ? ? ? ?  usa? ? ? ? ?  40
12? 27/04/2009? ? ? ? ? ?  Mexique? ? ? ? ?  26
13? 27/04/2009? ? ? ? ? ? ? Canada? ? ? ? ? ? 6
14? 27/04/2009? ? ? ? ? ? ?  Spain? ? ? ? ? ? 1
15? 28/04/2009? ? ? ? ? ?  Canada? ? ? ? ? ?  6

I want to create something like that:
???? When entering two dates date1,date2 in the fuction extraction.
The result must be: a? new subdata? with one line? per date , per PAYS,per 
nb_pays.ILI (by summing all the number in variable nb_pays.ILI per date,per
country)? and the date must be between date1 and date2.
I sart to do somethings like that
extraction=function(date1,date2)
? {date<-derdata[["DATE"]]
?  date
?  sort(date)
?  PAYS<-derdata[["PAYS"]]
?  nb_pays.ILI<-derdata[["nb_pays.ILI."]]
?  test1<-as.character(date,"%d %m %y")
?  test1
?  #the first date
?  date1<- "04 03 2009"
?  date1 <- strptime(date1, "%d %m %Y")
?  date1
?  unlist(unclass(date1))
?  date1 <- as.POSIXct(date1)
?  date1
?  attributes(date1)
?  date1 <-unclass(date1)
?  date1
? 
?  #the second date
?  date2<- "04 12 2009"
?  date2 <- strptime(date2, "%d %m %Y")
?  date2
?  unlist(unclass(date2))
?  date2 <- as.POSIXct(date2)
?  date2
?  attributes(date2)
?  date2 <-unclass(date2)
?  date2
?  B1<- as.POSIXct(test1)
?  B1 <-unclass(B1)
?  B1
?  B4 <- B1[(B1>date1) & (B1<date2)]
?  B4
? 
}




--
View this message in context: http://r.789695.n4.nabble.com/help-on-date-dataset-tp4649175.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.
Rui Barradas
# 
Hello,

If I understand it correctly, you have a data.frame whose first column 
is a date and want to extract all lines between two given dates. If so, 
try the following. Note that I've added two new arguments to your function.

dat <- read.table(text="
DATE                       PAYS         nb_pays.ILI.
1   24/04/2009                 usa            0
2   24/04/2009                 usa            0
3   24/04/2009             Mexique            0
4   24/04/2009             Mexique            0
5   26/04/2009                 usa           20
6   26/04/2009                 usa           20
7   26/04/2009                 usa           20
8   26/04/2009                 usa           20
9   26/04/2009                 usa           20
10  26/04/2009             Mexique           18
11  27/04/2009                 usa           40
12  27/04/2009             Mexique           26
13  27/04/2009              Canada            6
14  27/04/2009               Spain            1
15  28/04/2009             Canada             6
", header = TRUE)

dat
dat$DATE <- as.Date(dat$DATE, format = "%d/%m/%Y")

extraction <- function(DF, date1, date2, format = "%Y-%m-%d"){
     date1 <- as.Date(date1, format)
     date2 <- as.Date(date2, format)
     idx <- date1 < DF[[1]] & DF[[1]] < date2
     DF[idx, ]
}

date1 <- "04 03 2009"
date2 <- "04 12 2009"
extraction(dat, date1, date2, format = "%d %m %Y")


Hope this helps,

Rui Barradas
Em 10-11-2012 13:21, anoumou escreveu:
Rui Barradas
# 
Hello,

Sorry, forgot the sum part.

extraction <- function(DF, date1, date2, format = "%Y-%m-%d"){
     date1 <- as.Date(date1, format)
     date2 <- as.Date(date2, format)
     idx <- date1 < DF[[1]] & DF[[1]] < date2
     aggregate(DF[idx, 3], DF[idx, 1:2], FUN = sum)
}


Hope this helps,

Rui Barradas
Em 10-11-2012 17:13, Rui Barradas escreveu:
arun
# 
HI Rui,

For some reason, I am not getting the result as expected.
date1
#[1] "24/04/2009"
?date2
#[1] "27/04/2009"

? extraction(dat,date1,date2,format="%Y-%m-%d")
#[1] DATE PAYS x?? 
#<0 rows> (or 0-length row.names)
#Warning messages:
#1: In extraction(dat, date1, date2, format = "%Y-%m-%d") :
?# Incompatible methods ("Ops.Date", "Ops.factor") for "<"
#2: In extraction(dat, date1, date2, format = "%Y-%m-%d") :
?# Incompatible methods ("Ops.factor", "Ops.Date") for "<"

extraction(dat,"24/04/2009","27/04/2009",format="%Y-%m-%d")
#[1] DATE PAYS x?? 
#<0 rows> (or 0-length row.names)
#Warning messages:
#1: In extraction(dat, "24/04/2009", "27/04/2009", format = "%Y-%m-%d") :

I tried with my function:
fun1<-function(dat,date1,date2){
date1new<-as.Date(date1,format="%d/%m/%Y")
date2new<-as.Date(date2,format="%d/%m/%Y")
dat[,1]<-as.Date(dat$DATE,format="%d/%m/%Y")
res1<-with(dat,aggregate(nb_pays.ILI.,by=list(DATE,PAYS),sum))
names(res1)<-names(dat)
res2<-res1[res1[,1]>=date1new & res1[,1] <=date2new,]
res2<-res2[order(res2[,1],res2[,2]),]
#res2[,1]<-as.POSIXct(res2[,1])??? #if you want to convert to as.POSIXct()
rownames(res2)<-1:nrow(res2)
res2}

?fun1(dat,date1,date2)
#??????? DATE??? PAYS nb_pays.ILI.
#1 2009-04-24 Mexique??????????? 0
#2 2009-04-24???? usa??????????? 0
#3 2009-04-26 Mexique?????????? 18
#4 2009-04-26???? usa????????? 100
#5 2009-04-27? Canada??????????? 6
#6 2009-04-27 Mexique?????????? 26
#7 2009-04-27?? Spain??????????? 1
#8 2009-04-27???? usa?????????? 40

A.K.




From: Rui Barradas <ruipbarradas at sapo.pt>
To: anoumou <teko_maurice at yahoo.fr>
Cc: r-help at r-project.org
Sent: Saturday, November 10, 2012 12:17 PM
Subject: Re: [R] help on date dataset

Hello,

Sorry, forgot the sum part.

extraction <- function(DF, date1, date2, format = "%Y-%m-%d"){
? ? date1 <- as.Date(date1, format)
? ? date2 <- as.Date(date2, format)
? ? idx <- date1 < DF[[1]] & DF[[1]] < date2
? ? aggregate(DF[idx, 3], DF[idx, 1:2], FUN = sum)
}


Hope this helps,

Rui Barradas
Em 10-11-2012 17:13, Rui Barradas 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.
Rui Barradas
# 
Hello,

Arun, you're using the wrong format, "%Y-%m-%d" is the default, with 
"24/04/2009" you must use

extraction(dat, date1, date2, format = "%d/%m/%Y")
#        DATE    PAYS   x
#1 2009-04-26 Mexique  18
#2 2009-04-26     usa 100

Rui Barradas

Em 10-11-2012 18:26, arun escreveu: