unix - Extract columns from a file based on header selected from another file

Question

I have the following problem that I want to solve in awk. I have one large text table, comma separated, consisting of 100k rows and 5k cols. The first row is a header and the first column is a record id. 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

   ID, head1, head2, head3, head4  
    1, 25.5, 1364.0, 22.5, 13.2  
    2, 10.1, 215.56, 1.15, 22.2  

LIST.TXT

head1  
head4  

Desired output:

ID, head1, head4  
1, 25.5, 13.2  
2, 10.1, 22.2

Anybody can give me some advice on how to solve this problem in awk or however through unix scripting? Thanks in advance for any help!

Answer 1

There is a useful awk script here which you can use to extract specific column names from a csv file.

I have modified it slightly so that it can read column names from another file. Save the script below as dataExtractor.sh.

#!/bin/bash

DATAFILE=${1:-data.txt}
COLUMNFILE=${2:-list.txt}

awk -F, -v colsFile="$COLUMNFILE" '
   BEGIN {
     j=1
     while ((getline < colsFile) > 0) {
        col[j++] = $1
     }
     n=j-1;
     close(colsFile)
     for (i=1; i<=n; i++) s[col[i]]=i
   }
   NR==1 {
     for (f=1; f<=NF; f++)
       if ($f in s) c[s[$f]]=f
     next
   }
   { sep=""
     for (f=1; f<=n; f++) {
       printf("%c%s",sep,$c[f])
       sep=FS
     }
     print ""
   }
' "$DATAFILE"

Running it:

$ cat data.txt
ID,head1,head2,head3,head4
1,25.5,1364.0,22.5,13.2
2,10.1,215.56,1.15,22.2

$ cat list.txt
ID
head1
head4

$ dataExtractor.sh data.txt list.txt
1,25.5,13.2
2,10.1,22.2