Feature request: Add extra characters to cells based on other criteria

[rempsyc]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

I am the author of the rempsyc package and one of the package's function (cormatrix_excel) relies on openxlsx to export a formatted correlation matrix to Excel. Some users have made the request to include significance stars or p-values in the exported Excel file like other popular software/packages do. It currently does not because it relies on the numeric value of the cells to do conditional formatting (i.e., colour of the cells).

Describe the solution you'd like A clear and concise description of what you want to happen.

I would like to export to Excel a correlation table with correlation coefficients as well as e.g., significance stars, while colouring cells based on the size of the correlation coefficients.

Thus, I would like to be able to either ignore certain characters (e.g., significance stars "***") when processing the cells with conditionalFormatting, or to be able to add them as extra information in the cells, separately (e.g., based on a separate dataframe of p-values).

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Alternatives include using the apaTables or correlation packages, though they don't export to Excel. Else, it is possible to use rempsyc::cormatrix_excel but it won't have the p-values or stars.

Additional context Add any other context or screenshots about the feature request here.

Reprex:

apaTables::apa.cor.table(mtcars, "ex.CorTable1.doc")
#> 
#> 
#> Means, standard deviations, and correlations with confidence intervals
#>  
#> 
#>   Variable M      SD     1            2            3            4           
#>   1. mpg   20.09  6.03                                                      
#>                                                                             
#>   2. cyl   6.19   1.79   -.85**                                             
#>                          [-.93, -.72]                                       
#>                                                                             
#>   3. disp  230.72 123.94 -.85**       .90**                                 
#>                          [-.92, -.71] [.81, .95]                            
#>                                                                             
#>   4. hp    146.69 68.56  -.78**       .83**        .79**                    
#>                          [-.89, -.59] [.68, .92]   [.61, .89]               
#>                                                                             
#>   5. drat  3.60   0.53   .68**        -.70**       -.71**       -.45**      
#>                          [.44, .83]   [-.84, -.46] [-.85, -.48] [-.69, -.12]
#>                                                                             
#>   6. wt    3.22   0.98   -.87**       .78**        .89**        .66**       
#>                          [-.93, -.74] [.60, .89]   [.78, .94]   [.40, .82]  
#>                                                                             
#>   7. qsec  17.85  1.79   .42*         -.59**       -.43*        -.71**      
#>                          [.08, .67]   [-.78, -.31] [-.68, -.10] [-.85, -.48]
#>                                                                             
#>   8. vs    0.44   0.50   .66**        -.81**       -.71**       -.72**      
#>                          [.41, .82]   [-.90, -.64] [-.85, -.48] [-.86, -.50]
#>                                                                             
#>   9. am    0.41   0.50   .60**        -.52**       -.59**       -.24        
#>                          [.32, .78]   [-.74, -.21] [-.78, -.31] [-.55, .12] 
#>                                                                             
#>   10. gear 3.69   0.74   .48**        -.49**       -.56**       -.13        
#>                          [.16, .71]   [-.72, -.17] [-.76, -.26] [-.45, .23] 
#>                                                                             
#>   11. carb 2.81   1.62   -.55**       .53**        .39*         .75**       
#>                          [-.75, -.25] [.22, .74]   [.05, .65]   [.54, .87]  
#>                                                                             
#>   5            6            7            8            9           10         
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>                                                                              
#>   -.71**                                                                     
#>   [-.85, -.48]                                                               
#>                                                                              
#>   .09          -.17                                                          
#>   [-.27, .43]  [-.49, .19]                                                   
#>                                                                              
#>   .44*         -.55**       .74**                                            
#>   [.11, .68]   [-.76, -.26] [.53, .87]                                       
#>                                                                              
#>   .71**        -.69**       -.23         .17                                 
#>   [.48, .85]   [-.84, -.45] [-.54, .13]  [-.19, .49]                         
#>                                                                              
#>   .70**        -.58**       -.21         .21          .79**                  
#>   [.46, .84]   [-.77, -.29] [-.52, .15]  [-.15, .52]  [.62, .89]             
#>                                                                              
#>   -.09         .43*         -.66**       -.57**       .06         .27        
#>   [-.43, .27]  [.09, .68]   [-.82, -.40] [-.77, -.28] [-.30, .40] [-.08, .57]
#>                                                                              
#> 
#> Note. M and SD are used to represent mean and standard deviation, respectively.
#> Values in square brackets indicate the 95% confidence interval.
#> The confidence interval is a plausible range of population correlations 
#> that could have caused the sample correlation (Cumming, 2014).
#>  * indicates p < .05. ** indicates p < .01.
#> 

mtcars |> 
  correlation::correlation() |> 
  summary(redundant = TRUE) |> 
  plot()

rempsyc::cormatrix_excel(mtcars)
#>        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
#> 
#>  [Correlation matrix 'mycormatrix.xlsx' has been saved to working directory (or where specified).]

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

So the ideal result would be a mix of options 2 and 3 of this reprex, i.e., an Excel table including coloured correlation coefficients but that would also include significance stars.

Is there any way to do this? That is, to add information not contained in the actual cells?

[JanMarvin]

I have a solution for this, but I've created it with openxlsx2. (It could be backward compatible, but I haven't checked it.)

I exported both the correlation matrix and the p value matrix and created numeric styles for all required styles, e.g. negative correlation with **. That keeps the actual values untouched.

Later I'll post my solution as a discussion in openxlsx2. It's not really something we should provide in the package, but something you can implement in your export function.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.