In R , is there any avaiable funcation like IFERROR formula in EXCEL

Question

In R , is there any available function like IFERROR formula in EXCEL ? I want to calculate moving average using 4 nearest figures, but if the figures less than 4 in the group then using normal average. Detail refer to below code, the IF_ERROR is just i wished function and can't work

library(tidyverse)
library(TTR)
test_data <- data.frame(category=c('a','a','a','b','b','b','b','b','b'),
             amount=c(1,2,3,4,5,6,7,8,9))

test_data %>% group_by(category) %>% mutate(avg_amount=IF_ERROR(TTR::runMedian(amount,4),
                                                                median(amount),
                                                                TTR::runMedian(amount,4))

See `?tryCatch`. Alternatively, you could use one of dplyr's windowing functions, then run a second `mutate` to catch any NAs left over. — dash2, Feb 08 '22 at 10:39
@dash2 Thanks for your replay . Could you help to show the specific code ? Thanks — anderwyang, Feb 08 '22 at 11:00
I'm not going to write a whole answer, but you want something like `mutate(avg_amount=tryCatch(TTR::runMedian(amount, 4), error = median(amount))` — dash2, Feb 08 '22 at 11:12
@dash2 Whe I run the code , the error message show as below : Error: Problem with `mutate()` column `avg_amount`. i `avg_amount = tryCatch(TTR::runMedian(amount, 4), error = median(amount))`. x attempt to apply non-function i The error occurred in group 1: category = "a". Run `rlang::last_error()` to see where the error occurred. — anderwyang, Feb 08 '22 at 11:32

score 2 · Accepted Answer · answered Feb 08 '22 at 11:27

In general, input should only generate errors in exceptional circumstances. It can be computationally expensive to catch and handle errors where a simple if statement will suffice. The key here is realising that runMedian throws an error if the group size is less than 4. Remember we can check the group size inside mutate by using n(), so all you need do is:

test_data %>% 
  group_by(category) %>% 
  mutate(avg_amount = if(n() > 3) TTR::runMedian(amount, 4) else median(amount))
#> # A tibble: 9 x 3
#> # Groups:   category [2]
#>   category amount avg_amount
#>   <chr>     <dbl>      <dbl>
#> 1 a             1        2  
#> 2 a             2        2  
#> 3 a             3        2  
#> 4 b             4       NA  
#> 5 b             5       NA  
#> 6 b             6       NA  
#> 7 b             7        5.5
#> 8 b             8        6.5
#> 9 b             9        7.5

Additionally, if you want to replace the NA values from the beginning of the running median, you could use ifelse:

test_data %>% 
  group_by(category) %>% 
  mutate(avg_amount = if(n() > 3) TTR::runMedian(amount, 4) else median(amount),
         avg_amount = ifelse(is.na(avg_amount), median(amount), avg_amount))
#> # A tibble: 9 x 3
#> # Groups:   category [2]
#>   category amount avg_amount
#>   <chr>     <dbl>      <dbl>
#> 1 a             1        2  
#> 2 a             2        2  
#> 3 a             3        2  
#> 4 b             4        6.5
#> 5 b             5        6.5
#> 6 b             6        6.5
#> 7 b             7        5.5
#> 8 b             8        6.5
#> 9 b             9        7.5

In R , is there any avaiable funcation like IFERROR formula in EXCEL

1 Answers1