group by and find first change in value of group of columns in R

user_14550741 0
Viewed 130

I have a data frame in R which is grouped by id. The column "value3" always has positive values. I would like to obtain the first row in which one of the other columns has non-zero entries for each id.

For instance:

Given the following data set

structure(list(id = c(1, 1, 1, 4, 4, 4, 4, 6, 6, 6), value1 = c(0,10, 10, 0, 20, 20, 8, 0, 10, 10), value2 = c(0, 30, 10, 0, 0,0, 10, 20, 20, 15), value3 = c(10, 10, 10, 10, 10, 10, 10, 10,10, 10)), class= "data.frame", row.names = c(NA, -10L))

the desired result would be

structure(list(resid = c(1, 4, 6), resvalue1 = c(10, 20, 0),resvalue2 = c(30, 0, 20), resvalue3 = c(10, 10, 10)), class = "data.frame", row.names = c(NA,-3L))

I have been using dplyr to slice and select, but I cannot figure out this particular problem. Any help is appreciated.

r
2 Answers

After grouping by 'id', select the data columns 'value1', 'value2', get the rowSums of logical matrix i.e. > 0, convert it to logical vector and get the index of first TRUE value (which) to slice the row

library(dplyr)    
df1 %>% 
    group_by(id) %>%
    slice(which(rowSums(select(cur_data(), value1:value2) > 0) > 0)[1]) %>%
    ungroup

-output

# A tibble: 3 x 4
#     id value1 value2 value3
#  <dbl>  <dbl>  <dbl>  <dbl>
#1     1     10     30     10
#2     4     20      0     10
#3     6      0     20     10
user_37322710

Alternative using data.table:

library(data.table)

input <- structure(list(id = c(1, 1, 1, 4, 4, 4, 4, 6, 6, 6), value1 = c(0,10, 10, 0, 20, 20, 8, 0, 10, 10), value2 = c(0, 30, 10, 0, 0,0, 10, 20, 20, 15), value3 = c(10, 10, 10, 10, 10, 10, 10, 10,10, 10)), class= "data.frame", row.names = c(NA, -10L))

dt <- as.data.table(input)

output <- dt[, any(value1:value2 != 0), by = id:value3][V1 == TRUE, .SD[1], by = id, .SDcols = !V1:V1]

That yields:

   id value1 value2 value3
1:  1     10     30     10
2:  4     20      0     10
3:  6      0     20     10
user_7731590
Related