1

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.

3
  • Please edit your question and include an accurate input sample (without dots (...) and descriptions, but a few belieavable lines) and the corresponding final output for this input. I see no reason to do this task in to steps. You could use one command to decide the columns to extract and just extract them.
    – thanasisp
    Nov 24, 2020 at 4:31
  • Okay, so I've made the edits. I think I have a reasonable command with awk -F '\t' '{for(i=1; i<=NF; i++) {if($i ~ /Untreated|untreated/ || i==1) col_array[i]=i}}' file that gets me the columns I want to extract (given my testing by printing out the array). But I'm stuck on using this to extract the columns that I want and print it out so that I still get a tab-delimited file.
    – Lyn
    Nov 24, 2020 at 4:48
  • Your command seems good, this is the logic to get the column numbers. You can do this at first pass, and at second pass print only these columns, I have added a way for this, I think it's readable enough. Also you cab just set col[i], no need to assign a value.
    – thanasisp
    Nov 24, 2020 at 5:41

1 Answer 1

3

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
5
  • This is strange, but this isn't giving me the output I expect. Using my command above to get me the columns I want to extract, I painstakingly double-checked to make sure that they were the right columns. Applying this script results in IDs that don't match up with the right Group. It's possible that I've oversimplified my file structure too much here. I've fiddled with this some more and gotten what I think is the correct output using a two-step approach (first, output the columns I want to a file, then use that to select columns.) Thank you for your time and demonstrating the pass syntax.
    – Lyn
    Nov 24, 2020 at 18:31
  • 1
    I have updated with setting the delimiter to tab. From what you describe, this should be the reason. I see this delimiter in your example (but any files copy-pasted here have their tabs converted to spaces and I missed that by testing)
    – thanasisp
    Nov 24, 2020 at 20:22
  • Thank you so much. Running this, I get the same approach as my two-step solution, and I've also taken the time to check just to be sure. If you don't mind, would you be able to explain this step: if (i in cols) rec = (rec ? rec FS $i : $i)? From what I can gather, you start with rec being empty. Then, if the field number i is one of the ones we want to keep, we evaluate the next expression. When rec is empty, I guess it evaluates to null, so rec becomes equal to $i. But after that, rec evaluates to true, so you append the next desired column to the existing rec with a tab?
    – Lyn
    Nov 25, 2020 at 21:42
  • Nice. Regarding your query: There is no null for awk. There can be empty values, this is the empty string (or zero). So the expression rec = rec ? rec sep value : value is an idiom to append every time the new value to rec using a separator, excluding the first time when rec is empty, and we don't want the separator in front of the first value. Only for the first time when rec = "" the : part is executed. I also enclose the second part of this assignment into parentheses, for readability, it works without them, similar to C.
    – thanasisp
    Nov 25, 2020 at 21:54
  • In general for awk, the empty string and the numerical zero, are the equivalent to false. awk functions return 0 to indicate "false", e.g. if (index(...)), if (gsub(...)) etc.
    – thanasisp
    Nov 25, 2020 at 22:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .