Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

filtering duplicated rows with maximum values in a column with NAs

I have a dataframe that kinda looks like this:

studentID Enrollment_date (class=Date)
56783536 2019-02-01
56783536 2019-02-12
56783406 NA
56783406 NA
56783522 2019-03-02
56783522 NA
56783599 NA
56783599 NA

I’m trying to filter the rows in order to 1) keep the row with the maximum date, 2) keep both duplicated rows when both enrollment dates are NA and 3) when one date is NA and the other isn’t, keep the one row without NA. So it should output this:

studentID Enrollment_date (class=Date)
56783536 2019-02-12
56783406 NA
56783406 NA
56783522 2019-03-02
56783599 NA
56783599 NA

I tried this:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

dup2_ENROLL_2020 <- dup_ENROLL_2020 %>%
  group_by(studentID) %>%
  filter(Enrollment_date==max(Enrollment_date))

And this:

dup2_ENROLL_2020 <- dup_ENROLL_2020 %>%
  group_by(studentID) %>%
  slice(which.max(Enrollment_date))

With a bunch of variations of !is.na() and na.omit() in them. None of them worked, somebody help me (sorry for the rookie problem)

>Solution :

Try this:

dat %>%
  group_by(studentID) %>%
  filter(if (all(is.na(Enrollment_date))) TRUE else Enrollment_date == max(Enrollment_date, na.rm = TRUE)) %>%
  ungroup()
# # A tibble: 6 x 2
#   studentID Enrollment_date
#       <int> <date>         
# 1  56783536 2019-02-12     
# 2  56783406 NA             
# 3  56783406 NA             
# 4  56783522 2019-03-02     
# 5  56783599 NA             
# 6  56783599 NA             
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading