How to get the first values by group

Question

I would like to retrieve the first non-zero values from all groups.

DataFrame

Group  values Days
A      0      12 
A      0      15
A      30     18
A      60     20
B      0      2
B      15     10
B      16     20
B      20     30
C      0      5
C      30     10
C      70     15

Output

Group Values  Days
A     30      18
B     15      10
C     30      10

Does this answer your question? [Get first and last values per group – dplyr group\_by with last() and first()](https://stackoverflow.com/questions/42654219/get-first-and-last-values-per-group-dplyr-group-by-with-last-and-first) — user438383, Jun 28 '23 at 12:57

score 3 · Answer 1 · answered Jun 28 '23 at 12:57

You can do

> subset(df, ave(values != 0, Group, FUN = cumsum) == 1)
   Group values Days
3      A     30   18
6      B     15   10
10     C     30   10

or

> aggregate(. ~ Group, subset(df, values != 0), head, 1)
  Group values Days
1     A     30   18
2     B     15   10
3     C     30   10

score 3 · Accepted Answer · answered Jun 28 '23 at 12:59

3

data.table approach

library(data.table)
setDT(df)[!values == 0, .SD[1], Group]
#    Group values Days
# 1:     A     30   18
# 2:     B     15   10
# 3:     C     30   10

answered Jun 28 '23 at 12:59

Wimpel

26,031
1
20
37

score 3 · Answer 3 · answered Jun 28 '23 at 12:59

Two subset`'s.

df |> subset(values > 0) |> subset(!duplicated(Group)) 
#    Group values Days
# 3      A     30   18
# 6      B     15   10
# 10     C     30   10

Data:

df <- structure(list(Group = c("A", "A", "A", "A", "B", "B", "B", "B", 
"C", "C", "C"), values = c(0L, 0L, 30L, 60L, 0L, 15L, 16L, 20L, 
0L, 30L, 70L), Days = c(12L, 15L, 18L, 20L, 2L, 10L, 20L, 30L, 
5L, 10L, 15L)), class = "data.frame", row.names = c(NA, -11L))

score 2 · Answer 4 · answered Jun 28 '23 at 12:54

You could first filter the rows with values 0 and after that slice the first value .by you Group like this:

library(dplyr)

df %>%
  filter(values != 0) %>%
  slice(1, .by = Group)
#>   Group values Days
#> 1     A     30   18
#> 2     B     15   10
#> 3     C     30   10

^{Created on 2023-06-28 with reprex v2.0.2}

score 2 · Answer 5 · answered Jun 28 '23 at 13:00

Alternatively we can try

library(dplyr) 

df %>% group_by(Group) %>% mutate(val=cumall(lag(!values))) %>% 
filter(is.na(val)) %>% slice_tail(n=1) %>% select(-val)

# ouuput

# A tibble: 3 × 3
# Groups:   Group [3]
  Group values  Days
  <chr>  <dbl> <dbl>
1 A         30    18
2 B         15    10
3 C         30    10

How to get the first values by group

5 Answers5