2

I was looking to use slice, and this example here seemed to be most applicable. However, when I tried this for my data in didn't work but I'm not sure what I am missing.

Data

library(tidyverse)
library(lubridate)


# Data
my_df <- structure(list(Date = structure(c(17896, 17986, 18077, 18160), class = "Date"), 
                        t_period = c("Quarter", "Quarter", "Quarter", "Quarter"), 
                        A = c(-10.2, 9.4, 3.2, 0.7), B = c(-13.6, 13.4, 6.2, 2.8)), class = c("grouped_df", 
                                                                                              "tbl_df", "tbl", "data.frame"), row.names = c(NA, -4L), groups = structure(list(
                                                                                                Date = structure(c(17896, 17986, 18077, 18160), class = "Date"), 
                                                                                                .rows = list(1L, 2L, 3L, 4L)), row.names = c(NA, -4L), class = c("tbl_df", 
                                                                                                                                                                 "tbl", "data.frame"), .drop = TRUE))


# my_df
# A tibble: 4 x 4
# Groups:   Date [4]
#  Date       t_period     A     B
#  <date>     <chr>    <dbl> <dbl>
#1 2018-12-31 Quarter  -10.2 -13.6
#2 2019-03-31 Quarter    9.4  13.4
#3 2019-06-30 Quarter    3.2   6.2
#4 2019-09-21 Quarter    0.7   2.8

What I want to do is filter out the last quarter date if it is before the quarter end.

# Get end of existing quarter
end_of_qtr <- ceiling_date(as.Date("2019-09-28"), "quarter") - 1

# Filter out last row if Date < quarter end date
filtered_df <- my_df %>%
  group_by(t_period) %>%
  arrange(Date) %>%
  slice(ifelse( n() < end_of_qtr, 1:(n()-1), n() ))

However, this does not return the desired result, which would be the first 3 rows of my_data since the last date ("2019-09-21" is before the end_of_qtr date "2019-09-30")

Could someone tell me the correct way to use slice to do this please?

dave_in_newengland
  • 251
  • 2
  • 8
  • For this case `slice(if(Date[n()] < end_of_qtr) -n() else 1:n())`, but you'll need logic to determine last date of each quarter if you want to work with data for multiple quarters. – eipi10 Oct 13 '19 at 17:11
  • 1
    Maybe I'm misunderstanding the question, but this seems simpler w/o slice: `my_df %>% mutate(qtr_end = ceiling_date(Date, "quarter") - 1) %>% filter(Date == qtr_end | Date != last(Date))` This includes all non-last rows, plus the last one if it's at quarter end. – Jon Spring Oct 13 '19 at 17:14
  • I knew I was missing something simple. Thanks eipi10, that works. I know -n() works in the True condition, but why doesn't `1:n() -1`? – dave_in_newengland Oct 13 '19 at 17:22
  • Thanks Jon, I agree that also works. I was just trying to figure out why I couldn't get slice to work. – dave_in_newengland Oct 13 '19 at 17:23
  • Your code compares `n()` to `end_of_qtr`, rather than comparing a value of `Date` to `end_of_qtr`. That's the part that wasn't working. – eipi10 Oct 13 '19 at 17:24
  • Yes, I meant if I use `slice(ifelse( Date[n()] < end_of_qtr, 1:(n() -1), n() ))` vs. using `slice(ifelse( Date[n()] < end_of_qtr, -n(), n() ))` – dave_in_newengland Oct 13 '19 at 17:25
  • I think it's because `ifelse` is vectorized, so it operates on each row of the group separately, whereas `if` operates on the group as a whole. This will work: `slice(if(Date[n()] < end_of_qtr) 1:(n()-1) else 1:n())`. – eipi10 Oct 13 '19 at 17:32
  • Many thanks. I'm happy to accept your answer if you would like to post it. – dave_in_newengland Oct 13 '19 at 19:05

0 Answers0