Prior month lookback

Hi - how can I approach adding a column that has the value of n for the same date and time in the prior month? i.e. the row for 2013-02-01 05:00:00 would have a new column with the value from 2013-01-01 05:00:00, etc... ty!

pacman::p_load(tidyverse,nycflights13,lubridate)
nycflights13::flights %>% 
  filter(between(month(time_hour),1,3)) %>% 
  count(time_hour)

Here's one approach. I bet there is a more elegant way of doing this, but this works!

library(tidyverse)
library(nycflights13)
library(lubridate)

by_hour <- flights %>% 
  filter(between(month(time_hour), 1, 3)) %>% 
  count(time_hour)

by_hour_prior <- by_hour %>% 
  mutate(time_hour = time_hour + months(1)) %>% 
  rename(n_prior_month = n)

by_hour %>% 
  inner_join(by_hour_prior, by = "time_hour")
#> # A tibble: 1,064 x 3
#>    time_hour               n n_prior_month
#>    <dttm>              <int>         <int>
#>  1 2013-02-01 05:00:00     5             6
#>  2 2013-02-01 06:00:00    76            52
#>  3 2013-02-01 07:00:00    64            49
#>  4 2013-02-01 08:00:00    77            58
#>  5 2013-02-01 09:00:00    57            56
#>  6 2013-02-01 10:00:00    44            39
#>  7 2013-02-01 11:00:00    45            37
#>  8 2013-02-01 12:00:00    47            56
#>  9 2013-02-01 13:00:00    52            54
#> 10 2013-02-01 14:00:00    55            48
#> # ... with 1,054 more rows

Created on 2020-07-24 by the reprex package (v0.3.0)

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.