Changing NA only in subset in column of data frame based on conditions

user_16657280 0
Viewed 122

I have data that comes from multiple arrays. I would like to change NAs in a dataframe belonging to only the array where there is partial data. The other data should remain NA. I have an example for 2 array but it could be multiple arrays.

 data_frame = data.frame(
  col1 = c(111,232,12,NA, NA, NA, NA, NA, NA, NA,NA,NA),
  col2 = c(NA,NA,NA,4,50,800,NA,NA,NA,NA,NA,NA),
  col3 = c(NA,NA,NA,NA,NA,NA,NA,NA,NA,20,3,500),
  ARRAY = c(1,1,1,2,2,2,1,1,1,2,2,2))

so, it should look like in col1 replaced the NAs in rows 7-9, in col2 the NAs replaced in row 10-12, in col3 rows 4-6....

data frame how it should look

I tried finding which rows and columns were having data with which(!is.na(data_frame), arr.ind = TRUE) but then I got stuck. This should be generic for any script where I have multiple arrays, and want to change the NAs only when there is partial data for an array.

The order should not change, as it needs to be put back into a large data frame set.

After a bit of trying I came up with this:

for (j in 1:3){ 

A <- as.data.frame(df[,j]) 
NonNAindex <- which(!is.na(A)) 
NonNA <- min(NonNAindex) 
ARRAY <- df[firstNonNA,ncol(df)] 

for (r in 1:nrow(df)){ 

if (is.na(df[r,j]) & df[r,4]==ARRAY) {df[r,j] <- 0} 
else{df[r,j] <- df[r,j]}
} 
}
na subset grouping dataframe r
2 Answers

You can try the code below

df[-4] <- lapply(
  df[-4],
  function(x) {
    ave(x,
      df$ARRAY,
      FUN = function(v) if (all(is.na(v))) NA else replace(v, is.na(v), 1)
    )
  }
)

which gives

> df
   col1 col2 col3 ARRAY
1   111   NA   NA     1
2   232   NA   NA     1
3    12   NA   NA     1
4    NA    4    1     2
5    NA   50    1     2
6    NA  800    1     2
7     1   NA   NA     1
8     1   NA   NA     1
9     1   NA   NA     1
10   NA    1   20     2
11   NA    1    3     2
12   NA    1  500     2
user_121587570

Similar to @ThomasIsCoding 's answer in dplyr -

library(dplyr)

data_frame %>%
  group_by(ARRAY) %>%
  mutate(across(starts_with('col'), 
          ~if(all(is.na(.))) NA else replace(., is.na(.), 1))) %>%
  ungroup

#    col1  col2  col3 ARRAY
#   <dbl> <dbl> <dbl> <dbl>
# 1   111    NA    NA     1
# 2   232    NA    NA     1
# 3    12    NA    NA     1
# 4    NA     4     1     2
# 5    NA    50     1     2
# 6    NA   800     1     2
# 7     1    NA    NA     1
# 8     1    NA    NA     1
# 9     1    NA    NA     1
#10    NA     1    20     2
#11    NA     1     3     2
#12    NA     1   500     2
user_39629140
Related