How do I replace NA's in a column with all 0's based on if a value exists in other columns that begin with the same prefix? For example, for column A1, I only want to replace the NA's with a 0 where columns A2 or A3 are NONBLANK. My real data has hundreds of groups of columns.
MY DATA:
ID<-c(1,2,3,4,5,6,7,8)
A1<-c(1,NA,1,NA,1,1,1,NA)
A2<-c(1,NA,NA,1,NA,1,NA,NA)
A3<-c(1,NA,NA,NA,1,NA,NA,NA)
B1<-c(1,1,1,1,1,1,NA,1)
B2<-c(1,1,1,1,NA,1,NA,NA)
B3<-c(1,1,NA,NA,1,NA,NA,NA)
mydata<-cbind.data.frame(ID,A1,A2,A3,B1,B2,B3)
HAVE:

WANTED:
A 0 should replace NA in column A1 if column A2 or A3 have a 1. A 0 should replace NA in column A2 if columns A1 or A3 have a 1, and so on, as below:

Another method is
mydata[, 2:4][is.na(mydata[, 2:4])] <- rep(NA^(rowSums(is.na(mydata[2:4])) == 3) - 1,
length(2:4))[is.na(mydata[, 2:4])]
mydata[, 5:7][is.na(mydata[, 5:7])] <- rep(NA^(rowSums(is.na(mydata[5:7])) == 3) - 1,
length(5:7))[is.na(mydata[, 5:7])]
mydata
ID A1 A2 A3 B1 B2 B3
1 1 1 1 1 1 1 1
2 2 NA NA NA 1 1 1
3 3 1 0 0 1 1 0
4 4 0 1 0 1 1 0
5 5 1 0 1 1 0 1
6 6 1 1 0 1 1 0
7 7 1 0 0 NA NA NA
8 8 NA NA NA 1 0 0
The column values are hard-coded, which is not helpful with many groups, so following @haboryme's technique, you could do
# group columns into list elements with lapply and grep
myCols <- lapply(c("A", "B"), function(i) grep(i, colnames(mydata)))
# loop through and make changes
for(i in myCols) {
mydata[, i][is.na(mydata[, i])] <- rep(NA^(rowSums(is.na(mydata[i])) == 3) - 1,
length(i))[is.na(mydata[, i])]
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With