How to insert zeros in a data frame of R

Question

I have following data.frame, DF DF is already in R. we Do not need to load it in r using read.csv or something

            timeStamp count
1 2014-01-15 14:30:00     2
2 2014-01-15 16:30:00     3
3 2014-01-15 17:00:00     2
4 2014-01-15 17:15:00     1

I have an "independent seq of timestamps", say tmpSeq from 2014-01-15 14:00:00 to 2014-01-22 13:00:00. I want to get a List of counts from this data.frame and insert zeros for timeStamp not present in data.frame but in the tmpSeq

You should provide a reproducible example and show what you have so far tried. — agstudy, May 01 '14 at 10:13

score 1 · Answer 1 · answered May 01 '14 at 10:14

Assuming your sequence is in 15 minute increments:

DF <- data.frame(timeStamp=as.POSIXct(c("2014-01-15 14:30:00","2014-01-15 16:30:00",
                                        "2014-01-15 17:00:00","2014-01-15 17:15:00")),
                 count=c(2,3,2,1))


tmpSeq <- seq(as.POSIXct("2014-01-15 14:00:00"),
              as.POSIXct("2014-01-22 13:00:00"), by="15 mins")

DF <- merge(DF, data.frame(timeStamp=tmpSeq, count=0), all=TRUE)

should do it.

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

It seems what you are looking for is a 'merge'. Look at this post: How to join (merge) data frames (inner, outer, left, right)?

You need a right outer join ( if you make tmpSeq as your right data frame)

Edit: Adding the merge statement in the answer to make the answer clearer :

Right outer: merge(x = DF, y = data.frame(timeStamp=tmpSeq, count=0), all.y=TRUE)

edited May 23 '17 at 12:24

Community

1
1

answered May 01 '14 at 10:15

user3585718

396
1
4

agstudy · Answer 3 · 2014-05-01T10:41:42.253

Generally, it is better to work with some ts packages when you deal with time series objects. Using xts package you can use rbind to merge 2 times series.

First I create the short time series
I generate the long ts assuming it is regular ts with 15 mins interval
I merge the 2 series using rbind

Here my code:

library(xts)
dat = as.xts(read.zoo(text='
time Stamp count        ## a small hack here to read your data
1 2014-01-15 14:30:00     2
2 2014-01-15 16:30:00     3
3 2014-01-15 17:00:00     2
4 2014-01-15 17:15:00     1',
         header=TRUE,
         index=1:2,
         format='%Y-%m-%d %H:%M:%S',tz=''))

## generate the long ts
tmpSeq <-
seq.POSIXt(as.POSIXct('2014-01-15 14:00:00'),
    as.POSIXct('2014-01-22 13:00:00'),by = '15 mins')
tmpSeq <- 
xts(x=rep(0,length(tmpSeq)),tmpSeq)

## insert dat values in tmpSeq
rbind(tmpSeq,dat)

How to insert zeros in a data frame of R

3 Answers3