Customize correlation output #286

JanMarvin · 2022-07-29T17:01:56Z

JanMarvin
Jul 29, 2022
Maintainer

Create significance stars to correlation output (asked for in ycphs/openxlsx#374 by @rempsyc).

library(correlation)
library(openxlsx2)

# create correlation matrix with p values
cm <- mtcars %>% correlation::correlation() %>% summary(redundant = TRUE)

# prepare the data. the values first
df <- as.data.frame(cm)
rownames(df) <- df$Parameter
df$Parameter <- NULL

# get the sign for the data frame
sf <- sign(cm[,-1])

# prepare the p values.
pf <- attr(cm, "p")
rownames(pf) <- pf$Parameter
pf$Parameter <- NULL

pf <- as.data.frame(mapply(sf, pf, FUN = "*"))
pf <- cbind(Parameter = cm$Parameter, pf)


## prepare styles for neutral, positive and negative with *, **, and ***. NumFmts that contain whitespaces of a specific
## width are added as underscore followed by the character `_*` .

# neutral (bad actually p == 0)
yellow_f <- c(rgb = "FF9C6500")
yellow_b <- c(rgb = "FFFFEB9C")

yel_style <- create_dxfs_style(font_color = yellow_f, bg_fill= yellow_b, num_fmt = "#.##0 _*_*_*")

# positive
green_f <- c(rgb = "FF006100")
green_b <- c(rgb = "FFC6EFCE")

gre_style    <- create_dxfs_style(font_color = green_f, bg_fill = green_b, num_fmt = "#.##0 _*_*_*")
gre_style_p1 <- create_dxfs_style(font_color = green_f, bg_fill = green_b, num_fmt = "#.##0 \\*_*_*")
gre_style_p2 <- create_dxfs_style(font_color = green_f, bg_fill = green_b, num_fmt = "#.##0 \\*\\*_*")
gre_style_p3 <- create_dxfs_style(font_color = green_f, bg_fill = green_b, num_fmt = "#.##0 \\*\\*\\*")


# negative
red_f <- c(rgb = "FF9C0006")
red_b <- c(rgb = "FFFFC7CE")

red_style    <- create_dxfs_style(font_color = red_f, bg_fill = red_b, num_fmt = "#.##0 _*_*_*")
red_style_p1 <- create_dxfs_style(font_color = red_f, bg_fill = red_b, num_fmt = "#.##0 \\*_*_*")
red_style_p2 <- create_dxfs_style(font_color = red_f, bg_fill = red_b, num_fmt = "#.##0 \\*\\*_*")
red_style_p3 <- create_dxfs_style(font_color = red_f, bg_fill = red_b, num_fmt = "#.##0 \\*\\*\\*")


# create openxlsx2 workbook
wb <- wb_workbook()

# assign all the required styles to the workbook
wb$styles_mgr$add(yel_style, "yel_style")
wb$styles_mgr$add(gre_style, "gre_style")
wb$styles_mgr$add(red_style, "red_style")

wb$styles_mgr$add(gre_style_p1, "gre_style_p1")
wb$styles_mgr$add(red_style_p1, "red_style_p1")

wb$styles_mgr$add(gre_style_p2, "gre_style_p2")
wb$styles_mgr$add(red_style_p2, "red_style_p2")

wb$styles_mgr$add(gre_style_p3, "gre_style_p3")
wb$styles_mgr$add(red_style_p3, "red_style_p3")


# create the worksheets and write the data to the worksheets. I hide the second, but it does not matter.
wb$add_worksheet("cor")$add_data(x = cm)
wb$add_worksheet("p", visible ="veryhidden")$add_data(x = pf)

# create conditional formatting for the styles
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2=0)", style = "yel_style")
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>.05)", style = "gre_style")
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2<-.05)", style = "red_style")

wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>0, p!B2<=.05)", style = "gre_style_p1")
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>=-.05, p!B2<0)", style = "red_style_p1")

wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>0, p!B2<=.01)", style = "gre_style_p2")
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>=-.01, p!B2<0)", style = "red_style_p2")

wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>0, p!B2<=.001)", style = "gre_style_p3")
wb$add_conditional_formatting("cor", cols = 2:12, rows = 2:12, rule = "AND(p!B2>=-.001, p!B2<0)", style = "red_style_p3")

# open in Excel
wb$open()

This is what it looks like:

For comparison in R it looks like this:

