R help - Jan 2010 - zero-fill absent data

Hello,

I have a set of data frames, generated by an SQL query that I am working
with. Because of the way the query was written, zero values for the
dependent variable (V2 in the example) are not recorded. Up until now
this has not been a problem.

I would like to be able to fill all absent data with 0. 

Current state of data (e.g.):
> frame<-as.data.frame(cbind(c(1:2,5:7),c(0.5,0.2,1,1.6,2)))
> frame
  V1  V2
1  1 0.5
2  2 0.2
3  5 1.0
4  6 1.6
5  7 2.0

So that frame returns:

  V1  V2
1  1 0.5
2  2 0.2
3  3 0.0
4  4 0.0
5  5 1.0
6  6 1.6
7  7 2.0


Since absent data may be beyond the last recorded point I'd like to be
able to use a terminating 0 
> frame<-as.data.frame(cbind(c(1:2,5:7,10),c(0.5,0.2,1,1.6,2,0)))
> frame
  V1  V2
1  1 0.5
2  2 0.2
3  5 1.0
4  6 1.6
5  7 2.0
6 10 0.0

So that values 7<V1<10 are zero filled.

Can anyone suggest a method to do this?

thank you for your time.
Dan

Tena koe Dan

On approach - create a fullFrame with all your observations and merge
with the frame:
> frame <- as.data.frame(cbind(c(1:2,5:7,10),c(0.5,0.2,1,1.6,2,0)))
> fullFrame <- as.data.frame(min(frame[,1]):max(frame[,1])) # Create
fullFrame
> fullFrame
   min(frame[, 1]):max(frame[, 1])
1                                1
2                                2
3                                3
4                                4
5                                5
6                                6
7                                7
8                                8
9                                9
10                              10
> fullFrame <- merge(fullFrame, frame, by=1, all.x=T) # Merge with frame
> fullFrame[is.na(fullFrame[,2]),2] <- 0 # Replace NAs with 0s
> fullFrame
   min(frame[, 1]):max(frame[, 1])  V2
1                                1 0.5
2                                2 0.2
3                                3 0.0
4                                4 0.0
5                                5 1.0
6                                6 1.6
7                                7 2.0
8                                8 0.0
9                                9 0.0
10                              10 0.0
  
HTH ...

Peter Alspach
> -----Original Message-----
> From: r-help-bounces at r-project.org 
> [mailto:r-help-bounces at r-project.org] On Behalf Of Dan Kortschak
> Sent: Wednesday, 6 January 2010 11:40 a.m.
> To: r-help at r-project.org
> Subject: [R] zero-fill absent data
> 
> Hello,
> 
> I have a set of data frames, generated by an SQL query that I 
> am working with. Because of the way the query was written, 
> zero values for the dependent variable (V2 in the example) 
> are not recorded. Up until now this has not been a problem.
> 
> I would like to be able to fill all absent data with 0. 
> 
> Current state of data (e.g.):
> > frame<-as.data.frame(cbind(c(1:2,5:7),c(0.5,0.2,1,1.6,2)))
> > frame
>   V1  V2
> 1  1 0.5
> 2  2 0.2
> 3  5 1.0
> 4  6 1.6
> 5  7 2.0
> 
> So that frame returns:
> 
>   V1  V2
> 1  1 0.5
> 2  2 0.2
> 3  3 0.0
> 4  4 0.0
> 5  5 1.0
> 6  6 1.6
> 7  7 2.0
> 
> 
> Since absent data may be beyond the last recorded point I'd 
> like to be able to use a terminating 0 
> 
> > frame<-as.data.frame(cbind(c(1:2,5:7,10),c(0.5,0.2,1,1.6,2,0)))
> > frame
>   V1  V2
> 1  1 0.5
> 2  2 0.2
> 3  5 1.0
> 4  6 1.6
> 5  7 2.0
> 6 10 0.0
> 
> So that values 7<V1<10 are zero filled.
> 
> Can anyone suggest a method to do this?
> 
> thank you for your time.
> Dan
> 
> ______________________________________________
> 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.
>

The zoo package's merge.zoo routines has a fill=0 argument so:  create
an expanded index, ix, and then in the next line create zoo objects
from the data and the expanded index and merge them together.  Finally
in the last line convert back to a data frame.

library(zoo)
ix <- with(frame, seq(min(V1), max(V1)))
z <- with(frame, merge(zoo(V2, V1), zoo(, ix), fill = 0))
data.frame(V1 = time(z), V2 = coredata(z))


On Tue, Jan 5, 2010 at 5:39 PM, Dan Kortschak
<dan.kortschak at adelaide.edu.au> wrote:
> Hello,
>
> I have a set of data frames, generated by an SQL query that I am working
> with. Because of the way the query was written, zero values for the
> dependent variable (V2 in the example) are not recorded. Up until now
> this has not been a problem.
>
> I would like to be able to fill all absent data with 0.
>
> Current state of data (e.g.):
>> frame<-as.data.frame(cbind(c(1:2,5:7),c(0.5,0.2,1,1.6,2)))
>> frame
> ?V1 ?V2
> 1 ?1 0.5
> 2 ?2 0.2
> 3 ?5 1.0
> 4 ?6 1.6
> 5 ?7 2.0
>
> So that frame returns:
>
> ?V1 ?V2
> 1 ?1 0.5
> 2 ?2 0.2
> 3 ?3 0.0
> 4 ?4 0.0
> 5 ?5 1.0
> 6 ?6 1.6
> 7 ?7 2.0
>
>
> Since absent data may be beyond the last recorded point I'd like to be
> able to use a terminating 0
>
>> frame<-as.data.frame(cbind(c(1:2,5:7,10),c(0.5,0.2,1,1.6,2,0)))
>> frame
> ?V1 ?V2
> 1 ?1 0.5
> 2 ?2 0.2
> 3 ?5 1.0
> 4 ?6 1.6
> 5 ?7 2.0
> 6 10 0.0
>
> So that values 7<V1<10 are zero filled.
>
> Can anyone suggest a method to do this?
>
> thank you for your time.
> Dan
>
> ______________________________________________
> 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.
>