Imputations based on grouped results

[regneck]

Hi all!
I have a very similar dataset to one presented below:

M1 <- matrix(rnorm(100),nrow=100)
M2 <- rep(c("2011", "2012", "2013", "2014"), times = 25)
M1 <- as.data.frame(sapply(M1, as.numeric))
M4 <- cbind(M1, M2)
colnames(M4) <- c("value", "year")
M4[2:5,1] <- 0

Now, what I would like to do is to eliminate "0" in the table and replace them with a mean value grouped by a given year.

I try to use the following code:

M4 <- M4 %>% group_by(year) %>% mutate(nc = if_else(M4[,1] == 0,  mean(M4[,1]), M4[,1]))

Unfortunately, I get the following error and I do not know how to solve it using similar solutions. I want to avoid splitting the data frame by year, calculating and then binding it again.

Error: Problem with mutate() column nk.
nk = if_else(M4[, 1] == 0, mean(M4[, 1]), M4[, 1]).
nk must be size 50 or 1, not 200.
The error occurred in group 1: year = "2011".

[StatSteph]

I think a few things are going on and this might work a bit better. I also am guessing you want the group mean without the 0s so I've chosen to not include those in the means.

library(tidyverse)
M1 <- matrix(rnorm(100),nrow=100)
M2 <- rep(c("2011", "2012", "2013", "2014"), times = 25)
M1 <- as.data.frame(sapply(M1, as.numeric))
M4 <- cbind(M1, M2)
colnames(M4) <- c("value", "year")
M4[2:5,1] <- 0

M4_imp <- M4 %>% 
  group_by(year) %>%
  mutate(
    value_temp=if_else(value==0, NA_real_, value),
    group_mean=mean(value_temp, na.rm=TRUE),
    nc = if_else(is.na(value_temp),  group_mean, value)) %>%
  ungroup()

M4_imp
#> # A tibble: 100 x 5
#>      value year  value_temp group_mean        nc
#>      <dbl> <chr>      <dbl>      <dbl>     <dbl>
#>  1  0.0448 2011      0.0448  -0.000777  0.0448  
#>  2  0      2012     NA       -0.0562   -0.0562  
#>  3  0      2013     NA       -0.135    -0.135   
#>  4  0      2014     NA       -0.227    -0.227   
#>  5  0      2011     NA       -0.000777 -0.000777
#>  6  0.782  2012      0.782   -0.0562    0.782   
#>  7 -0.688  2013     -0.688   -0.135    -0.688   
#>  8  0.382  2014      0.382   -0.227     0.382   
#>  9 -0.117  2011     -0.117   -0.000777 -0.117   
#> 10  1.54   2012      1.54    -0.0562    1.54    
#> # ... with 90 more rows

^{Created on 2021-11-28 by the reprex package (v2.0.1)}

[regneck]

Thank you very much, StatSteph! That helped. Based on your solution, it looks like it can be even a bit shortened:

M4 <- M4 %>% group_by(year) %>% mutate(nc = if_else(is.na(value),  mean(value, na.rm = TRUE),  value))

[StatSteph]

Almost. With your example, there are no NA values, only 0 values so you probably want something like this:

M4 <- M4 %>% group_by(year) %>%
   mutate(
     value=if_else(value==0, NA_real_, value), #change 0s to NA
     nc = if_else(is.na(value),  mean(value, na.rm = TRUE),  value))