AWK - a question about columns

Question

I have a question. I was trying to deal with it by myself, but it seems like I am too new in awk to make it work.

Let's assume that we have a file (eg. database.txt) (values are tab-separated):

NA64715 YU24921 MI84612 MI98142 NA94732    
3241531 4957192 4912030 6574918 0473625     
0294637 9301032 8561730 8175919 8175920     
9481732 9359032 8571930 8134983 9385130     
9345091 9385112 2845830 4901742 3455141     

In a separate file (eg. populations.txt) I have information about which ID belongs to which group, eg.:

NA64715 Europe    
YU24921 Europe    
MI84612 Asia    
MI98142 Africa    
NA94732 Asia    

What I need to do is to force awk to create separate files with columns for all groups (Europe, Asia, Africa). The file I need to work on is huge, so I cannot simply count and number columns and do it the easy way. I need awk to check which ID belongs to which population (Europe etc.), then find that particular column in a database file, and then copy a whole column to a new file (separate for all the populations).

The result should look like:

File 1 (europe.txt):

NA64715 YU24921     
3241531 4957192     
0294637 9301032     
9481732 9359032    
9345091 9385112      

File 2 (asia.txt)

MI84612 NA94732    
4912030 0473625    
8561730 8175920    
8571930 9385130    
2845830 3455141    

File 3 (africa.txt)

MI98142     
6574918    
8175919    
8134983    
4901742    

Can anyone help me with this issue?

Answer 1

This works in one pass through the file, and does not need to store the whole file in memory. It does keep open file descriptors for each destination file.

awk -F '\t' '
    NR==FNR {population[$1]=$2; next}
    FNR==1 {
        for (i=1; i<=NF; i++) {
            destination[i] = population[$i] ".txt"
        }
    }
    {
        delete separator
        for (i=1; i<=NF; i++) {
            printf "%s%s", separator[destination[i]], $i > destination[i]
            separator[destination[i]] = FS
        }
        for (file in separator) {
            printf "\n" > file
        }
    }
' populations.txt database.txt

Answer 2

I believe this is not the best way, as we need to read a database.txt as many time as regions we have plus one. Unfortunately the other way didn't came to my mind.

1. Transpose database.txt:

   awk '{for(i=1;i<=NF;i++){a[NR,i]=$i}}NF>p{p=NF}END{for(j=1;j<=p;j++){str=a[1,j];for(i=2;i<=NR;i++){str=str" "a[i,j];}print str}}' database.txt > database.tmp

a bit more readable ( the same command):

awk '
{ 
    for (i=1; i<=NF; i++)  {
        a[NR,i] = $i
    }
}
NF>p { p = NF }
END {    
    for(j=1; j<=p; j++) {
        str=a[1,j]
        for(i=2; i<=NR; i++){
            str=str" "a[i,j];
        }
        print str
    }
}' database.txt > database.tmp

2. read file with ids and grep all ids from transposed database.tmp:

while read id region ; do grep -m 1 $id database.tmp >> $region.txt.tmp ; done < population.txt

3. transpose all region.txt.tmp file to a form you need:

for region_file in *txt.tmp ; do awk '{for(i=1;i<=NF;i++){a[NR,i]=$i}}NF>p{p=NF}END{for(j=1;j<=p;j++){str=a[1,j];for(i=2;i<=NR;i++){str=str" "a[i,j];}print str}}' $region_file > ${region_file%.tmp} ; done

4. remove all temporaly files