CODEWITHSUNDEEP
rexcel
G_z
G_z

Reputation: 97

How to fill up missing dates?

I have data that looks like this: 

Date1       Date2       Value2
1/1/1998    1/1/1998    2
1/2/1998    1/2/1998    4
1/3/1998    1/4/1998    6
1/4/1998    1/5/1998    8
1/6/1998    1/6/1998    10
1/7/1998    1/7/1998    12
1/8/1998    1/10/1998   14
1/9/1998        
1/10/1998

I want Date2 to match Date1 and fill the missing Value2 with the average of the value2 before it and after it.

My final data would look like this:

Date1       Date2       Value
1/1/1998    1/1/1998    2
1/2/1998    1/2/1998    4
1/3/1998    1/3/1998    5
1/4/1998    1/4/1998    6
1/6/1998    1/6/1998    10
1/7/1998    1/7/1998    12
1/8/1998    1/8/1998    13
1/9/1998    1/9/1998    13
1/10/1998   1/10/1998   14

Upvotes: 0

Views: 70

Answers (1)

Roland
Roland

Reputation: 132706

Import your data into R (usually you would read from two files):

DF1 <- read.table(text = "Date1      
                  1/1/1998    
                  1/2/1998   
                  1/3/1998    
                  1/4/1998   
                  1/6/1998   
                  1/7/1998   
                  1/8/1998    
                  1/9/1998        
                  1/10/1998", header = TRUE)

DF2 <- read.table(text = "Date2       Value2
                  1/1/1998    2
                  1/2/1998    4
                  1/4/1998    6
                  1/5/1998    8
                  1/6/1998    10
                  1/7/1998    12
                  1/10/1998   14", header = TRUE)

Merge the data.frames:

DF <- merge(DF1, DF2, by.x = "Date1", by.y = "Date2", all.x = TRUE)
DF$Date1 <- as.Date(DF$Date1, format = "%m/%d/%Y") #parse dates
DF <- DF[order(DF$Date1),] #order the data.frame

Fill in the mean values:

library(zoo)
DF$Value2 <-  na.approx(DF$Value2, method = "constant", f = 0.5)

#        Date1 Value2
# 1 1998-01-01      2
# 3 1998-01-02      4
# 4 1998-01-03      5
# 5 1998-01-04      6
# 6 1998-01-06     10
# 7 1998-01-07     12
# 8 1998-01-08     13
# 9 1998-01-09     13
# 2 1998-01-10     14

Upvotes: 5

Related Questions