CODEWITHSUNDEEP
r
neuron
neuron

Reputation: 2049

How to highlight excel cells in R

So I have a bunch of data that I am looking through. In the past, I have used the openxlsx to highlight entire rows. I want to step it up a bit and highlight specific cells. Here is a sample of the format of the data I am working with

df <- structure(list(Name = c("ENSCAFG00000000019","ENSCAFG00000000052", "ENSCAFG00000000094","ENSCAFG00000000210"), baseMean = c(692.430970065448, 391.533849079888, 1223.74083601928, 280.477417588943), log2FoldChange = c("0.0819834415495699", 
"-2.6249568393179099", "6.15181461329998", "0.23483770613468"
), lfcSE = c("0.247177913269579", "0.65059275393549898", "0.33371763683349598", "0.353449339778654"), stat = c("4.3773467751931898", "-4.0347157625707997", 
"3.4514646101088902", "3.4936766522410099"), pvalue = c("1.20132758621478E-5", "5.4668435006169397E-5", "5.5755287106466398E-4", "4.7641767052765697E-4"), padj = c("9.8372077245438908E-4", "0.00004", "0.000006", "1.47480018315951E-2"), symbol = c("ZNF516", "CDH19", "LMAN1", "NA"), entrez = c("483930", "483948", "476186", "NA")), .Names = c("Names", "baseMean", "log2FoldChange", "lfcSE", "stat", "pvalue", "padj", "symbol", "entrez"), row.names = c(NA, -4L), class = c("tbl_df", "tbl", "data.frame"))

So what I want to do is highlight cells in log2FoldChange that are either <= -1 or >= 1 and highlight cells that are <= 0.05. Is this something that can be done? I have read a lot about highlighting rows but not specific cells with a condition.

enter image description here

This is sort of what I am hoping I can get the data to look like. The log2Foldchange and the padj don't need to make up like the example above.

Thanks in advance

Upvotes: 3

Views: 2304

Answers (1)

Karsten W.
Karsten W.

Reputation: 18400

Here is one example. Note, however, that all cells in column padj have values below 0.05.

library(openxlsx)

# note that some columns of df look numeric, but are character
df <- data.frame(
    Name = c("ENSCAFG00000000019","ENSCAFG00000000052", "ENSCAFG00000000094","ENSCAFG00000000210"), 
    baseMean = c(692.430970065448, 391.533849079888, 1223.74083601928, 280.477417588943), 
    log2FoldChange = c(0.0819834415495699, -2.6249568393179099, 6.15181461329998, 0.23483770613468), 
    lfcSE = c(0.247177913269579, 0.65059275393549898, 0.33371763683349598, 0.353449339778654), 
    stat = c(4.3773467751931898, -4.0347157625707997, 3.4514646101088902, 3.4936766522410099), 
    pvalue = c(1.20132758621478E-5, 5.4668435006169397E-5, 5.5755287106466398E-4, 4.7641767052765697E-4), 
    padj = c(9.8372077245438908E-4, 0.00004, 0.000006, 1.47480018315951E-2), 
    symbol = c("ZNF516", "CDH19", "LMAN1", "NA"), entrez = c("483930", "483948", "476186", "NA"),
    stringsAsFactors=FALSE
)

# write dataset
wb <- createWorkbook()
addWorksheet(wb, sheetName="df")
writeData(wb, sheet="df", x=df)

# define style
yellow_style <- createStyle(fgFill="#FFFF00")

# log2FoldChange
y <- which(colnames(df)=="log2FoldChange")
x <- which(abs(df$log2FoldChange)>=1)
addStyle(wb, sheet="df", style=yellow_style, rows=x+1, cols=y, gridExpand=TRUE) # +1 for header line

# padj
y <- which(colnames(df)=="padj")
x <- which(abs(df$padj)<=0.05)
addStyle(wb, sheet="df", style=yellow_style, rows=x+1, cols=y, gridExpand=TRUE) # +1 for header line

# write result
saveWorkbook(wb, "yellow.xlsx", overwrite=TRUE)

You may also want to have a look at BERT.

Upvotes: 3

Related Questions