This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Retrieve header of column (field) in which data is above or equals a number

Imagine I have the following file with a header (A,B,C,D,E,F) and one row of data:

A    B  C   D   E    F
-20  0  4   20  10   50

I want to extract the header names corresponding to the fields where data is >= 20 with the command line, so that I get:

D F

How can I do this? (with command line or awk)

commandline awk
• 2,053 views • link
updated by Pierre Lindenbaum Pierre Lindenbaum • written by salamandra salamandra

3 answers

Just transpose (rotate) the table using GNU datamash and filter using awk:

cat data.txt \
    | datamash transpose \
    | awk '$2 >= 20' \
    | cut -f 1 | paste -s -d ' '

If there are more than one row of data, you need csvtk:

$ cat data.txt 
A       B       C       D       E       F
-20     0       4       20      10      50
-20     20      4       10      0       30


$ cat data.txt \
    | csvtk transpose -t \
    | csvtk filter -H -t --any -f '2,3>=20' \
    | cut -f 1 | paste -s -d ' '
B D F

Where --any -f '2,3>=20' means finding rows in which value of any column is >= 20.

No --any:

$ cat data.txt \
        | csvtk transpose -t \
        | csvtk filter -H -t -f '2,3>=20' \ 
        | cut -f 1 | paste -s -d ' '
F
• 0 views • link
written by shenwei356 shenwei356

My first guess with command line tools would be to transpose your file, awk for selecting those above your cutoff, cut for getting the header (which is the first column after transposing).

But my language of choice is Python, so I would do the following:
The code is untested, so let me know if something doesn't work as expected.

(I wrote this slightly more verbose than I would do for myself, but hopefully this makes it more clear what's going on.)

• 0 views • link
written by WouterDeCoster WouterDeCoster 
echo -e  "A    B  C   D   E    F\n-20  0  4   20  10   50" | \
awk 'NR==1 {split($0,header);next;} {for(i=1;i<=NF;++i) if($i>=20) printf("%s ",header[i]);} END{printf("\n");}'
D F
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this