Geek Answers Handbook

Delete rows from a data frame that contains only 0 or only one 0

I am trying to create a function in R that will allow me to filter my data set depending on whether the row contains one column with zero in it. Also, sometimes I only want to delete rows equal to zero in all columns.

Moreover, this is where he enjoys; not all columns contain numbers and the number of columns may vary.

I tried to insert some of my data here with the results I want to get.

unfiltered:
    ID  GeneName    DU145small  DU145total  PC3small    PC3total
    1   MIR22HG     33221.5     1224.55     2156.43     573.315
    2   MIRLET7E    87566.1     7737.99     25039.3     16415.6
    3   MIR612      0           0           530.068     0
    4   MIR218-1    0           0           1166.88     701.253
    5   MIR181B2    70723.2     3958.01     6209.85     1399.34
    6   MIR218-2    0           0           0           0
    7   MIR10B      787.516     330.556     0           20336.4
    8   MIR3176     0           0           0           0

any rows with containing a zero removed:
    ID  GeneName    DU145small  DU145total  PC3small    PC3total
    1   MIR22HG     33221.5     1224.55     2156.43     573.315
    2   MIRLET7E    87566.1     7737.99     25039.3     16415.6
    5   MIR181B2    70723.2     3958.01     6209.85     1399.34

only rows that is all zero is filtered away:
    ID  GeneName    DU145small  DU145total  PC3small    PC3total
    1   MIR22HG     33221.5     1224.55     2156.43     573.315
    2   MIRLET7E    87566.1     7737.99     25039.3     16415.6
    3   MIR612      0           0           530.068     0
    4   MIR218-1    0           0           1166.88     701.253
    5   MIR181B2    70723.2     3958.01     6209.85     1399.34
    7   MIR10B      787.516     330.556     0           20336.4

I found a way to remove any lines that had at least 1 zero, but it was a “spoof” by exchanging all zeros with NA, and then using the completed files to filter.

, , GeneName ( MIR10B).

, , , R .

EDIT: Xin Yin , .

+5
4

data.table (, df - )

library(data.table)
setDT(df)[, .SD[!all(.SD[, -1, with = F] == 0)], by = GeneName]

#    GeneName ID DU145small DU145total  PC3small  PC3total
# 1:  MIR22HG  1  33221.500   1224.550  2156.430   573.315
# 2: MIRLET7E  2  87566.100   7737.990 25039.300 16415.600
# 3:   MIR612  3      0.000      0.000   530.068     0.000
# 4: MIR218-1  4      0.000      0.000  1166.880   701.253
# 5: MIR181B2  5  70723.200   3958.010  6209.850  1399.340
# 6:   MIR10B  7    787.516    330.556     0.000 20336.400

,

setDT(df)[, .SD[!any(.SD[, -1, with = F] == 0)], by = GeneName]

#    GeneName ID DU145small DU145total PC3small  PC3total
# 1:  MIR22HG  1    33221.5    1224.55  2156.43   573.315
# 2: MIRLET7E  2    87566.1    7737.99 25039.30 16415.600
# 3: MIR181B2  5    70723.2    3958.01  6209.85  1399.340
+10

:

df[!rowSums(df[-c(1:2)] == 0) >= 1,]

:

df[!!rowSums(abs(df[-c(1:2)])),]

+3

Using rowSumsover a subset of columns, try the following:

#dummy data
df <- read.table(text="
ID  GeneName    DU145small  DU145total  PC3small    PC3total
1   MIR22HG     33221.5     1224.55     2156.43     573.315
2   MIRLET7E    87566.1     7737.99     25039.3     16415.6
3   MIR612      0           0           530.068     0
4   MIR218-1    0           0           1166.88     701.253
5   MIR181B2    70723.2     3958.01     6209.85     1399.34
6   MIR218-2    0           0           0           0
7   MIR10B      787.516     330.556     0           20336.4
8   MIR3176     0           0           0           0",
                 header=TRUE)
#remove any zero
df[ !rowSums(df[,colnames(df)[(3:ncol(df))]]==0)>=1, ]

#remove all zero
df[ !rowSums(df[,colnames(df)[(3:ncol(df))]]==0)==ncol(df)-2, ]
+2
source

It will work

> (unfiltered <- read.table(text="
+    ID  GeneName    DU145small  DU145total  PC3small    PC3total
+     1   MIR22HG     33221.5     1224.55     2156.43     573.315
+     2   MIRLET7E    87566.1     7737.99     25039.3     16415.6
+     3   MIR612      0           0           530.068     0
+     4   MIR218-1    0           0           1166.88     701.253
+     5   MIR181B2    70723.2     3958.01     6209.85     1399.34
+     6   MIR218-2    0           0           0           0
+     7   MIR10B      787.516     330.556     0           20336.4
+     8   MIR3176     0           0           0           0
+ ", header=T))
  ID GeneName DU145small DU145total  PC3small  PC3total
1  1  MIR22HG  33221.500   1224.550  2156.430   573.315
2  2 MIRLET7E  87566.100   7737.990 25039.300 16415.600
3  3   MIR612      0.000      0.000   530.068     0.000
4  4 MIR218-1      0.000      0.000  1166.880   701.253
5  5 MIR181B2  70723.200   3958.010  6209.850  1399.340
6  6 MIR218-2      0.000      0.000     0.000     0.000
7  7   MIR10B    787.516    330.556     0.000 20336.400
8  8  MIR3176      0.000      0.000     0.000     0.000
> 
> (any.zero <- unfiltered[!apply(unfiltered[, -c(1,2)], 1, function(row) any(row == 0)), ])
  ID GeneName DU145small DU145total PC3small  PC3total
1  1  MIR22HG    33221.5    1224.55  2156.43   573.315
2  2 MIRLET7E    87566.1    7737.99 25039.30 16415.600
5  5 MIR181B2    70723.2    3958.01  6209.85  1399.340
> (all.zero <- unfiltered[!apply(unfiltered[, -c(1,2)], 1, function(row) all(row == 0)), ])
  ID GeneName DU145small DU145total  PC3small  PC3total
1  1  MIR22HG  33221.500   1224.550  2156.430   573.315
2  2 MIRLET7E  87566.100   7737.990 25039.300 16415.600
3  3   MIR612      0.000      0.000   530.068     0.000
4  4 MIR218-1      0.000      0.000  1166.880   701.253
5  5 MIR181B2  70723.200   3958.010  6209.850  1399.340
7  7   MIR10B    787.516    330.556     0.000 20336.400
+1
source

More articles:


All Articles