Appending of the Quarterly Data into the cpi_stats (master spreadsheet)

Hi @james-t-bila,

Sorry this has taken me a while to get to. The openxlsx R package is probably the best one to use in this case because it allows reading and writing to excel formatted files. There are others out there but openxlsx has worked the best for me. Take a look at the introduction to openxlsx materials for a bit more information.

With openxlsx, you're first step is to identify where in the excel file you want to edit, for this you need:

The name of the xlsx formatted file and path to where it is
The name of the sheet in the xlsx file you want to edit
The column and row indices of cell(s) you would like to edit. Note that for inserting tables you specify the row and column of the top left cell of your table.

You can extract data from an excel file using something like this code:

quarterly_data <- openxlsx::read.xlsx(
    xlsxFile = file.path("insert", "path", "to", "xlsx", "file", "here", "filename.xlsx"),
    sheet = ["name" or index of sheet - defaults to 1],
    startRow = [index of row to start on]
)

Note that you can use the rows and cols parameters for the read.xlsx() to more specifically target which rows and columns to read by their index.

Once extracted, you'll want to identify the index of the column to edit - try something like:

current_quarter <- "Dec08"
index_of_column_to_edit <- which(colnames(quarterly_data) == current_quarter)

You'll need to identify the row to edit in a similar way - by finding the matching commodity name in the commodity column. Something like:

# Find row index for specific commodity of interest
# Using grep to allow for not having the exact commodity name - use which() if you have exact name
commodity <- "bananas"
index_of_row_to_edit <- grep(
    pattern = commodity, 
    x = quarterly_data$[insert name of column with commodity names here]
)

# Check only single row was identified
if(length(index_of_row_to_edit) > 1){
    index_of_row_to_edit <- index_of_row_to_edit[1]
    warn(paste0("Multiple row indices were identified based on the commodity pattern provided (", commodity, "). First index selected to update."))
}

With the row and column indices of where you would like to edit in the xlsx file. You can update using similar code to the following:

# Load the excel file
workbook <-  openxlsx::loadWorkbook(file.path("insert", "path", "to", "xlsx", "file", "here", "filename.xlsx"))

# Define data to update in table <- note this could be a matrix/dataframe of values instead of single value
my_updated_price_per_kilo <- 55

# Edit specific cells within a specific sheet
openxlsx::writeData(
    workbook, 
    sheet = [insert sheet "name" or index here],
    startCol = index_of_column_to_edit, 
    startRow = index_of_row_to_edit,
    x = updated_data_to_insert
)

# Save the changes to the excel files
openxlsx::saveWorkbook(
    workbook, 
    file = file.path("insert", "path", "to", "xlsx", "file", "here", "filename.xlsx"),
    overwrite = TRUE
)

Let me know if the above is useful, and feel free add more comments here with any issues or questions.

Thanks and apologies again for not getting back to you sooner.

Joe

Vanuatu-National-Statistics-Office / vnso-RAP-consumerPriceIndexStats-materials

Appending of the Quarterly Data into the cpi_stats (master spreadsheet) #20