filling missing month in time series with NA

Viewed 728

I have the data set: year-month numbers

2002-01    2.392909
2002-02    2.496800
2002-03    2.341897
2002-04    1.665625
2002-05    2.398261
2003-01    1.765912
2003-03    3.270870
2003-12    2.907812

I need end up with:

2002-01    2.392909
2002-02    2.496800
2002-03    2.341897
2002-04    1.665625
2002-05    2.398261
2002-06    NA
2002-07    NA
2002-08    NA
2002-09    NA
2002-10    NA
2002-11    NA
2002-12    NA
2003-01    1.765912
2003-02    NA
2003-03    3.270870
2002-04    NA
2003-05    NA
2003-06    NA
2003-07    NA
2003-08    NA
2003-09    NA
2003-10    NA
2003-11    NA
2003-12    2.907812

all missing month will fill with NA, till Dec any ideas much appreciated!

2 Answers

Here is one way. It sets up a column of all the year-months and then merges your data into it, so blanks are left where you have no data.

merge(data.frame(V1=paste(rep(2002:2003,each=12), #the years
                          sprintf("%02d",1:12), #the months, padded with zeros
                          sep="-")),
      df, #your dataframe (assumes the first column is "V1")
      all.x=TRUE)

        V1       V2
1  2002-01 2.392909
2  2002-02 2.496800
3  2002-03 2.341897
4  2002-04 1.665625
5  2002-05 2.398261
6  2002-06       NA
7  2002-07       NA
8  2002-08       NA
9  2002-09       NA
10 2002-10       NA
11 2002-11       NA
12 2002-12       NA
13 2003-01 1.765912
14 2003-02       NA
15 2003-03 3.270870
16 2003-04       NA
17 2003-05       NA
18 2003-06       NA
19 2003-07       NA
20 2003-08       NA
21 2003-09       NA
22 2003-10       NA
23 2003-11       NA
24 2003-12 2.907812

In general it is easier to deal with time series if we use a time series representation such as "zoo" or "ts" class in the first place.

library(zoo)

z0 <- read.zoo(DF0, FUN = as.yearmon) # zoo object
tt <- as.ts(z0) # convert to ts class -- this fills in the NAs
zz <- as.zooreg(tt) # convert back to zoo
fortify.zoo(zz) # convert to data.frame

We could just stop after creating tt or zz if a time series object is preferred as the result.

magrittr

This can alternately be expressed as this magrittr pipeline:

library(magrittr)
library(zoo)

DF0 %>%
    read.zoo(FUN = as.yearmon) %>%
    as.ts %>%
    as.zooreg %>%
    fortify.zoo

Note: The input DF0 in reproducible form was assumed to be:

Lines <- "
2002-01    2.392909
2002-02    2.496800
2002-03    2.341897
2002-04    1.665625
2002-05    2.398261
2003-01    1.765912
2003-03    3.270870
2003-12    2.907812"
DF0 <- read.table(text = Lines, header = TRUE)
Related