Set value to zero if condition is fulfilled for each column separately

user_16250224 0
Viewed 178

If there is an entry >= 10, I try to set all other values for that ID to zero for that specific year in a data table. All values stay the same if there is no value bigger or equal to 10. If there are more than one value bigger than 10, only the biggest entry should be kept.

Here I have a sample data table:

library(data.table)

data = data.table(
  ID = c("a1", "a2", "a2", "a1", "a2", "a1", "a1"),
  "2018" = c(3,5,11,3,9,22,6),
  "2019" = c(3,5,6,21,1,4,0),
  "2020" = c(0,4,13,9,16,7,9),
  "2021" = c(4,0,3,8,5,4,6))

I tried it with a for loop, but I wasn't able to do it with two dimensions. The desired outcome would look like that:

solution <- data.table(
  ID = c("a1", "a2", "a2", "a1", "a2", "a1", "a1"),
  "2018" = c(0,0,11,0,0,22,0),
  "2019" = c(0,5,6,21,1,0,0),
  "2020" = c(0,0,0,9,16,7,9),
  "2021" = c(4,0,3,8,5,4,6))
data.table r
2 Answers

We can use

library(data.table)
nm1 <- names(data)[-1]
data[, (nm1) := lapply(.SD, function(x) if(any(x >= 10)) 
       replace(x, x != max(x), 0) else x), ID]

-output

> data
   ID 2018 2019 2020 2021
1: a1    0    0    0    4
2: a2    0    5    0    0
3: a2   11    6    0    3
4: a1    0   21    9    8
5: a2    0    1   16    5
6: a1   22    0    7    4
7: a1    0    0    9    6
user_37322710

If you can accept a solution with tibble instead of data.table,

library(tidyverse)

data = tibble(
  ID = c("a1", "a2", "a2", "a1", "a2", "a1", "a1"),
  "2018" = c(3,5,11,3,9,22,6),
  "2019" = c(3,5,6,21,1,4,0),
  "2020" = c(0,4,13,9,16,7,9),
  "2021" = c(4,0,3,8,5,4,6))

data %>% 
  mutate(row_id = 1:n()) %>%
  pivot_longer(-c(row_id, ID), names_to = "year") %>%
  group_by(year, ID) %>%
  mutate(
    value = if (any(value >= 10)) {
      if_else(1:length(value) == which.max(value), value, 0)
    } else {
      value
    }) %>%
  ungroup() %>%
  pivot_wider(names_from = year)
#> # A tibble: 7 x 6
#>   ID    row_id `2018` `2019` `2020` `2021`
#>   <chr>  <int>  <dbl>  <dbl>  <dbl>  <dbl>
#> 1 a1         1      0      0      0      4
#> 2 a2         2      0      5      0      0
#> 3 a2         3     11      6      0      3
#> 4 a1         4      0     21      9      8
#> 5 a2         5      0      1     16      5
#> 6 a1         6     22      0      7      4
#> 7 a1         7      0      0      9      6

Created on 2021-08-19 by the reprex package (v1.0.0)

user_100654730
Related