mardi 16 avril 2019

Assign A Value of 1 or 0 based on condition

I have monthly reports from October through April and have stacked all of the data. I sorted the data by UniqueID and then Date.

I want to create a dummy variable that will meet the following condition:

1.) If the last occurrence of a specific UniqueID is not in the last month (April), then I want the variable to =1, otherwise 0.

The Freq column counts how many times the UniqueID shows up in the entire dataset of stacked monthly reports.

UniqueID Date        Freq
XX343_1  02/01/2019  3
XX343_1  03/01/2019  3  
XX343_1  04/01/2019  3
SD229_1  11/01/2018  4 
SD229_1  12/01/2018  4
SD229_1  01/01/2019  4
SD229_1  02/01/2019  4
WE321_1  10/01/2018  1

Basically, I would want the following output:

UniqueID Date        Freq Dummy
XX343_1  02/01/2019  3    0
XX343_1  03/01/2019  3    0
XX343_1  04/01/2019  3    0
SD229_1  11/01/2018  4    0
SD229_1  12/01/2018  4    0
SD229_1  01/01/2019  4    0
SD229_1  02/01/2019  4    1
WE321_1  10/01/2018  1    1

The following code is what I have attempted:

 data$Dummy=ifelse(data$Date=="2018-10-01" & data$Freq==1,1,ifelse(
                   data$Date=="2018-10-01" & data$Freq>=2,0,ifelse(
                   data$Date=="2018-11-01" & data$Freq<=2,1,ifelse(
                   data$Date=="2018-11-01" & data$Freq >2,0,ifelse(
                   data$Date=="2018-12-01" & data$Freq<=3,1,ifelse(
                   data$Date=="2018-12-01" & data$Freq >3,0,ifelse(
                   data$Date=="2019-01-01" & data$Freq<=4,1,ifelse(
                   data$Date=="2019-01-01" & data$Freq >4,0,ifelse(
                   data$Date=="2019-02-01" & data$Freq<=5,1,ifelse(
                   data$Date=="2019-02-01" & data$Freq >5,0,ifelse(
                   data$Date=="2019-03-01" & data$Freq<=6,1,ifelse(
                   data$Date=="2019-03-01" & data$Freq >6,0,0
               ))))))))))))

I keep getting errors and I'm not sure how to fix my problems. I get a lot of situations where if the first occurrence of a UniqueID is not in October, then the Dummy will = 0 in the second to last month. Can someone point me in the right direction?

Aucun commentaire:

Enregistrer un commentaire