1
votes

I have a df with 4 columns

Col0  Col1  Col2  COl3  Col4
  1    0     NA    NA   NA
  2    0     NA    NA   NA
  3    1     NA    NA   NA
  3    0     NA    NA   NA
  5    1     NA    NA   NA

I need to fill Col2, Col3 and Col4 based on the values of Col1 So if Col1 is 0, Col2,Col3 and Col4 should be filled with 0 If Col1 is 1, Col2,Col3 and Col4 should be filled with the value of Col0 Else Col2,Col3 and Col4 should be filled with the value of average of Col0 and Col1.

df$Col2[df$Col1==0]<-0 affects only 1 column.

Expected output:

Col0  Col1  Col2  COl3  Col4
  1    0     0    0   0
  2    0     0    0   0
  3    1     3    3   3
  3    0     0    0   0
  5    1     3    3   3
3

3 Answers

2
votes
Col0<-c(1,2,3,3,5)
Col1<-c(0,0,1,0,1)
Col2<-c(NA,NA,NA,NA,NA)
Col3<-c(NA,NA,NA,NA,NA)
Col4<-c(NA,NA,NA,NA,NA)

df<-data.frame(Col0,Col1,Col2,Col3,Col4)


  df[which(df$Col1 == 0),3:5] <- 0
  df[which(df$Col1 == 1),3:5] <- df[which(df$Col1 == 1),1]
  df[which(df$Col1 != 1 & df$Col1 != 0),3:5] <- 
    mean(df[which(df$Col1 != 1 & df$Col1 != 0),1],df[which(df$Col1 != 1 & df$Col1 != 0),2])

#df:     
  Col0 Col1 Col2 Col3 Col4
1    1    0    0    0    0
2    2    0    0    0    0
3    3    1    3    3    3
4    3    0    0    0    0
5    5    1    5    5    5
1
votes

We can calculate the mean value for the rows beforehand using rowMeans and then use nested ifelse and replace values in columns.

mean_vals <- rowMeans(df[1:2])
df[3:5] <- with(df, ifelse(Col1 == 0, 0, ifelse(Col1 == 1, Col0, mean_vals)))

df
#  Col0 Col1 Col2 Col3 Col4
#1    1    0    0    0    0
#2    2    0    0    0    0
#3    3    1    3    3    3
#4    3    0    0    0    0
#5    5    1    5    5    5
0
votes

Essentially you want the rest of the columns to be a weighted average of Col0 and Col1 where possible weights are 0, 0.5, and 1. We can get the weights for Col0 with

(idx <- ((df$Col1 != 0) + (df$Col1 == 1)) / 2)
# [1] 0.0 0.0 1.0 0.0 0.5

so that it's 0 if the corresponding element of Col1 is 0; it's 1 if the corresponding element of Col1 is 1; and it's 0.5 otherwise. Thus,

df[-1:-2] <- df$Col0 * idx + df$Col1 * (1 - idx)
df
#   Col0 Col1 Col2 COl3 Col4
# 1    1    0    0    0    0
# 2    2    0    0    0    0
# 3    3    1    3    3    3
# 4    3    0    0    0    0
# 5    5    1    5    5    5