This is a test version of Biostars. For the public version, visit https://www.biostars.org.
extract columns from file based on header selected from 2nd file

I have one large text table, tab separated. The first row is a header. I then have a second text file that contains a subset of the headers in the first file. I want to extract all the columns of the first file whose header is contained in the list given in the second file. Here an example of the inputs and the desired output:

DATA.TXT

head0 head1 head2 head3 head4
1 25 1364 22 13
2 10 215 1 22

LIST.TXT

head0  
head4

Desired output:

head0 head4  
1 13  
2 22
text delimited
• 13,922 views • link
updated by Ram Ram • written by joaofadista • joaofadista

Can we have a few actual examples of the header names? It's easy if they do actually end 0, 1, 2, 3 ... but I assume they don't?

• 0 views • link
updated by Ram Ram • written by george.ry george.ry

How can i do it in R? Thanks

• 0 views • link
written by rse rse

How can i do it in R? Thanks

• 0 views • link
written by rse rse

3 answers

Just with a simple google search a hundred of questions/answers related to this topic can be easily found, specially, in stackoverflow: http://unix.stackexchange.com/questions/114829/display-column-that-has-a-specific-string

I'd suggest you to try to solve this kind of problems searching on internet and trying yourself before asking. In this case, you can get what you want with a little modification to the solution proposed on the link:

awk -F'\t' 'NR==FNR{arr[$1]++;next}{for(i=1; i<=NF; i++) if ($i in arr){a[i]++;}} { for (i in a) printf "%s\t", $i; printf "\n"}' file2 file1
• 0 views • link
updated by Ram Ram • written by iraun iraun

This works perfectly. However, if LIST.TXT contains headers which do not exist in DATA.TXT, say headX and headY, and their order is "head0, headX, head4, headY", can the output include null values for headX and headY columns and also print output in the same order as in LIST.TXT file?

• 1 views • link
updated by Ram Ram • written by gollapudiv • gollapudiv

Ok, regardless of my comment above ... two bash commands should see you done ...

paste <(seq 1 $(head -1 DATA.TXT | wc -w))  <(head -1 DATA.TXT | tr '\t' '\n') > TMP
cat DATA.TXT | cut -f$(grep -wFf LIST.TXT TMP | cut -f1 | sed ':a;N;$!ba;s/\n/,/g') > OUTPUT

It's the sort of thing that's easier in R or something, though!

// EDIT // On a Mac (useless BSD rather than GNU tools!) apparently you'd have to alter the sed command there to ...

sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/,/g'
• 0 views • link
updated by Ram Ram • written by george.ry george.ry

Thanks George, but it outputs this error message:

cut: option requires an argument -- 'f'
• 0 views • link
updated by Ram Ram • written by joaofadista • joaofadista

if you know the columns in advance, and depending upon the delimitator, you can simply use a cut command. If tabs are used, in this case you select the first and fifth column

cat DATA.TXT | cut -f 1,5 > output.file

This requires you to reveal the desired columns. If this is easy to check, see how easy the code is

If the delimiting is different to a tab, such as an empty space

cat DATA.TXT | cut -d " " -f 1,5 > output.file

I dare to put this here because of the simplicity of this code

• 0 views • link
updated by Ram Ram • written by Antonio R. Franco Antonio R. Franco

Log in to answer this question.

More posts like this