1

I have a tab delimited table

  a b c
A 5 2 0
B 0 5 4
C 4 3 4
D 2 0 2

I want to change the non-zero values to "1", without changing the column or row names.

Desired output:

  a b c
A 1 1 0
B 0 1 1
C 1 1 1
D 1 0 1

To clarify, this is an example table. The letters are variables representing the column/row names - there may be hundreds of columns and rows. The non-zero values (given here as numbers) may not necessarily be numbers - they might the names of people for example.

  • 1
    With the additional info that you have added about names of people etc. it becomes very important to know what the delimiter is between the columns. – Kusalananda Jan 24 '17 at 15:03
  • 1
    What does it need to do with values like 000, 0.0, -0, 0x0, +0e20? – Stéphane Chazelas Jan 24 '17 at 15:13
4

Assuming strictly tab-delimited input:

$ cat data.in
        a       b       c
A       nancy   bilbo baggins   0
B       0       darcy   bender
C       phantom menace  Unix    !!
D       last row        0       the end

$ cat -t data.in
^Ia^Ib^Ic
A^Inancy^Ibilbo baggins^I0
B^I0^Idarcy^Ibender
C^Iphantom menace^IUnix^I!!
D^Ilast row^I0^Ithe end

An awk script to do the job:

BEGIN { OFS = FS = "\t" }

NR != 1 {
    for (i = 2; i <= NF; ++i) {
        if ($i != "0") {
            $i = "1";
        }
    }
}

{ print }

Running it:

$ awk -f script.awk data.in
        a       b       c
A       1       1       0
B       0       1       1
C       1       1       1
D       1       0       1

The script compares each field (column) with the single character 0 (except for the first field) and replaces everything that isn't exactly 0 with a 1. The output will be tab-delimited.

  • This works perfectly! – P.tin Jan 24 '17 at 15:46
2
sed '1!s/ [^ ]*[^ 0][^ ]*/ 1/g'

Would replace any sequence of non-space characters that contains at least one character other than 0 (and that follows a space) with 1 (except on the first line).

IOW, that would replace anything other than sequences of 0s and the first column and row with 1.

1

Considering that headers does not include numbers like your sample, the most easy way i can think of is this:

sed 's/[1-9]/1/g' file.txt

mind the range : 1 up to 9 , zero excluded.

$ echo "A 5 2 0" |sed 's/[1-9]/1/g'
A 1 1 0

This works If the numbers in columns is up to 9. If this is not the case and numbers can be 10 or more i have to revise.

According to OP latest clarification , since non zero entries can be names, etc this will NOT work.

  • 1
    you can in fact say [2-9], since replacing 1 with 1 is not necessary :) Also, what if the numbers are 10 or bigger? – fedorqui Jan 24 '17 at 15:10
  • @fedorqui According to the question, it's not numbers at all... – Kusalananda Jan 24 '17 at 15:14
  • @Kusalananda ok, I see. Then probably the OP should provide more representative sample data, so we get a better idea. – fedorqui Jan 24 '17 at 15:29
0

This is a good task for Python.

$ cat input.txt
    a   b   c
A   alpha   beta    0
B   0   gamma   zeta
C   alpha   phi omega
D   kappa   0   delta

$ ./replace_nonzero.py  < input.txt                                                                                      
a   b   c
A   1   1   0
B   0   1   1
C   1   1   1
D   1   0   1

And here's the replace_nonzero.py itself:

#!/usr/bin/env python
from __future__ import print_function
import sys

for index,line in enumerate(sys.stdin):
   if index == 0 :
       print(line.strip())
       continue
   words = line.strip().split()
   print(words[0],end="\t")

   new_line = []
   for word in words[1:]:
      if word.isdigit() and int(word) == 0:
          new_line.append('0')
      else:
          new_line.append('1')
   print("\t".join(new_line))

The way this works is simple: we skip first line since it's special case, and break down all other lines into words, and examine those starting from position 1. The logic here works from the opposite assumption - if the word that we got is a digit and is zero, then we append a string '0' into new list (which is a list of words per each line), otherwise - we append string '1'. Finally we take the new list , and print it out, again - tab-delimited.

0

This only works in bash

bash$ paste <(cut -f1 file) <(cut -f2- file |
        sed -r '1b;        # if title line then skip to end
        s#\t#\n#g          # seperate line to multi-line
        s#.*[^0].*#1#Mg    # apply multi-line operation 
        s#\n#\t#g' )       # turn to one line

        a       b       c
A       1       1       0
B       0       1       1
C       1       1       1
D       1       0       1
  • Worth noting that this only works where process substitution is supported, e.g. bash. – phk Jan 25 '17 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.