CODEWITHSUNDEEP
rselect
rbonac
rbonac

Reputation: 125

Select multiple rows by condition and order

I have the following extract of my dataset:

basisanddowngradessingledates[3017:3023, c("Dates", "Bank", "Rating agency") ]
     Dates      Bank                                     Rating agency
3017 2011-10-04 Banca Monte dei Paschi di Siena SpA            NA
3018 2011-10-05 Banca Monte dei Paschi di Siena SpA       Moody's
3019 2011-10-06 Banca Monte dei Paschi di Siena SpA            NA
3020 2011-10-07 Banca Monte dei Paschi di Siena SpA            NA
3021 2011-10-10 Banca Monte dei Paschi di Siena SpA            NA
3022 2011-10-11 Banca Monte dei Paschi di Siena SpA         Fitch
3023 2011-10-12 Banca Monte dei Paschi di Siena SpA            NA

My dataset has 45078 entries with 536 downgrades (column "Rating agency" is not "NA"). I need to have the row before a downgrade, the row where one occured and the row after the downgrade.

I did this with the following commands:

keepindex <- which(basisanddowngradessingledates[,8] != "NA")
interval1 <- basisanddowngradessingledates[c(keepindex-1,keepindex, 
keepindex+1), ]

This gives me this output:

interval1[c(14:15, 358:359, 696:697),c("Dates", "Bank", "Rating agency")]
    Dates      Bank                                     Rating agency
14  2011-10-04 Banca Monte dei Paschi di Siena SpA            NA
15  2011-10-10 Banca Monte dei Paschi di Siena SpA            NA
358 2011-10-05 Banca Monte dei Paschi di Siena SpA       Moody's
359 2011-10-11 Banca Monte dei Paschi di Siena SpA         Fitch
696 2011-10-06 Banca Monte dei Paschi di Siena SpA            NA
697 2011-10-12 Banca Monte dei Paschi di Siena SpA            NA

Now to my concern: I need to reorder the list, in order that the 3 rows of the respective downgrade are right after each other in my output. In my example like this:

    Dates      Bank                                     Rating agency
14  2011-10-04 Banca Monte dei Paschi di Siena SpA            NA
358 2011-10-05 Banca Monte dei Paschi di Siena SpA       Moody's
696 2011-10-06 Banca Monte dei Paschi di Siena SpA            NA
15  2011-10-10 Banca Monte dei Paschi di Siena SpA            NA
359 2011-10-11 Banca Monte dei Paschi di Siena SpA         Fitch
697 2011-10-12 Banca Monte dei Paschi di Siena SpA            NA

The problem is that I cannot sort it by date,I tried, but if I have multiple downgrades on consecutive days it gave me a mess.

How can I solve this?

Upvotes: 0

Views: 62

Answers (1)

Milan Val&#225;šek
Milan Val&#225;šek

Reputation: 611

This should be sanitised for borderline cases (e.g., 2 downgrades on two consecutive days, downgrade on the first/last day):

df <- data.frame(date = Sys.Date() - 19:0, dgrd = NA)
df$dgrd[c(4, 10, 11, 16, 20)] <- "X" # add downgrades including tricky cases

down <- which(!is.na(df$dgrd))
keep <- unique(sort(c(down-1, down, down + 1))) # unique() prevents overlap

# make sure you're not asking for indices outside of 1:nrow(df)
df2 <- df[keep[keep %in% 1:nrow(df)], ]

     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-05 <NA>
2017-10-08 <NA>
2017-10-09    X
2017-10-10 <NA>
2017-10-12 <NA>
2017-10-13    X

Upvotes: 2

Related Questions