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:
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:
- I would like to avoid selecting the columns (I prefer to keep the complete data) and
- 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)))