CODEWITHSUNDEEP
rselect
rbonac
rbonac

Reputation: 125

Selecting rows by condition and ordering including duplicates

I tried to create a small example of my problem. First column are different dates and the second column downgrades which occured on these specific dates. I need to filter 3 rows per downgrade: The day before the downgrade, the day where the downgrade occured and the day after the downgrade. 

df <- data.frame(date = Sys.Date() - 19:0, dgrd = NA)
df$dgrd[c(4, 10, 11, 16)] <- "X" #small dataframe inclduing problematic downgrades

down <- which(!is.na(df$dgrd)) #select all days where downgrade occured
keep <- df[c(down-1, down, down+1), ] #select the specific days for each downgrade

print(keep)

date       dgrd
2017-09-26 <NA>
2017-10-02 <NA>
2017-10-03    X
2017-10-08 <NA>
2017-09-27    X
2017-10-03    X
2017-10-04    X
2017-10-09    X
2017-09-28 <NA>
2017-10-04    X
2017-10-05 <NA>
2017-10-10 <NA>

Now I need to sort this output that I have the 3 days which belong to one particular downgrade right after each other. I cannot sort it by date because if have downgrades on 2 consecutive days the order is not correct.

So in the end my table should look as follows, so that every 3 rows belong to one downgrade:

date       dgrd
2017-09-26 <NA>
2017-09-27    X
2017-09-28 <NA>
2017-10-02 <NA>
2017-10-03    X
2017-10-04    X
2017-10-03    X
2017-10-04    X
2017-10-05 <NA>
2017-10-08 <NA>
2017-10-09    X
2017-10-10 <NA>

In the case of downgrades on consecutive days, there are duplicate rows which I need in my final output, therefore the unique() function cannot be used.

How can I solve this problem?

Upvotes: 1

Views: 46

Answers (1)

www
www

Reputation: 39174

You may want to change the way you created the index as follows.

down <- which(!is.na(df$dgrd))
index <- unlist(lapply(down, function(x) c(x - 1, x, x + 1)))
keep <- df[index, ]
keep
           date dgrd
3    2017-09-26 <NA>
4    2017-09-27    X
5    2017-09-28 <NA>
9    2017-10-02 <NA>
10   2017-10-03    X
11   2017-10-04    X
10.1 2017-10-03    X
11.1 2017-10-04    X
12   2017-10-05 <NA>
15   2017-10-08 <NA>
16   2017-10-09    X
17   2017-10-10 <NA>

Upvotes: 2

Related Questions