> cm
# Correlation Matrix (pearson-method)

Parameter |      mpg |      cyl |     disp |       hp |     drat |       wt |     qsec |       vs |       am |    gear |    carb
--------------------------------------------------------------------------------------------------------------------------------
mpg       |  1.00*** | -0.85*** | -0.85*** | -0.78*** |  0.68*** | -0.87*** |     0.42 |   0.66** |   0.60** |    0.48 |  -0.55*
cyl       | -0.85*** |  1.00*** |  0.90*** |  0.83*** | -0.70*** |  0.78*** |   -0.59* | -0.81*** |   -0.52* |   -0.49 |   0.53*
disp      | -0.85*** |  0.90*** |  1.00*** |  0.79*** | -0.71*** |  0.89*** |    -0.43 | -0.71*** |   -0.59* |  -0.56* |    0.39
hp        | -0.78*** |  0.83*** |  0.79*** |  1.00*** |    -0.45 |   0.66** | -0.71*** | -0.72*** |    -0.24 |   -0.13 | 0.75***
drat      |  0.68*** | -0.70*** | -0.71*** |    -0.45 |  1.00*** | -0.71*** |     0.09 |     0.44 |  0.71*** | 0.70*** |   -0.09
wt        | -0.87*** |  0.78*** |  0.89*** |   0.66** | -0.71*** |  1.00*** |    -0.17 |   -0.55* | -0.69*** |  -0.58* |    0.43
qsec      |     0.42 |   -0.59* |    -0.43 | -0.71*** |     0.09 |    -0.17 |  1.00*** |  0.74*** |    -0.23 |   -0.21 | -0.66**
vs        |   0.66** | -0.81*** | -0.71*** | -0.72*** |     0.44 |   -0.55* |  0.74*** |  1.00*** |     0.17 |    0.21 |  -0.57*
am        |   0.60** |   -0.52* |   -0.59* |    -0.24 |  0.71*** | -0.69*** |    -0.23 |     0.17 |  1.00*** | 0.79*** |    0.06
gear      |     0.48 |    -0.49 |   -0.56* |    -0.13 |  0.70*** |   -0.58* |    -0.21 |     0.21 |  0.79*** | 1.00*** |    0.27
carb      |   -0.55* |    0.53* |     0.39 |  0.75*** |    -0.09 |     0.43 |  -0.66** |   -0.57* |     0.06 |    0.27 | 1.00***

p-value adjustment method: Holm (1979)

There is no real reason why this should not be backward compatible. Styles in openxlsx use stylesObject(), otherwise it should work too.

rempsyc · 2022-07-30T15:20:01Z

rempsyc
Jul 30, 2022

That's amazing @JanMarvin! Great work (on this and on openxlsx2). I tested it locally and it works.

I would like to update my package's cormatrix_excel function with the code you provided, but I would also like to acknowledge you properly for it. What would you suggest? May I add you as author of this function in function documentation (given that you've pretty much fully written this new function) as well as add a shout-out in my NEWS.md to your username and your openxlsx2 package? Well perhaps I should even ask first, if you even agree that I integrate this function in my package?

I will of course make minor modifications as I familiarize myself with the new openxlsx2 syntax, but the bulk of it should stay pretty similar, including the correlation dependency since it provides the info we need. I feel like this function will be very useful to students and researchers in psychology, and probably in other fields too. Thanks for taking the time to write this up.

0 replies

JanMarvin · 2022-07-30T15:36:46Z

JanMarvin
Jul 30, 2022
Maintainer Author

Ha, I have nothing against that. Glad that you like it.

But for the time being, I suggest that you try to get it working with openxlsx. Even though much work has already been done for openxlsx2, we are currently not available on CRAN and there is no real ETA yet. I want to provide a stable release without hot fixes and API changes every other week.

