Interpolating Mid-Year Averages

user_14609332 0
Viewed 45

I have yearly observations of income for a series of geographies, like this:

library(dplyr)
library(lubridate)
date <- c("2004-01-01", "2005-01-01", "2006-01-01", 
          "2004-01-01", "2005-01-01", "2006-01-01")
geo <- c(1, 1, 1, 2, 2, 2)
inc <- c(10, 12, 14, 32, 34, 50)
data <- tibble(date = ymd(date), geo, inc)

  date        geo   inc
  <date>     <dbl> <dbl>
1 2004-01-01     1    10
2 2005-01-01     1    12
3 2006-01-01     1    14
4 2004-01-01     2    32
5 2005-01-01     2    34
6 2006-01-01     2    50

I need to insert mid-year values, as averages of the start-of-year and end-of-year observations, so that the data is every 6 months. The outcome would like this:

2004-01-01     1    10
2004-06-01     1    11
2005-01-01     1    12
2004-06-01     1    13
2006-01-01     1    14
2004-01-01     2    32
2004-06-01     2    33
2005-01-01     2    34
2004-06-01     2    42
2006-01-01     2    50

Would appreciate any ideas.

dplyr interpolation r
2 Answers

Grouped by 'geoo', add (+) the 'inc' with the next value (lead) and get the average (/2), as well as add 5 months to the 'date', then filter out the NA elements in 'inc', bind the rows with the original data

library(dplyr)
library(lubridate)
data %>% 
    group_by(geo) %>% 
    summarise(date = date %m+% months(5),
              inc = (inc + lead(inc))/2, .groups = 'drop') %>%
    filter(!is.na(inc)) %>%
    bind_rows(data, .) %>% 
    arrange(geo, date)

-output

# A tibble: 10 x 3
#   date         geo   inc
#   <date>     <dbl> <dbl>
# 1 2004-01-01     1    10
# 2 2004-06-01     1    11
# 3 2005-01-01     1    12
# 4 2005-06-01     1    13
# 5 2006-01-01     1    14
# 6 2004-01-01     2    32
# 7 2004-06-01     2    33
# 8 2005-01-01     2    34
# 9 2005-06-01     2    42
#10 2006-01-01     2    50
user_37322710

You can use complete to create a sequence of dates for 6 months and then use na.approx to fill the NA values with interpolated values.

library(dplyr)
library(lubridate)

data %>%
  group_by(geo) %>%
  tidyr::complete(date = seq(min(date), max(date), by = '6 months')) %>%
  mutate(date = if_else(is.na(inc), date %m-% months(1), date), 
         inc = zoo::na.approx(inc))

#    geo date         inc
#   <dbl> <date>     <dbl>
# 1     1 2004-01-01    10
# 2     1 2004-06-01    11
# 3     1 2005-01-01    12
# 4     1 2005-06-01    13
# 5     1 2006-01-01    14
# 6     2 2004-01-01    32
# 7     2 2004-06-01    33
# 8     2 2005-01-01    34
# 9     2 2005-06-01    42
#10     2 2006-01-01    50
user_39629140
Related