Add a row to data frame containing numeric and non-numeric data

Question

I have a series of data frames that I need to add a row for totals to before I print them out using kable. The frames contain numeric and non-numeric data. Maybe it's just a case of Sunday slow brain, but I can't figure out a solution to this other than doing it the long way of making a temp data frame with the totals then manually inserting the values.

apps_combined <- data.frame(
  UAA_mau = rep("UAA", 4L),
  UAA_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAA_2022 = c(65L, 2476L, 894L, 150L),
  UAA_2023 = c(68L, 2691L, 1145L, 164L),
  UAA_percent = c(4.61538461538462, 8.68336025848142, 28.076062639821, 9.33333333333333),
  UAF_mau = rep("UAF", 4L),
  UAF_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAF_2022 = c(19L, 1165L, 281L, 304L),
  UAF_2023 = c(38L, 1161L, 318L, 269L),
  UAF_percent = c(100, -0.343347639484979, 13.1672597864769, -11.5131578947368),
  UAS_mau = rep("UAS", 4L),
  UAS_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAS_2022 = c(12L, 328L, 110L, 39L),
  UAS_2023 = c(8L, 327L, 133L, 33L),
  UAS_percent = c(-33.3333333333333, -0.304878048780488, 20.9090909090909, -15.3846153846154),
  UAT_mau = rep("UAT", 4L),
  UAT_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAT_2022 = c(96L, 3969L, 1285L, 493L),
  UAT_2023 = c(114L, 4179L, 1596L, 466L),
  UAT_percent = c(18.75, 5.29100529100529, 24.2023346303502, -5.47667342799189)
)

I have a verbose solution of making a temp data frame and using add_row to assign the text values, and numeric values, but I feel like there is a more elegant solution to this.

temp <- colSums(apps_combined[,c("UAA_2022", "UAA_2023", "UAF_2022", "UAF_2023", "UAS_2022", "UAS_2023", "UAT_2022", "UAT_2023")])
apps_combined_test <- apps_combined %>%
    add_row("UAA_mau" = "UAA","UAA_type_ind" = "total", "UAA_2022" = temp[[1]], "UAA_2023" = temp[[2]], "UAA_percent" = ((temp[[2]]-temp[[1]])/temp[[1]]) * 100, 
                 "UAF_mau" = "UAF","UAF_type_ind" = "total", "UAF_2022" = temp[[3]], "UAF_2023" = temp[[4]], "UAF_percent" = ((temp[[4]]-temp[[3]])/temp[[3]]) * 100,
                 "UAS_mau" = "UAS","UAS_type_ind" = "total", "UAS_2022" = temp[[5]], "UAS_2023" = temp[[6]], "UAS_percent" = ((temp[[6]]-temp[[5]])/temp[[5]]) * 100,
                 "UAT_mau" = "UAT","UAT_type_ind" = "total", "UAT_2022" = temp[[7]], "UAT_2023" = temp[[8]], "UAT_percent" = ((temp[[8]]-temp[[7]])/temp[[7]]) * 100)

If this is the best solution then I guess I'll just have to make it a function to make code easier to maintain.

Answer 1

Only ever so slightly more elegant solution using base R that employs grep() for calling regular expressions. In the following, "df" is a dataframe created from your dput():

# Create a blank row at the end of df
df[nrow(df)+1, ] <- NA

# Get last value in columns where colnames() ends in "mau"
# and add to last row
df[nrow(df), grep("*mau$", colnames(df))] <- df[nrow(df)-1, grep("*mau$", colnames(df))]

# Add string "Total" to last row where colnames() end in "ind"
df[nrow(df), grep("*ind$", colnames(df))] <- "Total"

# Sum columns where colnames end with a number
df[nrow(df), grep("*[0-9]$", colnames(df))] <-
   colSums(df[grep("*[0-9]$", colnames(df))], na.rm=TRUE)

# Calculate % (using your formula)
df[nrow(df),grep("*percent$", colnames(df))] <- 
  ((df[nrow(df),grep("*percent$", colnames(df))-1] -
  df[nrow(df),grep("*percent$", colnames(df))-2]) /
  df[nrow(df),grep("*percent$", colnames(df))-2]) * 100

If you're not familiar with these methods, I recommend running each component of each line separately to see what's going on. In short, these methods return vectors of columns and uses those to calculate values in the last row nrow(df).