We are on the right track and are closing in, but we are not there yet. This was one of the first real world examples I messed around with to see how we behave and it has already triggered two pull request (#288 and another one following soon). But if you want to be an early adapter and help us with testing things, you're more than welcome to try and use openxlsx2 as you please. Just be careful :)

0 replies

rempsyc · 2022-07-31T22:51:22Z

rempsyc
Jul 31, 2022

Just to clarify, you are saying that it would be possible to achieve the same result with openxlsx1? I suppose then the syntax is different but the underlying logic would remain the same. I will try to figure out that logic and translate the code. I will be happy to switch to the openxlsx2 dependency when it is on CRAN.

0 replies

rempsyc · 2022-08-01T01:04:52Z

rempsyc
Aug 1, 2022

So I was able to create a new sheet with the p-values and format those according to desired criteria using openxlsx1.

However, one aspect I am not able to replicate is your style including the stars. It seems the style with stars works with openxlsx::addStyle but not with openxlsx::conditionalFormatting, in which case it is simply ignored. Reprex:

data <- mtcars
my.cor.matrix <- stats::cor(data, use = "pairwise.complete.obs")
my.cor.matrix <- as.data.frame(my.cor.matrix)

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "r-values")
openxlsx::writeData(wb, sheet = "r-values", my.cor.matrix, rowNames = TRUE)
all.columns <- 1:(ncol(my.cor.matrix) + 1)

r.style <- openxlsx::createStyle(numFmt = "#.#0 \\*")
star_style <- openxlsx::createStyle(numFmt = "#.#0 \\*\\*\\*")

# Note: Commenting the code below does not make the following one work
openxlsx::addStyle(wb,
                   sheet = "r-values",
                   style = r.style,
                   rows = all.columns,
                   cols = all.columns,
                   gridExpand = TRUE)

openxlsx::conditionalFormatting(wb,
                                "r-values",
                                cols = all.columns,
                                rows = all.columns,
                                rule = ">0",
                                style = star_style)

openxlsx::saveWorkbook(wb,
                       file = "test.xlsx",
                       overwrite = TRUE)
openxlsx::openXL("test.xlsx")

^{Created on 2022-07-31 by the reprex package (v2.0.1)}

If I use the AND or OR operators in the rule (like in your example), I get the following message:

We found a problem with some content in 'test.xlsx'. Do you want to try to recover as much as we can? If you trust the source of this workbook, click Yes.

Any idea?

0 replies

JanMarvin · 2022-08-01T06:36:02Z

JanMarvin
Aug 1, 2022
Maintainer Author

Hi @rempsyc , thanks for trying. Unfortunately with openxlsx the dxf style, which is used for conditional formatting, does not get the numfmt. Maybe it's possible to fix this, but I've removed the entire styleObject part in openxlsx2 because of such inconsistencies.

With your style in openxlsx it looks like this:

> wb$styles$dxfs
[1] "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font> </dxf>"

And it should look like this (from openxlsx2, apparently the <fill> is not required, I should fix that):

> wb$styles_mgr$styles$dxfs
[1] "<dxf><font><color rgb=\"FF000000\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*\\*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"

The <numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*\\*\"/> part somehow is lost. Therefore either you try to fix that in openxlsx or you have to wait until we release to CRAN (or have to rely on our github release for a little while).

0 replies

JanMarvin · 2022-08-01T08:18:54Z

JanMarvin
Aug 1, 2022
Maintainer Author

Oh you could add the numfmt XML node to the openxlsx workbook. That works as well.

0 replies

rempsyc · 2022-08-01T14:21:19Z

rempsyc
Aug 1, 2022

Thanks! How exactly would I go about adding "the numfmt XML node to the openxlsx workbook"? Is that a PR I need to submit with openxlsx or something in my code I need to change? I checked for these keywords on Google but I cannot find something relevant. Are there any docs I could read about this?

0 replies

JanMarvin · 2022-08-01T14:31:39Z

JanMarvin
Aug 1, 2022
Maintainer Author

You could simply assign to wb$styles$dxfs <- .... E.g. try to use the openxlsx2 string from above. That should work as long as you create a file from scratch. Otherwise you'd have to fix openxlsx::conditionalFormatting() to handle numFmt.

0 replies

rempsyc · 2022-08-01T17:27:42Z

rempsyc
Aug 1, 2022

Cool stuff! I'm able to make it work with one conditional formatting, but not with several ones. From the dxfs, it is not clear to me which sheet and what conditions are specified? I've noticed that when you have multiple conditional formatting, dxfs becomes a list of character strings that all look the same, except for the star specification for instance. However, it is not clear how the software decides which belongs to which. Is it only based on order of the list? First element is applied to the first conditional formatting, etc.?

Because when I use more than one, only the first one is used, not the second one.

When I look at wb$styles_mgr$styles$dxfs in your openxlsx2 example, I get:

