Impute Intermediate Missing Values

user_697363 0
Viewed 242

I've the data a group level. The data look likes the following.

I actual data is the "Value" & the required data is "Expected_Value".

I tried the following code:

setDT(file_to_share)[,Expected_Value := na.locf(na.locf(Value, na.rm=FALSE), fromLast=TRUE),by = c("Group_A",   "Group_B")]

But in this code the imputation is done over entire missing value. I want to calculate the missing value if the missing value is in between the values. And missing value will be the replication of the previous available value.

If someone can guide me how to do it then it will be a great help.

Note : I tried to calculate using data.table and zoo. But any other approach will also do.

data.table zoo r
2 Answers

Even though you are after a data.table solution, here is one using tidyverse methods. (I might try and translate to data.table If time permits).

The idea is to create a grouping variable to capture your weeks as well and fill Value under grouping GroupA, groupB and week(here called grp). We also create a replica of Value to fill fromlast (tidyr terminology is .direction = 'up'). We then create another grouping variable with the cumulative sum of NA values and replace the values in column Value to NA under the assumption that the new group size (Group_A, GROUP_B, grp and grp1) is 1 and its value1 is NA. That gives the expected result.

library(tidyverse)

df2 <- df1 %>% 
  mutate(Date = as.POSIXct(Date, format = '%m/%d/%Y')) %>% 
  mutate(value1 = Value) %>%
  group_by(Group_A, GROUP_B, grp = cumsum(format(Date, '%d')=='01'))%>% 
  fill(Value) %>% 
  fill(value1, .direction = 'up') %>% 
  mutate(grp1 = cumsum(is.na(Value))) %>% 
  group_by(Group_A, GROUP_B, grp, grp1) %>% 
  mutate(new = n(), Value = replace(Value, new == 1 | is.na(value1), NA)) %>%
  ungroup() %>%
  select(-c(value1, grp, grp1, new))

which gives,

# A tibble: 42 × 5
   Group_A   GROUP_B       Date Value Expected_Value
     <chr>     <chr>     <dttm> <int>          <int>
1  GROUP_1 Group_1_1 2017-01-01    NA             NA
2  GROUP_1 Group_1_1 2017-01-02    NA             NA
3  GROUP_1 Group_1_1 2017-01-03    34             34
4  GROUP_1 Group_1_1 2017-01-04    20             20
5  GROUP_1 Group_1_1 2017-01-05    20             20
6  GROUP_1 Group_1_1 2017-01-06    20             20
7  GROUP_1 Group_1_1 2017-01-07    38             38
8  GROUP_1 Group_1_2 2017-01-01    35             35
9  GROUP_1 Group_1_2 2017-01-02    28             28
10 GROUP_1 Group_1_2 2017-01-03    28             28
# ... with 32 more rows

#Where,

identical(df2$Value, df2$Expected_Value)
#[1] TRUE
user_56355800

The OP has requested to fill only NA values which are between other values within each group. This means to skip any sequence of NA values at start or end of each group when applying zoo::na.locf().

With data.table, this can be done by identifying the indices of the rows to be skipped and a kind of anti-join:

library(data.table)
setDT(DT)[!DT[, {
  na_grp <- rleid(is.na(Value))
  .I[na_grp %in% c(1L, max(na_grp))]
}, by = .(Group_A, GROUP_B)]$V1, Value := zoo::na.locf(Value)][]

    Group_A    GROUP_B     Date Value Expected_Value
 1: GROUP_1  Group_1_1 1/1/2017    NA             NA
 2: GROUP_1  Group_1_1 1/2/2017    NA             NA
 3: GROUP_1  Group_1_1 1/3/2017    34             34
 4: GROUP_1  Group_1_1 1/4/2017    20             20
 5: GROUP_1  Group_1_1 1/5/2017    20             20
 6: GROUP_1  Group_1_1 1/6/2017    20             20
 7: GROUP_1  Group_1_1 1/7/2017    38             38
 8: GROUP_1  Group_1_2 1/1/2017    35             35
 9: GROUP_1  Group_1_2 1/2/2017    28             28
