Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-21T14:02:24.140Z
Answers.Text: Great it's working... Thanks a lot, @Sam.
Answers.UserDisplayName: Deb
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-21T05:46:56.003Z
Answers.LastActivityDate: 2023-06-21T05:46:56.003Z
Answers.UserDisplayName: Sam
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-06-21T12:02:38.040Z
Comments.Text: 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.
Comments.UserDisplayName: glenn jackman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-06-21T13:24:21.523Z
Comments.Text: Why do you need to do this? It seems like [an XY problem](https://xyproblem.info/)
Comments.UserDisplayName: Ram RS
Tags: <bash><bed><awk><text-processing><command-line>
Title: How can I change all text in a .tsv file to 1, except the first three fields?
ThreadText: # 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, @username_3.  
  
  ---
  
  