Convert zeros in data.frame to another number in R

Question

I have a dataframe where I cannot have values equal to ZERO. I would like R to identify ZERO values in specific columns (3rd to 12th) and convert to a limit of 0.0001 for example.

for example

df=read.table(text="st  Date    OD  pH  DBO st  year    Veg Water   Soil    Crop    Grass
A   01/07/2005  8   6.3 3   A   2005    100 200 80  130 70
A   02/06/2005  7   6.2 2.2 A   2005    100 200 80  130 70
A   01/01/2005  0   6.5 3.1 A   2005    100 200 80  130 70
A   03/05/2006  6   6.3 4   A   2005    100 200 80  130 70
A   09/08/2006  6.8 7.1 1.1 A   2005    100 200 80  130 70
A   12/12/2006  7.3 8.1 2.9 A   2005    100 200 80  130 70
B   02/07/2005  6.8 5.4 2.6 B   2006    98  180 0.1 132 86
B   03/06/2005  6   5.3 1.9 B   2006    98  180 84  132 86
B   02/01/2005  6.2 5.5 2.6 B   2006    98  180 84  132 86
B   04/05/2006  0   5.4 0   B   2006    98  0   84  132 0
B   10/08/2006  5.8 6   0.9 B   2006    98  0   84  132 86
B   13/12/2006  6.2 6.9 2.5 B   2006    98  0   84  132 86
C   20/12/2006  6.5 7.2 2.6 C   2007    93  0   79  127 106
C   27/12/2006  6.8 7.6 2.7 C   2007    93  0   79  127 0
C   03/01/2007  7.2 8   2.9 C   2007    93  0   79  127 106
C   10/01/2007  7.5 8.4 3   C   2007    93  0   0   127 106
C   17/01/2007  7.9 8.8 0   C   2007    93  175 79  127 106
C   24/01/2007  8.3 9.2 3.3 C   2007    93  175 79  0   106
C   31/01/2007  8.7 9.7 3.5 C   2007    93  175 79  127 106
C   07/02/2007  9.2 10.2    3.6 C   2007    93  175 79  0   106", sep="", header=TRUE)

score 2 · Answer 1 · answered Nov 30 '21 at 21:31

You can use pmax(1e-5, .) for that.

isnum <- intersect(3:12, which(sapply(df, is.numeric)))
isnum
# [1]  3  4  5  7  8  9 10 11 12
df[isnum] <- lapply(df[isnum], pmax, 1e-5)
df
#    st       Date      OD   pH     DBO st.1 year Veg    Water    Soil     Crop    Grass
# 1   A 01/07/2005 8.00000  6.3 3.00000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 2   A 02/06/2005 7.00000  6.2 2.20000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 3   A 01/01/2005 0.00001  6.5 3.10000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 4   A 03/05/2006 6.00000  6.3 4.00000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 5   A 09/08/2006 6.80000  7.1 1.10000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 6   A 12/12/2006 7.30000  8.1 2.90000    A 2005 100 2.00e+02 8.0e+01 1.30e+02 7.00e+01
# 7   B 02/07/2005 6.80000  5.4 2.60000    B 2006  98 1.80e+02 1.0e-01 1.32e+02 8.60e+01
# 8   B 03/06/2005 6.00000  5.3 1.90000    B 2006  98 1.80e+02 8.4e+01 1.32e+02 8.60e+01
# 9   B 02/01/2005 6.20000  5.5 2.60000    B 2006  98 1.80e+02 8.4e+01 1.32e+02 8.60e+01
# 10  B 04/05/2006 0.00001  5.4 0.00001    B 2006  98 1.00e-05 8.4e+01 1.32e+02 1.00e-05
# 11  B 10/08/2006 5.80000  6.0 0.90000    B 2006  98 1.00e-05 8.4e+01 1.32e+02 8.60e+01
# 12  B 13/12/2006 6.20000  6.9 2.50000    B 2006  98 1.00e-05 8.4e+01 1.32e+02 8.60e+01
# 13  C 20/12/2006 6.50000  7.2 2.60000    C 2007  93 1.00e-05 7.9e+01 1.27e+02 1.06e+02
# 14  C 27/12/2006 6.80000  7.6 2.70000    C 2007  93 1.00e-05 7.9e+01 1.27e+02 1.00e-05
# 15  C 03/01/2007 7.20000  8.0 2.90000    C 2007  93 1.00e-05 7.9e+01 1.27e+02 1.06e+02
# 16  C 10/01/2007 7.50000  8.4 3.00000    C 2007  93 1.00e-05 1.0e-05 1.27e+02 1.06e+02
# 17  C 17/01/2007 7.90000  8.8 0.00001    C 2007  93 1.75e+02 7.9e+01 1.27e+02 1.06e+02
# 18  C 24/01/2007 8.30000  9.2 3.30000    C 2007  93 1.75e+02 7.9e+01 1.00e-05 1.06e+02
# 19  C 31/01/2007 8.70000  9.7 3.50000    C 2007  93 1.75e+02 7.9e+01 1.27e+02 1.06e+02
# 20  C 07/02/2007 9.20000 10.2 3.60000    C 2007  93 1.75e+02 7.9e+01 1.00e-05 1.06e+02

In case of negative values, this will have an issue. Please fix that — Onyambu, Nov 30 '21 at 21:48
(1) Sample data has no negatives. (2) All column headers imply quantity measurements, in which case a negative value is unexpected. — r2evans, Nov 30 '21 at 22:33

AndrewGB · Accepted Answer · 2021-12-01T01:02:45.813

You can also do this with dplyr.

library(tidyverse)

df %>%
  mutate(across(OD:Grass, ~ replace(., . ==  0 , 0.0001)))

Output

   st       Date     OD   pH    DBO st.1 year Veg    Water    Soil     Crop    Grass
1   A 01/07/2005 8.0000  6.3 3.0000    A 2005 100 200.0000 80.0000 130.0000  70.0000
2   A 02/06/2005 7.0000  6.2 2.2000    A 2005 100 200.0000 80.0000 130.0000  70.0000
3   A 01/01/2005 0.0001  6.5 3.1000    A 2005 100 200.0000 80.0000 130.0000  70.0000
4   A 03/05/2006 6.0000  6.3 4.0000    A 2005 100 200.0000 80.0000 130.0000  70.0000
5   A 09/08/2006 6.8000  7.1 1.1000    A 2005 100 200.0000 80.0000 130.0000  70.0000
6   A 12/12/2006 7.3000  8.1 2.9000    A 2005 100 200.0000 80.0000 130.0000  70.0000
7   B 02/07/2005 6.8000  5.4 2.6000    B 2006  98 180.0000  0.1000 132.0000  86.0000
8   B 03/06/2005 6.0000  5.3 1.9000    B 2006  98 180.0000 84.0000 132.0000  86.0000
9   B 02/01/2005 6.2000  5.5 2.6000    B 2006  98 180.0000 84.0000 132.0000  86.0000
10  B 04/05/2006 0.0001  5.4 0.0001    B 2006  98   0.0001 84.0000 132.0000   0.0001
11  B 10/08/2006 5.8000  6.0 0.9000    B 2006  98   0.0001 84.0000 132.0000  86.0000
12  B 13/12/2006 6.2000  6.9 2.5000    B 2006  98   0.0001 84.0000 132.0000  86.0000
13  C 20/12/2006 6.5000  7.2 2.6000    C 2007  93   0.0001 79.0000 127.0000 106.0000
14  C 27/12/2006 6.8000  7.6 2.7000    C 2007  93   0.0001 79.0000 127.0000   0.0001
15  C 03/01/2007 7.2000  8.0 2.9000    C 2007  93   0.0001 79.0000 127.0000 106.0000
16  C 10/01/2007 7.5000  8.4 3.0000    C 2007  93   0.0001  0.0001 127.0000 106.0000
17  C 17/01/2007 7.9000  8.8 0.0001    C 2007  93 175.0000 79.0000 127.0000 106.0000
18  C 24/01/2007 8.3000  9.2 3.3000    C 2007  93 175.0000 79.0000   0.0001 106.0000
19  C 31/01/2007 8.7000  9.7 3.5000    C 2007  93 175.0000 79.0000 127.0000 106.0000
20  C 07/02/2007 9.2000 10.2 3.6000    C 2007  93 175.0000 79.0000   0.0001 106.0000

this approach is too much for using the `%>%` pipe but the columns are in character mode how would that solve? — wesleysc352, Nov 30 '21 at 22:07
@wesleysc352 Apologies, I put 0.0001 in quotation marks, which is why it converted to character. But I have updated the answer and output so that it won't do that. — AndrewGB, Nov 30 '21 at 22:39
`c()` is not needed inside `across`; This can be replaced with `mutate(across(OD:Grass, ~replace.....` — GuedesBF, Dec 01 '21 at 00:46
FYI, if it's ever possible that `st.1` could contain the literal `"0"` (perhaps unlikely?), then you might want to condition the column-selection on `is.numeric`. — r2evans, Dec 01 '21 at 00:48

score 1 · Answer 3 · answered Nov 30 '21 at 21:48

df[3:12][df[3:12]==0] <- 0.0001
df
   st       Date     OD   pH    DBO st.1 year Veg    Water    Soil     Crop    Grass
1   A 01/07/2005 8.0000  6.3 3.0000    A 2005 100 200.0000 80.0000 130.0000  70.0000
2   A 02/06/2005 7.0000  6.2 2.2000    A 2005 100 200.0000 80.0000 130.0000  70.0000
3   A 01/01/2005 0.0001  6.5 3.1000    A 2005 100 200.0000 80.0000 130.0000  70.0000
4   A 03/05/2006 6.0000  6.3 4.0000    A 2005 100 200.0000 80.0000 130.0000  70.0000
5   A 09/08/2006 6.8000  7.1 1.1000    A 2005 100 200.0000 80.0000 130.0000  70.0000
6   A 12/12/2006 7.3000  8.1 2.9000    A 2005 100 200.0000 80.0000 130.0000  70.0000
7   B 02/07/2005 6.8000  5.4 2.6000    B 2006  98 180.0000  0.1000 132.0000  86.0000
8   B 03/06/2005 6.0000  5.3 1.9000    B 2006  98 180.0000 84.0000 132.0000  86.0000
9   B 02/01/2005 6.2000  5.5 2.6000    B 2006  98 180.0000 84.0000 132.0000  86.0000
10  B 04/05/2006 0.0001  5.4 0.0001    B 2006  98   0.0001 84.0000 132.0000   0.0001
11  B 10/08/2006 5.8000  6.0 0.9000    B 2006  98   0.0001 84.0000 132.0000  86.0000
12  B 13/12/2006 6.2000  6.9 2.5000    B 2006  98   0.0001 84.0000 132.0000  86.0000
13  C 20/12/2006 6.5000  7.2 2.6000    C 2007  93   0.0001 79.0000 127.0000 106.0000
14  C 27/12/2006 6.8000  7.6 2.7000    C 2007  93   0.0001 79.0000 127.0000   0.0001
15  C 03/01/2007 7.2000  8.0 2.9000    C 2007  93   0.0001 79.0000 127.0000 106.0000
16  C 10/01/2007 7.5000  8.4 3.0000    C 2007  93   0.0001  0.0001 127.0000 106.0000
17  C 17/01/2007 7.9000  8.8 0.0001    C 2007  93 175.0000 79.0000 127.0000 106.0000
18  C 24/01/2007 8.3000  9.2 3.3000    C 2007  93 175.0000 79.0000   0.0001 106.0000
19  C 31/01/2007 8.7000  9.7 3.5000    C 2007  93 175.0000 79.0000 127.0000 106.0000
20  C 07/02/2007 9.2000 10.2 3.6000    C 2007  93 175.0000 79.0000   0.0001 106.0000

In case that `==0` does not work (https://stackoverflow.com/q/9508518, https://stackoverflow.com/q/588004), this too will have an issue. — r2evans, Nov 30 '21 at 22:32

Andre Wildberg · Answer 4 · 2021-11-30T22:55:35.530

Try with runif

#z <- df[,3:12] == 0
z <- df[,3:12] < 1e-15 & df[,3:12] > -1e-15 # addressing floating point accuracy

df[3:12][z] <- runif( length( which( z ) ), 0, 1e-05)

df
   st       Date           OD   pH          DBO st.1 year Veg        Water
1   A 01/07/2005 8.000000e+00  6.3 3.000000e+00    A 2005 100 2.000000e+02
2   A 02/06/2005 7.000000e+00  6.2 2.200000e+00    A 2005 100 2.000000e+02
3   A 01/01/2005 9.398892e-06  6.5 3.100000e+00    A 2005 100 2.000000e+02
4   A 03/05/2006 6.000000e+00  6.3 4.000000e+00    A 2005 100 2.000000e+02
5   A 09/08/2006 6.800000e+00  7.1 1.100000e+00    A 2005 100 2.000000e+02
6   A 12/12/2006 7.300000e+00  8.1 2.900000e+00    A 2005 100 2.000000e+02
7   B 02/07/2005 6.800000e+00  5.4 2.600000e+00    B 2006  98 1.800000e+02
8   B 03/06/2005 6.000000e+00  5.3 1.900000e+00    B 2006  98 1.800000e+02
9   B 02/01/2005 6.200000e+00  5.5 2.600000e+00    B 2006  98 1.800000e+02
10  B 04/05/2006 7.465607e-06  5.4 1.840605e-06    B 2006  98 2.418576e-06
11  B 10/08/2006 5.800000e+00  6.0 9.000000e-01    B 2006  98 8.369634e-06
12  B 13/12/2006 6.200000e+00  6.9 2.500000e+00    B 2006  98 4.785023e-06
13  C 20/12/2006 6.500000e+00  7.2 2.600000e+00    C 2007  93 3.208513e-06
14  C 27/12/2006 6.800000e+00  7.6 2.700000e+00    C 2007  93 9.316095e-06
15  C 03/01/2007 7.200000e+00  8.0 2.900000e+00    C 2007  93 3.188397e-06
16  C 10/01/2007 7.500000e+00  8.4 3.000000e+00    C 2007  93 8.296945e-06
17  C 17/01/2007 7.900000e+00  8.8 9.138739e-06    C 2007  93 1.750000e+02
18  C 24/01/2007 8.300000e+00  9.2 3.300000e+00    C 2007  93 1.750000e+02
19  C 31/01/2007 8.700000e+00  9.7 3.500000e+00    C 2007  93 1.750000e+02
20  C 07/02/2007 9.200000e+00 10.2 3.600000e+00    C 2007  93 1.750000e+02
           Soil         Crop        Grass
1  8.000000e+01 1.300000e+02 7.000000e+01
2  8.000000e+01 1.300000e+02 7.000000e+01
3  8.000000e+01 1.300000e+02 7.000000e+01
4  8.000000e+01 1.300000e+02 7.000000e+01
5  8.000000e+01 1.300000e+02 7.000000e+01
6  8.000000e+01 1.300000e+02 7.000000e+01
7  1.000000e-01 1.320000e+02 8.600000e+01
8  8.400000e+01 1.320000e+02 8.600000e+01
9  8.400000e+01 1.320000e+02 8.600000e+01
10 8.400000e+01 1.320000e+02 9.053972e-06
11 8.400000e+01 1.320000e+02 8.600000e+01
12 8.400000e+01 1.320000e+02 8.600000e+01
13 7.900000e+01 1.270000e+02 1.060000e+02
14 7.900000e+01 1.270000e+02 8.210766e-06
15 7.900000e+01 1.270000e+02 1.060000e+02
16 1.481542e-06 1.270000e+02 1.060000e+02
17 7.900000e+01 1.270000e+02 1.060000e+02
18 7.900000e+01 9.491512e-06 1.060000e+02
19 7.900000e+01 1.270000e+02 1.060000e+02
20 7.900000e+01 2.509321e-06 1.060000e+02

In case that `==0` does not work (https://stackoverflow.com/q/9508518, https://stackoverflow.com/q/588004), this too will have an issue. — r2evans, Nov 30 '21 at 22:33

jpdugo17 · Answer 5 · 2021-11-30T23:22:23.463

We can try mapping through all numeric columns and when a zero (or a very small zero say 0.0000001) is found, then replace it with 0.0001.

library(tidyverse)

map_if(df[, 3:12], is.numeric, ~ ifelse(near(., 0), .0001, .)) %>%
    bind_cols() %>% 
    {bind_cols(df[1:2], .)}
#>    st       Date     OD   pH    DBO st.1 year Veg    Water    Soil     Crop
#> 1   A 01/07/2005 8.0000  6.3 3.0000    A 2005 100 200.0000 80.0000 130.0000
#> 2   A 02/06/2005 7.0000  6.2 2.2000    A 2005 100 200.0000 80.0000 130.0000
#> 3   A 01/01/2005 0.0001  6.5 3.1000    A 2005 100 200.0000 80.0000 130.0000
#> 4   A 03/05/2006 6.0000  6.3 4.0000    A 2005 100 200.0000 80.0000 130.0000
#> 5   A 09/08/2006 6.8000  7.1 1.1000    A 2005 100 200.0000 80.0000 130.0000
#> 6   A 12/12/2006 7.3000  8.1 2.9000    A 2005 100 200.0000 80.0000 130.0000
#> 7   B 02/07/2005 6.8000  5.4 2.6000    B 2006  98 180.0000  0.1000 132.0000
#> 8   B 03/06/2005 6.0000  5.3 1.9000    B 2006  98 180.0000 84.0000 132.0000
#> 9   B 02/01/2005 6.2000  5.5 2.6000    B 2006  98 180.0000 84.0000 132.0000
#> 10  B 04/05/2006 0.0001  5.4 0.0001    B 2006  98   0.0001 84.0000 132.0000
#> 11  B 10/08/2006 5.8000  6.0 0.9000    B 2006  98   0.0001 84.0000 132.0000
#> 12  B 13/12/2006 6.2000  6.9 2.5000    B 2006  98   0.0001 84.0000 132.0000
#> 13  C 20/12/2006 6.5000  7.2 2.6000    C 2007  93   0.0001 79.0000 127.0000
#> 14  C 27/12/2006 6.8000  7.6 2.7000    C 2007  93   0.0001 79.0000 127.0000
#> 15  C 03/01/2007 7.2000  8.0 2.9000    C 2007  93   0.0001 79.0000 127.0000
#> 16  C 10/01/2007 7.5000  8.4 3.0000    C 2007  93   0.0001  0.0001 127.0000
#> 17  C 17/01/2007 7.9000  8.8 0.0001    C 2007  93 175.0000 79.0000 127.0000
#> 18  C 24/01/2007 8.3000  9.2 3.3000    C 2007  93 175.0000 79.0000   0.0001
#> 19  C 31/01/2007 8.7000  9.7 3.5000    C 2007  93 175.0000 79.0000 127.0000
#> 20  C 07/02/2007 9.2000 10.2 3.6000    C 2007  93 175.0000 79.0000   0.0001
#>       Grass
#> 1   70.0000
#> 2   70.0000
#> 3   70.0000
#> 4   70.0000
#> 5   70.0000
#> 6   70.0000
#> 7   86.0000
#> 8   86.0000
#> 9   86.0000
#> 10   0.0001
#> 11  86.0000
#> 12  86.0000
#> 13 106.0000
#> 14   0.0001
#> 15 106.0000
#> 16 106.0000
#> 17 106.0000
#> 18 106.0000
#> 19 106.0000
#> 20 106.0000

Using across:

df %>%
    mutate(across(where(is.numeric), ~ifelse(near(., 0), 0.0001, .)))
#>    st       Date     OD   pH    DBO st.1 year Veg    Water    Soil     Crop
#> 1   A 01/07/2005 8.0000  6.3 3.0000    A 2005 100 200.0000 80.0000 130.0000
#> 2   A 02/06/2005 7.0000  6.2 2.2000    A 2005 100 200.0000 80.0000 130.0000
#> 3   A 01/01/2005 0.0001  6.5 3.1000    A 2005 100 200.0000 80.0000 130.0000
#> 4   A 03/05/2006 6.0000  6.3 4.0000    A 2005 100 200.0000 80.0000 130.0000
#> 5   A 09/08/2006 6.8000  7.1 1.1000    A 2005 100 200.0000 80.0000 130.0000
#> 6   A 12/12/2006 7.3000  8.1 2.9000    A 2005 100 200.0000 80.0000 130.0000
#> 7   B 02/07/2005 6.8000  5.4 2.6000    B 2006  98 180.0000  0.1000 132.0000
#> 8   B 03/06/2005 6.0000  5.3 1.9000    B 2006  98 180.0000 84.0000 132.0000
#> 9   B 02/01/2005 6.2000  5.5 2.6000    B 2006  98 180.0000 84.0000 132.0000
#> 10  B 04/05/2006 0.0001  5.4 0.0001    B 2006  98   0.0001 84.0000 132.0000
#> 11  B 10/08/2006 5.8000  6.0 0.9000    B 2006  98   0.0001 84.0000 132.0000
#> 12  B 13/12/2006 6.2000  6.9 2.5000    B 2006  98   0.0001 84.0000 132.0000
#> 13  C 20/12/2006 6.5000  7.2 2.6000    C 2007  93   0.0001 79.0000 127.0000
#> 14  C 27/12/2006 6.8000  7.6 2.7000    C 2007  93   0.0001 79.0000 127.0000
#> 15  C 03/01/2007 7.2000  8.0 2.9000    C 2007  93   0.0001 79.0000 127.0000
#> 16  C 10/01/2007 7.5000  8.4 3.0000    C 2007  93   0.0001  0.0001 127.0000
#> 17  C 17/01/2007 7.9000  8.8 0.0001    C 2007  93 175.0000 79.0000 127.0000
#> 18  C 24/01/2007 8.3000  9.2 3.3000    C 2007  93 175.0000 79.0000   0.0001
#> 19  C 31/01/2007 8.7000  9.7 3.5000    C 2007  93 175.0000 79.0000 127.0000
#> 20  C 07/02/2007 9.2000 10.2 3.6000    C 2007  93 175.0000 79.0000   0.0001
#>       Grass
#> 1   70.0000
#> 2   70.0000
#> 3   70.0000
#> 4   70.0000
#> 5   70.0000
#> 6   70.0000
#> 7   86.0000
#> 8   86.0000
#> 9   86.0000
#> 10   0.0001
#> 11  86.0000
#> 12  86.0000
#> 13 106.0000
#> 14   0.0001
#> 15 106.0000
#> 16 106.0000
#> 17 106.0000
#> 18 106.0000
#> 19 106.0000
#> 20 106.0000

^{Created on 2021-11-30 by the reprex package (v2.0.1)}

Convert zeros in data.frame to another number in R

5 Answers5