Replacing with zero values below the treshold, but starting from specific cell

Question

I have a matrix in which I want to replace all values with 0 if they are below 25. I want to keep first row and column.

         p1    p10  p16 p19 p25 p3  p5  p6  p8  p9
call1   567     0   3   0   18  17  8   4   6   7
call20  4900    7   6   2   23  26  20  14  12  29
echo34  73784   1   4   1   6   4   1   4   8   5
kol45   145873  6   4   0   11  17  5   9   22  11

When I am removing those values, I am also removing row and column names and I don't want that.

You've tagged the statistical program r here; do answers need to use r for the solution (noticing that we already have one perl and two awk answers). — Jeff Schaller, Jun 20 '18 at 16:23

score 1 · Answer 1 · answered Jun 20 '18 at 15:49

1

You can try this awk

awk 'NR>1{for(i=2;i<=NF;i++)$i=$i<25?0:$i}1' infile

NR>1 keep the first line
i=2 keep the first column

answered Jun 20 '18 at 15:49

ctac_

1,960

score 1 · Answer 2 · answered Jun 20 '18 at 19:56

Using R:

dat <- as.matrix(read.table(text="p1    p10  p16 p19 p25 p3  p5  p6  p8  p9
call1   567     0   3   0   18  17  8   4   6   7
call20  4900    7   6   2   23  26  20  14  12  29
echo34  73784   1   4   1   6   4   1   4   8   5
kol45   145873  6   4   0   11  17  5   9   22  11"))

dat
#            p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
# call1     567   0   3   0  18 17  8  4  6  7
# call20   4900   7   6   2  23 26 20 14 12 29
# echo34  73784   1   4   1   6  4  1  4  8  5
# kol45  145873   6   4   0  11 17  5  9 22 11

dat[-1, -1][dat[-1, -1] < 25] <- 0

dat
#            p1 p10 p16 p19 p25 p3 p5 p6 p8 p9
# call1     567   0   3   0  18 17  8  4  6  7
# call20   4900   0   0   0   0 26  0  0  0 29
# echo34  73784   0   0   0   0  0  0  0  0  0
# kol45  145873   0   0   0   0  0  0  0  0  0

score 0 · Answer 3 · answered Jun 20 '18 at 15:04

This seems to do the trick:

$ awk '{ for (field=1;field<=NF;field++) { if( NR > 1 && field>=2 && $field < 25 ) { $field=0 } } print }' input
         p1    p10  p16 p19 p25 p3  p5  p6  p8  p9
call1 567 0 0 0 0 0 0 0 0 0
call20 4900 0 0 0 0 26 0 0 0 29
echo34 73784 0 0 0 0 0 0 0 0 0
kol45 145873 0 0 0 0 0 0 0 0 0

score 0 · Answer 4 · answered Jun 20 '18 at 16:21

To preserve the spacing, you could do something like:

perl -pe '
  if ($. > 1) {
    $skip = 2;
    s{ +\d+}{
      --$skip > 0 || $& >= 25 ? $& : " " x (length($&) - 1) . "0"
    }ge
  }' < file

Compressed:

perl -pe'if($.>1){$s=2;s@ +\d+@--$s>0||$&>24?$&:" "x(length($&)-1)."0"@ge}' file

Replacing with zero values below the treshold, but starting from specific cell

4 Answers4