Inserting missing records into one data frame from another data frame of a different size - vectorized solution?

Question

I'll start by saying that filling in missing data in one data frame with info from another has one solution that may work for my problem. However, it solves it with a FOR loop, and I would prefer a vectorized solution.

I have 125 years of climate data with year, month, temperature, precipitation, and open pan evaporation. It is daily data summarized by month. Some years in the late 1800's have entire months missing, and I would like to substitute those missing months with its equivalent month from a 30-year average around that time.

I have pasted some of the code I've been playing with, below:

# For simplicity, let's pretend there are 5 months in the year, so year 3 
# is the only year with a complete set of data, years 1 and 2 are missing some.

df1<-structure(
  list(
    Year=c(1,1,1,2,2,3,3,3,3,3),
    Month=c(1,2,4,2,5,1,2,3,4,5),
    Temp=c(-2,2,10,-4,12,2,4,8,14,16),
    Precip=c(20,10,50,10,60,26,18,40,60,46),
    Evap=c(2,6,30,4,48,4,10,32,70,40)
  )
)


# This represents the 30-year average data:

df2<-structure(
  list(
    Month=c(1,2,3,4,5),
    Temp=c(1,3,9,13,15),
    Precip=c(11,13,21,43,35),
    Evap=c(1,5,13,35,45)
  )
)

# to match my actual setup
df1<-as_tibble(df1)
df2<-as_tibble(df2)

# I can get to the list of months missing from a given year
full_year <- df2[,1]
compare_year1 <- df1[df1$Year==1,2]
missing_months <- setdiff(full_year,compare_year1)

# Or I can get the full data from each year missing one or more months
year_full <- df2[,1]
years_compare <- split(df1[,c(2)], df1$Year)
years_missing_months <- names(years_compare[sapply(years_compare,nrow)<5])
complete_years_missing_months <- df1[df1$Year %in% years_missing_months,]

This is where I've gotten stumped.

I've looked at anti_join and merge, but it looks like they need data of the same length in each frame. I can get from lists grouped by year to identify the years that are missing months, but I'm not sure how to actually get the rows inserted from there. It seems like lapply could be useful, but the answer ain't comin'.

Thanks in advance.

Edit 7/19: As an illustration of what I need, just looking at year "1", the current data (df1) has the following:
Year | Mon | Temp | Precip | Evap
1 | 1 | -2 | 20 | 2
1 | 2 | 2 | 10 | 6
1 | 4 | 10 | 50 | 30

Months 3 and 5 are missing data, so I would like to insert the equivalent-month data from the 30-year average table (df2), so the final result for year "1" would look like:
Year | Mon | Temp | Precip | Evap
1 | 1 | -2 | 20 | 2
1 | 2 | 2 | 10 | 6
1 | 3 | 9 | 21 | 13
1 | 4 | 10 | 50 | 30
1 | 5 | 15 | 35 | 45

Then fill in every year missing months in like manner. Year "3" would have no change, because (in this 5-month example) there are no months missing data.

I'm not sure what you're trying to do. Sounds like some form of join to me (you don't need data of the same length). Can you edit you post to include your expected output based on the sample data? — Maurits Evers
Thank you @Maurits. I just added what output should look like for the first year. — E Maas

Hack-R Hack-R · Accepted Answer · 2018-07-18T21:52:27

First just add rows to hold the imputed values, since you know that there are missing rows with known dates:

df1$date <- as.Date(paste0("200",df1$Year,"/",df1$Month,"/01"))

pretend_12months <- seq(min(df1$date),max(df1$date),by = "1 month")
pretend_5months  <- pretend_12months[lubridate::month(pretend_12months) < 6]
pretend_5months  <- data.frame(date=pretend_5months)

new <- merge(df1,
             pretend_5months, 
             by="date", 
             all=TRUE)

new$Year <- ifelse(is.na(new$Year),
                   substr(lubridate::year(new$date),4,4),
                   new$Year)
new$Month <- ifelse(is.na(new$Month),
                    lubridate::month(new$date),
                   new$Month)

Impute the NA values using a left join:

# key part: left join using any library or builtin method (left_join,merge, etc)
fillin <- sqldf::sqldf("select a.date,a.Year,a.Month, b.Temp, b.Precip, b.Evap from new a left join df2 b on a.Month = b.Month")

# apply data set from join to the NA data
new$Temp[is.na(new$Temp)]     <- fillin$Temp[is.na(new$Temp)]
new$Precip[is.na(new$Precip)] <- fillin$Precip[is.na(new$Precip)]
new$Evap[is.na(new$Evap)]     <- fillin$Evap[is.na(new$Evap)]

         date Year Month Temp Precip Evap
1  2001-01-01    1     1   -2     20    2
2  2001-02-01    1     2    2     10    6
3  2001-03-01    1     3    9     21    9
4  2001-04-01    1     4   10     50   30
5  2001-05-01    1     5   15     35   15
6  2002-01-01    2     1    1     11    1
7  2002-02-01    2     2   -4     10    4
8  2002-03-01    2     3    9     21    9
9  2002-04-01    2     4   13     43   13
10 2002-05-01    2     5   12     60   48
11 2003-01-01    3     1    2     26    4
12 2003-02-01    3     2    4     18   10
13 2003-03-01    3     3    8     40   32
14 2003-04-01    3     4   14     60   70
15 2003-05-01    3     5   16     46   40

Inserting missing records into one data frame from another data frame of a different size - vectorized solution?

1 Answers