Creating a script

[Fortunat]

Hello, could anyone please have an idea regarding this situation: I have a first Excel file
File1.pdf (22.9 KB) containing variables, to which we've associated a second file
File2.pdf (20.8 KB) containing the same variables (code, object and country). What specific R script can be used to set the numerical values of the second file to red when the values of the first file are between 1 and 2 (see the value column) and to set the empty cells of the second file to N/A (with a gray color in the cell) when the values of the first file are 0 (see the value column)?

[vedoa]

Hi @Fortunat,

i would argue that the easiest way to do this would be:

• Have both files in the same format. file1 is in long, file2 is in wide format . Having both in long format would make the comparison easier.
• Use excels conditional formatting to do the coloring of the cells based on the value from file1.

Even if you are insistent on using R you still have to switch to long format for file2 and do a merge or logical deduction based on the value column of file1 and save again to excel for the coloring part. So using openxlsx and the stats::reshape function (or tidyr::pivot_longer) can achieve this.

Still think keeping everything in excel is easier for this particular case.

[Fortunat]

Hello @vedoa, thank you for your feedback, I tried to put the two tables in a long format to proceed with the comparison, however my R script below is limited, because I do not get the expected results, because I wanted to format according to the conditions set the numbers so that they can have the color of the red font, then I do not have the gray fill color in the cell containing N/A (I get only N/A, the gray fill color in the cell does not appear).

code <- c(5, 5, 5, 5, 4, 4, 4, 4, 7, 7,7,7)
object <- c("telephone","computer", "car", "house","telephone","computer","car","house","telephone","computer","car","house")
country <- c("A","A","A","A","B","B","B","B","C","C","C","C")
cost <- c(1218,1100,"",1389,2963,"",2677,1200,1235, 1420,2141,"")
value <- c(5,1,0,2,2,0,3,2,0,5,1,0)

data1 <- data.frame(code,country, object,value)
data2 <- data.frame(code,country, object,cost)
data1$value <- as.numeric(data1$value)

Assuming the cost column is the one you want to color

data2$cost <- ifelse(data1$value >= 1 & data1$value <= 2, "red", data2$cost)
data2$cost[data1$value == 0] <- "N/A"

write.xlsx(data2,'outputs/data.xlsx', overwrite = TRUE)

[vedoa]

Hi @Fortunat

# library for reading and writing excel
library(openxlsx)

# your code slightly adjusted
code <- c(5, 5, 5, 5, 4, 4, 4, 4, 7, 7, 7, 7)
object <- c("telephone", "computer", "car", "house", "telephone", "computer", "car", "house", "telephone", "computer", "car", "house")
country <- c("A", "A", "A", "A", "B", "B", "B", "B", "C", "C", "C", "C")
cost <- c(1218, 1100, NA, 1389, 2963, NA, 2677, 1200, 1235, 1420, 2141, NA)
value <- c(5, 1, 0, 2, 2, 0, 3, 2, 0, 5, 1, 0)

data1 <- data.frame(code, country, object, value)
data2 <- data.frame(code, country, object, cost)

# which rows should be colored (the + 1 is because of the headers in excel)
red <- which(data1$value >= 1 & data1$value <= 2) + 1 
gray <- which(data1$value == 0) + 1

# styles for cells
styleRedFont <- createStyle(fontColour = "#FF0000")
styleGrayBg <- createStyle(fgFill = "#A6A6A6")


# Create a new workbook
wb <- createWorkbook("Fortunat")

# Add a worksheets
addWorksheet(wb, "file2colored")

# write data to worksheet file2colored
writeData(wb = wb, sheet = "file2colored", x = data2, rowNames = FALSE)

# Add styles according to your explanation
addStyle(wb, sheet = "file2colored", style = styleRedFont, rows = red, cols = which(names(data2) == "cost"))
addStyle(wb, sheet = "file2colored", style = styleGrayBg, rows = gray, cols = which(names(data2) == "cost"))

# save the workbook
saveWorkbook(wb, "Fortunat.xlsx", overwrite  = TRUE)

Is this given the result that you want?

[Fortunat]

Hello @vedoa , thank you very much for your R script proposal which solved my problem.