Home > Mobile >  How to get data for 7 day or 10 day old value in r?
How to get data for 7 day or 10 day old value in r?

Time:01-02

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))
  • Related