hyperlink issue with a R package
1
0
Entering edit mode
@bioinformatics-10931
Last seen 2.3 years ago
United States

 

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)

 
TPP • 2.6k views
ADD COMMENT •
2
Entering edit mode
Mike Smith ★ 6.5k
@mike-smith
Last seen 4 hours ago
EMBL Heidelberg

Looking at the code, it appears TPP doesn't look for a 'plots' column when it makes the hyperlinks.  Has that column name changed recently?

Regardless you can create the hyperlinks yourself using something like the example here:

## load library
library(openxlsx)

## path to the original results table
xlsx_file <- 'c:/Users/smith/Desktop/TPP/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'

## load workbook to preserve formatting & overwrite plots
wb <- loadWorkbook(file = xlsx_file)
writeData(wb = wb, 
          sheet = 'Results', 
          x = plotPaths, 
          startCol = which(colnames(tab) == "plot"), 
          startRow = 2, 
          keepNA = TRUE)
saveWorkbook(wb = wb, 
             file = xlsx_file, 
             overwrite = TRUE)
ADD COMMENT •
0
Entering edit mode

@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?

ADD REPLY •
0
Entering edit mode

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.

ADD REPLY •
0
Entering edit mode

@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

ADD REPLY •
0
Entering edit mode

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 tab object 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.

ADD REPLY •
0
Entering edit mode

@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.

ADD REPLY •
0
Entering edit mode

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 a character in the second part.

ADD REPLY •
0
Entering edit mode

@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 

ADD REPLY •
0
Entering edit mode

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.

ADD REPLY •
0
Entering edit mode

@Mike Smith please look at above, 

ADD REPLY •
0
Entering edit mode

Ok, so you're really close in your example, but the issue is with the class of data you're giving to the x argument.  To understand this a bit better, lets look at your gsub() outside of the function call:

genes <- gsub("DoseResponse_Curves/drCurve_", "", gsub(".pdf", "", plotPaths))
is(genes)

[1] "hyperlink"

You can see that even after the gsub() command, what you've got is a vector of class hyperlink, and when you use that it overwrites the links you used already.  Lets try setting it to a the simple character class and then using it.

class(genes) <- 'character'
writeData(wb = wb, 
          sheet = 'Results', 
          x = genes, 
          startCol = which(colnames(tab) == "plot"), 
          startRow = 2)
ADD REPLY •
0
Entering edit mode

@Mike Smith Yes, that was it. Thanks

ADD REPLY •

Login before adding your answer.

Similar Posts
Loading Similar Posts
Traffic: 516 users visited in the last hour
Content Search
Users
Tags
Badges
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6