R condition new variable on two variables

Question

So i have encountrered the following problem: I have a dataframe of the following form:

ID   Date        Var1
1    20200101    0
1    20200102    0
1    20200103    0
1    20200104    0
1    20200104    0
2    20200101    0
2    20200102    0
2    20200103    4
2    20200104    7
2    20200105    13

and I would like to define an additional variable, taking on 1, if Var1 > 0 for the first time in the month of January (20200101:20200131) and 0 otherwise, with respect to the IDs. The actual database spreads over 6 months and Var1 is a running total, so if > 0 once, it won´t decrease ever again. So the final frame should look something like this:

ID   Date        Var1   new_var
1    20200101    0       0
1    20200102    0       0
1    20200103    0       0
1    20200104    0       0
1    20200104    0       0
2    20200101    0       1
2    20200102    0       1
2    20200103    4       1
2    20200104    7       1
2    20200105    13      1

Thanks for all your answers!

user_8245406 · Answer

Here is a solution using ave and substr to get only the month/year of each date.

z <- substr(df1$Date, 1, 6)
jan <- ave(df1$Date, df1$ID, FUN = function(x) substr(x, 5, 6) == "01")
zero <- ave(df1$Var1, df1$ID, z, FUN = function(x) any(x > 0))
df1$new_var <- +(as.logical(jan) & zero)

df1
#   ID     Date Var1 new_var
#1   1 20200101    0       0
#2   1 20200102    0       0
#3   1 20200103    0       0
#4   1 20200104    0       0
#5   1 20200104    0       0
#6   2 20200101    0       1
#7   2 20200102    0       1
#8   2 20200103    4       1
#9   2 20200104    7       1
#10  2 20200105   13       1

Data

df1 <- read.table(text = "
ID   Date        Var1
1    20200101    0
1    20200102    0
1    20200103    0
1    20200104    0
1    20200104    0
2    20200101    0
2    20200102    0
2    20200103    4
2    20200104    7
2    20200105    13
", header = TRUE)

R condition new variable on two variables

1 Answers