0

I have a data file (data.txt) with ~320 rows and ~1800 columns (1.9 MB), and I need to extract certain columns out (for all rows). The general layout of data.txt is as follows:

 ID           Adipocyte - breast, donor2.CNhs11969.11327-117E4   ....
HGNC:8888                        0                               .... 
HGNC:9999                     123.92                             ....
HGNC:1000                      9.31                              .... 

I have a list of the columns I need to extract (in a file list.txt), but this list is made up of only the CNhsXXXXX identifier in the column term (i.e. for the above example, the list would only contain CNhs11969, not the whole term Adipocyte - breast, donor2.CNhs11969.11327-117E4).

I've used grep for a list of rows before, but have not for columns. I had a look around but could not find a way to grep multiple columns with multiple terms. I'm very new to unix (I'm a biologist, little experience in computing), so I'm unsure if grep can do this.

Any help would be appreciated.

EDIT: My sample output would be only ~850 of the ~1800 columns (only those containing the terms in my list.txt file). Example: If my list contained only CNhs5006 and CNhs7021, I would only want columns containing those terms in the header. Example of the data.txt:

ID        XXXCNhs5006XXX   XXXCNhs6025XXX   XXXCNhs7021XXX   XXXCNhs8095XXX
HGNC:1111     1.23                 1.53             9.21            0
HGNC:2222     1.95                73.92               0           123.29 

Example of the desired output:

ID         XXXCNhs5006XXX   XXXCNhs7021
HGNC:1111          1.23          9.21
HGNC:2222          1.95           0

My list.txt is just a simple list of terms (1 column, ~850 rows, each row containing 1 search term). Example:

CNhs1111 CNhs2222 CNhs3333 CNhs4444

  • 1
    I don't understand what would you want to extract ? If you can post your sample output that would be nice to us – Rahul Jul 12 '16 at 5:45
  • Of the ~1800 columns, I would want only ~850 containing the terms in my list.txt file. – DiscoA Jul 12 '16 at 5:55
  • If I understand you correctly, you have a file containing column names and want to extract the corresponding columns from a data file on the basis of the column names in the header line. If so, grep can't do that, awk can. But it is unclear how to identify the columns. Please elaborate. – Michael Vehrs Jul 12 '16 at 5:56
  • what list.txt contains ? rather than extending conversation here, please edit your post and show it to others – Rahul Jul 12 '16 at 5:57
  • My post is now updated for my desired output. – DiscoA Jul 12 '16 at 6:03
0

I suppose OP can do the follows:

  1. Read header data.txt and convert columns into rows
  2. Grep the rows to obtain numbers to match list.txt
  3. Pass data.txt through the cut

If columns are tab delimited the script can be:

cut -f 1,$(
    head -n1 data.txt |
    tr '\t' '\n' |
    grep -nf list.txt |
    sed ':a;$!N;s/:[^\n]*\n/,/;ta;s/:.*//'
) data.txt
0

You could try something like this:

awk  -F'\t' -f script.awk column.names data

where script.awk is:

NR == FNR { columns[NR] = $0; next }
NR > FNR && FNR == 1 {
    for (i = 1; i <= NF; i++) {
        for (j = 1; j < NR; j++) {
            if ($i ~ columns[j]) {
                selected[i] = 1;
                break;
            }
        }
    }
}
{
    for (i = 1; i <= NF; i++) {
        if (i in selected) printf $i "\t";
    }
    print "";
}

@Costas's approach is neat. However, I think it could be slightly simplified:

head -n1 data | tr '\t' '\n' | grep -nf column.names | cut -f1 -d: \
  | paste -sd, | xargs -I{} cut -f {} data
  • Unfortunately this is just returning a blank document (~1400 lines, just nothing there). I tried recreating the list file and re-duplicating the data.txt file from its copy but still no luck, so I don't believe it's a dos line ending problem. – DiscoA Jul 12 '16 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.