Writing a formula to subtract row value(s) from all column values in R-CodePudding

I need help writing an if then statement in R. I need to subtract the values from row 6 from each cell from every column.

If after the subtraction the value is greater than 0, I need the subtracted value reported. If after the substraction the value is less than 0 (negative value), I need the formula to report a 0. I need this done for every column, accross nearly 14,000 columns. I assume I need to use if_else and then a loop to execute? Any help would be hugeley appreciated!!

INPUT

         A     B      C
1     1345     0    100
2     1300    24      3
3       89    25      5
4    19000    90    200
5        0   100     10
6      100     9      7

DESIRED OUTPUT

           A      B     C
1       1245    0    93
2       1200    15   16
3          0    16    0
4      18900    81  193
5          0    91    3
6        100    9     7

CodePudding user response：

You could do something like this:

library(tidyverse)

input |>
  mutate(across(everything(), ~ifelse(row_number() == 6, ., .-.[6])),
         across(everything(), ~ifelse(. < 0, 0, .)))
#> # A tibble: 6 x 3
#>       A     B     C
#>   <dbl> <dbl> <dbl>
#> 1  1245     0    93
#> 2  1200    15     0
#> 3     0    16     0
#> 4 18900    81   193
#> 5     0    91     3
#> 6   100     9     7

or with base R


apply(input, 2, \(x)ifelse(x == x[6], x, x-x[6])) |>
  apply(2, \(x) ifelse(x < 0, 0, x)) |>
  as.data.frame()
#>       A  B   C
#> 1  1245  0  93
#> 2  1200 15   0
#> 3     0 16   0
#> 4 18900 81 193
#> 5     0 91   3
#> 6   100  9   7