Sum up columns until the cell in adjacent column is NA and then divide each of them by the preceding column[SOLVED]

I'm trying to add up columns in a data table until the adjacent column has a value of NA. For example, if I had the table:

I would only add up to row 2 in column 1, as row 3 in column 2 is NA, and row 1 in column 2. I would then divide each of those columns by the preceding column. I'd also want to ignore the date. Is there a way to do this without hard coding it? This would be the desired end row:

I tried using "adorn_table" and excluding NAs, but that didn't seem to do it.

For the original question, try:

library(tidyverse)

# create made-up data
df <- tibble(
  x = c(2, 1, 3),
  y = c(12, 1, NA)
)

df |>
  filter(cumsum(is.na(y)) == 0) |>  # This will get rid of all rows starting from the first time when y is NA
  summarize(                        # add up the values of x
    s = sum(x)
  ) |>
  pull(s)                        

For the clarified question, I'm assuming the number of columns is arbitrary.

library(tidyverse)

# create a function to do the work
f <- function(.x, .y) {
  keep_rows <- cumsum(is.na(.y)) == 0
  sum(.x[keep_rows])
}

# create made-up data
df <- tibble(
  date = 1:3, # dates in your case, I'm keeping it simple
  x = c(2, 1, 3),
  y = c(12, 1, NA),
  z = c(12, NA, NA)
)

total_row <- map2(
 df |> select(x:z),  # select the columns being summed
 df |> select(y, z1 = z, z2 = z), # If you want the last column to depend on itself, you'll need to do something like this
  f
)

bind_rows(df |>
  mutate(date = as.character(date)), 
  c(date = "Total", total_row)
)

1) Define a function sumToNA which sums x to the position prior to the first NA in y. If there are no NAs it sums x entirely.

Then mapply that function to DF without column 1 and DF without the first two columns but the last column twice. No packages are used.

sumToNA <- function(x, y) sum(x[!cummax(is.na(y))], na.rm = TRUE)
n <- ncol(DF)
rbind(DF, c("Total", mapply(sumToNA, DF[2:n], DF[c(3:n, n)])))
##         DATE 1    2    3
## 1 07/01/2022 2   12   12
## 2 08/01/2022 1    1 <NA>
## 3 09/01/2022 3 <NA> <NA>
## 4      Total 3   12   12

2) Define a function NAfwd which takes a vector and returns it but with all NA's after the first NA and converts all remaining non-NA's to 0. Then add DF[-1] to the processed DF without the first two columns and the last column twice after transforming each column with NAfwd. Then use rowSums to sum the rows of that and insert the result as a new row.

NAfwd <- \(z) 0 * Reduce(\(x, y) if (is.na(x)) x else y, z, acc = TRUE)

n <- ncol(DF)
rbind(DF, c("Total", colSums(DF[-1] + apply(DF[c(3:n,n)], 2, NAfwd), na.rm=TRUE)))
##         DATE 1    2    3
## 1 07/01/2022 2   12   12
## 2 08/01/2022 1    1 <NA>
## 3 09/01/2022 3 <NA> <NA>
## 4      Total 3   12   12

Note

DF <- data.frame(
  DATE = c("07/01/2022", "08/01/2022", "09/01/2022"),
  `1` = c(2L, 1L, 3L),
  `2` = c(12L, 1L, NA),
  `3` = c(12L, NA, NA),
  check.names = FALSE
)