library(tidyr)
# Create a data frame in long format
long_data <- data.frame(
digits = rep(1:5, each = 3),
category = rep(c("A", "B", "C"), times = 5),
value = c(10, 20, 30, 15, 25, 35, 12, 22, 32, 18, 28, 38, 14, 24, 34),
color_code = c("red", "red", "red", "red", "blue", "blue", "blue", "blue", "blue", "green", "green", "green", "green", "green", "green")
)
I have a data_frame in a long format where each value is color_coded. I found it possible to color code this output in Excel using openxlsx library by applying conditional formatting based on color_code. However, I cannot figure out how to do it if I need the final Excel output to be in the wide format but still color-coded.
I wasn’t able to find a solution for this. Please help.
You can pivot your data from long to wide in R and then apply your conditional color formatting in Excel after pivoting. you can apply conditional formatting by setting a rule type using “Format only cells that contain” and for each text value that you have (ie red) format accordingly. can you describe in greater detail at which step your color coding is not working as expected?
Are you suggesting to write a code for each value and tell Excel what color should it be?That can work for this table, but the actual data is much bigger.
What is the wide format you are expecting?
let’s say Digits as rows, category as columns, value as value.
in R, there are several packages that you can use to get the rgb value for the text values (ie red, yellow, blue) that you have. Create an additional column with the rgb values. then in excel, you can probably use those rgb values to format your cells…