CODEWITHSUNDEEP
rdataframefilterdplyrsubset
say.ff
say.ff

Reputation: 395

filtering each column of a data frame an put NA for unmatched values

I have a table as follows:

     [,1]  [,2] [,3]    [,4]   [,5] 
[1,]  a     A   0.06    0.31   -1.5
[2,]  b     B  -0.75    0.2    0.02
[3,]  c     C   1.58   -0.02    1.5

I would like to put a cutoff on column 3 to 5 to find the cells that their absolute value are greater than 1.

I also would like to fill the matching values with the absolute value and the cells that do not match the criteria with "NA".

To do so I am using filter function from dplyr package but I can not get the desired result. Does any one have idea How can I do this.

here is what the output must look: 

     [,1]  [,2] [,3]    [,4]   [,5] 
[1,]  a     A   NA       NA    1.5
[2,]  b     B   NA       NA     NA
[3,]  c     C   1.58     NA    1.5

Thanks!

Upvotes: 1

Views: 304

Answers (4)

Jilber Urbina
Jilber Urbina

Reputation: 61154

You can do it using only R base functions like this:

> dat <- read.table(text="a     A   0.06    0.31   -1.5
 b     B  -0.75    0.2    0.02
 c     C   1.58   -0.02    1.5", header=FALSE)
> dat[,3:5][abs(dat[,3:5])<=1]<-NA
> dat[,3:5] <- abs(dat[,3:5])
> dat
  V1 V2   V3 V4  V5
1  a  A   NA NA 1.5
2  b  B   NA NA  NA
3  c  C 1.58 NA 1.5

Upvotes: 1

MKR
MKR

Reputation: 20085

One can try using dplyr::mutate_at as:

library(dplyr)

df %>% mutate_at(vars(3:5), funs(ifelse(abs(.) > 1, abs(.), NA_integer_)))

OR If data has factor type columns try as:

df %>% mutate_at(vars(3:5), funs(ifelse(abs(as.numeric(as.character(.))) > 1, 
                                     abs(as.numeric(as.character(.))), NA_integer_)))

#   V1 V2   V3 V4  V5
# 1  a  A   NA NA 1.5
# 2  b  B   NA NA  NA
# 3  c  C 1.58 NA 1.5

Data:

df <- read.table(text="
a     A   0.06    0.31   -1.5
b     B  -0.75    0.2    0.02
c     C   1.58   -0.02    1.5",
stringsAsFactors = FALSE)

Upvotes: 2

akrun
akrun

Reputation: 886938

Here is a base R option

df[3:5] <- lapply(df[3:5], function(x) replace(x, abs(x) < 1, NA))
df
#  V1 V2   V3 V4   V5
#1  a  A   NA NA -1.5
#2  b  B   NA NA   NA
#3  c  C 1.58 NA  1.5

data

df <- structure(list(V1 = c("a", "b", "c"), V2 = c("A", "B", "C"), 
V3 = c(0.06, -0.75, 1.58), V4 = c(0.31, 0.2, -0.02), V5 = c(-1.5, 
0.02, 1.5)), .Names = c("V1", "V2", "V3", "V4", "V5"), 
class = "data.frame", row.names = c(NA, -3L))

Upvotes: 1

Onyambu
Onyambu

Reputation: 79188

 dat <- read.table(text="
        a     A   0.06    0.31   -1.5
        b     B  -0.75    0.2    0.02
        c     C   1.58   -0.02    1.5", header=FALSE)

 dat[3:5] = abs(dat[3:5])

 is.na(dat[3:5]) = dat[3:5]<1

 dat
  V1 V2   V3 V4  V5
1  a  A   NA NA 1.5
2  b  B   NA NA  NA
3  c  C 1.58 NA 1.5

Upvotes: 0

Related Questions