Conditionally formatting a dataframe in R-CodePudding

I have the following dataframe in R. I have created an example of a conditional formatted column which prints "red" if a value in a cell is above it's mean, "green" if it's below its mean and "yellow" if it doesn't meet either of these requirements.

Instead, I would like to tweak my conditional formatting to print "red" if a value in a cell is more than 2 standard deviations from its rolling 3 day mean, "yellow" if its between 1 and 2 standard deviations from the abovementioned rolling mean, and green if its within 1 standard deviation from its rolling mean. I'm having trouble on how I can code this intuitively - any ideas on how to do something like this?

library(tidyverse)

data <- structure(list(Date = structure(c(19282, 19283, 19284, 19285, 
19286, 19289, 19290, 19291, 19292, 19293, 19296, 19297, 19298, 
19299, 19300, 19303), class = "Date"), `US 10 Year` = c(3.824, 
3.881, 3.881, 3.947, 3.943, 4.018, 4.01, 4.007, 4.134, 4.228, 
4.217, 4.242, 4.102, 4.003, 3.919, 3.998)), row.names = c(NA, 
-16L), class = c("tbl_df", "tbl", "data.frame"))

data %>% 
  mutate(Color = case_when(.[,2] > mean(.$`US 10 Year`) ~ "red", 
                           .[,2] < mean(.$`US 10 Year`) ~ "green", TRUE ~ "yellow"))

CodePudding user response：

Using zoo:rollmean:

data %>%
  mutate(Rollmean = zoo::rollmean(`US 10 Year`, 3, fill = `US 10 Year`[1:2]),
         Color = case_when(`US 10 Year` > 2*sd(`US 10 Year`)   Rollmean ~ "red",
                           `US 10 Year` < 1*sd(`US 10 Year`)   Rollmean ~ "green",
                           TRUE ~ "yellow"))