Replacing values in multiple columns in dataframe

Question

In the following example, res has 90 rows and 6 columns and includes several NAs. There is also a matrix, tmpCombs with 6 rows and 2 columns. Each row of tmpCombs corresponds to a group of 15 rows in res (6*15=90). The number of its columns (2) means that I must replace 2 of the existing NA's in each row of res with a specified character (say "B"). (It is ensured that there will be at least 2 NAs in each row).

For example, the values 2 and 4 in row 5 of tmpCombs mean that for each row in the group 61-75 (4*15+1):(5*15) of res, the 2nd and 4th appearing NAs must be replaced with "B".

I can do the job using nested for loops, but I am looking for a faster/massive way to perform those replacements (perhaps using dplyr or data.table), because my code gets more and more slow, as the dimensions increase.

An example of res and tmpCombs is given below. There is also a 2nd dataframe, res2, which contains the desired output.

res <- structure(list(X1 = c("A", "A", "A", "A", "A", NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), X2 = c("A", NA, NA, 
NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA, "A", NA, 
NA, NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA, "A", 
NA, NA, NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA, "A", 
NA, NA, NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA, "A", 
NA, NA, NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA, "A", 
NA, NA, NA, NA, "A", "A", "A", "A", NA, NA, NA, NA, NA, NA), 
    X3 = c(NA, "A", NA, NA, NA, "A", NA, NA, NA, "A", "A", "A", 
    NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, NA, "A", "A", 
    "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, NA, "A", 
    "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, NA, 
    "A", "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, 
    NA, NA, "A", "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", 
    NA, NA, NA, "A", "A", "A", NA, NA, NA), X4 = c(NA, NA, "A", 
    NA, NA, NA, "A", NA, NA, "A", NA, NA, "A", "A", NA, NA, NA, 
    "A", NA, NA, NA, "A", NA, NA, "A", NA, NA, "A", "A", NA, 
    NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, NA, "A", "A", 
    NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, NA, "A", 
    "A", NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, NA, 
    "A", "A", NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", 
    NA, NA, "A", "A", NA), X5 = c(NA, NA, NA, "A", NA, NA, NA, 
    "A", NA, NA, "A", NA, "A", NA, "A", NA, NA, NA, "A", NA, 
    NA, NA, "A", NA, NA, "A", NA, "A", NA, "A", NA, NA, NA, "A", 
    NA, NA, NA, "A", NA, NA, "A", NA, "A", NA, "A", NA, NA, NA, 
    "A", NA, NA, NA, "A", NA, NA, "A", NA, "A", NA, "A", NA, 
    NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, "A", NA, "A", 
    NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, "A", NA, 
    "A"), X6 = c(NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, 
    "A", NA, "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", 
    NA, NA, "A", NA, "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, 
    "A", NA, NA, "A", NA, "A", "A", NA, NA, NA, NA, "A", NA, 
    NA, NA, "A", NA, NA, "A", NA, "A", "A", NA, NA, NA, NA, "A", 
    NA, NA, NA, "A", NA, NA, "A", NA, "A", "A", NA, NA, NA, NA, 
    "A", NA, NA, NA, "A", NA, NA, "A", NA, "A", "A")), .Names = c("X1", 
"X2", "X3", "X4", "X5", "X6"), row.names = c(NA, -90L), class = "data.frame")

tmpCombs <- structure(c(1L, 1L, 1L, 2L, 2L, 3L, 2L, 3L, 4L, 3L, 4L, 4L), .Dim = c(6L, 
2L))

