How to select lowest value or remove duplicates after using group_by function in r

Question

I've looked at a number of threads and can't quite find what I'm looking for. I have a dataset with multiple ids and dates like the below.

id  date       code
1   2000-10-08  690
1   2000-10-08  75
1   2000-10-08  35
1   2001-01-01  315
1   2001-01-01  70
1   2008-09-05  690
1   2008-09-05  5
1   2008-09-05  60
2   2006-02-01  188
2   2006-02-01  198
2   2006-02-01  555
2   2006-02-01  690
3   2010-10-10  120
3   2010-10-10  75
3   2010-10-10  25

I don't want duplicate dates per id and want to select this based on lowest code value so it would end up like this:

id  date       code
1   2000-10-08  35
1   2001-01-01  70
1   2008-09-05  5
2   2006-02-01  188
3   2010-10-10  25

I've used the group_by function so that it treats data by id and date:

df %>%
 group_by(id, date) %>%
 arrange(code)

However, I'm struggling to work out what code to use so as to now keep only the lowest value of each id/date combination.

Could anyone help me with this?

Thanks

The question is not a dup of https://stackoverflow.com/questions/9723208/aggregate-summarize-multiple-variables-per-group-e-g-sum-mean because aggregation is only one way (maybe not the best) to answer the question. — Ista, Jan 04 '19 at 18:52
@Ista Agree. What about https://stackoverflow.com/questions/24070714/extract-row-corresponding-to-minimum-value-of-a-variable-by-group — markus, Jan 04 '19 at 19:12

score 1 · Answer 1 · answered Jan 04 '19 at 18:20

data.table approach

library( data.table )
dt[, .( min = min( code ) ), by = .( id, date )]

#    id       date min
# 1:  1 2000-10-08  35
# 2:  1 2001-01-01  70
# 3:  1 2008-09-05   5
# 4:  2 2006-02-01 188
# 5:  3 2010-10-10  25

sample data

dt <- fread("id  date       code
1   2000-10-08  690
1   2000-10-08  75
1   2000-10-08  35
1   2001-01-01  315
1   2001-01-01  70
1   2008-09-05  690
1   2008-09-05  5
1   2008-09-05  60
2   2006-02-01  188
2   2006-02-01  198
2   2006-02-01  555
2   2006-02-01  690
3   2010-10-10  120
3   2010-10-10  75
3   2010-10-10  25", header = TRUE)

score 1 · Accepted Answer · answered Jan 04 '19 at 18:30

1

With tidyverse you can do:

library(tidyverse)
dt %>% 
    group_by(id, date) %>% 
    summarise(code = min(code))

  id       date   x
1  1 2000-10-08  35
2  1 2001-01-01  70
3  1 2008-09-05   5
4  2 2006-02-01 188
5  3 2010-10-10  25

answered Jan 04 '19 at 18:30

YOLO

20,181
5
20
40

akrun · Answer 3 · 2019-01-04T19:15:45.750

From a tidyverse perspective, if there are many columns and need the row based on the minimum value of 'code' for each group, it may be better to filter with a logical condition

library(tidyverse)
df %>%
   group_by(id, date) %>%
   filter(code == min(code))
# A tibble: 5 x 3
# Groups:   id, date [5]
#     id date        code
#  <int> <chr>      <int>
#1     1 2000-10-08    35
#2     1 2001-01-01    70
#3     1 2008-09-05     5
#4     2 2006-02-01   188
#5     3 2010-10-10    25

If there are ties for min value for each group and want only the first occurence of the min 'code'

df %>%
   group_by(id, date) %>%
   slice(which.min(code))

Or another option is using top_n

df %>%
   group_by(id, date) %>%
   top_n(1, -code)

Using aggregate from base R

aggregate(code ~ id + date, df, min)
#  id       date code
#1  1 2000-10-08   35
#2  1 2001-01-01   70
#3  2 2006-02-01  188
#4  1 2008-09-05    5
#5  3 2010-10-10   25

data

df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 
2L, 2L, 3L, 3L, 3L), date = c("2000-10-08", "2000-10-08", "2000-10-08", 
"2001-01-01", "2001-01-01", "2008-09-05", "2008-09-05", "2008-09-05", 
"2006-02-01", "2006-02-01", "2006-02-01", "2006-02-01", "2010-10-10", 
 "2010-10-10", "2010-10-10"), code = c(690L, 75L, 35L, 315L, 70L,
 690L, 5L, 60L, 188L, 198L, 555L, 690L, 120L, 75L, 25L)), 
 class = "data.frame", row.names = c(NA, -15L))

score 1 · Answer 4 · answered Jan 04 '19 at 18:50

Just add slice(1) to the end of your code:

df %>%
 group_by(id, date) %>%
    arrange(code) %>%
    slice(1)
## # A tibble: 5 x 3
## # Groups:   id, date [5]
##      id date        code
##   <int> <fct>      <int>
## 1     1 2000-10-08    35
## 2     1 2001-01-01    70
## 3     1 2008-09-05     5
## 4     2 2006-02-01   188
## 5     3 2010-10-10    25

How to select lowest value or remove duplicates after using group_by function in r

4 Answers4

data