0

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?

rbonac
  • 125
  • 2
  • 14
  • Please give a [mcve]. For example *how* did you try to sort things and what do you mean by the result being "a mess"? Please give an example that someone can use to reproduce the problem. See this: https://stackoverflow.com/q/5963269/4996248 – John Coleman Oct 13 '17 at 10:03

1 Answers1

2

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
Milan Valášek
  • 571
  • 3
  • 10
  • It's a nice approach, however I need the overlap. In your example in the case of the downgrades in consecutive days I would need 10-02, 10-03, 10-04, 10-03, 10-04 and 10-05 in this order. Unfortunately unique prevents the duplicates which I need. – rbonac Oct 13 '17 at 12:47
  • 1
    In that case, instead of `unique(sort(...))`, you can do something like `keep <- as.vector(rbind(down-1, down, down+1))` – Milan Valášek Oct 13 '17 at 14:34