Formattable based on multiple columns

2019-04-12 00:35发布

问题:

I am using the package formattable to generate a formatted table. I found a nice resource Formatting tables in R. But here the example of arrow formatting is based only on the specific column.

My requirement is: Say I have from different cities, column A and B, where A has factors with two levels 'Number of Trees' and 'Pollution', while B is the percentage change of these YoY. So I want to format the Column B with a positive green arrow if there has an increase for Column A values being 'Number of Trees' (since it is positive) and red if there is a decrease while for pollution the other way round.

So taking the example (credit: Markus Gesmann) from the link itself, say just for IBM even if the change is <0, I want to show a positive green arrow against it.

library(formattable)
DF <- data.frame(Ticker=c("", "", "", "IBM", "AAPL", "MSFT"),
             Name=c("Dow Jones", "S&P 500", "Technology", 
                    "IBM", "Apple", "Microsoft"),
             Value=accounting(c(15988.08, 1880.33, NA, 
                                130.00, 97.05, 50.99)),
             Change=percent(c(-0.0239, -0.0216, 0.021, 
                              -0.0219, -0.0248, -0.0399)))
DF
##   Ticker       Name     Value Change
## 1         Dow Jones 15,988.08 -2.39%
## 2           S&P 500  1,880.33 -2.16%
## 3        Technology        NA  2.10%
## 4    IBM        IBM    130.00 -2.19%
## 5   AAPL      Apple     97.05 -2.48%
## 6   MSFT  Microsoft     50.99 -3.99%
formattable(DF, list(
  Name=formatter(
    "span",
    style = x ~ ifelse(x == "Technology", 
                       style(font.weight = "bold"), NA)),
  Value = color_tile("white", "orange"),
  Change = formatter(
    "span",
    style = x ~ style(color = ifelse(x < 0 , "red", "green")),
    x ~ icontext(ifelse(x < 0, "arrow-down", "arrow-up"), x)))
)

回答1:

Based on the doc at ?formatter:

formattable(DF, list(
  Name=formatter(
    "span",
    style = x ~ ifelse(x == "Technology", 
                       style(font.weight = "bold"), NA)),
  Value = color_tile("white", "orange"),
  Change = formatter(
    "span",
    style = ~ style(color = ifelse(Change < 0 & Ticker != "IBM", "red", "green")),
    ~ icontext(ifelse(Change < 0 & Name != "IBM", "arrow-down", "arrow-up"), Change)))
)

Apparently, the x ~ formula style restricts you to use the only the variable itself on the right-hand side. You have to switch to ~ and then write the column name explicitly on the right-hand side (instead of as x).