# expand data based on scores

Firstly I have a set of valid combinations of 3 columns (validlist), so the final result must be a subset of this file.

``````a   b   c
c   b   c
p   b   d
d   y   d
p   y   d
x   y   z
``````

I have a score matrix like below (scorefile), where the 3rd column ( 1 max, 0 min) says how close the 2nd column variable is to the 1st column variable

``````a       b       0.3
a       c       0.87
a       d       0.75
b       x       0.87
b       y       0.98
b       z       0.24
c       m       0.9
c       n       0.86
d       p       0.87
``````

Given a set of variables, I need to expand the selection to other combinations which are significantly close (> 0.7) to the given column variables also the sum of closeness is greater than 1.6. .

For example : variable a can be expanded to include variables c and d as they have scores > 0.7 with a.

Variable b, can be expanded to include y, and c can include m and n.

So my example input is

``````a   b   c
d   b   a
``````

and expanded output is

``````intermediate output

a   b   c
c   b   c
d   b   c
a   y   c
c   y   c
d   y   c
a   b   m
c   b   m
d   b   m
a   y   m
c   y   m
d   y   m
a   b   n
c   b   n
d   b   n
a   y   n
c   y   n
d   y   n
d   b   a
p   b   a
d   y   a
p   y   a
d   b   c
p   b   c
d   y   c
p   y   c
d   b   d
p   b   d
d   y   d
p   y   d
``````

which is then subset by the the validlist to have the final output.

``````a   b   c
c   b   c
p   b   d
d   y   d
p   y   d
``````

I have a working code for both steps

``````awk '
NR==FNR {
if (\$3 > 0.7) {
scr[\$1,\$2]=\$3
var[\$1]
}
next
}
{
for (col1 in var) {
for (col2 in var) {
for (col3 in var)    {
if (
scr[\$1,col1] && scr[\$2,col2] && scr[\$3,col3] &&
scr[\$1,col1] > 0.7   &&
scr[\$2,col2] > 0.7   &&
scr[\$3,col3] > 0.7   &&
scr[\$1,col1] + scr[\$1,col1] > 1.6
) {
print col1,  col2,   col3
}
}
}
}
}
'  score input > intermediateout

grep -f intermediateout validlist > finalout
``````

The problem is score file has 345 million records and the valid list has only 2600 records. So the 3 for loops run forever, can you please help speed up the process, because if we can filter the invalid combinations first, the output is much smaller.

`````` free -m
total       used       free     shared    buffers     cached
Mem:        387591     299120      88471          2        481     292698

cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6
``````

Thanks a lot for your help !!

Hi Glenn, I am getting a syntax error on the server. Would you please have a look? Strangely, this syntax error doesnt show on cygwin.

``````awk: cmd. line:9:         if (\$3 > 0.7) clos[\$1][\$2]  # I would name this array "close"
awk: cmd. line:9:                               ^ syntax error
awk: cmd. line:15:             col[i][\$i]
awk: cmd. line:15:                   ^ syntax error
awk: cmd. line:16:             for (key in clos[\$i])
awk: cmd. line:16:                             ^ syntax error
awk: cmd. line:17:                 col[i][key]
awk: cmd. line:17:                       ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                              ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                                   ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                                                  ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                                                       ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                                                                      ^ syntax error
awk: cmd. line:24:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:24:                                                                           ^ syntax error
awk: cmd. line:25:             if ( (a in col) && (a in col) && (a in col) )
awk: cmd. line:25:                                                                             ^ unexpected newline or end of string
``````
#

Update:

Hi Glenn,

I downloaded gawk 4.xxx and this error is gone. Thank you for the suggestion.

I played around with the code quite a bit and I think I have a better understanding of 2 dimensional arrays now, thank you.

As for the problem at hand, if I understand correctly there is a potential issue that each input row has to be processed independent of the others. So there should be a set of possible output rows for each input row.

That is where the sum of closeness comes in ,

For each input row 1) Expanded variables with \$1 must be 0.7 close with \$1. 2) Expanded variables with \$2 must be 0.7 close with \$2. 3) For each variable in the \$1 array, for each element in the \$2 array closeless (\$1 with variable in \$1 array) + closeless (\$2 with variable in \$2 array) must be greater than 1.6 4) Expanded variables with \$3 must be 0.7 close with \$3.

As you are creating 3 arrays based on the 3 input columns, the "for each row" information is being lost, and the sum of closeness can not be implemented. Please let me know if this makes sense.

I tried a possible tweak but I think I`m lost in the complexity of 2-dim arrays and also possibility of using a 3-dim array.

``````gawk '
# validlist
FILENAME == ARGV {
valid[\$1 FS \$2 FS \$3]
next
}
# scorefile
FILENAME == ARGV {
if (\$3 > 0.7) clos[\$1][\$2]
scr[\$1][\$2]=\$3;                   # I would name this array "close"
next                        # but that is a keyword
}
# input
{

col[NR FS \$2][\$1];
col[NR FS \$2][\$2];
col[NR FS \$3][\$3];

for (key in clos[\$i])
{
col[NR FS \$1][\$i];
col[NR FS \$2][\$i];
col[NR FS \$3][\$i];

if scr[\$1][i] + scr[\$2][i] > 1.6
possible[\$i]=\$1 FS \$2 FS \$3
}

}
END {
PROCINFO["sorted_in"] = "@ind_str_asc"
for (v in valid) {
for (allposs in possible)
if ( v==allpos  )
print v
}
}
' validlist scorefile input
``````
• I tried to be more precise. thanks, please advise. – Hia Sen Nov 10 '15 at 22:49

OK, here you go. The key is to have the awk program read the valid list too. Process the score and input files. Then loop over the valid combinations, not all the permutations.

Uses GNU awk for arrays of arrays

``````gawk '
# validlist
FILENAME == ARGV {
valid[\$1 FS \$2 FS \$3]
next
}
# scorefile
FILENAME == ARGV {
if (\$3 > 0.7) clos[\$1][\$2]  # I would name this array "close"
next                        # but that is a keyword
}
# input
{
for (i=1; i<=3; i++) {
col[i][\$i]
for (key in clos[\$i])
col[i][key]
}
}
END {
PROCINFO["sorted_in"] = "@ind_str_asc"
for (v in valid) {
split(v, a)
if ( (a in col) && (a in col) && (a in col) )
print v
}
}
' validlist scorefile input
``````

outputs

``````a b c
c b c
d y d
p b d
p y d
``````
• I realize I'm missing the sum of closeness. Left as an exercise – glenn jackman Nov 11 '15 at 0:45
• Hi Glenn, I am getting a syntax error on the server. – Hia Sen Nov 11 '15 at 10:10
• What version of gawk are you running? – glenn jackman Nov 11 '15 at 11:10
• \$ awk -W version GNU Awk 3.1.7 – Hia Sen Nov 11 '15 at 11:54
• I wonder if arrays of arrays is a gawk 4 feature. Check the change log. – glenn jackman Nov 11 '15 at 12:10