基于行对的R弹性条件格式 [英] R flextable conditional formatting based on pairs of rows

问题描述

我正在尝试根据标记为Act的行(实际上)中的值是否超过相应行中的值(即相同的KPI)标记为Plan.超出的值应具有绿色背景，而低于Plan的值应具有红色背景.

I am trying to change the bg color of some cells in a flextable based on whether the values in the rows labeled Act (for actual) exceed the values in the corresponding rows (that is, same KPI) labeled Plan. Those that exceed should get a green background, while those values that are below Plan should get a red background.

(在一个完美的世界中，无论单元格是大于还是小于，我都可以更改背景颜色，具体取决于我配置为说出要走哪个方向的列表，但是接下来会来.)

(In a perfect world, I would be able to change the background color whether the cell was greater than or less than, depending upon a list I configured to say which direction to go, but that will come next.)

df <- structure(list(KPI = c("Quality", "Quality", "On Time", "On Time", 
"Attrition", "Attrition", "Growth 1", "Growth 1", "Growth 2", 
"Growth 2", "WCT", "WCT", "ROI", "ROI"), Type = c("Plan", "Act", 
"Plan", "Act", "Plan", "Act", "Plan", "Act", "Plan", "Act", "Plan", 
"Act", "Plan", "Act"), JAN = c(1, 1, NA, NA, 0.05, 0.09, NA, 
NA, NA, NA, 4, -1.8, NA, NA), FEB = c(1, 0.98, NA, NA, 0.05, 
0.08, NA, NA, NA, NA, -0.2, -1.3, NA, NA), MAR = c(1, 1, 0.79, 
0.81, 0.05, 0.08, 0.1, 0.08, 116, 199, -0.7, -0.2, NA, NA), APR = c(1, 
1, NA, NA, 0.05, 0.08, NA, NA, NA, NA, -0.2, -0.3, NA, NA), MAY = c(1, 
1, NA, NA, 0.05, 0.09, NA, NA, NA, NA, -0.2, -0.6, NA, NA), JUN = c(1, 
1, 0.79, 0.8, 0.05, 0.08, 0.12, 0.03, -33, 22, 0.1, 1.1, NA, 
NA), JUL = c(1, 1, NA, NA, 0.05, 0.09, NA, NA, NA, NA, 0.3, 0.2, 
NA, NA), AUG = c(1, 1, NA, NA, 0.05, 0.09, NA, NA, NA, NA, 0.3, 
0.8, NA, NA), SEP = c(1, 1, 0.79, 0.78, 0.05, 0.09, 0.2, 0.14, 
173, 303, 1.5, 2.1, NA, NA), OCT = c(1, NA, NA, NA, 0.05, NA, 
NA, NA, NA, NA, 2.3, NA, NA, NA), NOV = c(1, NA, NA, NA, 0.05, 
NA, NA, NA, NA, NA, 2, NA, NA, NA), DEC = c(1, NA, NA, NA, 0.05, 
NA, NA, NA, NA, NA, 0.2, NA, NA, NA)), row.names = c(NA, -14L
), class = c("tbl_df", "tbl", "data.frame"))

library(regulartable)
library(magrittr)

df %>% regulartable() %>% bg(i = ~ Type %in% "Act", j = 3:14, bg="#cceecc")

其产生的图像如下.我之所以陷入困境，是因为我不知道如何添加第二个条件，即(value > lag(value))位置中的任何内容.有人知道吗，还是我需要先spread和gather?任何帮助将不胜感激.

The image it produces is below. I am currently stuck because I can not figure out how to add a second condition, that is, whatever would go in the (value > lag(value)) position. Does anyone know, or do I need to spread and gather first? Any help would be greatly appreciated.

df %>% regulartable() %>% bg(i = ~ Type %in% "Act" && (value > lag(value)), j = 3:14, bg="#cceecc")

推荐答案

我敢肯定有一种更优雅的方法可以解决此问题，如果有的话，我希望有人发布它.但与此同时，我发现了一个权宜之计.首先将df分为计划值和实际值，然后使用这些差异来确定每个单元格的合适颜色:

I'm sure there is a more elegant way to solve this, and if there is, I hope someone posts it. But in the meantime I found a stopgap. First split the df into plan values and actual values, and then use those differences to determine appropriate color for each cell:

library(gdata)

df %>% group_split(Type) -> plan.act
ifelse(plan.act[[1]][,3:14]-plan.act[[2]][,3:14]>=0, "#cceecc", "#eecccc") -> colorgrid 

这会为每个单元格创建一个红色/绿色列表:

This creates a list of red/green colors for each cell:

> colorgrid
     JAN       FEB       MAR       APR       MAY       JUN       JUL       AUG       SEP       OCT NOV DEC
[1,] "#cceecc" "#eecccc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" NA  NA  NA 
[2,] NA        NA        "#cceecc" NA        NA        "#cceecc" NA        NA        "#eecccc" NA  NA  NA 
[3,] "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" "#cceecc" NA  NA  NA 
[4,] NA        NA        "#eecccc" NA        NA        "#eecccc" NA        NA        "#eecccc" NA  NA  NA 
[5,] NA        NA        "#cceecc" NA        NA        "#cceecc" NA        NA        "#cceecc" NA  NA  NA 
[6,] "#eecccc" "#eecccc" "#cceecc" "#eecccc" "#eecccc" "#cceecc" "#eecccc" "#cceecc" "#cceecc" NA  NA  NA 
[7,] NA        NA        NA        NA        NA        NA        NA        NA        NA        NA  NA  NA 

现在为Plan组的颜色创建另一个df，然后绘制表格:

Now create another df for colors for the Plan group, then plot the table:

blankgrid <- colorgrid
blankgrid[!is.na(blankgrid)] <- NA_character_

df %>% regulartable() %>% bg(j = 3:14, bg=interleave(blankgrid,colorgrid))

然后，您可以在其中添加更多弹性表，以使表更漂亮:

And from there you can add more flextable goodness to make table prettier:

df %>% regulartable() %>% bg(j = 3:14, bg=interleave(blankgrid,colorgrid)) %>%
     merge_v(j=1) %>% border_inner_v(border = fp_border(color="gray", width=1))

这篇关于基于行对的R弹性条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！