Skip to content

zero-fill absent data

3 messages · Dan Kortschak, Peter Alspach, Gabor Grothendieck

Dan Kortschak
# 
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.):
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
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
Peter Alspach
# 
Tena koe Dan

On approach - create a fullFrame with all your observations and merge
with the frame:
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
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
Gabor Grothendieck
# 
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: