CODEWITHSUNDEEP
rdate
PriyamK
PriyamK

Reputation: 141

Selecting distinct entries based on specific variables in R

I want to select distinct entries for my dataset based on two specific variables. I may, in fact, like to create a subset and do analysis using each subset.

The data set looks like this

id <- c(3,3,6,6,4,4,3,3)
date <- c("2017-1-1", "2017-3-3", "2017-4-3", "2017-4-7", "2017-10-1", "2017-11-1", "2018-3-1", "2018-4-3")
date_cat <- c(1,1,1,1,2,2,3,3)
measurement <- c(10, 13, 14,13, 12, 11, 14, 17)
myData <- data.frame(id, date, date_cat, measurement)
myData

myData$date1 <- as.Date(myData$date)
myData

  id      date date_cat measurement      date1
1  3  2017-1-1        1          10 2017-01-01
2  3  2017-3-3        1          13 2017-03-03
3  6  2017-4-3        1          14 2017-04-03
4  6  2017-4-7        1          13 2017-04-07
5  4 2017-10-1        2          12 2017-10-01
6  4 2017-11-1        2          11 2017-11-01
7  3  2018-3-1        3          14 2018-03-01
8  3  2018-4-3        3          17 2018-04-03

#select the last date for the ID in each date category.
Here date_cat is the date category and date1 is date formatted as date. How can I get the last date for each ID in each date_category?

I want my data to show up as

  id      date date_cat measurement      date1
1  3  2017-3-3        1          13 2017-03-03
2  6  2017-4-7        1          13 2017-04-07
3  4 2017-11-1        2          11 2017-11-01
4  3  2018-4-3        3          17 2018-04-03

Thanks!

Upvotes: 1

Views: 59

Answers (4)

PriyamK
PriyamK

Reputation: 141

Using dplyr:

myData %>%
  group_by(id,date_cat) %>%
  top_n(1,date)

Upvotes: 0

ThomasIsCoding
ThomasIsCoding

Reputation: 101129

I am not sure if you want something like below

subset(myData,ave(date1,id,date_cat,FUN = function(x) tail(sort(x),1))==date1)

which gives

> subset(myData,ave(date1,id,date_cat,FUN = function(x) tail(sort(x),1))==date1)
  id      date date_cat measurement      date1
2  3  2017-3-3        1          13 2017-03-03
4  6  2017-4-7        1          13 2017-04-07
6  4 2017-11-1        2          11 2017-11-01
8  3  2018-4-3        3          17 2018-04-03

Upvotes: 0

akrun
akrun

Reputation: 886948

We could create a group with rleid on the 'id' column, slice the last row, remove the temporary grouping column

library(dplyr)
library(data.table)
myData %>%
    group_by(grp = rleid(id)) %>% 
    slice(n()) %>%
    ungroup %>%
    select(-grp)
# A tibble: 4 x 5
#     id date      date_cat measurement date1     
#  <dbl> <chr>        <dbl>       <dbl> <date>    
#1     3 2017-3-3         1          13 2017-03-03
#2     6 2017-4-7         1          13 2017-04-07
#3     4 2017-11-1        2          11 2017-11-01
#4     3 2018-4-3         3          17 2018-04-03

Or this can be done on the fly without creating a temporary column

myData %>% 
    filter(!duplicated(rleid(id), fromLast = TRUE))

Or using base R with subset and rle

subset(myData, !duplicated(with(rle(id), 
    rep(seq_along(values), lengths)), fromLast = TRUE))
# id      date date_cat measurement      date1
#2  3  2017-3-3        1          13 2017-03-03
#4  6  2017-4-7        1          13 2017-04-07
#6  4 2017-11-1        2          11 2017-11-01
#8  3  2018-4-3        3          17 2018-04-03

Upvotes: 0

daniellga
daniellga

Reputation: 1224

Using data.table:

library(data.table)
myData_DT <- as.data.table(myData)
myData_DT[, .SD[.N] , by = .(date_cat, id)]

Upvotes: 1

Related Questions