Entering edit mode
I am using a package
source("https://bioconductor.org/biocLite.R")
biocLite("TPP")
Then you load the library
library(TPP)
Then run their example
data(hdacCCR_smallExample)
> tppccrResults <- analyzeTPPCCR(configTable=hdacCCR_config,
+ data=hdacCCR_data, nCores=1,resultPath ="C:/Users/admin/Desktop/myfolder")
The problem is after it finishes the calculation, the results will be saved on the folder that you want and then the column named "plot" normally gives a hyperlink to each "pdf". This is not working it just give a text rather than the hyperlink
> sessionInfo() R version 3.4.1 (2017-06-30) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 7 x64 (build 7601) Service Pack 1 Matrix products: default locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C LC_TIME=English_United States.1252 attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] bindrcpp_0.2 BiocInstaller_1.26.1 TPP_3.4.3 tidyr_0.7.1 magrittr_1.5 dplyr_0.7.2 loaded via a namespace (and not attached): [1] tidyselect_0.2.0 purrr_0.2.3 reshape2_1.4.2 lattice_0.20-35 splines_3.4.1 colorspace_1.3-2 htmltools_0.3.6 [8] stats4_3.4.1 rlang_0.1.2 foreign_0.8-69 glue_1.1.1 BiocGenerics_0.22.0 RColorBrewer_1.1-2 lambda.r_1.1.9 [15] foreach_1.4.3 bindr_0.1 plyr_1.8.4 stringr_1.2.0 munsell_0.4.3 gtable_0.2.0 futile.logger_1.4.3 [22] psych_1.7.5 codetools_0.2-15 VGAM_1.0-4 evaluate_0.10.1 labeling_0.3 Biobase_2.36.2 knitr_1.17 [29] biobroom_1.8.0 doParallel_1.0.10 parallel_3.4.1 broom_0.4.2 Rcpp_0.12.12 scales_0.5.0 backports_1.1.0 [36] limma_3.32.5 gridExtra_2.2.1 mnormt_1.5-5 ggplot2_2.2.1 digest_0.6.12 stringi_1.1.5 openxlsx_4.0.17 [43] grid_3.4.1 rprojroot_1.2 nls2_0.2 tools_3.4.1 bitops_1.0-6 lazyeval_0.2.0 RCurl_1.95-4.8 [50] tibble_1.3.4 futile.options_1.0.0 sme_0.8 pkgconfig_2.0.1 MASS_7.3-47 data.table_1.10.4 assertthat_0.2.0 [57] rmarkdown_1.6 iterators_1.0.8 R6_2.2.2 VennDiagram_1.6.17 nlme_3.1-131 compiler_3.4.1
#load library
library(openxlsx)
## path to the original results table
xlsx_file <- 'c:/Users/admin/Desktop/myfolder/results_TPP_CCR.xlsx'
## read in the existing data
tab <- read.xlsx(xlsx_file, sheet = 'Results')
plotPaths <- tab[,'plot']
plotPaths[which(is.na(plotPaths))] <- ""
class(plotPaths) <- 'hyperlink'
writeData(wb = wb,
sheet = 'Results',
x = plotPaths,
startCol = which(colnames(tab) == "plot"),
startRow = 2,
keepNA = TRUE)
writeData(wb = wb,
sheet = 'Results',
x= gsub("DoseResponse_Curves/drCurve_", "", gsub(".pdf", "", plotPaths)),
startCol = which(colnames(tab) == "plot"),
startRow = 2,
keepNA = TRUE)
saveWorkbook(wb = wb,
file = xlsx_file,
overwrite = TRUE)
@Mike Smith hi, I have no idea if they have change the column or not. I have one question related to your code which seems very elegant. Is there a way to show the text to display in excel only the gene name instead the who path? like what was in original done by the package?
Yes that can be done. I'd suggest taking a look at the documentation for the openxlsx package. Specifically, the first page of this document (https://cran.r-project.org/web/packages/openxlsx/vignettes/formatting.pdf) has details on formatting hyperlinks.
@Mike Smith unfortunately, I cannot figure out how to just write the gen instead all the path. it would be very helpful if you could guide me through
I'm very much in favour of trying to encourage people to try and solve problems themselves, and I don't think the 8 minutes between our two posts is indicative of much effort.
I will point out that the
tabobject created in the code above contains everything in the Excel file, including a column with the protein ID in it, and somewhere near the bottom of the first page in the openxlsx vignette there is a clear example of changing hyperlink text.Try a few things based on that, see how far you can get, and then come back with some example code that we can discuss.
@Mike Smith If i correctly understood that when I use `x= gsub("DoseResponse_Curves/drCurve_", "", gsub(".pdf", "", plotPaths)),` instead `x = plotPaths,` I get the name but the hyperlink does not work anymore.
Ok, that's good progress. If you check the comment right at the bottom of the page in that vignette it says "to change the display text for a hyperlink column just write over those cells". That implies to me that we need to write the values in this column twice; first to put in the hyperlink, second to add the text we want displayed.
So I would try running the
writeData()step two times, once with the full paths to the files, and once with your new text.You'll also notice that at one point we set the class of the vector to
hyperlink. You might need to make sure it's acharacterin the second part.@Mike Smith unfortunately does not work. I have tried this, once you writeData, if you write again, you will overwrite it and everything will set to the latter one. Please try it yourself and you will see that it does not work
I have tried it and can get it to work fine.
Why don't you include your complete code here, from reading the file to writing it back out, like in my first example, and we'll see if we can find the issue.
@Mike Smith please look at above,
Ok, so you're really close in your example, but the issue is with the class of data you're giving to the
xargument. To understand this a bit better, lets look at yourgsub()outside of the function call:genes <- gsub("DoseResponse_Curves/drCurve_", "", gsub(".pdf", "", plotPaths)) is(genes)You can see that even after the
gsub()command, what you've got is a vector of classhyperlink, and when you use that it overwrites the links you used already. Lets try setting it to a the simplecharacterclass and then using it.class(genes) <- 'character' writeData(wb = wb, sheet = 'Results', x = genes, startCol = which(colnames(tab) == "plot"), startRow = 2)@Mike Smith Yes, that was it. Thanks