Home > database >  Count columns until a value is found per each row
Count columns until a value is found per each row

Time:03-17

I want to get the number of columns until a value of 1 is found. The count should only include var3, var4, and var5. I have something like this:

data

var1 var2     var3 var4 var5 var6
1    3750893  .5   .6   1    73891
2    75934     1    0  .1    60361
3    85        1   .1  .9    1
4    99283     0    1  .1    64123

And I want the following:

var1 var2     var3 var4 var5 var6   var7
1    3750893  .5   .6   1    73891  3
2    75934     1    0  .1    60361  1
3    85        1   .1  .9    1      1
4    99283     0    1  .1    64123  2

This is the code, but:

  1. I would like to avoid selecting the columns (I prefer to keep the complete data) and
  2. the length function needs to change to get the desired output
    df <- data.frame(var1=c(1, 2, 3, 4),var2=c(3750893, 75934, 85, 99283),
                 var3=c(.5, 1, 1, 0), var4=c(.6, 0, .1, 1),
                 var5=c(1, .1, .9, .1), var6=c(73891, 60361, 1, 64123))

    #select columns that will be used to create the new variable
    df2 <- df[,c("var3", "var4", "var5")]

    #create new variable counting occurrences of value "1" in var3-var5.      
    df2$var7 <- apply(df2, 1, function(x) length(which(x==1)))

CodePudding user response:

df$var7 <- max.col(df[3:5] == 1, ties.method = 'first')
df
  var1    var2 var3 var4 var5  var6 var7
1    1 3750893  0.5  0.6  1.0 73891    3
2    2   75934  1.0  0.0  0.1 60361    1
3    3      85  1.0  0.1  0.9     1    1
4    4   99283  0.0  1.0  0.1 64123    2

If you must use apply then:

 df$var7 <- apply(df[3:5], 1, function(x)which(x==1)[1])

CodePudding user response:

Not super-efficient but it work:

df %>% pivot_longer(-id) %>% 
  group_by(id) %>% 
  mutate(position=ifelse(value==1,str_extract(name,pattern=".$"),NA)) %>% 
  ungroup() %>% 
  pivot_wider(names_from=c(name),values_from = c(value,),id_cols = c(id,position)) %>%  
  mutate(position=as.numeric(position)) %>% 
  group_by(id) %>% 
  summarize(across(everything(),~sum(.x,na.rm=T)))
  •  Tags:  
  • r
  • Related