R Row and Column Totals Example
This example shows how the Execute R action can be used to add row and column totals to a table, replacing any null values in the table with zeros. Given an input table that looks like this…
… the example produces a result table that looks like this.
Note that the input table should have an initial column which names each of the rows, however it may have an unknown number of additional columns.
Using the Execute SQL action, we might achieve this using three separate looping constructs. The first loop would replace the nulls with zeros in each of the value columns. The second loop would sum each of the columns, and the third loop would sum each of the rows, generating dynamic SQL to reference each of the columns.
Using the Execute R action, we can achieve the same results using the following R statements. The source table is provided in a Table parameter named Table1, and the result table is stored in a Table dataset named Result.
The comments above each R statement explain its purpose.
/* Create a vector of column names, excluding the first column */
vnames <- Table1_col_names_[-1]
/* Create a data frame which stores each column in vnames as a numeric vector */
df <- data.frame(lapply(mget(vnames), as.numeric))
/* Replace any null values in the data frame with zeros */
df[is.na(df)] <- 0
/* Add row totals column named TOTALS to the data frame */
df$TOTALS <- rowSums(df)
/* Create a new data frame containing the column sums for the data frame */
dfTot <- colSums(df)
/* Append the column sums data frame to the data frame of numeric values *.
df <- rbind(df, dfTot)
/* Create a character vector for the first table column */
col1 <- get(Table1_col_names_[1])
/* Add an entry named TOTALS to the end of the column */
col1 <- c(col1, "TOTALS")
/* Create a new data frame for the result table using col1 and the numeric data stored in df1 */
ResultTable <- data.frame(col1, df)
/* Set the column name for col1 in the result table to the name of the first column in the source table */
colnames(ResultTable)[1] <- Table1_col_names_[1]
/* Remove the ‘Table1_’ prefixes from the ResultTable dataset’s column names */
names(ResultTable) <- gsub("^.*?_", "", names(ResultTable))
/* Copy the Table1 metadata to the ResultTable dataset, using int(4) as the type and length of the TOTALS column */
ResultTable_col_types_ <- c(Table1_col_types_, "int")
ResultTable_col_lengths_ <- c(Table1_col_lengths_, 4)