An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20130105/3c9b7c57/attachment-0001.pl>
Need help with time series data
4 messages · Simonas Kecorius, Achim Zeileis, arun
On Sat, 5 Jan 2013, Simonas Kecorius wrote:
Dear R users,
Could you share your knowledge on following problem:
Suppose we have dataframe:
ID TIME Data1 Data2 Data3
........... Data700
1. 2013-01-01 00:00:00 34 53 66
............ 55
2. 2013-01-01 00:00:01 333 4 5
............ 50
3. 2013-01-01 00:00:02 and so on
4. 2013-01-01 00:00:03 and so on
5. 2013-01-01 03:00:45 44 33 4
............. 66
6. 2013-01-01 03:00:46 46 35 7
............. 6
Notice that between ID 4 and ID 5 there is a gap, that instrument was
turned off. It started automatically in 2013-01-01 03:00:45 and continues
till the next turn off.
What I need R to do, is to find these gaps, and insert missing time values
with empty Data lines to have no gaps in time and empty (or NA) values in
data place:
ID TIME Data1 Data2 Data3
........... Data700
1. 2013-01-01 00:00:00 34 53 66
............ 55
2. 2013-01-01 00:00:01 333 4 5
............ 50
3. 2013-01-01 00:00:02 and so on
4. 2013-01-01 00:00:03 and so on
5. 2013-01-01
00:00:04
<- time with empty (or NA) data values inserted
6. 2013-01-01
00:00:05
<- time with empty (or NA) data values inserted
7. 2013-01-01
00:00:06
<- time with empty (or NA) data values inserted
................
and so on, until the time when instrument works again:
2013-01-01 03:00:45 44 33 4
............. 66
2013-01-01 03:00:46 46 35 7
............. 6
You can use zoo time series with POSIXct time stamps (or chron or
timeDate) and then extend to a regular grid. For example:
## regular time grid (by second)
t <- seq(as.POSIXct("2000-01-01 00:00"), by = "1 sec", length = 5)
## zoo series with only part of the time stamps (3rd stamp missing)
z <- zoo(1:4, t[-3])
## extend to full time grid by merging with an empty zoo series
z2 <- merge(zoo( , t), z)
This yields:
2000-01-01 00:00:00 2000-01-01 00:00:01 2000-01-01 00:00:02
1 2 NA
2000-01-01 00:00:03 2000-01-01 00:00:04
3 4
You can also use different strategies for filling these NAs.
See help("merge.zoo", package = "zoo") for another example and the
vignettes in vignette(package = "zoo") for more details.
All your suggestions will be appreciated! Simonas Kecorius ** [[alternative HTML version deleted]]
______________________________________________ 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,
You could try this:
Time1<-as.POSIXct("2013-01-01 00:00:00")
?tseq<-seq(Time1,length.out=50,by="secs")
?dat2<-data.frame(TIME=tseq,Data1=NA,Data2=NA,Data3=NA)
dat3<-read.table(text="
ID??? Date TIME??????????????????????? Data1??????? Data2??????? Data3
1??? 2013-01-01 00:00:00????? 34??????????????? 53??????????? 66
2??? 2013-01-01 00:00:01????? 333????????????? 4????????????? 5
3?? 2013-01-01 00:00:05????? 333????????????? 4????????????? 5
4??? 2013-01-01 00:00:20????? 34??????????????? 63??????????? 66
5??? 2013-01-01 00:00:25????? 433????????????? 5???????????? 8
6?? 2013-01-01 00:00:26????? 533????????????? 8????????????? 5
",sep="",stringsAsFactors=FALSE,header=TRUE)
?dat4<-data.frame(TIME=as.POSIXct(paste(dat3$Date,dat3$TIME)),dat3[,4:6])
?dat2[,-1][dat2[,1]%in%dat4[,1],]<-dat4[,-1]
?head(dat2)
?# ?????????????? TIME Data1 Data2 Data3
#1 2013-01-01 00:00:00??? 34??? 53??? 66
#2 2013-01-01 00:00:01?? 333???? 4???? 5
#3 2013-01-01 00:00:02??? NA??? NA??? NA
#4 2013-01-01 00:00:03??? NA??? NA??? NA
#5 2013-01-01 00:00:04??? NA??? NA??? NA
#6 2013-01-01 00:00:05?? 333???? 4???? 5
A.K.
----- Original Message -----
From: Simonas Kecorius <simolas2008 at gmail.com>
To: r-help at r-project.org
Cc:
Sent: Saturday, January 5, 2013 8:54 AM
Subject: [R] Need help with time series data
Dear R users,
Could you share your knowledge on following problem:
Suppose we have dataframe:
ID? ? TIME? ? ? ? ? ? ? ? ? ? ? ? Data1? ? ? ? Data2? ? ? ? Data3
........... Data700
1.? ? 2013-01-01 00:00:00? ? ? 34? ? ? ? ? ? ? ? 53? ? ? ? ? ? 66
............? ? 55
2.? ? 2013-01-01 00:00:01? ? ? 333? ? ? ? ? ? ? 4? ? ? ? ? ? ? 5
............? ? 50
3.? ? 2013-01-01 00:00:02? ? ? and so on
4.? ? 2013-01-01 00:00:03? ? ? and so on
5.? ? 2013-01-01 03:00:45? ? ? 44? ? ? ? ? ? ? ? 33? ? ? ? ? ? ? 4
.............? ? 66
6.? ? 2013-01-01 03:00:46? ? ? 46? ? ? ? ? ? ? ? 35? ? ? ? ? ? ? 7
.............? ? 6
Notice that between ID 4 and ID 5 there is a gap, that instrument was
turned off. It started automatically in 2013-01-01 03:00:45 and continues
till the next turn off.
What I need R to do, is to find these gaps, and insert missing time values
with empty Data lines to have no gaps in time and empty (or NA) values in
data place:
ID? ? TIME? ? ? ? ? ? ? ? ? ? ? ? Data1? ? ? ? Data2? ? ? ? Data3
........... Data700
1.? ? 2013-01-01 00:00:00? ? ? 34? ? ? ? ? ? ? ? 53? ? ? ? ? ? 66
............? ? 55
2.? ? 2013-01-01 00:00:01? ? ? 333? ? ? ? ? ? ? 4? ? ? ? ? ? ? 5
............? ? 50
3.? ? 2013-01-01 00:00:02? ? ? and so on
4.? ? 2013-01-01 00:00:03? ? ? and so on
5.? ? 2013-01-01
00:00:04
<- time with empty (or NA) data values inserted
6.? ? 2013-01-01
00:00:05
<- time with empty (or NA) data values inserted
7.? ? 2013-01-01
00:00:06
<- time with empty (or NA) data values inserted
................
and so on, until the time when instrument works again:
? ? ? 2013-01-01 03:00:45? ? ? 44? ? ? ? ? ? ? ? 33? ? ? ? ? ? ? 4
.............? ? 66
? ? ? 2013-01-01 03:00:46? ? ? 46? ? ? ? ? ? ? ? 35? ? ? ? ? ? ? 7
.............? ? 6
All your suggestions will be appreciated!
Simonas Kecorius
**
??? [[alternative HTML version deleted]]
______________________________________________
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,
Just to add:
As you have more columns of data, it would be better to do in this way:
?Time1<-as.POSIXct("2013-01-01 00:00:00")
?tseq<-seq(Time1,length.out=50,by="secs")
dat2<-data.frame(TIME=tseq,matrix(NA,nrow=50,ncol=3)) #change nrow and ncol
names(dat2)[-1]<-paste("Data",1:3,sep="")
dat3<-read.table(text="
ID??? Date TIME??????????????????????? Data1??????? Data2??????? Data3
1??? 2013-01-01 00:00:00????? 34??????????????? 53??????????? 66
2??? 2013-01-01 00:00:01????? 333????????????? 4????????????? 5
3?? 2013-01-01 00:00:05????? 333????????????? 4????????????? 5
4??? 2013-01-01 00:00:20????? 34??????????????? 63??????????? 66
5??? 2013-01-01 00:00:25????? 433????????????? 5???????????? 8
6?? 2013-01-01 00:00:26????? 533????????????? 8????????????? 5
",sep="",stringsAsFactors=FALSE,header=TRUE)
?dat4<-data.frame(TIME=as.POSIXct(paste(dat3$Date,dat3$TIME)),dat3[,4:6])
dat2[,-1][dat2[,1]%in%dat4[,1],]<-dat4[,-1]
?head(dat2)
#???????????????? TIME Data1 Data2 Data3
#1 2013-01-01 00:00:00??? 34??? 53??? 66
#2 2013-01-01 00:00:01?? 333???? 4???? 5
#3 2013-01-01 00:00:02??? NA??? NA??? NA
#4 2013-01-01 00:00:03??? NA??? NA??? NA
#5 2013-01-01 00:00:04??? NA??? NA??? NA
#6 2013-01-01 00:00:05?? 333???? 4???? 5
A.K.
----- Original Message -----
From: Simonas Kecorius <simolas2008 at gmail.com>
To: r-help at r-project.org
Cc:
Sent: Saturday, January 5, 2013 8:54 AM
Subject: [R] Need help with time series data
Dear R users,
Could you share your knowledge on following problem:
Suppose we have dataframe:
ID? ? TIME? ? ? ? ? ? ? ? ? ? ? ? Data1? ? ? ? Data2? ? ? ? Data3
........... Data700
1.? ? 2013-01-01 00:00:00? ? ? 34? ? ? ? ? ? ? ? 53? ? ? ? ? ? 66
............? ? 55
2.? ? 2013-01-01 00:00:01? ? ? 333? ? ? ? ? ? ? 4? ? ? ? ? ? ? 5
............? ? 50
3.? ? 2013-01-01 00:00:02? ? ? and so on
4.? ? 2013-01-01 00:00:03? ? ? and so on
5.? ? 2013-01-01 03:00:45? ? ? 44? ? ? ? ? ? ? ? 33? ? ? ? ? ? ? 4
.............? ? 66
6.? ? 2013-01-01 03:00:46? ? ? 46? ? ? ? ? ? ? ? 35? ? ? ? ? ? ? 7
.............? ? 6
Notice that between ID 4 and ID 5 there is a gap, that instrument was
turned off. It started automatically in 2013-01-01 03:00:45 and continues
till the next turn off.
What I need R to do, is to find these gaps, and insert missing time values
with empty Data lines to have no gaps in time and empty (or NA) values in
data place:
ID? ? TIME? ? ? ? ? ? ? ? ? ? ? ? Data1? ? ? ? Data2? ? ? ? Data3
........... Data700
1.? ? 2013-01-01 00:00:00? ? ? 34? ? ? ? ? ? ? ? 53? ? ? ? ? ? 66
............? ? 55
2.? ? 2013-01-01 00:00:01? ? ? 333? ? ? ? ? ? ? 4? ? ? ? ? ? ? 5
............? ? 50
3.? ? 2013-01-01 00:00:02? ? ? and so on
4.? ? 2013-01-01 00:00:03? ? ? and so on
5.? ? 2013-01-01
00:00:04
<- time with empty (or NA) data values inserted
6.? ? 2013-01-01
00:00:05
<- time with empty (or NA) data values inserted
7.? ? 2013-01-01
00:00:06
<- time with empty (or NA) data values inserted
................
and so on, until the time when instrument works again:
? ? ? 2013-01-01 03:00:45? ? ? 44? ? ? ? ? ? ? ? 33? ? ? ? ? ? ? 4
.............? ? 66
? ? ? 2013-01-01 03:00:46? ? ? 46? ? ? ? ? ? ? ? 35? ? ? ? ? ? ? 7
.............? ? 6
All your suggestions will be appreciated!
Simonas Kecorius
**
??? [[alternative HTML version deleted]]
______________________________________________
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.