please help with the following.
I have 4 col data .. instrument , category, variable and value. the instruments belong to particular categories and they all measure some variables (var1 and var2 in this example), the last column is the value an instrument outputs for a variable.
I have used some blank rows for ease of understanding , there is no blank row in the actual dataset.
In this example instruments (ab,bc,pt and ef) belong to cat1 ; instruments (cd,gh and pt ) belong to cat2.
ab cat1 var1 aa
bc cat1 var1 aa
pt cat1 var1 tt
cd cat2 var1 tt
gh cat2 var1 gg
ab cat1 var2 aa
ef cat1 var2 aa
pd cat2 var2 tt
gh cat2 var2 tt
As you can see , there are some rows missing like
( ef cat1 var1 )
( cd cat2 var2)
...
...
I want to impute these rows , if there is a consensus value within the same (cat var ) combination above 60%.
For example, in the part of data
ab cat1 var1 aa
bc cat1 var1 aa
pt cat1 var1 tt
(cat1 var1) has a value of aa 2 out of 3 times (66%). Since this is greater than cutoff of 60% , we can impute the missing instrument (ef) value in this category (cat1) and variable(var1) as aa.
ab cat1 var1 aa data
bc cat1 var1 aa data
pt cat1 var1 tt data
ef cat1 var1 aa imputed
This is my desired output, row order doesn't matter and blank rows not needed.
ab cat1 var1 aa data
bc cat1 var1 aa data
pt cat1 var1 tt data
ef cat1 var1 aa imputed
ab cat1 var2 aa data
ef cat1 var2 aa data
bc cat1 var2 aa imputed
pt cat1 var2 aa imputed
cd cat2 var1 tt data
gh cat2 var1 gg data
pd cat2 var2 tt data
gh cat2 var2 tt data
cd cat2 var2 tt imputed
what I tried but getting lost in arrays
awk 'NR==FNR{
f1[$2][$1]=$3;
cat[$1][$2]++;
ins[$1]=$2;
val[$1$2]=$4;
next
}
{
for(ins in f1[cat]){
for(var in f1){
if(f1[var][ins]){
num[cat][var][f1[cat][ins]]++
len[cat][var]++;
}
}
for(val in num[cat][var){
if(num[cat][var][val]/len[cat][var] >= .60){
printf ins,cat,var,val
}
}
}' data data