Issues with write.xlsx function

Question

I am still novice regarding R programming and I have some issues with the write.xlsx function. To sum up : I have 70 data frames (each with 9 values, i.e. mean, max, min, std deviation,...) named C01->C10, D01->D10, E01-E10, F01->F10, G01->G10, H01->H10, I01->I10 and 70 png files associated (containing each a plot of a data frame). Each plot represents thousands of values. All the statistics on these values are put into a data frame.

    res_C01 <- data.frame(mean_C01, max_value_C01, min_value_C01, hourly_dose_added_C01, hours_per_year, hours_per_year_with_values_C01, Availability_of_the_device_C01, Onavailability_of_the_device_C01, Sum_Avail_Onavail_C01)
    res_C02 <- data.frame(mean_C02, max_value_C02, min_value_C02, hourly_dose_added_C02, hours_per_year, hours_per_year_with_values_C02, Availability_of_the_device_C02, Onavailability_of_the_device_C02, Sum_Avail_Onavail_C02)

    png(file = "IMA_C01.png", width = 800, height = 700); plot(E_C01,F_C01, col="black", cex=10, lwd = 6, pch=19, xlab="time (day)", ylab="value (Sv/h)"); legend("topright", legend = "value IMA_C01", col = "black", lwd = 2, cex = 0.8); title("IMA_C01", outer = T, line = -3); abline(h = mean(F_C01), col="blue", lwd = 4); dev.off()
    png(file = "IMA_C02.png", width = 800, height = 700); plot(E_C02,F_C02, col="black", cex=10, lwd = 6, pch=19, xlab="time (day)", ylab="value (Sv/h)"); legend("topright", legend = "value IMA_C02", col = "black", lwd = 2, cex = 0.8); title("IMA_C02", outer = T, line = -3); abline(h = mean(F_C02), col="blue", lwd = 4); dev.off()

Now, I would like to write a xlsx file. In the beginning, I was using the write.xlsx function like this:

    write.xlsx(res_C01, file="IMA_Results.xlsx", sheetName="IMA_C01_results", col.names=TRUE, row.names=FALSE, append=FALSE)
    write.xlsx(res_C02, file="IMA_Results.xlsx", sheetName="IMA_C02_results", col.names=TRUE, row.names=FALSE, append=TRUE)

However, obtaining a final Excel file with 70 sheets will be too complicated. So I was wondering whether it is possible to write an Excel file with a unique sheet containing 70 rows and 9 columns. In the first column of this sheet, each row would be named C01, C02, C03,... C10, etc... The nine columns would also have a particular name (mean for the 1st column, max for the 2nd column, min for the 3rd column, ...).

Does anyone could help me facing this issue ? It would be very helpful :)

Please create a simple reproducible example. write.xlsx can come from 2 packages, so which package are you using, and add a dput(head(res_C01, 10)) to the question. Or if the data is confidential, a data.frame that simulates this data. — phiver

phiver phiver · Accepted Answer · 2020-05-15T14:03:30

Assuming all the system data.frames end with the pattern "System", you can get all of them into 1 data.frame like below and export that one to excel with write.xlsx.

# find all variables that end in "System". If they start with "res_" use "^res" 
# in the pattern part
var_names <- ls(envir = globalenv(), pattern = "System$")

# create a list of the data.frames    
space <- mget(var_names)

# rename the column names
cols <- c("planet1", "planet2", "planet3", "planet4", "planet5", "planet6", "planet7", "planet8", "planet9")
space <- lapply(space, setNames, cols)

# bind to data.frame
space_df <- do.call(rbind, space)
space_df
               planet1 planet2 planet3 planet4 planet5 planet6 planet7 planet8 planet9
Keppler_System     100     200     300     400     500     600     700     800     900
Solar_System        10      20      30      40      50      60      70      80      90

Issues with write.xlsx function

1 Answers