<p>Not an expert, but :</p>
<pre><code>awk 'BEGIN { FS = OFS = &quot;\t&quot; } { for(i=4; i&lt;=6; i++) if(<span class="math-container">$i ~ /^ *$</span>/) {<span class="math-container">$i = 0} else {$</span>i = 1} }; 1'
</code></pre>
<p>Since you have a very specific file format, this code starts from field 4, checking whether the fields after it are empty or not. I assume also that know that you file has 6 fields max exactly, so I &quot;hardcoded&quot; this knowledge.</p>

CC BY-SA 4.0
2023-06-21T14:02:24.140Z
Great it's working... Thanks a lot, @Sam.
Deb
CC BY-SA 4.0
2023-06-21T05:46:56.003Z
2023-06-21T05:46:56.003Z
Sam
<p>I want to convert a .tsv file table in binary format (1 and 0).  In which all cells containing text would become <code>1</code> and blank cells would become <code>0</code>,  except first three columns.</p>
<p>The given .tsv file has 6 fields</p>
<pre><code>head adrbvbeT_copy.tsv 
chr1    1   100         
chr1    101 200         
chr1    201 300         
chr1    301 400         
chr1    401 500         
chr1    501 600         
chr1    601 700         
chr1    701 800         
chr1    801 900         
chr1    901 1000    

tail adrbvbeT.tsv 
chrY    57226501    57226600            
chrY    57226601    57226700    Mmusculus-jaspar2016-Tcf21-MA0832.1     
chrY    57226701    57226800    Mmusculus-HOCOMOCOv10-TF7L2_MOUSE.H10MO.A       
chrY    57226801    57226900    Mmusculus-HOCOMOCOv10-STAT1_MOUSE.H10MO.A       
chrY    57226901    57227000    Mmusculus-HOCOMOCOv10-ZEP2_MOUSE.H10MO.D        
chrY    57227001    57227100    NA-SwissRegulon-SMARCC2.SwissRegulon    Mmusculus-HOCOMOCOv10-PBX2_MOUSE.H10MO.C    
chrY    57227101    57227200    Mmusculus-HOCOMOCOv10-ZIC3_MOUSE.H10MO.C    Mmusculus-HOCOMOCOv10-TBX5_MOUSE.H10MO.D    Mmusculus-HOCOMOCOv10-PIT1_MOUSE.H10MO.C
chrY    57227201    57227300    Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D    Mmusculus-UniPROBE-Tbp.UP00029  Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D
chrY    57227301    57227400    Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D   Hsapiens-jaspar2016-TGIF2-MA0797.1  Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D
chrY    57227401    57227415    Mmusculus-HOCOMOCOv10-TFDP1_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-TEAD4_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-ZBTB6_MOUSE.H10MO.D
</code></pre>
<p>First, I converted all blank cells to <code>0</code>. Now I'm wondering how I can replace the text in the cells with <code>1</code>.</p>
<pre><code>awk 'BEGIN { FS = OFS = &quot;\t&quot; } { for(i=1; i&lt;=NF; i++) if(<span class="math-container">$i ~ /^ *$</span>/) $i = 0 }; 1' adrbvbeT_copy.tsv &gt; adrbvbeT_copyN0.tsv

head adrbvbeT_copyN0.tsv 
chr1    1   100 0   0   0
chr1    101 200 0   0   0
chr1    201 300 0   0   0
chr1    301 400 0   0   0
chr1    401 500 0   0   0
chr1    501 600 0   0   0
chr1    601 700 0   0   0
chr1    701 800 0   0   0
chr1    801 900 0   0   0
chr1    901 1000    0   0   0


tail adrbvbeT_copyN0.tsv 
chrY    57226501    57226600    0   0   0
chrY    57226601    57226700    Mmusculus-jaspar2016-Tcf21-MA0832.1 0   0
chrY    57226701    57226800    Mmusculus-HOCOMOCOv10-TF7L2_MOUSE.H10MO.A   0   0
chrY    57226801    57226900    Mmusculus-HOCOMOCOv10-STAT1_MOUSE.H10MO.A   0   0
chrY    57226901    57227000    Mmusculus-HOCOMOCOv10-ZEP2_MOUSE.H10MO.D    0   0
chrY    57227001    57227100    NA-SwissRegulon-SMARCC2.SwissRegulon    Mmusculus-HOCOMOCOv10-PBX2_MOUSE.H10MO.C    0
chrY    57227101    57227200    Mmusculus-HOCOMOCOv10-ZIC3_MOUSE.H10MO.C    Mmusculus-HOCOMOCOv10-TBX5_MOUSE.H10MO.D    Mmusculus-HOCOMOCOv10-PIT1_MOUSE.H10MO.C
chrY    57227201    57227300    Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D    Mmusculus-UniPROBE-Tbp.UP00029  Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D
chrY    57227301    57227400    Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D   Hsapiens-jaspar2016-TGIF2-MA0797.1  Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D
chrY    57227401    57227415    Mmusculus-HOCOMOCOv10-TFDP1_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-TEAD4_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-ZBTB6_MOUSE.H10MO.D
</code></pre>

