Values from previous year

January 6, 2022

is there any way to get a new column containing values from the previous years from the same table?

DATA:
library(data.table)

table = data.table( Date =c( "01/01/2022", "01/10/2021", "01/07/2021", "01/04/2021", "01/01/2021", "01/10/2020", "01/07/2020"),
                    Hospital = "Hospital 1",
                    Patients = c(13,11,9,7,5,3,1) 
                    )

RESULT:

result = data.table( Date =c( "01/01/2022", "01/10/2021", "01/07/2021", "01/04/2021", "01/01/2021", "01/10/2020", "01/07/2020"),
                     Hospital = 'Hospital 1',
                    Patients = c(13,11,9,7,5,3,1),
                    Patients_previous_year = c(5,3,1,NA,NA,NA,NA) )

>Solution :

Convert to Date class, then do a grouping by ‘Hospital’ and year standardized ‘Date’ column, get the lead values of ‘Patients’ and assign (:=) as new column

library(data.table)
table[, Date := as.IDate(Date, '%m/%d/%Y')]
table[,  Patients_previous_year := shift(Patients, type = 'lead'),
    .(Hospital, grp = format(Date, '2022-%m-%d'))]

-output

> table
         Date   Hospital Patients Patients_previous_year
1: 2022-01-01 Hospital 1       13                      5
2: 2021-01-10 Hospital 1       11                      3
3: 2021-01-07 Hospital 1        9                      1
4: 2021-01-04 Hospital 1        7                     NA
5: 2021-01-01 Hospital 1        5                     NA
6: 2020-01-10 Hospital 1        3                     NA
7: 2020-01-07 Hospital 1        1                     NA