I need to form a matrix out of unbalanced set of records. First eliminate the sample that do not have at least 3 variables (col2). So, in the example, samples 4 and 5 get eliminated.
Then form a matrix of values (col3) from the samples using only variables that are present accross all samples. So in the example, var4 from sample 3 gets eliminated.
Input
sample1 var1 xx
sample2 var1 yy
sample3 var1 zz
sample4 var1 zz
sample1 var2 xx
sample2 var2 xx
sample3 var2 yy
sample5 var2 yy
sample1 var3 xx
sample2 var3 tt
sample3 var3 yy
sample3 var4 yy
sample5 var3 yy
Output
sample1 sample2 sample3
xx yy zz
xx xx yy
xx tt yy
The following approach might work for a smaller data set but for millions of rows that I have will need some sophisticated approach.
I have broken it down into steps,
awk ' {LN[$2]++; HD[$1]++; MX[$2,$1]=$3}
END {for (i in HD) if (HD < 3) delete HD
for (i in LN) if (LN < 3) delete LN
printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
for (j in LN) {printf "%10s",j; for (i in HD) printf "%10s", MX[j,i]; print ""}
}
' file
sample1 sample2 sample3
var1 xx yy zz
var2 xx xx yy
var3 xx tt yy
works for the small sample given, I'm afraid it will show limitations soon as the input file grows larger...
---------- Post updated at 13:33 ---------- Previous update was at 13:09 ----------
OK, this might do:
awk ' {LN[$2]++; HD[$1]++; MX[$2,$1]=$3}
END {do {CNT=0
for (i in HD) if (HD < 3) {delete HD; for (j in LN) if (MX[j,i]) {delete MX[j,i]; LN[j]--; CNT++}}
for (j in LN) if (LN[j] < 3) {delete LN[j]; for (i in HD) if (MX[j,i]) {delete MX[j,i]; HD--; CNT++}}
}
while (CNT > 0)
printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
for (j in LN) {printf "%10s",j; for (i in HD) printf "%10s", MX[j,i]; print ""}
}
' file