wb$styles_mgr$styles$dxfs
[1] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 _*_*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"   
[2] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 _*_*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"   
[3] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*_*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"  
[4] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*_*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"  
[5] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>" 
[6] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*_*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>" 
[7] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*\\*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"
[8] "<dxf><font><color rgb=\"FF9C0006\"/><name val=\"Calibri\"/><sz val=\"11\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.##0 \\*\\*\\*\"/><fill><patternFill patternType=\"solid\"><bgColor rgb=\"FFFFC7CE\"/></patternFill></fill></dxf>"

I realized the description of those styles are available in wb$styles_mgr$dxf:

wb$styles_mgr$dxf
  typ id         name
1 dxf  0    gre_style
2 dxf  1    red_style
3 dxf  2 gre_style_p1
4 dxf  3 red_style_p1
5 dxf  4 gre_style_p2
6 dxf  5 red_style_p2
7 dxf  6 gre_style_p3
8 dxf  7 red_style_p3

Seems like I can't access that info in openxlsx1 but I assume the same logic would apply (order is defined when they are first added). In openxlsx1 there is wb$styleObjects but I don't know if that would be the closest equivalent to wb$styles_mgr$dxf (doesn't seem so).

Here is a reprex showing that the first style (no star) gets applied correctly, but not the second style:

wb <- openxlsx::createWorkbook()
r.style <- openxlsx::createStyle(numFmt = "#.#0 _*_*_*")
starstyle <- openxlsx::createStyle(numFmt = "#.#0 \\*_*_*")

cm <- mtcars |> correlation::correlation() |>  summary(redundant = TRUE)
all.columns <- 1:(ncol(cm))
pf <- attr(cm, "p")

openxlsx::addWorksheet(wb, "p-values")
openxlsx::writeData(wb, sheet = "p-values", pf, rowNames = FALSE)

openxlsx::conditionalFormatting(wb,
                                "p-values",
                                cols = all.columns,
                                rows = all.columns,
                                rule = "> 0.05",
                                style = r.style)

openxlsx::conditionalFormatting(wb,
                                "p-values",
                                cols = all.columns,
                                rows = all.columns,
                                rule = "<= 0.05",
                                style = starstyle)

wb$styles$dxfs
#> [1] "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font> </dxf>"

wb$styles$dxfs <- c("<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 _*_*_*\"/></dxf>",
                    "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 \\*_*_*\"/></dxf>")

wb$styles$dxfs
#> [1] "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 _*_*_*\"/></dxf>" 
#> [2] "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 \\*_*_*\"/></dxf>"

openxlsx::saveWorkbook(wb,
                       file = "test.xlsx",
                       overwrite = TRUE)
openxlsx::openXL("test.xlsx")

^{Created on 2022-08-01 by the reprex package (v2.0.1)}

But keeping only the second style/dfx of the list does add stars as expected since it becomes the first one. But then if there's a second style with two stars it doesn't get applied, etc.

Maybe there is something else breaking that I am missing. Perhaps I should open a new PR in openxlsx to simply ask to add support for numFmt in conditionalFormatting.

0 replies

JanMarvin · 2022-08-01T21:05:05Z

JanMarvin
Aug 1, 2022
Maintainer Author

You only see a single style, because openxlsx calls unique on styles. Therefore only a single style is created and applied. It works if you create two styles and apply the correct format afterwards.

wb <- openxlsx::createWorkbook()
r.style <- openxlsx::createStyle(numFmt = "#.#0 _*_*_*", bgFill = "blue")
starstyle <- openxlsx::createStyle(numFmt = "#.#0 \\*_*_*", bgFill = "green")

cm <- mtcars |> correlation::correlation() |>  summary(redundant = TRUE)
all.columns <- 1:(ncol(cm))
pf <- attr(cm, "p")

openxlsx::addWorksheet(wb, "p-values")
openxlsx::writeData(wb, sheet = "p-values", pf, rowNames = FALSE)

openxlsx::conditionalFormatting(wb,
                                "p-values",
                                cols = all.columns,
                                rows = all.columns,
                                rule = "> 0.05",
                                style = r.style)

openxlsx::conditionalFormatting(wb,
                                "p-values",
                                cols = all.columns,
                                rows = all.columns,
                                rule = "<= 0.05",
                                style = starstyle)

wb$styles$dxfs <- c("<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 _*_*_*\"/></dxf>",
                    "<dxf><font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Calibri\"/></font><numFmt numFmtId=\"3\" formatCode=\"#.#0 \\*_*_*\"/></dxf>")

openxlsx::openXL(wb)

Ofc you can open an issue in openxlsx for someone to fix numFmts in conditionalFormatting(), but chances are high, that I'll simply ignore it 😄

2 replies

rempsyc Aug 1, 2022

Ok so this is really weird I think. I've tried your code and, to my surprise, it worked on my setup as well. So I compared it to mine and it is exactly the same, except for one thing, the specification of bgFill in the style creation! So I tested adding and removing bgFill to my earlier code, and that's enough to reliably break the code or make it work! That's so interesting! And is it expected?

JanMarvin Aug 1, 2022
Maintainer Author

The entire open XML format is quite easy to break. The style is applied as index to the conditional formatting node. If the style is not found or if to many styles are found, Excel will complain. This makes development a bit iffy, because you have to check with Excel all the time.

rempsyc · 2022-08-01T21:16:02Z

rempsyc
Aug 1, 2022

Ok so in the meanwhile I have decided to try to make openxlsx2 work to my needs. Here is the result. Reprex:

library(rempsyc)
#> Tutorials: https://remi-theriault.com/tutorials
#> Bug report, support, special request: https://github.com/rempsyc/rempsyc/issues
#> 
#> Suggested APA citation: Thériault, R. (2022). rempsyc: Convenience functions for psychology (R package version 0.0.5.5) [Computer software]. https://rempsyc.remi-theriault.com
cormatrix_excel2(mtcars)
#> # Correlation Matrix (pearson-method)
#> 
#> Parameter |      mpg |      cyl |     disp |       hp |     drat |       wt |     qsec |       vs |       am |    gear |    carb
#> --------------------------------------------------------------------------------------------------------------------------------
#> mpg       |  1.00*** | -0.85*** | -0.85*** | -0.78*** |  0.68*** | -0.87*** |     0.42 |   0.66** |   0.60** |    0.48 |  -0.55*
#> cyl       | -0.85*** |  1.00*** |  0.90*** |  0.83*** | -0.70*** |  0.78*** |   -0.59* | -0.81*** |   -0.52* |   -0.49 |   0.53*
#> disp      | -0.85*** |  0.90*** |  1.00*** |  0.79*** | -0.71*** |  0.89*** |    -0.43 | -0.71*** |   -0.59* |  -0.56* |    0.39
#> hp        | -0.78*** |  0.83*** |  0.79*** |  1.00*** |    -0.45 |   0.66** | -0.71*** | -0.72*** |    -0.24 |   -0.13 | 0.75***
#> drat      |  0.68*** | -0.70*** | -0.71*** |    -0.45 |  1.00*** | -0.71*** |     0.09 |     0.44 |  0.71*** | 0.70*** |   -0.09
#> wt        | -0.87*** |  0.78*** |  0.89*** |   0.66** | -0.71*** |  1.00*** |    -0.17 |   -0.55* | -0.69*** |  -0.58* |    0.43
#> qsec      |     0.42 |   -0.59* |    -0.43 | -0.71*** |     0.09 |    -0.17 |  1.00*** |  0.74*** |    -0.23 |   -0.21 | -0.66**
#> vs        |   0.66** | -0.81*** | -0.71*** | -0.72*** |     0.44 |   -0.55* |  0.74*** |  1.00*** |     0.17 |    0.21 |  -0.57*
#> am        |   0.60** |   -0.52* |   -0.59* |    -0.24 |  0.71*** | -0.69*** |    -0.23 |     0.17 |  1.00*** | 0.79*** |    0.06
#> gear      |     0.48 |    -0.49 |   -0.56* |    -0.13 |  0.70*** |   -0.58* |    -0.21 |     0.21 |  0.79*** | 1.00*** |    0.27
#> carb      |   -0.55* |    0.53* |     0.39 |  0.75*** |    -0.09 |     0.43 |  -0.66** |   -0.57* |     0.06 |    0.27 | 1.00***
#> 
#> p-value adjustment method: Holm (1979)

cormatrix_excel2(iris)
#> # Correlation Matrix (pearson-method)
#> 
#> Parameter    | Sepal.Length | Sepal.Width | Petal.Length | Petal.Width
#> ----------------------------------------------------------------------
#> Sepal.Length |      1.00*** |       -0.12 |      0.87*** |     0.82***
#> Sepal.Width  |        -0.12 |     1.00*** |     -0.43*** |    -0.37***
#> Petal.Length |      0.87*** |    -0.43*** |      1.00*** |     0.96***
#> Petal.Width  |      0.82*** |    -0.37*** |      0.96*** |     1.00***
#> 
#> p-value adjustment method: Holm (1979)

cormatrix_excel2(airquality)
#> # Correlation Matrix (pearson-method)
#> 
#> Parameter |    Ozone | Solar.R |     Wind |     Temp |     Month |       Day
#> ----------------------------------------------------------------------------
#> Ozone     |  1.00*** |  0.35** | -0.60*** |  0.70*** |      0.16 |     -0.01
#> Solar.R   |   0.35** | 1.00*** |    -0.06 |   0.28** |     -0.08 |     -0.15
#> Wind      | -0.60*** |   -0.06 |  1.00*** | -0.46*** |     -0.18 |      0.03
#> Temp      |  0.70*** |  0.28** | -0.46*** |  1.00*** |   0.42*** |     -0.13
#> Month     |     0.16 |   -0.08 |    -0.18 |  0.42*** |   1.00*** | -7.96e-03
#> Day       |    -0.01 |   -0.15 |     0.03 |    -0.13 | -7.96e-03 |   1.00***
#> 
#> p-value adjustment method: Holm (1979)

^{Created on 2022-08-01 by the reprex package (v2.0.1)}

It seems that styles overwrite each other (instead of being additive). Which means that to make it work I had do it through the rule = “AND(…)” command that you illustrated, for every possibility of p-value and correlation size. It was a lot of work but it works now! :D

So overall, I am pretty satisfied (though I'll probably need to tweak the colours eventually) and will probably use openxlsx2 as a remote dependency even though it is still in developmental stage and not on CRAN yet. As it is the only solution that works for me right now. If the API changes, I will adapt. I think it should be OK.

One aspect I have noticed is that the default font and background are coloured, whereas I think they should not be. This forces me to specify font as black and background as white, but in doing so, the natural borders are lost (and I don't feel like specifying them manually and try to match the default colour, etc.). It would be nice if defaults would be actual defaults, if possible. Thanks for all your help!

1 reply

JanMarvin Aug 1, 2022
Maintainer Author

:) Yes, the issue with colored background and fonts is noted #291 (there are so many things to do and it's impossible to fix and check everything).

rempsyc · 2022-08-02T23:43:58Z

rempsyc
Aug 2, 2022

I suppose it is best to continue the discussion here, so just an update.

I have been having trouble with the openxlsx2 remote dependency and have asked for help on the R Discord as well as the RStudio community. Long story short, it seems rlang::check_installed only supports CRAN, not GitHub unfortunately, so I made a custom check-install at the beginning of the function:

if (isFALSE(requireNamespace(openxlsx2, quietly = TRUE))) {
  rlang::check_installed(
    "remotes",
    reason = "to install the required dependency for this function (openxlsx2)."
  )
  cat("The package `openxlsx2` is required for this function\n",
      "Would you like to install it?")
  if (utils::menu(c("Yes", "No")) == 1) {
    remotes::install_github('JanMarvin/openxlsx2')
  } else (stop(
    "The cormatrix_excel2 function relies on the `openxlsx2` package.
    You can install it manually with:
    remotes::install_github('JanMarvin/openxlsx2')"))
  }

Not best practice, but at least it works! That said, I am also getting no errors or warnings when running R CMD check locally, but I am getting one on GitHub tests for Windows only:

C:\rtools40\usr\bin\tar.exe --use-compress-program "zstd -d" -xf D:/a/_temp/a1c2b5ca-dcde-4da8-9a76-7dcea8572bd6/cache.tzst -P -C D:/a/rempsyc/rempsyc --force-local

 ! rlang, fansi, utf8, and vctrs are loaded in other R sessions and have locked
  files in your library. The installation will probably fail, unless you quit
  from this R session:
  
  • Rterm.exe (5876) in cmd.exe (6908)

 ℹ Building openxlsx2 0.2.1.9000
  ✖ Failed to build openxlsx2 0.2.1.9000
  
  Error: Error: <callr_remote_error: Failed to build source package 'openxlsx2'>
   in process 5876 
  -->
  Failed to build source package 'openxlsx2', stdout + stderr:

  OE> * installing *source* package 'openxlsx2' ...

 OE> Error: package or namespace load failed for 'openxlsx2' in inDL(x, as.logical(local), as.logical(now), ...):
  OE>  unable to load shared object 'C:/Users/runneradmin/AppData/Local/Temp/RtmpIn5JEg/pkg-lib16f464242299/openxlsx2/libs/x64/openxlsx2.dll':
  OE>   LoadLibrary failure:  The specified procedure could not be found.
  OE> 
  OE> Error: loading failed
  OE> Execution halted
  OE> ERROR: loading failed
  OE> * removing 'C:/Users/RUNNER~1/AppData/Local/Temp/RtmpIn5JEg/pkg-lib16f464242299/openxlsx2'

Note: I know you already warned me that the package is at one's "own risk and perils" until it reaches CRAN but I am just sharing here FYI.

8 replies

JanMarvin Aug 4, 2022
Maintainer Author

You could add this to your GitHub action, but that ofc won't magically happen for others.
My advice is still to try to make it work with openxlsx. This might require some XML changes, but this package is on CRAN for some time. Whereas openxlsx2 is still under very active development. It may already be working well for many things, but just yesterday I fixed three small annoying bugs that someone needed to find and fix. We will reach the goal, but we will not rush :)

rempsyc Aug 5, 2022

CONFIRMED solution:

actions/setup-r-dependencies at v2 · r-lib/actions · GitHub

Dependencies in other CRAN-like repositories

If your dependency is available in a CRAN-like repository (e.g. R-universe), then you can use the extra-repositories parameter of the setup-r action. See more at the setup-r documentation.

So I had to add the extra-repositories: "https://janmarvin.r-universe.dev" line after with in the - uses: r-lib/actions/setup-r@v2 line, so it looks like this in the R-CMD-check.yaml workflow file:

  - uses: r-lib/actions/setup-r@v2
    with:
      r-version: ${{ matrix.config.r }}
      http-user-agent: ${{ matrix.config.http-user-agent }}
      use-public-rspm: true
      extra-repositories: "https://janmarvin.r-universe.dev"

JanMarvin Sep 30, 2022
Maintainer Author

@rempsyc we are now on CRAN so it should be easier to include us. Thanks for sticking around :)

rempsyc Sep 30, 2022

@rempsyc we are now on CRAN so it should be easier to include us. Thanks for sticking around :)

That is absolutely fantastic! Congratulations! :D

That's such a coinciding timing, because rempsyc just got accepted on CRAN this week as well! Incredible!

Had I known I would have waited a few extra days to submit to avoid having to justify to CRAN maintainers why I absolutely needed an external non-CRAN package dependency 😅

I can't wait to change it to a CRAN dependency but I think I'll need to wait at least a month before resubmitting a new version to CRAN.

JanMarvin Sep 30, 2022
Maintainer Author

Ha, sorry, was a bit surprised, why your package was already waiting for us on CRAN.
Not sure about any waiting time, afaik a hotfix can be submitted anytime. Though, maybe the waiting time is helpful to see if anything pops up once the dusk settles. Congrats and thanks :)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Customize correlation output #286

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 12 comments 11 replies

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Dependencies in other CRAN-like repositories

{{title}}

{{title}}

{{title}}

Select a reply

Customize correlation output #286

JanMarvin Jul 29, 2022 Maintainer

Replies: 12 comments · 11 replies

JanMarvin Jul 30, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 1, 2022 Maintainer Author

JanMarvin Aug 4, 2022 Maintainer Author

CONFIRMED solution:

Dependencies in other CRAN-like repositories

JanMarvin Sep 30, 2022 Maintainer Author

JanMarvin Sep 30, 2022 Maintainer Author

JanMarvin
Jul 29, 2022
Maintainer

Replies: 12 comments 11 replies

JanMarvin
Jul 30, 2022
Maintainer Author

JanMarvin
Aug 1, 2022
Maintainer Author

JanMarvin
Aug 1, 2022
Maintainer Author

JanMarvin
Aug 1, 2022
Maintainer Author

JanMarvin
Aug 1, 2022
Maintainer Author

JanMarvin Aug 1, 2022
Maintainer Author

JanMarvin Aug 1, 2022
Maintainer Author

JanMarvin Aug 4, 2022
Maintainer Author

JanMarvin Sep 30, 2022
Maintainer Author

JanMarvin Sep 30, 2022
Maintainer Author