How to align 3 files based on first column value

Question

I have 3 text files c.dat, n.dat, and h.dat The contents are similar, in this format

c.dat    n.dat    h.dat
1 0.ccc  3 1.nnn  1 2.hhh
2 0.ccc  4 1.nnn  2 2.hhh
4 0.ccc  5 1.nnn  5 2.hhh

Desired output:

1 0.ccc Inf 2.hhh
2 0.ccc Inf 2.hhh
3 Inf 1.nnn Inf
4 0.ccc 1.nnn Inf
5 Inf 1.nnn 2.hhh
6 Inf Inf Inf
7 ....

Each file has ~100 rows, but they don't always start from 1, and don't aren't always consecutive.

I need to align the 3 files by the first column, such that if the other files don't have it, it's filled in with something like NA, or NaN, or Inf... anything.

Thanks!

yes, please provide 3 sample inputs (one for each file) and the desired output — SiegeX, Dec 09 '10 at 16:26
I've modified my original question to address your comments. — Mikhail, Dec 09 '10 at 16:31
See also http://stackoverflow.com/questions/10364455/merge-two-files-by-key-if-exists-in-the-first-file-bash-script — John Carter, Oct 26 '12 at 20:49
therefromhere - I've discovered that solution, but it's not what I wanted; I needed a full intersection even if not in all files — Mikhail, Oct 29 '12 at 21:03
@Mikhail, fair enough, but thought it was similar enough to worth mentioning - it might help future visitors. — John Carter, Oct 30 '12 at 09:41

score 4 · Accepted Answer · answered Dec 09 '10 at 17:00

awk '
{
        if(FNR==1){f++}
        a[$1,f] = $2
        if($1 > max){max = $1}
}

END{
        for(j=1;j<=max;j++){
          printf("%d\t", j)
          for(i=1;i<=f;i++){
            if(!a[j,i]){printf("Inf\t")}
            else{printf("%s\t", a[j,i])}
          }
          printf("\n")
        }
}' ./c.dat ./n.dat ./h.dat

Output

$ ./awk.dat
1       0.ccc   Inf     2.hhh
2       0.ccc   Inf     2.hhh
3       Inf     1.nnn   Inf
4       0.ccc   1.nnn   Inf
5       Inf     1.nnn   2.hhh

FYI Linux has `join` which does half of the OP – Mikhail Mar 16 '12 at 14:14 — Mikhail, Mar 16 '12 at 14:14

score 0 · Answer 2 · answered Dec 09 '10 at 16:20

0

Kind of a dirty solution -- I created a perl script to loop from 1 to 100, preforming 3 grep statements, piping to awk and if that returns blank, then print Inf.

Still looking for a more elegant solution.

answered Dec 09 '10 at 16:20

Mikhail

8,692
8
56
82

Fritz G. Mehner · Answer 3 · 2010-12-10T08:40:08.683

Pure Bash.

maxindex=0

while read idx val ; do                         # build array from c.dat
    c[idx]=$val
    [ $maxindex -lt $idx  ] && maxindex=$idx
done < 'c.dat'

while read idx val ; do                         # build array from n.dat
    n[idx]=$val
    [ $maxindex -lt $idx  ] && maxindex=$idx
done < 'n.dat'

while read idx val ; do                         # build array from h.dat
    h[idx]=$val
    [ $maxindex -lt $idx  ] && maxindex=$idx
done < 'h.dat'

for (( idx=1; idx<=$maxindex; idx+=1 )); do
    echo -e "$idx  ${c[idx]:-INF} ${n[idx]:-INF} ${h[idx]:-INF}"
done

score -1 · Answer 4 · answered Dec 09 '10 at 17:04

-1

man paste should give you the answer - paste -d ' ' file1 file2 file3

answered Dec 09 '10 at 17:04

l0b0

55,365
30
138
223

I specifically pointed out that the files do not start from the same row number, and are not consecutive – Mikhail Dec 09 '10 at 17:14

How to align 3 files based on first column value

4 Answers4

Output