CODEWITHSUNDEEP
rif-statementnadplyr
ESKim
ESKim

Reputation: 432

Fill missing with 0 if all values are 0 with R

I'm trying to fill missing value with R.

If all other value is 0, then I want to fill missing with 0.

An example is shown below. In this data, All value in c column except NA is 0. So, I want to fill Na with 0.

set.seed(1000)
a<-rnorm(10)
b<-rnorm(10)
c<-rep(0,10)
c[c(2,4,8)]<-NA
test<-cbind(a,b,c)

               a          b  c
 [1,]  0.1901328  0.6141360  0
 [2,] -0.9884426  0.6508993 NA
 [3,] -0.9783197  2.1059862  0
 [4,] -1.8584651  0.4354903 NA
 [5,]  0.6623067  1.6382126  0
 [6,] -1.2542872  0.1370791  0
 [7,] -1.9971880  1.9302738  0
 [8,]  1.9417941  0.0449239 NA
 [9,]  1.7046508  1.0726263  0
[10,] -0.7289351 -2.8374912  0

I can't find a good example of code. Would you give me a good advice?

Upvotes: 5

Views: 1167

Answers (6)

MichaelChirico
MichaelChirico

Reputation: 34763

Using setnafill in data.table you can do two passes -- check which columns have all 0s, then fill them:

library(data.table)
test = data.table(test)

# this will warn about converting double->numeric;
#   you may want to suppressWarnings here; more
#   "properly" you would do
#   sapply(test, function(x) any(x != 0, na.rm = TRUE))
empty_cols = !sapply(test, any, na.rm = TRUE)

# use setnafill to do the replacement in-place
setnafill(test, type = 'const', fill = 0, cols = which(empty_cols))
test[]
#               a           b c
#  1: -0.44577826 -0.98242783 0
#  2: -1.20585657 -0.55448870 0
#  3:  0.04112631  0.12138119 0
#  4:  0.63938841 -0.12087232 0
#  5: -0.78655436 -1.33604105 0
#  6: -0.38548930  0.17005748 0
#  7: -0.47586788  0.15507872 0
#  8:  0.71975069  0.02493187 0
#  9: -0.01850562 -2.04658541 0
# 10: -1.37311776  0.21315411 0

Upvotes: 2

Cole
Cole

Reputation: 11255

A one-line base option:

test[, colSums(test, na.rm = T) == 0L] <- 0

And a similar idea in dplyr

library(dplyr)
test%>%
  as_tibble()%>%
  mutate_if(~ sum(., na.rm = T) == 0L, function(x) x = 0)

Upvotes: 0

Krishna
Krishna

Reputation: 11

First convert test into data frame to access $ operator

   set.seed(1000)
   a<-rnorm(10)
   b<-rnorm(10)
   c<-rep(0,10)
   c[c(2,4,8)]<-NA
   test<-cbind(a,b,c)

   test <- data.frame(test)

Convert variable c into a factor and create a level "0", if test$c is "NA"

test$c <- as.factor(test$c)
test$c[is.na(test$c)] <- "0"

Check test data set for 'NA' are replaced by '0'

test

     a           b      c
-0.44577826 -0.98242783 0
-1.20585657 -0.55448870 0
0.04112631  0.12138119  0
0.63938841  -0.12087232 0
-0.78655436 -1.33604105 0
-0.38548930 0.17005748  0
-0.47586788 0.15507872  0
0.71975069  0.02493187  0
-0.01850562 -2.04658541 0
-1.37311776 0.21315411  0

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389315

In base R, we can use apply column-wise check if all other values in column are 0 and replace missing values with 0. 

apply(test, 2, function(x) 
          if(all(x == 0, na.rm = TRUE)) replace(x, is.na(x), 0) else x)

#            a       b c
# [1,] -0.4458 -0.9824 0
# [2,] -1.2059 -0.5545 0
# [3,]  0.0411  0.1214 0
# [4,]  0.6394 -0.1209 0
# [5,] -0.7866 -1.3360 0
# [6,] -0.3855  0.1701 0
# [7,] -0.4759  0.1551 0
# [8,]  0.7198  0.0249 0
# [9,] -0.0185 -2.0466 0
#[10,] -1.3731  0.2132 0

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 40171

One dplyr option could be:

test %>%
 as.data.frame() %>%
 mutate_if(~ all(. %in% c(0, NA)), ~ replace(., is.na(.), 0))

             a           b c
1  -0.44577826 -0.98242783 0
2  -1.20585657 -0.55448870 0
3   0.04112631  0.12138119 0
4   0.63938841 -0.12087232 0
5  -0.78655436 -1.33604105 0
6  -0.38548930  0.17005748 0
7  -0.47586788  0.15507872 0
8   0.71975069  0.02493187 0
9  -0.01850562 -2.04658541 0
10 -1.37311776  0.21315411 0

Or:

test %>%
 as.data.frame() %>%
 mutate_if(~ all(. == 0, na.rm = TRUE), ~ replace(., is.na(.), 0))

Upvotes: 1

StupidWolf
StupidWolf

Reputation: 46978

Quick value, if all your columns are numeric:

colSums(!is.na(test)) == colSums(test==0,na.rm=TRUE)
    a     b     c 
FALSE FALSE  TRUE

We change the TRUE columns

wh = which(colSums(!is.na(test)) == colSums(test==0,na.rm=TRUE))
for(i in wh){test[is.na(test[,i]),i] = 0}

                a           b c
 [1,] -0.44577826 -0.98242783 0
 [2,] -1.20585657 -0.55448870 0
 [3,]  0.04112631  0.12138119 0
 [4,]  0.63938841 -0.12087232 0
 [5,] -0.78655436 -1.33604105 0
 [6,] -0.38548930  0.17005748 0
 [7,] -0.47586788  0.15507872 0
 [8,]  0.71975069  0.02493187 0
 [9,] -0.01850562 -2.04658541 0
[10,] -1.37311776  0.21315411 0

Upvotes: 1

Related Questions