Replacing missing quarter and missing data per quarter

user_1655567 0
Viewed 401

Background

I've a quarterly data set where certain quarters and corresponding values are missing. The characteristics of the data set are:

  • Each group should have the same number of quarters but in practe quarters are missing
  • For missing quarter values are unknown
    • This is to be resolved by sourcing imputing next available value; for instance, as available via na.locf function

Example data

# Packages
Vectorize(require)(package = c("tidyverse", "zoo", "magrittr"),
                   character.only = TRUE)

# Seed
set.seed(123)

# Dummy data
dta <- data.frame(group = rep(LETTERS[1:5], 10)) %>%
    group_by(group) %>%
    mutate(qrtr = seq(
        from = as.Date("01/01/2012", "%d/%m/%Y"),
        to = as.Date("31/5/2014", "%d/%m/%Y"),
        by = "quarter"
    )) %>%
    ungroup() %>%
    mutate(qrtr = as.yearqtr(qrtr)) %>%
    arrange(group, qrtr) %>%
    mutate(value = sample(1:10, 50, replace = TRUE))

# Remove random rows
dta[sample(1:dim(dta)[1], 10), c(2, 3)] <- NA
dta %<>% na.omit()

Preview

# A tibble: 40 x 3
   group          qrtr value
   <chr> <S3: yearqtr> <int>
 1     A       2012 Q1     3
 2     A       2012 Q2     8
 3     A       2012 Q4     9
 4     A       2013 Q1    10
 5     A       2013 Q3     6
 6     A       2013 Q4     9
 7     A       2014 Q1     6
 8     B       2012 Q1    10
 9     B       2012 Q2     5
10     B       2012 Q3     7
# ... with 30 more rows

Problem

  1. Create add rows within each group where quarter are missing. Total number of quarter is derived from sequence min(qrtr) to max(qrtr), in the context of existing code:

    seq(from = as.Date("01/01/2012", "%d/%m/%Y"),
    to = as.Date("31/5/2014", "%d/%m/%Y"),
    by = "quarter")

  2. First non-missing value should be carried forward for the missing value.

Desired results:

>> dta
# A tibble: 50 x 3
   group          qrtr value
   <chr> <S3: yearqtr> <int>
 1     A       2012 Q1     3
 2     A       2012 Q2     8
 3     A       2012 Q3     8
 4     A       2012 Q4     9
 5     A       2013 Q1    10
 6     A       2013 Q2    10
 7     A       2013 Q3     6
 8     A       2013 Q4     9
 9     A       2014 Q1     6
10     A       2015 Q1     6
# ... with 40 more rows

Proposed approaches

One approach would rely on making use of the expand, in order to convert implicitly missing values to explicitly missing values. This so far creates missing quarters but there is no clear way of creating missing observations for value column for where given quarter is missing.

dta %>%
    # Append mixing quarters
    expand(group, qrtr) %>% 
    left_join(data.frame(qrtr = as.yearqtr(
        seq(
            from = as.Date("01/01/2012", "%d/%m/%Y"),
            to = as.Date("31/5/2014", "%d/%m/%Y"),
            by = "quarter"
        )
    )), by = "qrtr") %>%
    # TODO
    # mutate(value = na.locf(value)) %>% 
    arrange(group, qrtr) -> dta_fixed
dplyr zoo time-series r
2 Answers

You seems to be interested in padr

library(padr)
library(zoo)

#convert to POSIXct as pad() expect it to be like this
dta$qrtr <- as.POSIXct(dta$qrtr,format="%Y %q")
dta %>% 
  pad(group="group") %>% 
  arrange(group, qrtr) %>%
  mutate(qrtr = as.yearqtr(qrtr)) %>%
  na.locf()

output is:

# A tibble: 49 x 3
   group    qrtr value
   <chr>   <chr> <chr>
 1     A 2012 Q1     3
 2     A 2012 Q2     8
 3     A 2012 Q3     8
 4     A 2012 Q4     9
 5     A 2013 Q1    10
 6     A 2013 Q2    10
 7     A 2013 Q3     6
 8     A 2013 Q4     9
 9     A 2014 Q1     6
10     B 2012 Q1    10
# ... with 39 more rows
user_82405610

Use read.zoo to create a multivariate time series z with one column per group; merge that with a zero width series of quarters, run na.locf and then convert that back to long form.

We can omit:

  • the line with the merge if there is no quarter missing from every group -- in the example data in the question this is the case. i.e. for the data in the question we could omit the merge (although if we left it in it would not cause a problem)
  • the last line (the one with fortify.zoo) if we can work with the 10 x 5 multivariate time series z directly, which may actually be more convenient, e.g. library(ggplot); autoplot(z, facet = NULL) + scale_x_yearqtr() or the same without the facet argument will plot it using ggplot2 graphics using 1 or 5 panels.

This does not use any packages that the question is not already using anyways and works directly with the index in the original "yearqtr" class without conversion.

library(zoo)

z <- read.zoo(dat, index = "qrtr", split = "group")
z <- merge(z, zoo(, seq(start(z), end(z), 1/4))
z <- na.locf(z)
fortify.zoo(z, melt = TRUE)

This could alternately be expressed as the following pipeline:

library(dplyr) # or library(magrittr)
library(zoo)

dta %>%
    read.zoo(index = "qrtr", split = "group") %>%
    merge(zoo(, start(z), end(z), 1/4)) %>%
    na.locf %>%
    fortify.zoo(melt = TRUE)

Updates Have added pipeline and made a number of wording improvements and clarifications.

user_5165480
Related