Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to replace blanks for groups of columns based on column group prefix?

Tags:

replace

r

na

prefix

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:

enter image description here

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: enter image description here

like image 917
Mary Avatar asked Nov 25 '25 12:11

Mary


1 Answers

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])]
}
like image 169
lmo Avatar answered Nov 28 '25 03:11

lmo



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!