Filter data, remove entire dataframe

Question

My data is like this:

X1e  X2e  X3e  X4e
360    0    0    0
360    0    0    0
260    0    0    0
0      0    0    0
0      0    0    0
0      0    0    0
90     0    0    0
360    0  360    0
360    0  360  260

I want to remove the X1e X4e columns between 0 and 270, did not remove 0 row

for (i in c(1,4)){
  e <- assign(paste("X",i, "e",sep = ""),i)
  dat <- dat[with(dat, !((e>0)&(e<270))), ] 
}

This removes all my dat and my dat became empty. Where is my problem?

I only want to remove the row ( 260 0 0 0) ,(90 0 0 0) and row (360 0 360 260) , but it remove all the line ,I think my code is right Is there a problem with my R enviroment , but I reinstall my R from 3.5.1 to 3.4.4 the problem still remain — maryadi, Oct 23 '18 at 06:24
*"Where is my problem?"* You're not using `assign` correctly, and in fact, you should avoid using `assign` altogether! See [Why is using assign bad](https://stackoverflow.com/questions/17559390/why-is-using-assign-bad). — Maurits Evers, Oct 23 '18 at 06:38
Your code is definitiely not right. In your loop the variable `e` is `1` in the first iteration. Therefore all the rows get deletetd, because `1>0 & 1<270` is always true. — symbolrush, Oct 23 '18 at 06:44

Saurabh Chauhan · Answer 1 · 2018-10-23T06:49:07.147

2

Base R Solution:

dat[!(dat$X1e>0 & dat$X1e<270) & !(dat$X4e>0 & dat$X4e<270),]

OR

Using sqldf:

library(sqldf)
sqldf("select * from dat where X1e not between 1 AND 270 AND X4e not between 1 AND 270")

Output:

   X1e X2e X3e X4e
1 360   0   0   0
2 360   0   0   0
3   0   0   0   0
4   0   0   0   0
5   0   0   0   0
6 360   0 360   0

edited Oct 23 '18 at 06:49

answered Oct 23 '18 at 06:32

Saurabh Chauhan

3,161
2
19
46

“select * from dat where X2e not between 0 AND 360 AND X3e not between 0 AND 360" – maryadi Oct 23 '18 at 07:13

score 2 · Answer 2 · answered Oct 23 '18 at 07:13

Create the column names of interest

cidx <- paste0("X", c(1, 4), "e")

Perform the logical operations on each column

test <- !(df[,cidx] > 0 & df[,cidx] < 270)

Sum (logical 'and') across rows to find those where all columns are TRUE

ridx <- rowSums(test) == length(cidx)

Subset the original data.frame

df[ridx,]

score 1 · Answer 3 · answered Oct 23 '18 at 06:29

1

Like this?

library(tidyverse)
 df<-read.table(text="X1e  X2e  X3e  X4e
 360    0    0    0
            360    0    0    0
            260    0    0    0
            0      0    0    0
            0      0    0    0
            0      0    0    0
            90     0    0    0
            360    0  360    0
            360    0  360  260",header=T)
 df%>%
   filter_at(vars(X1e,X4e), all_vars(.<=0 | .>270))
  X1e X2e X3e X4e
1 360   0   0   0
2 360   0   0   0
3   0   0   0   0
4   0   0   0   0
5   0   0   0   0
6 360   0 360   0

answered Oct 23 '18 at 06:29

jyjek

2,627
11
23

but is not the all_vars(.<=0 | .>270) is the [X1e,X4e] (.<=0 | .>270) and [X2e, X3e](.<=0 | .>=360). – maryadi Oct 23 '18 at 06:39
'all_vars` there used only for `vars(X1e,X4e)` – jyjek Oct 23 '18 at 06:57
I had an error " Error in all_vars(. = 0 | . >= 270) : unused argument (. = 0 | . >= 270)" – maryadi Oct 23 '18 at 07:44
Can I use this style: filter_at(vars(a,h), all_vars(((a+h)<3.8)&((a+h)>0))) – maryadi Oct 23 '18 at 07:54
a and h is two columns date – maryadi Oct 23 '18 at 07:54
And if I want to delete row X1e is NA and X2e is not NA . I can use this – maryadi Oct 23 '18 at 08:03
for last one `df%>% filter_at(vars(X1e,X4e), all_vars(.<=0 | .>270))%>% filter(is.na(X1e) & !is.na(X2e))` – jyjek Oct 23 '18 at 08:04
And if I want to delete row a is NA and h is not NA . I can use this line: filter_at(vars(a,h), all_vars(((a=NA)&(h!=NA))|((a!=NA)&(h=NA)))) – maryadi Oct 23 '18 at 08:13
filter((is.na(a) & !is.na(h))|(!is.na(a) & is.na(h))) this delete all my data is there to filter two step? – maryadi Oct 23 '18 at 08:23

score 1 · Answer 4 · answered Oct 23 '18 at 06:59

Yet another solution.
I usually don't like subset because it uses non standard evaluation and it is slow but here it goes.

subset(df, (X1e <= 0 | X1e >= 270) & (X4e <= 0 | X4e >= 270))
#  X1e X2e X3e X4e
#1 360   0   0   0
#2 360   0   0   0
#4   0   0   0   0
#5   0   0   0   0
#6   0   0   0   0
#8 360   0 360   0

Filter data, remove entire dataframe

4 Answers4