Loop through columns, then transform cells, then show unique values in awk

I'd like to transform some data using awk but need some help pls. I want to extract for columns starting with "sam" (where the column number is undefined) everything before the first colon.

Input:

col1	col2	col3	col4	sam1	sam2	sam3
a	b	c	d	0/1:12	1/0:9	0/1:16
e	f	g	h	0/0:7	1/1:98	0/0:8

Desired output:

col1	col2	col3	col4	sam1	sam2	sam3
a	b	c	d	0/1	1/0	0/1
e	f	g	h	0/0	1/1	0/0

This is the best I've got so far... but it doesn't work.

awk -F"\t" '{ for(i=5; i<=NF; --i); split($i,a,":"); print a[1]}}' input > output

I know how to cut a column i.e. cut -d ':' -f2 but as far as I understand you can't combine cut with awk in a loop!

Then, I want to find all the unique values for columns starting with sam in the output file e.g.

0/1
1/0
0/0
1/1

I'm afraid I'm totally lost on an awk solution for that. I can do it in R but an awk solution would be preferred and much faster.

R solution:

output %>% pivot_longer(-c(col1:col4)) -> df_long
df_long %<>% select(value)
unique(df_long)

$ awk 'NR==1 {for(i=1;i<=NF;i++) if($i~/^sam/) cols[i]} 
             {for(i=1;i<=NF;i++) 
                if(i in cols) 
                  {split($i,t,":"); $i=t[1]}}1' file | 
  column -t

col1  col2  col3  col4  sam1  sam2  sam3
a     b     c     d     0/1   1/0   0/1
e     f     g     h     0/0   1/1   0/0

or just interested in the unique values

$ awk 'NR==1 {for(i=1;i<=NF;i++) if($i~/^sam/) cols[i]; next} 
             {for(i in cols) {split($i,t,":"); if(!vals[t[1]]++) print t[1]}}' file

0/1
1/0
0/0
1/1

awk '
    BEGIN { FS = OFS = "\t" }
    NR==1 { while(i++<NF) if ($i ~ /^sam/) p[i] }
    NR>1 { for (i in p) { sub(/:.*$/,"",$i); u[$i] } }
    { print >"output" }
    END { for (i in u) print i >"unique" }
' input

• use first row to populate a list with columns of interest
• on subsequent rows, process relevant columns and copy amended values to hash
• print each line to the file called "output"
• at the end print the keys of the hash to a file called "unique"

awk's arrays are hashes so storing items as keys of an array gives the unique items

You can do it all in one go fairly easily. For example simply using gsub() to remove the :XX from each field and then a simple array to collect the unique sam fields you would have:

awk -F"\t" '{gsub(/:[^[:space:]]+/,"")} FNR>1 {for (i=5; i<=NF; i++) a[$i]++} END {for (i in a) print i}1' file

Example Use/Output

With your content in file you would have:

$ awk -F"\t" '{gsub(/:[^[:space:]]+/,"")} FNR>1 {for (i=5; i<=NF; i++) a[$i]++} END {for (i in a) print i}1' file
col1    col2    col3    col4    sam1    sam2    sam3
a       b       c       d       0/1     1/0     0/1
e       f       g       h       0/0     1/1     0/0
1/0
1/1
0/0
0/1

In Awk Script Form

You can put the contents in a simple script file and make it executable with chmod +x and then just provide the filename to read as an argument. For example, create sam.awk as follows:

#!/bin/awk -f

BEGIN { FS = "\t" }
{
  gsub(/:[^[:space:]]+/,"")
  print
}
FNR>1 {
  for (i=5; i<=NF; i++)
    a[$i]++
}
END {
  for (i in a)
    print i
}

Now simply chmod +x sam.awk and execute ./sam.awk file to produce:

$ ./sam.awk file
col1    col2    col3    col4    sam1    sam2    sam3
a       b       c       d       0/1     1/0     0/1
e       f       g       h       0/0     1/1     0/0
1/0
1/1
0/0
0/1

Either way, as a one-liner or as a script is fine -- up to you entirely.

"find all the unique values for columns starting with sam"

$ grep -o "[0-9]/[0-9]" inputfile|sort -u
0/0
0/1
1/0
1/1