10: GROUP_1  Group_1_2 1/3/2017    20             28
11: GROUP_1  Group_1_2 1/4/2017    32             32
12: GROUP_1  Group_1_2 1/5/2017    39             39
13: GROUP_1  Group_1_2 1/6/2017    28             28
14: GROUP_1  Group_1_2 1/7/2017    NA             NA
15: GROUP_2 Group_1_11 1/1/2017    NA             NA
16: GROUP_2 Group_1_11 1/2/2017    NA             NA
17: GROUP_2 Group_1_11 1/3/2017    40             40
18: GROUP_2 Group_1_11 1/4/2017    32             32
19: GROUP_2 Group_1_11 1/5/2017    20             20
20: GROUP_2 Group_1_11 1/6/2017    NA             NA
21: GROUP_2 Group_1_11 1/7/2017    NA             NA
22: GROUP_2 Group_1_21 1/1/2017    NA             NA
23: GROUP_2 Group_1_21 1/2/2017    32             32
24: GROUP_2 Group_1_21 1/3/2017    36             36
25: GROUP_2 Group_1_21 1/4/2017    36             36
26: GROUP_2 Group_1_21 1/5/2017    28             28
27: GROUP_2 Group_1_21 1/6/2017    33             33
28: GROUP_2 Group_1_21 1/7/2017    40             40
29: GROUP_3 Group_1_13 1/1/2017    NA             NA
30: GROUP_3 Group_1_13 1/2/2017    NA             NA
31: GROUP_3 Group_1_13 1/3/2017    NA             NA
32: GROUP_3 Group_1_13 1/4/2017    29             29
33: GROUP_3 Group_1_13 1/5/2017    31             31
34: GROUP_3 Group_1_13 1/6/2017    31             31
35: GROUP_3 Group_1_13 1/7/2017    34             34
36: GROUP_3 Group_1_23 1/1/2017    26             26
37: GROUP_3 Group_1_23 1/2/2017    33             33
38: GROUP_3 Group_1_23 1/3/2017    27             27
39: GROUP_3 Group_1_23 1/4/2017    23             23
40: GROUP_3 Group_1_23 1/5/2017    25             25
41: GROUP_3 Group_1_23 1/6/2017    41             41
42: GROUP_3 Group_1_23 1/7/2017    25             25
    Group_A    GROUP_B     Date Value Expected_Value

Explanation

  • For each group, the streaks of NA / non-NA values are numbered
  • The first and last of these streaks in each group is picked and the indices are retrieved from the special symbol .I. (As Value will be updated in place it doesn't matter whether the first or last streak contains NA or not; they will not be updated anyway.)
  • The found indices
    DT[, {na_grp <- rleid(is.na(Value)); .I[na_grp %in% c(1L, max(na_grp))]}, by = .(Group_A, GROUP_B)]$V1
    are excluded so that zoo::na.locf(Value) is only applied to the "inner" streaks of each group.

Data

DT <- structure(list(Group_A = c("GROUP_1", "GROUP_1", "GROUP_1", "GROUP_1", 
"GROUP_1", "GROUP_1", "GROUP_1", "GROUP_1", "GROUP_1", "GROUP_1", 
"GROUP_1", "GROUP_1", "GROUP_1", "GROUP_1", "GROUP_2", "GROUP_2", 
"GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", 
"GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", "GROUP_2", 
"GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", 
"GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", "GROUP_3", 
"GROUP_3", "GROUP_3"), GROUP_B = c("Group_1_1", "Group_1_1", 
"Group_1_1", "Group_1_1", "Group_1_1", "Group_1_1", "Group_1_1", 
"Group_1_2", "Group_1_2", "Group_1_2", "Group_1_2", "Group_1_2", 
"Group_1_2", "Group_1_2", "Group_1_11", "Group_1_11", "Group_1_11", 
"Group_1_11", "Group_1_11", "Group_1_11", "Group_1_11", "Group_1_21", 
"Group_1_21", "Group_1_21", "Group_1_21", "Group_1_21", "Group_1_21", 
"Group_1_21", "Group_1_13", "Group_1_13", "Group_1_13", "Group_1_13", 
"Group_1_13", "Group_1_13", "Group_1_13", "Group_1_23", "Group_1_23", 
"Group_1_23", "Group_1_23", "Group_1_23", "Group_1_23", "Group_1_23"
), Date = c("1/1/2017", "1/2/2017", "1/3/2017", "1/4/2017", "1/5/2017", 
"1/6/2017", "1/7/2017", "1/1/2017", "1/2/2017", "1/3/2017", "1/4/2017", 
"1/5/2017", "1/6/2017", "1/7/2017", "1/1/2017", "1/2/2017", "1/3/2017", 
"1/4/2017", "1/5/2017", "1/6/2017", "1/7/2017", "1/1/2017", "1/2/2017", 
"1/3/2017", "1/4/2017", "1/5/2017", "1/6/2017", "1/7/2017", "1/1/2017", 
"1/2/2017", "1/3/2017", "1/4/2017", "1/5/2017", "1/6/2017", "1/7/2017", 
"1/1/2017", "1/2/2017", "1/3/2017", "1/4/2017", "1/5/2017", "1/6/2017", 
"1/7/2017"), Value = c(NA, NA, 34L, 20L, NA, NA, 38L, 35L, 28L, 
NA, 32L, 39L, 28L, NA, NA, NA, 40L, 32L, 20L, NA, NA, NA, 32L, 
36L, NA, 28L, 33L, 40L, NA, NA, NA, 29L, 31L, NA, 34L, 26L, 33L, 
27L, 23L, 25L, 41L, 25L), Expected_Value = c(NA, NA, 34L, 20L, 
20L, 20L, 38L, 35L, 28L, 28L, 32L, 39L, 28L, NA, NA, NA, 40L, 
32L, 20L, NA, NA, NA, 32L, 36L, 36L, 28L, 33L, 40L, NA, NA, NA, 
29L, 31L, 31L, 34L, 26L, 33L, 27L, 23L, 25L, 41L, 25L)), .Names = c("Group_A", 
"GROUP_B", "Date", "Value", "Expected_Value"), row.names = c(NA, 
-42L), class = "data.frame")
user_38170040
Related