Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)


Can anyone help me with this issue?

share|improve this question
Have all columns the same number ow rows? Is this five colums only? – Hauke Laging Jun 5 '13 at 15:03
Unless you're only going to do this once, ever, you're probably much better off dumping it into an actual database. – l0b0 Jun 5 '13 at 15:20
@Hauke Laging All columns have the same amount of rows, but there are many columns - around 500 or so. – Lalilienne Jun 5 '13 at 15:44
@l0b0 I will use it more than once. =) I will probably have to modify it slightly though, but I guess I can do it. – Lalilienne Jun 5 '13 at 15:45

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
share|improve this answer
+1 Great solution! – jaypal singh Jun 10 '13 at 2:15

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++) {
        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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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