I am trying to look up a value of 7 days back from the data but unable to subset the value using dplyr or base R.
library(nycflights13)
library(tidyverse)
library(lubridate)
nycflights13::flights %>%
mutate(date = ymd(paste(year,"-",month,"-",day))) %>%
dplyr::select(date, carrier, flight, origin, dest, arr_delay) %>%
group_by(carrier, flight, origin, dest) %>%
arrange(desc(date)) %>%
mutate(old_delay = .[.$date - 7, "arr_delay"])
# mutate(old_delay = . %>% filter(date - 7) %>% select(arr_delay))
# ERROR
Error: Problem with `mutate()` column `old_delay`.
i `old_delay = .[.$date - 7, "arr_delay"]`.
x Must subset rows with a valid subscript vector.
x Subscript `i` has the wrong type `date`.
i It must be logical, numeric, or character.
i The error occurred in group 1: carrier = "9E", flight = 2900, origin = "JFK", dest = "BNA".
How can I pull the old values in a new column. Do i need to use apply inside mutate ?
I also had a look at zoo
package where times windows are used but couldn't find a function to pull the exact value.
Desired Result:
nycflights
may not be a right dataset for this. But think it of this as some stock data set where I am trying to find the stock closing value 7 days back so that I can calculate % change from every day to its 7 day old value.
CodePudding user response:
Are you looking for dplyr::lag
?
library(dplyr);library(lubridate)
nycflights13::flights %>%
mutate(date=ymd(paste(year, "-", month, "-", day))) %>%
dplyr::select(date, carrier, flight, origin, dest, arr_delay) %>%
group_by(carrier, flight, origin, dest) %>%
mutate(old_delay=dplyr::lag(arr_delay, 7)) %>%
arrange(desc(date))