Create binary variable based on two columns

Question

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)

MrFlick · Accepted Answer · 2022-12-13 21:01:48Z

1

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)

answered Dec 13, 2022 at 21:01

MrFlick

209k19 gold badges300 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TarJae · Accepted Answer · 2022-12-13 21:34:15Z

0

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

answered Dec 13, 2022 at 21:34

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

Collectives™ on Stack Overflow

Create binary variable based on two columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related