How to remove rows from a datatable after grouping variables?

Question

I have one data x with id, date, and details columns:

id date                 details
1  28-07-2017 20:01:30  NA
2  28-07-2017 18:01:30  NA
1  28-07-2017 22:02:20  SQL
2  28-07-2017 19:01:30  SQL
1  29-07-2017 22:20:00  NA
3  30-07-2017 18:01:30  NA
3  31-07-2017 18:01:30  SQL
4  28-07-2017 18:01:30  NA

I want my data look like this:

id date                 details
1  28-07-2017 20:01:30  NA
1  28-07-2017 22:02:20  SQL
2  28-07-2017 18:01:30  NA
2  28-07-2017 19:01:30  SQL
3  30-07-2017 18:01:30  NA
3  31-07-2017 18:01:30  SQL
4  28-07-2017 18:01:30  NA

I want to group data x on the basis of id, then sort date in ascending order, then if there is a "SQL" value in the details column then delete the below rows of same id. There should be no records on each id once SQL stage arrives in details variable.

How can I do this in R?

Could you add a [reproducible example of the data?](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — AaronT86, Jul 31 '17 at 17:21

score 0 · Answer 1 · answered Jul 31 '17 at 17:19

0

Use dput to convert your data into something that we can work with. Here's a link to understand it: https://gist.github.com/dsparks/3688652

answered Jul 31 '17 at 17:19

TheSciGuy

1,154
11
22

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/16888328) – kdopen Jul 31 '17 at 20:27
The data was in an unusable form when I commented. The asker updated the question (making the data easier to understand), making my initial comment irrelevant. – TheSciGuy Jul 31 '17 at 20:40

score 0 · Answer 2 · answered Jul 31 '17 at 17:30

0

Use the dplyr package along with magrittr. Here's a quick stab at it.

library("dplyr")
library("magrittr")
newdata <- olddata %>% group_by(id) %>% arrange (date) %>% filter(details !="SQL")

answered Jul 31 '17 at 17:30

TheSciGuy

1,154
11
22

How to remove rows from a datatable after grouping variables?

2 Answers2