For a given file, use AWK to to print columns containing particular patterns

Question

I have an input file (tab-delimited) with a little over 200 columns (just a few columns shown).

col1     col2        col3          col4               col5 
ID       GPD1431     GPD1632       GPD1253            GPD2353
Group    GDS_Treated GDS_Untreated GDS_paired_Treated GDS_paired_Untreated 
Measure1 7.6         8.2           9.3                4.2
Measure2 0.32        0.56          0.343              0.423

What I would like to do is subset this datafile so that I only get the columns where Group describes an Untreated sample or is the first column. I would like the output file to be a tab-delimited file too. Like so:

col1     col3          col5 
ID       GPD1632       GPD2353
Group    GDS_Untreated GDS_paired_Untreated 
Measure1 8.2           4.2
Measure2 0.56          0.423

So far, what I have is this:

awk -F '\t' '{for(i=1; i<=NF; i++) {if($i ~ /Untreated|untreated/ || i==1) col_array[i]=i}} END {for (val in col_array) {print col_array[val]}}' file > columns_to_print.txt

The results of this output are:

1
3
7
9
12
43
...
203

Printing out the contents of col_array, the numbers seem to match the correct column numbers, based on a quick visual check. So, this seems to be a good start. But I'm stuck as to how to use this information to print out the columns I want in a tab-delimited file.

I've tried awk 'NR==FNR{columns[$1]=$1;next}{for(i=1; i<=NF; i++){if(columns[i]>1)print $i}}' columns_to_print.txt file, but this doesn't seem to print the right columns out (and the things that are printed out are formatted such that everything is in one column.)

Thank you for your help.

Answer 1

Here is an awk script for this task:

awk -F '\t' 'BEGIN {cols[1]}
    pass == 1 && $1 == "Group" {
        for (i=2;i<=NF;i++) if ($i ~ /[Uu]ntreated$/) cols[i]
        nextfile
    }
    pass == 2 {
        rec = ""
        for (i=1;i<=NF;i++) {
            if (i in cols) rec = (rec ? rec FS $i : $i)
        }
        print rec
    }' pass=1 file pass=2 file

At first pass we search for the line starting with "Group" and for this line we store the column numbers for the fields matching our pattern. And we exit the first parsing immediately with nextfile.

At second pass, we print only the columns stored in cols. Loop for all fields and construct the line to print into variable rec. Also a standard conditional expression is being used here.

Test output:

col1      col3           col5
ID        GPD1632        GPD2353
Group     GDS_Untreated  GDS_paired_Untreated
Measure1  8.2            4.2
Measure2  0.56           0.423