how to replace missing values with previous year's binned mean

Question

I have a data frame as below p1_bin and f1_bin are calculated by cut function by me with

Bins <- function(x) cut(x, breaks = c(0, seq(1, 1000, by = 5)), labels = 1:200)  
    binned <- as.data.frame (sapply(df[,-1], Bins))
    colnames(binned) <- paste("Bin", colnames(binned), sep = "_")
    df<- cbind(df, binned)

Now how to calculate mean/avg for previous two years and replace in NA values with in that bin

for example :  at row-5 value is NA for p1 and f1 is 30 with corresponding bin 7.. now replace NA with previous 2 years mean for same bin (7) ,i.e 
df
ID year       p1     f1       Bin_p1     Bin_f1    
 1  2013       20     30        5           7
 2  2013       24     29        5           7
 3  2014       10     16        2           3
 4  2014       11     17        2           3
 5  2015       NA     30        NA          7
 6  2016       10     NA        2           NA

df1

ID year       p1     f1       Bin_p1     Bin_f1    
 1  2013       20     30        5           7
 2  2013       24     29        5           7
 3  2014       10     16        2           3
 4  2014       11     17        2           3
 5  2015   **22**     30        NA          7
 6  2016       10  **16.5**     2           NA

Thanks in advance

why is the last observation for f1 16.5. I get `mean(c(30, 16, 17))# [1] 21` i.e. mean for previous 2 years 2014-2015 — akrun, Jun 30 '17 at 09:47

Constantinos · Accepted Answer · 2017-07-10T09:07:45.497

1

I believe the following code produces the desired output. There's probably a much more elegant way than using mean(rev(lag(f1))[1:2]) to get the average of the last two values of f1 but this should do the trick anyway.

library(dplyr)

df %>%
  arrange(year) %>%
  mutate_at(c("p1", "f1"), "as.double") %>%
  group_by(Bin_p1) %>%
  mutate(f1 = ifelse(is.na(f1), mean(rev(lag(f1))[1:2]), f1)) %>%
  group_by(Bin_f1) %>%
  mutate(p1 = ifelse(is.na(p1), mean(rev(lag(p1))[1:2]), p1)) %>%
  ungroup

and the output is:

# A tibble: 6 x 6
     ID  year    p1    f1 Bin_p1 Bin_f1
  <int> <dbl> <dbl> <dbl>  <dbl>  <dbl>
1     1  2013    20  30.0      5      7
2     2  2013    24  29.0      5      7
3     3  2014    10  16.0      2      3
4     4  2014    11  17.0      2      3
5     5  2015    22  30.0     NA      7
6     6  2016    10  16.5      2     NA

edited Jul 10 '17 at 09:07

answered Jul 03 '17 at 07:43

Constantinos

1,327
7
17

Thanks constantinos i am getting error as ""Error in mutate_impl(.data, dots) : incompatible types, expecting a numeric vector"" – Srm Murty Jul 10 '17 at 08:15
I believe that's because `p1` and `f1` are stored as `integers` (in the simple example that you have). I've added a line which forces them to be stored as `double` which should solve your problem. – Constantinos Jul 10 '17 at 09:07
Thanks iwe can solve the issue by adding as.numeric before ifelse – Srm Murty Jul 11 '17 at 04:24

how to replace missing values with previous year's binned mean

1 Answers1