CODEWITHSUNDEEP
rmatrix
user2588829
user2588829

Reputation: 1583

populate matrix full of times with 0s?

I currently have a matrix as below:

mat
   00:00   1
   00:05   2
   00:07   4
   etc...

that contains the times of day at which certain events of interest happen. However, I want the table to be populated at every minute of this matrix, resulting in a matrix like this:

mat
   00:00   1
   00:01   0
   00:02   0
   00:03   0
   00:04   0
   00:05   2
   00:06   0
   00:07   4
   etc...

Is there an easy way to do this in R?

For your information, `dput(head(mat))' returns:

structure(list(Var1 = structure(1:6, .Label = c("00:00", "00:05", "00:07", "05:52", "16:28", "19:34", "19:55", "20:01", "20:40", "23:29", "23:56"), class = "factor"), Freq = c(1L, 2L, 4L, 1L, 1L, 1L)), .Names = c("Var1", "Freq"), row.names = c(NA, 6L), class = "data.frame")

Upvotes: 0

Views: 59

Answers (3)

agstudy
agstudy

Reputation: 121588

The idea is to use a merge with a sequence of dates. But I think the difficulty here is to create a sequence of times.

Here a way :

seq <- seq.POSIXt(as.POSIXct("2000/1/1"), length.out=10,
          by = as.difftime(1,unit='mins'))
dt2 <- data.frame(V1=format(seq,'%H:%M'))
   V1
1  00:00
2  00:01
3  00:02
4  00:03
5  00:04
6  00:05
7  00:06
8  00:07
9  00:08
10 00:09

Then using merge

dt1<- read.table(text='00:00   1
00:05   2
00:07   4')
res <- merge(dt1,dt2,all.y=TRUE)
          V1 V2
1  00:00  1
2  00:05  2
3  00:07  4
4  00:01 NA
5  00:02 NA
6  00:03 NA
7  00:04 NA
8  00:06 NA
9  00:08 NA
10 00:09 NA

EDIT after OP data clarification:

# dat
# Var1 Freq
# 1 00:00    1
# 2 00:05    2
# 3 00:07    4
# 4 05:52    1
# 5 16:28    1
# 6 19:34    1
##  convert factor to string , better to get ordered merge
dat$Var1 <- as.character(dat$Var1)
## compute number of minutes 
Nmins <- difftime(as.POSIXct(tail(dat$Var1,1),format='%H:%M'),
                  as.POSIXct(head(dat$Var1,1),format='%H:%M'),units='mins')
## create sequence
seq <- seq.POSIXt(as.POSIXct("2000/1/1"), length.out=as.numeric(Nmins),
                  by = as.difftime(1,unit='mins'))
dt2 <- data.frame(V1=format(seq,'%H:%M'))

merge(dat,dt2,all.y=TRUE,by.x='Var1',by.y='V1')

Upvotes: 2

Doctor Dan
Doctor Dan

Reputation: 771

My 2 cents' worth using zoo: 

aaa<-zoo(c(1,2,4), times(c("0:00:00", "0:05:00", "0:07:00")))
aa1<-seq(min(time(aaa)), max(time(aaa)), by=times("00:01:00"))
aa2<-zoo(rep(0, length(aa1)), aa1)
aa3<-merge(aaa,aa2)
aa4<-aa3$aaa+aa3$aa2
aa4[is.na(aa4)] <- 0

Not too pretty, but seems to work...

Upvotes: 0

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193597

There are undoubtedly better ways to do this, but here is one simple tool that comes to mind: merge.

Here, I'm merging on character vectors, but I would suggest that you work with actual time formats if you're really dealing with time:

# Something that resembles your input data.frame
df1 <- data.frame(V1 = c("00:00", "00:05", "00:07"), 
                  V2 = c(1, 2, 4), stringsAsFactors = FALSE)
# Another data.frame of just a single column of the complete time range
df2 <- data.frame(V1 = sprintf("00:%02d", 0:10), 
                  stringsAsFactors = FALSE)


# Now, merge them
out <- merge(df1, df2, all.y = TRUE, sort = TRUE)
# Replace NA with 0
out[is.na(out)] <- 0

# View your result
out
#       V1 V2
# 1  00:00  1
# 2  00:01  0
# 3  00:02  0
# 4  00:03  0
# 5  00:04  0
# 6  00:05  2
# 7  00:06  0
# 8  00:07  4
# 9  00:08  0
# 10 00:09  0
# 11 00:10  0

Upvotes: 2

Related Questions