Create binary variable based on two columns-CodePudding

I am trying to create a binary variable where if an individual did not make $100 or more in all four quarters of a year they are listed as 0; if an individual did make $100 or more they are listed as 1. Below is an example of the desired output that I am trying to make. I have created the Binary column, I just need Binary2

dd <- read.table(text="
MPI     yrqtr  Wage Binary Binary2
PersonA 20101  100    1      1
PersonA 20102  100    1      1
PersonA 20103  100    1      1  
PersonA 20104  100    1      1
PersonA 20111  100    1      0
PersonA 20112  100    1      0
PersonA 20113  100    1      0
PersonA 20114   50    0      0", header=T)

CodePudding user response：

Here we can group by year and person, and then use all() to look across all records for a year. I use 0 to turn the boolean value into 1/0.

dd %>% 
  group_by(MPI, year=substr(yrqtr,1,4)) %>% 
  mutate(Binary2 = all(Wage>=100) 0)

CodePudding user response：

Here is similar approach:

dd %>%
  group_by(group = str_extract(yrqtr, '\\d{4}')) %>% 
  mutate(Binary = ifelse(Wage >= 100, 1, 0),
         Binary2 = ifelse(sum(Binary)==4, 1, 0)) %>% 
  ungroup() %>% 
  select(-group)

 MPI     yrqtr  Wage Binary Binary2
  <chr>   <int> <int>  <dbl>   <dbl>
1 PersonA 20101   100      1       1
2 PersonA 20102   100      1       1
3 PersonA 20103   100      1       1
4 PersonA 20104   100      1       1
5 PersonA 20111   100      1       0
6 PersonA 20112   100      1       0
7 PersonA 20113   100      1       0
8 PersonA 20114    50      0       0