res2 <- structure(list(X1 = c("A", "A", "A", "A", "A", "B", "B", "B", 
"B", "B", "B", "B", "B", "B", "B", "A", "A", "A", "A", "A", "B", 
"B", "B", "B", "B", "B", "B", "B", "B", "B", "A", "A", "A", "A", 
"A", "B", "B", "B", "B", "B", "B", "B", "B", "B", "B", "A", "A", 
"A", "A", "A", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "A", "A", 
"A", "A", "A", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "A", "A", 
"A", "A", "A", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), X2 = c("A", 
"B", "B", "B", "B", "A", "A", "A", "A", "B", "B", "B", "B", "B", 
"B", "A", "B", "B", "B", "B", "A", "A", "A", "A", NA, NA, NA, 
NA, NA, NA, "A", "B", "B", "B", "B", "A", "A", "A", "A", NA, 
NA, NA, NA, NA, NA, "A", NA, NA, NA, NA, "A", "A", "A", "A", 
"B", "B", "B", "B", "B", "B", "A", NA, NA, NA, NA, "A", "A", 
"A", "A", "B", "B", "B", "B", "B", "B", "A", NA, NA, NA, NA, 
"A", "A", "A", "A", NA, NA, NA, NA, NA, NA), X3 = c("B", "A", 
"B", "B", "B", "A", "B", "B", "B", "A", "A", "A", NA, NA, NA, 
"B", "A", NA, NA, NA, "A", NA, NA, NA, "A", "A", "A", "B", "B", 
"B", "B", "A", NA, NA, NA, "A", NA, NA, NA, "A", "A", "A", NA, 
NA, NA, NA, "A", "B", "B", "B", "A", "B", "B", "B", "A", "A", 
"A", "B", "B", "B", NA, "A", "B", "B", "B", "A", "B", "B", "B", 
"A", "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, 
NA, "A", "A", "A", "B", "B", "B"), X4 = c("B", "B", "A", NA, 
NA, "B", "A", NA, NA, "A", NA, NA, "A", "A", NA, NA, NA, "A", 
"B", "B", NA, "A", "B", "B", "A", "B", "B", "A", "A", NA, NA, 
NA, "A", NA, NA, NA, "A", NA, NA, "A", NA, NA, "A", "A", "B", 
"B", "B", "A", "B", "B", "B", "A", "B", "B", "A", "B", "B", "A", 
"A", NA, "B", "B", "A", NA, NA, "B", "A", NA, NA, "A", NA, NA, 
"A", "A", "B", NA, NA, "A", "B", "B", NA, "A", "B", "B", "A", 
"B", "B", "A", "A", "B"), X5 = c(NA, NA, NA, "A", NA, NA, NA, 
"A", NA, NA, "A", NA, "A", NA, "A", "B", "B", "B", "A", NA, "B", 
"B", "A", NA, "B", "A", NA, "A", NA, "A", NA, NA, NA, "A", "B", 
NA, NA, "A", "B", NA, "A", "B", "A", "B", "A", "B", "B", "B", 
"A", NA, "B", "B", "A", NA, "B", "A", NA, "A", NA, "A", NA, NA, 
NA, "A", "B", NA, NA, "A", "B", NA, "A", "B", "A", "B", "A", 
"B", "B", "B", "A", "B", "B", "B", "A", "B", "B", "A", "B", "A", 
"B", "A"), X6 = c(NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, NA, 
"A", NA, "A", "A", NA, NA, NA, NA, "A", NA, NA, NA, "A", NA, 
NA, "A", NA, "A", "A", "B", "B", "B", "B", "A", "B", "B", "B", 
"A", "B", "B", "A", "B", "A", "A", NA, NA, NA, NA, "A", NA, NA, 
NA, "A", NA, NA, "A", NA, "A", "A", "B", "B", "B", "B", "A", 
"B", "B", "B", "A", "B", "B", "A", "B", "A", "A", "B", "B", "B", 
"B", "A", "B", "B", "B", "A", "B", "B", "A", "B", "A", "A")), .Names = c("X1", 
"X2", "X3", "X4", "X5", "X6"), row.names = c(NA, -90L), class = "data.frame")

alexis_laz · Accepted Answer · 2016-04-10 11:16:08Z

2

tmpCombs can be expanded to get a mapping for each row:

tc2 = tmpCombs[rep(seq_len(nrow(tmpCombs)), 
                   each = nrow(res) %/% nrow(tmpCombs)), ]
dim(tc2)
#[1] 90  2

Then, looping, only, over the columns and counting the successive appearance of NAs, replace when the specific (according to tmpCombs) increasing number of NA appears:

NAcounts = integer(nrow(res))
for(j in seq_along(res)) {
   nas = is.na(res[[j]])

   NAcounts = NAcounts + nas  #`NA`s found so far in each row

   #only for rows with `NA`s 
   #(no need for extended lookup if few rows contain `NA`)
   #check if _this_ appearance of `NA` matches in `tmpCombs`
   wnas = which(nas)
   matches = NAcounts[wnas] == tc2[wnas, ]

   res[[j]][wnas[as.logical(rowSums(matches))]] = "B"
}
identical(res, res2)
#[1] TRUE

edited Apr 10, 2016 at 11:16

answered Apr 9, 2016 at 22:17

alexis_laz

13.2k4 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yorgos Over a year ago

Thank you. Could you suggest a generalization, that will allow tmpCombs to have an arbitrary number of columns?

alexis_laz Over a year ago

@GeorgeDontas : made a few edits; I thought that as.logical(rowSums(x == y)) (logical -> numeric -> logical) might be excess work than, just, accumulating | on the columns of x == y, but, I guess, it shouldn't be much inefficient.

Yorgos Over a year ago

I converted res to a matrix - the code runs much faster in that way. Then I made some necessary changes to your code and got the faster execution. Congrats!

alexis_laz Over a year ago

@GeorgeDontas : I'd expect that using "matrix" might slow things a bit since, generally, data.frame[[j]] does not copy while matrix[, j] does, and, also, with the above setup there seems to be no overhead from any "data.frame" methods. Or am I missing something in your comment?

Yorgos Over a year ago

Check out that post for more details codereview.stackexchange.com/questions/125186/…

|

digEmAll · Accepted Answer · 2016-04-09 13:54:06Z

1

I don't think it would be much better than your for-loop solution, but here's a possibile option :

# Base R solution: 
# note that res is a data.frame of characters but 
# returned value will be a matrix of characters
toBind <- 
lapply(1:nrow(tmpCombs),function(idx){
  rowIdxs<- ((idx-1)*15+1):(idx*15)
  replaceIdxs <- tmpCombs[idx,]
  tmp <- apply(res[rowIdxs,],1,function(row){
    row[na.omit(which(is.na(row))[replaceIdxs])] <- 'B'
    return(row)
  })
  return(t(tmp))
})

fixed <- do.call(rbind,toBind)

N.B.

I suggest you to turn res into a matrix of characters, since usually access to matrix elements is faster than data.frame

edited Apr 9, 2016 at 13:54

answered Apr 9, 2016 at 13:47

digEmAll

57.4k9 gold badges118 silver badges142 bronze badges

Collectives™ on Stack Overflow

Replacing values in multiple columns in dataframe

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related