How to fill the missing values for a replicated time series data?

user_6209514 0
Viewed 41

I'm trying to filling a replicated time-series data with some missing values, and I have tried serveral methods, but none works.

The data should be like this:

Year   Var
2001   1
2002   2
2003   3
2001   4
2002   5  
2001   6
2003   7

What I want to get is:

Year   Var
2001   1
2002   2
2003   3
2001   4
2002   5 
2003   NA 
2001   6
2002   NA
2003   7

I have tried merge() by first building a dataframe which includes the whole sequence I need.

yearlabel <- data.frame(Year = rep(2001:2003, 3)    
df <- merge(df, yearlabel, all = T)

But the resutls had a number of length(df)*length(yearlabel) rows.

Also, I tried cbind.fill from the rowr package, it just add the NAs at the end of df. If I use

Map(merge, df, yearlabel, by = 'Year', all = T),

it would return:

Error in fix.by(by.x, x) : 'by' must specify a uniquely valid column

Can anyone help me with this problem? Thank you very much!

missing-data r merge
1 Answers

Here is one option with complete. After creating a column 'grp' based on the occurence of 'min' value of "Year", use complete to expand the 'Year' from min to max with seq, arrange the rows based on 'grp' and remove the 'grp' column

library(tidyverse)
df1 %>%
   mutate(grp = cumsum(lag(Year  > lead(Year, default = 
                      last(Year)),default = TRUE))) %>%
   # or in this case, it can be simplified
   #mutate(grp = cumsum(Year == min(Year))) %>%
   complete(Year = min(Year):max(Year), grp) %>%
   arrange(grp) %>%
   select(-grp)
# A tibble: 9 x 2
#   Year   Var
#  <int> <int>
#1  2001     1
#2  2002     2
#3  2003     3
#4  2001     4
#5  2002     5
#6  2003    NA
#7  2001     6
#8  2002    NA
#9  2003     7

data

df1 <- structure(list(Year = c(2001L, 2002L, 2003L, 2001L, 2002L, 2001L, 
 2003L), Var = 1:7), class = "data.frame", row.names = c(NA, -7L
  ))
user_37322710
Related