Insert missing values

Shell Programming and Scripting
1

Hi, please help with this, I need to insert missing values into a matrix for a regression analysis.

I have made up an example. The first three columns are variables with levels and the next 3 are values, the 4th column missing values should be replaced by 0s, and 5th and 6th column missing values replaced by NA. The final table should have all combinations of the variables in columns 1 , 2 and 3.
The example has just 2 or 3 levels of each variable, the actual data might have upto 50 levels.

Input

Var1	Var2	Var3	Val1	Val2	Val3
X1	L1	G1	1	4	7
X2	L2	G2	2	5	8
X2	L3	G1	3	6	9

Output

Var1	Var2	Var3	Val1	Val2	Val3
X1	L1	G1	1	4	7
X1	L1	G2	0	NA	NA
X1	L2	G1	0	NA	NA
X1	L2	G2	0	NA	NA
X1	L3	G1	0	NA	NA
X1	L3	G2	0	NA	NA
X2	L1	G1	0	NA	NA
X2	L1	G2	0	NA	NA
X2	L2	G1	0	NA	NA
X2	L2	G2	2	5	8
X2	L3	G1	3	6	9
X2	L3	G2	0	NA	NA
2

Hi, what have you tried so far?

3

try:

awk '
NR==1 {$1=$1; print $0; next;}
{ c1[$1]=$1;
  c2[$2]=$2;
  c3[$3]=$3;
  co[$1,$2,$3]=$4 "\t" $5 "\t" $6;
}
END {
  for (i in c1) {
     for (j in c2) {
        for (k in c3) {
           printf i "\t" j "\t" k "\t";
           if (co[i, j, k]) {
              print co[i, j, k];
           } else {
              print "0\tNA\tNA";
           }
        }
     }
  }
}' OFS="\t" infile
4 
perl -lane 'BEGIN {$" = "\t"}
  if($. == 1) {print; next}
  {$A[$.] = $F[0] if (! grep(/$F[0]/, @A));
  $B[$.] = $F[1] if (! grep(/$F[1]/, @B));
  $C[$.] = $F[2] if (! grep(/$F[2]/, @C));
  $H{$F[0] . $" . $F[1] . $" . $F[2]} = $_}
  END {for($i = 2; $i <= $#A; $i++) {
    for($j = 2; $j <= $#B; $j++) {
      for($k = 2; $k <= $#C; $k++) {
        $T = ($A[$i] . $" . $B[$j] . $" . $C[$k]);
        if(exists $H{$T}) {print $H{$T}}
        else
          {print ($T . $" . 0 . $" . "NA" . $" . "NA")}}}}}' file