CC BY-SA 4.0
2023-06-21T12:02:38.040Z
You can use `if (!$i)` -- an empty string is false, and `0` is false. And if the field has the value zero, we're setting it to zero anyway.
glenn jackman
CC BY-SA 4.0
2023-06-21T13:24:21.523Z
Why do you need to do this? It seems like [an XY problem](https://xyproblem.info/)
Ram RS
<bash><bed><awk><text-processing><command-line>
How can I change all text in a .tsv file to 1, except the first three fields?
# How can I change all text in a .tsv file to 1, except the first three fields?

(Asked by: username_0 on 2023-06-21)

I want to convert a .tsv file table in binary format (1 and 0).  In which all cells containing text would become 1 and blank cells would become 0,  except first three columns.
The given .tsv file has 6 fields
head adrbvbeT_copy.tsv 
chr1    1   100         
chr1    101 200         
chr1    201 300         
chr1    301 400         
chr1    401 500         
chr1    501 600         
chr1    601 700         
chr1    701 800         
chr1    801 900         
chr1    901 1000    

tail adrbvbeT.tsv 
chrY    57226501    57226600            
chrY    57226601    57226700    Mmusculus-jaspar2016-Tcf21-MA0832.1     
chrY    57226701    57226800    Mmusculus-HOCOMOCOv10-TF7L2_MOUSE.H10MO.A       
chrY    57226801    57226900    Mmusculus-HOCOMOCOv10-STAT1_MOUSE.H10MO.A       
chrY    57226901    57227000    Mmusculus-HOCOMOCOv10-ZEP2_MOUSE.H10MO.D        
chrY    57227001    57227100    NA-SwissRegulon-SMARCC2.SwissRegulon    Mmusculus-HOCOMOCOv10-PBX2_MOUSE.H10MO.C    
chrY    57227101    57227200    Mmusculus-HOCOMOCOv10-ZIC3_MOUSE.H10MO.C    Mmusculus-HOCOMOCOv10-TBX5_MOUSE.H10MO.D    Mmusculus-HOCOMOCOv10-PIT1_MOUSE.H10MO.C
chrY    57227201    57227300    Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D    Mmusculus-UniPROBE-Tbp.UP00029  Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D
chrY    57227301    57227400    Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D   Hsapiens-jaspar2016-TGIF2-MA0797.1  Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D
chrY    57227401    57227415    Mmusculus-HOCOMOCOv10-TFDP1_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-TEAD4_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-ZBTB6_MOUSE.H10MO.D

First, I converted all blank cells to 0. Now I'm wondering how I can replace the text in the cells with 1.
awk 'BEGIN { FS = OFS = "\t" } { for(i=1; i<=NF; i++) if($i ~ /^ *$/) $i = 0 }; 1' adrbvbeT_copy.tsv > adrbvbeT_copyN0.tsv

head adrbvbeT_copyN0.tsv 
chr1    1   100 0   0   0
chr1    101 200 0   0   0
chr1    201 300 0   0   0
chr1    301 400 0   0   0
chr1    401 500 0   0   0
chr1    501 600 0   0   0
chr1    601 700 0   0   0
chr1    701 800 0   0   0
chr1    801 900 0   0   0
chr1    901 1000    0   0   0


tail adrbvbeT_copyN0.tsv 
chrY    57226501    57226600    0   0   0
chrY    57226601    57226700    Mmusculus-jaspar2016-Tcf21-MA0832.1 0   0
chrY    57226701    57226800    Mmusculus-HOCOMOCOv10-TF7L2_MOUSE.H10MO.A   0   0
chrY    57226801    57226900    Mmusculus-HOCOMOCOv10-STAT1_MOUSE.H10MO.A   0   0
chrY    57226901    57227000    Mmusculus-HOCOMOCOv10-ZEP2_MOUSE.H10MO.D    0   0
chrY    57227001    57227100    NA-SwissRegulon-SMARCC2.SwissRegulon    Mmusculus-HOCOMOCOv10-PBX2_MOUSE.H10MO.C    0
chrY    57227101    57227200    Mmusculus-HOCOMOCOv10-ZIC3_MOUSE.H10MO.C    Mmusculus-HOCOMOCOv10-TBX5_MOUSE.H10MO.D    Mmusculus-HOCOMOCOv10-PIT1_MOUSE.H10MO.C
chrY    57227201    57227300    Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D    Mmusculus-UniPROBE-Tbp.UP00029  Mmusculus-HOCOMOCOv10-MAFB_MOUSE.H10MO.D
chrY    57227301    57227400    Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D   Hsapiens-jaspar2016-TGIF2-MA0797.1  Mmusculus-HOCOMOCOv10-PITX2_MOUSE.H10MO.D
chrY    57227401    57227415    Mmusculus-HOCOMOCOv10-TFDP1_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-TEAD4_MOUSE.H10MO.D   Mmusculus-HOCOMOCOv10-ZBTB6_MOUSE.H10MO.D

**Tags:** bash, bed, awk, text-processing, command-line
> **Comments:**
> **username_1**: You can use `if (!$i)` -- an empty string is false, and `0` is false. And if the field has the value zero, we're setting it to zero anyway.  
> **username_2**: Why do you need to do this? It seems like [an XY problem](https://xyproblem.info/)  

---

## 1 answers

### Answer by username_3 on 2023-06-21. Score: 3 | Accepted answer

Not an expert, but :
awk 'BEGIN { FS = OFS = "\t" } { for(i=4; i<=6; i++) if($i ~ /^ *$/) {$i = 0} else {$i = 1} }; 1'

Since you have a very specific file format, this code starts from field 4, checking whether the fields after it are empty or not. I assume also that know that you file has 6 fields max exactly, so I "hardcoded" this knowledge.

> **Comments:**
> **username_0**: Great it's working... Thanks a lot, @Sam.  

---

