Compare Values Across Two Dataframes R

Question

I am building a crime report in R and am comparing two separate dataframes, one from the current year and one from the previous year. The data structure is the same in both. Is there a way to color the values in a flextable based on the crimes that were committed the previous year? So, for example, if the month of January 2020 had more homicides than January 2019 then color that value red. If the month of January 2020 had less burglaries than January 2019 then color that value green, and so on for every month of the year and for every crime. Here is a sample of the data:

df2019 <- data.frame(crime = c("assault", "homicide", "burglary"),
                 Jan = c(5, 2, 7),
                 Feb = c(2, 4, 0),
                 Mar = c(1, 2, 1))

df2020 <- data.frame(crime = c("assault", "homicide", "burglary"),
                 Jan = c(1, 2, 5),
                 Feb = c(1, 3, 0),
                 Mar = c(2, 2, 1))

My desired output is the to have the df2020 values colored based on the df2019 values (I have included a picture below). I would then like to include the table in a Powerpoint using the Officer package.

Does anyone have any ideas? I have been exploring options in kable, kableExtra, and flextable but can't find any solutions that work across dataframes. Thanks for the help!

Answer 1

Here is a solution:

library(flextable)
library(magrittr)

df2019 <- data.frame(crime = c("assault", "homicide", "burglary"),
                     Jan = c(5, 2, 7),
                     Feb = c(2, 4, 0),
                     Mar = c(1, 2, 1))

df2020 <- data.frame(crime = c("assault", "homicide", "burglary"),
                     Jan = c(1, 2, 5),
                     Feb = c(1, 3, 0),
                     Mar = c(2, 2, 1))

colors <- unlist(df2020[-1] - df2019[-1]) %>% 
  cut(breaks = c(-Inf, -.1, 0.1, Inf),
      labels = c("green", "transparent", "red")) %>% 
  as.character()

flextable(df2020) %>% 
  bg(j = ~ . -crime, bg = colors) %>% 
  theme_vanilla() %>% 
  autofit() %>% save_as_pptx(path = "test.pptx")