0

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.

enter image description here

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!

Logowilliams
  • 98
  • 8

1 Answers1

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")

enter image description here

David Gohel
  • 9,180
  • 2
  • 16
  • 34
  • Thanks for your quick response, David! I copy and pasted your solution and although it compiles the data nicely in a flextable, the colors don't work on my end. Any idea why? – Logowilliams Nov 25 '20 at 22:20
  • no. Did you open the file with PowerPoint (no LibreOffice, or other rich text editor)? – David Gohel Nov 25 '20 at 22:28
  • Yes I opened it with powerpoint. The flextable is the same as yours only without the colors. I copy and pasted your solution so I wonder why thats happening – Logowilliams Nov 25 '20 at 22:34
  • It looks like the reason the colors weren't showing up was the `theme_vanilla()` line of code. When I take that out it runs perfectly! Thank you so much, David! – Logowilliams Nov 25 '20 at 22:41
  • I changed theme_vanilla, I may have changed from white background to transparent background, with a recent version, you should have no problem – David Gohel Nov 26 '20 at 08:22