aj1s
commented
3 years ago Attempting modification of plot_caland.r to remove Java dependency, replacing {XLConnect} with {readxl} (and, if need be, {writexl}) which have no external dependencies.
Note that if {readxl} functions prove too difficult to splice into the for loops of plot_caland.r – the pkg was intended to be used instead with base:: lapply() – other potential options include {openxlsx} and {rio}. See: []https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames
The following indicates: (1) each line in plot_caland.r that calls an ‘XLConnect’ function. (All occur within for loops). (2) the closest matching function call in ‘readxl’.
# Line 333: Load Excel workbooks
# using XLConnect::loadWorkbook(), then readxl::read_xls(). Usage of the latter: read_xls(path, sheet = NULL, ...), where path must be a string.
scen_wrkbk = loadWorkbook(data_file)
scen_wrkbk = read_xls(data_file)
# Line 336: List all sheet names in an Excel workbook
# using XLConnect::loadWorkbook(), then readxl::read_xls().
scen_sheets = getSheets(scen_wrkbk)
scen_sheets <- excel_sheets(data_file)
# Lines 343: Read data from a sheet in an Excel workbook (to populate ‘scen_df_list’)
# using XLConnect::loadWorkbook(), then readxl::read_xls().
scen_df_list[[i]] <- readWorksheet(scen_wrkbk, i, startRow = 1)
scen_df_list[[i]] <- read_xls(data_file, sheet = i)
# Lines 570 AND 821: Read data from a sheet in an Excel workbook
# using XLConnect::loadWorkbook(), then readxl::read_xls().
temp_df <- readWorksheet(scen_wrkbk, lind, startRow = 1)
temp_df <- read_xls(data_file, lind)
# Lines 1648 AND 1775: Read data from a sheet in an Excel workbook
# using XLConnect::loadWorkbook(), then readxl::read_xls().
BC_df <- readWorksheet(scen_wrkbk, i+2, startRow = 1)
BC_df <- read_xls(data_file, i+2)(The first argument of functions readxl::read_xls() and readxl::excelsheets() is normally the directory path to the xlsx/xls file. Applied to a variable in a for loop, the path must still be a string, so in the replacements above the {readxl} functions are pointed directly at data_file (i.e., side-stepping scen_wrkbk).)
The nested for loops that read in the data (lines 230 – 1891) were extracted from the {XLConnect} and {readxl} script versions, then "de-looped" - i.e., modified into two respective scripts allowing for comparison of incremental results between the packages when run for a single iteration. Here, the historical and alternative scenario CALAND.r outputs were modified so each xls contained only two sheets. The run script:
# this enables java to use up to 16GB of memory for reading and writing excel files
options(java.parameters = "-d64 -Xms8g -Xmx64g" ) # originally set to "-Xmx8g"
#Install and load {readxl} and {writexl}
install.packages(c("readxl", “writexl”))
library(readxl)
library(writexl)
#load the write_caland_inputs function to memory
source("plot_caland_readxl.r")
plot_caland(
scen_fnames = c("Proto_Hist_TwoSheets.xls",
"Proto_Alt_TwoSheets.xls"),
scen_snames = c("Baseline","A"),
data_dir = "./outputs/2021_05_14_carbon_input_nwland_testB3_devAll_noOcean",
reg = c("All_region"),
lt = c("All_land", "Barren", "Cultivated", "Desert", "Developed_all", "Forest", "Grassland", "Ice", "Meadow", "Riparian", "Savanna", "Shrubland", "Sparse", "Water", "Wetland", "Woodland"),
own = c("All_own"),
figdir = "figures"
)Within this single iteration, both versions return identical dataframes, respectively, for scen_df_list[[i]], temp_df, and BC_df.
Returning to plot_caland_readxl.r, the full script relying on {readxl}, its run script was modified to assign the original test scenario workbooks (given as all worksheets are required for plotting that begins on line 1895):
scen_fnames = c("NWL_Proto_v1_Hist_protoA_hist_output_mean_BC1_NR120.xls",
"NWL_Proto_v1_Alt_A_protoA_hist_output_mean_BC1_NR120.xls")On a fresh reboot, this run script was run from cmd line, which returned:
Error: filepath: C:\Wk_R\USCA_NCS\NWLAND\outputs\2021_05_14_carbon_input_nwland_testB3_devAll_noOcean\NWL_Proto_v1_Hist_protoA_hist_output_mean_BC1_NR120.xls
libxls error: Unable to allocate memoryAs evident in this thread: []https://github.com/tidyverse/readxl/issues/598, many issues result in this error.
Given the persistent memory issues, we may need to consider the lift involved in adapting plot_caland_readxl.r to read in individual csvs rather than workbooks.
sbassett
plot_caland.r was run using 45f9a2611e265b0308ff17d614ffd3d40ccecf26. Received the following error, whether run from RStudio or command line:
Error: OutOfMemoryError (Java): GC overhead limit exceeded