0
$\begingroup$

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
$\endgroup$
2
  • $\begingroup$ 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. $\endgroup$ Jun 21, 2023 at 12:02
  • $\begingroup$ Why do you need to do this? It seems like an XY problem $\endgroup$
    – Ram RS
    Jun 21, 2023 at 13:24

1 Answer 1

3
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ Great it's working... Thanks a lot, @Sam. $\endgroup$
    – Deb
    Jun 21, 2023 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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